Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-16195

MariaDB 10.2 Select Query Between Dates Not Returning Data

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Duplicate
    • Affects Version/s: 10.2.14
    • Fix Version/s: 10.2.15
    • Component/s: Optimizer
    • Environment:
      CloudLinux release 7.5 (Viktor Gorbatko) with cPanel 11.70.0.39

      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

            Activity

              People

              Assignee:
              alice Alice Sherepa
              Reporter:
              jeremyr Jeremy Romine
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: