2.1.5 查询表带别名返回结果不准

Viewed 98
2.1

doris 2.1.5
使用的是聚合表，发现某些情况下，比如查询的表带上别名，查询返回的结果不准确。

  • 正常查询
    SELECT * FROM tb ORDER BY @timestamp
    image.png

  • 查询表带上别名
    SELECT * FROM tb t2 ORDER BY @timestamp
    image.png
    可以看出查询出来的结果是这样的，感觉十分奇怪！

  • 表的创建语句为
    image.png

____5
5 Answers

问题已经在2.1.7修复，可以等一下发版哈。 fix pr

黄海军@SelectDB4173

show tablets from xxx;
select * from xxx tablet(xxx);
看下数据是不是在不同tablet里了

zclllyybb421

未复现，复现步骤如下 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

image.png

黄海军@SelectDB4173

SELECT @timestamp, total FROM tb
查询出错，需要把int类型改成bigint才可以。这个是doris与mysql不兼容还是doris这边有问题？
image.png

____5 
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;
结果是未聚合的，与未加别名的查询结果不一致。
image.png

____5