CubeSQL sort pushdown turns grouped integer literals into positional ORDER BY references

[jwils]

Failed SQL

When a grouped integer literal is aliased and the query sorts by that alias, CubeSQL sort pushdown can replace the alias with the literal itself.

Minimal reproduction:

WITH with_rate AS (
  SELECT
    customer_gender,
    notes,
    SUM(taxful_total_price) AS hourly_rate
  FROM KibanaSampleDataEcommerce
  GROUP BY 1, 2
)
SELECT
  customer_gender,
  19 AS hour_slot,
  8 AS minute_slot,
  SUM(hourly_rate) AS total_price
FROM with_rate
GROUP BY 1, 2, 3
ORDER BY
  hour_slot ASC,
  minute_slot DESC,
  customer_gender ASC
LIMIT 50000;

The pushed-down SQL contains the equivalent of:

SELECT
  ...,
  19 AS "<generated_hour_slot_alias>",
  8 AS "<generated_minute_slot_alias>",
  ...
ORDER BY 19 ASC, 8 DESC, ...

Many SQL dialects interpret bare integers in ORDER BY as positional references into the select list.

When the integer exceeds the select-list width, the generated query fails. When it refers to a valid position, the query can silently sort by an unrelated output column.

Expected behavior

CubeSQL should preserve or restore the generated aliases:

ORDER BY
  "<generated_hour_slot_alias>" ASC,
  "<generated_minute_slot_alias>" DESC,
  ...

Sort direction, null ordering, and unrelated sort expressions should remain unchanged.

Logical Plan

There is no Can't rewrite plan error. The rewrite completes, but sort pushdown changes the aliased sort expressions into integer literal expressions while the grouped projection retains generated aliases. SQL generation then renders those literals directly in the ORDER BY clause.

Tool

The query above is a minimal direct SQL API reproduction. Queries generated by other tools can also produce this grouped-literal shape.

Version

Present on master, including v1.7.23 / 8b6e94c.

Additional context

A fix and regression test are proposed in #11496.