分页查询,同一个字段多次排序,会导致排序字段为null

Viewed 37

doris版本为 2.0.5
建表语句

CREATE DATABASE testdb;

CREATE TABLE testdb.test_table(
    user_id            BIGINT       NOT NULL COMMENT "用户 ID",
    name               VARCHAR(20)           COMMENT "用户姓名",
    age                INT                   COMMENT "用户年龄",
		`arr` array<text> NULL COMMENT '数组'
)
UNIQUE KEY(user_id)
DISTRIBUTED BY HASH(user_id) BUCKETS 10 PROPERTIES (
"replication_allocation" = "tag.location.default: 1",
"is_being_synced" = "false",
"storage_format" = "V2",
"enable_unique_key_merge_on_write" = "true",
"light_schema_change" = "true",
"store_row_column" = "true",
"disable_auto_compaction" = "false",
"enable_single_replica_compaction" = "false"
);

新增数据

INSERT INTO testdb.test_table (user_id, name, age,arr)
VALUES (1, "Emily", 25,'["测试1"]'),
       (2, "Benjamin", 35,'["测试2"]');

问题复现

-- age字段展示正常
SELECT te.`name`,te.`name`,te.age,te.arr FROM test_table te ORDER BY te.age DESC  LIMIT 0,6;

-- age字段为null
SELECT te.`name`,te.`name`,te.age,te.arr FROM test_table te ORDER BY te.age DESC,te.`name`,te.age ASC LIMIT 0,6;

-- 去掉limit之后,显示正常
SELECT te.`name`,te.`name`,te.age,te.arr FROM test_table te ORDER BY te.age DESC,te.`name`,te.age ASC ;
-- 不去掉limit之后,把arr字段去掉,也显示正常
SELECT te.`name`,te.`name`,te.age FROM test_table te ORDER BY te.age DESC,te.`name`,te.age ASC ;
1 Answers

这个应该是个已知问题,应该是fix了,我测试的版本是2.1.3,看着都是正常的

image.png