lgbo-ustc opened a new issue, #13234:
URL: https://github.com/apache/gluten/issues/13234
### Problem
For ClickHouse backend, the window-to-aggregate-topk optimization can be
applied to a `row_number` top-k query where the partition key and order key are
the same expression, for example:
```sql
WITH ranked AS (
SELECT
l_orderkey,
l_returnflag,
ROW_NUMBER() OVER (
PARTITION BY l_returnflag
ORDER BY l_returnflag DESC
) AS rn
FROM lineitem
WHERE l_orderkey > 1000
)
SELECT
l_returnflag,
l_orderkey,
rn
FROM ranked
WHERE rn <= 3
ORDER BY l_returnflag DESC
```
When the query is planned through the aggregate top-k path, native execution
may fail with a cast error on the generated row number column, e.g.:
```text
Cannot convert NULL value to non-Nullable type: while converting source
column `rn#...` to destination column `rn#...`: while executing 'FUNCTION
_CAST(rn#... :: 2, Int32 :: 4) -> _CAST(rn#..., Int32) Int32 : 3'
```
A related manifestation is parsing a partition key value as `Int32` when the
row number output position is confused with a string partition/order column.
### Root cause
For a window definition like:
```sql
PARTITION BY l_returnflag
ORDER BY l_returnflag DESC
```
the partition key and sort key are identical. This is also a
non-deterministic top-k query because the order key does not provide a stable
order within each partition.
The aggregate top-k optimization is not suitable for this shape. It can
produce an output layout where the generated `rn` column is treated
inconsistently with the Spark schema.
### Expected behavior
The ClickHouse backend should not apply the window-to-aggregate-topk
optimization when partition keys and order keys are exactly the same
expressions. It should keep the regular window/filter path for this case.
### Reproduction
Force the aggregate top-k rule to be considered:
```sql
SET spark.sql.optimizer.windowGroupLimitThreshold=-1;
SET
spark.gluten.sql.columnar.backend.ch.runtime_settings.enable_window_group_limit_to_aggregate=true;
SET
spark.gluten.sql.columnar.backend.ch.runtime_settings.window.aggregate_topk_high_cardinality_threshold=2.0;
```
Then run the SQL above on the TPCH `lineitem` table.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
