Bug #114917 Incorrect query result
Submitted: 8 May 2:52 Modified: 13 May 1:34
Reporter: Sakurajima Mai Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:8.0.35, 8.0.37, 8.4 OS:Any
Assigned to: CPU Architecture:Any

[8 May 2:52] Sakurajima Mai 
Description:
Incorrect query result.

mysql> SELECT time_col, mediumint_col FROM test WHERE time_col IS NOT UNKNOWN AND time_col NOT BETWEEN '06:59:59.000000' AND NULL OR time_col < '10:59:59.000000' group by 1,2 ORDER BY 1, 2;
+-----------------+---------------+
| time_col        | mediumint_col |
+-----------------+---------------+
| 01:59:59.000000 |      -8388608 |
| 02:59:59.000000 |      -8388608 |
| 03:59:59.000000 |      -8388608 |
| 04:59:59.000000 |      -8388608 |
| 05:59:59.000000 |      -8388608 |
| 06:59:59.000000 |      -8388608 |
| 07:59:59.000000 |      -8388608 |
| 08:59:59.000000 |      -8388608 |
| 09:09:59.000000 |      -8388608 |
| 10:59:59.000000 |      -8388608 |
| 12:00:00.000000 |             1 |
| 20:59:59.000000 |      -8388608 |
| 23:23:59.000000 |      -8388608 |
+-----------------+---------------+

I think the time_col of the query result should be between 00:00:000.000000 and 09:09:59.000000

How to repeat:
DROP TABLE IF EXISTS `test`;

CREATE TABLE `test` (
  `time_col` time(6) DEFAULT NULL,
  `mediumint_col` mediumint DEFAULT NULL,
  KEY `ndx_mediumint_col` (`mediumint_col` DESC),
  KEY `ndx_time_col` (`time_col` DESC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `test` VALUES (NULL,1),(NULL,1),(NULL,1),(NULL,1),(NULL,1),(NULL,1),(NULL,1),(NULL,1),(NULL,1),(NULL,1),(NULL,1),(NULL,1),(NULL,1),(NULL,1),(NULL,1),(NULL,1),(NULL,1),(NULL,1),(NULL,1),(NULL,1),(NULL,NULL),(NULL,NULL),(NULL,NULL),(NULL,NULL),(NULL,NULL),('23:23:59.000000',-8388608),('23:23:59.000000',-8388608),('23:23:59.000000',-8388608),('23:23:59.000000',-8388608),('23:23:59.000000',-8388608),('20:59:59.000000',-8388608),('20:59:59.000000',-8388608),('20:59:59.000000',-8388608),('20:59:59.000000',-8388608),('20:59:59.000000',-8388608),('08:59:59.000000',-8388608),('08:59:59.000000',-8388608),('08:59:59.000000',-8388608),('08:59:59.000000',-8388608),('08:59:59.000000',-8388608),('10:59:59.000000',-8388608),('10:59:59.000000',-8388608),('10:59:59.000000',-8388608),('10:59:59.000000',-8388608),('10:59:59.000000',-8388608),('07:59:59.000000',-8388608),('07:59:59.000000',-8388608),('07:59:59.000000',-8388608),('07:59:59.000000',-8388608),('07:59:59.000000',-8388608),('01:59:59.000000',-8388608),('01:59:59.000000',-8388608),('01:59:59.000000',-8388608),('01:59:59.000000',-8388608),('01:59:59.000000',-8388608),('06:59:59.000000',-8388608),('06:59:59.000000',-8388608),('06:59:59.000000',-8388608),('06:59:59.000000',-8388608),('06:59:59.000000',-8388608),('03:59:59.000000',-8388608),('03:59:59.000000',-8388608),('03:59:59.000000',-8388608),('03:59:59.000000',-8388608),('03:59:59.000000',-8388608),('09:09:59.000000',-8388608),('09:09:59.000000',-8388608),('09:09:59.000000',-8388608),('09:09:59.000000',-8388608),('09:09:59.000000',-8388608),('05:59:59.000000',-8388608),('05:59:59.000000',-8388608),('05:59:59.000000',-8388608),('05:59:59.000000',-8388608),('05:59:59.000000',-8388608),('04:59:59.000000',-8388608),('04:59:59.000000',-8388608),('04:59:59.000000',-8388608),('04:59:59.000000',-8388608),('04:59:59.000000',-8388608),('02:59:59.000000',-8388608),('02:59:59.000000',-8388608),('02:59:59.000000',-8388608),('02:59:59.000000',-8388608),('02:59:59.000000',-8388608),(NULL,1),(NULL,1),(NULL,1),(NULL,1),(NULL,1),('12:00:00.000000',1),('12:00:00.000000',1),('12:00:00.000000',1),('12:00:00.000000',1),('12:00:00.000000',1),('12:00:00.000000',1),('12:00:00.000000',1),('12:00:00.000000',1),('12:00:00.000000',1),('12:00:00.000000',1),('12:00:00.000000',1),('12:00:00.000000',1),('12:00:00.000000',1);

SELECT time_col, mediumint_col
FROM test
WHERE time_col IS NOT UNKNOWN
	AND time_col NOT BETWEEN '06:59:59.000000' AND NULL
	OR time_col < '10:59:59.000000'
GROUP BY 1, 2
ORDER BY 1, 2;

Suggested fix:
the time_col of the query result must between 00:00:00.000000 and 09:09:59.000000.
[8 May 5:33] MySQL Verification Team 
Hello Sakurajima Mai,

Thank you for the report and test case.
Verified as described.

regards,
Umesh
[11 May 8:57] huahua xu 
Hi Sakurajima Mai,

Is the below query result what you expected after applying the patch?

MySQL [test]> SELECT time_col, mediumint_col FROM test WHERE time_col IS NOT UNKNOWN AND time_col NOT BETWEEN '06:59:59.000000' AND NULL OR time_col < '10:59:59.000000' group by 1,2 ORDER BY 1, 2;
+-----------------+---------------+
| time_col        | mediumint_col |
+-----------------+---------------+
| 01:59:59.000000 |      -8388608 |
| 02:59:59.000000 |      -8388608 |
| 03:59:59.000000 |      -8388608 |
| 04:59:59.000000 |      -8388608 |
| 05:59:59.000000 |      -8388608 |
| 06:59:59.000000 |      -8388608 |
| 07:59:59.000000 |      -8388608 |
| 08:59:59.000000 |      -8388608 |
| 09:09:59.000000 |      -8388608 |
+-----------------+---------------+
9 rows in set (0.03 sec)
[13 May 1:34] Sakurajima Mai 
yeah,I think that's the right result
[23 May 11:41] huahua xu 
The patch would fix the bug

Attachment: cmpfunc_between_with_time_and_null_types.patch (application/octet-stream, text), 768 bytes.
[23 May 12:10] MySQL Verification Team 
Hello huahua xu,

Thank you for the contribution.
Please don't forget to upload the patch using the "Contributions" tab of this bug page otherwise we will not be able to accept it. 

regards,
Umesh