Bug #105754 Selecting from column with type "date" returns weird results when joining
Submitted: 30 Nov 2021 15:54 Modified: 1 Dec 2021 13:38
Reporter: Dmytro Liashko Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Data Types Severity:S3 (Non-critical)
Version:8.0 OS:Any
Assigned to: CPU Architecture:Any

[30 Nov 2021 15:54] Dmytro Liashko
Description:
When a column having type "date" is selected in a query which joins another table using comparison operators "<" or ">", the query returns weird results, like "0000-00-00". This issue affects all the MySQL versions starting from 8.0.20.

How to repeat:
Here is the database dump which could help reproducing the issue:

CREATE DATABASE `date_bug`;

INSERT INTO `dates` (`currentdate`) VALUES
	('2021-11-27'),
	('2021-11-28'),
	('2021-11-29'),
	('2021-11-30');
/*!40000 ALTER TABLE `dates` ENABLE KEYS */;

-- Dumping data for table date_bug.test: ~2 rows (approximately)
/*!40000 ALTER TABLE `test` DISABLE KEYS */;
INSERT INTO `test` (`from1`, `dateto`) VALUES
	('2021-11-28 00:00:00', '2021-11-29 00:00:00'),
	('2021-11-27 00:00:00', '2021-11-30 00:00:00');
/*!40000 ALTER TABLE `test` ENABLE KEYS */;

/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
/*!40014 SET FOREIGN_KEY_CHECKS=IFNULL(@OLD_FOREIGN_KEY_CHECKS, 1) */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40111 SET SQL_NOTES=IF
NULL(@OLD_SQL_NOTES, 1) */;

The query which reproduces the bug looks like this:

SELECT * FROM dates JOIN test ON datestart < dateto;

which results in `currentdate` column returning '0000-00-00' result.
[1 Dec 2021 12:44] Dmytro Liashko
This file contains the test dump of the database where the bug is reproducible

Attachment: date_bug.sql (application/octet-stream, text), 2.17 KiB.

[1 Dec 2021 13:38] MySQL Verification Team
Hi Mr. Liashko,

Thank you very much for your bug report.

We have tried and succeeded to reproduce this behaviour of our latest 8.0.27 release.

First query is the one that you have sent and second is just a dump of the first table:

currentdate	datestart	dateend	from1	dateto
0000-00-00	2021-11-27 00:00:00	2021-11-28 00:00:00	2021-11-28 00:00:00	2021-11-29 00:00:00
0085-09-02	2021-11-28 00:00:00	2021-11-29 00:00:00	2021-11-28 00:00:00	2021-11-29 00:00:00
0000-00-00	2021-11-27 00:00:00	2021-11-28 00:00:00	2021-11-27 00:00:00	2021-11-30 00:00:00
0085-09-02	2021-11-28 00:00:00	2021-11-29 00:00:00	2021-11-27 00:00:00	2021-11-30 00:00:00
4400-11-20	2021-11-29 00:00:00	2021-11-30 00:00:00	2021-11-27 00:00:00	2021-11-30 00:00:00
currentdate	datestart	dateend
2021-11-27	2021-11-27 00:00:00	2021-11-28 00:00:00
2021-11-28	2021-11-28 00:00:00	2021-11-29 00:00:00
2021-11-29	2021-11-29 00:00:00	2021-11-30 00:00:00
2021-11-30	2021-11-30 00:00:00	2021-12-01 00:00:00

Verified as reported.

Thank you for your contribution.