append doesn't check column, column order matters

[cottrell]

What happened?

I'm not sure if this is intentional or not.

PRQL input

from invoices
group billing_country (
aggregate {
  a = count_distinct billing_city
  }
)
into A

from invoices
aggregate {
a = count_distinct billing_city
}
derive {
billing_country = '*'
}
select {a, billing_country}
into B

from A
append B

SQL output

WITH "A" AS (
  SELECT
    billing_country,
    COUNT(DISTINCT billing_city) AS a
  FROM
    invoices
  GROUP BY
    billing_country
),
table_0 AS (
  SELECT
    COUNT(DISTINCT billing_city) AS a
  FROM
    invoices
),
"B" AS (
  SELECT
    a,
    '*' AS billing_country
  FROM
    table_0
)
SELECT
  billing_country,
  a
FROM
  "A"
UNION
ALL
SELECT
  *
FROM
  "B"

-- Generated by PRQL compiler version:0.12.2 (https://prql-lang.org)

Expected SQL output

No response

MVCE confirmation

• [ ] Minimal example
• [ ] New issue

Anything else?

It works if you manually align the columns with a select. Might be intended not sure.

billing_country a
Germany 3
Norway  1
Belgium 1
Canada  8
USA 12
France  4
Ireland 1
United Kingdom  2
Australia   1
Chile   1
India   2
Brazil  4
Portugal    2
Netherlands 1
Spain   1
Sweden  1
Czech Republic  1
Finland 1
Denmark 1
Italy   1
Poland  1
Austria 1
Hungary 1
Argentina   1
53  *

[max-sixty]

Sorry for being slow, but could you explain in a bit more detail what the issue is?

(An expected SQL output would be great)

[cottrell]

Sorry for being slow, but could you explain in a bit more detail what the issue is?

(An expected SQL output would be great)

If you look at the last row it is "53 " and it shoudl be " 53". I should have chosen a better text than "*" I guess as it doesn't jump out.

If it's possible, prql should probably inspect the columns and align them but if the column names are no available (select * clause) then it is not possible.

I think the simplest solution would be to simply warn in the docs for append that the user must manually align the tables.

WITH "A" AS (
  SELECT
    billing_country,
    COUNT(DISTINCT billing_city) AS a
  FROM
    invoices
  GROUP BY
    billing_country
),
table_0 AS (
  SELECT
    COUNT(DISTINCT billing_city) AS a
  FROM
    invoices
),
"B" AS (
  SELECT
    '*' AS billing_country, -- Ensure this column matches the first query
    a
  FROM
    table_0
)
SELECT
  billing_country,
  a
FROM
  "A"
UNION ALL
SELECT
  billing_country,
  a
FROM
  "B";