MySQL Bugs: #103137: Deprecated VALUES vs INSERT SELECT ON DUPLICATE KEY UPDATE

Bug #103137	Deprecated VALUES vs INSERT SELECT ON DUPLICATE KEY UPDATE
Submitted:	29 Mar 2021 10:03	Modified:	6 May 2021 22:58
Reporter:	Jens Hatlak	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	8.0.20	OS:	Any
Assigned to:	Jon Stephens	CPU Architecture:	Any

Description:
As described in the forum [1], the documentation [2] and 8.0.20 release notes [3] are lacking information about how to adapt existing, more complex INSERT SELECT ON DUPLICATE KEY UPDATE queries using the deprecated/to be removed VALUES syntax.

The case without SELECT is covered well enough (including examples and alternative row aliases available since 8.0.19) so this is just for the case with SELECT.

[1] https://forums.mysql.com/read.php?12,691028,691028
[2] https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html
[3] https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-20.html#mysqld-8-0-20-deprecation...

How to repeat:
Given a more complex INSERT SELECT ON DUPLICATE KEY UPDATE query including VALUES syntax, read the documentation [2] and try to understand how that query needs to be rewritten such that it is accepted without issuing a deprecation warning.

By more complex I refer to the SELECT part not simply listing some column names but either containing "*" or calculations, function calls etc.

[2] https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html

Suggested fix:
1. Add a statement explicitly stating that there is no alternative convenience syntax for INSERT SELECT ON DUPLICATE KEY UPDATE queries using the deprecated/to be removed VALUES syntax

2. Add an example for how to convert a query including "SELECT *"

3. Add an example for how to convert a query including calculations, function calls etc. in the SELECT part

(2 and 3 may be combined in to a single example covering both cases.)

Hi Mr. Hatlak,

Thank you for your documentation bug report.

Can you please send us the exact statement that causes the warning about the deprecated syntax. You can also send us the exact CREATE TABLE for the table(s) involved.

We are waiting on your feedback.

Hi Mr. Hatlak,

We have studied further your report and found out that our documentation depicts very clearly what will be the syntax that is supported.

This is the relevant extract from our documentation:

------------------------------------------------------

Instead of:

INSERT INTO t1 (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE c=3;
INSERT INTO t1 (a,b,c) VALUES (4,5,6)
  ON DUPLICATE KEY UPDATE c=9;

Use this:

INSERT INTO t1 (a,b,c) VALUES (1,2,3),(4,5,6) AS new
  ON DUPLICATE KEY UPDATE c = new.a+new.b;
------------------------------------------------------

This is all contained in the chapter found in your item 2).

Not a bug.

Guys, I'm getting slightly angry now, especially I already lost a lot of time explaining all this in the forum.

Please carefully read my initial comment and find that this is about the variant with *SELECT* !!!!!!oneeleven

Oh and the example can *also* be found in the forum thread, you just have to actually *read* it.

https://forums.mysql.com/read.php?12,691028,695604#msg-695604

1. Add a statement explicitly stating that there is no alternative convenience syntax for INSERT SELECT ON DUPLICATE KEY UPDATE queries using the deprecated/to be removed VALUES syntax

I am not aware of any statement containing VALUES that cannot be expressed with alternative syntax. Please give an example.

2. Add an example for how to convert a query including "SELECT *"

create table t1(a int primary key, b int);
insert into t1 values(1,1),(2,4);
create table t2(c int, d int);
insert into t2 values(1,11),(2,12);

insert into t1 select * from t2 on duplicate key update b=b+c;

3. Add an example for how to convert a query including calculations, function calls etc. in the SELECT part

I think this is best expressed with a derived table, like

insert into t1
select *
from (select c,c+d as e from t2) as dt
on duplicate key update b=b+e;

Thanks Roy. This is the kind of discussion I would've liked to have in the first place in the forum thread. As you can see there, I wasn't sure that there was indeed no alternative syntax to cover my case; the lack of useful answers there just made me think that.

Taking all your points from your comment into account I now think that only point 3 is relevant for documentation / upgrade purposes, esp. your suggested fix of using a derived table. Upon reconsideration I did not find a reason why "SELECT *" would need any special case. In other words, please document that

  insert into t1
  select c, c+d from t2
  on duplicate key update b = values(b);

should be converted to

  insert into t1
  select * from (select c, c+d as e from t2) as dt
  on duplicate key update b = e;

in order to prevent the deprecation warning and not have to perform the calculation multiple times. Thanks.

So will someone reopen this bug?

I already explained that the status change was not warranted.

I will create a new bug with the same contents otherwise.

Verified by docs team; I've assigned to myself for handling.

Thanks!

Fixed in mysqldoc rev 69663.

See https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html for the changes, which should appear soon.

Thanks!

Closed.

Thank you, Jon.