MySQL Bugs: #104211: ON DUPLICATE KEY UPDATE and table alias for new VALUES function replacement

Bug #104211	ON DUPLICATE KEY UPDATE and table alias for new VALUES function replacement
Submitted:	5 Jul 2021 20:54	Modified:	7 Jul 2021 13:19
Reporter:	Roman Roman	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Parser	Severity:	S3 (Non-critical)
Version:	8.0.x	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
How preset table alias ?

INSERT `-uni_works` (id, title)
VALUE(-1,'Test') AS v
ON DUPLICATE KEY UPDATE `-uni_works`.title=CONCAT(`-uni_works`.title,v.title)

How to repeat:
CREATE TABLE IF NOT EXISTS `-uni_table` (
            `id` int NOT NULL,
            `title` char(200) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL);

# Old method working:

INSERT `-uni_works` (id, title)
VALUE(-1,'Test')
ON DUPLICATE KEY UPDATE title=CONCAT(title,VALUES(title));

# Not working:

INSERT `-uni_works` (id, title)
VALUE(-1,'Test') AS v
ON DUPLICATE KEY UPDATE title=CONCAT(title,v.title);

# Not working:

INSERT `-uni_works` (id, title) AS t
VALUE(-1,'Test') AS v
ON DUPLICATE KEY UPDATE t.title=CONCAT(t.title,v.title);

# Working:

INSERT `-uni_works` (id, title)
VALUE(-1,'Test') AS v
ON DUPLICATE KEY UPDATE `-uni_works`.title=CONCAT(`-uni_works`.title,v.title)

Hi Mr. Roman,

Thank you for your bug report.

However, your report is not totally clear to us.

Would you be so kind and to quote from latest release of our Reference Manual for 8.0, that syntax that you are trying to use should be working.

Thank you in advance.

I don't understand how to replace "`-uni_works`" in expression with aliase or use it without aliase.
I think use full db_name.table_name every time in ON DUPLICATE KEY UPDATE every time is not very interesting.

Hi Mr. Roman Roman,

You have not replied at all to our last comment.

Next, you are asking us questions on how to change your SQL statements, which is not part of the bug reporting process.

Not a bug.

You can name the columns in the VALUES clause, as in:

INSERT INTO t(id, title) VALUES(-1,'Test') AS v(a,b)
ON DUPLICATE KEY UPDATE title=CONCAT(title,v.b);

This way, it is easy to obtain non-ambiguous column names.

Without explicit names, the columns are called column_0, column_1, etc...

Thanks a lot, Roy ....