MySQL Bugs: #6427: Replace error

Bug #6427	Replace error
Submitted:	4 Nov 2004 10:34	Modified:	23 Feb 2005 18:05
Reporter:	Struchkov Vladimir	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.1.6-gamma-standard-log	OS:	Linux (Linux)
Assigned to:	Timour Katchaounov	CPU Architecture:	Any

Description:
My table:
create table site_click_statistics (siteId int unsigned not null default 0, iclick int unsigned not null default 0, tclick int unsigned not null default 0, primary key (siteId), FOREIGN KEY (siteId) REFERENCES sites (siteId) ON DELETE CASCADE);

If I get some clicks on link I run 'replace into site_click_statistics set iclick=iclick+1, tclick=tclick+2,siteId=10'

But  every time I do this query I see:
mysql> select * from site_click_statistics;
+--------+--------+--------+
| siteId | iclick | tclick |
+--------+--------+--------+
|     10 |      1 |      2 |
+--------+--------+--------+

I read documentation once more :). ''You can't refer to values from the old row and use them in the new row. It appeared that you could do this in some old MySQL versions, but that was a bug that has been corrected."

My query is not correct  but mysql don't alert me.

How to repeat:
every time

Verified with latest BK 4.1 source tree on Linux.

Given that this is a MySQL extension and the standard doesn't say anything about what should happen in such cases, the current behavior is correct since the documentation does not say that any error will be issued. What happens is that the server silently omits updating such columns. It may be argued whether this is the most resonable behavior, but a request for a change in this behavior would be a feature request.

I reading http://dev.mysql.com/doc/mysql/en/REPLACE.html now.

Values for all columns are taken from the values specified in the REPLACE statement. Any missing columns are set to their default values, just as happens for INSERT. You can't refer to values from the old row and use them in the new row. It appeared that you could do this in some old MySQL versions, but that was a bug that has been corrected.

At my oppinion this is a bug as the statement is incorrect even syntatically.

The common logic in ANSI SQL is - if something is not going to work error should be reported, In MySQL it was normally much less restrictive but even it probably should 
throw an error here as it  will never work.  At least warning should be generated. 

This might be not showstopper error and it might be not needed to be fixed in the 4.1 release but it is deffinetely a bug.

Same for INSERT:

insert into site_click_statistics set iclick=iclick+1, tclick=tclick+2,siteId=10;

(no wonder as REPLACE is just a variant of INSERT).

When a column refers to itself, it's not an "old value" - as there is no "old value" for INSERT, but a default value for this column. That's why you see (1, 2, 10).

So there's no ambiguity here, everything is well defined. Though such a behaviour may be confusing, I agree.

Issuing an error for the case when the right side of REPLACE contains a column would be
change in behavior, therefore for this version (4.1) it is better to clarify the documentation.

This is the change suggested by Peter Gulutzan:
The documentation could give the wrong impression saying "You can't refer to values
from the old row and use them in the new row" if you can refer to what seems to be an
old column value. the wording should have been, instead: "If you use column references such
as SET col_name=col_name+1 then the result value will be the default value for col_name,
for example NULL, and not the value that was in the original row".

Rephrased the para in question:

Values for all columns are taken from the values specified in the
`REPLACE' statement.  Any missing columns are set to their default
values, just as happens for `INSERT'.  You can't refer to values from
the current row and use them in the new row.  If you use an assignment
such as `SET COL_NAME = COL_NAME + 1', the reference to the column name
on the right hand side is treated as `DEFAULT(COL_NAME)', so the
assignment is equivalent to `SET COL_NAME = DEFAULT(COL_NAME) + 1'.