Got interface conversion error when there are get_sys_var expr

[guo-shaoge]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

create table test (a datetime , b bigint,c bigint);
insert into test values('2010-10-10 10:10:10', 100, 100);
select a,b as 'asd',@total:=@total+b from (select date(a) as a,sum(c) as b from test group by date(a)) as temp,(select @total:=0) as T1 order by a;

2. What did you expect to see? (Required)

runs ok

3. What did you see instead (Required)

ERROR 1105 (HY000): interface conversion: interface {} is nil, not *types.MyDecimal

4. What is your TiDB version? (Required)

MySQL [test]> select tidb_version();
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version()                                                                                                                                                                                                                                                                                           |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v7.2.0-alpha
Edition: Community
Git Commit Hash: 7eff8ce629d00cf4ab1eeda82a1ecd5a4e394b02
Git Branch: heads/refs/tags/v7.2.0-alpha
UTC Build Time: 2023-05-03 14:33:44
GoVersion: go1.20.3
Race Enabled: false
TiKV Min Version: 6.2.0-alpha
Check Table Before Drop: false
Store: tikv |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.009 sec)

[guo-shaoge]

1. (select @total:=0) makes @total as int type
2. @total:=@total+b: b is decimal type, so + will call getVar.evalDecimal, but @total is actually a int value, so we got error here

A cast expr is missing for getVar, check how getVar is built, will not add cast, we should add cast when build getVar expr

[zanmato1984]

This is variable related so changing sig to planner.

[hi-rustin]

I tested it on MySQL:

use test;
create table test (a datetime, b bigint, c float);
insert into test values('2010-10-10 10:10:10', 100, 100.0);
SELECT @total := @total + c FROM (SELECT c FROM test) AS temp, (SELECT @total := 200) AS T1;

It outputted:

mysql> SELECT @total := @total + c FROM (SELECT c FROM test) AS temp, (SELECT @total := 200) AS T1;
+----------------------+
| @total := @total + c |
+----------------------+
|                  300 |
+----------------------+
1 row in set, 2 warnings (0.00 sec)

mysql> show warnings;
+---------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                                                                                              |
+---------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1287 | Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'. |
| Warning | 1287 | Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'. |
+---------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)