Bug #77639 Implement VALUES equivalent to a query specification
Submitted: 7 Jul 2015 12:40 Modified: 11 Oct 2019 7:45
Reporter: Roy Lyseng Email Updates:
Status: Closed Impact on me:
Category:MySQL Server: Optimizer Severity:S4 (Feature request)
Version:5.7 OS:Any
Assigned to: CPU Architecture:Any

[7 Jul 2015 12:40] Roy Lyseng
A VALUES clause is currently used as part of an INSERT ... VALUES statement and as VALUES(expression) in connection with INSERT ... ON DUPLICATE KEY.

However, the SQL standard lets us use VALUES wherever a SELECT query specification may be used, with the common name <simple table>.

For example, this is possible:

  SELECT * FROM t JOIN (VALUES 1,2,3,4,5,6) AS v(x)) ON t.z=v.x;

The VALUES clause in this example represents a table with 6 rows, each with one integer column value, which is then joined to the table named "t".

With this extended definition of VALUES, INSERT ... SELECT and INSERT ... VALUES will actually become the same statement, the syntax being

  INSERT INTO <table name> [ <insert column list> ] <query expression>

When the query expression here is a <simple table> (ie not a UNION), it can be either a SELECT query specification or a VALUES clause.

How to repeat:
[11 Oct 2019 7:45] Roy Lyseng
Implemented as WL#10358 in MySQL 8.0.18.

- Row values must be prefixed with the keyword ROW, due to conflict with VALUES(x) clause.
- Row values must be enclosed in parentheses, even with one column value per row.
[11 Dec 2019 1:09] Jon Stephens
Implemented in MySQL 8.0.19, not 8.0.18.