[Commits] Rev 2942: Merge in file:///home/igor/maria/maria-5.1/

igor at askmonty.org igor at askmonty.org
Thu Sep 23 20:03:46 EEST 2010


At file:///home/igor/maria/maria-5.1/

------------------------------------------------------------
revno: 2942 [merge]
revision-id: igor at askmonty.org-20100923170347-evhjbbfspfe2pc19
parent: igor at askmonty.org-20100920193941-kirxqacsw500ihc1
parent: igor at askmonty.org-20100921042200-o52vwhnkz6kuwrcn
committer: Igor Babaev <igor at askmonty.org>
branch nick: maria-5.1
timestamp: Thu 2010-09-23 10:03:47 -0700
message:
  Merge
modified:
  mysql-test/r/join_outer.result sp1f-join_outer.result-20001228015633-vk2jshiracfus3ze2d2bim2csnnrc5us
  mysql-test/t/join_outer.test   sp1f-join_outer.test-20001228015636-himrcptylaquy6l5d7pl7pawom3ytmtw
  sql/item.h                     sp1f-item.h-19700101030959-rrkb43htudd62batmoteashkebcwykpa
-------------- next part --------------
=== modified file 'mysql-test/r/join_outer.result'
--- a/mysql-test/r/join_outer.result	2010-09-11 15:20:35 +0000
+++ b/mysql-test/r/join_outer.result	2010-09-21 04:22:00 +0000
@@ -1411,4 +1411,45 @@
 a	b	a	b
 1	NULL	NULL	NULL
 drop table t1,t2;
+#
+# Bug#53161: outer join in the derived table is erroneously converted
+#            into an inner join for a query with a group by clause
+# 
+create table t1 (pk int not null primary key, a int not null);
+create table t2 like t1;
+create table t3 like t1;
+create table t4 (pk int not null primary key);
+insert into t1 values (1000, 1), (1001, 1);
+insert into t2 values (2000, 2), (2001, 2);
+insert into t3 values (3000, 3), (3001, 2);
+insert into t4 values (4000), (4001);
+set @save_optimizer_switch=@@optimizer_switch;
+set @@optimizer_switch='table_elimination=off';
+explain extended
+select t2.pk, 
+(select t3.pk 
+from t3 left join t4 on t4.pk=t3.pk 
+where t3.pk=t2.pk+1000 limit 1 ) as t
+from t1,t2 
+where t2.pk=t1.pk+1000 and t1.pk>1000
+group by t2.pk;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1	index	PRIMARY	PRIMARY	4	NULL	2	100.00	Using where; Using index; Using temporary; Using filesort
+1	PRIMARY	t2	eq_ref	PRIMARY	PRIMARY	4	func	1	100.00	Using where; Using index
+2	DEPENDENT SUBQUERY	t3	eq_ref	PRIMARY	PRIMARY	4	func	1	100.00	Using where; Using index
+2	DEPENDENT SUBQUERY	t4	eq_ref	PRIMARY	PRIMARY	4	test.t3.pk	1	100.00	Using where; Using index
+Warnings:
+Note	1276	Field or reference 'test.t2.pk' of SELECT #2 was resolved in SELECT #1
+Note	1003	select `test`.`t2`.`pk` AS `pk`,(select `test`.`t3`.`pk` from `test`.`t3` left join `test`.`t4` on((`test`.`t4`.`pk` = `test`.`t3`.`pk`)) where (`test`.`t3`.`pk` = (`test`.`t2`.`pk` + 1000)) limit 1) AS `t` from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`pk` = (`test`.`t1`.`pk` + 1000)) and (`test`.`t1`.`pk` > 1000)) group by `test`.`t2`.`pk`
+select t2.pk, 
+(select t3.pk 
+from t3 left join t4 on t4.pk=t3.pk 
+where t3.pk=t2.pk+1000 limit 1 ) as t
+from t1,t2 
+where t2.pk=t1.pk+1000 and t1.pk>1000
+group by t2.pk;
+pk	t
+2001	3001
+set @@optimizer_switch=@save_optimizer_switch;
+drop table t1,t2,t3,t4;
 End of 5.1 tests

=== modified file 'mysql-test/t/join_outer.test'
--- a/mysql-test/t/join_outer.test	2010-09-11 15:20:35 +0000
+++ b/mysql-test/t/join_outer.test	2010-09-21 04:22:00 +0000
@@ -996,4 +996,42 @@
 
 drop table t1,t2;
 
+--echo #
+--echo # Bug#53161: outer join in the derived table is erroneously converted
+--echo #            into an inner join for a query with a group by clause
+--echo # 
+
+create table t1 (pk int not null primary key, a int not null);
+create table t2 like t1;
+create table t3 like t1;
+create table t4 (pk int not null primary key);
+insert into t1 values (1000, 1), (1001, 1);
+insert into t2 values (2000, 2), (2001, 2);
+insert into t3 values (3000, 3), (3001, 2);
+insert into t4 values (4000), (4001);
+
+set @save_optimizer_switch=@@optimizer_switch;
+set @@optimizer_switch='table_elimination=off';
+
+explain extended
+select t2.pk, 
+      (select t3.pk 
+         from t3 left join t4 on t4.pk=t3.pk 
+           where t3.pk=t2.pk+1000 limit 1 ) as t
+  from t1,t2 
+    where t2.pk=t1.pk+1000 and t1.pk>1000
+  group by t2.pk;
+
+select t2.pk, 
+      (select t3.pk 
+         from t3 left join t4 on t4.pk=t3.pk 
+           where t3.pk=t2.pk+1000 limit 1 ) as t
+  from t1,t2 
+    where t2.pk=t1.pk+1000 and t1.pk>1000
+  group by t2.pk;
+
+set @@optimizer_switch=@save_optimizer_switch;
+
+drop table t1,t2,t3,t4;
+
 --echo End of 5.1 tests

=== modified file 'sql/item.h'
--- a/sql/item.h	2010-08-27 14:12:44 +0000
+++ b/sql/item.h	2010-09-21 04:22:00 +0000
@@ -2505,6 +2505,7 @@
   {
     return (*ref)->const_item() ? 0 : OUTER_REF_TABLE_BIT;
   }
+  table_map not_null_tables() const { return 0; }
   virtual Ref_Type ref_type() { return OUTER_REF; }
   bool check_inner_refs_processor(uchar * arg); 
 };



More information about the commits mailing list