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.

已提問 2 年前檢視次數 236 次
1 個回答
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
已回答 1 年前

您尚未登入。 登入 去張貼答案。

一個好的回答可以清楚地回答問題並提供建設性的意見回饋,同時有助於提問者的專業成長。

回答問題指南