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 Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.6.31,5.7.14 OS:Any
Assigned to: 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] Umesh Shastry
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] Umesh Shastry
See, Bug #5365