jiangxt2 opened a new issue, #11972: URL: https://github.com/apache/gravitino/issues/11972
### Describe the bug When a ClickHouse table contains a Normal-type `PROJECTION` that includes an internal `ORDER BY` clause, loading the table through the ClickHouse catalog produces incorrect sort order metadata. The sort-order extraction from the table DDL mistakenly matches the PROJECTION's internal `ORDER BY` instead of the table's actual sort key. The returned sort orders contain garbled PROJECTION query fragments rather than the table's sort columns. Aggregate-type `PROJECTION`s (defined with `GROUP BY`, no `ORDER BY`) are not affected. ### Motivation This is a correctness bug — downstream tools that rely on Gravitino's sort order metadata for query planning or schema validation will make decisions based on wrong information. The issue is silent: no warning or error is produced. The incorrect sort orders look plausible at a glance (they contain valid column references), making the problem hard to detect. ### Describe the solution you'd like Ensure the sort-key extraction logic does not match into `PROJECTION` bodies, so that a PROJECTION's internal `ORDER BY` is not mistaken for the table-level sort key. ### Test plan | # | Scenario | Expected after fix | |---|----------|-------------------| | 1 | Normal PROJECTION with `ORDER BY` — loadTable | sortOrders = table's `ORDER BY` columns, not PROJECTION fragments | | 2 | Aggregate PROJECTION with `GROUP BY` (no `ORDER BY`) — loadTable | sortOrders = table's `ORDER BY` columns (already correct, must not regress) | | 3 | Multiple PROJECTIONs on same table | sortOrders = table's `ORDER BY` columns | | 4 | Table without PROJECTION — loadTable | sortOrders unchanged (no regression) | | 5 | Table with PROJECTION + existing INDEX | sortOrders + indexes both correct, no interference | | 6 | Normal PROJECTION at all positions (inline, ALTER-added) | consistent behavior regardless of DDL position | ### Additional context **Test results** (ClickHouse + Gravitino 1.2.0): | # | Scenario | Result before fix | |---|----------|:-----------------:| | 1 | Normal PROJECTION w/ `ORDER BY` → loadTable | ❌ sortOrders = `dt\n)...ORDER BY (id, dt)` (garbled) | | 2 | Aggregate PROJECTION (no `ORDER BY`) → loadTable | ✅ sortOrders = `(id, dt)` (correct) | | 3 | Multiple Normal PROJECTIONs → loadTable | ❌ all PROJECTION bodies consumed into sortOrders | | 4 | No PROJECTION → loadTable | ✅ normal (no regression risk confirmed) | | 5 | PROJECTION added via ALTER TABLE | ❌ same corruption as inline | | 6 | PROJECTION with SECONDARY INDEX on same table | ❌ sortOrders corrupted, INDEX unaffected | A follow-up feature request will track full PROJECTION definition support for round-trip preservation. I'd like to pick this up. Please let me know if you have any feedback. -- 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]
