MySQL Bugs: #84629: In update statement all values not getting initialized

Bug #84629	In update statement all values not getting initialized
Submitted:	23 Jan 2017 22:21	Modified:	31 Jan 2017 17:15
Reporter:	Harish Naik	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S4 (Feature request)
Version:	5.6, 5.6.35, 5.7.17, 5.5.54	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Hi,

 The issues is all values in the update statement not getting initialized.

 Not sure if this is related to http://bugs.mysql.com/bug.php?id=84416

 But the bug behavior is completely different, the problem of values becoming zeros  is reported in the above mentioned bug. But here the issue mentioned is about (varchar type) second column values not getting changed in certain update statement.

How to repeat:
Please find the steps to repeat.

mysql> create table  var_test(v1 varchar(100),v2 varchar(100));

mysql> insert into var_test values('Karwar 581301 ','Bangalore 560001');

mysql> select * from var_test;
+----------------+------------------+
| v1             | v2               |
+----------------+------------------+
| Karwar 581301  | Bangalore 560001 |
+----------------+------------------+

mysql> update var_test set v1=v2='Kolkata 700001';
Query OK, 1 row affected (0.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from var_test;
+------+------------------+
| v1   | v2               |
+------+------------------+
| 0    | Bangalore 560001 |
+------+------------------+

Suggested fix:
If V1 = 'text 1' and v2 = 'text 2'

Then upon updating v1 = v2 = 'New text' . v1 and v2 must contain 'new text'.

Thank you for the report.

This is not a bug.

SQL does not support the C/C++ syntax:

   a= b= expression

which would assign the value of 'expression' to both columns a and b.

Thus, the statement

  update var_test set v1=v2='Kolkata 700001'

is executed as

  update var_test set v1= (v2 = 'Kolkata 700001');

This means, the boolean expression containing v2 is first evaluated giving false, aka 0, which is then converted to character string and assigned to v1.

It is only MySQL's relaxed data conversion rules that lets this statement be executed at all.

Hi!

This is still not a bug. However, we concluded that it is a feature request. This feature request requires some drastic changes in the behavior of MySQL server. Millions of sites depend on that behavior, so the change will not come soon. It will happen in the rather distant future.