apache / apache/cloudberry

[Bug] ORCA: "bogus index qualification" when a filter on an INCLUDE-only index column is pushed into the index qual

Open
#1,948 0 comments 0 reactions 0 assignees View on GitHub
type: Bug type: Orca
Dominant language
C
Stars
1.4k
Forks
247
Avg merge
4d 3h
Merged PRs (30d)
39

Description

### Apache Cloudberry version

main branch (recent build)

### What happened

With ORCA enabled, a query filtering on a column that is only an `INCLUDE` column of an index fails at execution time with an internal error:

```
ERROR: bogus index qualification (nodeIndexscan.c:1249)
```

ORCA generates an index scan on `bogus_i` and pushes `c1` into the index qualification (`EXPLAIN` shows `Index Cond: (c1 = true)`), but `c1` is a non-key `INCLUDE` column and `ExecIndexBuildScanKeys` rejects it. Line number is from current `main`. With `optimizer = off` the query returns the correct 2 rows.

Found by SQLancer.

### What you think should happen instead

The query should return the two rows where `c1` is true, with either optimizer.

### How to reproduce

```sql
CREATE TABLE bogus_t (c0 boolean, c1 boolean);
CREATE INDEX bogus_i ON bogus_t (c0) INCLUDE (c1);
INSERT INTO bogus_t VALUES (true, true), (false, true), (true, false);

SET optimizer = on;
SELECT * FROM bogus_t WHERE c1;
-- ERROR: bogus index qualification (nodeIndexscan.c:1249)

SET optimizer = off;
SELECT * FROM bogus_t WHERE c1;
-- 2 rows
```

### Operating System

Linux

### Anything else

Found while running SQLancer against Cloudberry `main` (3-segment demo cluster, `--enable-cassert`). See the proposal to run SQLancer continuously: https://github.com/apache/cloudberry/discussions/1952

### Are you willing to submit PR?

- [x] Yes, I am willing to submit a PR!

### Code of Conduct

- [x] I agree to follow this project's [Code of Conduct](https://www.apache.org/foundation/policies/conduct)

Contributor guide

Open the contributing guide

Research direction

Reproduce the failure with the SQL example, then read nodeIndexscan.c around line 1249 and compare the ORCA EXPLAIN output with optimizer disabled. The fix is complete when the INCLUDE-only-column filter no longer causes a bogus index qualification and the query returns the expected two rows with either optimizer setting.

Written by the indexing model from the issue text.

Assessment

Tech stack
c, postgresql, sql
Domain
databases
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Active
Clarity
Mostly clear
Newbie friendliness
52/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.