MySQL Bugs: #113369: In the hash partition table, the query result is error.

Bug #113369	In the hash partition table, the query result is error.
Submitted:	8 Dec 2023 3:51	Modified:	8 Dec 2023 8:29
Reporter:	Alice Alice	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S3 (Non-critical)
Version:	8.0.34, 8.0.35	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
In the hash partition table, the query result is error.
The result should not be empty.
mysql> CREATE TABLE `tt` (

(PARTITION p0,
 PARTITION p1,
 PARTITION p2) */;    ->   `id_col` int NOT NULL,
    ->   `bigint_col` bigint NOT NULL,
    ->   `year_col` year DEFAULT NULL,
    ->   KEY `ndx_year_col` (`year_col` DESC)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
    -> /*!50100 PARTITION BY HASH (`bigint_col`)
    -> (PARTITION p0,
    ->  PARTITION p1,
    ->  PARTITION p2) */;
Query OK, 0 rows affected (0.07 sec)

mysql> INSERT into tt values (61,-9223372036854775808,'1913');
Query OK, 1 row affected (0.00 sec)

mysql>  SELECT A.year_col, A.id_col FROM tt A WHERE A.id_col < A.year_col AND A.id_col = 61 GROUP BY 1, 2;
Empty set (0.00 sec)

How to repeat:
1.create table and insert data
CREATE TABLE `tt` (
  `id_col` int NOT NULL,
  `bigint_col` bigint NOT NULL,
  `year_col` year DEFAULT NULL,
  KEY `ndx_year_col` (`year_col` DESC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY HASH (`bigint_col`)
(PARTITION p0,
 PARTITION p1,
 PARTITION p2) */;
 
INSERT into tt values (61,-9223372036854775808,'1913');

2.execute the query
SELECT A.year_col, A.id_col FROM tt A WHERE A.id_col < A.year_col AND A.id_col = 61 GROUP BY 1, 2;

Hello Alice Alice,

Thank you for the report and feedback.

regards,
Umesh

Posted by developer:
 
A few observations : 

- Problem could be seen without partitioned tables too.  It has nothing to do with hash partitions as reported in the bug.  
- We can remove the column `bigint_col`  from the table as well.
- We could omit GROUP BY clause as well. 
- I think the issue here is due to comparing the different data type YEAR with INT.   Data type YEAR has interesting caveats as mentioned at https://dev.mysql.com/doc/refman/8.0/en/year.html 

That means following steps are sufficient to reproduce the problem: 

CREATE TABLE `tt` (
`id_col` int NOT NULL,
`year_col` year DEFAULT NULL,
KEY `ndx_year_col` (`year_col` DESC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT into tt values (61,'1913');

mysql> SELECT A.year_col, A.id_col FROM tt A WHERE A.id_col < A.year_col AND A.id_col = 61;
Empty set (0.00 sec)

if we change the YEAR to int data type then above query works:

mysql> SELECT A.year_col, A.id_col FROM tt A WHERE A.id_col < CAST(A.year_col AS SIGNED) AND A.id_col=61;
+----------+--------+
| year_col | id_col |
+----------+--------+
|     1913 |     61 |
+----------+--------+
1 row in set (0.00 sec)

But not the following : 

mysql> SELECT A.year_col, A.id_col FROM tt A WHERE CAST(A.id_col AS YEAR) < A.year_col AND A.id_col=61;
Empty set (0.00 sec)

Why ? Because as per the manual the CAST(A.id_col AS YEAR) converts the year to 2061 which obviously is greater than 1913 so it doesn't return anything. 

mysql> SELECT CAST(id_col AS YEAR) as ID FROM tt;
+------+
| ID   |
+------+
| 2061 |
+------+

If we change the condition then query return the result : 

mysql> SELECT A.year_col, A.id_col FROM tt A WHERE CAST(A.id_col AS YEAR) > A.year_col;
+----------+--------+
| year_col | id_col |
+----------+--------+
|     1913 |     61 |
+----------+--------+
1 row in set (0.00 sec)

Hmm that means perhaps mysql is implicitly converting the INT data type to Year that means value 
Of 61 becomes 2061 thus comparison fails but had that been the case then the following should not have returned any value. 

mysql> SELECT A.year_col, A.id_col FROM tt A WHERE A.id_col < A.year_col;
+----------+--------+
| year_col | id_col |
+----------+--------+
|     1913 |     61 |
+----------+--------+

It means there is something definitely odd that needs to be investigated. Stay tuned.