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 2 years ago225 views
1 Answer
0

An asterisk qualified by a table name (from your example, spine_table.*) will be expanded to every column of that table that is not listed in the USING clause. But since spine_table does not have any other column you are getting that error.

You will need to find a way to switch from using (user_id) to on spine_table.user_id = feature_table.user_id

The error message language is misleading and should have been saying something better. I request you to use the Feedback link on bottom left of Athena page and submit the feedback Type as Report an issue.

profile pictureAWS
answered a year ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions