MySQL Bugs: #72840: Wrong result (extra rows) with subquery in HAVING clause

Bug #72840	Wrong result (extra rows) with subquery in HAVING clause
Submitted:	2 Jun 2014 19:33	Modified:	9 Oct 2019 7:58
Reporter:	Elena Stepanova	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.1, 5.5	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
https://mariadb.atlassian.net/browse/MDEV-6294

Test case from "How to repeat" section returns two rows:

MySQL [test]> SELECT a FROM t1 GROUP BY a HAVING a IN ( SELECT 3 );
+------+
| a    |
+------+
|    3 |
|    8 |
+------+
2 rows in set (0.00 sec)

Expected result is 1 row:
+------+
| a    |
+------+
|    3 |
+------+

The wrong result started happening on 5.1 tree from the following revision:

revno: 4005
revision-id: sergey.glukhov@oracle.com-20130507091058-x4li5jat6pvhop96
parent: annamalai.gurusami@oracle.com-20130506105856-d5il8vpqcovru47g
committer: Sergey Glukhov <sergey.glukhov@oracle.com>
branch nick: mysql-5.1
timestamp: Tue 2013-05-07 13:10:58 +0400
message:
  Bug#16095534 CRASH: PREPARED STATEMENT CRASHES IN ITEM_BOOL_FUNC2::FIX_LENGTH_AND_DEC
  The problem happened due to broken left expression in Item_in_optimizer object.
  In case of the bug left expression is runtime created Item_outer_ref item which
  is deleted at the end of the statement and one of Item_in_optimizer arguments
  becomes bad when re-executed. The fix is to use real_item() instead of original
  left expression. Note: It feels a bit weird that after preparing, the field is
  directly part of the generated Item_func_eq, whereas in execution it is replaced
  with an Item_outer_ref wrapper object.

How to repeat:
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (8),(3);
SELECT a FROM t1 GROUP BY a HAVING a IN ( SELECT 3 );
DROP TABLE t1;

Hello Elena,

Thank you for the bug report and test case.
Verified as described.

Thanks,
Umesh Shastry

// 5.7.5 - Not affected

mysql> use test
Database changed
mysql> CREATE TABLE t1 (a INT);
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO t1 VALUES (8),(3);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT a FROM t1 GROUP BY a HAVING a IN ( SELECT 3 );
+------+
| a    |
+------+
|    3 |
+------+
1 row in set (0.01 sec)

// 5.6.20  - Not affected

mysql> select version();
+-------------------------------------------------+
| version()                                       |
+-------------------------------------------------+
| 5.6.20-enterprise-commercial-advanced-debug-log |
+-------------------------------------------------+
1 row in set (0.00 sec)

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

mysql> INSERT INTO t1 VALUES (8),(3);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT a FROM t1 GROUP BY a HAVING a IN ( SELECT 3 );
+------+
| a    |
+------+
|    3 |
+------+
1 row in set (0.00 sec)

// 5.5.38 - affected

mysql> select version();
+------------------+
| version()        |
+------------------+
| 5.5.38-debug-log |
+------------------+
1 row in set (0.00 sec)

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

mysql> INSERT INTO t1 VALUES (8),(3);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT a FROM t1 GROUP BY a HAVING a IN ( SELECT 3 );
+------+
| a    |
+------+
|    3 |
|    8 |
+------+
2 rows in set (0.00 sec)

This is the same problem as bug#71244, which was fixed in MySQL 5.6.17. Closing.