[DRILL-4374] Drill rewrites Postgres query with ambiguous column references

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Cannot Reproduce
• Affects Version/s: 1.4.0
• Fix Version/s: 1.16.0
• Component/s: Storage - JDBC
• Assignee: Taras Supyk
• Reporter: Justin Bradford
• Created: 09/Feb/16 00:12
• Updated: 03/Mar/19 12:14
• Resolved: 03/Mar/19 12:14

Description

Drill drops table references when rewriting this query, resulting in ambiguous column references.

This query:

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; 

Results in this error:

DATA_READ ERROR: The JDBC storage plugin failed while trying setup the SQL query.

Trying to run this re-written query:

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" 

That query fails due to an ambiguous "partner_id" reference as two of the tables have that column.

Issue Links

• is related to: DRILL-4211 Column aliases not pushed down to JDBC stores in some cases when Drill expects aliased columns to be returned.