[Commits] Rev 3686: Fixed bug mdev-4962. in file:///home/igor/maria/maria-5.3-mdev4962/

Igor Babaev igor at askmonty.org
Thu Aug 29 20:56:13 EEST 2013


At file:///home/igor/maria/maria-5.3-mdev4962/

------------------------------------------------------------
revno: 3686
revision-id: igor at askmonty.org-20130829175612-acvqr7uadpa7kylp
parent: igor at askmonty.org-20130826225147-cb5wezwpfgkn6i22
committer: Igor Babaev <igor at askmonty.org>
branch nick: maria-5.3-mdev4962
timestamp: Thu 2013-08-29 10:56:12 -0700
message:
  Fixed bug mdev-4962.
  When a non-nullable datetime field is used under an IS NULL predicate
  of the WHERE condition in a query with outer joins the remove_eq_conds
  function should check whether this field belongs to an inner table
  of any outer join that can be, in a general case, a nested outer join.
-------------- next part --------------
=== modified file 'mysql-test/r/join_outer.result'
--- a/mysql-test/r/join_outer.result	2013-08-26 22:51:47 +0000
+++ b/mysql-test/r/join_outer.result	2013-08-29 17:56:12 +0000
@@ -1862,4 +1862,30 @@
 WHERE ( dt IS NULL OR FALSE ) AND b2 IS NULL;
 a1	b1	dt	a2	b2
 DROP TABLE t1,t2;
+#
+# Bug mdev-4962: nested outer join with 
+#                <non-nullable datetime field> IS NULL in WHERE 
+#                causes an assert failure  
+#
+CREATE TABLE t1 (i1 int) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1),(2);
+CREATE TABLE t2 (i2 int) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (10),(20);
+CREATE TABLE t3 (i3 int, d3 datetime NOT NULL) ENGINE=MyISAM;
+INSERT INTO t3 VALUES (8,'2008-12-04 17:53:42'),(9,'2012-12-21 12:12:12');
+SELECT * FROM t1 LEFT JOIN t2 LEFT JOIN t3 ON i2 = i3 ON i1 = i3
+WHERE d3 IS NULL;
+i1	i2	i3	d3
+1	NULL	NULL	NULL
+2	NULL	NULL	NULL
+EXPLAIN EXTENDED
+SELECT * FROM t1 LEFT JOIN t2 LEFT JOIN t3 ON i2 = i3 ON i1 = i3
+WHERE d3 IS NULL;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+Warnings:
+Note	1003	select `test`.`t1`.`i1` AS `i1`,`test`.`t2`.`i2` AS `i2`,`test`.`t3`.`i3` AS `i3`,`test`.`t3`.`d3` AS `d3` from `test`.`t1` left join (`test`.`t2` join `test`.`t3`) on(((`test`.`t2`.`i2` = `test`.`t1`.`i1`) and (`test`.`t3`.`i3` = `test`.`t1`.`i1`))) where ((`test`.`t3`.`d3` = 0) or isnull(`test`.`t3`.`d3`))
+DROP TABLE t1,t2,t3;
 SET optimizer_switch=@save_optimizer_switch;

=== modified file 'mysql-test/r/join_outer_jcl6.result'
--- a/mysql-test/r/join_outer_jcl6.result	2013-08-26 22:51:47 +0000
+++ b/mysql-test/r/join_outer_jcl6.result	2013-08-29 17:56:12 +0000
@@ -1873,6 +1873,32 @@
 WHERE ( dt IS NULL OR FALSE ) AND b2 IS NULL;
 a1	b1	dt	a2	b2
 DROP TABLE t1,t2;
+#
+# Bug mdev-4962: nested outer join with 
+#                <non-nullable datetime field> IS NULL in WHERE 
+#                causes an assert failure  
+#
+CREATE TABLE t1 (i1 int) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1),(2);
+CREATE TABLE t2 (i2 int) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (10),(20);
+CREATE TABLE t3 (i3 int, d3 datetime NOT NULL) ENGINE=MyISAM;
+INSERT INTO t3 VALUES (8,'2008-12-04 17:53:42'),(9,'2012-12-21 12:12:12');
+SELECT * FROM t1 LEFT JOIN t2 LEFT JOIN t3 ON i2 = i3 ON i1 = i3
+WHERE d3 IS NULL;
+i1	i2	i3	d3
+1	NULL	NULL	NULL
+2	NULL	NULL	NULL
+EXPLAIN EXTENDED
+SELECT * FROM t1 LEFT JOIN t2 LEFT JOIN t3 ON i2 = i3 ON i1 = i3
+WHERE d3 IS NULL;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (flat, BNL join)
+1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (incremental, BNL join)
+Warnings:
+Note	1003	select `test`.`t1`.`i1` AS `i1`,`test`.`t2`.`i2` AS `i2`,`test`.`t3`.`i3` AS `i3`,`test`.`t3`.`d3` AS `d3` from `test`.`t1` left join (`test`.`t2` join `test`.`t3`) on(((`test`.`t2`.`i2` = `test`.`t1`.`i1`) and (`test`.`t3`.`i3` = `test`.`t1`.`i1`))) where ((`test`.`t3`.`d3` = 0) or isnull(`test`.`t3`.`d3`))
+DROP TABLE t1,t2,t3;
 SET optimizer_switch=@save_optimizer_switch;
 set join_cache_level=default;
 show variables like 'join_cache_level';

=== modified file 'mysql-test/t/join_outer.test'
--- a/mysql-test/t/join_outer.test	2013-08-26 22:51:47 +0000
+++ b/mysql-test/t/join_outer.test	2013-08-29 17:56:12 +0000
@@ -1410,4 +1410,27 @@
 
 DROP TABLE t1,t2;
 
+--echo #
+--echo # Bug mdev-4962: nested outer join with 
+--echo #                <non-nullable datetime field> IS NULL in WHERE 
+--echo #                causes an assert failure  
+--echo #
+
+CREATE TABLE t1 (i1 int) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1),(2);
+
+CREATE TABLE t2 (i2 int) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (10),(20);
+
+CREATE TABLE t3 (i3 int, d3 datetime NOT NULL) ENGINE=MyISAM;
+INSERT INTO t3 VALUES (8,'2008-12-04 17:53:42'),(9,'2012-12-21 12:12:12');
+
+SELECT * FROM t1 LEFT JOIN t2 LEFT JOIN t3 ON i2 = i3 ON i1 = i3
+  WHERE d3 IS NULL;
+EXPLAIN EXTENDED
+SELECT * FROM t1 LEFT JOIN t2 LEFT JOIN t3 ON i2 = i3 ON i1 = i3
+  WHERE d3 IS NULL;
+
+DROP TABLE t1,t2,t3;
+
 SET optimizer_switch=@save_optimizer_switch;

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2013-08-26 22:51:47 +0000
+++ b/sql/sql_select.cc	2013-08-29 17:56:12 +0000
@@ -13564,7 +13564,7 @@
 	if (!(eq_cond= new Item_func_eq(args[0],new Item_int("0", 0, 2))))
           return cond;
 	
-        if (field->table->pos_in_table_list->outer_join)
+        if (field->table->pos_in_table_list->is_inner_table_of_outer_join())
         {
           // outer join: transform "col IS NULL" to "col IS NULL or col=0"
           Item *or_cond= new  Item_cond_or(eq_cond, cond);

=== modified file 'sql/table.h'
--- a/sql/table.h	2012-11-20 12:57:49 +0000
+++ b/sql/table.h	2013-08-29 17:56:12 +0000
@@ -1845,6 +1845,16 @@
 
   bool single_table_updatable();
 
+  bool is_inner_table_of_outer_join()
+  {
+    for (TABLE_LIST *tbl= this; tbl; tbl= tbl->embedding)
+    {
+      if (tbl->outer_join)
+        return true;
+    }
+    return false;
+  } 
+
 private:
   bool prep_check_option(THD *thd, uint8 check_opt_type);
   bool prep_where(THD *thd, Item **conds, bool no_where_clause);



More information about the commits mailing list