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

Datetime range search in subquery returns an empty resultset

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Duplicate
    • Affects Version/s: 10.2.14
    • Fix Version/s: 10.2.15, 10.3.6
    • Component/s: Optimizer
    • Labels:
      None
    • Environment:
      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

              Assignee:
              alice Alice Sherepa
              Reporter:
              CamaroSS396 Denis
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: