Bug #77639 Implement VALUES equivalent to a query specification

• Submitted: 7 Jul 2015 12:40
• Modified: 11 Oct 2019 7:45
• Reporter: Roy Lyseng
• Status: Closed
• Category: MySQL Server: Optimizer
• Severity: S4 (Feature request)
• Version: 5.7
• OS: Any
• CPU Architecture: Any

[7 Jul 2015 12:40] Roy Lyseng

Description:
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:
N/A

[11 Oct 2019 7:45] Roy Lyseng

Implemented as WL#10358 in MySQL 8.0.18.

Exceptions:
- 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.