time-and-fate
commented
5 days ago I see you mentioned 2 problems:
- The result of tidb is not ordered. This is not a bug. The SQL standard doesn't require the result set to be ordered if you don't specify
ORDER BYin your SQL. The ordered result set can be considered as a special property of MySQL's implementation. - The result set from tidb is different from MySQL. Specifically, in your test result, MySQL returns 53 rows of data while tidb only returns 49 rows. But I failed to reproduce this problem after trying several times on a clustered with 1 tidb, 1 tikv, 1 pd and 1 tiflash. Can you provide more information on the reproduction and the EXPLAIN ANALYZE of the wrong result.
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)
r33s3n6
yibin87
ti-chi-bot[bot]
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.