summaryrefslogtreecommitdiff
path: root/mysql-test
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test')
-rw-r--r--mysql-test/r/join_outer.result14
-rw-r--r--mysql-test/r/join_outer_jcl6.result14
-rw-r--r--mysql-test/r/select.result35
-rw-r--r--mysql-test/r/select_jcl6.result35
-rw-r--r--mysql-test/r/select_pkeycache.result35
-rw-r--r--mysql-test/r/subselect_sj2_mat.result2
-rw-r--r--mysql-test/r/type_datetime.result16
-rw-r--r--mysql-test/t/join_outer.test17
-rw-r--r--mysql-test/t/select.test37
-rw-r--r--mysql-test/t/type_datetime.test15
10 files changed, 219 insertions, 1 deletions
diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result
index c28887d4ff2..1206d954d07 100644
--- a/mysql-test/r/join_outer.result
+++ b/mysql-test/r/join_outer.result
@@ -2071,4 +2071,18 @@ b c d
5 8 88
5 8 81
DROP TABLE t1,t2;
+#
+# Bug mdev-4336: LEFT JOIN with disjunctive
+# <non-nullable datetime field> IS NULL in WHERE
+# causes a hang and eventual crash
+#
+CREATE TABLE t1 (
+id int(11) NOT NULL,
+modified datetime NOT NULL,
+PRIMARY KEY (id)
+);
+SELECT a.* FROM t1 a LEFT JOIN t1 b ON a.id = b.id
+WHERE a.modified > b.modified or b.modified IS NULL;
+id modified
+DROP TABLE t1;
SET optimizer_switch=@save_optimizer_switch;
diff --git a/mysql-test/r/join_outer_jcl6.result b/mysql-test/r/join_outer_jcl6.result
index 4efb6126cba..6abd827ccfc 100644
--- a/mysql-test/r/join_outer_jcl6.result
+++ b/mysql-test/r/join_outer_jcl6.result
@@ -2082,6 +2082,20 @@ b c d
5 8 88
5 8 81
DROP TABLE t1,t2;
+#
+# Bug mdev-4336: LEFT JOIN with disjunctive
+# <non-nullable datetime field> IS NULL in WHERE
+# causes a hang and eventual crash
+#
+CREATE TABLE t1 (
+id int(11) NOT NULL,
+modified datetime NOT NULL,
+PRIMARY KEY (id)
+);
+SELECT a.* FROM t1 a LEFT JOIN t1 b ON a.id = b.id
+WHERE a.modified > b.modified or b.modified IS NULL;
+id modified
+DROP TABLE t1;
SET optimizer_switch=@save_optimizer_switch;
set join_cache_level=default;
show variables like 'join_cache_level';
diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result
index 3d16d37c5f1..38a991990be 100644
--- a/mysql-test/r/select.result
+++ b/mysql-test/r/select.result
@@ -5119,6 +5119,7 @@ SELECT 1 FROM t1 GROUP BY 1;
1
1
drop table t1;
+set sql_buffer_result= 0;
#
# Bug #58422: Incorrect result when OUTER JOIN'ing
# with an empty table
@@ -5322,4 +5323,38 @@ AND Time_zone_id = Time_zone_id
OR Time_zone_id <> Time_zone_id )
AND Use_leap_seconds <> 'N';
Time_zone_id Use_leap_seconds
+#
+# Bug mdev-4274: result of simplification of OR badly merged
+# into embedding AND
+#
+CREATE TABLE t1 (a int, b int, INDEX idx(b)) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (8,8);
+CREATE TABLE t2 (c int, INDEX idx(c)) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (8), (9);
+EXPLAIN EXTENDED
+SELECT * FROM t1 INNER JOIN t2 ON ( c = a )
+WHERE 1 IS NULL OR b < 33 AND b = c;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 system idx NULL NULL NULL 1 100.00
+1 SIMPLE t2 ref idx idx 5 const 1 100.00 Using index
+Warnings:
+Note 1003 select 8 AS `a`,8 AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` where ((`test`.`t2`.`c` = 8) and 1)
+SELECT * FROM t1 INNER JOIN t2 ON ( c = a )
+WHERE 1 IS NULL OR b < 33 AND b = c;
+a b c
+8 8 8
+DROP TABLE t1,t2;
+#
+# Bug mdev-4413: another manifestations of bug mdev-2474
+# (valgrind complains)
+#
+CREATE TABLE t1 (a int, b int) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (7,1);
+CREATE TABLE t2 (c int) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (0), (8);
+SELECT * FROM t1, t2
+WHERE c = a AND
+( 0 OR ( b BETWEEN 45 AND 300 OR a > 45 AND a < 100 ) AND b = c );
+a b c
+DROP TABLE t1, t2;
End of 5.3 tests
diff --git a/mysql-test/r/select_jcl6.result b/mysql-test/r/select_jcl6.result
index 467f940c85d..8fa4f27b4b8 100644
--- a/mysql-test/r/select_jcl6.result
+++ b/mysql-test/r/select_jcl6.result
@@ -5130,6 +5130,7 @@ SELECT 1 FROM t1 GROUP BY 1;
1
1
drop table t1;
+set sql_buffer_result= 0;
#
# Bug #58422: Incorrect result when OUTER JOIN'ing
# with an empty table
@@ -5333,6 +5334,40 @@ AND Time_zone_id = Time_zone_id
OR Time_zone_id <> Time_zone_id )
AND Use_leap_seconds <> 'N';
Time_zone_id Use_leap_seconds
+#
+# Bug mdev-4274: result of simplification of OR badly merged
+# into embedding AND
+#
+CREATE TABLE t1 (a int, b int, INDEX idx(b)) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (8,8);
+CREATE TABLE t2 (c int, INDEX idx(c)) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (8), (9);
+EXPLAIN EXTENDED
+SELECT * FROM t1 INNER JOIN t2 ON ( c = a )
+WHERE 1 IS NULL OR b < 33 AND b = c;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 system idx NULL NULL NULL 1 100.00
+1 SIMPLE t2 ref idx idx 5 const 1 100.00 Using index
+Warnings:
+Note 1003 select 8 AS `a`,8 AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` where ((`test`.`t2`.`c` = 8) and 1)
+SELECT * FROM t1 INNER JOIN t2 ON ( c = a )
+WHERE 1 IS NULL OR b < 33 AND b = c;
+a b c
+8 8 8
+DROP TABLE t1,t2;
+#
+# Bug mdev-4413: another manifestations of bug mdev-2474
+# (valgrind complains)
+#
+CREATE TABLE t1 (a int, b int) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (7,1);
+CREATE TABLE t2 (c int) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (0), (8);
+SELECT * FROM t1, t2
+WHERE c = a AND
+( 0 OR ( b BETWEEN 45 AND 300 OR a > 45 AND a < 100 ) AND b = c );
+a b c
+DROP TABLE t1, t2;
End of 5.3 tests
set join_cache_level=default;
show variables like 'join_cache_level';
diff --git a/mysql-test/r/select_pkeycache.result b/mysql-test/r/select_pkeycache.result
index 3d16d37c5f1..38a991990be 100644
--- a/mysql-test/r/select_pkeycache.result
+++ b/mysql-test/r/select_pkeycache.result
@@ -5119,6 +5119,7 @@ SELECT 1 FROM t1 GROUP BY 1;
1
1
drop table t1;
+set sql_buffer_result= 0;
#
# Bug #58422: Incorrect result when OUTER JOIN'ing
# with an empty table
@@ -5322,4 +5323,38 @@ AND Time_zone_id = Time_zone_id
OR Time_zone_id <> Time_zone_id )
AND Use_leap_seconds <> 'N';
Time_zone_id Use_leap_seconds
+#
+# Bug mdev-4274: result of simplification of OR badly merged
+# into embedding AND
+#
+CREATE TABLE t1 (a int, b int, INDEX idx(b)) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (8,8);
+CREATE TABLE t2 (c int, INDEX idx(c)) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (8), (9);
+EXPLAIN EXTENDED
+SELECT * FROM t1 INNER JOIN t2 ON ( c = a )
+WHERE 1 IS NULL OR b < 33 AND b = c;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 system idx NULL NULL NULL 1 100.00
+1 SIMPLE t2 ref idx idx 5 const 1 100.00 Using index
+Warnings:
+Note 1003 select 8 AS `a`,8 AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` where ((`test`.`t2`.`c` = 8) and 1)
+SELECT * FROM t1 INNER JOIN t2 ON ( c = a )
+WHERE 1 IS NULL OR b < 33 AND b = c;
+a b c
+8 8 8
+DROP TABLE t1,t2;
+#
+# Bug mdev-4413: another manifestations of bug mdev-2474
+# (valgrind complains)
+#
+CREATE TABLE t1 (a int, b int) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (7,1);
+CREATE TABLE t2 (c int) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (0), (8);
+SELECT * FROM t1, t2
+WHERE c = a AND
+( 0 OR ( b BETWEEN 45 AND 300 OR a > 45 AND a < 100 ) AND b = c );
+a b c
+DROP TABLE t1, t2;
End of 5.3 tests
diff --git a/mysql-test/r/subselect_sj2_mat.result b/mysql-test/r/subselect_sj2_mat.result
index c91ee67a717..1af507668da 100644
--- a/mysql-test/r/subselect_sj2_mat.result
+++ b/mysql-test/r/subselect_sj2_mat.result
@@ -1377,7 +1377,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 100.00 Using where
2 MATERIALIZED t3 hash_ALL NULL #hash#$hj 5 test.t2.i2 3 100.00 Using where; Using join buffer (flat, BNLH join)
Warnings:
-Note 1003 select `test`.`t1`.`i1` AS `i1` from `test`.`t1` semi join (`test`.`t2` join `test`.`t3`) where ((`test`.`t3`.`i3` = `test`.`t2`.`i2`) and (`test`.`t1`.`i1` = `test`.`t2`.`i2`) and (`test`.`t3`.`i3` > 0))
+Note 1003 select `test`.`t1`.`i1` AS `i1` from `test`.`t1` semi join (`test`.`t2` join `test`.`t3`) where ((`test`.`t3`.`i3` = `test`.`t2`.`i2`) and (`test`.`t1`.`i1` = `test`.`t2`.`i2`) and (`test`.`t2`.`i2` > 0))
SELECT * FROM t1
WHERE i1 IN (SELECT i3 FROM t2, t3 WHERE i3 > 0 AND i3 = i2 OR 1=2);
i1
diff --git a/mysql-test/r/type_datetime.result b/mysql-test/r/type_datetime.result
index 05cdcf7e256..d6676bbf16b 100644
--- a/mysql-test/r/type_datetime.result
+++ b/mysql-test/r/type_datetime.result
@@ -665,6 +665,22 @@ Warning 1292 Incorrect datetime value: '10:19:31'
Warning 1292 Incorrect datetime value: '22:55:23'
Warning 1292 Incorrect datetime value: '10:19:31'
drop table t1;
+#
+# Semantics of the condition <non-nullable datetime field> IS NULL
+# when the field belongs to an inner table of an outer join
+#
+create table t1 (a int, b date not null);
+insert t1 values (1, 0), (2, '1999-01-02');
+create table t2 (c int);
+insert t2 values (1),(3);
+select * from t2 left join t1 on t1.a=t2.c where t1.a is null;
+c a b
+3 NULL NULL
+select * from t2 left join t1 on t1.a=t2.c where t1.b is null;
+c a b
+1 1 0000-00-00
+3 NULL NULL
+drop table t1,t2;
End of 5.3 tests
#
# Start of 5.5 tests
diff --git a/mysql-test/t/join_outer.test b/mysql-test/t/join_outer.test
index 44f4afd451b..a325d0b6233 100644
--- a/mysql-test/t/join_outer.test
+++ b/mysql-test/t/join_outer.test
@@ -1622,4 +1622,21 @@ ORDER BY t1.b;
DROP TABLE t1,t2;
+--echo #
+--echo # Bug mdev-4336: LEFT JOIN with disjunctive
+--echo # <non-nullable datetime field> IS NULL in WHERE
+--echo # causes a hang and eventual crash
+--echo #
+
+CREATE TABLE t1 (
+ id int(11) NOT NULL,
+ modified datetime NOT NULL,
+ PRIMARY KEY (id)
+);
+
+SELECT a.* FROM t1 a LEFT JOIN t1 b ON a.id = b.id
+ WHERE a.modified > b.modified or b.modified IS NULL;
+
+DROP TABLE t1;
+
SET optimizer_switch=@save_optimizer_switch;
diff --git a/mysql-test/t/select.test b/mysql-test/t/select.test
index af507b1f7ef..b036cc6bc01 100644
--- a/mysql-test/t/select.test
+++ b/mysql-test/t/select.test
@@ -4289,6 +4289,7 @@ SELECT f1 FROM t1 GROUP BY 1;
SELECT f1 FROM t1 GROUP BY '123' = 'abc';
SELECT 1 FROM t1 GROUP BY 1;
drop table t1;
+set sql_buffer_result= 0;
--echo #
@@ -4479,5 +4480,41 @@ WHERE ( NOT (Use_leap_seconds <= Use_leap_seconds AND Time_zone_id != 1)
OR Time_zone_id <> Time_zone_id )
AND Use_leap_seconds <> 'N';
+--echo #
+--echo # Bug mdev-4274: result of simplification of OR badly merged
+--echo # into embedding AND
+--echo #
+
+CREATE TABLE t1 (a int, b int, INDEX idx(b)) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (8,8);
+
+CREATE TABLE t2 (c int, INDEX idx(c)) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (8), (9);
+
+EXPLAIN EXTENDED
+SELECT * FROM t1 INNER JOIN t2 ON ( c = a )
+ WHERE 1 IS NULL OR b < 33 AND b = c;
+SELECT * FROM t1 INNER JOIN t2 ON ( c = a )
+ WHERE 1 IS NULL OR b < 33 AND b = c;
+
+DROP TABLE t1,t2;
+
+--echo #
+--echo # Bug mdev-4413: another manifestations of bug mdev-2474
+--echo # (valgrind complains)
+--echo #
+
+CREATE TABLE t1 (a int, b int) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (7,1);
+
+CREATE TABLE t2 (c int) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (0), (8);
+
+SELECT * FROM t1, t2
+ WHERE c = a AND
+ ( 0 OR ( b BETWEEN 45 AND 300 OR a > 45 AND a < 100 ) AND b = c );
+
+DROP TABLE t1, t2;
+
--echo End of 5.3 tests
diff --git a/mysql-test/t/type_datetime.test b/mysql-test/t/type_datetime.test
index d1648254fb2..05bc72291cd 100644
--- a/mysql-test/t/type_datetime.test
+++ b/mysql-test/t/type_datetime.test
@@ -471,6 +471,21 @@ insert into t1 values ('2000-12-03','22:55:23'),('2008-05-03','10:19:31');
select case when d = '2012-12-12' then d else t end as cond, group_concat( d ) from t1 group by cond;
drop table t1;
+--echo #
+--echo # Semantics of the condition <non-nullable datetime field> IS NULL
+--echo # when the field belongs to an inner table of an outer join
+--echo #
+
+create table t1 (a int, b date not null);
+insert t1 values (1, 0), (2, '1999-01-02');
+create table t2 (c int);
+insert t2 values (1),(3);
+
+select * from t2 left join t1 on t1.a=t2.c where t1.a is null;
+select * from t2 left join t1 on t1.a=t2.c where t1.b is null;
+
+drop table t1,t2;
+
--echo End of 5.3 tests
--echo #
View Lorry Controller Status