[MDEV-13417] UPDATE produces wrong values if an updated column is later used as an update source Created: 2017-08-01  Updated: 2019-04-27  Resolved: 2018-02-13

Status: Closed
Project: MariaDB Server
Component/s: Data Manipulation - Update
Affects Version/s: 10.0, 10.1, 10.2
Fix Version/s: 10.3.5

Type: Bug Priority: Blocker
Reporter: Alexander Barkov Assignee: Sergei Golubchik
Resolution: Fixed Votes: 0
Labels: Compatibility

Issue Links:
Relates
relates to MDEV-13418 Compatibility: The order of evaluatio... Stalled
relates to MDEV-15122 Simultaneous assignment for LOAD..SET Open
relates to MDEV-15123 Make multiple user variable assignmen... Open
relates to MDEV-15124 Simultaneous assignment for INSERT ..... Open
relates to MDEV-19304 Segfault in ALTER TABLE after UPDATE ... Closed

 Description   

This problem was originally reported by Jerome B. on the maria-developers list on 2017-07-21:
This script:

drop table t1;
 
create table t1 (c1 integer, c2 integer, c3 integer);
 
insert into t1(c1,c2,c3) values (1,1,1);
 
update t1
 
   set c1 = c1+1,
 
       c2 = c1+1,
 
       c3 = c2+1;
 
select * from t1;

produces this result:

+------+------+------+
 
| c1   | c2   | c3   |
 
+------+------+------+
 
|    2 |    3 |    4 |
 
+------+------+------+

Other databases return 2 for all columns:

PostgreSQL:

 c1 | c2 | c3 
----+----+----
 
  2 |  2 |  2

Oracle:

	C1	   C2	      C3
 
---------- ---------- ----------
 
	 2	    2	       2

SQLite3:

2|2|2

MariaDB's behavior contradicts the SQL standard, which says in the section <update statement: searched>:

10) The <update source> of each <set clause> contained in SCL is effectively evaluated for each row of T before any row of T is updated.

 Comments   
Comment by Sergei Golubchik [ 2017-08-04 ]

This is and always was the documented behavior. When fixing it, think of backward compatibility implications.

Comment by Elena Stepanova [ 2017-08-04 ]

I'd rather it was closed as "Won't fix" (or, if it's required for the compatibility project, made somehow configurable, with the default behavior being the old one). I think fixing it will cause more troubles than gains.

Comment by Sergei Golubchik [ 2017-08-05 ]

Yes, I think we'll have to have the standard behavior eventually. "made somehow configurable" — that's exactly what I implied in "think of backward compatibility implications". Options:

  • do it only in ORACLE mode. I don't like it, because it's standard compatibility, not oracle compatibility issue
  • do it only in TRADITIONAL mode
  • do it only in ANSI mode
  • have a new STANDARD_UPDATE_BEHAVIOR mode or @@standard_update_behavior variable
  • do it always, by default, and disable with @@old=OLD_UPDATE_BEHAVIOR.
Comment by Alexander Barkov [ 2017-08-11 ]

I also do not like to do prior evaluation of <update source> only in ORACLE mode.

Doing prior evaluation only in TRADITIONAL or ANSI mode also does not look good enough.
ORACLE does not seem to include neither TRADITIONAL nor ANSI.
So Oracle users would have to do something like this:

SET sql_mode='ORACLE,TRADITIONAL';

which looks not so convenient.

I prefer to have a new sql_mode flag, and turn it on by default, to have all users get used with the standard behavior.

However, STANDARD_UPDATE_BEHAVIOR sounds too optimistic, because there might be some other aspects of UPDATE (not related to the order of evaluation of value sources) that expose non-standard behavior

Why not have something like this:

SET sql_mode=NO_UPDATE_SOURCE_PRIOR_EVALUATION;

to activate the legacy non-standard behavior?

Generated at Thu Feb 08 08:05:26 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.