Details
Description
Hello,
We have found that as of MariaDB 10.2 queries using BETWEEN to compare dates no longer work as expected. The following query works just fine on MariaDB 10.1 and MySQL 5.7, but not on MariaDB 10.2. With MariaDB 10.2 this query just returns no rows but the query is executed as valid with no warnings.
SELECT * FROM ( SELECT 'ProduceFD' AS `Type`,
`inspections_fresh_produce_fd`.* FROM `inspections_fresh_produce_fd`
UNION ALL SELECT 'ProduceP' AS `Type`, `inspections_fresh_produce_p`.*
FROM `inspections_fresh_produce_p` UNION ALL SELECT 'Prepped' AS `Type`,
`inspections_prepped`.* FROM `inspections_prepped`) AS query WHERE
`DataCheck`=1 AND `InspectionDate` BETWEEN '2017-04-16' AND '2017-05-13'
ORDER BY `InspectionDate` DESC
This similar query not using "BETWEEN" works as expected and returns the correct data on MariaDB 10.2:
SELECT * FROM ( SELECT 'ProduceFD' AS `Type`, `inspections_fresh_produce_fd`.* FROM `inspections_fresh_produce_fd` UNION ALL SELECT 'ProduceP' AS `Type`, `inspections_fresh_produce_p`.* FROM `inspections_fresh_produce_p` UNION ALL SELECT 'Prepped' AS `Type`, `inspections_prepped`.* FROM `inspections_prepped`) AS query WHERE `DataCheck`=1 AND InspectionDate > '2017-04-16' AND InspectionDate < '2017-05-13' ORDER BY `InspectionDate` DESC;
We have attached an SQL backup that can be used to reproduce this issue.
Attachments
Issue Links
- duplicates
-
MDEV-15765 BETWEEN not working in certain cases
- Closed