Selecting 2 unrelated types with Union

[ddunlea]

I am trying to select 2 unrelated types via a union such that the resultant type is essentially a not-particularly-type-safe OR of the 2

I define 2 types:

@Table
public struct First: Identifiable, Sendable, Equatable {
  public let id: Int
  public let name: String
  public let description: String
}

@Table
public struct Second: Identifiable, Sendable, Equatable {
  public let id: Int
  public let name: String
  public let count: Int
}

and then I define a selection which is an OR of them:

@Selection
public struct FirstOrSecond: Equatable, Identifiable, Sendable {
  public let name: String
  public let first: First?
  public let second: Second?
  
  public var id: String {
    if let first {
      "first-\(first.id)"
    } else if let second {
      "second-\(second.id)"
    } else {
      "unknown"
    }
  }
  
  public static func all() -> some Statement<FirstOrSecond> {
    let firstPart = First.select{ first in
      return FirstOrSecond.Columns(name: first.name, first: Optional(first))
    }
    let secondPart = Second.select{ second in
      return FirstOrSecond.Columns(name: second.name, second: Optional(second))
    }
    return With {
      firstPart.union(all: true, secondPart)
    } query: {
      FirstOrSecond.order{ $0.name }
    }
  }
}

I have a test that inserts one of each type, and then tries to select both:

0.000s INSERT INTO "firsts"
("id", "name", "description")
VALUES
(NULL, 'first', 'description')
RETURNING "id", "name", "description"
0.000s INSERT INTO "seconds"
("id", "name", "count")
VALUES
(NULL, 'second', 2)
RETURNING "id", "name", "count"
0.000s WITH "firstOrSeconds" AS (
  SELECT "firsts"."name" AS "name", "firsts"."id" AS "id", "firsts"."name" AS "name", "firsts"."description" AS "description", NULL AS "id", NULL AS "name", NULL AS "count"
  FROM "firsts"
    UNION ALL
  SELECT "seconds"."name" AS "name", NULL AS "id", NULL AS "name", NULL AS "description", "seconds"."id" AS "id", "seconds"."name" AS "name", "seconds"."count" AS "count"
  FROM "seconds"
)
SELECT "firstOrSeconds"."name", "firstOrSeconds"."id", "firstOrSeconds"."name", "firstOrSeconds"."description", "firstOrSeconds"."id", "firstOrSeconds"."name", "firstOrSeconds"."count"
FROM "firstOrSeconds"
ORDER BY "firstOrSeconds"."name"
FirstOrSecond(name: "first", first: Optional(DbLibrary.First(id: 1, name: "first", description: "description")), second: nil)
FirstOrSecond(name: "second", first: nil, second: nil)
✘ Test example() recorded an issue at DbLibraryTests.swift:39:9: Expectation failed: (fos.second → nil) != nil

At first, I thought that the multiple AS "name" selections were a factor so I renamed the fields to firstname and secondname respectively. This didn't help, but I did then realise that there are multiple AS "id" parts so I changed the types to have fields named id1 and id2. At this point, the test did pass, but I don't think that's a practical fix as it breaks the code idiom. For example First.Draft expects the id field to be named id

Sample code to reproduce the problem can be found here on the union-demo branch of my sandbox repo

[mbrandonw]

Hi @ddunlea, I didn't have time to look deeply into your code, but I can see that there is an easier way to achieve what you want. You can use @Table with enums to properly describe the exclusive choice of some values rather than using a structure with multiple optionals.

So when I refactor your FirstOrSecond like so:

@Selection @CasePathable
public enum FirstOrSecond: Equatable, Sendable {
  case first(First)
  case second(Second)

  public static func all() -> some Statement<FirstOrSecond> {
    First.select {
      FirstOrSecond.Selection.first($0)
    }
    .union(
      Second.select {
        FirstOrSecond.Selection.second($0)
      }
    )
  }
}

…then the following test passes:

#expect(
  firstOrSecond == [
    .second(Second(id: 1, name: "second", count: 2)),
    .first(First(id: 1, name: "first", description: "description")),
  ]
)

[ddunlea]

Thanks @mbrandonw . I had tried something similar to that previously, but the resultant PartialSelectStatement didn't give me access to an order method which is why I moved to a CTE. I can sort the collection in-memory, but I liked the idea of having the DB do that for me.