The following query

SELECT a FROM t1 WHERE ( SELECT MIN(a) = 100 );

if executed on a table with a single row, returns the row regardless the condition in WHERE – it can be = <any number>, or IS NULL, or IS NOT NULL, – and with any of MIN/MAX/COUNT/SUM/AVG.

bzr version-info
revision-id: <email address hidden>
date: 2011-12-16 10:21:46 +0400
build-date: 2011-12-16 16:38:19 +0200
revno: 3356
branch-nick: maria-5.3

Also reproducible on revno 3250.

Not reproducible on 5.3.2, 5.2.10, MySQL 5.1.60.

Reproducible on MyISAM and Aria tables, but not on InnoDB.

Either in_to_exists or materialization is required, otherwise the query fails with ER_ILLEGAL_SUBQUERY_OPTIMIZER_SWITCHES. Reproducible with either of these, also if everything else is OFF.

EXPLAIN:

1 PRIMARY t1 system NULL NULL NULL NULL 1 100.00
2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used

1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
1003 select 1 AS `a` from `test`.`t1` where (select (min(1) = 100))

Minimal optimizer_switch: in_to_exists=on or materialization=on (otherwise the query refuses to execute)

Full optimizer_switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on

Test case:

1. The table needs to be MyISAM or Aria
   CREATE TABLE t1 ( a INT ) ENGINE=MyISAM;
   INSERT INTO t1 VALUES (1);

SELECT a FROM t1 WHERE ( SELECT MIN(a) = 100 );

1. Expected result:
2. a
   #
3. Actual result:
4. a
5. 1