I tested this query, it was used on aws redshift. It has two joins.
SELECT cs.post_id,
t.tag_id,
cs.language_code,
cs.likes_count,
cs.comments_count,
cs.clippings_count,
cs.cr_recency_score
FROM redshift.public.card_scores AS cs
JOIN redshift.public.taggings AS t
ON cs.post_id = t.post_id
INNER JOIN redshift.public.min_scale_scores AS mss
ON mss.post_id=cs.post_id
WHERE cs.cr_recency_score IS NOT NULL
AND t.status <> 'unpublished'
Then, error raised.
2016-02-18 05:44:26,862 [293aa5c5-4dcd-3cd8-7b40-4847289d71fa:frag:0:0] INFO
o.a.d.e.store.jdbc.JdbcRecordReader - User Error Occurred
org.apache.drill.common.exceptions.UserException: DATA_READ ERROR: The JDBC
storage plugin failed while trying setup the SQL query.
sql SELECT *
FROM (SELECT *
FROM "public"."card_scores"
INNER JOIN "public"."taggings" ON "card_scores"."post_id" = "taggings"."post_id"
WHERE "card_scores"."cr_recency_score" IS NOT NULL AND "taggings"."status" <>
'unpublished') AS "t"
INNER JOIN "public"."min_scale_scores" ON "t"."post_id" =
"min_scale_scores"."post_id"
plugin redshift
[Error Id: 0ffb54f1-95b9-4a8b-b985-f05e16a2aa6a ]
at
org.apache.drill.common.exceptions.UserException$Builder.build(UserException.java:543)
~[drill-common-1.5.0.jar:1.5.0]
at
org.apache.drill.exec.store.jdbc.JdbcRecordReader.setup(JdbcRecordReader.java:221)
[drill-jdbc-storage-1.5.0.jar:1.5.0]
Caused by: org.postgresql.util.PSQLException: ERROR: column reference "post_id"
is ambiguous
I think this error caused by "SELECT *".
