Description:
The table structure and data are the same, but the query results for a partitioned table are different from those for a regular table.
t0 is partition table,t1 is ordinary table
mysql> WITH cte1 AS ( SELECT enum_col, datetime_col FROM t0 ), cte2 AS ( SELECT c1.enum_col, B.datetime_col FROM t0 B LEFT JOIN cte1 c1 ON B.enum_col WHERE c1.datetime_col IS NULL ) SELECT * FROM cte2 GROUP BY 1, 2;
+----------+----------------------------+
| enum_col | datetime_col |
+----------+----------------------------+
| NULL | 9999-12-31 23:59:59.000000 |
| NULL | 1998-09-02 13:20:00.000000 |
+----------+----------------------------+
2 rows in set (0.00 sec)
mysql> WITH cte1 AS ( SELECT enum_col, datetime_col FROM t1 ), cte2 AS ( SELECT c1.enum_col, B.datetime_col FROM t1 B LEFT JOIN cte1 c1 ON B.enum_col WHERE c1.datetime_col IS NULL ) SELECT * FROM cte2 GROUP BY 1, 2;
+----------+----------------------------+
| enum_col | datetime_col |
+----------+----------------------------+
| NULL | 1998-09-02 13:20:00.000000 |
+----------+----------------------------+
1 row in set (0.00 sec)
How to repeat:
drop table if exists t0;
create table t0 (enum_col enum('aaa','bbb','ccc'),datetime_col datetime(6),tinyint_col tinyint)
partition by list (weekday(`datetime_col`))
(PARTITION Holidays VALUES IN (0,6),
PARTITION Single VALUES IN (1,3,5),
PARTITION Multiple VALUES IN (2,4)
);
insert into t0 values ("ccc","9999-12-31 23:59:59.000000",-128);
insert into t0 values (NULL,"1998-09-02 13:20:00.000000",NULL);
drop table if exists t1;
create table t1 (enum_col enum('aaa','bbb','ccc'),datetime_col datetime(6),tinyint_col tinyint);
insert into t1 values ("ccc","9999-12-31 23:59:59.000000",-128);
insert into t1 values (NULL,"1998-09-02 13:20:00.000000",NULL);
WITH cte1 AS ( SELECT enum_col, datetime_col FROM t0 ), cte2 AS ( SELECT c1.enum_col, B.datetime_col FROM t0 B LEFT JOIN cte1 c1 ON B.enum_col WHERE c1.datetime_col IS NULL ) SELECT * FROM cte2 GROUP BY 1, 2;
WITH cte1 AS ( SELECT enum_col, datetime_col FROM t1 ), cte2 AS ( SELECT c1.enum_col, B.datetime_col FROM t1 B LEFT JOIN cte1 c1 ON B.enum_col WHERE c1.datetime_col IS NULL ) SELECT * FROM cte2 GROUP BY 1, 2;
Suggested fix:
same results
Description: The table structure and data are the same, but the query results for a partitioned table are different from those for a regular table. t0 is partition table,t1 is ordinary table mysql> WITH cte1 AS ( SELECT enum_col, datetime_col FROM t0 ), cte2 AS ( SELECT c1.enum_col, B.datetime_col FROM t0 B LEFT JOIN cte1 c1 ON B.enum_col WHERE c1.datetime_col IS NULL ) SELECT * FROM cte2 GROUP BY 1, 2; +----------+----------------------------+ | enum_col | datetime_col | +----------+----------------------------+ | NULL | 9999-12-31 23:59:59.000000 | | NULL | 1998-09-02 13:20:00.000000 | +----------+----------------------------+ 2 rows in set (0.00 sec) mysql> WITH cte1 AS ( SELECT enum_col, datetime_col FROM t1 ), cte2 AS ( SELECT c1.enum_col, B.datetime_col FROM t1 B LEFT JOIN cte1 c1 ON B.enum_col WHERE c1.datetime_col IS NULL ) SELECT * FROM cte2 GROUP BY 1, 2; +----------+----------------------------+ | enum_col | datetime_col | +----------+----------------------------+ | NULL | 1998-09-02 13:20:00.000000 | +----------+----------------------------+ 1 row in set (0.00 sec) How to repeat: drop table if exists t0; create table t0 (enum_col enum('aaa','bbb','ccc'),datetime_col datetime(6),tinyint_col tinyint) partition by list (weekday(`datetime_col`)) (PARTITION Holidays VALUES IN (0,6), PARTITION Single VALUES IN (1,3,5), PARTITION Multiple VALUES IN (2,4) ); insert into t0 values ("ccc","9999-12-31 23:59:59.000000",-128); insert into t0 values (NULL,"1998-09-02 13:20:00.000000",NULL); drop table if exists t1; create table t1 (enum_col enum('aaa','bbb','ccc'),datetime_col datetime(6),tinyint_col tinyint); insert into t1 values ("ccc","9999-12-31 23:59:59.000000",-128); insert into t1 values (NULL,"1998-09-02 13:20:00.000000",NULL); WITH cte1 AS ( SELECT enum_col, datetime_col FROM t0 ), cte2 AS ( SELECT c1.enum_col, B.datetime_col FROM t0 B LEFT JOIN cte1 c1 ON B.enum_col WHERE c1.datetime_col IS NULL ) SELECT * FROM cte2 GROUP BY 1, 2; WITH cte1 AS ( SELECT enum_col, datetime_col FROM t1 ), cte2 AS ( SELECT c1.enum_col, B.datetime_col FROM t1 B LEFT JOIN cte1 c1 ON B.enum_col WHERE c1.datetime_col IS NULL ) SELECT * FROM cte2 GROUP BY 1, 2; Suggested fix: same results