select语句中临时变量@i与decimal为什么没有隐式转换，报not *types.MyDecimal

[TiDB_C罗]

建表语句、测试数据

create table a (name varchar(20), v_int int, v_decimal decimal(10,2));
insert into  a values('a',18,100.00),('b',19,200.00),('c',18,300.00);

[test]>desc a;
+-----------+---------------+------+------+---------+-------+
| Field     | Type          | Null | Key  | Default | Extra |
+-----------+---------------+------+------+---------+-------+
| name      | varchar(20)   | YES  |      | NULL    |       |
| v_int     | int(11)       | YES  |      | NULL    |       |
| v_decimal | decimal(10,2) | YES  |      | NULL    |       |
+-----------+---------------+------+------+---------+-------+
3 rows in set (0.00 sec)

[test]>select * from a;
+------+-------+-----------+
| name | v_int | v_decimal |
+------+-------+-----------+
| a    |    18 |    100.00 |
| b    |    19 |    200.00 |
| c    |    18 |    300.00 |
+------+-------+-----------+
3 rows in set (0.00 sec)

1.临时变量与常量隐式转换

[test]>select name, (@i:=v_decimal+1) as rs  from a, (select @i:=0) b ;
+------+--------+
| name | rs     |
+------+--------+
| a    | 101.00 |
| b    | 201.00 |
| c    | 301.00 |
+------+--------+
3 rows in set (0.01 sec)

[test]>select name, (@i:=v_decimal+1.0) as rs  from a, (select @i:=0) b ;
+------+--------+
| name | rs     |
+------+--------+
| a    | 101.00 |
| b    | 201.00 |
| c    | 301.00 |
+------+--------+
3 rows in set (0.00 sec)

2.decimal字段与临时变量未能隐式转换

[test]>select name, (@i:=v_decimal+@i) as rs  from a, (select @i:=0) b ;
ERROR 1105 (HY000): interface conversion: interface {} is nil, not *types.MyDecimal
[test]>select name, (@i:=v_decimal+@i) as rs  from a, (select @i:=0.0) b ;
+------+--------+
| name | rs     |
+------+--------+
| a    | 100.00 |
| b    | 300.00 |
| c    | 600.00 |
+------+--------+
3 rows in set (0.01 sec)

3.聚合函数与临时变量未能隐式转换

select name, (@i:=@i+sum(v_int)) from a , (select @i:=0) b  group by name;
ERROR 1105 (HY000): interface conversion: interface {} is nil, not *types.MyDecimal
[test]>select name, (@i:=@i+v_int) from a , (select @i:=0) b ;
+------+----------------+
| name | (@i:=@i+v_int) |
+------+----------------+
| a    |             18 |
| b    |             37 |
| c    |             55 |
+------+----------------+
3 rows in set (0.01 sec)

问题：在MySQL上不管是decimal字段还是sum的结果都是可以隐式转换。在TiDB中Decimal与临时变量却没有隐藏转换，这是BUG吗？

[Billmay表妹 (表妹)]

老师，你这个是总结经验，还是想要提问？

[TiDB_C罗]

为什么没有隐式转换，是bug还是有意为之？

[Billmay表妹 (表妹)]

TiDB中的@i是用户自定义变量，而不是临时表。在TiDB中，用户自定义变量的类型是字符串类型，因此在使用用户自定义变量时，需要进行类型转换。decimal类型是一种高精度的数字类型，而字符串类型无法隐式转换为decimal类型，因此需要使用CAST函数进行显式转换。例如：

SET @i = '123.45';
SELECT CAST(@i AS DECIMAL(5,2));

这里将字符串类型的@i转换为DECIMAL(5,2)类型。如果不进行类型转换，就会出现报错信息中提到的“not *types.MyDecimal”错误。