ccoffline opened a new issue #5249:
URL: https://github.com/apache/incubator-doris/issues/5249
**Describe the bug**
Wrong aggregation query on aggregate table.
This bug has been fixed on the latest master branch. Which commit has fixed
this bug?
**To Reproduce**
```sql
DROP DATABASE IF EXISTS `test`; CREATE DATABASE `test`; USE `test`;
CREATE TABLE `agg_bug_test` (
`dt` bigint(20) NULL,
`id1` bigint(20) NULL,
`id2` bigint(20) NULL,
`id3` bigint(20) NULL,
`first_time` varchar(20) MIN NULL,
`last_time` varchar(20) MAX NULL
) ENGINE=OLAP
AGGREGATE KEY(`dt`, `id1`, `id2`, `id3`)
DISTRIBUTED BY HASH(`id1`, `id2`, `id3`) BUCKETS 10
PROPERTIES (
"replication_num" = "3",
"in_memory" = "false",
"storage_format" = "DEFAULT"
);
INSERT INTO `agg_bug_test` (`dt`, `id1`, `id2`, `id3`, `first_time`,
`last_time`) VALUES
('20210106', '13', '53', '21', NULL, NULL),
('20210106', '13', '77', '96', NULL, NULL),
('20210106', '13', '52', '63', NULL, NULL),
('20210106', '13', '77', '37', NULL, NULL),
('20210106', '13', '77', '81', NULL, NULL),
('20210106', '13', '81', '87', NULL, NULL),
('20210106', '13', '97', '81', NULL, NULL),
('20210106', '13', '41', '16', NULL, NULL),
('20210106', '13', '66', '69', NULL, NULL),
('20210106', '13', '53', '11', NULL, NULL),
('20210106', '81', '56', '77', '2021-01-05 09:29:29', '2021-01-05 09:29:29'),
('20210106', '81', '86', '7', '2021-01-05 09:19:56', '2021-01-05 09:19:56'),
('20210106', '37', '27', '41', '2021-01-05 09:21:14', '2021-01-05 09:21:14'),
('20210106', '81', '86', '8', '2021-01-05 09:19:56', '2021-01-05 09:19:56'),
('20210106', '60', '4', '99', '2021-01-05 08:56:09', '2021-01-05 08:56:09'),
('20210106', '49', '39', '57', '2021-01-05 09:20:54', '2021-01-05 09:20:54'),
('20210106', '88', '73', '80', '2021-01-05 09:21:20', '2021-01-05 09:21:20'),
('20210106', '62', '60', '0', '2021-01-05 09:19:05', '2021-01-05 09:19:05'),
('20210106', '49', '39', '91', '2021-01-05 09:20:54', '2021-01-05 09:20:54'),
('20210106', '81', '20', '82', '2021-01-05 09:24:40', '2021-01-05 09:24:40')
;
select dt,
min(first_time) as first_time
,max(last_time) as last_time
from agg_bug_test
group by 1
order by 1
;
+----------+---------------------+---------------------+
| dt | first_time | last_time |
+----------+---------------------+---------------------+
| 20210106 | 2021-01-05 09:20:54 | 2021-01-05 09:29:29 |
+----------+---------------------+---------------------+
select dt,
max(first_time) as first_time_max,
min(first_time) as first_time,
max(last_time) as last_time,
min(last_time) as last_time_min
from agg_bug_test
group by 1
order by 1
;
+----------+---------------------+---------------------+---------------------+---------------------+
| dt | first_time_max | first_time | last_time
| last_time_min |
+----------+---------------------+---------------------+---------------------+---------------------+
| 20210106 | 2021-01-05 09:29:29 | 2021-01-05 08:56:09 | 2021-01-05 09:29:29
| 2021-01-05 08:56:09 |
+----------+---------------------+---------------------+---------------------+---------------------+
```
**Correct Result**
```sql
CREATE TABLE `agg_bug_uniq` (
`dt` bigint(20) NULL,
`id1` bigint(20) NULL,
`id2` bigint(20) NULL,
`id3` bigint(20) NULL,
`first_time` varchar(20) NULL,
`last_time` varchar(20) NULL
) ENGINE=OLAP
UNIQUE KEY(`dt`, `id1`, `id2`, `id3`)
DISTRIBUTED BY HASH(`id1`, `id2`, `id3`) BUCKETS 10
PROPERTIES (
"replication_num" = "3",
"in_memory" = "false",
"storage_format" = "DEFAULT"
);
INSERT INTO `agg_bug_uniq` (`dt`, `id1`, `id2`, `id3`, `first_time`,
`last_time`) VALUES
('20210106', '13', '53', '21', NULL, NULL),
('20210106', '13', '77', '96', NULL, NULL),
('20210106', '13', '52', '63', NULL, NULL),
('20210106', '13', '77', '37', NULL, NULL),
('20210106', '13', '77', '81', NULL, NULL),
('20210106', '13', '81', '87', NULL, NULL),
('20210106', '13', '97', '81', NULL, NULL),
('20210106', '13', '41', '16', NULL, NULL),
('20210106', '13', '66', '69', NULL, NULL),
('20210106', '13', '53', '11', NULL, NULL),
('20210106', '81', '56', '77', '2021-01-05 09:29:29', '2021-01-05 09:29:29'),
('20210106', '81', '86', '7', '2021-01-05 09:19:56', '2021-01-05 09:19:56'),
('20210106', '37', '27', '41', '2021-01-05 09:21:14', '2021-01-05 09:21:14'),
('20210106', '81', '86', '8', '2021-01-05 09:19:56', '2021-01-05 09:19:56'),
('20210106', '60', '4', '99', '2021-01-05 08:56:09', '2021-01-05 08:56:09'),
('20210106', '49', '39', '57', '2021-01-05 09:20:54', '2021-01-05 09:20:54'),
('20210106', '88', '73', '80', '2021-01-05 09:21:20', '2021-01-05 09:21:20'),
('20210106', '62', '60', '0', '2021-01-05 09:19:05', '2021-01-05 09:19:05'),
('20210106', '49', '39', '91', '2021-01-05 09:20:54', '2021-01-05 09:20:54'),
('20210106', '81', '20', '82', '2021-01-05 09:24:40', '2021-01-05 09:24:40')
;
select dt,
min(first_time) as first_time
,max(last_time) as last_time
from agg_bug_uniq
group by 1
order by 1
;
+----------+---------------------+---------------------+
| dt | first_time | last_time |
+----------+---------------------+---------------------+
| 20210106 | 2021-01-05 08:56:09 | 2021-01-05 09:29:29 |
+----------+---------------------+---------------------+
```
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
