SELECT chooses all columns when using quoted queries

[cmeeren]

trafficstars

Consider this:

let cardsAndUsers =
  <@ query {
    for c in ctx.Dbo.Card do
    for u in ctx.Dbo.User do
    select (c, u)
  } @>

let cardIdsAndUserIds =
  query {
    for c, u in (%cardsAndUsers) do
    select (c.Id, u.Id)
  }

When I execute this, I would expect the SQL statement to only SELECT Card.Id, User.Id, but instead, all columns of Card and User appear in the SELECT.

[cmeeren]

Note that I also get the same (incorrect) behaviour when inlining:

query {
    for c, u in (query {
      for c in ctx.Dbo.Card do
      for u in ctx.Dbo.User do
      select (c, u)}) do
    select (c.Id, u.Id)
  }

[Thorium]

Yes, I thought of this before, but the behaviour wanted is not trivial. When you start to make more complex sub-queries you need support for nested sub-queries with aliasses, so that the query logic stays correct. Consider the following:

query {
    for cards1 in (query {
        for c in ctx.Dbo.Card do where (c.Value < 5) select (c)}) do
    join cards2 in (query {
        for c in ctx.Dbo.Card do where (c.Value > 7) select (c)}) on (cards1.Level = cards2.Level)
    select (cards1.Value, cards2.Value)
}

To be able to do that correctly in SQL. we'd have to transfer the sub-queries in SQL, something like this:

select cards1.Value, cards2.Value
from (select * from card where value < 5) as cards1
join (select * from card where value > 7) as cards2
on (cards1.Value = cards2.Value)

(Of course there is different syntax in different SQL-databases for sub-queries.)

Now, keeping that in mind, your query should actually translate to nested SQL query, something like this:

select sub.CardId, sub.UserId
from (
   select Card.*, User.* from Card, User
) as sub

...but alias SQL don't support that kind of double-aliassing (c, u) and selecting two columns of names "id" will cause SQL error = of "specified multiple times".

Instead we currently run the SQL with non-optimal translation, but at least it works.