Description:
A three-layer nested SQL, in which the first layer uses "group by", the second layer uses "case value when", and the third layer adds "where" condition, will return unexpected results when executed on MySQL8.0.25,but rigtht results when excute on MySQL5.7.21.this is a bug?
[root@localhost][test]> select * from test1;
+---+------+
| a | b |
+---+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
+---+------+
3 rows in set (0.00 sec)
execute on mysql8.0.25:
[root@localhost][test]> select b from ( select case when 1=1 then b else 1 end as b from ( select b from test1 group by b) t1 ) t2 where b is not null;
+------+
| b |
+------+
| 3 |
| 3 |
| 3 |
+------+
3 rows in set (0.00 sec)
execute on mysql5.7.21:
[root@localhost][test]> select b from ( select case when 1=1 then b else 1 end as b from ( select b from test1 group by b) t1 ) t2 where b is not null;
+------+
| b |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.00 sec)
How to repeat:
execute on mysql8.0.25:
CREATE TABLE `test1` ( `a` int(11) NOT NULL, `b` int(11) DEFAULT NULL, PRIMARY KEY (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
insert into test1 values (1,1),(2,2),(3,3);
select b
from (
select case when 1=1 then b else 1 end as b
from (
select b from test1 group by b) t1
) t2
where t2.b is not null;
[my.cnf]
[mysql]
default-character-set=utf8mb4
no-auto-rehash
prompt=[\\u@\\h][\\d]>\\_
[mysqld]
autocommit=ON
back_log=128
basedir=/data/app/mysql
binlog_cache_size=1M
binlog_format=ROW
binlog_gtid_simple_recovery=1
binlog_rows_query_log_events=1
character_set_server=utf8mb4
collation_server=utf8mb4_bin
datadir=/data/mysql_data/data
enforce_gtid_consistency=ON
gtid_mode=ON
host_cache_size=0
information_schema_stats_expiry=10
innodb_adaptive_hash_index=OFF
innodb_autoinc_lock_mode=2
innodb_buffer_pool_dump_at_shutdown=1
innodb_buffer_pool_dump_pct=40
innodb_buffer_pool_instances=8
innodb_buffer_pool_load_at_startup=1
innodb_buffer_pool_size=4096m
innodb_concurrency_tickets=500
innodb_data_file_path=ibdata1:2000M:autoextend
innodb_data_home_dir=/data/mysql_data/data/
innodb_file_per_table=1
innodb_flush_log_at_trx_commit=1
innodb_flush_method=O_DIRECT
innodb_flush_neighbors=0
innodb_io_capacity=10000
innodb_io_capacity_max=20000
innodb_lock_wait_timeout=10
innodb_log_buffer_size=16M
innodb_log_file_size=4G
innodb_log_files_in_group=2
innodb_log_group_home_dir=/data_log/mysql_log/innodb_log
innodb_lru_scan_depth=1024
innodb_max_dirty_pages_pct=75
innodb_max_undo_log_size=2G
innodb_online_alter_log_max_size=1G
innodb_open_files=10000
innodb_page_cleaners=4
innodb_page_size=16384
innodb_print_all_deadlocks=1
innodb_purge_rseg_truncate_frequency=128
innodb_purge_threads=4
innodb_read_io_threads=16
innodb_sort_buffer_size=64M
innodb_spin_wait_delay=6
innodb_stats_on_metadata=OFF
innodb_stats_persistent_sample_pages=64
innodb_strict_mode=1
innodb_sync_spin_loops=20
innodb_temp_data_file_path=ibtmp1:50M:autoextend:max:8G
innodb_thread_concurrency=16
innodb_undo_log_truncate=1
innodb_write_io_threads=16
interactive_timeout=28800
join_buffer_size=32K
local_infile=ON
lock_wait_timeout=1800
log_bin=/data_log/mysql_log/bin_log/mysql-bin
log_bin_trust_function_creators=1
log_error=/data/mysql_data/data/mysql.err
log_queries_not_using_indexes=0
log_slave_updates=1
log_slow_admin_statements=1
log_slow_slave_statements=1
log_timestamps=SYSTEM
long_query_time=1
lower_case_table_names=1
max_allowed_packet=32M
max_binlog_size=512M
max_connect_errors=1000
max_connections=256
max_heap_table_size=64M
max_prepared_stmt_count=16382
max_user_connections = 0
open_files_limit = 131072
performance_schema = 1
performance-schema-consumer-events-stages-current=1
performance-schema-consumer-events-stages-history=1
performance-schema-consumer-events-stages-history-long=1
performance-schema-instrument='memory/%=COUNTED'
pid_file=/data/mysql_data/data/mysql.pid
plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
port=3306
read_buffer_size=128K
read_only=0
read_rnd_buffer_size=256K
relay_log=/data_log/mysql_log/relay_log/mysql-relay
relay_log_recovery = 1
rpl_semi_sync_master_enabled = 0
rpl_semi_sync_master_timeout = 3000
rpl_semi_sync_master_wait_for_slave_count = 1
rpl_semi_sync_slave_enabled = 0
secure_file_priv = NULL
server_id = 1122912064
skip_name_resolve = 1
skip_slave_start = 1
slave_load_tmpdir = /data_tmp
slave_parallel_type = LOGICAL_CLOCK
slave_parallel_workers = 16
slave_pending_jobs_size_max = 536870912
slave_preserve_commit_order = 1
slave_skip_errors = OFF
slave_transaction_retries = 128
slow_query_log = 1
slow_query_log_file = /data_log/mysql_log/slow_queries.log
socket = /data/mysql_data/data/mysql.sock
sort_buffer_size = 128K
sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'
sync_binlog = 1
table_definition_cache = 2000
table_open_cache = 2000
table_open_cache_instances = 16
thread_cache_size = 128
thread_handling=one-thread-per-connection
tmp_table_size = 64M
tmpdir = /data_tmp
transaction_isolation = READ-COMMITTED
wait_timeout = 28800
default_authentication_plugin=mysql_native_password
event_scheduler=OFF
log_error_verbosity=3
binlog_expire_logs_seconds = 604800
sql_require_primary_key=ON
