UNION ALL produces unstable result, and results differ from MySQL's
#52987
Labels
affects-8.1
may-affects-5.4
This bug maybe affects 5.4.x versions.
may-affects-6.1
may-affects-6.5
may-affects-7.1
may-affects-7.5
severity/critical
sig/execution
SIG execution
type/bug
This issue is a bug.
Comments
ti-chi-bot
bot
added
may-affects-5.4
|
I see you mentioned 2 problems:
This is the result when I'm trying to reproduce: 127.0.0.1:4000[test]> EXPLAIN ANALYZE SELECT ref_0.c_vj7i1vxsgs as c0, ref_0.c_e as c2, nth_value( cast(ref_0.c_j20ga as char), 1000) over (partition by ref_0.c_vj7i1vxsgs ) as c3, cast(ref_0.c_f as double) as c5 FROM t_xqspe7yna as ref_0 UNION ALL (SELECT DISTINCT ref_6.c_qsm5jm as c0, ref_6.c_ug8wlcrz as c2, substring_index( cast('i' as char), cast(NULL as char), 3) as c3, ref_6.c_qsm5jm as c5 FROM t_n2 as ref_6 WHERE (8126 < ref_6.c_qsm5jm) );
+----------------------------------------+---------+---------+-----------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+---------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+----------------------------------------+---------+---------+-----------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+---------+
| Union_15 | 66.67 | 53 | root | | time:688µs, loops:7, RU:2.609193 | | N/A | N/A |
| ├─Projection_17 | 44.00 | 44 | root | | time:591.3µs, loops:6, Concurrency:OFF | test.t_xqspe7yna.c_vj7i1vxsgs->Column#24, test.t_xqspe7yna.c_e->Column#25, Column#11->Column#26, Column#8->Column#27 | 2.36 KB | N/A |
| │ └─Shuffle_38 | 44.00 | 44 | root | | time:583.4µs, loops:6, ShuffleConcurrency:5 | execution info: concurrency:5, data sources:[Projection_22] | N/A | N/A |
| │ └─Window_19 | 44.00 | 44 | root | | time:2.68ms, loops:10 | nth_value(Column#10, 1000)->Column#11 over(partition by test.t_xqspe7yna.c_vj7i1vxsgs) | N/A | N/A |
| │ └─Sort_33 | 44.00 | 44 | root | | time:2.59ms, loops:10 | test.t_xqspe7yna.c_vj7i1vxsgs | 1.92 KB | 0 Bytes |
| │ └─ShuffleReceiver_67 | 44.00 | 44 | root | | time:2.52ms, loops:10 | | N/A | N/A |
| │ └─Projection_22 | 44.00 | 44 | root | | time:496.4µs, loops:2, Concurrency:OFF | test.t_xqspe7yna.c_vj7i1vxsgs, test.t_xqspe7yna.c_e, cast(test.t_xqspe7yna.c_f, double BINARY)->Column#8, cast(test.t_xqspe7yna.c_j20ga, var_string(5))->Column#10 | 2.80 KB | N/A |
| │ └─TableReader_29 | 44.00 | 44 | root | | time:476µs, loops:2, cop_task: {num: 4, max: 441.5µs, min: 370.3µs, avg: 403.5µs, p95: 441.5µs, max_proc_keys: 16, p95_proc_keys: 16, tot_proc: 194.4µs, tot_wait: 142.7µs, rpc_num: 4, rpc_time: 1.54ms, copr_cache_hit_ratio: 0.00, build_task_duration: 11.9µs, max_distsql_concurrency: 4} | data:TableFullScan_28 | 2.53 KB | N/A |
| │ └─TableFullScan_28 | 44.00 | 44 | cop[tikv] | table:ref_0 | tikv_task:{proc max:0s, min:0s, avg: 0s, p80:0s, p95:0s, iters:4, tasks:4}, scan_detail: {total_process_keys: 44, total_process_keys_size: 3418, total_keys: 48, get_snapshot_time: 73.9µs, rocksdb: {delete_skipped_count: 44, key_skipped_count: 88, block: {}}} | keep order:false | N/A | N/A |
| └─Projection_43 | 22.67 | 9 | root | | time:489.6µs, loops:2, Concurrency:OFF | test.t_n2.c_qsm5jm->Column#24, test.t_n2.c_ug8wlcrz->Column#25, <nil>->Column#26, test.t_n2.c_qsm5jm->Column#27 | 760 Bytes | N/A |
| └─TableReader_47 | 22.67 | 9 | root | | time:482.9µs, loops:2, cop_task: {num: 1, max: 432.5µs, proc_keys: 68, tot_proc: 97.3µs, tot_wait: 40.8µs, rpc_num: 1, rpc_time: 411.3µs, copr_cache_hit_ratio: 0.00, build_task_duration: 3.52µs, max_distsql_concurrency: 1} | data:Selection_46 | 425 Bytes | N/A |
| └─Selection_46 | 22.67 | 9 | cop[tikv] | | tikv_task:{time:0s, loops:2}, scan_detail: {total_process_keys: 68, total_process_keys_size: 5558, total_keys: 69, get_snapshot_time: 21.8µs, rocksdb: {delete_skipped_count: 68, key_skipped_count: 136, block: {}}} | lt(8126, test.t_n2.c_qsm5jm) | N/A | N/A |
| └─TableFullScan_45 | 68.00 | 68 | cop[tikv] | table:ref_6 | tikv_task:{time:0s, loops:2} | keep order:false | N/A | N/A |
+----------------------------------------+---------+---------+-----------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+---------+
13 rows in set (0.00 sec)127.0.0.1:4000[test]> SELECT ref_0.c_vj7i1vxsgs as c0, ref_0.c_e as c2, nth_value( cast(ref_0.c_j20ga as char), 1000) over (partition by ref_0.c_vj7i1vxsgs ) as c3, cast(ref_0.c_f as double) as c5 FROM t_xqspe7yna as ref_0 UNION ALL (SELECT DISTINCT ref_6.c_qsm5jm as c0, ref_6.c_ug8wlcrz as c2, substring_index( cast('i' as char), cast(NULL as char), 3) as c3, ref_6.c_qsm5jm as c5 FROM t_n2 as ref_6 WHERE (8126 < ref_6.c_qsm5jm) );
+-----------------------+----------------------+------+-----------------------+
| c0 | c2 | c3 | c5 |
+-----------------------+----------------------+------+-----------------------+
| 32767.8 | 59.67 | NULL | 32767.8 |
| 65534.7 | 50.47 | NULL | 65534.7 |
| 2147483649.2 | 2147483646.4 | NULL | 2147483649.2 |
| 1.8446744073709552e19 | 61.2 | NULL | 1.8446744073709552e19 |
| 4294967295 | 47.44 | NULL | 4294967295 |
| 9.223372036854776e18 | 95.32 | NULL | 9.223372036854776e18 |
| 65536.3 | 97.83 | NULL | 65536.3 |
| 2147483646.8 | 66.28 | NULL | 2147483646.8 |
| 32769 | 88.48 | NULL | 32769 |
| 80.47 | NULL | NULL | 75.54 |
| 81.45 | 73.36 | NULL | 28.74 |
| 86.53 | 2147483646 | NULL | 65535.2 |
| 1.8446744073709552e19 | 47.46 | NULL | 71.7 |
| 78.45 | 31.73 | NULL | 78.45 |
| 6.74 | 32768.7 | NULL | 6.74 |
| NULL | 2147483649.7 | NULL | NULL |
| 10.76 | 128.6 | NULL | 10.76 |
| 23.23 | 37.4 | NULL | 23.23 |
| 78.47 | 129.8 | NULL | 78.47 |
| 1.84 | 86.22 | NULL | 12.5 |
| 21.28 | 30.26 | NULL | 94.57 |
| 22.6 | 18.8 | NULL | 93.12 |
| 25.45 | 48.43 | NULL | NULL |
| 28.98 | 62.75 | NULL | 86.76 |
| 32.49 | 4294967297.1 | NULL | 2147483646.2 |
| 37.18 | 79.54 | NULL | 11.23 |
| 44.9 | 26.64 | NULL | 29.12 |
| 79.39 | 43.61 | NULL | 2147483646.4 |
| 94.88 | 97.11 | NULL | 59.4 |
| 254 | 65537.3 | NULL | 88.29 |
| 65534.4 | NULL | NULL | 65535.4 |
| 9.223372036854776e18 | 26.27 | NULL | 9.223372036854776e18 |
| 13.27 | 66.79 | NULL | 13.27 |
| 98.2 | 39.87 | NULL | 98.2 |
| NULL | 56.43 | NULL | NULL |
| 127.3 | 128.4 | NULL | 127.3 |
| 87.5 | 4294967297 | NULL | 87.5 |
| 98.77 | 32769.2 | NULL | 98.77 |
| 8.2 | 2147483647 | NULL | 8.2 |
| 257.8 | 9.223372036854776e18 | NULL | 257.8 |
| 47.16 | 93.1 | NULL | 47.16 |
| 87.6 | 100.94 | NULL | 87.6 |
| 255.6 | NULL | NULL | 255.6 |
| NULL | 29.55 | NULL | NULL |
| 32768.2 | 81.63 | NULL | 32768.2 |
| 93.6 | 4.96 | NULL | 93.6 |
| 12.16 | 79.87 | NULL | 128.7 |
| 13.38 | 29.36 | NULL | 38.86 |
| 55.8 | 20.83 | NULL | 29.1 |
| 67.12 | 32769.6 | NULL | 6.65 |
| 67.45 | NULL | NULL | 99.35 |
| 85.72 | 40.24 | NULL | 65.42 |
| 2147483648.1 | 40.86 | NULL | 57.7 |
+-----------------------+----------------------+------+-----------------------+
53 rows in set (0.00 sec) |
|
I'm really sorry for the mix-up. When I uploaded the simplified I apologize for not being clearer. The problem is not about the order. When using This issue happens occasionally, you can try setting up the database multiple times to see if you can replicate it. Below is a result I encountered on a single node version. And this is the result from MySQL. For example, the row appears in the result from TiDB but not in the result from MySQL |
|
Thanks for updating~ |
|
I believe this issue has the same reason as #52992, just it becomes the 1st and 4th column this time. |
time-and-fate
added
sig/execution
|
Duplicated with #52992, close it. |
|
/close |
|
@yibin87: Closing this issue. In response to this:
Instructions for interacting with me using PR comments are available here. If you have questions or suggestions related to my behavior, please file an issue against the kubernetes/test-infra repository. |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
1. Minimal reproduce step (Required)
Firstly, execute
init.sqlto create the table. Then executingerror.sqlyields unexpected results. Note that reproducing these results might not be entirely stable. Typically, it can be completed within three attempts. You can try executingerror.sqlmultiple times or executeinit.sqlagain to rebuild the table.init.sql.txt
error.sql.txt
2. What did you expect to see? (Required)
MySQL result and TiDB result are sometimes consistent.
output_re_main2.log
3. What did you see instead (Required)
The query results from TiDB's are sometimes inconsistent.
output_re_single2.log
4. What is your TiDB version? (Required)
topology:
distributed.yaml:
single.yaml
about us
We are the BASS team from the School of Cyber Science and Technology at Beihang University. Our main focus is on system software security, operating systems, and program analysis research, as well as the development of automated program testing frameworks for detecting software defects. Using our self-developed database vulnerability testing tool, we have identified the above-mentioned vulnerabilities in TiDB that may lead to database logic error.
The text was updated successfully, but these errors were encountered: