diff options
Diffstat (limited to 'mysql-test/main/null_key.result')
| -rw-r--r-- | mysql-test/main/null_key.result | 478 |
1 files changed, 478 insertions, 0 deletions
diff --git a/mysql-test/main/null_key.result b/mysql-test/main/null_key.result new file mode 100644 index 00000000..c48382e7 --- /dev/null +++ b/mysql-test/main/null_key.result @@ -0,0 +1,478 @@ +drop table if exists t1,t2; +set @@note_verbosity=replace(@@note_verbosity,"explain",""); +create table t1 (a int, b int not null,unique key (a,b),index(b)) engine=myisam; +insert ignore into t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(null,7),(9,9),(8,8),(7,7),(null,9),(null,9),(6,6); +Warnings: +Warning 1062 Duplicate entry '6-6' for key 'a' +explain select * from t1 where a is null; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref a a 5 const 3 Using where; Using index +explain select * from t1 where a is null and b = 2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref a,b a 9 const,const 1 Using where; Using index +explain select * from t1 where a is null and b = 7; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref a,b a 9 const,const 1 Using where; Using index +explain select * from t1 where a=2 and b = 2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 const a,b a 9 const,const 1 Using index +explain select * from t1 where a<=>b limit 2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index NULL a 9 NULL 12 Using where; Using index +explain select * from t1 where (a is null or a > 0 and a < 2) and b < 5 limit 3; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range a,b a 9 NULL 2 Using where; Using index +explain select * from t1 where (a is null or a = 7) and b=7; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref_or_null a,b a 9 const,const 2 Using where; Using index +explain select * from t1 where (a is null or a = 7) and b=7 order by a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref_or_null a,b a 9 const,const 2 Using where; Using index; Using filesort +explain select * from t1 where (a is null and b>a) or a is null and b=7 limit 2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref a,b a 5 const 3 Using where; Using index +explain select * from t1 where a is null and b=9 or a is null and b=7 limit 3; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range a,b a 9 NULL 3 Using where; Using index +explain select * from t1 where a > 1 and a < 3 limit 1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range a a 5 NULL 1 Using where; Using index +explain select * from t1 where a > 8 and a < 9; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range a a 5 NULL 1 Using where; Using index +select * from t1 where a is null; +a b +NULL 7 +NULL 9 +NULL 9 +select * from t1 where a is null and b = 7; +a b +NULL 7 +select * from t1 where a<=>b limit 2; +a b +1 1 +2 2 +select * from t1 where (a is null or a > 0 and a < 3) and b < 5 limit 3; +a b +1 1 +2 2 +select * from t1 where (a is null or a > 0 and a < 3) and b > 7 limit 3; +a b +NULL 9 +NULL 9 +select * from t1 where (a is null or a = 7) and b=7; +a b +7 7 +NULL 7 +select * from t1 where a is null and b=9 or a is null and b=7 limit 3; +a b +NULL 7 +NULL 9 +NULL 9 +select * from t1 where a > 1 and a < 3 limit 1; +a b +2 2 +select * from t1 where a > 8 and a < 9; +a b +create table t2 like t1; +insert into t2 select * from t1; +alter table t1 modify b blob not null, add c int not null, drop key a, add unique key (a,b(20),c), drop key b, add key (b(10)); +explain select * from t1 where a is null and b = 2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref a,b a 5 const 3 Using where +explain select * from t1 where a is null and b = 2 and c=0; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref a,b a 5 const 3 Using where +explain select * from t1 where a is null and b = 7 and c=0; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref a,b a 5 const 3 Using where +explain select * from t1 where a=2 and b = 2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref a,b a 5 const 1 Using where +explain select * from t1 where a<=>b limit 2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 12 Using where +explain select * from t1 where (a is null or a > 0 and a < 3) and b < 5 and c=0 limit 3; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range a,b a 5 NULL 5 Using where +explain select * from t1 where (a is null or a = 7) and b=7 and c=0; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref_or_null a,b a 5 const 4 Using where +explain select * from t1 where (a is null and b>a) or a is null and b=7 limit 2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref a,b a 5 const 3 Using where +explain select * from t1 where a is null and b=9 or a is null and b=7 limit 3; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref a,b a 5 const 3 Using where +explain select * from t1 where a > 1 and a < 3 limit 1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range a a 5 NULL 1 Using where +explain select * from t1 where a is null and b=7 or a > 1 and a < 3 limit 1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range a,b a 5 NULL 4 Using where +explain select * from t1 where a > 8 and a < 9; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range a a 5 NULL 1 Using where +explain select * from t1 where b like "6%"; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range b b 12 NULL 1 Using where +select * from t1 where a is null; +a b c +NULL 7 0 +NULL 9 0 +NULL 9 0 +select * from t1 where a is null and b = 7 and c=0; +a b c +NULL 7 0 +select * from t1 where a<=>b limit 2; +a b c +1 1 0 +2 2 0 +select * from t1 where (a is null or a > 0 and a < 3) and b < 5 limit 3; +a b c +1 1 0 +2 2 0 +select * from t1 where (a is null or a > 0 and a < 3) and b > 7 limit 3; +a b c +NULL 9 0 +NULL 9 0 +select * from t1 where (a is null or a = 7) and b=7 and c=0; +a b c +7 7 0 +NULL 7 0 +select * from t1 where a is null and b=9 or a is null and b=7 limit 3; +a b c +NULL 7 0 +NULL 9 0 +NULL 9 0 +select * from t1 where b like "6%"; +a b c +6 6 0 +drop table t1; +rename table t2 to t1; +alter table t1 modify b int null; +insert into t1 values (7,null), (8,null), (8,7); +explain select * from t1 where a = 7 and (b=7 or b is null); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref_or_null a,b a 10 const,const 2 Using where; Using index +select * from t1 where a = 7 and (b=7 or b is null); +a b +7 7 +7 NULL +explain select * from t1 where (a = 7 or a is null) and (b=7 or b is null); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref_or_null a,b a 5 const 5 Using where; Using index +select * from t1 where (a = 7 or a is null) and (b=7 or b is null); +a b +7 NULL +7 7 +NULL 7 +explain select * from t1 where (a = 7 or a is null) and (a = 7 or a is null); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref_or_null a a 5 const 5 Using where; Using index +select * from t1 where (a = 7 or a is null) and (a = 7 or a is null); +a b +7 NULL +7 7 +NULL 7 +NULL 9 +NULL 9 +create table t2 (a int); +insert into t2 values (7),(8); +explain select * from t2 straight_join t1 where t1.a=t2.a and b is null; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where +1 SIMPLE t1 ref a,b a 10 test.t2.a,const 2 Using where; Using index +drop index b on t1; +explain select * from t2,t1 where t1.a=t2.a and b is null; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where +1 SIMPLE t1 ref a a 10 test.t2.a,const 2 Using where; Using index +select * from t2,t1 where t1.a=t2.a and b is null; +a a b +7 7 NULL +8 8 NULL +explain select * from t2,t1 where t1.a=t2.a and (b= 7 or b is null); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where +1 SIMPLE t1 ref_or_null a a 10 test.t2.a,const 5 Using where; Using index +select * from t2,t1 where t1.a=t2.a and (b= 7 or b is null); +a a b +7 7 7 +7 7 NULL +8 8 7 +8 8 NULL +explain select * from t2,t1 where (t1.a=t2.a or t1.a is null) and b= 7; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 +1 SIMPLE t1 ref_or_null a a 10 test.t2.a,const 5 Using where; Using index +select * from t2,t1 where (t1.a=t2.a or t1.a is null) and b= 7; +a a b +7 7 7 +7 NULL 7 +8 8 7 +8 NULL 7 +explain select * from t2,t1 where (t1.a=t2.a or t1.a is null) and (b= 7 or b is null); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 +1 SIMPLE t1 ref_or_null a a 5 test.t2.a 5 Using where; Using index +select * from t2,t1 where (t1.a=t2.a or t1.a is null) and (b= 7 or b is null); +a a b +7 7 NULL +7 7 7 +7 NULL 7 +8 8 NULL +8 8 7 +8 NULL 7 +insert into t2 values (null),(6); +delete from t1 where a=8; +explain select * from t2,t1 where t1.a=t2.a or t1.a is null; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 4 +1 SIMPLE t1 ref_or_null a a 5 test.t2.a 4 Using where; Using index +explain select * from t2,t1 where t1.a<=>t2.a or (t1.a is null and t1.b <> 9); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 4 +1 SIMPLE t1 ref_or_null a a 5 test.t2.a 4 Using where; Using index +select * from t2,t1 where t1.a<=>t2.a or (t1.a is null and t1.b <> 9); +a a b +7 7 NULL +7 7 7 +7 NULL 7 +8 NULL 7 +NULL NULL 7 +NULL NULL 9 +NULL NULL 9 +6 6 6 +6 NULL 7 +drop table t1,t2; +CREATE TABLE t1 ( +id int(10) unsigned NOT NULL auto_increment, +uniq_id int(10) unsigned default NULL, +PRIMARY KEY (id), +UNIQUE KEY idx1 (uniq_id) +) ENGINE=MyISAM; +CREATE TABLE t2 ( +id int(10) unsigned NOT NULL auto_increment, +uniq_id int(10) unsigned default NULL, +PRIMARY KEY (id) +) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1,NULL),(2,NULL),(3,1),(4,2),(5,NULL),(6,NULL),(7,3),(8,4),(9,NULL),(10,NULL); +INSERT INTO t1 VALUES (11,5),(12,6),(13,7),(14,8),(15,9); +INSERT INTO t2 VALUES (1,NULL),(2,NULL),(3,1),(4,2),(5,NULL),(6,NULL),(7,3),(8,4),(9,NULL),(10,NULL); +explain select id from t1 where uniq_id is null; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL idx1 NULL NULL NULL 15 Using where +explain select id from t1 where uniq_id =1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 const idx1 idx1 5 const 1 +UPDATE t1 SET id=id+100 where uniq_id is null; +UPDATE t2 SET id=id+100 where uniq_id is null; +select id from t1 where uniq_id is null; +id +101 +102 +105 +106 +109 +110 +select id from t2 where uniq_id is null; +id +101 +102 +105 +106 +109 +110 +DELETE FROM t1 WHERE uniq_id IS NULL; +DELETE FROM t2 WHERE uniq_id IS NULL; +SELECT * FROM t1 ORDER BY uniq_id, id; +id uniq_id +3 1 +4 2 +7 3 +8 4 +11 5 +12 6 +13 7 +14 8 +15 9 +SELECT * FROM t2 ORDER BY uniq_id, id; +id uniq_id +3 1 +4 2 +7 3 +8 4 +DROP table t1,t2; +CREATE TABLE `t1` ( +`order_id` char(32) NOT NULL default '', +`product_id` char(32) NOT NULL default '', +`product_type` int(11) NOT NULL default '0', +PRIMARY KEY (`order_id`,`product_id`,`product_type`) +) ENGINE=MyISAM; +CREATE TABLE `t2` ( +`order_id` char(32) NOT NULL default '', +`product_id` char(32) NOT NULL default '', +`product_type` int(11) NOT NULL default '0', +PRIMARY KEY (`order_id`,`product_id`,`product_type`) +) ENGINE=MyISAM; +INSERT INTO t1 (order_id, product_id, product_type) VALUES +('3d7ce39b5d4b3e3d22aaafe9b633de51',1206029, 3), +('3d7ce39b5d4b3e3d22aaafe9b633de51',5880836, 3), +('9d9aad7764b5b2c53004348ef8d34500',2315652, 3); +INSERT INTO t2 (order_id, product_id, product_type) VALUES +('9d9aad7764b5b2c53004348ef8d34500',2315652, 3); +select t1.* from t1 +left join t2 using(order_id, product_id, product_type) +where t2.order_id=NULL; +order_id product_id product_type +select t1.* from t1 +left join t2 using(order_id, product_id, product_type) +where t2.order_id is NULL; +order_id product_id product_type +3d7ce39b5d4b3e3d22aaafe9b633de51 1206029 3 +3d7ce39b5d4b3e3d22aaafe9b633de51 5880836 3 +drop table t1,t2; +create table t1 (id int); +insert into t1 values (null), (0); +create table t2 (id int); +insert into t2 values (null); +select * from t1, t2 where t1.id = t2.id; +id id +alter table t1 add key id (id); +select * from t1, t2 where t1.id = t2.id; +id id +drop table t1,t2; +create table t1 ( +id integer, +id2 integer not null, +index (id), +index (id2) +); +insert ignore into t1 values(null,null),(1,1); +Warnings: +Warning 1048 Column 'id2' cannot be null +select * from t1; +id id2 +NULL 0 +1 1 +select * from t1 where id <=> null; +id id2 +NULL 0 +select * from t1 where id <=> null or id > 0; +id id2 +NULL 0 +1 1 +select * from t1 where id is null or id > 0; +id id2 +NULL 0 +1 1 +select * from t1 where id2 <=> null or id2 > 0; +id id2 +1 1 +select * from t1 where id2 is null or id2 > 0; +id id2 +1 1 +delete from t1 where id <=> NULL; +select * from t1; +id id2 +1 1 +drop table t1; +CREATE TABLE t1 (a int); +CREATE TABLE t2 (a int, b int, INDEX idx(a)); +CREATE TABLE t3 (b int, INDEX idx(b)); +CREATE TABLE t4 (b int, INDEX idx(b)); +INSERT INTO t1 VALUES (1), (2), (3), (4); +INSERT INTO t2 VALUES (1, 1), (3, 1); +INSERT INTO t3 VALUES +(NULL), (NULL), (NULL), (NULL), (NULL), +(NULL), (NULL), (NULL), (NULL), (NULL); +INSERT INTO t4 SELECT * FROM t3; +INSERT INTO t3 SELECT * FROM t4; +INSERT INTO t4 SELECT * FROM t3; +INSERT INTO t3 SELECT * FROM t4; +INSERT INTO t4 SELECT * FROM t3; +INSERT INTO t3 SELECT * FROM t4; +INSERT INTO t4 SELECT * FROM t3; +INSERT INTO t3 SELECT * FROM t4; +INSERT INTO t4 SELECT * FROM t3; +INSERT INTO t3 SELECT * FROM t4; +INSERT INTO t4 SELECT * FROM t3; +INSERT INTO t3 SELECT * FROM t4; +INSERT INTO t4 SELECT * FROM t3; +INSERT INTO t3 SELECT * FROM t4; +INSERT INTO t4 SELECT * FROM t3; +INSERT INTO t3 SELECT * FROM t4; +INSERT INTO t3 VALUES (2), (3); +ANALYZE table t1, t2, t3; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +test.t2 analyze status Engine-independent statistics collected +test.t2 analyze status OK +test.t3 analyze status Engine-independent statistics collected +test.t3 analyze status OK +SELECT COUNT(*) FROM t3; +COUNT(*) +15972 +EXPLAIN SELECT SQL_CALC_FOUND_ROWS * FROM t1 LEFT JOIN t2 ON t1.a=t2.a +LEFT JOIN t3 ON t2.b=t3.b; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 +1 SIMPLE t2 ref idx idx 5 test.t1.a 1 Using where +1 SIMPLE t3 ref idx idx 5 test.t2.b 1 Using where; Using index +FLUSH STATUS ; +SELECT SQL_CALC_FOUND_ROWS * FROM t1 LEFT JOIN t2 ON t1.a=t2.a +LEFT JOIN t3 ON t2.b=t3.b; +a a b b +1 1 1 NULL +2 NULL NULL NULL +3 3 1 NULL +4 NULL NULL NULL +SELECT FOUND_ROWS(); +FOUND_ROWS() +4 +SHOW STATUS LIKE "handler_read%"; +Variable_name Value +Handler_read_first 0 +Handler_read_key 6 +Handler_read_last 0 +Handler_read_next 2 +Handler_read_prev 0 +Handler_read_retry 0 +Handler_read_rnd 0 +Handler_read_rnd_deleted 0 +Handler_read_rnd_next 5 +DROP TABLE t1,t2,t3,t4; +CREATE TABLE t1 ( +a int(11) default NULL, +b int(11) default NULL, +KEY a (a,b) +); +INSERT INTO t1 VALUES (0,10),(0,11),(0,12); +CREATE TABLE t2 ( +a int(11) default NULL, +b int(11) default NULL, +KEY a (a) +); +INSERT INTO t2 VALUES (3,NULL),(3,11),(3,12); +SELECT * FROM t2 inner join t1 WHERE ( t1.a = 0 OR t1.a IS NULL) AND t2.a = 3 AND t2.b = t1.b; +a b a b +3 11 0 11 +3 12 0 12 +drop table t1, t2; +End of 5.0 tests +# +# BUG#727667 Wrong result with OR + NOT NULL in maria-5.3 +# +CREATE TABLE t1 ( +f3 int(11), +f10 varchar(1), +KEY (f3) +); +INSERT INTO t1 VALUES ('9','k'),(NULL,'r'); +SELECT * FROM t1 WHERE (f3 = 83) OR (f10 = 'z' AND f3 IS NULL); +f3 f10 +DROP TABLE t1; +## end of 10.6 tests +set @@note_verbosity=default; |