Details
-
Technical task
-
Status: Closed (View Workflow)
-
Minor
-
Resolution: Fixed
-
None
-
None
-
None
Description
CREATE TABLE t1 (pk INT PRIMARY KEY, a INT, KEY(a)) ENGINE=LevelDB; |
INSERT INTO t1 VALUES (1, NULL),(2, 8); |
SELECT ( 3 ) NOT IN ( SELECT a FROM t1 ); |
( 3 ) NOT IN ( SELECT a FROM t1 ) |
1
|
EXPLAIN EXTENDED
|
SELECT ( 3 ) IN ( SELECT a FROM t1 ); |
id select_type table type possible_keys key key_len ref rows filtered Extra |
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used |
2 DEPENDENT SUBQUERY t1 index_subquery a a 5 const 20 100.00 Using index |
Warnings:
|
Note 1003 /* select#1 */ select <in_optimizer>(3,<exists>(<index_lookup>(<cache>(3) in t1 on a checking NULL having <is_not_null_test>(`test`.`t1`.`a`)))) AS `( 3 ) IN ( SELECT a FROM t1 )` |
Same query without the key on `a` returns NULL. Same query with the key and InnoDB table also returns NULL.
Test case:
CREATE TABLE t1 (pk INT PRIMARY KEY, a INT, KEY(a)) ENGINE=LevelDB;
|
INSERT INTO t1 VALUES (1, NULL),(2, 8);
|
SELECT ( 3 ) NOT IN ( SELECT a FROM t1 );
|
Version info:
revision-id: psergey@askmonty.org-20130125181032-kuuhprzmkes6p9u0
|
revno: 4517
|
branch-nick: mysql-5.6-leveldb
|