create view query field is not the same with the alias

[marverlous811]

What type of bug is this?

Incorrect result

What subsystems are affected?

Standalone mode

Minimal reproduce step

I created a view with the query, but the view I created does not have the field like the alias I wrote in the query. This is the query I used to create.

CREATE VIEW all_cpu_usage AS (
    SELECT
        date_trunc('minute', ts) AS minute,
        COUNT(DISTINCT CASE WHEN cpu_usage <= 10 THEN mac END) AS "bucket_10",
        COUNT(DISTINCT CASE WHEN cpu_usage > 10 AND cpu_usage <= 20 THEN mac END) AS "bucket_20",
        COUNT(DISTINCT CASE WHEN cpu_usage > 20 AND cpu_usage <= 30 THEN mac END) AS "bucket_30",
        COUNT(DISTINCT CASE WHEN cpu_usage > 30 AND cpu_usage <= 40 THEN mac END) AS "bucket_40",
        COUNT(DISTINCT CASE WHEN cpu_usage > 40 AND cpu_usage <= 50 THEN mac END) AS "bucket_50",
        COUNT(DISTINCT CASE WHEN cpu_usage > 50 AND cpu_usage <= 60 THEN mac END) AS "bucket_60",
        COUNT(DISTINCT CASE WHEN cpu_usage > 60 AND cpu_usage <= 70 THEN mac END) AS "bucket_70",
        COUNT(DISTINCT CASE WHEN cpu_usage > 70 AND cpu_usage <= 80 THEN mac END) AS "bucket_80",
        COUNT(DISTINCT CASE WHEN cpu_usage > 80 AND cpu_usage <= 90 THEN mac END) AS "bucket_90",
        COUNT(DISTINCT CASE WHEN cpu_usage > 90 AND cpu_usage <= 10 THEN mac END) AS "bucket_100"
    FROM device
    GROUP BY minute
    ORDER BY minute DESC
);

What did you expect to see?

I want the result of my view is

minute	bucket_10	bucket_20	bucket_30	bucket_40	bucket_50	bucket_60	bucket_70	bucket_80	bucket_90	bucket_100
1730888820000000000	1	2	3	4	5	6	7	8	9	0

What did you see instead?

This is what I see when I run SELECT * FROM all_cpu_usage LIMIT 10

What operating system did you use?

Ubuntu 20.04 x64

What version of GreptimeDB did you use?

0.9.5

Relevant log output and stack trace

No response

[killme2008]

@marverlous811 Yep, it's a known issue in DataFusion substrate cc @waynexia,the alias in the query logical plan will disappear after serialization by substrate.

Currently, you can change the creating view SQL by specifying an alias name list to avoid this issue:

CREATE OR REPLACE VIEW all_cpu_usage("minute", bucket_10, bucket_20, bucket_30, bucket_40, bucket_50, bucket_60, bucket_70, bucket_80, bucket_90, bucket_100) AS (
    SELECT
        date_trunc('minute', ts) as minute,
        COUNT(DISTINCT CASE WHEN cpu_usage <= 10 THEN mac END) ,
        COUNT(DISTINCT CASE WHEN cpu_usage > 10 AND cpu_usage <= 20 THEN mac END),
        COUNT(DISTINCT CASE WHEN cpu_usage > 20 AND cpu_usage <= 30 THEN mac END),
        COUNT(DISTINCT CASE WHEN cpu_usage > 30 AND cpu_usage <= 40 THEN mac END),
        COUNT(DISTINCT CASE WHEN cpu_usage > 40 AND cpu_usage <= 50 THEN mac END),
        COUNT(DISTINCT CASE WHEN cpu_usage > 50 AND cpu_usage <= 60 THEN mac END),
        COUNT(DISTINCT CASE WHEN cpu_usage > 60 AND cpu_usage <= 70 THEN mac END),
        COUNT(DISTINCT CASE WHEN cpu_usage > 70 AND cpu_usage <= 80 THEN mac END),
        COUNT(DISTINCT CASE WHEN cpu_usage > 80 AND cpu_usage <= 90 THEN mac END),
        COUNT(DISTINCT CASE WHEN cpu_usage > 90 AND cpu_usage <= 10 THEN mac END) 
    FROM device
    GROUP BY minute
    ORDER BY minute DESC
);

[marverlous811]

@killme2008 It's worked for me. Thanks for your suggestion.