doris 2.1.5
使用的是聚合表,发现某些情况下,比如查询的表带上别名,查询返回的结果不准确。
-
正常查询
SELECT * FROM tb ORDER BY@timestamp
-
查询表带上别名
SELECT * FROM tb t2 ORDER BY@timestamp
可以看出查询出来的结果是这样的,感觉十分奇怪! -
表的创建语句为
doris 2.1.5
使用的是聚合表,发现某些情况下,比如查询的表带上别名,查询返回的结果不准确。
正常查询
SELECT * FROM tb ORDER BY @timestamp
查询表带上别名
SELECT * FROM tb t2 ORDER BY @timestamp
可以看出查询出来的结果是这样的,感觉十分奇怪!
表的创建语句为
未复现,复现步骤如下 Doris版本:2.1.6
CREATE TABLE `test_as` (
`@timestamp` datetimev2 NULL,
`addr` varchar(20) NULL,
`Year` varchar(20) NULL
) ENGINE=OLAP
DUPLICATE KEY(`@timestamp`)
COMMENT 'OLAP'
DISTRIBUTED BY HASH(`@timestamp`) BUCKETS 10
PROPERTIES (
"replication_allocation" = "tag.location.default: 1",
"min_load_replica_num" = "-1",
"is_being_synced" = "false",
"storage_medium" = "hdd",
"storage_format" = "V2",
"inverted_index_storage_format" = "V1",
"light_schema_change" = "true",
"disable_auto_compaction" = "false",
"enable_single_replica_compaction" = "false",
"group_commit_interval_ms" = "10000",
"group_commit_data_bytes" = "134217728"
);
INSERT into test_as values(now(),'daf','fdsa'),('2024-08-19 10:54:31','a','b'),('2024-08-19 10:54:31','a','c'),('2024-08-20 10:54:31','doris','hello')
SELECT * from test_as t order by @timestamp
CREATE TABLE `test_as` (
`@timestamp` datetimev2 NULL,
`addr` varchar(20) NULL,
`Year` varchar(20) NULL,
`request_time` INT SUM DEFAULT 1,
) ENGINE=OLAP
aggregate key(`@timestamp`,`addr`,`Year`)
COMMENT 'OLAP'
DISTRIBUTED BY RANDOM BUCKETS AUTO
PROPERTIES (
"replication_allocation" = "tag.location.default: 1",
"min_load_replica_num" = "-1",
"is_being_synced" = "false",
"storage_medium" = "hdd",
"storage_format" = "V2",
"inverted_index_storage_format" = "V1",
"light_schema_change" = "true",
"disable_auto_compaction" = "false",
"enable_single_replica_compaction" = "false",
"group_commit_interval_ms" = "10000",
"group_commit_data_bytes" = "134217728"
);
INSERT into test_as values('2024-08-19 10:54:31','a','b',5),('2024-08-19 10:54:31','a','c',6),('2024-08-20 10:54:31','doris','hello',7);
INSERT into test_as values('2024-08-19 10:54:31','a','b',5),('2024-08-19 10:54:31','a','c',6),('2024-08-20 10:54:31','doris','hello',7);
SELECT * from test_as t order by @timestamp;
SELECT * from test_as order by @timestamp;
我这边试了几遍,聚合表,发现分桶为随机时会出现此问题。DISTRIBUTED BY RANDOM BUCKETS AUTO。
哈希没有问题。
执行sql:SELECT * from test_as t order by @timestamp;
结果是未聚合的,与未加别名的查询结果不一致。