Description:
The following simple query fails to return any records with version 8.0.12. It works correctly in 5.1.14
---
SELECT tag_id, MAX(datetime) AS maxdt
FROM online_samples
WHERE datetime <= '2018-09-18 19:06:05'
GROUP BY tag_id;
---
How to repeat:
Version information:
+-------------------------+------------------------------+
| Variable_name | Value |
+-------------------------+------------------------------+
| innodb_version | 8.0.12 |
| protocol_version | 10 |
| slave_type_conversions | |
| tls_version | TLSv1,TLSv1.1,TLSv1.2 |
| version | 8.0.12 |
| version_comment | MySQL Community Server - GPL |
| version_compile_machine | x86_64 |
| version_compile_os | Win64 |
| version_compile_zlib | 1.2.11 |
+-------------------------+------------------------------+
I have the following table:
---
CREATE TABLE `online_samples` (
`datetime` datetime NOT NULL,
`tag_id` varchar(40) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
`value` float DEFAULT NULL,
`wrtime` datetime NOT NULL,
PRIMARY KEY (`datetime`,`tag_id`),
UNIQUE KEY `online_samples_index_tag_id_datetime` (`tag_id`,`datetime` DESC),
KEY `online_samples_index_wrtime` (`wrtime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
---
If I run the following query I get results as follows:
---
SELECT tag_id, datetime
FROM online_samples
WHERE datetime <= '2018-09-18 19:06:05'
LIMIT 10;
---
Results:
+------------+---------------------+
| tag_id | datetime |
+------------+---------------------+
| test1_ABCD | 2018-08-23 13:37:25 |
| test1_E | 2018-08-23 13:37:25 |
| test1_FGH | 2018-08-23 13:37:25 |
| test1_ABCD | 2018-08-23 13:37:30 |
| test1_E | 2018-08-23 13:37:30 |
| test1_FGH | 2018-08-23 13:37:30 |
| test1_ABCD | 2018-08-23 13:37:35 |
| test1_E | 2018-08-23 13:37:35 |
| test1_FGH | 2018-08-23 13:37:35 |
| test1_ABCD | 2018-08-23 13:37:40 |
+------------+---------------------+
10 rows in set (0.00 sec)
If I do a simple group by without restricting by datetime it works:
---
SELECT tag_id, MAX(datetime)
FROM online_samples
GROUP BY tag_id;
---
Results:
+--------------+---------------------+
| tag_id | MAX(datetime) |
+--------------+---------------------+
| test3t1_IJ | 2018-09-18 19:00:56 |
| test3t1_ABCD | 2018-09-18 18:42:05 |
| test3t1_KL | 2018-09-18 18:56:43 |
| test4t2_IJ | 2018-09-18 19:05:25 |
| test4t2_ABCD | 2018-09-18 19:05:25 |
| test4t2_KL | 2018-09-18 19:05:25 |
| test4t2_O | 2018-09-18 19:05:25 |
| test4t2_MN | 2018-09-18 19:05:25 |
| test4t3_IJ | 2018-08-29 11:52:55 |
| test4t3_ABCD | 2018-08-29 11:52:55 |
| test4t3_KL | 2018-08-29 11:52:55 |
| test4t3_O | 2018-08-29 11:52:55 |
| test4t3_MN | 2018-08-29 11:52:55 |
| test1_ABCD | 2018-09-18 19:05:25 |
| test1_E | 2018-09-18 19:05:25 |
| test1_FGH | 2018-09-18 19:05:25 |
| test2_E | 2018-08-29 16:41:00 |
| test2_FGH | 2018-08-29 16:40:00 |
+--------------+---------------------+
But if I add the two together as follows I get no results:
---
SELECT tag_id, MAX(datetime) AS maxdt
FROM online_samples
WHERE datetime <= '2018-09-18 19:06:05'
GROUP BY tag_id;
---
Results:
Empty set (0.00 sec)
This is an incredibly simple query so it has been driving me crazy as to what the problem could be.
If I do the same exact thing with version 5.1.14 on Linux or on Windows it works perfectly:
+--------------+---------------------+
| tag_id | maxdt |
+--------------+---------------------+
| test3t1_IJ | 2018-09-18 19:00:56 |
| test3t1_ABCD | 2018-09-18 18:42:05 |
| test3t1_KL | 2018-09-18 18:56:43 |
| test4t2_IJ | 2018-09-18 19:05:25 |
| test4t2_ABCD | 2018-09-18 19:05:25 |
| test4t2_KL | 2018-09-18 19:05:25 |
| test4t2_O | 2018-09-18 19:05:25 |
| test4t2_MN | 2018-09-18 19:05:25 |
| test4t3_IJ | 2018-08-29 11:52:55 |
| test4t3_ABCD | 2018-08-29 11:52:55 |
| test4t3_KL | 2018-08-29 11:52:55 |
| test4t3_O | 2018-08-29 11:52:55 |
| test4t3_MN | 2018-08-29 11:52:55 |
| test1_ABCD | 2018-09-18 19:05:25 |
| test1_E | 2018-09-18 19:05:25 |
| test1_FGH | 2018-09-18 19:05:25 |
| test2_E | 2018-08-29 16:41:00 |
| test2_FGH | 2018-08-29 16:40:00 |
+--------------+---------------------+
18 rows in set (0.00 sec)
Any Idea what the problem could be? I had to revert to the old DB version again to get things working.