[Commits] Rev 3088: Back-ported the test cases for bug #12763207 from mysql-5.6 code line into 5.2 in file:///home/igor/maria/maria-5.2/

Igor Babaev igor at askmonty.org
Fri Jan 13 22:23:20 EET 2012


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

------------------------------------------------------------
revno: 3088
revision-id: igor at askmonty.org-20120113202319-8u2exriofkfz6dz4
parent: wlad at montyprogram.com-20120110182647-th1vy0dlpzes4gzh
committer: Igor Babaev <igor at askmonty.org>
branch nick: maria-5.2
timestamp: Fri 2012-01-13 12:23:19 -0800
message:
  Back-ported the test cases for bug #12763207 from mysql-5.6 code line into 5.2
  Completed the fix for this bug.
  Note: in 5.3 the affected 'if' statement in Item_in_subselect::single_value_transformer()
  starting with the  condition (thd->variables.sql_mode & MODE_ONLY_FULL_GROUP_BY)
  should be removed altogether. The change from table.cc is not needed either.
  This is because in 5.3
   - min/max transformation for subqueries are done at the optimization phase
   - evaluation of the expensive subqueries is done at the execution phase.
  
  Added an EXPLAIN EXTENDED to the test case for bug #12329653. 
-------------- next part --------------
=== modified file 'mysql-test/r/subselect.result'
--- a/mysql-test/r/subselect.result	2011-12-27 21:19:13 +0000
+++ b/mysql-test/r/subselect.result	2012-01-13 20:23:19 +0000
@@ -4568,6 +4568,13 @@
 INSERT INTO t1 VALUES (1),(2);
 SELECT @@session.sql_mode INTO @old_sql_mode;
 SET SESSION sql_mode='ONLY_FULL_GROUP_BY';
+EXPLAIN EXTENDED
+SELECT 1 FROM t1 WHERE 1 < SOME (SELECT a1 FROM t1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
+2	SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
+Warnings:
+Note	1003	select 1 AS `1` from `test`.`t1` where 1
 SELECT 1 FROM t1 WHERE 1 < SOME (SELECT a1 FROM t1);
 1
 1
@@ -5088,4 +5095,50 @@
 NULL
 5
 DROP TABLE t1, t2, t3;
+#
+# Bug#12763207 - ASSERT IN SUBSELECT::SINGLE_VALUE_TRANSFORMER
+#
+CREATE TABLE t1(a1 int);
+INSERT INTO t1 VALUES (1),(2);
+CREATE TABLE t2(a1 int);
+INSERT INTO t2 VALUES (3);
+SELECT @@session.sql_mode INTO @old_sql_mode;
+SET SESSION sql_mode='ONLY_FULL_GROUP_BY';
+SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 2 FROM t2);
+1
+1
+1
+SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 2.0 FROM t2);
+1
+1
+1
+SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 'a' FROM t2);
+1
+SELECT 1 FROM t1 WHERE 1 < SOME (SELECT a1 FROM t2);
+1
+1
+1
+SET SESSION sql_mode=@old_sql_mode;
+DROP TABLE t1, t2;
+create table t2(i int);
+insert into t2 values(0);
+SELECT @@session.sql_mode INTO @old_sql_mode;
+SET SESSION sql_mode='ONLY_FULL_GROUP_BY';
+CREATE VIEW v1 AS  
+SELECT 'f' FROM t2 UNION SELECT 'x' FROM t2
+;
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_varchar_key varchar(1) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY col_varchar_key (col_varchar_key)
+);
+SELECT t1.pk
+FROM t1
+WHERE t1.col_varchar_key < ALL ( SELECT * FROM v1 )
+;
+pk
+SET SESSION sql_mode=@old_sql_mode;
+drop table t2, t1;
+drop view v1;
 End of 5.2 tests

=== modified file 'mysql-test/t/subselect.test'
--- a/mysql-test/t/subselect.test	2011-11-24 16:48:58 +0000
+++ b/mysql-test/t/subselect.test	2012-01-13 20:23:19 +0000
@@ -3460,6 +3460,8 @@
 
 ## First a simpler query, illustrating the transformation
 ## '1 < some (...)' => '1 < max(...)'
+EXPLAIN EXTENDED
+SELECT 1 FROM t1 WHERE 1 < SOME (SELECT a1 FROM t1);
 SELECT 1 FROM t1 WHERE 1 < SOME (SELECT a1 FROM t1);
 
 ## The query which made the server crash.
@@ -3958,4 +3960,55 @@
 SELECT a1.f3 AS r FROM t2 AS a1 , t1 WHERE a1.f3 < ALL ( SELECT f3 FROM t3 WHERE f3 = 1 ) ;
 DROP TABLE t1, t2, t3;
 
+--echo #
+--echo # Bug#12763207 - ASSERT IN SUBSELECT::SINGLE_VALUE_TRANSFORMER
+--echo #
+
+CREATE TABLE t1(a1 int);
+INSERT INTO t1 VALUES (1),(2);
+
+CREATE TABLE t2(a1 int);
+INSERT INTO t2 VALUES (3);
+
+SELECT @@session.sql_mode INTO @old_sql_mode;
+SET SESSION sql_mode='ONLY_FULL_GROUP_BY';
+
+## All these are subject to the transformation
+## '1 < some (...)' => '1 < max(...)'
+SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 2 FROM t2);
+SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 2.0 FROM t2);
+SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 'a' FROM t2);
+SELECT 1 FROM t1 WHERE 1 < SOME (SELECT a1 FROM t2);
+
+SET SESSION sql_mode=@old_sql_mode;
+
+DROP TABLE t1, t2;
+
+create table t2(i int);
+insert into t2 values(0);
+
+SELECT @@session.sql_mode INTO @old_sql_mode;
+SET SESSION sql_mode='ONLY_FULL_GROUP_BY';
+
+CREATE VIEW v1 AS  
+SELECT 'f' FROM t2 UNION SELECT 'x' FROM t2
+;
+
+CREATE TABLE t1 (
+  pk int NOT NULL,
+  col_varchar_key varchar(1) DEFAULT NULL,
+  PRIMARY KEY (pk),
+  KEY col_varchar_key (col_varchar_key)
+);
+
+SELECT t1.pk
+FROM t1
+WHERE t1.col_varchar_key < ALL ( SELECT * FROM v1 )
+;
+
+SET SESSION sql_mode=@old_sql_mode;
+
+drop table t2, t1;
+drop view v1;
+
 --echo End of 5.2 tests

=== modified file 'sql/item_subselect.cc'
--- a/sql/item_subselect.cc	2011-12-27 21:19:13 +0000
+++ b/sql/item_subselect.cc	2012-01-13 20:23:19 +0000
@@ -1057,7 +1057,8 @@
                    print_where(item, "rewrite with MIN/MAX", QT_ORDINARY););
       if (thd->variables.sql_mode & MODE_ONLY_FULL_GROUP_BY)
       {
-        DBUG_ASSERT(select_lex->non_agg_field_used());
+        DBUG_ASSERT(item->get_arg(0)->real_item()->type() != Item::FIELD_ITEM ||
+                    select_lex->non_agg_field_used());
         select_lex->set_non_agg_field_used(false);
       }
 

=== modified file 'sql/table.cc'
--- a/sql/table.cc	2011-12-20 09:56:41 +0000
+++ b/sql/table.cc	2012-01-13 20:23:19 +0000
@@ -4467,6 +4467,7 @@
   {
     select->non_agg_fields.push_back(item);
     item->marker= select->cur_pos_in_select_list;
+    select->set_non_agg_field_used(true);
   }
   return item;
 }



More information about the commits mailing list