[Commits] Rev 3873: Merge 5.3->5.5 in file:///home/igor/maria/maria-5.5-merge/

Igor Babaev igor at askmonty.org
Thu Aug 29 22:32:43 EEST 2013


At file:///home/igor/maria/maria-5.5-merge/

------------------------------------------------------------
revno: 3873 [merge]
revision-id: igor at askmonty.org-20130829193209-b88om4lfuodksrqv
parent: sergii at pisem.net-20130828201613-pv1til86b9s65nie
parent: igor at askmonty.org-20130829175612-acvqr7uadpa7kylp
committer: Igor Babaev <igor at askmonty.org>
branch nick: maria-5.5-merge
timestamp: Thu 2013-08-29 12:32:09 -0700
message:
  Merge 5.3->5.5
modified:
  mysql-test/r/join_outer.result sp1f-join_outer.result-20001228015633-vk2jshiracfus3ze2d2bim2csnnrc5us
  mysql-test/r/join_outer_jcl6.result join_outer_jcl6.resu-20091221012858-uiftww98yhc31z02-1
  mysql-test/t/join_outer.test   sp1f-join_outer.test-20001228015636-himrcptylaquy6l5d7pl7pawom3ytmtw
  sql/sql_select.cc              sp1f-sql_select.cc-19700101030959-egb7whpkh76zzvikycs5nsnuviu4fdlb
  sql/table.h                    sp1f-table.h-19700101030959-dv72bajftxj5fbdjuajquappanuv2ija
-------------- next part --------------
=== modified file 'mysql-test/r/join_outer.result'
--- a/mysql-test/r/join_outer.result	2013-08-28 05:19:14 +0000
+++ b/mysql-test/r/join_outer.result	2013-08-29 19:32:09 +0000
@@ -2164,4 +2164,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-28 05:19:14 +0000
+++ b/mysql-test/r/join_outer_jcl6.result	2013-08-29 19:32:09 +0000
@@ -2175,6 +2175,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-28 05:19:14 +0000
+++ b/mysql-test/t/join_outer.test	2013-08-29 19:32:09 +0000
@@ -1721,4 +1721,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 17:04:10 +0000
+++ b/sql/sql_select.cc	2013-08-29 19:32:09 +0000
@@ -13832,7 +13832,7 @@
         if (!eq_cond)
           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(thd->mem_root) Item_cond_or(eq_cond, cond);

=== modified file 'sql/table.h'
--- a/sql/table.h	2013-07-16 17:09:54 +0000
+++ b/sql/table.h	2013-08-29 19:32:09 +0000
@@ -2129,6 +2129,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