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