Bug Report

未关闭
#19,473 0 条评论 0 个 reaction 已指派 0 人 在 GitHub 查看

维护者通常 1 天内回复

还没有人认领这个 Issue。

评估

难度
4/5
预计耗时
3-5 天
新手友好度
30/100
Issue 类型
缺陷
描述清晰度
需要澄清
活跃度
停滞
技术栈
sql
领域
databases

调研方向

首先加载附加的 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, and l_discount from the lineitem table.
  • Both filter rows by comparing the first non-NULL value among l_quantity, l_suppkey, 0.21501538554113775, and l_receiptdate to l_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 uses GROUP BY l_extendedprice, l_orderkey, l_discount, whereas the rewritten query uses GROUP 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:

    • COALESCE returns the first non-NULL value as is, maintaining its type.
    • The rewritten CASE casts l_quantity to decimal(15,2) and l_suppkey to unsigned.
      If the types differ from those in the original, it may change comparison logic or evaluation, causing filter mismatches.
  • Implicit vs Explicit Null Handling:

    • COALESCE and CASE should 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_receiptdate is omitted, whereas it is the last fallback in the COALESCE. This means when all other columns are NULL, the rewritten query will not use l_receiptdate, while the original will.

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

环境准备

从这里开始

  1. 先读完整个 Issue,再读项目的贡献指南。
  2. 在 Issue 下留言说明你要接手 —— 这能避免两个人做同样的事。
  3. Fork 仓库,在一个分支上完成修改。
  4. 提交 Pull Request,并在描述里引用这个 Issue 编号。

pingcap/docs 的其他 Issue

查看 pingcap/docs 的全部 Issue

相似的 Issue

更多 Python Issue

把新 issue 发到你的邮箱

精选适合新手参与的 GitHub issue 摘要。