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

Datetime range search in subquery returns an empty resultset

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Duplicate
    • 10.2.14
    • 10.2.15, 10.3.6
    • Optimizer
    • None
    • Windows Server 2008 R2 x64.

    Description

      SQL mode is `STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION`

      Create test data as follows

      CREATE TABLE `test` (
      	`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
      	`user_id` INT UNSIGNED NULL,
      	`date_time` TIMESTAMP NULL,
      	PRIMARY KEY (`id`)
      )
      COLLATE='latin1_swedish_ci'
      ENGINE=InnoDB;
      INSERT INTO `test` (`user_id`, `date_time`) VALUES ('1', '2018-04-01 10:00:00');
      INSERT INTO `test` (`user_id`, `date_time`) VALUES ('1', '2018-04-02 10:00:00');
      INSERT INTO `test` (`user_id`, `date_time`) VALUES ('2', '2018-04-02 10:00:00');
      INSERT INTO `test` (`user_id`, `date_time`) VALUES ('2', '2018-04-03 10:00:00');
      

      Trying to search data by minimal datetime. The query should have returned one row but an empty result is returned

      SELECT *
      FROM
      	(
      		SELECT `user_id`, MIN(`date_time`) AS `min_date_time`
      		FROM `test`
      		GROUP BY `user_id`
      		ORDER BY NULL
      	) AS `temp`
      WHERE
      	`min_date_time` BETWEEN '2018-04-02 00:00:00' AND '2018-04-03 00:00:00'
      

      Affected rows: 0 Found rows: 0 Warnings: 0 Duration for 1 query: 0,172 sec.

      The condition works though, when split in two like this:

      SELECT *
      FROM
      	(
      		SELECT `user_id`, MIN(`date_time`) AS `min_date_time`
      		FROM `test`
      		GROUP BY `user_id`
      		ORDER BY NULL
      	) AS `temp`
      WHERE
      	`min_date_time` >= '2018-04-02 00:00:00' AND `min_date_time` <= '2018-04-03 00:00:00'
      

      Attachments

        Issue Links

          Activity

            People

              alice Alice Sherepa
              CamaroSS396 Denis
              Votes:
              0 Vote for this issue
              Watchers:
              3 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.