apache/cassandra · error · InvalidRequestException

Can only select columns by name when defining a…

Error message

Can only select columns by name when defining a materialized view (got %s)

What it means

Each selected column in a materialized view must be a plain column identifier. Any computed selector (functions, casts, literals, writetime, ttl, etc.) is rejected because views can only mirror base columns.

Solutions

Project only bare base-table column names in the view SELECT
Compute expressions in the application when reading from the view, or store precomputed values in a denormalized base table
For aggregation, use a separate analytics path (e.g. Spark) instead of MVs

Example fix

// before
CREATE MATERIALIZED VIEW mv AS SELECT bucket, count(*) FROM base WHERE bucket IS NOT NULL ...;
// after
CREATE MATERIALIZED VIEW mv AS SELECT bucket, col1, col2 FROM base WHERE bucket IS NOT NULL AND col1 IS NOT NULL ...;

Defensive patterns

Strategy: validation

Validate before calling

for (String col : selectColumns) if (!baseTableColumns.contains(col)) throw new IllegalArgumentException("MV selector must be a plain column: " + col);

Try / catch

try { session.execute(createMvStmt); } catch (InvalidQueryException e) { if (e.getMessage().contains("Can only select columns by name")) { /* rewrite selectors as plain columns */ } }

Prevention

Never project functions, casts, or expressions in MV definitions
Treat MVs as row mirrors of the base table only; compute values elsewhere

When it happens

Trigger: CREATE MATERIALIZED VIEW ... AS SELECT count(*), system.currentTimeUUID(), col+1, ... FROM base ...;

Common situations: Trying to build an 'aggregate view' (aggregations are not supported in MVs); generated SELECT lists containing expressions; porting SQL views with computed columns.

Understand the failure class

Background: "Must be a positive integer", "Invalid value", "Unsupported": the invalid-argument-value error family, when a library rejects the value you pass — this error's family across 35 libraries.

Related errors

AI-assisted analysis of apache/cassandra@88fd0f6a0e (2026-09-10). Data as JSON: /api/errors/c2913fddd38aeb0e. Report an issue: GitHub.

Appendix: source

Thrown at src/java/org/apache/cassandra/cql3/statements/schema/CreateViewStatement.java:218

                      "'%s' as it is being dropped.",
                      viewName, tableName);

        /*
         * Process SELECT clause
         */

        Set<ColumnIdentifier> selectedColumns = new HashSet<>();

        if (rawColumns.isEmpty()) // SELECT *
            table.columns().forEach(c -> selectedColumns.add(c.name));

        rawColumns.forEach(selector ->
        {
            if (null != selector.alias)
                throw ire("Cannot use aliases when defining a materialized view (got %s)", selector);

            if (!(selector.selectable instanceof Selectable.RawIdentifier))
                throw ire("Can only select columns by name when defining a materialized view (got %s)", selector.selectable);

            // will throw IRE if the column doesn't exist in the base table
            Selectable.RawIdentifier rawIdentifier = (Selectable.RawIdentifier) selector.selectable;
            ColumnMetadata column = rawIdentifier.columnMetadata(table);

            selectedColumns.add(column.name);
        });

        selectedColumns.stream()
                       .map(table::getColumn)
                       .filter(ColumnMetadata::isStatic)
                       .findAny()
                       .ifPresent(c -> { throw ire("Cannot include static column '%s' in materialized view '%s'", c, viewName); });

        /*
         * Process PRIMARY KEY columns and CLUSTERING ORDER BY clause
         */

View on GitHub (pinned to 88fd0f6a0e)