Using alias in simple view

[andrelec1]

I try to make a view... for the moment i have this request :

drop table if exists computed_datum_hours;
create view computed_datum_hours as
select
    toStartOfHour(b.datetime_value) as datetime_desc,
    b.computed_id,
    b.computed_kind,
    b.computed_type,
    sum(b.value) as value
from computed_datum as b
group by
    b.computed_id,
    b.computed_kind,
    b.computed_type,
    toStartOfHour(b.datetime_value)
;

but clickhouse say :
Code: 47, e.displayText() = DB::Exception: Missing columns: 'b.value' 'b.datetime_value' 'b.computed_type' 'b.computed_kind' 'b.computed_id' while processing query: 'SELECT toStartOfHour(b.datetime_value) AS datetime_desc, b.computed_id, b.computed_kind, b.computed_type, sum(b.value) AS value FROM api_client.computed_datum GROUP BY b.computed_id, b.computed_kind, b.computed_type, toStartOfHour(b.datetime_value)', required columns: 'b.computed_id' 'b.computed_kind' 'b.computed_type' 'b.datetime_value' 'b.value', source columns: 'flags' 'computed_type' 'datetime_value' 'uuid' 'value' 'computed_id' 'insert' 'computed_kind' 'EventDateTime' (version 20.4.2.9 (official build))

this is a bug or alias is different from mariadb ?
( PS, if i remove every alias to b , the view work )

[den-crane]

STR:

create table vt(datetime_value DateTime, value Float64) Engine=Memory;

create view computed_datum_hours as
SELECT
        toStartOfHour(b.datetime_value) AS datetime_desc,
        sum(b.value) AS value
FROM vt AS b
GROUP BY toStartOfHour(b.datetime_value)

Received exception from server (version 20.3.8):
Code: 47. DB::Exception: Received from localhost:9000. DB::Exception: Missing columns: 'b.value'

SELECT
    toStartOfHour(b.datetime_value) AS datetime_desc,
    sum(b.value) AS value
FROM vt AS b
GROUP BY toStartOfHour(b.datetime_value)

Ok.

0 rows in set. Elapsed: 0.003 sec.

[PHaroZ]

The same here with a more simple query

CREATE OR REPLACE view testView as SELECT t.number FROM numbers AS t
        
[2020-09-11 09:15:59] Code: 47, e.displayText() = DB::Exception: Missing columns: 't.number' while processing query: 'SELECT t.number FROM system.numbers', required columns: 't.number', source columns: 'number' (version 20.6.4.44 (official build))

A simple workaround is to prefix table with its database name :

CREATE OR REPLACE view testView as SELECT t.number FROM system.numbers AS t
        
[2020-09-11 09:16:38] completed in 105 ms

My CH version is 20.6.4.44

[filimonov]

Old versions had the same issue.

Received exception from server (version 18.16.1):
Code: 47. DB::Exception: Received from localhost:9000, 127.0.0.1. DB::Exception: Unknown identifier: b.value.  

Received exception from server (version 19.11.14):
Code: 47. DB::Exception: Received from localhost:9000. DB::Exception: Missing columns: 'b.datetime_value' 'b.value' while processing query: 'SELECT toStartOfHour(b.datetime_value) AS datetime_desc, sum(b.value) AS value FROM default.vt GROUP BY toStartOfHour(b.datetime_value)', required columns: 'b.datetime_value' 'b.value', source columns: 'value' 'datetime_value'. 

[andrelec1]

Affect last version too
version 20.9.2.20 (official build)

[2020-10-14 17:12:32] Code: 47, e.displayText() = DB::Exception: Missing columns: 'b.value' 'b.datetime_value' while processing query: 'SELECT toStartOfHour(b.datetime_value) AS datetime_desc, sum(b.value) AS value FROM api_client.vt GROUP BY toStartOfHour(b.datetime_value)', required columns: 'b.datetime_value' 'b.value', source columns: 'value' 'datetime_value' (version 20.9.2.20 (official build))

PS: Think 20.7 ans 20.8 is affected too ...

[sah4ez]

I got the same error, but when I add the database name, a view was created.

[andrelec1]

any news ?

[NpDen4ik]

Still no solution please fix it

[Gachegov59]

please fix it 🙂

[Nicholas-Kant]

please fix it 🙏