Bug Report

Ouverte
#19,473 0 commentaires 0 réactions 0 personnes assignées Voir sur GitHub

Les mainteneurs répondent en général sous 1 jour

Personne n'a encore pris cette issue.

Évaluation

Difficulté
4/5
Temps estimé
3-5 jours
Accessibilité débutants
30/100
Type d'issue
Bug
Clarté
À clarifier
Activité
À l'abandon
Stack technique
sql
Domaine
databases

Piste de recherche

Commencez par charger le jeu de données joint tidb/tpcd.sql et reproduisez les deux requêtes sur TiDB 8.3.0, en comparant leurs résultats et leur comportement à l’exécution. Vérifiez l’ordre de GROUP BY, les conversions de types et l’omission de l_receiptdate décrits dans le rapport. La tâche sera considérée comme terminée lorsqu’une explication confirmée de l’écart et une voie de résolution documentée seront disponibles.

Rédigé par le modèle d'indexation à partir du texte de l'issue.

Description

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) .

Langage dominant
Python
Étoiles
616
Forks
724
Merge moyen
2 j 5 h
PR mergées (30 j)
168

Préparer son environnement

Par où commencer

  1. Lisez l'issue en entier, puis le guide de contribution du projet.
  2. Signalez en commentaire que vous la prenez — cela évite que deux personnes fassent le même travail.
  3. Forkez le dépôt et travaillez sur une branche.
  4. Ouvrez une pull request qui référence le numéro de l'issue.

Autres issues de pingcap/docs

Toutes les issues de pingcap/docs

Issues similaires

Plus d'issues Python

Recevez les nouvelles issues par e-mail

Un résumé court des issues GitHub adaptées aux débutants.