标量子查询不自动折叠？

🪐 TiDB 技术问题
1

【TiDB 使用环境】生产环境
【TiDB 版本】v6.5.12
【问题复现路径】

从 v6.5.0 升级至 v6.5.12，开始出现该问题

-- 其中 customer_position.pos_date 是 varchar(8)，存放八位日期
explain
with cte as (
    select  '20251101' as x
)
select pos_date, cust_type
from customer_position a
where a.pos_date = (select x from cte)
id estRows task access object operator info
Projection_14 2576475.96 root dcdb.cr_customer_position.pos_date, dcdb.cr_customer_position.cust_type
└─IndexLookUp_20 2576475.96 root
├─IndexRangeScan_18(Build) 2576475.96 cop[tikv] table:a, index:idx_pos_date_and_code(pos_date, product_code) range:[“20251101”,“20251101”], keep order:false
└─TableRowIDScan_19(Probe) 2576475.96 cop[tikv] table:a keep order:false

可以看到 cte 被折叠为 ‘20251101’，但如果再使用一次 cte，执行计划就会有问题，变成两个 HashJoin，table dual 直接被用了两次来 build 哈希表，表达式无法下推到 kv，性能大幅下降

explain
with cte as (
    select  '20251101' as x
)
select pos_date, cust_type
from cr_customer_position a
where a.pos_date = (select x from cte)
  and a.pos_date = (select x from cte);
id estRows task access object operator info
Projection_19 616.59 root dcdb.cr_customer_position.pos_date, dcdb.cr_customer_position.cust_type
└─HashJoin_20 616.59 root inner join, equal:[eq(dcdb.cr_customer_position.pos_date, Column#56)]
├─Selection_50(Build) 0.80 root not(isnull(Column#56))
│ └─MaxOneRow_51 1.00 root
│ └─CTEFullScan_52 1.00 root CTE:cte data:CTE_0
└─IndexHashJoin_28(Probe) 3054126.67 root inner join, inner:IndexLookUp_25, outer key:Column#55, inner key:dcdb.cr_customer_position.pos_date, equal cond:eq(Column#55, dcdb.cr_customer_position.pos_date)
├─Selection_38(Build) 0.80 root not(isnull(Column#55))
│ └─MaxOneRow_39 1.00 root
│ └─CTEFullScan_40 1.00 root CTE:cte data:CTE_0
└─IndexLookUp_25(Probe) 3054126.67 root
├─Selection_24(Build) 3054126.67 cop[tikv] not(isnull(dcdb.cr_customer_position.pos_date))
│ └─IndexRangeScan_22 3054126.67 cop[tikv] table:a, index:idx_pos_date_and_code(pos_date, product_code) range: decided by [eq(dcdb.cr_customer_position.pos_date, Column#55)], keep order:false
└─TableRowIDScan_23(Probe) 3054126.67 cop[tikv] table:a keep order:false
CTE_0 1.00 root Non-Recursive CTE
└─Projection_17(Seed Part) 1.00 root 20251101->Column#1
└─TableDual_18 1.00 root rows:1

但如果用 /*+ MERGE() */ 来关闭子查询物化，那么该 cte 可正常折叠

explain
with cte as (
    select /*+ MERGE() */ '20251101' as x
)
select pos_date, cust_type
from cr_customer_position a
where a.pos_date = (select x from cte)
  and a.pos_date = (select x from cte)
id estRows task access object operator info
Projection_22 2576475.96 root dcdb.cr_customer_position.pos_date, dcdb.cr_customer_position.cust_type
└─IndexLookUp_28 2576475.96 root
├─IndexRangeScan_26(Build) 2576475.96 cop[tikv] table:a, index:idx_pos_date_and_code(pos_date, product_code) range:[“20251101”,“20251101”], keep order:false
└─TableRowIDScan_27(Probe) 2576475.96 cop[tikv] table:a keep order:false
2

期待有大神出现

2 个赞
3

所以你为啥要这么写呢？

1 个赞
4

用 with 生成查询主体的参数不是一个很常见的写法么

explain
with cte as (
    select '20251101' as x, '20251103' as y
)
select pos_date
from dcdb.cr_customer_position a
where a.pos_date between (select x from cte) and (select y from cte)
id estRows task access object operator info
HashJoin_18 15142979470.00 root CARTESIAN inner join, other cond:le(dcdb.cr_customer_position.pos_date, cast(Column#59, var_string(8)))
├─MaxOneRow_30(Build) 1.00 root
│ └─CTEFullScan_31 1.00 root CTE:cte data:CTE_0
└─HashJoin_20(Probe) 15142979470.00 root CARTESIAN inner join, other cond:ge(dcdb.cr_customer_position.pos_date, cast(Column#56, var_string(8)))
├─MaxOneRow_28(Build) 1.00 root
│ └─CTEFullScan_29 1.00 root CTE:cte data:CTE_0
└─IndexReader_25(Probe) 15142979470.00 root index:IndexFullScan_24
└─IndexFullScan_24 15142979470.00 cop[tikv] table:a, index:idx_pos_date_and_code(pos_date, product_code) keep order:false
CTE_0 1.00 root Non-Recursive CTE
└─Projection_15(Seed Part) 1.00 root 20251101->Column#1, 20251103->Column#2
└─TableDual_16 1.00 root rows:1
5

那也没见 CTE 里就写一个标量的，一般 CTE 里都是有一定逻辑的

6

标量不是说是常量，max 一个表作为参数也是一样的效果，用常量只是说想简化说明，剥离子查询的执行计划更清晰

explain
with cte as (
    select date_format(day_date, '%Y%m%d') as x
    from cr_calendar_day
    order by day_date desc
    limit 1
)
select pos_date
from dcdb.cr_customer_position a
where a.pos_date = (select x from cte)
union all
select pos_date
from dcdb.cr_customer_position_hjt a
where a.pos_date = (select x from cte)
id estRows task access object operator info
Union_37 3080578.78 root
├─IndexHashJoin_44 3054126.67 root inner join, inner:IndexReader_41, outer key:Column#62, inner key:dcdb.cr_customer_position.pos_date, equal cond:eq(Column#62, dcdb.cr_customer_position.pos_date)
│ ├─Selection_52(Build) 0.80 root not(isnull(Column#62))
│ │ └─MaxOneRow_53 1.00 root
│ │ └─CTEFullScan_54 1.00 root CTE:cte data:CTE_0
│ └─IndexReader_41(Probe) 3054126.67 root index:Selection_40
│ └─Selection_40 3054126.67 cop[tikv] not(isnull(dcdb.cr_customer_position.pos_date))
│ └─IndexRangeScan_39 3054126.67 cop[tikv] table:a, index:idx_pos_date_and_code(pos_date, product_code) range: decided by [eq(dcdb.cr_customer_position.pos_date, Column#62)], keep order:false
└─IndexHashJoin_65 26452.11 root inner join, inner:IndexReader_62, outer key:Column#109, inner key:dcdb.cr_customer_position_hjt.pos_date, equal cond:eq(Column#109, dcdb.cr_customer_position_hjt.pos_date)
├─Selection_73(Build) 0.80 root not(isnull(Column#109))
│ └─MaxOneRow_74 1.00 root
│ └─CTEFullScan_75 1.00 root CTE:cte data:CTE_0
└─IndexReader_62(Probe) 26452.11 root index:Selection_61
└─Selection_61 26452.11 cop[tikv] not(isnull(dcdb.cr_customer_position_hjt.pos_date))
└─IndexRangeScan_60 26452.11 cop[tikv] table:a, index:idx_cust_pos_data_code_no(pos_date, ta_system_code, ta_account_no) range: decided by [eq(dcdb.cr_customer_position_hjt.pos_date, Column#109)], keep order:false
CTE_0 1.00 root Non-Recursive CTE
└─Projection_28(Seed Part) 1.00 root date_format(dcdb.cr_calendar_day.day_date, %Y%m%d)->Column#8
└─TopN_29 1.00 root dcdb.cr_calendar_day.day_date:desc, offset:0, count:1
└─TableReader_36 1.00 root data:TopN_35
└─TopN_35 1.00 cop[tikv] dcdb.cr_calendar_day.day_date:desc, offset:0, count:1
└─TableFullScan_34 5844.00 cop[tikv] table:cr_calendar_day keep order:false
7

cte 只被引用一次，会被自动 inline，这是从 v6.4 引入的
cte 相关的改动在 v6.5 版本也比较多，具体不知道那个导致你这种情况的，猜测可能跟这个有关 invalid memory address or nil pointer dereference in `memory.(*Tracker).AttachTo` · Issue #55881 · pingcap/tidb · GitHub

image
image647×836 199 KB

建议你还是从应用去修改下 SQL 绕过吧

2 个赞
8

比较头疼反正是，我在 6.5.0 上测了是没这个问题的，有多少次他都会折叠，现在我们生产只能挨个看哪个慢的离谱了然后加 hint 或者改 sql

9

cte 只被引用一次，会被自动 inline，这是从 v6.4 引入的

10

cte 相关的改动在 v6.5 版本也比较多，具体不知道那个导致你这种情况的，猜测可能跟这个有关 引用

11

不明白为啥这样写的用意？

12

期待有大佬出现

13

太高深，看不懂~~

14

以这个为例，而且这个问题随着查询复杂度猛涨，如果有多个 hashjoin 用多次 cte 的话，那有几次他就要加几层

17

如果 cte 是个有好多行的表的话他这个执行计划其实合理，物化 cte 在内存里应该也就基本是临时表了吧，但对于我这类使用方式来说就不合理

18

算子没有下推

20

只能找官方了

21

版本差异导致的吧

22

a.pos_date = (select x from cte)
and a.pos_date = (select x from cte)这样查询的意义是什么？

23

对,我感觉在后续版本（如 v7.0+）中，这类问题通常会得到修复