Bug #97698 | Different aggregation results between 5.7 and 8.0 | ||
---|---|---|---|
Submitted: | 20 Nov 2019 4:35 | Modified: | 21 Nov 2019 8:59 |
Reporter: | Masaki Oguro | Email Updates: | |
Status: | Not a Bug | Impact on me: | |
Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
Version: | 8.0, 8.0.11, 8.0.18 | OS: | Any |
Assigned to: | CPU Architecture: | Any |
[20 Nov 2019 4:35]
Masaki Oguro
[20 Nov 2019 5:17]
MySQL Verification Team
Hello Masaki, Thank you for the report and test case. regards, Umesh
[20 Nov 2019 5:18]
MySQL Verification Team
-- 8.0.18(lowest version checked 8.0.11) bin/mysql -uroot -S /tmp/mysql_ushastry.sock -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 Server version: 8.0.18 MySQL Community Server - GPL Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> SELECT @@optimizer_switch; +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | @@optimizer_switch | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,use_invisible_indexes=off,skip_scan=on,hash_join=on | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT @@sql_mode; +-----------------------------------------------------------------------------------------------------------------------+ | @@sql_mode | +-----------------------------------------------------------------------------------------------------------------------+ | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | +-----------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> CREATE DATABASE IF NOT EXISTS test; Query OK, 1 row affected (0.00 sec) mysql> use test; Database changed mysql> CREATE TABLE t1 ( -> id INT NOT NULL PRIMARY KEY, -> n INT NOT NULL -> ); Query OK, 0 rows affected (0.01 sec) mysql> mysql> CREATE TABLE t2 ( -> id INT NOT NULL PRIMARY KEY, -> n INT NOT NULL -> ); Query OK, 0 rows affected (0.01 sec) mysql> mysql> INSERT INTO t1 (id, n) VALUES (1, 10); Query OK, 1 row affected (0.01 sec) mysql> SELECT t1.n, MIN(t2.n) FROM t1, t2 WHERE t1.id = 1; +------+-----------+ | n | MIN(t2.n) | +------+-----------+ | NULL | NULL | +------+-----------+ 1 row in set (0.00 sec) mysql>
[20 Nov 2019 5:19]
MySQL Verification Team
-- 5.7.28 bin/mysql -uroot -S /tmp/mysql_ushastry.sock -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.7.28 MySQL Community Server (GPL) Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> SELECT @@optimizer_switch; +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | @@optimizer_switch | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT @@sql_mode; +-------------------------------------------------------------------------------------------------------------------------------------------+ | @@sql_mode | +-------------------------------------------------------------------------------------------------------------------------------------------+ | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | +-------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> CREATE DATABASE IF NOT EXISTS test; Query OK, 1 row affected (0.00 sec) mysql> use test; Database changed mysql> CREATE TABLE t1 ( -> id INT NOT NULL PRIMARY KEY, -> n INT NOT NULL -> ); Query OK, 0 rows affected (0.01 sec) mysql> mysql> CREATE TABLE t2 ( -> id INT NOT NULL PRIMARY KEY, -> n INT NOT NULL -> ); Query OK, 0 rows affected (0.01 sec) mysql> mysql> INSERT INTO t1 (id, n) VALUES (1, 10); Query OK, 1 row affected (0.00 sec) mysql> SELECT t1.n, MIN(t2.n) FROM t1, t2 WHERE t1.id = 1; +----+-----------+ | n | MIN(t2.n) | +----+-----------+ | 10 | NULL | +----+-----------+ 1 row in set (0.00 sec)
[20 Nov 2019 5:20]
MySQL Verification Team
-- 5.6.46 bin/mysql -uroot -S /tmp/mysql_ushastry.sock -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.6.46 MySQL Community Server (GPL) Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> SELECT @@optimizer_switch; +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | @@optimizer_switch | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT @@sql_mode; +------------------------+ | @@sql_mode | +------------------------+ | NO_ENGINE_SUBSTITUTION | +------------------------+ 1 row in set (0.00 sec) mysql> CREATE DATABASE IF NOT EXISTS test; Query OK, 1 row affected, 1 warning (0.00 sec) mysql> use test; Database changed mysql> CREATE TABLE t1 ( -> id INT NOT NULL PRIMARY KEY, -> n INT NOT NULL -> ); Query OK, 0 rows affected (0.01 sec) mysql> mysql> CREATE TABLE t2 ( -> id INT NOT NULL PRIMARY KEY, -> n INT NOT NULL -> ); Query OK, 0 rows affected (0.00 sec) mysql> mysql> INSERT INTO t1 (id, n) VALUES (1, 10); Query OK, 1 row affected (0.00 sec) mysql> SELECT t1.n, MIN(t2.n) FROM t1, t2 WHERE t1.id = 1; +----+-----------+ | n | MIN(t2.n) | +----+-----------+ | 10 | NULL | +----+-----------+ 1 row in set (0.01 sec) mysql>
[21 Nov 2019 8:59]
Roy Lyseng
The result in 8.0 is the correct one. The result of the join of tables t1 and t2 is an empty table. The final result of this query is an implicit grouping of this empty table that returns one row. Implicit grouping with aggregate functions over an empty table usually return NULL values, except for the COUNT function. Implicit grouping with simple column references also return the NULL value when the input is an empty table. Note also that t1.n is functionally dependent on the primary key (t1.id). Thus, since the key is limited to the value 1, the only choice for t1.n is 10, when the result from the join is non-empty, and NULL, when the result is empty. When selecting t1.id, the similar choices are 1 and NULL, respectively.