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