[#MDEV-15860] Datetime range search in subquery returns an empty resultset

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'

[MDEV-15860] Datetime range search in subquery returns an empty resultset Created: 2018-04-13 Updated: 2018-04-17 Resolved: 2018-04-17
Status:	Closed
Project:	MariaDB Server
Component/s:	Optimizer
Affects Version/s:	10.2.14
Fix Version/s:	10.2.15, 10.3.6