Bug #114982 Query result about list partition table is incorrect

• Submitted: 13 May 2024 11:47
• Modified: 13 May 2024 12:23
• Reporter: hel le
• Status: Verified
• Category: MySQL Server: Partitions
• Severity: S2 (Serious)
• Version: 8.0
• OS: Any
• CPU Architecture: Any
• Tags: Query result about list partition table is incorrect and is different with Non-p

[13 May 2024 11:47] hel le

Description:
1. create table and insert values

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);

2. create list partition table

create table t2 like t1;
insert into t2 select * from t1;
alter table t2 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));

3. run query with no-partitioned table t1.(Query results are marked as Result1)

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)

4. run query with list partition table t2.(Query results are marked as Result2)

mysql> WITH cte1 AS ( SELECT enum_col, datetime_col FROM t2 ),  cte2 AS ( SELECT c1.enum_col, B.datetime_col FROM t2 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.01 sec)

5. Result1 is different with Resutl2 and Result2 is incorrect.

How to repeat:
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);

create table t2 like t1;
insert into t2 select * from t1;
alter table t2 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));

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;

WITH cte1 AS ( SELECT enum_col, datetime_col FROM t2 ),  cte2 AS ( SELECT c1.enum_col, B.datetime_col FROM t2 B LEFT JOIN cte1 c1 ON B.enum_col WHERE c1.datetime_col IS NULL ) SELECT * FROM cte2 GROUP BY 1, 2;

Suggested fix:
No

[13 May 2024 12:23] MySQL Verification Team

Hi Mr. le,

Thank you for your bug report.

We have managed to repeat it with 8.0.37 and 8.4.0:

enum_col	datetime_col
NULL	1998-09-02 13:20:00.000000
enum_col	datetime_col
NULL	9999-12-31 23:59:59.000000
NULL	1998-09-02 13:20:00.000000

This is now a verified bug for version 8.0 and higher.

Since the results are wrong, we agree on the severity.

Thank you very much.