By using AWS re:Post, you agree to the Terms of Use

Simple Join query errors out with "table not found"

0

I'm trying to join a simple "spine" table with a "feature" table. The spine table may or may not have additional columns besides the join key. If there are additional columns (in this case, random_column), the following query works fine (please note that these queries are self-sufficient. they don't depend on any external tables - all tables are inline created by the query itself):

with spine_table as
(select 'abc' as user_id, 'random_value' as random_column),
feature_table as
(select 'abc' as user_id, '123' as feature_column)
select user_id, spine_table.*, feature_column
    from spine_table
    join feature_table
    using (user_id)

If, however, there's no additional column, the query throws an exception:

with spine_table as
(select 'abc' as user_id),
feature_table as
(select 'abc' as user_id, '123' as feature_column)
select user_id, spine_table.*, feature_column
    from spine_table
    join feature_table
    using (user_id)

Error: SYNTAX_ERROR: line 5:17: Table 'spine_table' not found The second query works fine if I omit spine_table. : with spine_table as

(select 'abc' as user_id),
feature_table as
(select 'abc' as user_id, '123' as feature_column)
select user_id, feature_column
    from spine_table
    join feature_table
    using (user_id)

The problem is that my application dynamically generates the query, and it doesn't know ahead of time whether there are additional columns in the spine_table besides the join keys.

asked 4 months ago40 views