MySQL Error 3942: Empty ROW() in a VALUES Table Constructor

MySQL Error 3942 (SQLSTATE HY000) is raised when a row in a table value constructor has no columns. In MySQL 8.0.19 and later, the constructor uses VALUES ROW(...) syntax. Each ROW() must contain at least one value.

For the full standalone syntax, result-column names, ORDER BY, and LIMIT, see the MySQL VALUES statement guide.

This is easy to confuse with the VALUES list in an INSERT statement. MySQL documents these as different forms; the Error Reference also notes an exception when a table value constructor is used as an INSERT source.

Reproduce Error 3942

The standalone VALUES statement returns rows as a table. This statement contains an empty ROW() and triggers Error 3942:

VALUES
  ROW(1, 'Laptop'),
  ROW(),
  ROW(3, 'Keyboard');

The error message is: Each row of a VALUES clause must have at least one column.

The MySQL VALUES statement reference defines a row constructor as ROW(value_list) and states that ROW() cannot be empty. It also says each row constructor in the same statement must have the same number of values.

Fix the empty row

Provide at least one value in every row. If the missing data should be represented as SQL NULL, include NULL as a value:

VALUES
  ROW(1, 'Laptop'),
  ROW(2, NULL),
  ROW(3, 'Keyboard');

ROW(2, NULL) has two columns; ROW() has none. If your application generates the VALUES statement, skip empty records or populate them with the columns required by your query instead of serializing them as ROW().

Do not confuse it with INSERT ... VALUES

A regular multi-row insert uses INSERT INTO followed by value lists in parentheses:

INSERT INTO inventory (product_id, product_name)
VALUES
  (1, 'Laptop'),
  (2, 'Mouse');

For the regular INSERT statement’s column-list and row-value syntax, see the MySQL INSERT guide.

That is different syntax from the standalone table value constructor VALUES ROW(...). If an INSERT statement lists a different number of columns and values, see MySQL Error 1136: Column Count Doesn’t Match Value Count for that separate problem. For valid multi-row inserts, see Insert Multiple Rows in MySQL.

Summary

• Error 3942 is for an empty row in a table value constructor, such as VALUES ROW().
• Put at least one value inside each ROW(...); use NULL when a column value should be null.
• The VALUES table constructor and the VALUES list in INSERT ... VALUES are related but distinct syntax forms.