In version 6.5.3, when using CTE statements together with UNION, some subquery results are lost

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: 6.5.3版本CTE语句和UNION一起使用, 部分子查询结果丢失

| username: Hacker_r2TAbGbZ

Bug Report
When running the attached SQL in version 6.5.3, it was found that the row count for the first part of the final UNION is 0, possibly ignoring some data?

问题sql.sql (2.4 KB)

I personally feel that this issue is very similar to the one discussed in this post: tidb遇到一个sql执行的bug - TiDB 的问答社区. Has this been resolved in version 6.5.3?

The result of EXPLAIN ANALYZE is as follows:
EXPLAIN ANALYZE 结果.xlsx (36.7 KB)

【TiDB Version】6.5.3

【Observed Unexpected Behavior】
The result of cnt is:
0
29652
【Expected Behavior】
If the final UNION is not performed, the row counts for the two CTEs should be:
214772
29652
The row count for the first CTE has become 0, which is very strange.

| username: Jellybean | Original post link

Are there other versions of the environment available? Can you verify if other versions are working properly?

The latest version is 7.5.0, you can check if this issue exists in that version.

| username: 小龙虾爱大龙虾 | Original post link

First, test if the latest version still has this issue. If it does, please file an issue on GitHub. This error is considered a serious bug, and once confirmed, it will be quickly fixed and merged into the existing LTS version.

| username: 春风十里 | Original post link

I see someone has already replied in the group. This is a known bug and it was fixed in version 7.5.
TiDB 7.5.0 Release Notes | PingCAP Documentation Center

  • Fixed an issue where pushing down conditions to CTEs resulted in incorrect results when CTEs were referenced multiple times #47881 @winoros
| username: Jellybean | Original post link

You can check the fix description of this issue, and it is also recommended to verify it in the latest test environment. It seems to be optimized.

| username: zhanggame1 | Original post link

6.5.6 Fixes
TiDB 6.5.6 Release Notes | PingCAP Documentation Center

| username: dba远航 | Original post link

Someone has mentioned this.

| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.