[Commits] Rev 3164: Fixed LP bug #826279. in file:///home/igor/maria/maria-5.3-bug826279/

Igor Babaev igor at askmonty.org
Sat Aug 20 07:02:06 EEST 2011


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

------------------------------------------------------------
revno: 3164
revision-id: igor at askmonty.org-20110820040205-1suikiokqfn8s81j
parent: timour at askmonty.org-20110817111032-nbegqxzq30iq7bim
committer: Igor Babaev <igor at askmonty.org>
branch nick: maria-5.3-bug826279
timestamp: Fri 2011-08-19 21:02:05 -0700
message:
  Fixed LP bug #826279.
  When the WHERE/HAVING condition of a subquery has been transformed
  by the optimizer the pointer stored the 'where'/'having' field 
  of the SELECT_LEX structure used for the subquery must be updated
  accordingly. Otherwise the pointer may refer to an invalid item.
  This can lead to the reported assertion failure for some queries
  with correlated subqueries   
-------------- next part --------------
=== modified file 'mysql-test/r/subselect.result'
--- a/mysql-test/r/subselect.result	2011-08-17 11:10:32 +0000
+++ b/mysql-test/r/subselect.result	2011-08-20 04:02:05 +0000
@@ -5338,4 +5338,29 @@
 1	PRIMARY	table2	index	NULL	f1_key	4	NULL	10	Using where; Using index; Using join buffer (flat, BNL join)
 2	DEPENDENT SUBQUERY	t2	range	f1_key	f1_key	4	NULL	6	Range checked for each record (index map: 0x1); Using temporary
 DROP TABLE t1,t2;
+#
+# LP bug #826279: assertion failure with GROUP BY a result of subquery
+#
+CREATE TABLE t1 (a int);
+INSERT INTO t1 VALUES (0), (0);
+CREATE TABLE t2 (a int, b int, c int);
+INSERT INTO t2 VALUES (10,7,0), (0,7,0);
+CREATE TABLE t3 (a int, b int);
+INSERT INTO t3 VALUES (10,7), (0,7);
+SELECT SUM(DISTINCT b),
+(SELECT t2.a FROM t1 JOIN t2 ON t2.c != 0
+WHERE t.a != 0 AND t2.a != 0)
+FROM (SELECT * FROM t3) AS t
+GROUP BY 2;
+SUM(DISTINCT b)	(SELECT t2.a FROM t1 JOIN t2 ON t2.c != 0
+WHERE t.a != 0 AND t2.a != 0)
+7	NULL
+SELECT SUM(DISTINCT b), 
+(SELECT t2.a FROM t1,t2 WHERE t.a != 0 or 1=2 LIMIT 1)
+FROM (SELECT * FROM t3) AS t
+GROUP BY 2;
+SUM(DISTINCT b)	(SELECT t2.a FROM t1,t2 WHERE t.a != 0 or 1=2 LIMIT 1)
+7	NULL
+7	10
+DROP TABLE t1,t2,t3;
 set optimizer_switch=@subselect_tmp;

=== modified file 'mysql-test/r/subselect_no_mat.result'
--- a/mysql-test/r/subselect_no_mat.result	2011-08-17 11:10:32 +0000
+++ b/mysql-test/r/subselect_no_mat.result	2011-08-20 04:02:05 +0000
@@ -5343,6 +5343,31 @@
 1	PRIMARY	table2	index	NULL	f1_key	4	NULL	10	Using where; Using index; Using join buffer (flat, BNL join)
 2	DEPENDENT SUBQUERY	t2	range	f1_key	f1_key	4	NULL	6	Range checked for each record (index map: 0x1); Using temporary
 DROP TABLE t1,t2;
+#
+# LP bug #826279: assertion failure with GROUP BY a result of subquery
+#
+CREATE TABLE t1 (a int);
+INSERT INTO t1 VALUES (0), (0);
+CREATE TABLE t2 (a int, b int, c int);
+INSERT INTO t2 VALUES (10,7,0), (0,7,0);
+CREATE TABLE t3 (a int, b int);
+INSERT INTO t3 VALUES (10,7), (0,7);
+SELECT SUM(DISTINCT b),
+(SELECT t2.a FROM t1 JOIN t2 ON t2.c != 0
+WHERE t.a != 0 AND t2.a != 0)
+FROM (SELECT * FROM t3) AS t
+GROUP BY 2;
+SUM(DISTINCT b)	(SELECT t2.a FROM t1 JOIN t2 ON t2.c != 0
+WHERE t.a != 0 AND t2.a != 0)
+7	NULL
+SELECT SUM(DISTINCT b), 
+(SELECT t2.a FROM t1,t2 WHERE t.a != 0 or 1=2 LIMIT 1)
+FROM (SELECT * FROM t3) AS t
+GROUP BY 2;
+SUM(DISTINCT b)	(SELECT t2.a FROM t1,t2 WHERE t.a != 0 or 1=2 LIMIT 1)
+7	NULL
+7	10
+DROP TABLE t1,t2,t3;
 set optimizer_switch=@subselect_tmp;
 set optimizer_switch=default;
 select @@optimizer_switch like '%materialization=on%';

=== modified file 'mysql-test/r/subselect_no_opts.result'
--- a/mysql-test/r/subselect_no_opts.result	2011-08-17 11:10:32 +0000
+++ b/mysql-test/r/subselect_no_opts.result	2011-08-20 04:02:05 +0000
@@ -5339,5 +5339,30 @@
 1	PRIMARY	table2	index	NULL	f1_key	4	NULL	10	Using where; Using index; Using join buffer (flat, BNL join)
 2	DEPENDENT SUBQUERY	t2	range	f1_key	f1_key	4	NULL	6	Range checked for each record (index map: 0x1); Using temporary
 DROP TABLE t1,t2;
+#
+# LP bug #826279: assertion failure with GROUP BY a result of subquery
+#
+CREATE TABLE t1 (a int);
+INSERT INTO t1 VALUES (0), (0);
+CREATE TABLE t2 (a int, b int, c int);
+INSERT INTO t2 VALUES (10,7,0), (0,7,0);
+CREATE TABLE t3 (a int, b int);
+INSERT INTO t3 VALUES (10,7), (0,7);
+SELECT SUM(DISTINCT b),
+(SELECT t2.a FROM t1 JOIN t2 ON t2.c != 0
+WHERE t.a != 0 AND t2.a != 0)
+FROM (SELECT * FROM t3) AS t
+GROUP BY 2;
+SUM(DISTINCT b)	(SELECT t2.a FROM t1 JOIN t2 ON t2.c != 0
+WHERE t.a != 0 AND t2.a != 0)
+7	NULL
+SELECT SUM(DISTINCT b), 
+(SELECT t2.a FROM t1,t2 WHERE t.a != 0 or 1=2 LIMIT 1)
+FROM (SELECT * FROM t3) AS t
+GROUP BY 2;
+SUM(DISTINCT b)	(SELECT t2.a FROM t1,t2 WHERE t.a != 0 or 1=2 LIMIT 1)
+7	NULL
+7	10
+DROP TABLE t1,t2,t3;
 set optimizer_switch=@subselect_tmp;
 set @optimizer_switch_for_subselect_test=null;

=== modified file 'mysql-test/r/subselect_no_semijoin.result'
--- a/mysql-test/r/subselect_no_semijoin.result	2011-08-17 11:10:32 +0000
+++ b/mysql-test/r/subselect_no_semijoin.result	2011-08-20 04:02:05 +0000
@@ -5339,5 +5339,30 @@
 1	PRIMARY	table2	index	NULL	f1_key	4	NULL	10	Using where; Using index; Using join buffer (flat, BNL join)
 2	DEPENDENT SUBQUERY	t2	range	f1_key	f1_key	4	NULL	6	Range checked for each record (index map: 0x1); Using temporary
 DROP TABLE t1,t2;
+#
+# LP bug #826279: assertion failure with GROUP BY a result of subquery
+#
+CREATE TABLE t1 (a int);
+INSERT INTO t1 VALUES (0), (0);
+CREATE TABLE t2 (a int, b int, c int);
+INSERT INTO t2 VALUES (10,7,0), (0,7,0);
+CREATE TABLE t3 (a int, b int);
+INSERT INTO t3 VALUES (10,7), (0,7);
+SELECT SUM(DISTINCT b),
+(SELECT t2.a FROM t1 JOIN t2 ON t2.c != 0
+WHERE t.a != 0 AND t2.a != 0)
+FROM (SELECT * FROM t3) AS t
+GROUP BY 2;
+SUM(DISTINCT b)	(SELECT t2.a FROM t1 JOIN t2 ON t2.c != 0
+WHERE t.a != 0 AND t2.a != 0)
+7	NULL
+SELECT SUM(DISTINCT b), 
+(SELECT t2.a FROM t1,t2 WHERE t.a != 0 or 1=2 LIMIT 1)
+FROM (SELECT * FROM t3) AS t
+GROUP BY 2;
+SUM(DISTINCT b)	(SELECT t2.a FROM t1,t2 WHERE t.a != 0 or 1=2 LIMIT 1)
+7	NULL
+7	10
+DROP TABLE t1,t2,t3;
 set optimizer_switch=@subselect_tmp;
 set @optimizer_switch_for_subselect_test=null;

=== modified file 'mysql-test/r/subselect_scache.result'
--- a/mysql-test/r/subselect_scache.result	2011-08-17 11:10:32 +0000
+++ b/mysql-test/r/subselect_scache.result	2011-08-20 04:02:05 +0000
@@ -5342,6 +5342,31 @@
 1	PRIMARY	table2	index	NULL	f1_key	4	NULL	10	Using where; Using index; Using join buffer (flat, BNL join)
 2	DEPENDENT SUBQUERY	t2	range	f1_key	f1_key	4	NULL	6	Range checked for each record (index map: 0x1); Using temporary
 DROP TABLE t1,t2;
+#
+# LP bug #826279: assertion failure with GROUP BY a result of subquery
+#
+CREATE TABLE t1 (a int);
+INSERT INTO t1 VALUES (0), (0);
+CREATE TABLE t2 (a int, b int, c int);
+INSERT INTO t2 VALUES (10,7,0), (0,7,0);
+CREATE TABLE t3 (a int, b int);
+INSERT INTO t3 VALUES (10,7), (0,7);
+SELECT SUM(DISTINCT b),
+(SELECT t2.a FROM t1 JOIN t2 ON t2.c != 0
+WHERE t.a != 0 AND t2.a != 0)
+FROM (SELECT * FROM t3) AS t
+GROUP BY 2;
+SUM(DISTINCT b)	(SELECT t2.a FROM t1 JOIN t2 ON t2.c != 0
+WHERE t.a != 0 AND t2.a != 0)
+7	NULL
+SELECT SUM(DISTINCT b), 
+(SELECT t2.a FROM t1,t2 WHERE t.a != 0 or 1=2 LIMIT 1)
+FROM (SELECT * FROM t3) AS t
+GROUP BY 2;
+SUM(DISTINCT b)	(SELECT t2.a FROM t1,t2 WHERE t.a != 0 or 1=2 LIMIT 1)
+7	NULL
+7	10
+DROP TABLE t1,t2,t3;
 set optimizer_switch=@subselect_tmp;
 set optimizer_switch=default;
 select @@optimizer_switch like '%subquery_cache=on%';

=== modified file 'mysql-test/t/subselect.test'
--- a/mysql-test/t/subselect.test	2011-08-17 11:10:32 +0000
+++ b/mysql-test/t/subselect.test	2011-08-20 04:02:05 +0000
@@ -4590,4 +4590,30 @@
 
 DROP TABLE t1,t2;
 
+--echo #
+--echo # LP bug #826279: assertion failure with GROUP BY a result of subquery
+--echo #
+
+CREATE TABLE t1 (a int);
+INSERT INTO t1 VALUES (0), (0);
+
+CREATE TABLE t2 (a int, b int, c int);
+INSERT INTO t2 VALUES (10,7,0), (0,7,0);
+
+CREATE TABLE t3 (a int, b int);
+INSERT INTO t3 VALUES (10,7), (0,7);
+
+SELECT SUM(DISTINCT b),
+       (SELECT t2.a FROM t1 JOIN t2 ON t2.c != 0
+          WHERE t.a != 0 AND t2.a != 0)
+  FROM (SELECT * FROM t3) AS t
+GROUP BY 2;
+
+SELECT SUM(DISTINCT b), 
+       (SELECT t2.a FROM t1,t2 WHERE t.a != 0 or 1=2 LIMIT 1)
+  FROM (SELECT * FROM t3) AS t
+GROUP BY 2;
+
+DROP TABLE t1,t2,t3;
+
 set optimizer_switch=@subselect_tmp;

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2011-08-17 07:57:01 +0000
+++ b/sql/sql_select.cc	2011-08-20 04:02:05 +0000
@@ -965,13 +965,16 @@
 
     sel->prep_where= conds ? conds->copy_andor_structure(thd) : 0;
 
+    sel->where= conds;
+
     if (arena)
       thd->restore_active_arena(arena, &backup);
   }
   
   inject_jtbm_conds(this, join_list, &conds);
 
-  conds= optimize_cond(this, conds, join_list, &cond_value, &cond_equal);   
+  conds= optimize_cond(this, conds, join_list, &cond_value, &cond_equal);
+     
   if (thd->is_error())
   {
     error= 1;
@@ -988,10 +991,17 @@
       DBUG_RETURN(1);
     }
     if (select_lex->where)
+    {
       select_lex->cond_value= cond_value;
+      if (sel->where != conds && cond_value == Item::COND_OK)
+        thd->change_item_tree(&sel->where, conds);
+    }  
     if (select_lex->having)
+    {
       select_lex->having_value= having_value;
-
+      if (sel->having != having && having_value == Item::COND_OK)
+        thd->change_item_tree(&sel->having, having);    
+    }
     if (cond_value == Item::COND_FALSE || having_value == Item::COND_FALSE || 
         (!unit->select_limit_cnt && !(select_options & OPTION_FOUND_ROWS)))
     {						/* Impossible cond */



More information about the commits mailing list