Bug #34874 Wrong result with IN(SELECT aggregate FROM DUAL)

• Submitted: 27 Feb 2008 9:47
• Modified: 8 Feb 2018 22:50
• Reporter: Gleb Shchepa
• Status: Closed
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 5.0+
• OS: Any
• CPU Architecture: Any

[27 Feb 2008 9:47] Gleb Shchepa

Description:
"SELECT col1 IN (SELECT aggregate(col2) FROM DUAL) FROM sometable GROUP BY col1", where 'aggregate is COUNT, SUM etc., always return 1 (TRUE) if 'sometable' is not empty.

"SELECT col1 = (SELECT aggregate(col2)) FROM sometable GROUP BY col1" works perfectly.

How to repeat:
Server version: 5.0.56-debug Source distribution

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

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

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

mysql> SELECT a, SUM(b), a IN (SELECT SUM(b)), a = (SELECT SUM(b)) FROM t1 GROUP
 BY a;
+------+--------+----------------------+---------------------+
| a    | SUM(b) | a IN (SELECT SUM(b)) | a = (SELECT SUM(b)) |
+------+--------+----------------------+---------------------+
|    1 |      2 |                    1 |                   0 | 
+------+--------+----------------------+---------------------+
1 row in set (0.00 sec)

mysql> UPDATE t1 SET b=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT a, SUM(b), a IN (SELECT SUM(b)), a = (SELECT SUM(b)) FROM t1 GROUP BY a;
+------+--------+----------------------+---------------------+
| a    | SUM(b) | a IN (SELECT SUM(b)) | a = (SELECT SUM(b)) |
+------+--------+----------------------+---------------------+
|    1 |      1 |                    1 |                   1 | 
+------+--------+----------------------+---------------------+
1 row in set (0.00 sec)

[27 Feb 2008 11:40] Valeriy Kravchuk

Thank you for a bug report. Verified just as describe with 5.0.56. Note that this way:

mysql> SELECT a, SUM(b), a IN (SUM(b)), a = SUM(b) FROM t1 GROUP BY a;
+------+--------+---------------+------------+
| a    | SUM(b) | a IN (SUM(b)) | a = SUM(b) |
+------+--------+---------------+------------+
|    1 |      2 |             0 |          0 |
+------+--------+---------------+------------+
1 row in set (0.00 sec)

mysql> update t1 set b=1;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT a, SUM(b), a IN (SUM(b)), a = SUM(b) FROM t1 GROUP BY a;
+------+--------+---------------+------------+
| a    | SUM(b) | a IN (SUM(b)) | a = SUM(b) |
+------+--------+---------------+------------+
|    1 |      1 |             1 |          1 |
+------+--------+---------------+------------+
1 row in set (0.00 sec)

everything works "as expected".

[27 Feb 2008 18:46] Omer Barnir

workaround: Use the '=' as mentioned in the bug

[8 Feb 2018 22:50] Roy Lyseng

Posted by developer:
 
Fixed in 8.0.5