Inconsistent Query Results with AS Syntax in TiDB 7.0.0

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

Original topic: tidb7.0.0 with as语法查询结果不一致

| username: TiDBer_zWsmzAL8

【TiDB Usage Environment】Production Environment
【TiDB Version】
【Reproduction Path】What operations were performed when the issue occurred
【Encountered Issue: Problem Phenomenon and Impact】
【Resource Configuration】SQL as shown in the figure below

The above is the SQL logic, among which

This “as” temporary table, when placed in the entire SQL, produces results that are inconsistent with the results when queried separately.

The final result is inconsistent with the result of one of the “as” temporary tables.

| username: tidb菜鸟一只 | Original post link

Could you provide a minimal reproducible scenario SQL? It’s hard to tell just from the screenshot.

| username: TiDBer_zWsmzAL8 | Original post link

I have updated the question.

| username: tidb菜鸟一只 | Original post link

Are the results still inconsistent between the two queries?

| username: TiDBer_zWsmzAL8 | Original post link

Yes, but I think I know the reason.

| username: linnana | Original post link

The SQL code is very long, what is the reason?

| username: tidb菜鸟一只 | Original post link

Got the results, please share.

| username: TiDBer_zWsmzAL8 | Original post link

This SQL has a recursive query part, you need to add the RECURSIVE keyword after WITH.

| username: Hacker_xUwtuKxa | Original post link

Impressive, you must have spent a lot of time analyzing it.

| username: redgame | Original post link

I see, thank you.