Using table expressions in more complex queries


#1

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


#2

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


#3

Issue raised here: https://github.com/mapd/mapd-core/issues/42


#4

Hi

You can do

 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;

Its seems the calcite process is losing its way when an alias is used in the group_by

regards


#5

Hi,

A fix for this has been applied to mapd-core. If you are building from source you can use this now.

It will be in the next release of MapD.

regards