| 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: | |
| Category: | MySQL Server: Data Types | Severity: | S3 (Non-critical) |
| Version: | 8.0 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[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.
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.