append is buggy with a bit of logic in tables

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

[X] Minimal example
[X] New issue

Anything else?

No response

PRQL / prql

append is buggy with a bit of logic in tables #4947