Description:
When I created a table named t0 and inserted several rows of data into it.
CREATE TABLE t0(c0 INT, c1 INT, c2 INT) ;
INSERT INTO t0(c2) VALUES(1);
INSERT INTO t0(c0) VALUES(2), (NULL);
I created two indexes on this table.
CREATE INDEX i1 ON t0(c2 DESC, c0);
CREATE INDEX i2 ON t0(c0, c2, c1 DESC) ALGORITHM DEFAULT;
I anticipate that the query "SELECT ALL t0.c0 AS ref0 FROM t0 WHERE (t0.c0) || (t0.c2);" would return the values NULL and 2, but it sometimes results in an empty set instead. This issue can be consistently reproduced by executing "SET SESSION optimizer_switch = 'index_merge_union=off';".
SET SESSION optimizer_switch = 'index_merge_union=off';
SELECT ALL t0.c0 AS ref0 FROM t0 WHERE (t0.c0) || (t0.c2);
After turning off the index_merge_union, the mentioned query did not return any results, resulting in an empty set. This outcome contrasts with my expectations. Upon re-enabling the index_merge_union by executing “SET SESSION optimizer_switch = 'index_merge_union=on';”, the query was able to return the values NULL and 2 as anticipated.
SET SESSION optimizer_switch = 'index_merge_union=on';
SELECT ALL t0.c0 AS ref0 FROM t0 WHERE (t0.c0) || (t0.c2);
How to repeat:
CREATE TABLE t0(c0 INT, c1 INT, c2 INT) ;
INSERT INTO t0(c2) VALUES(1);
INSERT INTO t0(c0) VALUES(2), (NULL);
CREATE INDEX i1 ON t0(c2 DESC, c0);
CREATE INDEX i2 ON t0(c0, c2, c1 DESC) ALGORITHM DEFAULT;
-- I expect the query "SELECT ALL t0.c0 AS ref0 FROM t0 WHERE (t0.c0) || (t0.c2);"returns values NULL and 2, but sometimes results in empty set. Or with the query "SET SESSION optimizer_switch = 'index_merge_union=off';" you can stably repeat this problem
SET SESSION optimizer_switch = 'index_merge_union=off';
SELECT ALL t0.c0 AS ref0 FROM t0 WHERE (t0.c0) || (t0.c2);
SET SESSION optimizer_switch = 'index_merge_union=on';
SELECT ALL t0.c0 AS ref0 FROM t0 WHERE (t0.c0) || (t0.c2);
