append is buggy with a bit of logic in tables

[PrettyWood]

What happened?

Still working on a query builder to convert transformation steps into SQL via PRQL
We have an issue with append that doesn't seem to work well with basic tables
I'm more than happy to help on debugging this

PRQL input

from `albums`
select { `album_id`, `title` }
sort {+`album_id`}
take 2
append (
  from `albums`
  select { `album_id`, `title` }
  sort {-`album_id`}
  take 2
)

SQL output

fails

Expected SQL output

WITH table_0 AS (
  SELECT
    album_id,
    title
  FROM
    albums
  ORDER BY
    album_id DESC
  LIMIT
    2
)
SELECT
  *
FROM
  (
    SELECT
      album_id,
      title
    FROM
      albums
    ORDER BY
      album_id
    LIMIT
      2
  ) AS table_1
UNION
ALL
SELECT
  *
FROM
  table_0

MVCE confirmation

• Minimal example
• New issue

Anything else?

No response

[PrettyWood]

It may be the same bug as #4063. I'll start digging

[richb-hanover]

Interesting serendipity (of timing). Just this morning, I was looking at this and #5055 which has a similar pattern. Rewriting the original query as follows works as expected in the current Playground.

I wonder if the fix from @lukapeshke will address #5055 as well...

# ==== from #4947 - rewritten
let a = (
from `albums`
select { `album_id`, `title` }
sort {+`album_id`}
take 2
)

let b = (
  from `albums`
  select { `album_id`, `title` }
  sort {-`album_id`}
  take 2
)
from a
append b