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

MariaDB 10.2 Select Query Between Dates Not Returning Data

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Duplicate
    • 10.2.14
    • 10.2.15
    • Optimizer
    • 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

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

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.