After Join generated extra ORDER BY section with phantom field

[annashmatko]

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

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
)
SELECT
  table_0.track_id,
  media_types.name
FROM
  table_0
  JOIN media_types ON table_0.media_type_id = media_types.media_type_id
ORDER BY
  table_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
)
SELECT
  table_0.track_id,
  media_types.name
FROM
  table_0
  JOIN media_types ON table_0.media_type_id = media_types.media_type_id

MVCE confirmation

• [X] Minimal example
• [X] New issue

Anything else?

No response

[max-sixty]

Yes, it seems to retain the sorting even though it's not needed in the DISTINCT ON case... Thanks for the report.