roseduan opened a new issue, #2026:
URL: https://github.com/apache/cloudberry/issues/2026
### Apache Cloudberry version
_No response_
### What happened
With GPORCA (`optimizer = on`), a qual on a btree index's `INCLUDE`
(non-key) column can be pushed into the `Index Cond`. `INCLUDE` columns are
payload only and are not searchable, so the executor
rejects the scan keys at runtime:
```
ERROR: btree index keys must be ordered by attribute (nbtutils.c:799)
```
The Postgres planner (`optimizer = off`) is not affected. The table does
not need to contain any rows.
### What you think should happen instead
_No response_
### How to reproduce
```sql
CREATE TABLE c2t(a int, b int) DISTRIBUTED BY (a);
CREATE INDEX ci1 ON c2t USING btree (a) INCLUDE (b);
SET optimizer = on;
SELECT a, b FROM c2t WHERE b IS NULL;
-- ERROR: btree index keys must be ordered by attribute (nbtutils.c:799)
(seg2 slice1 ...)
```
The plan puts the `INCLUDE` column `b` into the `Index Cond`:
```
EXPLAIN (COSTS OFF) SELECT a, b FROM c2t WHERE b IS NULL;
Gather Motion 3:1 (slice1; segments: 3)
-> Index Scan using ci1 on c2t
Index Cond: (b IS NULL)
Optimizer: GPORCA
```
Same error with `GROUP BY` and with an index whose `INCLUDE` list repeats
a key column:
```sql
CREATE TABLE c1t(a int, b int) DISTRIBUTED BY (a);
CREATE INDEX ci0 ON c1t USING btree (a) INCLUDE (a, b);
SET optimizer = on;
SELECT a, b FROM c1t WHERE b IS NULL GROUP BY a, b;
-- ERROR: btree index keys must be ordered by attribute (nbtutils.c:799)
```
With `SET optimizer = off;` all of the above return the correct rows.
### Operating System
any
### Anything else
_No response_
### Are you willing to submit PR?
- [ ] Yes, I am willing to submit a PR!
### Code of Conduct
- [x] I agree to follow this project's [Code of
Conduct](https://github.com/apache/cloudberry/blob/main/CODE_OF_CONDUCT.md).
--
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]
