search

pingcap / tiflash

The analytical engine for TiDB and TiDB Cloud. Try free: https://tidbcloud.com/free-trial
https://docs.pingcap.com/tidb/stable/tiflash-overview
Apache License 2.0
945 stars 409 forks source link

different behavior of cast between tidb and tiflash #1957

Open hanfei1991 opened 3 years ago

hanfei1991 commented 3 years ago

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 |
+-------+
hanfei1991 commented 3 years ago

eviroument for quick check: h85 mysql -uroot -P 8500 -h 127.0.0.1 -D randgen1