List:Commits« Previous MessageNext Message »
From:Gleb Shchepa Date:January 28 2009 11:00am
Subject:bzr commit into mysql-5.0-bugteam branch (gshchepa:2725) Bug#39069
View as plain text  
#At file:///work/bzr/5.0-39069/ based on revid:sergey.glukhov@stripped

 2725 Gleb Shchepa	2009-01-28
      Bug #39069: <row constructor> IN <table-subquery> seriously 
                  messed up
      
      "ROW(...) IN (SELECT ... FROM DUAL)" always returned TRUE.
      
      Item_in_subselect::row_value_transformer rewrites "ROW(...) 
      IN SELECT" conditions into the "EXISTS (SELECT ... HAVING ...)" 
      form.
      For a subquery from the DUAL pseudotable resulting HAVING 
      condition is an expression on constant values, so further 
      transformation with optimize_cond() eliminates this HAVING 
      condition and resets JOIN::having to NULL.
      Then JOIN::exec treated that NULL as an always-true-HAVING 
      and that caused a bug.
      
      To distinguish an optimized out "HAVING TRUE" clause from
      "HAVING FALSE" we already have the JOIN::having_value flag.
      However, JOIN::exec() ignored JOIN::having_value as described 
      above as if it always set to COND_TRUE.
      
      The JOIN::exec method has been modified to take into account 
      the value of the JOIN::having_value field.
modified:
  mysql-test/r/subselect3.result
  mysql-test/t/subselect3.test
  sql/sql_select.cc

per-file messages:
  mysql-test/r/subselect3.result
    Added test case for bug #39069.
  mysql-test/t/subselect3.test
    Added test case for bug #39069.
  sql/sql_select.cc
    Bug #39069: <row constructor> IN <table-subquery> seriously 
                messed up
    
    The JOIN::exec method has been modified to take into account 
    the value of the JOIN::having_value field.
=== 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	2009-01-28 11:00:48 +0000
@@ -795,4 +795,58 @@ 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	0
+2	0	0
+11	0	0
+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	0
+2	0	0
+11	0	0
+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 x == 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;
+# both columns should be same
+SELECT ROW(1,2) = (SELECT NULL, NULL), ROW(1,2) IN (SELECT NULL, NULL);
+ROW(1,2) = (SELECT NULL, NULL)	ROW(1,2) IN (SELECT NULL, NULL)
+NULL	NULL
+SELECT ROW(1,2) = (SELECT   1,  NULL), ROW(1,2) IN (SELECT    1, NULL);
+ROW(1,2) = (SELECT   1,  NULL)	ROW(1,2) IN (SELECT    1, NULL)
+NULL	NULL
+SELECT ROW(1,2) = (SELECT NULL,    2), ROW(1,2) IN (SELECT NULL,    2);
+ROW(1,2) = (SELECT NULL,    2)	ROW(1,2) IN (SELECT NULL,    2)
+NULL	NULL
+SELECT ROW(1,2) = (SELECT NULL,    1), ROW(1,2) IN (SELECT NULL,    1);
+ROW(1,2) = (SELECT NULL,    1)	ROW(1,2) IN (SELECT NULL,    1)
+0	0
+SELECT ROW(1,2) = (SELECT    1,    1), ROW(1,2) IN (SELECT    1,    1);
+ROW(1,2) = (SELECT    1,    1)	ROW(1,2) IN (SELECT    1,    1)
+0	0
+SELECT ROW(1,2) = (SELECT    1,    2), ROW(1,2) IN (SELECT    1,    2);
+ROW(1,2) = (SELECT    1,    2)	ROW(1,2) IN (SELECT    1,    2)
+1	1
 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	2009-01-28 11:00:48 +0000
@@ -640,4 +640,33 @@ WHERE NULL NOT IN (
 
 DROP TABLE t1;
 
+#
+# Bug #39069: <row constructor> IN <table-subquery> seriously messed up
+#
+
+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 x == 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 # both columns should be same
+SELECT ROW(1,2) = (SELECT NULL, NULL), ROW(1,2) IN (SELECT NULL, NULL);
+SELECT ROW(1,2) = (SELECT   1,  NULL), ROW(1,2) IN (SELECT    1, NULL);
+SELECT ROW(1,2) = (SELECT NULL,    2), ROW(1,2) IN (SELECT NULL,    2);
+SELECT ROW(1,2) = (SELECT NULL,    1), ROW(1,2) IN (SELECT NULL,    1);
+SELECT ROW(1,2) = (SELECT    1,    1), ROW(1,2) IN (SELECT    1,    1);
+SELECT ROW(1,2) = (SELECT    1,    2), ROW(1,2) IN (SELECT    1,    2);
+
 --echo End of 5.0 tests

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2008-12-24 15:24:11 +0000
+++ b/sql/sql_select.cc	2009-01-28 11:00:48 +0000
@@ -1608,8 +1608,13 @@ JOIN::exec()
         We have to test for 'conds' here as the WHERE may not be constant
         even if we don't have any tables for prepared statements or if
         conds uses something like 'rand()'.
+        If HAVING is impossible, the 'having' pointer is NULL
+        (like if HAVING is always TRUE), so it is necessary to check
+        'having_value' for COND_FALSE to distinguish HAVING TRUE from
+        an impossible HAVING.
       */
       if (cond_value != Item::COND_FALSE &&
+          having_value != Item::COND_FALSE &&
           (!conds || conds->val_int()) &&
           (!having || having->val_int()))
       {

Thread
bzr commit into mysql-5.0-bugteam branch (gshchepa:2725) Bug#39069Gleb Shchepa28 Jan