MySQL Bugs: #35311: ROW(...) IN (SELECT outer field,... FROM DUAL)" always returns TRUE

Bug #35311	ROW(...) IN (SELECT outer field,... FROM DUAL)" always returns TRUE
Submitted:	15 Mar 2008 17:19	Modified:	27 Jan 2009 8:48
Reporter:	Gleb Shchepa	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.0.38+	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	regression

Description:
Queries like

  SELECT t1.a AS x, ROW(12345, 54321) IN (SELECT x, 0) FROM t1 GROUP BY t1.a

always return TRUE result in second column (regardless of ROW constructor value).

Only FROM DUAL subqueries affected.

How to repeat:
Server version: 5.0.60 Source distribution

mysql> CREATE TABLE t1 (a INT);
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO t1 VALUES(1);
Query OK, 1 row affected (0.00 sec)

# both 2nd and 3rd result columns should be 0:

mysql> SELECT t1.a AS x, ROW(2, 3) = (SELECT x, 4), ROW(2, 3) IN (SELECT x, 4) FROM t1 GROUP BY t1.a;
+------+---------------------------+----------------------------+
| x    | ROW(2, 3) = (SELECT x, 4) | ROW(2, 3) IN (SELECT x, 4) |
+------+---------------------------+----------------------------+
|    1 |                         0 |                          1 | 
+------+---------------------------+----------------------------+
1 row in set (0.00 sec)

Thank you for a bug report. Verified just as described with latest 5.0.60-BK:

openxs@suse:~/dbs/5.0> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.60 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> drop table t1;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE t1 (a INT);
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO t1 VALUES(1);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT t1.a AS x, ROW(2, 3) = (SELECT x, 4), ROW(2, 3) IN (SELECT x, 4) FROM t1
    -> GROUP BY t1.a;
+------+---------------------------+----------------------------+
| x    | ROW(2, 3) = (SELECT x, 4) | ROW(2, 3) IN (SELECT x, 4) |
+------+---------------------------+----------------------------+
|    1 |                         0 |                          1 |
+------+---------------------------+----------------------------+
1 row in set (0.01 sec)

mysql> explain extended SELECT t1.a AS x, ROW(2, 3) = (SELECT x, 4), ROW(2, 3) IN (SELECT x, 4) FROM t1 GROUP BY t1.a\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: t1
         type: system
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
        Extra:
*************************** 2. row ***************************
           id: 3
  select_type: DEPENDENT SUBQUERY
        table: NULL
         type: NULL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
        Extra: Impossible HAVING
*************************** 3. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: NULL
         type: NULL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
        Extra: No tables used
3 rows in set, 3 warnings (0.01 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1276
Message: Field or reference 'x' of SELECT #2 was resolved in SELECT #1
*************************** 2. row ***************************
  Level: Note
   Code: 1276
Message: Field or reference 'x' of SELECT #3 was resolved in SELECT #1
*************************** 3. row ***************************
  Level: Note
   Code: 1003
Message: select '1' AS `x`,((2,3) = (select '1' AS `x`,4 AS `4`)) AS `ROW(2, 3) = (SELECT x, 4)`,<in_optimizer>((2,3),<exists>(select '1' AS `x`,4 AS `4` having 0)) AS `ROW(2, 3) IN (SELECT x, 4)` from `test`.`t1` group by '1'
3 rows in set (0.00 sec)

Changing synopsis from:
  "Wrong result with IN(SELECT aliased groupping outer field FROM DUAL)"
to
  "ROW(...) IN (SELECT outer field,... FROM DUAL)" always returns TRUE".

Simple queries like "SELECT ROW(11, 12) IN (SELECT a, 22) FROM t1" are affected too.

Queries with sum functions in subselect are *not* affected: "SELECT ROW(11, 12) IN (SELECT MAX(a), 22) FROM t1".

Problem is caused by needless last condition in is_having_used variable
initialization (!select_lex->table_list.elements covers FROM DUAL queries):

Item_in_subselect::row_value_transformer(JOIN *join)
{
...
  bool is_having_used= (join->having || select_lex->with_sum_func ||
                        select_lex->group_list.first ||
                        !select_lex->table_list.elements);

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/58587

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.

Ok to push

This bug is related to bug #39069, both bugs will be fixed together.

This bug is a duplicate of bug #39069