To reproduce

Creating a materialized view use

CREATE MATERIALIZED VIEW "trades_view" 
      as select sum(price),timestamp from trades1  sample by 1d;

and an exception throwed:

invalid column name [name=sum(price), position=0]

The reason for the exception is that no alias was specified for aggregate column when creating the materialized view, and its auto-generated column name contains a forbidden special character, '('.

The PR(#5929) that introduced the validation.

@bluestreak01 Please take a look if this restriction will affect the usability of materialized views.

As an additional note: DuckDB has no restrictions on the behavior of CREATE TABLE AS SELECT statements.

CREATE TABLE t2 as select sum(id), j from t1 group by j;

describe t2;
┌─────────────┬─────────────┬─────────┬─────────┬─────────┬─────────┐
│ column_name │ column_type │  null   │   key   │ default │  extra  │
│   varchar   │   varchar   │ varchar │ varchar │ varchar │ varchar │
├─────────────┼─────────────┼─────────┼─────────┼─────────┼─────────┤
│ sum(id)     │ HUGEINT     │ YES     │ NULL    │ NULL    │ NULL    │
│ j           │ VARCHAR     │ YES     │ NULL    │ NULL    │ NULL    │
└─────────────┴─────────────┴─────────┴─────────┴─────────┴─────────┘

Workaround solution

An alias must be specified for aggregate columns when creating a materialized view:

CREATE MATERIALIZED VIEW "trades_view" 
      as select sum(price) as sum_price,timestamp from trades1  sample by 1d;

QuestDB version:

9.0

OS, in case of Docker specify Docker and the Host OS:

None

File System, in case of Docker specify Host File System:

ext4

Full Name:

Victor

Affiliation:

None

Have you followed Linux, MacOs kernel configuration steps to increase Maximum open files and Maximum virtual memory areas limit?

• Yes, I have

Additional context

No response