[Commits] Rev 3157: Fixed LP bug #824463. in file:///home/igor/maria/maria-5.3-bug824463/

Igor Babaev igor at askmonty.org
Tue Aug 16 09:18:37 EEST 2011


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

------------------------------------------------------------
revno: 3157
revision-id: igor at askmonty.org-20110816061836-oixd1knk4uz4qoyd
parent: monty at askmonty.org-20110815191408-jo0wlbi2u869bl8z
committer: Igor Babaev <igor at askmonty.org>
branch nick: maria-5.3-bug824463
timestamp: Mon 2011-08-15 23:18:36 -0700
message:
  Fixed LP bug #824463.
  When merging a view / derived table the function SELECT_LEX::merge_subquery
  incorrectly updated the list SELECT_LEX::leaf_tables. Erroneously it
  appended the leaf_tables list of the merged object L and then removed the
  reference to the merged object T from the SELECT_LEX::leaf_tables list.
  A correct implementation should insert the list L into the
  SELECT_LEX::leaf_tables list in place of the element of the list that 
  refers to T.
  The bug could lead to wrong results or even crashes for queries with
  nested outer joins over views / derived tables.
-------------- next part --------------
=== modified file 'mysql-test/r/derived_view.result'
--- a/mysql-test/r/derived_view.result	2011-08-12 05:34:41 +0000
+++ b/mysql-test/r/derived_view.result	2011-08-16 06:18:36 +0000
@@ -704,8 +704,8 @@
 GROUP BY t.a;
 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	Using temporary; Using filesort
+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
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
 Warnings:
 Note	1003	select `test`.`t2`.`a` AS `a` from `test`.`t1` left join (`test`.`t2` join `test`.`t3`) on(((`test`.`t2`.`a` >= 1) and (`test`.`t3`.`b` > 5))) where 1 group by `test`.`t2`.`a`
 SELECT t.a FROM t1 LEFT JOIN
@@ -720,8 +720,8 @@
 GROUP BY t.a;
 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	Using temporary; Using filesort
+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
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
 Warnings:
 Note	1003	select `test`.`t2`.`a` AS `a` from `test`.`t1` left join (`test`.`t2` join `test`.`t3`) on(((`test`.`t2`.`a` >= 1) and (`test`.`t3`.`b` > 5))) where 1 group by `test`.`t2`.`a`
 SELECT t.a FROM t1 LEFT JOIN
@@ -1084,8 +1084,8 @@
 SELECT t.a,t.b FROM t3 RIGHT JOIN ((SELECT * FROM t1) AS t, t2) ON t2.b != 0 
 WHERE (t.a,t.b) NOT IN (SELECT 7, 5);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	100.00	
 1	PRIMARY	t2	system	NULL	NULL	NULL	NULL	1	100.00	
-1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	100.00	
 1	PRIMARY	t3	system	NULL	NULL	NULL	NULL	1	100.00	
 3	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 Warnings:
@@ -1098,8 +1098,8 @@
 SELECT t.a,t.b FROM t3 RIGHT JOIN (v1 AS t, t2) ON t2.b != 0 
 WHERE (t.a,t.b) NOT IN (SELECT 7, 5);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	100.00	
 1	PRIMARY	t2	system	NULL	NULL	NULL	NULL	1	100.00	
-1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	100.00	
 1	PRIMARY	t3	system	NULL	NULL	NULL	NULL	1	100.00	
 2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 Warnings:
@@ -1270,4 +1270,154 @@
 r
 p
 DROP TABLE t1,t2,t3;
+#
+# LP bug #824463: nested outer join using a merged view
+#                 as an inner table
+#
+CREATE TABLE t1 (b int, a int) ;
+CREATE TABLE t2 (a int) ;
+INSERT INTO t2 VALUES (5), (6);
+CREATE TABLE t3 (a int , c int) ;
+INSERT INTO t3 VALUES (22,1), (23,-1);
+CREATE TABLE t4 (a int);
+CREATE TABLE t5 (d int) ;
+INSERT INTO t5 VALUES (0), (7), (3), (5);
+CREATE VIEW v2 AS SELECT * FROM t2;
+CREATE VIEW v3 AS SELECT * FROM t3;
+EXPLAIN EXTENDED
+SELECT STRAIGHT_JOIN *
+FROM ( t2 AS s2
+JOIN
+( t3 AS s3
+LEFT JOIN
+( t4 LEFT JOIN t3 ON t4.a != 0 )
+ON s3.a != 0)
+ON s2.a != 0)
+JOIN t5 ON s3.c != 0 AND t5.d = 0;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	s2	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+1	SIMPLE	s3	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (flat, BNL join)
+1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	0	0.00	Using where
+1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+1	SIMPLE	t5	ALL	NULL	NULL	NULL	NULL	4	100.00	Using where; Using join buffer (flat, BNL join)
+Warnings:
+Note	1003	select straight_join `test`.`s2`.`a` AS `a`,`test`.`s3`.`a` AS `a`,`test`.`s3`.`c` AS `c`,`test`.`t4`.`a` AS `a`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`c` AS `c`,`test`.`t5`.`d` AS `d` from `test`.`t2` `s2` join `test`.`t3` `s3` left join (`test`.`t4` left join `test`.`t3` on((`test`.`t4`.`a` <> 0))) on((`test`.`s3`.`a` <> 0)) join `test`.`t5` where ((`test`.`t5`.`d` = 0) and (`test`.`s3`.`c` <> 0) and (`test`.`s2`.`a` <> 0))
+SELECT STRAIGHT_JOIN *
+FROM ( t2 AS s2
+JOIN
+( t3 AS s3
+LEFT JOIN
+( t4 LEFT JOIN t3 ON t4.a != 0 )
+ON s3.a != 0)
+ON s2.a != 0)
+JOIN t5 ON s3.c != 0 AND t5.d = 0;
+a	a	c	a	a	c	d
+5	22	1	NULL	NULL	NULL	0
+6	22	1	NULL	NULL	NULL	0
+5	23	-1	NULL	NULL	NULL	0
+6	23	-1	NULL	NULL	NULL	0
+EXPLAIN EXTENDED
+SELECT STRAIGHT_JOIN *
+FROM t2 AS s2 , t5,
+(t3 LEFT JOIN (t4 LEFT JOIN t3 AS s3 ON t4.a != 0) ON t3.a != 0)
+WHERE s2.a != 0 AND t3.c != 0 AND t5.d = 0;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	s2	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+1	SIMPLE	t5	ALL	NULL	NULL	NULL	NULL	4	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 (flat, BNL join)
+1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	0	0.00	Using where
+1	SIMPLE	s3	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+Warnings:
+Note	1003	select straight_join `test`.`s2`.`a` AS `a`,`test`.`t5`.`d` AS `d`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`c` AS `c`,`test`.`t4`.`a` AS `a`,`test`.`s3`.`a` AS `a`,`test`.`s3`.`c` AS `c` from `test`.`t2` `s2` join `test`.`t5` join `test`.`t3` left join (`test`.`t4` left join `test`.`t3` `s3` on((`test`.`t4`.`a` <> 0))) on((`test`.`t3`.`a` <> 0)) where ((`test`.`t5`.`d` = 0) and (`test`.`s2`.`a` <> 0) and (`test`.`t3`.`c` <> 0))
+SELECT STRAIGHT_JOIN *
+FROM t2 AS s2 , t5,
+(t3 LEFT JOIN (t4 LEFT JOIN t3 AS s3 ON t4.a != 0) ON t3.a != 0)
+WHERE s2.a != 0 AND t3.c != 0 AND t5.d = 0;
+a	d	a	c	a	a	c
+5	0	22	1	NULL	NULL	NULL
+6	0	22	1	NULL	NULL	NULL
+5	0	23	-1	NULL	NULL	NULL
+6	0	23	-1	NULL	NULL	NULL
+EXPLAIN EXTENDED
+SELECT STRAIGHT_JOIN *
+FROM v2 AS s2 , t5,
+(t3 LEFT JOIN (t4 LEFT JOIN v3 AS s3 ON t4.a != 0) ON t3.a != 0)
+WHERE s2.a != 0 AND t3.c != 0 AND t5.d = 0;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+1	SIMPLE	t5	ALL	NULL	NULL	NULL	NULL	4	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 (flat, BNL join)
+1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	0	0.00	Using where
+1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+Warnings:
+Note	1003	select straight_join `test`.`t2`.`a` AS `a`,`test`.`t5`.`d` AS `d`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`c` AS `c`,`test`.`t4`.`a` AS `a`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`c` AS `c` from `test`.`t2` join `test`.`t5` join `test`.`t3` left join (`test`.`t4` left join (`test`.`t3`) on((`test`.`t4`.`a` <> 0))) on((`test`.`t3`.`a` <> 0)) where ((`test`.`t5`.`d` = 0) and (`test`.`t2`.`a` <> 0) and (`test`.`t3`.`c` <> 0))
+SELECT STRAIGHT_JOIN *
+FROM v2 AS s2 , t5,
+(t3 LEFT JOIN (t4 LEFT JOIN v3 AS s3 ON t4.a != 0) ON t3.a != 0)
+WHERE s2.a != 0 AND t3.c != 0 AND t5.d = 0;
+a	d	a	c	a	a	c
+5	0	22	1	NULL	NULL	NULL
+6	0	22	1	NULL	NULL	NULL
+5	0	23	-1	NULL	NULL	NULL
+6	0	23	-1	NULL	NULL	NULL
+SELECT STRAIGHT_JOIN *
+FROM ( ( t2 AS s2
+LEFT JOIN
+( t3 AS s3
+LEFT JOIN
+( t4 AS s4 JOIN t3 ON s4.a != 0)
+ON s3.a != 0 )
+ON s2.a != 0)
+LEFT JOIN 
+t1 AS s1  
+ON s1.a != 0)
+JOIN t5 ON s3.c != 0;
+a	a	c	a	a	c	b	a	d
+5	22	1	NULL	NULL	NULL	NULL	NULL	0
+6	22	1	NULL	NULL	NULL	NULL	NULL	0
+5	23	-1	NULL	NULL	NULL	NULL	NULL	0
+6	23	-1	NULL	NULL	NULL	NULL	NULL	0
+5	22	1	NULL	NULL	NULL	NULL	NULL	7
+6	22	1	NULL	NULL	NULL	NULL	NULL	7
+5	23	-1	NULL	NULL	NULL	NULL	NULL	7
+6	23	-1	NULL	NULL	NULL	NULL	NULL	7
+5	22	1	NULL	NULL	NULL	NULL	NULL	3
+6	22	1	NULL	NULL	NULL	NULL	NULL	3
+5	23	-1	NULL	NULL	NULL	NULL	NULL	3
+6	23	-1	NULL	NULL	NULL	NULL	NULL	3
+5	22	1	NULL	NULL	NULL	NULL	NULL	5
+6	22	1	NULL	NULL	NULL	NULL	NULL	5
+5	23	-1	NULL	NULL	NULL	NULL	NULL	5
+6	23	-1	NULL	NULL	NULL	NULL	NULL	5
+SELECT STRAIGHT_JOIN *
+FROM ( ( v2 AS s2
+LEFT JOIN
+( v3 AS s3
+LEFT JOIN
+( t4 AS s4 JOIN v3 ON s4.a != 0)
+ON s3.a != 0 )
+ON s2.a != 0)
+LEFT JOIN 
+t1 AS s1  
+ON s1.a != 0)
+JOIN t5 ON s3.c != 0;
+a	a	c	a	a	c	b	a	d
+5	22	1	NULL	NULL	NULL	NULL	NULL	0
+6	22	1	NULL	NULL	NULL	NULL	NULL	0
+5	23	-1	NULL	NULL	NULL	NULL	NULL	0
+6	23	-1	NULL	NULL	NULL	NULL	NULL	0
+5	22	1	NULL	NULL	NULL	NULL	NULL	7
+6	22	1	NULL	NULL	NULL	NULL	NULL	7
+5	23	-1	NULL	NULL	NULL	NULL	NULL	7
+6	23	-1	NULL	NULL	NULL	NULL	NULL	7
+5	22	1	NULL	NULL	NULL	NULL	NULL	3
+6	22	1	NULL	NULL	NULL	NULL	NULL	3
+5	23	-1	NULL	NULL	NULL	NULL	NULL	3
+6	23	-1	NULL	NULL	NULL	NULL	NULL	3
+5	22	1	NULL	NULL	NULL	NULL	NULL	5
+6	22	1	NULL	NULL	NULL	NULL	NULL	5
+5	23	-1	NULL	NULL	NULL	NULL	NULL	5
+6	23	-1	NULL	NULL	NULL	NULL	NULL	5
+DROP VIEW v2,v3;
+DROP TABLE t1,t2,t3,t4,t5;
 set optimizer_switch=@exit_optimizer_switch;

=== modified file 'mysql-test/r/ps_ddl.result'
--- a/mysql-test/r/ps_ddl.result	2011-05-17 05:39:43 +0000
+++ b/mysql-test/r/ps_ddl.result	2011-08-16 06:18:36 +0000
@@ -1508,12 +1508,12 @@
 execute stmt;
 a	b	a	b
 1	1	1	1
-1	1	2	2
 2	2	1	1
-2	2	2	2
 1	1	1	1
-1	1	2	2
 2	2	1	1
+1	1	2	2
+2	2	2	2
+1	1	2	2
 2	2	2	2
 call p_verify_reprepare_count(1);
 SUCCESS
@@ -1521,12 +1521,12 @@
 execute stmt;
 a	b	a	b
 1	1	1	1
-1	1	2	2
 2	2	1	1
-2	2	2	2
 1	1	1	1
-1	1	2	2
 2	2	1	1
+1	1	2	2
+2	2	2	2
+1	1	2	2
 2	2	2	2
 call p_verify_reprepare_count(0);
 SUCCESS

=== modified file 'mysql-test/t/derived_view.test'
--- a/mysql-test/t/derived_view.test	2011-08-12 05:34:41 +0000
+++ b/mysql-test/t/derived_view.test	2011-08-16 06:18:36 +0000
@@ -783,5 +783,94 @@
 
 DROP TABLE t1,t2,t3;
 
+--echo #
+--echo # LP bug #824463: nested outer join using a merged view
+--echo #                 as an inner table
+--echo #
+
+CREATE TABLE t1 (b int, a int) ;
+
+CREATE TABLE t2 (a int) ;
+INSERT INTO t2 VALUES (5), (6);
+
+CREATE TABLE t3 (a int , c int) ;
+INSERT INTO t3 VALUES (22,1), (23,-1);
+
+CREATE TABLE t4 (a int);
+
+CREATE TABLE t5 (d int) ;
+INSERT INTO t5 VALUES (0), (7), (3), (5);
+
+CREATE VIEW v2 AS SELECT * FROM t2;
+CREATE VIEW v3 AS SELECT * FROM t3;
+
+EXPLAIN EXTENDED
+SELECT STRAIGHT_JOIN *
+  FROM ( t2 AS s2
+         JOIN
+         ( t3 AS s3
+           LEFT JOIN
+           ( t4 LEFT JOIN t3 ON t4.a != 0 )
+           ON s3.a != 0)
+         ON s2.a != 0)
+       JOIN t5 ON s3.c != 0 AND t5.d = 0;
+SELECT STRAIGHT_JOIN *
+  FROM ( t2 AS s2
+         JOIN
+         ( t3 AS s3
+           LEFT JOIN
+           ( t4 LEFT JOIN t3 ON t4.a != 0 )
+           ON s3.a != 0)
+         ON s2.a != 0)
+       JOIN t5 ON s3.c != 0 AND t5.d = 0;
+
+EXPLAIN EXTENDED
+SELECT STRAIGHT_JOIN *
+  FROM t2 AS s2 , t5,
+       (t3 LEFT JOIN (t4 LEFT JOIN t3 AS s3 ON t4.a != 0) ON t3.a != 0)
+  WHERE s2.a != 0 AND t3.c != 0 AND t5.d = 0;
+SELECT STRAIGHT_JOIN *
+  FROM t2 AS s2 , t5,
+       (t3 LEFT JOIN (t4 LEFT JOIN t3 AS s3 ON t4.a != 0) ON t3.a != 0)
+  WHERE s2.a != 0 AND t3.c != 0 AND t5.d = 0;
+
+EXPLAIN EXTENDED
+SELECT STRAIGHT_JOIN *
+  FROM v2 AS s2 , t5,
+       (t3 LEFT JOIN (t4 LEFT JOIN v3 AS s3 ON t4.a != 0) ON t3.a != 0)
+  WHERE s2.a != 0 AND t3.c != 0 AND t5.d = 0;
+SELECT STRAIGHT_JOIN *
+  FROM v2 AS s2 , t5,
+       (t3 LEFT JOIN (t4 LEFT JOIN v3 AS s3 ON t4.a != 0) ON t3.a != 0)
+  WHERE s2.a != 0 AND t3.c != 0 AND t5.d = 0;
+
+SELECT STRAIGHT_JOIN *
+  FROM ( ( t2 AS s2
+           LEFT JOIN
+           ( t3 AS s3
+             LEFT JOIN
+             ( t4 AS s4 JOIN t3 ON s4.a != 0)
+             ON s3.a != 0 )
+           ON s2.a != 0)
+         LEFT JOIN 
+         t1 AS s1  
+       ON s1.a != 0)
+       JOIN t5 ON s3.c != 0;
+SELECT STRAIGHT_JOIN *
+  FROM ( ( v2 AS s2
+           LEFT JOIN
+           ( v3 AS s3
+             LEFT JOIN
+             ( t4 AS s4 JOIN v3 ON s4.a != 0)
+             ON s3.a != 0 )
+           ON s2.a != 0)
+         LEFT JOIN 
+         t1 AS s1  
+       ON s1.a != 0)
+       JOIN t5 ON s3.c != 0;
+
+DROP VIEW v2,v3;
+DROP TABLE t1,t2,t3,t4,t5;
+
 # The following command must be the last one the file 
 set optimizer_switch=@exit_optimizer_switch;

=== modified file 'sql/sql_lex.cc'
--- a/sql/sql_lex.cc	2011-07-19 20:19:10 +0000
+++ b/sql/sql_lex.cc	2011-08-16 06:18:36 +0000
@@ -3220,18 +3220,19 @@
   tl->*link= table;
 }
 
+
 /*
   @brief
-  Remove given table from the leaf_tables list.
+  Replace given table from the leaf_tables list for a list of tables 
 
-  @param link  Offset to which list in table structure to use
-  @param table Table to remove
+  @param table Table to replace
+  @param list  List to substititute the table for
 
   @details
-  Remove 'table' from the leaf_tables list using the 'link' offset.
+  Replace 'table' from the leaf_tables list for a list of tables 'tbl_list'.
 */
 
-void st_select_lex::remove_table_from_list(TABLE_LIST *table)
+void st_select_lex::replace_leaf_table(TABLE_LIST *table, List<TABLE_LIST> &tbl_list)
 {
   TABLE_LIST *tl;
   List_iterator<TABLE_LIST> ti(leaf_tables);
@@ -3239,7 +3240,7 @@
   {
     if (tl == table)
     {
-      ti.remove();
+      ti.replace(tbl_list);
       break;
     }
   }
@@ -3344,8 +3345,6 @@
                                 uint table_no, table_map map)
 {
   derived->wrap_into_nested_join(subq_select->top_join_list);
-  /* Reconnect the next_leaf chain. */
-  leaf_tables.concat(&subq_select->leaf_tables);
 
   ftfunc_list->concat(subq_select->ftfunc_list);
   if (join ||
@@ -3361,18 +3360,14 @@
          in_subq->emb_on_expr_nest= derived;
     }
   }
-  /*
-    Remove merged table from chain.
-    When merge_subquery is called at a subquery-to-semijoin transformation
-    the derived isn't in the leaf_tables list, so in this case the call of
-    remove_table_from_list does not cause any actions.
-  */
-  remove_table_from_list(derived);
 
   /* Walk through child's tables and adjust table map, tablenr,
    * parent_lex */
   subq_select->remap_tables(derived, map, table_no, this);
   subq_select->merged_into= this;
+
+  replace_leaf_table(derived, subq_select->leaf_tables);
+
   return FALSE;
 }
 

=== modified file 'sql/sql_lex.h'
--- a/sql/sql_lex.h	2011-07-19 20:19:10 +0000
+++ b/sql/sql_lex.h	2011-08-16 06:18:36 +0000
@@ -888,7 +888,7 @@
   bool handle_derived(struct st_lex *lex, uint phases);
   void append_table_to_list(TABLE_LIST *TABLE_LIST::*link, TABLE_LIST *table);
   bool get_free_table_map(table_map *map, uint *tablenr);
-  void remove_table_from_list(TABLE_LIST *table);
+  void replace_leaf_table(TABLE_LIST *table, List<TABLE_LIST> &tbl_list);
   void remap_tables(TABLE_LIST *derived, table_map map,
                     uint tablenr, st_select_lex *parent_lex);
   bool merge_subquery(THD *thd, TABLE_LIST *derived, st_select_lex *subq_lex,



More information about the commits mailing list