Description:
Query returns different results after changing optimizer_switch
How to repeat:
drop database d0;
create database d0;
use d0;
SET session optimizer_switch = 'index_merge=off,index_merge_union=off,index_merge_sort_union=off,index_merge_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,mrr=off,mrr_cost_based=off,block_nested_loop=off,batched_key_access=off,materialization=off,semijoin=off,loosescan=off,firstmatch=off,duplicateweedout=off,subquery_materialization_cost_based=off,use_index_extensions=off,condition_fanout_filter=off,derived_merge=off,use_invisible_indexes=off,skip_scan=off,hash_join=off,subquery_to_derived=off,prefer_ordering_index=off,derived_condition_pushdown=off,hash_set_operations=off';
set session optimizer_prune_level=0;
set session optimizer_search_depth=0;
set @@sql_mode='';
create table if not exists t0(c0 mediumint );
replace into t0(c0) values(null);
replace into t0(c0) values(0.6820336538243837), (0.6297741008029466), ('');
SET session optimizer_switch = 'subquery_to_derived=on';
SELECT DISTINCTROW EXISTS (SELECT 1) AS ref0, (t0.c0) IS UNKNOWN AS ref1, (NULL) >= ("0.547592362833128") AS ref2 FROM t0 GROUP BY EXISTS (SELECT 1), (t0.c0) IS UNKNOWN, (NULL) >= ("0.547592362833128") ORDER BY (1.601440706E9) ^ (t0.c0);
+------+------+------+
| ref0 | ref1 | ref2 |
+------+------+------+
| 1 | 1 | NULL |
+------+------+------+
1 row in set (0.00 sec)
SET session optimizer_switch = 'subquery_to_derived=off';
SELECT DISTINCTROW EXISTS (SELECT 1) AS ref0, (t0.c0) IS UNKNOWN AS ref1, (NULL) >= ("0.547592362833128") AS ref2 FROM t0 GROUP BY EXISTS (SELECT 1), (t0.c0) IS UNKNOWN, (NULL) >= ("0.547592362833128") ORDER BY (1.601440706E9) ^ (t0.c0);
+------+------+------+
| ref0 | ref1 | ref2 |
+------+------+------+
| 1 | 1 | NULL |
| 1 | 0 | NULL |
+------+------+------+
2 rows in set (0.00 sec)
© 2026, Oracle Corporation and/or its affiliates
Description: Query returns different results after changing optimizer_switch How to repeat: drop database d0; create database d0; use d0; SET session optimizer_switch = 'index_merge=off,index_merge_union=off,index_merge_sort_union=off,index_merge_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,mrr=off,mrr_cost_based=off,block_nested_loop=off,batched_key_access=off,materialization=off,semijoin=off,loosescan=off,firstmatch=off,duplicateweedout=off,subquery_materialization_cost_based=off,use_index_extensions=off,condition_fanout_filter=off,derived_merge=off,use_invisible_indexes=off,skip_scan=off,hash_join=off,subquery_to_derived=off,prefer_ordering_index=off,derived_condition_pushdown=off,hash_set_operations=off'; set session optimizer_prune_level=0; set session optimizer_search_depth=0; set @@sql_mode=''; create table if not exists t0(c0 mediumint ); replace into t0(c0) values(null); replace into t0(c0) values(0.6820336538243837), (0.6297741008029466), (''); SET session optimizer_switch = 'subquery_to_derived=on'; SELECT DISTINCTROW EXISTS (SELECT 1) AS ref0, (t0.c0) IS UNKNOWN AS ref1, (NULL) >= ("0.547592362833128") AS ref2 FROM t0 GROUP BY EXISTS (SELECT 1), (t0.c0) IS UNKNOWN, (NULL) >= ("0.547592362833128") ORDER BY (1.601440706E9) ^ (t0.c0); +------+------+------+ | ref0 | ref1 | ref2 | +------+------+------+ | 1 | 1 | NULL | +------+------+------+ 1 row in set (0.00 sec) SET session optimizer_switch = 'subquery_to_derived=off'; SELECT DISTINCTROW EXISTS (SELECT 1) AS ref0, (t0.c0) IS UNKNOWN AS ref1, (NULL) >= ("0.547592362833128") AS ref2 FROM t0 GROUP BY EXISTS (SELECT 1), (t0.c0) IS UNKNOWN, (NULL) >= ("0.547592362833128") ORDER BY (1.601440706E9) ^ (t0.c0); +------+------+------+ | ref0 | ref1 | ref2 | +------+------+------+ | 1 | 1 | NULL | | 1 | 0 | NULL | +------+------+------+ 2 rows in set (0.00 sec)