MySQL Bugs: #106267: wrong result with COALESCE function

Bug #106267	wrong result with COALESCE function
Submitted:	24 Jan 2022 18:39	Modified:	26 Jan 2022 17:17
Reporter:	Morgan Tocker	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.7	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
When using an expression  NOT BETWEEN NULL AND COALESCE() MySQL is returning wrong results.

The COALESCE() function has two arguments, but the first argument is never NULL. So the results should be the same

This is a simplification/based on the testcase t/type_temporal_fractional.test listed as "bug 23".

How to repeat:
DROP TABLE IF EXISTS t2;
CREATE TABLE t2 (
  col_time_6_not_null_key TIME(6) NOT NULL,
  col_datetime_key DATETIME DEFAULT NULL,
  col_time_not_null TIME NOT NULL,
  pk TIMESTAMP(4) NOT NULL PRIMARY KEY
);

INSERT INTO `t2` VALUES ('03:07:29.013169','2000-06-10 19:41:30','13:56:51','2011-09-08 05:08:20.8362'),
('00:20:06.000000','2004-08-23 21:05:10','00:20:06','2011-09-08 05:08:22.8362'),
('00:20:03.000000','2022-01-24 11:35:37','01:04:48','2011-09-08 05:08:24.8362'),
('19:48:23.009935',NULL,'21:43:24','2011-09-08 05:08:26.8362'),
('00:20:01.000000','2003-01-23 14:57:31','00:20:09','2011-09-08 05:08:28.8362'),
('23:09:37.056340',NULL,'05:10:32','2011-09-08 05:08:19.8362'),
('09:44:10.025082','2022-01-24 11:35:37','00:20:00','2011-09-08 05:08:21.8362'),
('23:02:50.013380','2003-11-18 04:32:18','00:20:06','2011-09-08 05:08:23.8362'),
('00:20:01.000000','2022-01-24 11:35:37','14:48:29','2011-09-08 05:08:25.8362'),
('06:00:36.034953',NULL,'23:29:59','2011-09-08 05:08:27.8362');

SELECT pk, col_time_not_null, col_time_6_not_null_key, col_datetime_key
FROM t2
WHERE col_time_not_null NOT BETWEEN NULL AND COALESCE (col_time_6_not_null_key, col_datetime_key);

SELECT pk, col_time_not_null, col_time_6_not_null_key, col_datetime_key
FROM t2
WHERE col_time_not_null NOT BETWEEN NULL AND col_time_6_not_null_key;

Hi Mr. Tocker,

Thank you for your bug report. BTW, how are you ???

Unfortunately, you are quite correct. Both queries should return the same result, but they don't:

pk	col_time_not_null	col_time_6_not_null_key	col_datetime_key
2011-09-08 05:08:19.8362	05:10:32	23:09:37.056340	NULL
2011-09-08 05:08:21.8362	00:20:00	09:44:10.025082	2022-01-24 11:35:37
2011-09-08 05:08:22.8362	00:20:06	00:20:06.000000	2004-08-23 21:05:10
2011-09-08 05:08:23.8362	00:20:06	23:02:50.013380	2003-11-18 04:32:18
pk	col_time_not_null	col_time_6_not_null_key	col_datetime_key
2011-09-08 05:08:20.8362	13:56:51	03:07:29.013169	2000-06-10 19:41:30
2011-09-08 05:08:24.8362	01:04:48	00:20:03.000000	2022-01-24 11:35:37
2011-09-08 05:08:25.8362	14:48:29	00:20:01.000000	2022-01-24 11:35:37
2011-09-08 05:08:26.8362	21:43:24	19:48:23.009935	NULL
2011-09-08 05:08:27.8362	23:29:59	06:00:36.034953	NULL
2011-09-08 05:08:28.8362	00:20:09	00:20:01.000000	2003-01-23 14:57:31

Verified as an Optimiser bug.

Thank you very much !

Documented fix as follows in the MySQL 8.0.29 changelog:

    A query having a WHERE condition of the form c NOT BETWEEN NULL
    AND COALESCE(a, b), having DATETIME columns a, b, and c, with
    columns a and c created as NOT NULL, did not return correct
    results. Further investigation showed that, in such cases, when
    the second or third argument to BETWEEN was NULL, the comparison
    results were inverted, causing it to evaluate as NULL for FALSE,
    and FALSE for NULL.

Closed.

Thank you , Jon .....