prestodb/presto · error · SemanticException

COLUMN_TYPE_UNKNOWN

COLUMN_TYPE_UNKNOWN

Error message

Column type is unknown at position %s

What it means

CTAS with explicit column aliases requires every output column of the query to have a known (concrete) type. Presto's UNKNOWN type (e.g. from a NULL literal with no cast) cannot be used to create a table column, so visitCreateTableAsSelect throws COLUMN_TYPE_UNKNOWN at the 1-based position of the offending field.

Source

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

            node.getColumnAliases().ifPresent(analysis::setCreateTableColumnAliases);
            analysis.setCreateTableComment(node.getComment());

            analysis.addAccessControlCheckForTable(TABLE_CREATE, new AccessControlInfoForTable(accessControl, session.getIdentity(), session.getTransactionId(), session.getAccessControlContext(), targetTable));

            analysis.setCreateTableAsSelectWithData(node.isWithData());

            // analyze the query that creates the table
            Scope queryScope = process(node.getQuery(), scope);

            ImmutableList.Builder<OutputColumnMetadata> outputColumns = ImmutableList.builder();

            if (node.getColumnAliases().isPresent()) {
                validateColumnAliases(node.getColumnAliases().get(), queryScope.getRelationType().getVisibleFieldCount());
                int aliasPosition = 0;
                // analyze only column types in subquery if column alias exists
                for (Field field : queryScope.getRelationType().getVisibleFields()) {
                    if (field.getType().equals(UNKNOWN)) {
                        throw new SemanticException(COLUMN_TYPE_UNKNOWN, node, "Column type is unknown at position %s", queryScope.getRelationType().indexOf(field) + 1);
                    }
                    String columnName = node.getColumnAliases().get().get(aliasPosition).getValue();
                    outputColumns.add(OutputColumnMetadata.fromColumnLineage(columnName, field.getType().toString(), analysis.getColumnLineageForField(field)));
                    aliasPosition++;
                }
            }
            else {
                validateColumns(node, queryScope.getRelationType());
                queryScope.getRelationType().getVisibleFields().stream()
                        .map(this::createOutputColumn)
                        .forEach(outputColumns::add);
            }
            analysis.setUpdatedSourceColumns(Optional.of(outputColumns.build()));
            return createAndAssignScope(node, scope, Field.newUnqualified(node.getLocation(), "rows", BIGINT));
        }

        private OutputColumnMetadata createOutputColumn(Field field)
        {

View on GitHub (pinned to 55bb57d202)

Solutions

1. CAST the NULL to an explicit type, e.g. CAST(NULL AS VARCHAR)
2. Replace the bare NULL with a typed expression or a typed placeholder
3. Set the column alias's type via the query expression rather than relying on inference

Example fix

-- before
CREATE TABLE t (id, note) AS SELECT 1, NULL;
-- after
CREATE TABLE t (id, note) AS SELECT 1, CAST(NULL AS VARCHAR);

// before CTAS with aliases, ensure no untyped NULL columns:
// if any expression is a bare NULL, wrap it: CAST(NULL AS <type>)

boolean hasKnownType(Type t) { return t != UNKNOWN; }

When it happens

Trigger: CREATE TABLE ... AS SELECT with column aliases where one output column is a bare NULL (or otherwise unknown-typed expression), e.g. SELECT CAST(NULL AS VARCHAR) is fine but SELECT NULL is not.

Common situations: Hand-written queries with untyped NULL placeholder columns; generated SQL from ORMs or BI tools emitting NULL for missing values; migrating queries that relied on another engine's type inference of NULL.

Related errors

• TYPE_MISMATCH
• INVALID_FUNCTION_ARGUMENT
• INVALID_FUNCTION_ARGUMENT
• INVALID_FUNCTION_ARGUMENT
• TABLE_ALREADY_EXISTS

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