`group` & `select` generates additional field "_expr_0"

[echou]

from `db.t`
derive d = create_time + 1
group d (
  aggregate [
    n1 = (a | sum),
  ]
)
sort d
select [ d1 = d, n1 ]

generates the result:

SELECT
  create_time + 1 AS d1,
  SUM(a) AS n1,
  create_time + 1 AS _expr_0
FROM
  db.t
GROUP BY
  create_time + 1
ORDER BY
  _expr_0

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

1. if select with group key, the key is added. (correct)
2. if select without group key, _expr_0 is added. (correct. but not good)
3. if select with group key alias, _expr_0 is added. (wrong)

[echou]

it seems the sort statement cause the bug.

[max-sixty]

Thanks a lot for the excellent report @echou

[max-sixty]

Even smaller example:

from foo
derive d = x + 1
sort d
select [col]

SELECT
  col,
  x + 1 AS _expr_0
FROM
  foo
ORDER BY
  _expr_0

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

We're outputting two columns, when we should only be outputting col

[aljazerzen]

Interesting.

The strange alias seems to be caused by d not being selected - it should not even be in the SELECT.

[max-sixty]

I haven't got a fix yet but writing down what I have midway through:

Based on this query: https://github.com/prql/prql/blob/5640989058b213af0d453034e5c7033f385cca3b/prql-compiler/src/tests/test.rs#L3396

from foo
derive d = x
sort d
select [col]

...which currently generates:

SELECT
  col,
  x AS _expr_0
FROM
  foo
ORDER BY
  _expr_0

At https://github.com/max-sixty/prql/blob/5640989058b213af0d453034e5c7033f385cca3b/prql-compiler/src/sql/gen_query.rs#L254, we have two columns selected:

[prql-compiler/src/sql/gen_query.rs:254] pipeline.clone() = [
    Super(
        Select(
            [
                column-1,
                column-3,
            ],
        ),
    ),
    Super(
        Compute(
            Compute {
                id: column-3,
                expr: Expr {
                    kind: ColumnRef(
                        column-0,
                    ),
                    span: Some(
                        span-chars-29-30,
                    ),
                },
                window: None,
                is_aggregation: false,
            },
        ),
    ),
    Super(
        Sort(
            [
                ColumnSort {
                    direction: Asc,
                    column: column-3,
                },
            ],
        ),
    ),
]

...and I'm guessing there should only be one there.

(but one point — if I remove the final select [col], then there are three columns selected. Probably the context is shared between the pipeline Query struct and the ctx, such that it's not selecting three columns in the final output?)

---

Overall I'm going fairly slowly, adding dbg! statements, having a test fail, and then looking at the outputs, trying to think about they should be.

I'm thinking about ways to make it more observable — maybe we need tests around some of these inner functions to give folks a better starting point — "here's a test around a single function which demonstrates the problem" would make it trivial in comparison — rather than just the outer PRQL-> SQL. Or maybe I just need to understand the code better...

[max-sixty]

One small point: the RQ here is very nice IMO; it was obvious to me that this wasn't the problem very quickly:

def:
  version: null
  other: {}
tables:
- id: 0
  name: null
  relation:
    kind: !ExternRef foo
    columns:
    - !Single x
    - !Single col
    - Wildcard
relation:
  kind: !Pipeline
  - !From
    source: 0
    columns:
    - - !Single x
      - 0
    - - !Single col
      - 1
    - - Wildcard
      - 2
    name: foo
  - !Compute
    id: 3
    expr:
      kind: !ColumnRef 0
      span:
        start: 20
        end: 21
  - !Sort
    - direction: Asc
      column: 3
  - !Select
    - 1
  - !Select
    - 1
  columns:
  - !Single col

(it has a duplicated select at the end, but that shouldn't matter)