Using Embedded with Distinct can cause Incorrect and Duplicate Column Aliases

[deusaquilus]

Note that this issue involves the fixes from #1597 or else #1564 with occour before this problem even has a chance to manifest itself.

Version: (e.g. 3.4.5-SNAPSHOT)
Module: (e.g. quill-sql)
Database: (e.g. ALL)

When using an Embedded entity to represent a coproduct instead of a tuple, since aliases include embedded entity property (in ExpandSelect) names but properties are not expanded with embedded property names (in SqlIdiom) the set aliases select from the outer query will not be the same as the set of aliases being expressed.

case class TestEntity(s: String, i: Int, l: Long, o: Option[Int]) extends Embedded
case class Dual(ta: TestEntity, tb: TestEntity)

val qr1 = quote {
  query[TestEntity]
}

val q = quote {
  qr1.join(qr1).on((a, b) => a.i == b.i).nested.map(both => both match { case (a, b) => Dual(a, b) }).distinct.nested
}

println(run(q).string)
// SELECT x.s, x.i, x.l, x.o, x.s, x.i, x.l, x.o FROM (
//   SELECT DISTINCT both._1s AS tas, both._1i AS tai, both._1l AS tal, both._1o AS tao, both._2s AS tbs, both._2i AS tbi, both._2l AS tbl, both._2o AS tbo FROM (
//    SELECT a.s AS _1s, a.i AS _1i, a.l AS _1l, a.o AS _1o, b.s AS _2s, b.i AS _2i, b.l AS _2l, b.o AS _2o FROM test_entity a INNER JOIN test_entity b ON a.i = b.i) AS both) AS x

Notice that s, i, l, and o are not actually the aliases inside the 1st inner query. Also, s, i, l, and o are all selected twice.

Workaround

Use tuple co-products instead.

@getquill/maintainers