From: Date: August 7 2005 9:10pm
Subject: bk commit into 4.1 tree (igor:1.2363) BUG#11867
List-Archive: http://lists.mysql.com/internals/27980
X-Bug: 11867
Message-Id: <20050807191049.6F03E8ED74@rurik.mysql.com>

Below is the list of changes that have just been committed into a local
4.1 repository of igor. When igor does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet
  1.2363 05/08/07 12:10:33 igor@stripped +3 -0
  subselect.result:
    Added test case for bug #11867.
    Fixed results for two existing test cases.
  subselect.test:
    Added test case for bug #11867.
  item_subselect.cc:
    Fixed bug #11867.
    Added missing code in Item_in_subselect::row_value_transformer
    that caused problems for queries with
    ROW(elems) IN (SELECT DISTINCT cols FROM ...).

  mysql-test/r/subselect.result
    1.167 05/08/07 12:09:07 igor@stripped +17 -2
    Added test case for bug #11867.
    Fixed results for two existing test cases.

  mysql-test/t/subselect.test
    1.147 05/08/07 12:07:49 igor@stripped +15 -0
    Added test case for bug #11867.

  sql/item_subselect.cc
    1.138 05/08/07 12:03:59 igor@stripped +9 -4
    Fixed bug #11867.
    Added missing code in Item_in_subselect::row_value_transformer
    that caused problems for queries with
    ROW(elems) IN (SELECT DISTINCT cols FROM ...).

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	igor
# Host:	rurik.mysql.com
# Root:	/home/igor/dev/mysql-4.1-0

--- 1.166/mysql-test/r/subselect.result	Tue Jul 12 09:30:42 2005
+++ 1.167/mysql-test/r/subselect.result	Sun Aug  7 12:09:07 2005
@@ -932,7 +932,7 @@
 1
 select ROW(1, 2, 'a') IN (select a,b,c from t1);
 ROW(1, 2, 'a') IN (select a,b,c from t1)
-NULL
+0
 select ROW(1, 1, 'a') IN (select b,a,c from t1);
 ROW(1, 1, 'a') IN (select b,a,c from t1)
 1
@@ -950,7 +950,7 @@
 1
 select ROW(1, 2, 'a') IN (select a,b,c from t1 where c='b' or c='a');
 ROW(1, 2, 'a') IN (select a,b,c from t1 where c='b' or c='a')
-NULL
+0
 select ROW(1, 1, 'a') IN (select b,a,c from t1 where c='b' or c='a');
 ROW(1, 1, 'a') IN (select b,a,c from t1 where c='b' or c='a')
 1
@@ -2727,3 +2727,18 @@
 max(fld)
 1
 drop table t1;
+CREATE TABLE t1 (one int, two int, flag char(1));
+CREATE TABLE t2 (one int, two int, flag char(1));
+INSERT INTO t1 VALUES(1,2,'Y'),(2,3,'Y'),(3,4,'Y'),(5,6,'N'),(7,8,'N');
+INSERT INTO t2 VALUES(1,2,'Y'),(2,3,'Y'),(3,4,'Y'),(5,6,'N'),(7,8,'N');
+SELECT * FROM t1
+WHERE ROW(one,two) IN (SELECT DISTINCT one,two FROM t2 WHERE flag = 'N');
+one	two	flag
+5	6	N
+7	8	N
+SELECT * FROM t1
+WHERE ROW(one,two) IN (SELECT DISTINCT one,two FROM t1 WHERE flag = 'N');
+one	two	flag
+5	6	N
+7	8	N
+DROP TABLE t1,t2;

--- 1.146/mysql-test/t/subselect.test	Wed Jul 27 17:21:49 2005
+++ 1.147/mysql-test/t/subselect.test	Sun Aug  7 12:07:49 2005
@@ -1754,5 +1754,20 @@
 select * from (select max(fld) from t1) as foo;
 drop table t1;
 
+#
+# Bug #11867: queries with ROW(,elems>) IN (SELECT DISTINCT <cols> FROM ...)
+#
+
+CREATE TABLE t1 (one int, two int, flag char(1));
+CREATE TABLE t2 (one int, two int, flag char(1));
+INSERT INTO t1 VALUES(1,2,'Y'),(2,3,'Y'),(3,4,'Y'),(5,6,'N'),(7,8,'N');
+INSERT INTO t2 VALUES(1,2,'Y'),(2,3,'Y'),(3,4,'Y'),(5,6,'N'),(7,8,'N');
+
+SELECT * FROM t1
+  WHERE ROW(one,two) IN (SELECT DISTINCT one,two FROM t2 WHERE flag = 'N');
+SELECT * FROM t1
+  WHERE ROW(one,two) IN (SELECT DISTINCT one,two FROM t1 WHERE flag = 'N');
+
+DROP TABLE t1,t2;
 
 # End of 4.1 tests

--- 1.137/sql/item_subselect.cc	Thu Jun 23 06:12:58 2005
+++ 1.138/sql/item_subselect.cc	Sun Aug  7 12:03:59 2005
@@ -951,14 +951,19 @@
     List_iterator_fast<Item> li(select_lex->item_list);
     for (uint i= 0; i < n; i++)
     {
+      Item *func;
       DBUG_ASSERT(left_expr->fixed && select_lex->ref_pointer_array[i]->fixed);
       if (select_lex->ref_pointer_array[i]->
           check_cols(left_expr->el(i)->cols()))
         DBUG_RETURN(RES_ERROR);
-      Item *func= new Item_ref_null_helper(this,
-					   select_lex->ref_pointer_array+i,
-					   (char *) "<no matter>",
-					   (char *) "<list ref>");
+      if (join->having || select_lex->with_sum_func ||
+          select_lex->group_list.elements)
+        func= new Item_ref_null_helper(this,
+				       select_lex->ref_pointer_array+i,
+				       (char *) "<no matter>",
+				       (char *) "<list ref>");
+      else
+        func= li++;
       func=
 	eq_creator.create(new Item_direct_ref((*optimizer->get_cache())->
 					      addr(i),