summaryrefslogtreecommitdiff
path: root/mysql-test
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test')
-rw-r--r--mysql-test/r/null.result2
-rw-r--r--mysql-test/r/null_key.result26
-rw-r--r--mysql-test/r/order_by.result4
-rw-r--r--mysql-test/r/subselect.result2
-rw-r--r--mysql-test/r/subselect_no_mat.result2
-rw-r--r--mysql-test/r/subselect_no_opts.result2
-rw-r--r--mysql-test/r/subselect_no_semijoin.result2
-rw-r--r--mysql-test/t/null_key.test13
8 files changed, 39 insertions, 14 deletions
diff --git a/mysql-test/r/null.result b/mysql-test/r/null.result
index 897366787f1..7cadeb81c2c 100644
--- a/mysql-test/r/null.result
+++ b/mysql-test/r/null.result
@@ -170,7 +170,7 @@ insert into t1 select i*2 from t1;
insert into t1 values(null);
explain select * from t1 where i=2 or i is null;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref_or_null i i 5 const 9 Using index
+1 SIMPLE t1 ref_or_null i i 5 const 9 Using where; Using index
select count(*) from t1 where i=2 or i is null;
count(*)
10
diff --git a/mysql-test/r/null_key.result b/mysql-test/r/null_key.result
index 67725c45314..db2f662eeac 100644
--- a/mysql-test/r/null_key.result
+++ b/mysql-test/r/null_key.result
@@ -21,10 +21,10 @@ 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 index
+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 index; Using filesort
+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
@@ -151,7 +151,7 @@ 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 index
+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
@@ -166,7 +166,7 @@ a b
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 index
+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
@@ -192,7 +192,7 @@ a a b
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 index
+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
@@ -202,7 +202,7 @@ a a b
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 index
+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
@@ -226,7 +226,7 @@ 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 index
+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
@@ -447,3 +447,15 @@ a b a b
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;
diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result
index 50c601b14a8..24a1727b0b9 100644
--- a/mysql-test/r/order_by.result
+++ b/mysql-test/r/order_by.result
@@ -643,7 +643,7 @@ create table t1(a int, b int, index(b));
insert into t1 values (2, 1), (1, 1), (4, NULL), (3, NULL), (6, 2), (5, 2);
explain select * from t1 where b=1 or b is null order by a;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref_or_null b b 5 const 3 Using filesort
+1 SIMPLE t1 ref_or_null b b 5 const 3 Using index condition; Using filesort
select * from t1 where b=1 or b is null order by a;
a b
1 1
@@ -652,7 +652,7 @@ a b
4 NULL
explain select * from t1 where b=2 or b is null order by a;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref_or_null b b 5 const 4 Using filesort
+1 SIMPLE t1 ref_or_null b b 5 const 4 Using index condition; Using filesort
select * from t1 where b=2 or b is null order by a;
a b
3 NULL
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index 73d14acd532..a04b26a074f 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -906,7 +906,7 @@ a t1.a in (select t2.a from t2,t3 where t3.a=t2.a)
explain extended SELECT t1.a, t1.a in (select t2.a from t2,t3 where t3.a=t2.a) FROM t1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 index NULL PRIMARY 4 NULL 4 100.00 Using index
-2 DEPENDENT SUBQUERY t2 ref_or_null a a 5 func 2 100.00 Using index
+2 DEPENDENT SUBQUERY t2 ref_or_null a a 5 func 2 100.00 Using where; Using index
2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join)
Warnings:
Note 1003 select `test`.`t1`.`a` AS `a`,<expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(select 1 from `test`.`t2` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t2`.`a`) and ((<cache>(`test`.`t1`.`a`) = `test`.`t2`.`a`) or isnull(`test`.`t2`.`a`))) having <is_not_null_test>(`test`.`t2`.`a`)))) AS `t1.a in (select t2.a from t2,t3 where t3.a=t2.a)` from `test`.`t1`
diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result
index 486bb03373e..24a9593aea1 100644
--- a/mysql-test/r/subselect_no_mat.result
+++ b/mysql-test/r/subselect_no_mat.result
@@ -910,7 +910,7 @@ a t1.a in (select t2.a from t2,t3 where t3.a=t2.a)
explain extended SELECT t1.a, t1.a in (select t2.a from t2,t3 where t3.a=t2.a) FROM t1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 index NULL PRIMARY 4 NULL 4 100.00 Using index
-2 DEPENDENT SUBQUERY t2 ref_or_null a a 5 func 2 100.00 Using index
+2 DEPENDENT SUBQUERY t2 ref_or_null a a 5 func 2 100.00 Using where; Using index
2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join)
Warnings:
Note 1003 select `test`.`t1`.`a` AS `a`,<expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(select 1 from `test`.`t2` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t2`.`a`) and ((<cache>(`test`.`t1`.`a`) = `test`.`t2`.`a`) or isnull(`test`.`t2`.`a`))) having <is_not_null_test>(`test`.`t2`.`a`)))) AS `t1.a in (select t2.a from t2,t3 where t3.a=t2.a)` from `test`.`t1`
diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result
index 07412a0c72c..0e48c753dfb 100644
--- a/mysql-test/r/subselect_no_opts.result
+++ b/mysql-test/r/subselect_no_opts.result
@@ -907,7 +907,7 @@ a t1.a in (select t2.a from t2,t3 where t3.a=t2.a)
explain extended SELECT t1.a, t1.a in (select t2.a from t2,t3 where t3.a=t2.a) FROM t1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 index NULL PRIMARY 4 NULL 4 100.00 Using index
-2 DEPENDENT SUBQUERY t2 ref_or_null a a 5 func 2 100.00 Using index
+2 DEPENDENT SUBQUERY t2 ref_or_null a a 5 func 2 100.00 Using where; Using index
2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join)
Warnings:
Note 1003 select `test`.`t1`.`a` AS `a`,<expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(select 1 from `test`.`t2` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t2`.`a`) and ((<cache>(`test`.`t1`.`a`) = `test`.`t2`.`a`) or isnull(`test`.`t2`.`a`))) having <is_not_null_test>(`test`.`t2`.`a`)))) AS `t1.a in (select t2.a from t2,t3 where t3.a=t2.a)` from `test`.`t1`
diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result
index 737fd5f2eb5..8808ed15bea 100644
--- a/mysql-test/r/subselect_no_semijoin.result
+++ b/mysql-test/r/subselect_no_semijoin.result
@@ -907,7 +907,7 @@ a t1.a in (select t2.a from t2,t3 where t3.a=t2.a)
explain extended SELECT t1.a, t1.a in (select t2.a from t2,t3 where t3.a=t2.a) FROM t1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 index NULL PRIMARY 4 NULL 4 100.00 Using index
-2 DEPENDENT SUBQUERY t2 ref_or_null a a 5 func 2 100.00 Using index
+2 DEPENDENT SUBQUERY t2 ref_or_null a a 5 func 2 100.00 Using where; Using index
2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join)
Warnings:
Note 1003 select `test`.`t1`.`a` AS `a`,<expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(select 1 from `test`.`t2` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t2`.`a`) and ((<cache>(`test`.`t1`.`a`) = `test`.`t2`.`a`) or isnull(`test`.`t2`.`a`))) having <is_not_null_test>(`test`.`t2`.`a`)))) AS `t1.a in (select t2.a from t2,t3 where t3.a=t2.a)` from `test`.`t1`
diff --git a/mysql-test/t/null_key.test b/mysql-test/t/null_key.test
index 1400c643203..695b2835610 100644
--- a/mysql-test/t/null_key.test
+++ b/mysql-test/t/null_key.test
@@ -263,3 +263,16 @@ SELECT * FROM t2 inner join t1 WHERE ( t1.a = 0 OR t1.a IS NULL) AND t2.a = 3 AN
drop table t1, t2;
-- echo End of 5.0 tests
+--echo #
+--echo # BUG#727667 Wrong result with OR + NOT NULL in maria-5.3
+--echo #
+
+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);
+DROP TABLE t1;
+
View Lorry Controller Status