You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@doris.apache.org by GitBox <gi...@apache.org> on 2021/01/15 04:30:45 UTC
[GitHub] [incubator-doris] ccoffline opened a new issue #5249: 【Bug】Wrong aggregation query on aggregate table
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:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [incubator-doris] ccoffline closed issue #5249: 【Bug】Wrong aggregation query on aggregate table
Posted by GitBox <gi...@apache.org>.
ccoffline closed issue #5249:
URL: https://github.com/apache/incubator-doris/issues/5249
----------------------------------------------------------------
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:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [incubator-doris] ccoffline commented on issue #5249: 【Bug】Wrong aggregation query on aggregate table
Posted by GitBox <gi...@apache.org>.
ccoffline commented on issue #5249:
URL: https://github.com/apache/incubator-doris/issues/5249#issuecomment-764298615
I have found the fix commit for this bug #5018
----------------------------------------------------------------
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:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [incubator-doris] ccoffline closed issue #5249: 【Bug】Wrong aggregation query on aggregate table
Posted by GitBox <gi...@apache.org>.
ccoffline closed issue #5249:
URL: https://github.com/apache/incubator-doris/issues/5249
----------------------------------------------------------------
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:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [incubator-doris] ccoffline commented on issue #5249: 【Bug】Wrong aggregation query on aggregate table
Posted by GitBox <gi...@apache.org>.
ccoffline commented on issue #5249:
URL: https://github.com/apache/incubator-doris/issues/5249#issuecomment-764298615
I have found the fix commit for this bug #5018
----------------------------------------------------------------
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:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org