Why is there no implicit conversion between the temporary variable @i and decimal in the select statement, resulting in the error not *types.MyDecimal?

Translated
1

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

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

| username: TiDB_C罗

Table creation statement and test data

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. Implicit conversion between temporary variables and constants

[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 fields and temporary variables do not implicitly convert

[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. Aggregate functions and temporary variables do not implicitly convert

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)

Question: In MySQL, both decimal fields and the results of sum can be implicitly converted. In TiDB, however, Decimal and temporary variables do not implicitly convert. Is this a bug?

2
| username: Billmay表妹 | Original post link

Teacher, are you summarizing your experience or asking a question?

3
| username: TiDB_C罗 | Original post link

Why is there no implicit conversion? Is it a bug or intentional?

4
| username: Billmay表妹 | Original post link

In TiDB, @i is a user-defined variable, not a temporary table. In TiDB, the type of user-defined variables is string type, so type conversion is required when using user-defined variables. The decimal type is a high-precision numeric type, and the string type cannot be implicitly converted to the decimal type, so the CAST function needs to be used for explicit conversion. For example:

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

Here, the string type @i is converted to the DECIMAL(5,2) type. If type conversion is not performed, the error message “not *types.MyDecimal” mentioned will occur.

5
| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.