diff options
author | Michael Widenius <monty@mariadb.org> | 2018-03-09 14:05:35 +0200 |
---|---|---|
committer | Monty <monty@mariadb.org> | 2018-03-29 13:59:44 +0300 |
commit | a7abddeffa6a760ce948c2dfb007cdf3f1a369d5 (patch) | |
tree | 70eb743fa965a17380bbc0ac88ae79ca1075b896 /mysql-test/r/null_key.result | |
parent | ab1941266c59a19703a74b5593cf3f508a5752d7 (diff) | |
download | mariadb-git-a7abddeffa6a760ce948c2dfb007cdf3f1a369d5.tar.gz |
Create 'main' test directory and move 't' and 'r' there
Diffstat (limited to 'mysql-test/r/null_key.result')
-rw-r--r-- | mysql-test/r/null_key.result | 466 |
1 files changed, 0 insertions, 466 deletions
diff --git a/mysql-test/r/null_key.result b/mysql-test/r/null_key.result deleted file mode 100644 index 4b4a4b80dee..00000000000 --- a/mysql-test/r/null_key.result +++ /dev/null @@ -1,466 +0,0 @@ -drop table if exists t1,t2; -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 < 3) 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 3 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 4 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 4 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 4 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 4 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 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 ref idx1 idx1 5 const 5 Using index condition -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 -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 OK -test.t2 analyze status OK -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; |