[YSQL] Invalid attnum for INTERSECT queries with range conditions on secondary columns with partial HASH indexes

[Navaratna-yb]

Jira Link: DB-12037

Description

When the INTERSECT query was executed with the following flags set in tserver - ysql_pg_conf_csv="enable_seqscan=false,enable_indexscan=false,yb_enable_bitmapscan=true", it aborted throwing the following error -

ERROR:  invalid attnum 15 for relation "*SELECT* 1"

Query used -

/*+ BitmapScan(bitmapscan_test_table_1) */ EXPLAIN
                 (ANALYZE, COSTS OFF, SUMMARY OFF)
                 SELECT id, name, age, details
                 FROM bitmapscan_test_table_1 t1 WHERE age < 25
                 INTERSECT
                 SELECT id, name, age, details
                 FROM bitmapscan_test_table_1 t2 WHERE val = 1;

DMLs and DDLS mentioned in the JIRA Ticket.

Issue Type

kind/bug

Warning: Please confirm that this issue does not contain any sensitive information

• [X] I confirm this issue does not contain any sensitive information.

[timothy-e]

I can reproduce this issue with bitmap scans disabled as well (other flags discourage their plan from being chosen, but yb_enable_bitmapscan forbids it from being chosen. So this shows that the problem exists outside of bitmap scans):

SET enable_seqscan = false;
SET enable_indexonlyscan = false;
SET yb_enable_bitmapscan = false;
SET enable_indexscan = true;
CREATE TABLE test_intersect_att(id text, age int);
CREATE INDEX tia_partial ON test_intersect_att(age ) WHERE age < 50;

EXPLAIN SELECT age FROM test_intersect_att WHERE age < 25
        INTERSECT SELECT age FROM test_intersect_att WHERE age < 25;
ERROR:  invalid attnum 2 for relation "*SELECT* 1"
\errverbose
ERROR:  XX000: invalid attnum 2 for relation "*SELECT* 1"
LOCATION:  get_variable, ruleutils.c:6903

If index scans are disabled as well, then the plan uses a sequential scan and succeeds.

SET enable_indexscan = true;
EXPLAIN (COSTS OFF) SELECT age FROM test_intersect_att WHERE age < 25
        INTERSECT SELECT age FROM test_intersect_att WHERE age < 25;
                              QUERY PLAN
-----------------------------------------------------------------------
 HashSetOp Intersect
   ->  Append
         ->  Subquery Scan on "*SELECT* 1"
               ->  Seq Scan on test_intersect_att
                     Storage Filter: (age < 25)
         ->  Subquery Scan on "*SELECT* 2"
               ->  Seq Scan on test_intersect_att test_intersect_att_1
                     Storage Filter: (age < 25)
(8 rows)

If the index is not partial, the query also succeeds (because the planner does not use the index scan, even when its enabled).

DROP INDEX tia_partial;
CREATE INDEX tia_full ON test_intersect_att(age );
SET enable_indexscan = true;
EXPLAIN (COSTS OFF) SELECT age FROM test_intersect_att WHERE age < 25
        INTERSECT SELECT age FROM test_intersect_att WHERE age < 25;
                              QUERY PLAN
-----------------------------------------------------------------------
 HashSetOp Intersect
   ->  Append
         ->  Subquery Scan on "*SELECT* 1"
               ->  Seq Scan on test_intersect_att
                     Storage Filter: (age < 25)
         ->  Subquery Scan on "*SELECT* 2"
               ->  Seq Scan on test_intersect_att test_intersect_att_1
                     Storage Filter: (age < 25)
(8 rows)

if the index is an ascending index, it also succeeds:

DROP INDEX tia_full_asc;
CREATE INDEX tia_full_asc ON test_intersect_att(age ASC);
EXPLAIN (COSTS OFF) SELECT age FROM test_intersect_att WHERE age < 25
        INTERSECT SELECT age FROM test_intersect_att WHERE age < 25;
                                         QUERY PLAN
--------------------------------------------------------------------------------------------
 HashSetOp Intersect
   ->  Append
         ->  Subquery Scan on "*SELECT* 1"
               ->  Index Scan using tia_full_asc on test_intersect_att
                     Index Cond: (age < 25)
         ->  Subquery Scan on "*SELECT* 2"
               ->  Index Scan using tia_full_asc on test_intersect_att test_intersect_att_1
                     Index Cond: (age < 25)
(8 rows)

If the table is created with only the age column, it succeeds.

It seems that the problem exists only for queries with a range condition on a partial HASH index on a column that's not first.

The flags from the original issue enable_seqscan=false,enable_indexscan=false,yb_enable_bitmapscan=true cause the issue because bitmap scans rely on index scans to work. So the issue is an index scan issue that bitmap scans inherited.

[andrei-mart]

Looks like a duplicate of #22533

yugabyte=# EXPLAIN (COSTS OFF) SELECT age FROM test_intersect_att WHERE age < 25 INTERSECT SELECT age FROM test_intersect_att WHERE age < 25;
                                        QUERY PLAN                                         
-------------------------------------------------------------------------------------------
 HashSetOp Intersect
   ->  Append
         ->  Subquery Scan on "*SELECT* 1"
               ->  Index Scan using tia_partial on test_intersect_att
                     Storage Index Filter: (age < 25)
         ->  Subquery Scan on "*SELECT* 2"
               ->  Index Scan using tia_partial on test_intersect_att test_intersect_att_1
                     Storage Index Filter: (age < 25)
(8 rows)

There are Index Scans with a Storage Index Filter in a subquery.