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.

posta 2 anni fa236 visualizzazioni
1 Risposta
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
con risposta un anno fa

Accesso non effettuato. Accedi per postare una risposta.

Una buona risposta soddisfa chiaramente la domanda, fornisce un feedback costruttivo e incoraggia la crescita professionale del richiedente.

Linee guida per rispondere alle domande