[jira] [Commented] (DRILL-4374) Drill rewrites Postgres query with ambiguous column references
[
https://issues.apache.org/jira/browse/DRILL-4374?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15210149#comment-15210149
]
Taras Supyk commented on DRILL-4374:
This bug can't be reproduced. Probably it is already fixed.
> 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: Storage - JDBC
>Affects Versions: 1.4.0
>Reporter: Justin Bradford
>Assignee: Taras Supyk
>
> 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)
[jira] [Commented] (DRILL-4374) Drill rewrites Postgres query with ambiguous column references
[
https://issues.apache.org/jira/browse/DRILL-4374?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15158949#comment-15158949
]
Taras Supyk commented on DRILL-4374:
I've tried different jdbc drive and reproduced this bug. So now I have these
versions:
drill 1.4
postgresql 9.3.11
jdbc-driver postgresql-9.4-1205.jdbc41.jar
And this query:
create sequence user_ids;
create sequence group_ids;
create sequence book_ids;
create table users (id integer primary key default nextval('user_ids'), login
char(64), password char(64), group_id integer, book_id integer);
create table groups (id integer primary key default nextval('group_ids'), name
char(64));
create table books (id integer primary key default nextval('book_ids'), name
char(64));
select u.id from psql.drill.users u
join psql.drill.groups g on g.id = u.group_id
join psql.drill.books b on b.id = u.book_id
group by g.id, u.id;
And all this returns me DATA_READ ERROR: The JDBC storage plugin failed while
trying setup the SQL query.
> 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
>Assignee: Taras Supyk
>
> 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)
[jira] [Commented] (DRILL-4374) Drill rewrites Postgres query with ambiguous column references
[
https://issues.apache.org/jira/browse/DRILL-4374?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15157418#comment-15157418
]
Jacques Nadeau commented on DRILL-4374:
---
Please describe which version of postgres, what the table structures were and
what the query is that you tried to run.
> 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
>Assignee: Taras Supyk
>
> 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)
[jira] [Commented] (DRILL-4374) Drill rewrites Postgres query with ambiguous column references
[
https://issues.apache.org/jira/browse/DRILL-4374?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15157360#comment-15157360
]
Taras Supyk commented on DRILL-4374:
I've recreated db structure (simple three tables and join them) and tried to
make similar queries, but I can't make drill fail.
> 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
>Assignee: Taras Supyk
>
> 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)
[jira] [Commented] (DRILL-4374) Drill rewrites Postgres query with ambiguous column references
[
https://issues.apache.org/jira/browse/DRILL-4374?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15157319#comment-15157319
]
Jacques Nadeau commented on DRILL-4374:
---
[~dr-wolf] can you expound on how you tried to reproduce?
> 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
>Assignee: Taras Supyk
>
> 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)
[jira] [Commented] (DRILL-4374) Drill rewrites Postgres query with ambiguous column references
[
https://issues.apache.org/jira/browse/DRILL-4374?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15157308#comment-15157308
]
Taras Supyk commented on DRILL-4374:
I can't reproduce this bug
> 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
>Assignee: Taras Supyk
>
> 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)
[jira] [Commented] (DRILL-4374) Drill rewrites Postgres query with ambiguous column references
[
https://issues.apache.org/jira/browse/DRILL-4374?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15154195#comment-15154195
]
Taras Supyk commented on DRILL-4374:
ok
> 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
>Assignee: Taras Supyk
>
> 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)
[jira] [Commented] (DRILL-4374) Drill rewrites Postgres query with ambiguous column references
[
https://issues.apache.org/jira/browse/DRILL-4374?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15152714#comment-15152714
]
Jacques Nadeau commented on DRILL-4374:
---
Another potential similar example from mailing list:
{code}
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
{code}
> 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
>Assignee: Taras Supyk
>
> 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)
[jira] [Commented] (DRILL-4374) Drill rewrites Postgres query with ambiguous column references
[
https://issues.apache.org/jira/browse/DRILL-4374?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15151749#comment-15151749
]
Jacques Nadeau commented on DRILL-4374:
---
[~dr-wolf], can you take a look at this?
> 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
>Assignee: Taras Supyk
>
> 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)
