Details
Major Description
Summary
I observed inconsistent results for the following queries when not_null_range_scan is enabled and a DESC index exists on the column.
Reproduction Steps
CREATE TABLE t0(c0 REAL); |
|
|
CREATE INDEX ic0 ON t0(c0 DESC); |
|
|
INSERT INTO t0 VALUES (1); |
INSERT INTO t0 VALUES (NULL); |
SET SESSION optimizer_switch = 'not_null_range_scan=on'; |
SELECT t0.c0 FROM t0 WHERE (false OR ((t0.c0 IS FALSE) IS NOT NULL));-- cardinality: 1 |
SELECT ref0 FROM (SELECT t0.c0 AS ref0, (false OR ((t0.c0 IS FALSE) IS NOT NULL)) AS ref1 FROM t0) AS s WHERE ref1;-- cardinality: 2 |
Observed Behavior
mysql> SELECT t0.c0 FROM t0 WHERE (false OR ((t0.c0 IS FALSE) IS NOT NULL));-- cardinality: 1 |
+------+ |
| c0 |
|
+------+ |
| 1 |
|
+------+ |
1 row in set (0.00 sec) |
|
|
mysql> SELECT ref0 FROM (SELECT t0.c0 AS ref0, (false OR ((t0.c0 IS FALSE) IS NOT NULL)) AS ref1 FROM t0) AS s WHERE ref1;-- cardinality: 2 |
+------+ |
| ref0 |
|
+------+ |
| 1 |
|
| NULL | |
+------+ |
2 rows in set (0.00 sec) |
Expected Behavior (Consistent with MySQL v9.6.0 and TiDB v8.5.5):
mysql> SELECT t0.c0 FROM t0 WHERE (false OR ((t0.c0 IS FALSE) IS NOT NULL));-- cardinality: 1 |
+------+ |
| c0 |
|
+------+ |
| 1 |
|
| NULL | |
+------+ |
2 rows in set (0.00 sec) |
|
|
mysql> SELECT ref0 FROM (SELECT t0.c0 AS ref0, (false OR ((t0.c0 IS FALSE) IS NOT NULL)) AS ref1 FROM t0) AS s WHERE ref1;-- cardinality: 2 |
+------+ |
| ref0 |
|
+------+ |
| 1 |
|
| NULL | |
+------+ |
2 rows in set (0.00 sec) |