bzr push into mysql-5.1 branch (tor.didriksen:3714 to 3715) Bug#12763207

[Tor Didriksen]

 3715 Tor Didriksen	2012-03-27
      Backport of fix for Bug#12763207 - ASSERT IN SUBSELECT::SINGLE_VALUE_TRANSFORMER

    modified:
      mysql-test/r/subselect.result
      mysql-test/t/subselect.test
      sql/item_subselect.cc
      sql/table.cc
 3714 Praveenkumar Hulakund	2012-03-27
      Bug#11763507 - 56224: FUNCTION NAME IS CASE-SENSITIVE
      
      Analysis:
      -------------------------------
      According to the Manual
      (http://dev.mysql.com/doc/refman/5.1/en/identifier-case-sensitivity.html):
      "Column, index, stored routine, and event names are not case sensitive on any
      platform, nor are column aliases."
      
      In other words, 'lower_case_table_names' does not affect the behaviour of 
      those identifiers.
      
      On the other hand, trigger names are case sensitive on some platforms,
      and case insensitive on others. 'lower_case_table_names' does not affect
      the behaviour of trigger names either.
      
      The bug was that SHOW statements did case sensitive comparison
      for stored procedure / stored function / event names.
      
      Fix:
      Modified the code so that comparison in case insensitive for routines 
      and events for "SHOW" operation.

    modified:
      mysql-test/r/sp-bugs.result
      mysql-test/t/sp-bugs.test
      sql/sql_show.cc
=== modified file 'mysql-test/r/subselect.result'
--- a/mysql-test/r/subselect.result	2011-05-10 13:57:40 +0000
+++ b/mysql-test/r/subselect.result	2012-03-27 12:39:27 +0000
@@ -4461,6 +4461,55 @@ ERROR 21000: Subquery returns more than 
 SET SESSION sql_mode=@old_sql_mode;
 DEALLOCATE PREPARE stmt;
 DROP TABLE t1;
+#
+# 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;
+#
+# Bug#12763207 - ASSERT IN SUBSELECT::SINGLE_VALUE_TRANSFORMER
+#
+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.0 tests.
 CREATE TABLE t1 (a INT, b INT);
 INSERT INTO t1 VALUES (2,22),(1,11),(2,22);

=== modified file 'mysql-test/t/subselect.test'
--- a/mysql-test/t/subselect.test	2011-05-10 13:57:40 +0000
+++ b/mysql-test/t/subselect.test	2012-03-27 12:39:27 +0000
@@ -3427,6 +3427,61 @@ SET SESSION sql_mode=@old_sql_mode;
 DEALLOCATE PREPARE stmt;
 DROP TABLE t1;
 
+--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;
+
+--echo #
+--echo # Bug#12763207 - ASSERT IN SUBSELECT::SINGLE_VALUE_TRANSFORMER
+--echo #
+
+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.0 tests.
 
 #

=== modified file 'sql/item_subselect.cc'
--- a/sql/item_subselect.cc	2012-02-15 16:21:38 +0000
+++ b/sql/item_subselect.cc	2012-03-27 12:39:27 +0000
@@ -1020,7 +1020,13 @@ Item_in_subselect::single_value_transfor
                    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());
+        /*
+          If the argument is a field, we assume that fix_fields() has
+          tagged the select_lex with non_agg_field_used.
+          We reverse that decision after this rewrite with MIN/MAX.
+         */
+        if (item->get_arg(0)->type() == Item::FIELD_ITEM)
+          DBUG_ASSERT(select_lex->non_agg_field_used());
         select_lex->set_non_agg_field_used(false);
       }
 

=== modified file 'sql/table.cc'
--- a/sql/table.cc	2011-07-03 15:47:37 +0000
+++ b/sql/table.cc	2012-03-27 12:39:27 +0000
@@ -3979,6 +3979,7 @@ Item *Field_iterator_table::create_item(
   {
     select->non_agg_fields.push_back(item);
     item->marker= select->cur_pos_in_select_list;
+    select->set_non_agg_field_used(true);
   }
   return item;
 }

No bundle (reason: useless for push emails).