Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
11.1.2, 10.5, 10.6, 10.9(EOL), 10.10(EOL), 10.11, 11.0(EOL)
-
None
-
Docker: mariadb:lates
Description
By changing the setting: not_null_range_scan, a query returns different results shown as follows.
CREATE TABLE t0(c0 CHAR UNIQUE);
INSERT INTO t0 VALUES (DEFAULT);
SET SESSION optimizer_switch = 'not_null_range_scan=off';
SELECT c0 FROM t0 WHERE (TRUE NOT IN (LOCATE(1, false, c0))); –
SET SESSION optimizer_switch = 'not_null_range_scan=on';
SELECT c0 FROM t0 WHERE (TRUE NOT IN (LOCATE(1, false, c0))); – {}
Attachments
Activity
Thank you! I repeated as described on 10.5-11.1:
--source include/have_innodb.inc
|
|
|
CREATE TABLE t0(c0 int, key (c0)) ENGINE=INNODB; |
INSERT INTO t0 VALUES (null); |
|
|
SET optimizer_switch = 'not_null_range_scan=off'; |
select c0 from t0 where 1 <> locate(1,0,c0); |
|
|
SET optimizer_switch = 'not_null_range_scan=on'; |
select c0 from t0 where 1 <> locate(1,0,c0); |
|
|
alter table t0 drop index c0; |
select c0 from t0 where 1 <> locate(1,0,c0); |
drop table t0; |
MariaDB [test]> CREATE TABLE t0(c0 int, key (c0)) ENGINE=INNODB;
|
Query OK, 0 rows affected (0,038 sec)
|
|
|
MariaDB [test]> INSERT INTO t0 VALUES (null);
|
Query OK, 1 row affected (0,002 sec)
|
|
|
MariaDB [test]> SET optimizer_switch = 'not_null_range_scan=off';
|
Query OK, 0 rows affected (0,000 sec)
|
|
|
MariaDB [test]> select c0 from t0 where 1 <> locate(1,0,c0);
|
+------+
|
| c0 |
|
+------+
|
| NULL |
|
+------+
|
1 row in set (0,000 sec)
|
|
|
MariaDB [test]> SET optimizer_switch = 'not_null_range_scan=on';
|
Query OK, 0 rows affected (0,000 sec)
|
|
|
MariaDB [test]> select c0 from t0 where 1 <> locate(1,0,c0);
|
Empty set (0,000 sec)
|
|
|
MariaDB [test]> explain extended select c0 from t0 where 1 <> locate(1,0,c0);
|
+------+-------------+-------+-------+---------------+------+---------+------+------+----------+--------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+-------------+-------+-------+---------------+------+---------+------+------+----------+--------------------------+
|
| 1 | SIMPLE | t0 | range | NULL | c0 | 5 | NULL | 1 | 100.00 | Using where; Using index |
|
+------+-------------+-------+-------+---------------+------+---------+------+------+----------+--------------------------+
|
1 row in set, 1 warning (0,001 sec)
|
|
|
Note (Code 1003): select `test`.`t0`.`c0` AS `c0` from `test`.`t0` where 1 <> locate(1,0,`test`.`t0`.`c0`)
|
|
|
MariaDB [test]> alter table t0 drop index c0;
|
Query OK, 0 rows affected (0,031 sec)
|
Records: 0 Duplicates: 0 Warnings: 0
|
|
|
MariaDB [test]> select c0 from t0 where 1 <> locate(1,0,c0);
|
+------+
|
| c0 |
|
+------+
|
| NULL |
|
+------+
|
1 row in set (0,001 sec)
|
Sorry to bother you.
May I know whether there is any problem with reproducing this issue?