Bug #77135 Update on varchar and text columns produce incorrect results
Submitted: 22 May 2015 16:48 Modified: 19 Jul 2015 21:19
Reporter: Chris Sims Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Data Types Severity:S3 (Non-critical)
Version:5.6.21-70.1 OS:Linux
Assigned to: CPU Architecture:Any

[22 May 2015 16:48] Chris Sims
Description:
Updating varchar and text fields in the same update statement can produce incorrect results.

Specifically, if you set the text field to the varchar field, then change the varchar field, the text field's end results will have the initial characters replaced with what is set in the varchar field.

The expected results from the steps should be:
+--------+-------------+
| a      | b           |
+--------+-------------+
| inject | start trail |
+--------+-------------+
and it would be if both fields are varchar. Instead the results are:
+--------+-------------+
| a      | b           |
+--------+-------------+
| inject | injecttrail |
+--------+-------------+

How to repeat:
CREATE TABLE `test_bug` (
  `a` varchar(50) DEFAULT NULL,
  `b` text
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

insert into test_bug (a, b) values ('start trail', '');
update test_bug set b = a, a = 'inject';
select * from test_bug;
[25 May 2015 15:08] Sinisa Milivojevic
This is fully tested and verified.

The correct results should have been:

"inject", "start trail"
[19 Jul 2015 21:19] Paul Dubois
Noted in 5.5.46, 5.6.27, 5.7.9, 5.8.0 changelogs.

Updating VARCHAR and TEXT columns in the same UPDATE statement could
produce incorrect results. When a VARCHAR column was assigned to a
TEXT column and the VARCHAR column was then set to a different value,
the TEXT column's result contained the VARCHAR column's new value.