版本：社区版2.0.1
表结构
CREATE TABLE audit_project (
RowGuid varchar(65533) NOT NULL COMMENT “”,
APPLYDATE datetime NULL COMMENT “”,
AREACODE varchar(65533) NULL COMMENT “”
) ENGINE=OLAP
PRIMARY KEY(RowGuid)
COMMENT “OLAP”
DISTRIBUTED BY HASH(RowGuid) BUCKETS 32
PROPERTIES (
“replication_num” = “1”,
“in_memory” = “false”,
“storage_format” = “DEFAULT”
);
SQL：select * from audit_project where rowguid in (select rowguid from audit_project where 1=1 and areacode=‘210101’ order by applydate desc limit 10) order by applydate desc;
注释：原本的select * from 表 limit 性能非常差，因此想改写用子查询的方式优化，但是发现这么写sql结果集不对，貌似是个随机的值？并不是固定的值，理论上子查询里的结果集是固定的，那么外表的结果集也是固定的。用join的写法测试是正确的。
profile：
Query:
Summary:
- Query ID: 05d36bc6-87fd-11ec-a276-1c1b0d756fa2
- Start Time: 2022-02-07 18:02:13
- End Time: 2022-02-07 18:02:13
- Total: 6ms
- Query Type: Query
- Query State: EOF
- StarRocks Version: 2.0.1
- User: root
- Default Db: default_cluster:test
- Sql Statement: explain select * from audit_project where rowguid in (select rowguid from audit_project where 1=1 and areacode=‘210101’ order by applydate desc limit 10) order by applydate desc