drill-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Justin Bradford (JIRA)" <j...@apache.org>
Subject [jira] [Updated] (DRILL-4374) Drill rewrites Postgres query with ambiguous column references
Date Tue, 09 Feb 2016 00:15:18 GMT

     [ 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)

Mime
View raw message