different behavior of cast between tidb and tiflash
Open
Nobody has claimed this yet.
affects-6.0
affects-6.1
component/compute
component/mpp
severity/minor
type/bug
- Dominant language
- C++
- Stars
- 1k
- Forks
- 423
- Avg merge
- 1d 15h
- Merged PRs (30d)
- 24
Description
two tables, data in col_bigint_key_signed in table_20_utf8_undef is:
mysql> select col_bigint_key_signed from table_20_utf8_undef
-> ;
+-----------------------+
| col_bigint_key_signed |
+-----------------------+
| -9 |
| -9 |
| 13 |
| 68 |
| -18601 |
| 13 |
| 26 |
| -9 |
| NULL |
| -16 |
| -5881 |
| -11623 |
| -104 |
| -2622 |
| 13 |
| 13 |
| -9 |
| -8901 |
| -8125 |
| 1 |
+-----------------------+
20 rows in set (0.00 sec)
For SQL
(SELECT x1 FROM (SELECT `col_bigint_key_unsigned` x1 FROM table_10_utf8_undef t1 UNION ALL SELECT `col_bigint_key_signed` x1 FROM table_20_utf8_undef )) order by x1;
explain between non mpp and mpp:
+------------------------------+---------+-----------+-------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+------------------------------+---------+-----------+-------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------+
| Sort_10 | 30.00 | root | | Column#47 |
| └─Union_13 | 30.00 | root | | |
| ├─IndexReader_18 | 10.00 | root | | index:IndexFullScan_17 |
| │ └─IndexFullScan_17 | 10.00 | cop[tikv] | table:t1, index:col_bigint_key_unsigned(col_bigint_key_unsigned) | keep order:false, stats:pseudo |
| └─Projection_19 | 20.00 | root | | cast(randgen.table_20_utf8_undef.col_bigint_key_signed, bigint(20) UNSIGNED BINARY)->Column#47 |
| └─IndexReader_23 | 20.00 | root | | index:IndexFullScan_22 |
| └─IndexFullScan_22 | 20.00 | cop[tikv] | table:table_20_utf8_undef, index:col_bigint_key_signed(col_bigint_key_signed) | keep order:false, stats:pseudo |
+------------------------------+---------+-----------+-------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------+
mysql> explain (SELECT x1 FROM (SELECT `col_bigint_key_unsigned` x1 FROM table_10_utf8_undef t1 UNION ALL SELECT `col_bigint_key_signed` x1 FROM table_20_utf8_undef )) order by x1;
+--------------------------------+---------+--------------+---------------------------+-------------------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+--------------------------------+---------+--------------+---------------------------+-------------------------------------------------------------------------------------------------+
| Sort_10 | 30.00 | root | | Column#47 |
| └─TableReader_34 | 30.00 | root | | data:ExchangeSender_33 |
| └─ExchangeSender_33 | 30.00 | cop[tiflash] | | ExchangeType: PassThrough |
| └─Union_14 | 30.00 | cop[tiflash] | | |
| ├─TableFullScan_30 | 10.00 | cop[tiflash] | table:t1 | keep order:false, stats:pseudo |
| └─Projection_31 | 20.00 | cop[tiflash] | | cast(randgen1.table_20_utf8_undef.col_bigint_key_signed, bigint(20) UNSIGNED BINARY)->Column#47 |
| └─TableFullScan_32 | 20.00 | cop[tiflash] | table:table_20_utf8_undef | keep order:false, stats:pseudo |
+--------------------------------+---------+--------------+---------------------------+-------------------------------------------------------------------------------------------------+
7 rows in set (0.00 sec)
TiFlash result:
+----------------------+
| x1 |
+----------------------+
| NULL |
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
| 13 |
| 13 |
| 13 |
| 13 |
| 13 |
| 13 |
| 24 |
| 26 |
| 68 |
| 68 |
| 32354 |
| 18446744073709533015 |
| 18446744073709539993 |
| 18446744073709542715 |
| 18446744073709543491 |
| 18446744073709545735 |
| 18446744073709548994 |
| 18446744073709551512 |
| 18446744073709551600 |
| 18446744073709551607 |
| 18446744073709551607 |
| 18446744073709551607 |
| 18446744073709551607 |
+----------------------+
TiDB result:
+-------+
| x1 |
+-------+
| NULL |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
| 13 |
| 13 |
| 13 |
| 13 |
| 13 |
| 13 |
| 24 |
| 26 |
| 68 |
| 68 |
| 32354 |
+-------+
Contributor guide
No contributing guide indexed for this repository
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- Open a pull request that references the issue number.
Research direction
Start by reproducing the reported UNION ALL and ORDER BY query, then compare the TiDB and TiFlash EXPLAIN plans and results. Trace the unsigned BIGINT cast behavior in the relevant execution paths and add a regression check showing consistent conversion and ordering across both engines.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- cpp, sql
- Domain
- databases, distributed-systems
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 30/100