You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
In non generic targets the extra ORDER BY section is added in the end of the output.
In my input I don't specify sorting and I don't need it.
And there is a tracks.name field from the tracks table in this section, which is not presented in FROM section.
It produces the error:
Reproduced in the website playground.
PRQL input
prql target:sql.postgres
from tracks
group media_type_id(
sort name
take 1)join media_types (== media_type_id)select {
tracks.track_id,
media_types.name
}
SQL output
WITH table_0 AS (
SELECT
DISTINCT ON (media_type_id) track_id,
media_type_id,
name
FROM
tracks
ORDER BY
media_type_id,
name
)
SELECTtable_0.track_id,
media_types.nameFROM
table_0
JOIN media_types ONtable_0.media_type_id=media_types.media_type_idORDER BYtable_0.media_type_id,
tracks.name
Expected SQL output
WITH table_0 AS (
SELECT
DISTINCT ON (media_type_id) track_id,
media_type_id,
name
FROM
tracks
ORDER BY
media_type_id,
name
)
SELECTtable_0.track_id,
media_types.nameFROM
table_0
JOIN media_types ONtable_0.media_type_id=media_types.media_type_id
MVCE confirmation
Minimal example
New issue
Anything else?
No response
The text was updated successfully, but these errors were encountered:
I did some playing around with this and it seems to be caused by the combination of:
postgres or duckdb dialects
take 1 which specialises to DISTINCT ON for those dialects.
If you remove the dialect or change it to take 2 then it switches to the ROW_NUMBER() window function based approach which is more general and doesn't have that ORDER BY issue.
Is there any benefit to keep that DISTINCT ON specialisation or could we just use the more general algorithm?
Btw, in the general case, the intermediate variable _expr_0 leaks into the result set, e.g.
prql target:sql.postgres
from tracks
group media_type_id(
sort name
take 2)join media_types (== media_type_id)
Extra columns can often just be ignored but it might cause an issue for some people.
What happened?
In non generic targets the extra ORDER BY section is added in the end of the output.
In my input I don't specify sorting and I don't need it.
And there is a tracks.name field from the tracks table in this section, which is not presented in FROM section.
It produces the error:
Reproduced in the website playground.
PRQL input
SQL output
Expected SQL output
MVCE confirmation
Anything else?
No response
The text was updated successfully, but these errors were encountered: