MySQL Bugs: #57964: min/max(constant) in subquery gives wrong result

Bug #57964	min/max(constant) in subquery gives wrong result
Submitted:	4 Nov 2010 8:28	Modified:	4 Nov 2010 8:45
Reporter:	Øystein Grøvlen	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S3 (Non-critical)
Version:	5.0.91, 5.1.52, 5.5.8	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
The result of this query is definitely wrong:

select * from t where t.a in (select min(3));
a
1
2

How to repeat:
create table t (a int);
insert into t values (1), (2);
select * from t;
select * from t where t.a in (select min(3));
drop table t;

Verified with 5.1.52:

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot -P3310 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 16
Server version: 5.1.52-community MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> drop table t;
Query OK, 0 rows affected (0.17 sec)

mysql> create table t (a int);
Query OK, 0 rows affected (0.08 sec)

mysql> insert into t values (1), (2);
Query OK, 2 rows affected (0.06 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from t;
+------+
| a    |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

mysql> select * from t where t.a in (select min(3));
+------+
| a    |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.03 sec)

mysql> explain extended select * from t where t.a in (select min(3));
+----+--------------------+-------+------+---------------+------+---------+-----
-+------+----------+----------------+
| id | select_type        | table | type | possible_keys | key  | key_len | ref
 | rows | filtered | Extra          |
+----+--------------------+-------+------+---------------+------+---------+-----
-+------+----------+----------------+
|  1 | PRIMARY            | t     | ALL  | NULL          | NULL | NULL    | NULL
 |    2 |   100.00 | Using where    |
|  2 | DEPENDENT SUBQUERY | NULL  | NULL | NULL          | NULL | NULL    | NULL
 | NULL |     NULL | No tables used |
+----+--------------------+-------+------+---------------+------+---------+-----
-+------+----------+----------------+
2 rows in set, 1 warning (0.02 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: select `test`.`t`.`a` AS `a` from `test`.`t` where <in_optimizer>(`test
`.`t`.`a`,<exists>(select min(3) having (<cache>(`test`.`t`.`a`) = <ref_null_hel
per>(min(3)))))
1 row in set (0.00 sec)

mysql> select min(3);
+--------+
| min(3) |
+--------+
|      3 |
+--------+
1 row in set (0.00 sec)

Looks similar to bug#53485 - maybe a duplicate?