varchar和bigint 关联输出错误

🌞 建议反馈 产品缺陷
21

“精华”

22

隐式转换的问题，就不能这么玩

23

https://github.com/pingcap/tidb/issues/37260 这里有解释，如果有变量或参数能够控制使用不同精度对比满足不同场景会更好

24

主要是转换了 没报错，输出内容也是错误的，那怕报个错也好。

25

嗯 是的 看看tidb是否有参数控制

26

哈哈。是的

27

是的 感谢解答。

28

可以看一下ob的执行计划嘛。

29

前面发的就是

30

:handshake: :handshake: :handshake:

31

可以提前转换字段类型再关联查询

32

做了十几类库的对比测试： 底层使用Mysql的都有这问题，底层没用mysql且兼容mysql的只有tidb有问题，学习oracle的都没问问题
隐式转换测试.pdf (856.6 KB)

2 个赞
33

:+1: :+1: :+1:

34

牛，测了这么多

35

老哥 这个测试太牛了。

36

大佬说的对，同意+1

短数据的explain

mysql> explain select t1.id id1, t1.name name1, t2.id id2, t2.name name2 from t1,t2 where t1.name = t2.name;
+------------------------------+----------+-----------+---------------+-----------------------------------------------------------------------+
| id                           | estRows  | task      | access object | operator info                                                         |
+------------------------------+----------+-----------+---------------+-----------------------------------------------------------------------+
| HashJoin_12                  | 12500.00 | root      |               | inner join, equal:[eq(Column#5, Column#6)]                            |
| ├─Projection_17(Build)       | 10000.00 | root      |               | test.t2.id, test.t2.name, cast(test.t2.name, double BINARY)->Column#6 |
| │ └─TableReader_19           | 10000.00 | root      |               | data:TableFullScan_18                                                 |
| │   └─TableFullScan_18       | 10000.00 | cop[tikv] | table:t2      | keep order:false, stats:pseudo                                        |
| └─Projection_14(Probe)       | 10000.00 | root      |               | test.t1.id, test.t1.name, cast(test.t1.name, double BINARY)->Column#5 |
|   └─TableReader_16           | 10000.00 | root      |               | data:TableFullScan_15                                                 |
|     └─TableFullScan_15       | 10000.00 | cop[tikv] | table:t1      | keep order:false, stats:pseudo                                        |
+------------------------------+----------+-----------+---------------+-----------------------------------------------------------------------+
7 rows in set (0.00 sec)

长数据的explain

mysql> explain select t1.id id1, t1.name name1, t2.id id2, t2.name name2 from t1,t2 where t1.name = t2.name;
+------------------------------+----------+-----------+---------------+-----------------------------------------------------------------------+
| id                           | estRows  | task      | access object | operator info                                                         |
+------------------------------+----------+-----------+---------------+-----------------------------------------------------------------------+
| HashJoin_12                  | 12500.00 | root      |               | inner join, equal:[eq(Column#5, Column#6)]                            |
| ├─Projection_17(Build)       | 10000.00 | root      |               | test.t2.id, test.t2.name, cast(test.t2.name, double BINARY)->Column#6 |
| │ └─TableReader_19           | 10000.00 | root      |               | data:TableFullScan_18                                                 |
| │   └─TableFullScan_18       | 10000.00 | cop[tikv] | table:t2      | keep order:false, stats:pseudo                                        |
| └─Projection_14(Probe)       | 10000.00 | root      |               | test.t1.id, test.t1.name, cast(test.t1.name, double BINARY)->Column#5 |
|   └─TableReader_16           | 10000.00 | root      |               | data:TableFullScan_15                                                 |
|     └─TableFullScan_15       | 10000.00 | cop[tikv] | table:t1      | keep order:false, stats:pseudo                                        |
+------------------------------+----------+-----------+---------------+-----------------------------------------------------------------------+
7 rows in set (0.01 sec)

长数据转signed后的explain

mysql> explain select t1.id id1, t1.name name1, t2.id id2, t2.name name2 from t1,t2 where cast(t1.name as signed) = t2.name;
+-------------------------------+---------+-----------+---------------+---------------------------------------------------------------------------+
| id                            | estRows | task      | access object | operator info                                                             |
+-------------------------------+---------+-----------+---------------+---------------------------------------------------------------------------+
| Projection_10                 | 3.75    | root      |               | test.t1.id, test.t1.name, test.t2.id, test.t2.name                        |
| └─HashJoin_12                 | 3.75    | root      |               | inner join, equal:[eq(test.t2.name, Column#5)]                            |
|   ├─TableReader_14(Build)     | 3.00    | root      |               | data:TableFullScan_13                                                     |
|   │ └─TableFullScan_13        | 3.00    | cop[tikv] | table:t2      | keep order:false, stats:pseudo                                            |
|   └─Projection_15(Probe)      | 3.00    | root      |               | test.t1.id, test.t1.name, cast(test.t1.name, bigint(22) BINARY)->Column#5 |
|     └─TableReader_17          | 3.00    | root      |               | data:TableFullScan_16                                                     |
|       └─TableFullScan_16      | 3.00    | cop[tikv] | table:t1      | keep order:false, stats:pseudo                                            |
+-------------------------------+---------+-----------+---------------+---------------------------------------------------------------------------+
7 rows in set (0.00 sec)

图片
图片762×684 25 KB

1 个赞
37

两种方式，一个 是转bigint，一个是转binary

mysql> select t1.id id1, t1.name name1, t2.id id2, t2.name name2
    -> from t1,t2
    -> where cast(t1.name as unsigned) = t2.name;
+-----+--------------------+-----+--------------------+
| id1 | name1              | id2 | name2              |
+-----+--------------------+-----+--------------------+
|   1 | 123456789012345611 |   2 | 123456789012345611 |
|   1 | 123456789012345611 |   1 | 123456789012345611 |
|   3 | 123123             |   3 |             123123 |
+-----+--------------------+-----+--------------------+
3 rows in set (0.01 sec)

mysql> select t1.id id1, t1.name name1, t2.id id2, t2.name name2
    -> from t1,t2
    -> where t1.name = cast(t2.name as binary);
+-----+--------------------+-----+--------------------+
| id1 | name1              | id2 | name2              |
+-----+--------------------+-----+--------------------+
|   1 | 123456789012345611 |   2 | 123456789012345611 |
|   1 | 123456789012345611 |   1 | 123456789012345611 |
|   3 | 123123             |   3 |             123123 |
+-----+--------------------+-----+--------------------+
3 rows in set (0.03 sec)
38

此话题已在最后回复的 60 天后被自动关闭。不再允许新回复。