Inconsistent Data Returned from Multiple Executions

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

Original topic: 多次执行返回数据不一致

| username: TiDBer_Xxz1gCH6

【TiDB Usage Environment】Production/Development/Testing/Pre
【TiDB Version】7.5.0
【Reproduction Path】
sql.txt (3.6 KB)

【Encountered Problem: Problem Phenomenon and Impact】
bc3e72ed7a22fca37d6c0b926729672
42709a4a6d44f50167cadad98c0ce49
5fb865c9413fa52b5a763e6530c4c78

【Execution Plan】
_explain_analyze_with_t_00_as_select_distinct_t1_task_id_t2_anno_202403071456(1).csv (18.7 KB)

| username: 江湖故人 | Original post link

Are you sure the source table hasn’t changed?
Your query is nested too many layers, I suggest breaking it down layer by layer to identify the source of the change.
For example, in the first step, rewrite the final group statement into a regular select.

| username: zhanggame1 | Original post link

Are you sure which one is correct regarding the inconsistency? Is the data changing?

| username: TiDBer_jYQINSnf | Original post link

Put this SQL and the same data into MySQL for comparison testing. If the results are inconsistent with MySQL, file a bug report with TiDB and let them investigate. The SQL is too complex.

| username: DBAER | Original post link

Is it possible that the table is not static?

| username: TiDBer_aaO4sU46 | Original post link

No. That’s scary.

| username: FutureDB | Original post link

This logic has multiple layers and is a bit complicated…

| username: 哈喽沃德 | Original post link

It is possible that while performing operations, there are changes in business data.

| username: YuchongXU | Original post link

Try starting a transaction before the query.

| username: WinterLiu | Original post link

Data is changing, right? If TiDB has such a bug, wouldn’t that be too low-end?

| username: 数据库真NB | Original post link

Individually query this segment to see if it is because the sub_create_time (creation time) and status are constantly changing.

| username: 数据库真NB | Original post link

Are these two categories inconsistent because the status data state changes?

| username: TiDBer_Xxz1gCH6 | Original post link

The data is not changing.

| username: TiDBer_Xxz1gCH6 | Original post link

Querying this segment alone, the result value randomly jumps between 0, 1, and 2.

The data in the task_subject_log table has not changed. There are only 11 entries, and their status has not changed.

I now suspect that the distinct in t_00 filters out different data each time, causing the result value to vary.

| username: 不想干活 | Original post link

It should have changed.

| username: zhanggame1 | Original post link

There are quite a few bugs with CTE. I have also encountered issues where some query data is lost after a UNION ALL.

| username: 这里介绍不了我 | Original post link

This is probably the worst time TIDB has been hacked.

| username: 这里介绍不了我 | Original post link

Submit the bug for review.

| username: TiDBer_5cwU0ltE | Original post link

Is it good to nest like this? How about sorting out the logic and rewriting the SQL? Maybe this problem won’t exist then.

| username: TiDBer_Xxz1gCH6 | Original post link

0,1,2,3

Without adding status = 1, the result is always 4.