Bug #100398 The results of aggregation operations are inconsistent in different partitions

• Submitted: 31 Jul 2020 8:33
• Modified: 31 Jul 2020 9:48
• Reporter: jiaona chen
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 8.0.19, 5.7.31, 8.0.21
• OS: Any
• CPU Architecture: Any

[31 Jul 2020 8:33] jiaona chen

Description:
Execution results in MySQLl8.0.19
mysql> set @previous_sql_mode_htnt542nh=@@sql_mode;
Query OK, 0 rows affected (0.00 sec)

mysql> set sql_mode=(select replace(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE t1 (a INT PRIMARY KEY, b INT) engine=innodb
    -> partition by key() partitions 11;
Query OK, 0 rows affected (0.43 sec)

mysql> INSERT INTO t1 VALUES (1,1), (2,2);
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> 
mysql> CREATE TABLE t2 (a INT PRIMARY KEY, b INT) engine=innodb
    -> partition by key() partitions 11;
Query OK, 0 rows affected (0.55 sec)

mysql> INSERT INTO t2 VALUES (1,1), (3,3);
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT
    ->   (SELECT SUM(c.a) FROM t1 ttt, t2 ccc
    ->    WHERE ttt.a = ccc.b AND ttt.a = t.a GROUP BY ttt.a) AS minid
    -> FROM t1 t, t2 c WHERE t.a = c.b;
+-------+
| minid |
+-------+
|  NULL |
+-------+
1 row in set (0.00 sec)

mysql> DROP TABLE t1,t2;
Query OK, 0 rows affected (0.46 sec)

mysql> CREATE TABLE t1 (a INT PRIMARY KEY, b INT) engine=innodb
    -> partition by key() partitions 7;
Query OK, 0 rows affected (0.29 sec)

mysql> INSERT INTO t1 VALUES (1,1), (2,2);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> CREATE TABLE t2 (a INT PRIMARY KEY, b INT) engine=innodb
    -> partition by key() partitions 7;
Query OK, 0 rows affected (0.36 sec)

mysql> INSERT INTO t2 VALUES (1,1), (3,3);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT
    ->   (SELECT SUM(c.a) FROM t1 ttt, t2 ccc
    ->    WHERE ttt.a = ccc.b AND ttt.a = t.a GROUP BY ttt.a) AS minid
    -> FROM t1 t, t2 c WHERE t.a = c.b;
+-------+
| minid |
+-------+
|     1 |
+-------+
1 row in set (0.00 sec)

mysql> DROP TABLE t1,t2;
Query OK, 0 rows affected (0.30 sec)

It can be found that the execution results of different partitions are different
Note: this test example SQL_ Mode needs to be compatible with the group by statement

How to repeat:
source test：

let $part_num=11;
while ($part_num > 0)
{

eval
CREATE TABLE t1 (a INT PRIMARY KEY, b INT) engine=innodb
partition by key() partitions $part_num;
INSERT INTO t1 VALUES (1,1), (2,2);
eval
CREATE TABLE t2 (a INT PRIMARY KEY, b INT) engine=innodb
partition by key() partitions $part_num;
INSERT INTO t2 VALUES (1,1), (3,3);
--source include/turn_off_only_full_group_by.inc
SELECT
  (SELECT SUM(c.a) FROM t1 ttt, t2 ccc
   WHERE ttt.a = ccc.b AND ttt.a = t.a GROUP BY ttt.a) AS minid
FROM t1 t, t2 c WHERE t.a = c.b;
--source include/restore_sql_mode_after_turn_off_only_full_group_by.inc
DROP TABLE t1,t2;

dec $part_num;
}

turn_off_only_full_group_by.inc
--disable_query_log
# Cryptic variable name to avoid name conflicts
set @previous_sql_mode_htnt542nh=@@sql_mode;
set sql_mode=(select replace(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
--enable_query_log

restore_sql_mode_after_turn_off_only_full_group_by.inc
--disable_query_log
set @@sql_mode=@previous_sql_mode_htnt542nh;
--enable_query_log

[31 Jul 2020 9:48] MySQL Verification Team

Hello jiaona chen,

Thank you for the report and test case.
Observed with both 5.7.31 and 8.0.21.

regards,
Umesh