MySQL Lists are EOL. Please join:

List:Commits« Previous MessageNext Message »
From:Gleb Shchepa Date:November 12 2008 9:43pm
Subject:bzr commit into mysql-5.0-bugteam branch (gshchepa:2711) Bug#35311
View as plain text  
#At file:///work/bzr/5.0-bugteam-35311/

 2711 Gleb Shchepa	2008-11-13
      Bug #35311: ROW(...) IN (SELECT outer field,... FROM DUAL)"
                  always returns TRUE
      
      Queries like "SELECT ROW(1, 2) IN (SELECT a, 2) FROM t"
      always returned TRUE for non empty table t.
      Moreover, even ROW(1, 2) IN (SELECT a, 12345) returned
      TRUE.
      
      In the Item_in_subselect::row_value_transformer function it
      was incorrectly assumed, that an inner query always have
      HAVING clause if it doesn't have FROM clause (or has
      FROM DUAL clause).
      
      Needless condition has been removed.
modified:
  mysql-test/r/subselect3.result
  mysql-test/t/subselect3.test
  sql/item_subselect.cc

per-file messages:
  mysql-test/r/subselect3.result
    Added test case for bug #35311.
  mysql-test/t/subselect3.test
    Added test case for bug #35311.
  sql/item_subselect.cc
    Bug #35311: ROW(...) IN (SELECT outer field,... FROM DUAL)"
                always returns TRUE
    
    In the Item_in_subselect::row_value_transformer function it
    was incorrectly assumed, that an inner query always have
    HAVING clause if it doesn't have FROM clause (or has
    FROM DUAL clause).
    
    Needless condition has been removed.
=== modified file 'mysql-test/r/subselect3.result'
--- a/mysql-test/r/subselect3.result	2008-10-10 10:27:58 +0000
+++ b/mysql-test/r/subselect3.result	2008-11-12 21:43:00 +0000
@@ -795,4 +795,39 @@ WHERE INNR.varchar_key > 'n{'
 );
 varchar_nokey
 DROP TABLE t1;
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1), (2), (11);
+# 2nd and 3rd columns should be same
+SELECT a, ROW(11, 12) = (SELECT a, 22), ROW(11, 12) IN (SELECT a, 22) FROM t1 GROUP BY t1.a;
+a	ROW(11, 12) = (SELECT a, 22)	ROW(11, 12) IN (SELECT a, 22)
+1	0	1
+2	0	1
+11	0	1
+SELECT a, ROW(11, 12) = (SELECT a, 12), ROW(11, 12) IN (SELECT a, 12) FROM t1 GROUP BY t1.a;
+a	ROW(11, 12) = (SELECT a, 12)	ROW(11, 12) IN (SELECT a, 12)
+1	0	0
+2	0	0
+11	1	1
+SELECT a, ROW(11, 12) = (SELECT a, 22), ROW(11, 12) IN (SELECT a, 22) FROM t1;
+a	ROW(11, 12) = (SELECT a, 22)	ROW(11, 12) IN (SELECT a, 22)
+1	0	1
+2	0	1
+11	0	1
+SELECT a, ROW(11, 12) = (SELECT a, 12), ROW(11, 12) IN (SELECT a, 12) FROM t1;
+a	ROW(11, 12) = (SELECT a, 12)	ROW(11, 12) IN (SELECT a, 12)
+1	0	0
+2	0	0
+11	1	1
+SELECT a AS x, ROW(11, 12) = (SELECT MAX(x), 22), ROW(11, 12) IN (SELECT MAX(x), 22) FROM t1;
+x	ROW(11, 12) = (SELECT MAX(x), 22)	ROW(11, 12) IN (SELECT MAX(x), 22)
+1	0	0
+2	0	0
+11	0	0
+# 2nd and 3rd columns should be same for t1.a == 11 only
+SELECT a AS x, ROW(11, 12) = (SELECT MAX(x), 12), ROW(11, 12) IN (SELECT MAX(x), 12) FROM t1;
+x	ROW(11, 12) = (SELECT MAX(x), 12)	ROW(11, 12) IN (SELECT MAX(x), 12)
+1	0	1
+2	0	1
+11	1	1
+DROP TABLE t1;
 End of 5.0 tests

=== modified file 'mysql-test/t/subselect3.test'
--- a/mysql-test/t/subselect3.test	2008-10-10 10:27:58 +0000
+++ b/mysql-test/t/subselect3.test	2008-11-12 21:43:00 +0000
@@ -640,4 +640,26 @@ WHERE NULL NOT IN (
 
 DROP TABLE t1;
 
+#
+# Bug #35311: ROW(...) IN (SELECT outer field,... FROM DUAL)"
+#             always returns TRUE
+#
+
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1), (2), (11);
+
+--echo # 2nd and 3rd columns should be same
+SELECT a, ROW(11, 12) = (SELECT a, 22), ROW(11, 12) IN (SELECT a, 22) FROM t1 GROUP BY t1.a;
+SELECT a, ROW(11, 12) = (SELECT a, 12), ROW(11, 12) IN (SELECT a, 12) FROM t1 GROUP BY t1.a;
+SELECT a, ROW(11, 12) = (SELECT a, 22), ROW(11, 12) IN (SELECT a, 22) FROM t1;
+SELECT a, ROW(11, 12) = (SELECT a, 12), ROW(11, 12) IN (SELECT a, 12) FROM t1;
+
+# The x alias is used below to workaround bug #40674.
+# Regression tests for sum function on outer column in subselect from dual:
+SELECT a AS x, ROW(11, 12) = (SELECT MAX(x), 22), ROW(11, 12) IN (SELECT MAX(x), 22) FROM t1;
+--echo # 2nd and 3rd columns should be same for t1.a == 11 only
+SELECT a AS x, ROW(11, 12) = (SELECT MAX(x), 12), ROW(11, 12) IN (SELECT MAX(x), 12) FROM t1;
+
+DROP TABLE t1;
+
 --echo End of 5.0 tests

=== modified file 'sql/item_subselect.cc'
--- a/sql/item_subselect.cc	2008-03-28 11:31:52 +0000
+++ b/sql/item_subselect.cc	2008-11-12 21:43:00 +0000
@@ -1172,8 +1172,7 @@ Item_in_subselect::row_value_transformer
   Item *having_item= 0;
   uint cols_num= left_expr->cols();
   bool is_having_used= (join->having || select_lex->with_sum_func ||
-                        select_lex->group_list.first ||
-                        !select_lex->table_list.elements);
+                        select_lex->group_list.first);
   DBUG_ENTER("Item_in_subselect::row_value_transformer");
 
   if (select_lex->item_list.elements != left_expr->cols())

Thread
bzr commit into mysql-5.0-bugteam branch (gshchepa:2711) Bug#35311Gleb Shchepa12 Nov