Bug #82562 exists (select max(id) from t3 where id=2) produced the undesired result

• Submitted: 12 Aug 2016 5:12
• Modified: 12 Aug 2016 7:52
• Reporter: chen chen
• Status: Not a Bug
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 5.6.31,5.7.14
• OS: Any
• CPU Architecture: Any

[12 Aug 2016 5:12] chen chen

Description:
exists (select id from t3 where id=2) and exists (select max(id) from t3 where id=2) produced the different result

here it is：

mysql> create table t3(id int,t datetime);
Query OK, 0 rows affected (0.44 sec)

mysql> insert into t3 values(1,'20160812');
Query OK, 1 row affected (0.16 sec)

mysql> select 1 from dual where  exists (select id from t3 where id=2);
Empty set (0.15 sec)

mysql> select 1 from dual where  exists (select max(id) from t3 where id=2);
+---+
| 1 |
+---+
| 1 |

in fact,i found a similar question, but it is a different one anyway.

https://bugs.mysql.com/bug.php?id=81855

How to repeat:
i test this in 5.6.31 and 5.7.14,but the result doesn't change.

[12 Aug 2016 7:52] MySQL Verification Team

Hello Chen,

Thank you for the report and test case.
Imho it is expected behavior because MAX()/MIN() returns NULL if there were no matching rows.

-- 4.1,5.0, 5.1, 5.5, 5.6 and 5.7

mysql> select 1 from dual where  exists (select max(id) from t3 where id=2);
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

mysql> select max(id) from t3 where id=2;
+---------+
| max(id) |
+---------+
|    NULL |
+---------+
1 row in set (0.00 sec)

mysql> select 1 from dual where  exists (SELECT NULL);
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

Thanks,
Umesh

[12 Aug 2016 7:56] MySQL Verification Team

See, Bug #5365