Open cmeeren opened 5 years ago
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)
}
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.
Consider this:
When I execute this, I would expect the SQL statement to only
SELECT Card.Id, User.Id
, but instead, all columns ofCard
andUser
appear in theSELECT
.