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