[ https://issues.apache.org/jira/browse/DRILL-4374?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Justin Bradford updated DRILL-4374: ----------------------------------- Description: Drill drops table references when rewriting this query, resulting in ambiguous column references. This query: {code:sql} select s.uuid as site_uuid, psc.partner_id, sum(psc.net_revenue_dollars) as revenue from app.public.partner_site_clicks psc join app.public.sites s on psc.site_id = s.id join app.public.partner_click_days pcd on pcd.id = psc.partner_click_day_id where s.generate_revenue_report is true and pcd.`day` = '2016-02-07' group by s.uuid, psc.partner_id; {code} Results in this error: {quote} DATA_READ ERROR: The JDBC storage plugin failed while trying setup the SQL query. {quote} Trying to run this re-written query: {code:sql} SELECT "site_uuid", "partner_id", SUM("net_revenue_dollars") AS "revenue" FROM (SELECT "uuid" AS "site_uuid", "partner_id", "net_revenue_dollars" FROM "public"."partner_site_clicks" INNER JOIN "public"."sites" ON "partner_site_clicks"."site_id" = "sites"."id" INNER JOIN "public"."partner_click_days" ON "partner_site_clicks"."partner_click_day_id" = "partner_click_days"."id" WHERE "sites"."generate_revenue_report" IS TRUE AND "partner_click_days"."day" = '2016-02-07') AS "t0" GROUP BY "site_uuid", "partner_id" {code} That query fails due to an ambiguous "partner_id" reference as two of the tables have that column. was:Drill drops table references when rewriting this query, resulting in ambiguous column references. This query: {code:sql} select s.uuid as site_uuid, psc.partner_id, sum(psc.net_revenue_dollars) as revenue from app.public.partner_site_clicks psc join app.public.sites s on psc.site_id = s.id join app.public.partner_click_days pcd on pcd.id = psc.partner_click_day_id where s.generate_revenue_report is true and pcd.`day` = '2016-02-07' group by s.uuid, psc.partner_id; {code} Results in this error: {quote} DATA_READ ERROR: The JDBC storage plugin failed while trying setup the SQL query. {quote} Trying to run this re-written query: {code:sql} SELECT "site_uuid", "partner_id", SUM("net_revenue_dollars") AS "revenue" FROM (SELECT "uuid" AS "site_uuid", "partner_id", "net_revenue_dollars" FROM "public"."partner_site_clicks" INNER JOIN "public"."sites" ON "partner_site_clicks"."site_id" = "sites"."id" INNER JOIN "public"."partner_click_days" ON "partner_site_clicks"."partner_click_day_id" = "partner_click_days"."id" WHERE "sites"."generate_revenue_report" IS TRUE AND "partner_click_days"."day" = '2016-02-07') AS "t0" GROUP BY "site_uuid", "partner_id" {code} That query fails due to an ambiguous "partner_id" reference as two of the tables have that column. > Drill rewrites Postgres query with ambiguous column references > -------------------------------------------------------------- > > Key: DRILL-4374 > URL: https://issues.apache.org/jira/browse/DRILL-4374 > Project: Apache Drill > Issue Type: Bug > Components: Query Planning & Optimization > Affects Versions: 1.4.0 > Reporter: Justin Bradford > > Drill drops table references when rewriting this query, resulting in > ambiguous column references. > This query: > {code:sql} select s.uuid as site_uuid, psc.partner_id, > sum(psc.net_revenue_dollars) as revenue > from app.public.partner_site_clicks psc > join app.public.sites s on psc.site_id = s.id > join app.public.partner_click_days pcd on pcd.id = psc.partner_click_day_id > where s.generate_revenue_report is true and pcd.`day` = '2016-02-07' > group by s.uuid, psc.partner_id; > {code} > Results in this error: > {quote} > DATA_READ ERROR: The JDBC storage plugin failed while trying setup the SQL > query. > {quote} > Trying to run this re-written query: > {code:sql} > SELECT "site_uuid", "partner_id", SUM("net_revenue_dollars") AS "revenue" > FROM (SELECT "uuid" AS "site_uuid", "partner_id", "net_revenue_dollars" FROM > "public"."partner_site_clicks" INNER JOIN "public"."sites" ON > "partner_site_clicks"."site_id" = "sites"."id" INNER JOIN > "public"."partner_click_days" ON "partner_site_clicks"."partner_click_day_id" > = "partner_click_days"."id" WHERE "sites"."generate_revenue_report" IS TRUE > AND "partner_click_days"."day" = '2016-02-07') AS "t0" GROUP BY "site_uuid", > "partner_id" > {code} > That query fails due to an ambiguous "partner_id" reference as two of the > tables have that column. -- This message was sent by Atlassian JIRA (v6.3.4#6332)