子查询使用limit查不到结果

Seema · 2024年07月18日 09:56

为了更快的定位您的问题，请提供以下信息，谢谢
【详述】问题详细描述
子查询中使用limit，查不到结果，查询 sql 如下：

SELECT * FROM db_2000003485.tb_2000003485__starrocks__copied_ks3_project_2000159590__1569e819__h4YboS
WHERE __id__ IN (
SELECT __id__
FROM db_2000003485.tb_2000003485__starrocks__copied_ks3_project_2000159590__1569e819__h4YboS
WHERE timestamp >= '2024-07-18 17:00:45'
AND timestamp <= '2024-07-18 17:01:45'
ORDER BY timestamp DESC, __id__ ASC
limit 2
);

不添加 limit 限制可以查到结果

【背景】做过哪些操作？
集群版本由 3.2.6 升级到 3.3.0

【业务影响】查不到数据
【是否存算分离】是
【StarRocks版本】例如：3.3.0
【集群规模】3fe + 6cn
【机器信息】k8s容器环境
【联系方式】存算分离用户群 Seema
【附件】

Seema · 2024年07月18日 09:57

explain.txt (107.1 KB)

Seema · 2024年07月18日 10:55

Table:
CREATE TABLE tb_2000003485__starrocks__copied_ks3_project_2000159590__1569e819__h4YboS (
timestamp datetime NULL COMMENT “”,
__id__ bigint(20) NULL COMMENT “”,
c_ua22cG varchar(1048576) NULL COMMENT “”,
c_DqLAxZ varchar(1048576) NULL COMMENT “”,
c_4Wb76w varchar(1048576) NULL COMMENT “”,
c_4PQFpE varchar(1048576) NULL COMMENT “”,
c_ZFfCb4 bigint(20) NULL COMMENT “”,
c_2Kk97c varchar(1048576) NULL COMMENT “”,
c_XcjYfq bigint(20) NULL COMMENT “”,
c_7JfuiZ varchar(1048576) NULL COMMENT “”,
c_yLHNIy datetime NULL COMMENT “”,
c_DYCklW varchar(1048576) NULL COMMENT “”,
c_S1D7KP varchar(1048576) NULL COMMENT “”,
c_VSChcj varchar(1048576) NULL COMMENT “”,
c_KMWYsn varchar(1048576) NULL COMMENT “”,
c_9iz2Oj varchar(1048576) NULL COMMENT “”,
c_1uzEYh varchar(1048576) NULL COMMENT “”,
c_tQ8wXi varchar(1048576) NULL COMMENT “”,
c_KCkKoK varchar(1048576) NULL COMMENT “”,
c_JjKUtc varchar(1048576) NULL COMMENT “”,
_timestamp_ bigint(20) NULL COMMENT “”,
__source__ varchar(1024) NULL COMMENT “”,
__path__ varchar(1024) NULL COMMENT “”,
__extra__ json NULL COMMENT “”,
__errors__ json NULL COMMENT “”,
c_DUptTr varchar(1048576) NULL COMMENT “”,
c_XqJIM1 varchar(1048576) NULL COMMENT “”,
c_Jocf4W varchar(1048576) NULL COMMENT “”,
c_Y19seL varchar(1048576) NULL COMMENT “”,
c_PolaY6 varchar(1048576) NULL COMMENT “”,
c_XhRUAd varchar(1048576) NULL COMMENT “”,
c_8PkCTy varchar(1048576) NULL COMMENT “”,
c_D2T6hJ varchar(1048576) NULL COMMENT “”,
c_nNSZN1 varchar(1048576) NULL COMMENT “”,
c_7fYDIG varchar(1048576) NULL COMMENT “”,
c_oagkui boolean NULL COMMENT “”,
c_TPZSVB boolean NULL COMMENT “”,
c_5bJtmW boolean NULL COMMENT “”,
c_jQyvVB boolean NULL COMMENT “”,
c_gGVpLK boolean NULL COMMENT “”,
c_fGR41Q bigint(20) NULL COMMENT “”,
c_CXjYQw bigint(20) NULL COMMENT “”,
c_QIpWud bigint(20) NULL COMMENT “”,
c_B2Hozw bigint(20) NULL COMMENT “”,
c_aEbIIP bigint(20) NULL COMMENT “”,
c_b5u5hA double NULL COMMENT “”,
c_8vLBCU double NULL COMMENT “”,
c_oGBRbq double NULL COMMENT “”,
c_TiUWix double NULL COMMENT “”,
c_YM4gbb double NULL COMMENT “”,
c_KHbFbn datetime NULL COMMENT “”,
c_xIxDh4 datetime NULL COMMENT “”,
c_ZT1WWz datetime NULL COMMENT “”
) ENGINE=OLAP
DUPLICATE KEY(timestamp, __id__)
COMMENT “OLAP”
PARTITION BY RANGE(timestamp)
(PARTITION p_202407161500 VALUES [(“0000-01-01 00:00:00”), (“2024-07-16 15:00:00”)),
PARTITION p_202407161600 VALUES [(“2024-07-16 15:00:00”), (“2024-07-16 16:00:00”)),
PARTITION p_202407161700 VALUES [(“2024-07-16 16:00:00”), (“2024-07-16 17:00:00”)),
PARTITION p_202407161800 VALUES [(“2024-07-16 17:00:00”), (“2024-07-16 18:00:00”)),
PARTITION p_202407161900 VALUES [(“2024-07-16 18:00:00”), (“2024-07-16 19:00:00”)),
PARTITION p_202407162000 VALUES [(“2024-07-16 19:00:00”), (“2024-07-16 20:00:00”)),
PARTITION p_202407162100 VALUES [(“2024-07-16 20:00:00”), (“2024-07-16 21:00:00”)),
PARTITION p_202407162200 VALUES [(“2024-07-16 21:00:00”), (“2024-07-16 22:00:00”)),
PARTITION p_202407162300 VALUES [(“2024-07-16 22:00:00”), (“2024-07-16 23:00:00”)),
PARTITION p_202407170000 VALUES [(“2024-07-16 23:00:00”), (“2024-07-17 00:00:00”)),
PARTITION p_202407170100 VALUES [(“2024-07-17 00:00:00”), (“2024-07-17 01:00:00”)),
PARTITION p_202407170200 VALUES [(“2024-07-17 01:00:00”), (“2024-07-17 02:00:00”)),
PARTITION p_202407170300 VALUES [(“2024-07-17 02:00:00”), (“2024-07-17 03:00:00”)),
PARTITION p_202407170400 VALUES [(“2024-07-17 03:00:00”), (“2024-07-17 04:00:00”)),
PARTITION p_202407170500 VALUES [(“2024-07-17 04:00:00”), (“2024-07-17 05:00:00”)),
PARTITION p_202407170600 VALUES [(“2024-07-17 05:00:00”), (“2024-07-17 06:00:00”)),
PARTITION p_202407170700 VALUES [(“2024-07-17 06:00:00”), (“2024-07-17 07:00:00”)),
PARTITION p_202407170800 VALUES [(“2024-07-17 07:00:00”), (“2024-07-17 08:00:00”)),
PARTITION p_202407170900 VALUES [(“2024-07-17 08:00:00”), (“2024-07-17 09:00:00”)),
PARTITION p_202407171000 VALUES [(“2024-07-17 09:00:00”), (“2024-07-17 10:00:00”)),
PARTITION p_202407171100 VALUES [(“2024-07-17 10:00:00”), (“2024-07-17 11:00:00”)),
PARTITION p_202407171200 VALUES [(“2024-07-17 11:00:00”), (“2024-07-17 12:00:00”)),
PARTITION p_202407171300 VALUES [(“2024-07-17 12:00:00”), (“2024-07-17 13:00:00”)),
PARTITION p_202407171400 VALUES [(“2024-07-17 13:00:00”), (“2024-07-17 14:00:00”)),
PARTITION p_202407171500 VALUES [(“2024-07-17 14:00:00”), (“2024-07-17 15:00:00”)),
PARTITION p_202407171600 VALUES [(“2024-07-17 15:00:00”), (“2024-07-17 16:00:00”)),
PARTITION p_202407171700 VALUES [(“2024-07-17 16:00:00”), (“2024-07-17 17:00:00”)),
PARTITION p_202407171800 VALUES [(“2024-07-17 17:00:00”), (“2024-07-17 18:00:00”)),
PARTITION p_202407171900 VALUES [(“2024-07-17 18:00:00”), (“2024-07-17 19:00:00”)),
PARTITION p_202407172000 VALUES [(“2024-07-17 19:00:00”), (“2024-07-17 20:00:00”)),
PARTITION p_202407172100 VALUES [(“2024-07-17 20:00:00”), (“2024-07-17 21:00:00”)),
PARTITION p_202407172200 VALUES [(“2024-07-17 21:00:00”), (“2024-07-17 22:00:00”)),
PARTITION p_202407172300 VALUES [(“2024-07-17 22:00:00”), (“2024-07-17 23:00:00”)),
PARTITION p_202407180000 VALUES [(“2024-07-17 23:00:00”), (“2024-07-18 00:00:00”)),
PARTITION p_202407180100 VALUES [(“2024-07-18 00:00:00”), (“2024-07-18 01:00:00”)),
PARTITION p_202407180200 VALUES [(“2024-07-18 01:00:00”), (“2024-07-18 02:00:00”)),
PARTITION p_202407180300 VALUES [(“2024-07-18 02:00:00”), (“2024-07-18 03:00:00”)),
PARTITION p_202407180400 VALUES [(“2024-07-18 03:00:00”), (“2024-07-18 04:00:00”)),
PARTITION p_202407180500 VALUES [(“2024-07-18 04:00:00”), (“2024-07-18 05:00:00”)),
PARTITION p_202407180600 VALUES [(“2024-07-18 05:00:00”), (“2024-07-18 06:00:00”)),
PARTITION p_202407180700 VALUES [(“2024-07-18 06:00:00”), (“2024-07-18 07:00:00”)),
PARTITION p_202407180800 VALUES [(“2024-07-18 07:00:00”), (“2024-07-18 08:00:00”)),
PARTITION p_202407180900 VALUES [(“2024-07-18 08:00:00”), (“2024-07-18 09:00:00”)),
PARTITION p_202407181000 VALUES [(“2024-07-18 09:00:00”), (“2024-07-18 10:00:00”)),
PARTITION p_202407181100 VALUES [(“2024-07-18 10:00:00”), (“2024-07-18 11:00:00”)),
PARTITION p_202407181200 VALUES [(“2024-07-18 11:00:00”), (“2024-07-18 12:00:00”)),
PARTITION p_202407181300 VALUES [(“2024-07-18 12:00:00”), (“2024-07-18 13:00:00”)),
PARTITION p_202407181400 VALUES [(“2024-07-18 13:00:00”), (“2024-07-18 14:00:00”)),
PARTITION p_202407181500 VALUES [(“2024-07-18 14:00:00”), (“2024-07-18 15:00:00”)),
PARTITION p_202407181600 VALUES [(“2024-07-18 15:00:00”), (“2024-07-18 16:00:00”)),
PARTITION p_202407181700 VALUES [(“2024-07-18 16:00:00”), (“2024-07-18 17:00:00”)),
PARTITION p_202407181800 VALUES [(“2024-07-18 17:00:00”), (“2024-07-18 18:00:00”)),
PARTITION p_202407181900 VALUES [(“2024-07-18 18:00:00”), (“2024-07-18 19:00:00”)),
PARTITION p_202407182000 VALUES [(“2024-07-18 19:00:00”), (“2024-07-18 20:00:00”)),
PARTITION p_202407182100 VALUES [(“2024-07-18 20:00:00”), (“2024-07-18 21:00:00”)),
PARTITION p_202407182200 VALUES [(“2024-07-18 21:00:00”), (“2024-07-18 22:00:00”)),
PARTITION p_202407182300 VALUES [(“2024-07-18 22:00:00”), (“2024-07-18 23:00:00”)),
PARTITION p_202407190000 VALUES [(“2024-07-18 23:00:00”), (“2024-07-19 00:00:00”)),
PARTITION p_202407190100 VALUES [(“2024-07-19 00:00:00”), (“2024-07-19 01:00:00”)))
DISTRIBUTED BY RANDOM
PROPERTIES (
“bloom_filter_columns” = “c_Y19seL, c_2Kk97c, c_DqLAxZ, c_S1D7KP, c_7JfuiZ, c_tQ8wXi, path, c_KCkKoK, c_JjKUtc, timestamp, c_Jocf4W, id, c_ZFfCb4, c_9iz2Oj, c_KMWYsn, c_QIpWud, c_4Wb76w, c_CXjYQw, source, c_aEbIIP, c_nNSZN1, c_4PQFpE, c_DYCklW, c_1uzEYh, c_XqJIM1, c_7fYDIG, c_XcjYfq, c_VSChcj, c_ua22cG, c_B2Hozw, c_PolaY6, c_D2T6hJ, c_XhRUAd, c_8PkCTy, c_fGR41Q, c_DUptTr”,
“bucket_size” = “4294967296”,
“compression” = “ZSTD”,
“datacache.enable” = “true”,
“enable_async_write_back” = “false”,
“replication_num” = “1”,
“storage_volume” = “builtin_storage_volume”
);

许秀不许秀 · 2024年07月19日 11:04

应该是bloom filter的一个bug