WITH not behaving as expected

[dwayneberry]

I'm trying to use table expressions, eventually using them in joins but seem to be hitting limits. The following code snippets show the issue:

CREATE TABLE test (ts TIMESTAMP NOT NULL)

This is fine:

SELECT (EXTRACT(EPOCH FROM ts) / 60) * 60 start_time FROM test GROUP BY start_time;

As is:

SELECT * FROM (SELECT (EXTRACT(EPOCH FROM ts) / 60) * 60 start_time FROM test GROUP BY start_time);

However, the following fails:

WITH s AS (SELECT (EXTRACT(EPOCH FROM ts) / 60) * 60 start_time FROM test GROUP BY start_time) SELECT * FROM s

Exception: Validate failed: From line 3, column 10 to line 3, column 21: Column 'start_time' not found in any table

Similarly, I get the same error if I try to use the select expression in a join.

I can start to work round it by using:

WITH s AS (SELECT (EXTRACT(EPOCH FROM ts) / 60) * 60 FROM test GROUP BY ((EXTRACT(EPOCH FROM ts / 60) * 60)) SELECT EXPR$0 FROM s;

but is there a simpler/better way?

Thanks,

Julian

[dwayneberry]

Hi,

I can reproduce your issue with WITH not sure what is going on there will need to investigate.

As a work around you can use a view

create view eview as SELECT (EXTRACT(EPOCH FROM ts) / 60) * 60 START_TIME FROM test GROUP BY START_TIME;

and query like

select start_time from eview;

hope this helps

regards

[dwayneberry]

Additional info

this works:

 WITH s AS (SELECT (EXTRACT(EPOCH FROM ts) / 60) * 60 start_time FROM test GROUP BY (EXTRACT(EPOCH FROM ts) / 60) * 60) SELECT * FROM s;

The calcite process is losing something when as alias is used

[dwayneberry]

closed with 9738335