Different results of Function :=(Assign a value)

[SunRunAway]

Description

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. What did you do?
   If possible, provide a recipe for reproducing the error.

SELECT  'x' = ( @A := 0 ) ;

2. What did you expect to see?

mysql> SELECT  'x' = ( @A := 0 ) ;
+-------------------+
| 'x' = ( @A := 0 ) |
+-------------------+
|                 1 |
+-------------------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+-------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                           |
+---------+------+-------------------------------------------------------------------------------------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'x' |
+---------+------+-------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

3. What did you see instead?

mysql> SELECT  'x' = ( @A := 0 ) ;
+-------------------+
| 'x' = ( @A := 0 ) |
+-------------------+
|                 0 |
+-------------------+
1 row in set (0.00 sec)

4. What version of TiDB are you using (tidb-server -V or run select tidb_version(); on TiDB)?
   0ea1bf0

SIG slack channel

#sig-exec

Score

• 300

Mentor

• @lzmhhh123

[gaoxingliang]

/assign

[ghost]

Confirming that this can still be reproduced on master:

SELECT  'x' = ( @A := 0 ) ;

..

mysql> SELECT  'x' = ( @A := 0 ) ;
+-------------------+
| 'x' = ( @A := 0 ) |
+-------------------+
|                 0 |
+-------------------+
1 row in set (0.00 sec)

mysql> select tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v4.0.0-beta.2-769-g2024e4463
Edition: Community
Git Commit Hash: 2024e44636f2fb9798982a1cc0ec22176e07f5c1
Git Branch: master
UTC Build Time: 2020-07-14 09:26:07
GoVersion: go1.13
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false
1 row in set (0.00 sec)

[lzmhhh123]

For MySQL 5.7.29, the result is the same as TiDB.

mysql > SELECT  'x' = ( @A := 0 ) ;
+-------------------+
| 'x' = ( @A := 0 ) |
+-------------------+
|                 1 |
+-------------------+
1 row in set, 1 warning (0.00 sec)

mysql > show warnings;
+---------+------+---------------------------------------+
| Level   | Code | Message                               |
+---------+------+---------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'x' |
+---------+------+---------------------------------------+
1 row in set (0.01 sec)

mysql > select version();
+-----------+
| version() |
+-----------+
| 5.7.29    |
+-----------+
1 row in set (0.00 sec)

Maybe we should close the issue. What's your opinion? @SunRunAway @nullnotnil

[ghost]

I am not getting the same result in TiDB and MySQL. TiDB returns 0, with MySQL returning either 1 or 2 warnings:

tidb> SELECT  'x' = ( @A := 0 ) ;
+-------------------+
| 'x' = ( @A := 0 ) |
+-------------------+
|                 0 |
+-------------------+
1 row in set (0.00 sec)

tidb> select tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v4.0.0-beta.2-1463-gff69dfd41-dirty
Edition: Community
Git Commit Hash: ff69dfd41fd3cbeb9478102b38fbbc83e368ca0a
Git Branch: is-client-errors
UTC Build Time: 2020-11-03 02:42:02
GoVersion: go1.13.15
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false
1 row in set (0.00 sec)

mysql [localhost:5731] {msandbox} ((none)) > SELECT  'x' = ( @A := 0 ) ;
+-------------------+
| 'x' = ( @A := 0 ) |
+-------------------+
|                 1 |
+-------------------+
1 row in set, 1 warning (0.00 sec)

mysql [localhost:8022] {msandbox} ((none)) > SELECT  'x' = ( @A := 0 ) ;
+-------------------+
| 'x' = ( @A := 0 ) |
+-------------------+
|                 1 |
+-------------------+
1 row in set, 2 warnings (0.00 sec)

[Win-Man]

/pick-up

[ti-challenge-bot]

Pick up success.

[ichn-hu]

@Win-Man Friendly ping~ Do you have any updates on this?

[Win-Man]

@ichn-hu I think this problem is caused by rewriteVariable, TiDB handles variable A as mysql.TypeString. But it support to be handled as mysql.TypeLongLong.

But I have not yet figured out how to fix it.

[lzmhhh123]

@ichn-hu I think this problem is caused by rewriteVariable, TiDB handles variable A as mysql.TypeString. But it support to be handled as mysql.TypeLongLong.

But I have not yet figured out how to fix it.

Could #18973 fix this issue? @Win-Man

[ti-challenge-bot]

@Win-Man You did not submit PR within 7 days, so give up automatically.

[Win-Man]

@lzmhhh123 I tested it, #18973 could fix this problem.

mysql>  SELECT  'x' = ( @A := 0 ) ;
+-------------------+
| 'x' = ( @A := 0 ) |
+-------------------+
|                 1 |
+-------------------+
1 row in set, 2 warnings (0.00 sec)

mysql> show warnings;
+---------+------+--------------------------------------+
| Level   | Code | Message                              |
+---------+------+--------------------------------------+
| Warning | 1292 | Truncated incorrect FLOAT value: 'x' |
| Warning | 1292 | Truncated incorrect FLOAT value: 'x' |
+---------+------+--------------------------------------+
2 rows in set (0.01 sec)

I don't need to fix it by myself.

[ti-srebot]

Please edit this comment or add a new comment to complete the following information

Not a bug

1. Remove the 'type/bug' label
2. Add notes to indicate why it is not a bug

Duplicate bug

1. Add the 'type/duplicate' label
2. Add the link to the original bug

Bug

Note: Make Sure that 'component', and 'severity' labels are added
Example for how to fill out the template: #20100

1. Root Cause Analysis (RCA) (optional)

2. Symptom (optional)

3. All Trigger Conditions (optional)

4. Workaround (optional)

5. Affected versions

6. Fixed versions