prestodb/presto · error · SemanticException

MISMATCHED_SET_COLUMN_TYPES

MISMATCHED_SET_COLUMN_TYPES

Error message

Mismatch at column %d

What it means

checkTypesMatchForInsert compares the number and types of the query output columns against the target table columns for an INSERT. If the query returns fewer columns than the target table (the loop index i reached expectedColumns.size() while iterating), it throws MISMATCHED_SET_COLUMN_TYPES with 'Mismatch at column %d' (1-based index i+1).

Source

Thrown at presto-main-base/src/main/java/com/facebook/presto/sql/analyzer/StatementAnalyzer.java:634

            if (expectedColumns.size() != queryColumnTypes.size()) {
                errorMessage = format("Insert query has %d expression(s) but expected %d target column(s). ",
                        queryColumnTypes.size(), expectedColumns.size());
            }

            for (int i = 0; i < Math.max(expectedColumns.size(), queryColumnTypes.size()); i++) {
                Node node = insert;
                QueryBody queryBody = insert.getQuery().getQueryBody();
                if (queryBody instanceof Values) {
                    List<Expression> rows = ((Values) queryBody).getRows();
                    checkState(!rows.isEmpty(), "Missing column values");
                    node = rows.get(0);
                    if (node instanceof Row) {
                        int columnIndex = Math.min(i, queryColumnTypes.size() - 1);
                        node = ((Row) rows.get(0)).getFields().get(columnIndex).getExpression();
                    }
                }
                if (i == expectedColumns.size()) {
                    throw new SemanticException(MISMATCHED_SET_COLUMN_TYPES,
                            node,
                            errorMessage + "Mismatch at column %d",
                            i + 1);
                }
                if (i == queryColumnTypes.size()) {
                    throw new SemanticException(MISMATCHED_SET_COLUMN_TYPES,
                            node,
                            errorMessage + "Mismatch at column %d: '%s'",
                            i + 1,
                            expectedColumns.get(i).getName());
                }
                if (!functionAndTypeResolver.canCoerce(
                        queryColumnTypes.get(i),
                        expectedColumns.get(i).getType())) {
                    if (queryColumnTypes.get(i) instanceof RowType && expectedColumns.get(i).getType() instanceof RowType) {
                        String fieldName = expectedColumns.get(i).getName();
                        List<Type> columnRowTypes = queryColumnTypes.get(i).getTypeParameters();
                        List<RowType.Field> expectedRowFields = ((RowType) expectedColumns.get(i).getType()).getFields();

View on GitHub (pinned to 55bb57d202)

Solutions

1. Make the SELECT/VALUES produce exactly as many expressions as there are target columns (or entries in the explicit column list).
2. Add explicit DEFAULT literals or NULLs for missing columns in the SELECT/VALUES rows.
3. Use an explicit column list covering only the columns you actually supply, ensuring its size equals the query's column count.
4. Check the target table schema with DESCRIBE after recent DDL changes and update the statement.

Example fix

// before
INSERT INTO orders (orderkey, custkey, orderdate) VALUES (1, 2);
// after
INSERT INTO orders (orderkey, custkey, orderdate) VALUES (1, 2, DATE '2026-01-01');

-- Compare counts before inserting:
SELECT count(*) FROM (SELECT ... ) q; -- must equal target column count
-- Or check explicitly:
SELECT count(*) FROM information_schema.columns WHERE table_schema='s' AND table_name='target_table';

When it happens

Trigger: INSERT INTO t (col list or implicit full list) SELECT/VALUES providing fewer expressions than the number of target columns — e.g. INSERT INTO t (a,b,c) VALUES (1,2) or SELECT of two columns into a three-column table.

Common situations: Target table gained a column (schema evolution) so bare INSERT ... SELECT no longer lines up; writing VALUES rows with too few entries; SELECT list shortened after edits; forgetting that an explicit column list must still be matched by an equal count of query columns.

Related errors

• Expected column to be a time type but is
• INVALID_FUNCTION_ARGUMENT
• TYPE_MISMATCH
• MISSING_COLUMN
• DUPLICATE_COLUMN_NAME

AI-assisted analysis of prestodb/presto@55bb57d202 (2026-09-04). Data as JSON: /api/errors/f4e595e0606e4bb8. Report an issue: GitHub.