1# 2# Bug#27182010 SUBQUERY INCORRECTLY SHOWS DUPLICATE VALUES ON SUBQUERIES 3# 4CREATE TABLE p (Id INT,PRIMARY KEY (Id)); 5INSERT INTO p VALUES (1); 6# Test UNIQUE KEY with NULL values 7CREATE TABLE s (Id INT, u INT, UNIQUE KEY o(Id, u) ); 8INSERT INTO s VALUES (1, NULL),(1, NULL); 9EXPLAIN SELECT p.Id FROM (p) WHERE p.Id IN ( 10SELECT s.Id FROM s WHERE Id=1 AND u IS NULL)ORDER BY Id DESC; 11id select_type table partitions type possible_keys key key_len ref rows filtered Extra 121 SIMPLE p NULL const PRIMARY PRIMARY 4 const 1 100.00 Using index 131 SIMPLE s NULL ref o o 10 const,const 2 100.00 Using where; Using index; FirstMatch(p) 14Warnings: 15Note 1003 /* select#1 */ select '1' AS `Id` from `test`.`p` semi join (`test`.`s`) where ((`test`.`s`.`Id` = 1) and isnull(`test`.`s`.`u`)) order by '1' desc 16EXPLAIN SELECT p.Id FROM (p) WHERE p.Id IN ( 17SELECT s.Id FROM s WHERE Id=1 AND u IS NOT NULL) ORDER BY Id DESC; 18id select_type table partitions type possible_keys key key_len ref rows filtered Extra 191 SIMPLE p NULL const PRIMARY PRIMARY 4 const 1 100.00 Using index 201 SIMPLE s NULL range o o 10 NULL 1 100.00 Using where; Using index; FirstMatch(p) 21Warnings: 22Note 1003 /* select#1 */ select '1' AS `Id` from `test`.`p` semi join (`test`.`s`) where ((`test`.`s`.`Id` = 1) and (`test`.`s`.`u` is not null)) order by '1' desc 23SELECT p.Id FROM (p) WHERE p.Id IN ( 24SELECT s.Id FROM s WHERE Id=1 AND u IS NULL)ORDER BY Id DESC; 25Id 261 27SELECT p.Id FROM (p) WHERE p.Id IN ( 28SELECT s.Id FROM s WHERE Id=1 AND u IS NOT NULL) ORDER BY Id DESC; 29Id 30# UNIQUE KEY without NULL values 31CREATE TABLE s1 (Id INT, u INT, UNIQUE KEY o(Id, u) ); 32INSERT INTO s1 VALUES (1, 2),(1, 3); 33EXPLAIN SELECT p.Id FROM (p) WHERE p.Id IN ( 34SELECT s1.Id FROM s1 WHERE Id=1 AND u IS NOT NULL) ORDER BY Id DESC; 35id select_type table partitions type possible_keys key key_len ref rows filtered Extra 361 SIMPLE p NULL const PRIMARY PRIMARY 4 const 1 100.00 Using index 371 SIMPLE s1 NULL index o o 10 NULL 2 100.00 Using where; Using index; FirstMatch(p) 38Warnings: 39Note 1003 /* select#1 */ select '1' AS `Id` from `test`.`p` semi join (`test`.`s1`) where ((`test`.`s1`.`Id` = 1) and (`test`.`s1`.`u` is not null)) order by '1' desc 40EXPLAIN SELECT p.Id FROM (p) WHERE p.Id IN ( 41SELECT s1.Id FROM s1 WHERE Id=1 AND u != 1) ORDER BY Id DESC; 42id select_type table partitions type possible_keys key key_len ref rows filtered Extra 431 SIMPLE p NULL const PRIMARY PRIMARY 4 const 1 100.00 Using index 441 SIMPLE s1 NULL index o o 10 NULL 2 100.00 Using where; Using index; FirstMatch(p) 45Warnings: 46Note 1003 /* select#1 */ select '1' AS `Id` from `test`.`p` semi join (`test`.`s1`) where ((`test`.`s1`.`Id` = 1) and (`test`.`s1`.`u` <> 1)) order by '1' desc 47SELECT p.Id FROM (p) WHERE p.Id IN ( 48SELECT s1.Id FROM s1 WHERE Id=1 AND u IS NOT NULL) ORDER BY Id DESC; 49Id 501 51SELECT p.Id FROM (p) WHERE p.Id IN ( 52SELECT s1.Id FROM s1 WHERE Id=1 AND u != 1) ORDER BY Id DESC; 53Id 541 55# NON UNIQUE KEY Scenario 56CREATE TABLE s2 (Id INT, u INT, KEY o(Id, u) ); 57INSERT INTO s2 VALUES (1, NULL),(1, NULL); 58#UNIQUE KEY with NON NULL FIELDS 59CREATE TABLE s3 (Id INT NOT NULL, u INT NOT NULL, UNIQUE KEY o(Id, u)); 60INSERT INTO s3 VALUES (1, 2),(1, 3); 61EXPLAIN SELECT p.Id FROM (p) WHERE p.Id IN ( 62SELECT s.Id FROM s2 s WHERE Id=1 AND u IS NULL) ORDER BY Id DESC; 63id select_type table partitions type possible_keys key key_len ref rows filtered Extra 641 SIMPLE p NULL const PRIMARY PRIMARY 4 const 1 100.00 Using index 651 SIMPLE s NULL ref o o 10 const,const 2 100.00 Using where; Using index; FirstMatch(p) 66Warnings: 67Note 1003 /* select#1 */ select '1' AS `Id` from `test`.`p` semi join (`test`.`s2` `s`) where ((`test`.`s`.`Id` = 1) and isnull(`test`.`s`.`u`)) order by '1' desc 68EXPLAIN SELECT p.Id FROM (p) WHERE p.Id IN ( 69SELECT s.Id FROM s3 s WHERE Id=1 AND u IS NOT NULL) 70ORDER BY Id DESC; 71id select_type table partitions type possible_keys key key_len ref rows filtered Extra 721 SIMPLE p NULL const PRIMARY PRIMARY 4 const 1 100.00 Using index 731 SIMPLE s NULL ref o o 4 const 2 50.00 Using where; Using index; FirstMatch(p) 74Warnings: 75Note 1003 /* select#1 */ select '1' AS `Id` from `test`.`p` semi join (`test`.`s3` `s`) where ((`test`.`s`.`Id` = 1) and (`test`.`s`.`u` is not null)) order by '1' desc 76SELECT p.Id FROM (p) WHERE p.Id IN ( 77SELECT s.Id FROM s2 s WHERE Id=1 AND u IS NULL) ORDER BY Id DESC; 78Id 791 80SELECT p.Id FROM (p) WHERE p.Id IN ( 81SELECT s.Id FROM s3 s WHERE Id=1 AND u IS NOT NULL) 82ORDER BY Id DESC; 83Id 841 85DROP TABLE p, s, s1, s2, s3; 86