summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-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
-rw-r--r--sql/sql_select.cc28
11 files changed, 246 insertions, 2 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 #
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 24f480bbfb9..1b6e527294c 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -13478,7 +13478,27 @@ internal_remove_eq_conds(THD *thd, COND *cond, Item::cond_result *cond_value)
if (new_item_and_list->is_empty())
li.remove();
else
+ {
+ Item *list_item;
+ Item *new_list_item;
+ uint cnt= new_item_and_list->elements;
+ List_iterator<Item> it(*new_item_and_list);
+ while ((list_item= it++))
+ {
+ uchar* is_subst_valid= (uchar *) Item::ANY_SUBST;
+ new_list_item=
+ list_item->compile(&Item::subst_argument_checker,
+ &is_subst_valid,
+ &Item::equal_fields_propagator,
+ (uchar *) &cond_and->cond_equal);
+ if (new_list_item != list_item)
+ it.replace(new_list_item);
+ new_list_item->update_used_tables();
+ }
li.replace(*new_item_and_list);
+ for (cnt--; cnt; cnt--)
+ item= li++;
+ }
cond_and_list->concat((List<Item>*) cond_equal_items);
}
else if (new_item->type() == Item::FUNC_ITEM &&
@@ -13498,7 +13518,13 @@ internal_remove_eq_conds(THD *thd, COND *cond, Item::cond_result *cond_value)
if (new_item->type() == Item::COND_ITEM &&
((Item_cond*) new_item)->functype() ==
((Item_cond*) cond)->functype())
- li.replace(*((Item_cond*) new_item)->argument_list());
+ {
+ List<Item> *arg_list= ((Item_cond*) new_item)->argument_list();
+ uint cnt= arg_list->elements;
+ li.replace(*arg_list);
+ for ( cnt--; cnt; cnt--)
+ item= li++;
+ }
else
li.replace(new_item);
}
View Lorry Controller Status