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:
None 
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
Description:
Aggregation of an empty set produces different values between 5.7 and 8.0.

In the following example, the join result becomes empty because the table t2 has no rows.
Then the version 5.7.28 returns 10 as the value of t1.n, but the version 8.0.18 returns NULL.
Which behaviour is correct?

How to repeat:
CREATE TABLE t1 (
  id INT NOT NULL PRIMARY KEY,
  n INT NOT NULL
);

CREATE TABLE t2 (
  id INT NOT NULL PRIMARY KEY,
  n INT NOT NULL
);

INSERT INTO t1 (id, n) VALUES (1, 10);

SELECT t1.n, MIN(t2.n) FROM t1, t2 WHERE t1.id = 1;

# 5.7.28
+----+-----------+
| n  | MIN(t2.n) |
+----+-----------+
| 10 |      NULL |
+----+-----------+

# 8.0.18
+------+-----------+
| n    | MIN(t2.n) |
+------+-----------+
| NULL |      NULL |
+------+-----------+
[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.