Bug Report
维护者通常 1 天内回复
还没有人认领这个 Issue。
评估
调研方向
首先加载附加的 tidb/tpcd.sql 数据集,并在 TiDB 8.3.0 上重现两个查询,比较它们的结果和执行行为。检查报告中描述的 GROUP BY 顺序、类型转换以及省略的 l_receiptdate。确认差异的解释并记录解决路径后,才算完成。
由索引模型根据 Issue 内容生成。
描述
BUG Report
TiDB 8.3.0
Original Query
SELECT ALL l_extendedprice, l_orderkey, l_discount
FROM lineitem
WHERE (coalesce(l_quantity, l_suppkey, 0.21501538554113775, l_receiptdate)) <= l_returnflag
GROUP BY l_extendedprice, l_orderkey, l_discount;
Return: 6005 rows
Rewritten Query
SELECT l_extendedprice, l_orderkey, l_discount
FROM lineitem
WHERE CASE
WHEN l_quantity IS NOT NULL THEN CAST(l_quantity AS decimal(15,2))
WHEN l_suppkey IS NOT NULL THEN CAST(l_suppkey AS unsigned)
ELSE 0.21501538554113775
END <= l_returnflag
GROUP BY l_orderkey, l_extendedprice, l_discount
Return: 0 rows
Analysis
1. Logical Equivalence of the Queries
The original and rewritten queries are intended to be semantically equivalent:
- Both select
l_extendedprice,l_orderkey, andl_discountfrom thelineitemtable. - Both filter rows by comparing the first non-NULL value among
l_quantity,l_suppkey,0.21501538554113775, andl_receiptdatetol_returnflag.
- Original Query (using COALESCE):
SELECT ALL l_extendedprice, l_orderkey, l_discount
FROM lineitem
WHERE (coalesce(l_quantity, l_suppkey, 0.21501538554113775, l_receiptdate)) <= l_returnflag
GROUP BY l_extendedprice, l_orderkey, l_discount;
- Rewritten Query (using CASE):
SELECT l_extendedprice, l_orderkey, l_discount
FROM lineitem
WHERE CASE
WHEN l_quantity IS NOT NULL THEN CAST(l_quantity AS decimal(15,2))
WHEN l_suppkey IS NOT NULL THEN CAST(l_suppkey AS unsigned)
ELSE 0.21501538554113775
END <= l_returnflag
GROUP BY l_orderkey, l_extendedprice, l_discount
Expected Behavior
Logically, these two queries should produce the same result set for all rows, as the rewritten query mimics the logic of COALESCE with CASE WHEN.
2. Bug Manifestation
- Original Query result:
6005 rows - Rewritten Query result:
0 rows
This large discrepancy indicates that the semantic equivalence between the queries does not hold in TiDB 8.3.0 for this case.
3. Potential Cause
-
Column Order in GROUP BY:
The original query usesGROUP BY l_extendedprice, l_orderkey, l_discount, whereas the rewritten query usesGROUP BY l_orderkey, l_extendedprice, l_discount.
While standard SQL considers group column order insignificant, some engines may produce different plans if the order is changed. -
Type Casting Differences:
COALESCEreturns the first non-NULL value as is, maintaining its type.- The rewritten
CASEcastsl_quantitytodecimal(15,2)andl_suppkeytounsigned.
If the types differ from those in the original, it may change comparison logic or evaluation, causing filter mismatches.
-
Implicit vs Explicit Null Handling:
COALESCEandCASEshould be functionally similar, but differences in how TiDB handles type conversions and nullability can result in different evaluation orders or filter results.
-
Edge Case: l_receiptdate Ignored in Rewritten Query:
- In the rewritten query,
l_receiptdateis omitted, whereas it is the last fallback in theCOALESCE. This means when all other columns are NULL, the rewritten query will not usel_receiptdate, while the original will.
- In the rewritten query,
How to repeat ?
The exported file for the database is in the attachment. : (https://github.com/LLuopeiqi/newtpcd/blob/main/tidb/tpcd.sql) .
- 主要语言
- Python
- 星标
- 616
- 派生
- 724
- 平均合并
- 2 天 5 小时
- 30 天内合并 PR
- 168
环境准备
- 没有 Dockerfile 或 Docker Compose 文件
- 有 Pull Request 模板
- 阅读贡献指南
从这里开始
- 先读完整个 Issue,再读项目的贡献指南。
- 在 Issue 下留言说明你要接手 —— 这能避免两个人做同样的事。
- Fork 仓库,在一个分支上完成修改。
- 提交 Pull Request,并在描述里引用这个 Issue 编号。
pingcap/docs 的其他 Issue
-
Weekly keywords check: mismatches found可能已有人在做 @saryn17 于 57 天前认领。 未关闭
难度 2/5 1-3 小时 新手友好度 72/100
维护者通常 1 天内回复
-
难度 2/5 1-3 小时 新手友好度 66/100
维护者通常 1 天内回复
-
Weekly keywords check: mismatches found可能已有人在做 @saryn17 于 57 天前认领。 未关闭
难度 2/5 1-3 小时 新手友好度 66/100
维护者通常 1 天内回复
-
难度 2/5 1-3 小时 新手友好度 66/100
维护者通常 1 天内回复
-
tidb-cloud: Terraform import procedures need Terraform v1.5.0 or later, but the prerequisite says >=1.0可能已有人在做 @yahonda 于 5 天前认领。 未关闭
难度 2/5 1-3 小时 新手友好度 77/100
维护者通常 1 天内回复
相似的 Issue
-
难度 2/5 1-3 小时 新手友好度 76/100
维护者通常 1 天内回复
-
documentation
难度 2/5 1-3 小时 新手友好度 85/100
维护者通常 1 天内回复
-
Install the skills as a Claude Code plugin可能已有人在做 @ruban-24 今天认领。 未关闭
难度 2/5 1-3 小时 新手友好度 76/100
维护者通常 6 天内回复
-
难度 2/5 1-3 小时 新手友好度 62/100
FAIRmat-NFDI/pynxtools#874 ·
-
data
难度 2/5 1-3 小时 新手友好度 66/100