Description:
The difference in results caused by enabling or disabling the optimizer's index_merge_union.
mysql> SELECT * FROM t4 WHERE ((!(t4.c1))) OR (t4.c4);
+------------+--------------+----------+-------------+-------------------------------------------------------------------------------+--------------+--------------+-------------+
| c0 | c1 | c2 | c3 | c4 | c5 | c8 | c9 |
+------------+--------------+----------+-------------+-------------------------------------------------------------------------------+--------------+--------------+-------------+
| 0.0460299 | 000000000000 | NULL | 0.26644 | 00000000000000000000000000000000000000000000000000000000000000000000000000000 | 0000.0490494 | 000000000000 | 0 |
| 0.671721 | NULL | 0.33204 | 0.388409 | 00000000000000000000000000000000000000000000000000000000000000000000000000037 | 000000000000 | 000000000000 | 1 |
| 0.891163 | NULL | 0.681915 | 0.593244 | 00000000000000000000000000000000000000000000000000000000000000000000000000043 | 000000.12953 | 0000.0536147 | -350709983 |
| 0.285865 | NULL | 0.198292 | 0.176646 | 00000000000000000000000000000000000000000000000000000000000000000000000000071 | 00000.168606 | 00000.484513 | 1 |
| 0.540036 | 00000.652305 | NULL | -1112920000 | 00000000000000000000000000000000000000000000000000000000000000000000000000097 | 00000.822917 | 00000.574236 | NULL |
| 617316000 | 00000.912814 | NULL | 0.480269 | 00000000000000000000000000000000000000000000000000000000000000000000000000111 | 00000.676357 | NULL | -1339644130 |
| -350710000 | NULL | 0.783885 | -1112920000 | 00000000000000000000000000000000000000000000000000000000000000000000000000121 | 00000.937973 | 00000.618306 | 538848999 |
| 0 | NULL | 0.286395 | 0.98276 | 00000000000000000000000000000000000000000000000000000000000000000000376062323 | NULL | NULL | NULL |
| 0.0443214 | NULL | 0.610812 | 1889910000 | 00000000000000000000000000000000000000000000000000000000000000000000427596789 | 000000000000 | 000000000000 | 1821043783 |
| 0.387238 | NULL | NULL | 0.0554166 | 00000000000000000000000000000000000000000000000000000000000000000000538848999 | 00000.910407 | 000000000000 | 0 |
| 0.607895 | 000000.32391 | NULL | 0.420912 | 00000000000000000000000000000000000000000000000000000000000000000002056716209 | 00000.417183 | 00000.307293 | 1 |
+------------+--------------+----------+-------------+-------------------------------------------------------------------------------+--------------+--------------+-------------+
11 rows in set, 1 warning (0.00 sec)
mysql> SET SESSION optimizer_switch = 'index_merge_union=off';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM t4 WHERE ((!(t4.c1))) OR (t4.c4);
Empty set, 1 warning (0.00 sec)
How to repeat:
CREATE TABLE `t4` (
`c0` float NOT NULL,
`c1` float unsigned zerofill DEFAULT NULL,
`c2` float DEFAULT NULL COMMENT 'asdf',
`c3` float DEFAULT NULL,
`c4` int(77) unsigned zerofill,
`c5` float unsigned zerofill,
`c8` float unsigned zerofill DEFAULT NULL,
`c9` decimal(10,0) DEFAULT NULL COMMENT 'asdf',
PRIMARY KEY (`c0`),
UNIQUE KEY `i3` (`c9`,`c5`),
UNIQUE KEY `i4` (`c1`,`c4`,`c2`,`c3` DESC),
UNIQUE KEY `i7` (`c3`,`c9`,`c4`) ,
UNIQUE KEY `i8` (`c3`,`c2`,`c4`) ,
KEY `i0` (`c5`),
KEY `i1` (`c1`,`c4`),
KEY `i2` (`c1`,`c5`,`c9`) USING BTREE,
KEY `i5` (`c5` DESC,`c8`,`c2` DESC,`c4`,`c0`),
KEY `i6` (`c1`),
KEY `i9` (`c4`,`c8` DESC,`c5`,`c9`,`c3` DESC,`c2` DESC,`c1`,`c0` DESC)
);
INSERT INTO `t4` VALUES (-132502000,NULL,NULL,NULL,NULL,NULL,NULL,NULL),(0.577554,NULL,0.348846,0.953205,00000000000000000000000000000000000000000000000000000000000000000000000000000,00000.819667,00000.882667,1),(0.316271,NULL,0.7881,0.484693,00000000000000000000000000000000000000000000000000000000000000000000000000000,NULL,00000.315816,0),(0.0460299,000000000000,NULL,0.26644,00000000000000000000000000000000000000000000000000000000000000000000000000000,0000.0490494,000000000000,0),(0.724993,NULL,0.600091,0.0900874,00000000000000000000000000000000000000000000000000000000000000000000000000000,00000.784983,000000000000,1),(0.671721,NULL,0.33204,0.388409,00000000000000000000000000000000000000000000000000000000000000000000000000037,000000000000,000000000000,1),(0.891163,NULL,0.681915,0.593244,00000000000000000000000000000000000000000000000000000000000000000000000000043,000000.12953,0000.0536147,-350709983),(0.285865,NULL,0.198292,0.176646,00000000000000000000000000000000000000000000000000000000000000000000000000071,00000.168606,00000.484513,1),(0.540036,00000.652305,NULL,-1112920000,00000000000000000000000000000000000000000000000000000000000000000000000000097,00000.822917,00000.574236,NULL),(617316000,00000.912814,NULL,0.480269,00000000000000000000000000000000000000000000000000000000000000000000000000111,00000.676357,NULL,-1339644130),(-350710000,NULL,0.783885,-1112920000,00000000000000000000000000000000000000000000000000000000000000000000000000121,00000.937973,00000.618306,538848999),(0,NULL,0.286395,0.98276,00000000000000000000000000000000000000000000000000000000000000000000376062323,NULL,NULL,NULL),(0.0443214,NULL,0.610812,1889910000,00000000000000000000000000000000000000000000000000000000000000000000427596789,000000000000,000000000000,1821043783),(0.387238,NULL,NULL,0.0554166,00000000000000000000000000000000000000000000000000000000000000000000538848999,00000.910407,000000000000,0),(0.607895,000000.32391,NULL,0.420912,00000000000000000000000000000000000000000000000000000000000000000002056716209,00000.417183,00000.307293,1);
SET SESSION optimizer_switch = 'index_merge_union=on';
SELECT * FROM t4 WHERE ((!(t4.c1))) OR (t4.c4);
SET SESSION optimizer_switch = 'index_merge_union=off';
SELECT * FROM t4 WHERE ((!(t4.c1))) OR (t4.c4);
Suggested fix:
I'm not sure if my issue is the same as Bug #115353.
© 2025, Oracle Corporation and/or its affiliates