[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-4401) multi-table join projection returning character instead of integer type
[ https://issues.apache.org/jira/browse/DRILL-4401?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15204174#comment-15204174 ] Taras Supyk commented on DRILL-4401: This bug can't be reproduced. Looks like it is fixed already. > multi-table join projection returning character instead of integer type > --- > > Key: DRILL-4401 > URL: https://issues.apache.org/jira/browse/DRILL-4401 > Project: Apache Drill > Issue Type: Bug > Components: Storage - JDBC >Affects Versions: 1.5.0 >Reporter: N Campbell >Assignee: Taras Supyk > > column 8 should be an integer type but is described by drill as character. > select tj.tj1rnum, tj.c1 tj1c1, tj.c2 tj1c2, tj2rnum, tj.tj2c1, tj.tj2c2, > tjoin3.rnum tj3rnum, tjoin3.c1 tj3c1, tjoin3.c2 tj3c2 from (select > tjoin1.rnum tj1rnum, tjoin1.c1, tjoin2.c2, tjoin2.rnum tj2rnum,tjoin2.c1 > tj2c1, tjoin2.c2 tj2c2 from postgres.public.tjoin1 left outer join > postgres.public.tjoin2 on tjoin1.c1=tjoin2.c1) tj left outer join > postgres.public.tjoin3 on tj.c1=tjoin3.c1 > create table TJOIN1 (RNUM integer not null , C1 integer, C2 integer); > insert into TJOIN1 (RNUM, C1, C2) values ( 0, 10, 15); > insert into TJOIN1 (RNUM, C1, C2) values ( 1, 20, 25); > insert into TJOIN1 (RNUM, C1, C2) values ( 2, NULL, 50); > create table TJOIN2 (RNUM integer not null , C1 integer, C2 char(2)); > insert into TJOIN2 (RNUM, C1, C2) values ( 0, 10, 'BB'); > insert into TJOIN2 (RNUM, C1, C2) values ( 1, 15, 'DD'); > insert into TJOIN2 (RNUM, C1, C2) values ( 2, NULL, 'EE'); > insert into TJOIN2 (RNUM, C1, C2) values ( 3, 10, 'FF'); > create table TJOIN3 (RNUM integer not null , C1 integer, C2 char(2)); > insert into TJOIN3 (RNUM, C1, C2) values ( 0, 10, 'XX'); > insert into TJOIN3 (RNUM, C1, C2) values ( 1, 15, 'YY'); > create table TJOIN4 (RNUM integer not null , C1 integer, C2 char(2)); -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (DRILL-4402) pushing unsupported full outer join to Postgres
[ https://issues.apache.org/jira/browse/DRILL-4402?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15199542#comment-15199542 ] Taras Supyk commented on DRILL-4402: Looks like this bug is already fixed in new version of calcite. > pushing unsupported full outer join to Postgres > --- > > Key: DRILL-4402 > URL: https://issues.apache.org/jira/browse/DRILL-4402 > Project: Apache Drill > Issue Type: Bug > Components: Storage - JDBC >Affects Versions: 1.5.0 >Reporter: N Campbell >Assignee: Taras Supyk > > Error: DATA_READ ERROR: The JDBC storage plugin failed while trying setup the > SQL query. > sql SELECT * > FROM "public"."tjoin1" > FULL JOIN "public"."tjoin2" ON "tjoin1"."c1" < "tjoin2"."c1" > plugin postgres > Fragment 0:0 > [Error Id: bc54cf76-f4ff-474c-b3df-fa357bdf0ff8 on centos1:31010] > (org.postgresql.util.PSQLException) ERROR: FULL JOIN is only supported with > merge-joinable or hash-joinable join conditions > org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse():2182 > org.postgresql.core.v3.QueryExecutorImpl.processResults():1911 > org.postgresql.core.v3.QueryExecutorImpl.execute():173 > org.postgresql.jdbc.PgStatement.execute():622 > org.postgresql.jdbc.PgStatement.executeWithFlags():458 > org.postgresql.jdbc.PgStatement.executeQuery():374 > org.apache.commons.dbcp.DelegatingStatement.executeQuery():208 > org.apache.commons.dbcp.DelegatingStatement.executeQuery():208 > org.apache.drill.exec.store.jdbc.JdbcRecordReader.setup():177 > org.apache.drill.exec.physical.impl.ScanBatch.():108 > org.apache.drill.exec.physical.impl.ScanBatch.():136 > org.apache.drill.exec.store.jdbc.JdbcBatchCreator.getBatch():40 > org.apache.drill.exec.store.jdbc.JdbcBatchCreator.getBatch():33 > org.apache.drill.exec.physical.impl.ImplCreator.getRecordBatch():147 > org.apache.drill.exec.physical.impl.ImplCreator.getChildren():170 > org.apache.drill.exec.physical.impl.ImplCreator.getRecordBatch():127 > org.apache.drill.exec.physical.impl.ImplCreator.getChildren():170 > org.apache.drill.exec.physical.impl.ImplCreator.getRecordBatch():127 > org.apache.drill.exec.physical.impl.ImplCreator.getChildren():170 > org.apache.drill.exec.physical.impl.ImplCreator.getRootExec():101 > org.apache.drill.exec.physical.impl.ImplCreator.getExec():79 > org.apache.drill.exec.work.fragment.FragmentExecutor.run():230 > org.apache.drill.common.SelfCleaningRunnable.run():38 > java.util.concurrent.ThreadPoolExecutor.runWorker():1142 > java.util.concurrent.ThreadPoolExecutor$Worker.run():617 > java.lang.Thread.run():745 > SQLState: null > ErrorCode: 0 > create table TJOIN1 (RNUM integer not null , C1 integer, C2 integer); > insert into TJOIN1 (RNUM, C1, C2) values ( 0, 10, 15); > insert into TJOIN1 (RNUM, C1, C2) values ( 1, 20, 25); > insert into TJOIN1 (RNUM, C1, C2) values ( 2, NULL, 50); > create table TJOIN2 (RNUM integer not null , C1 integer, C2 char(2)); > insert into TJOIN2 (RNUM, C1, C2) values ( 0, 10, 'BB'); > insert into TJOIN2 (RNUM, C1, C2) values ( 1, 15, 'DD'); > insert into TJOIN2 (RNUM, C1, C2) values ( 2, NULL, 'EE'); > insert into TJOIN2 (RNUM, C1, C2) values ( 3, 10, 'FF'); -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (DRILL-4409) projecting literal will result in an empty resultset
[ https://issues.apache.org/jira/browse/DRILL-4409?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15175585#comment-15175585 ] Taras Supyk commented on DRILL-4409: Can't reproduce in oracle > projecting literal will result in an empty resultset > > > Key: DRILL-4409 > URL: https://issues.apache.org/jira/browse/DRILL-4409 > Project: Apache Drill > Issue Type: Bug > Components: Server >Affects Versions: 1.5.0 >Reporter: N Campbell >Assignee: Taras Supyk > > A query which projects a literal as shown against a Postgres table will > result in an empty result set being returned. > select 'BB' from postgres.public.tversion -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (DRILL-4398) SYSTEM ERROR: IllegalStateException: Memory was leaked by query
[ https://issues.apache.org/jira/browse/DRILL-4398?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15175581#comment-15175581 ] Taras Supyk commented on DRILL-4398: Reproduced with oracle > SYSTEM ERROR: IllegalStateException: Memory was leaked by query > --- > > Key: DRILL-4398 > URL: https://issues.apache.org/jira/browse/DRILL-4398 > Project: Apache Drill > Issue Type: Bug > Components: Server >Affects Versions: 1.5.0 >Reporter: N Campbell >Assignee: Taras Supyk > > Several queries fail with memory leaked errors > select tjoin2.rnum, tjoin1.c1, tjoin2.c1 as c1j2, tjoin2.c2 as c2j2 from > postgres.public.tjoin1 full outer join postgres.public.tjoin2 on tjoin1.c1 = > tjoin2.c1 > select tjoin1.rnum, tjoin1.c1, tjoin2.c1 as c1j2, tjoin2.c2 from > postgres.public.tjoin1, lateral ( select tjoin2.c1, tjoin2.c2 from > postgres.public.tjoin2 where tjoin1.c1=tjoin2.c1) tjoin2 > SYSTEM ERROR: IllegalStateException: Memory was leaked by query. Memory > leaked: (40960) > Allocator(op:0:0:3:JdbcSubScan) 100/40960/135168/100 > (res/actual/peak/limit) > create table TJOIN1 (RNUM integer not null , C1 integer, C2 integer); > insert into TJOIN1 (RNUM, C1, C2) values ( 0, 10, 15); > insert into TJOIN1 (RNUM, C1, C2) values ( 1, 20, 25); > insert into TJOIN1 (RNUM, C1, C2) values ( 2, NULL, 50); > create table TJOIN2 (RNUM integer not null , C1 integer, C2 char(2)); > insert into TJOIN2 (RNUM, C1, C2) values ( 0, 10, 'BB'); > insert into TJOIN2 (RNUM, C1, C2) values ( 1, 15, 'DD'); > insert into TJOIN2 (RNUM, C1, C2) values ( 2, NULL, 'EE'); > insert into TJOIN2 (RNUM, C1, C2) values ( 3, 10, 'FF'); -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (DRILL-4396) Generates invalid cast specification in re-written query to Postgres
[ https://issues.apache.org/jira/browse/DRILL-4396?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15175575#comment-15175575 ] Taras Supyk commented on DRILL-4396: Reproduced in oracle > Generates invalid cast specification in re-written query to Postgres > > > Key: DRILL-4396 > URL: https://issues.apache.org/jira/browse/DRILL-4396 > Project: Apache Drill > Issue Type: Bug > Components: Server >Affects Versions: 1.5.0 >Reporter: N Campbell >Assignee: Taras Supyk > > select vint.rnum, tflt.rnum from postgres.public.vint , postgres.public.tflt > where vint.cint = tflt.cflt > Error: DATA_READ ERROR: The JDBC storage plugin failed while trying setup the > SQL query. > sql SELECT * > FROM (SELECT "rnum", CAST("cint" AS DOUBLE) AS "$f2" > FROM "public"."vint") AS "t" > INNER JOIN "public"."tflt" ON "t"."$f2" = "tflt"."cflt" > plugin postgres > Fragment 0:0 > [Error Id: 9985ca6b-1faf-43e0-9465-b7a6e8876c6d on centos1:31010] > (org.postgresql.util.PSQLException) ERROR: type "double" does not exist > Position: 46 > org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse():2182 > org.postgresql.core.v3.QueryExecutorImpl.processResults():1911 > org.postgresql.core.v3.QueryExecutorImpl.execute():173 > org.postgresql.jdbc.PgStatement.execute():622 > org.postgresql.jdbc.PgStatement.executeWithFlags():458 > org.postgresql.jdbc.PgStatement.executeQuery():374 > org.apache.commons.dbcp.DelegatingStatement.executeQuery():208 > org.apache.commons.dbcp.DelegatingStatement.executeQuery():208 > org.apache.drill.exec.store.jdbc.JdbcRecordReader.setup():177 > org.apache.drill.exec.physical.impl.ScanBatch.():108 > org.apache.drill.exec.physical.impl.ScanBatch.():136 > org.apache.drill.exec.store.jdbc.JdbcBatchCreator.getBatch():40 > org.apache.drill.exec.store.jdbc.JdbcBatchCreator.getBatch():33 > org.apache.drill.exec.physical.impl.ImplCreator.getRecordBatch():147 > org.apache.drill.exec.physical.impl.ImplCreator.getChildren():170 > org.apache.drill.exec.physical.impl.ImplCreator.getRecordBatch():127 > org.apache.drill.exec.physical.impl.ImplCreator.getChildren():170 > org.apache.drill.exec.physical.impl.ImplCreator.getRecordBatch():127 > org.apache.drill.exec.physical.impl.ImplCreator.getChildren():170 > org.apache.drill.exec.physical.impl.ImplCreator.getRootExec():101 > org.apache.drill.exec.physical.impl.ImplCreator.getExec():79 > org.apache.drill.exec.work.fragment.FragmentExecutor.run():230 > org.apache.drill.common.SelfCleaningRunnable.run():38 > java.util.concurrent.ThreadPoolExecutor.runWorker():1142 > java.util.concurrent.ThreadPoolExecutor$Worker.run():617 > java.lang.Thread.run():745 > SQLState: null > ErrorCode: 0 > create table TINT ( RNUM integer not null , CINT integer ) ; > create view VINT as select * from TINT; > create table TFLT ( RNUM integer not null , CFLT float ) ; > create view VFLT as select * from TFLT; -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (DRILL-4395) equi-inner join of two tables in Postgres returns null one of the projected columns
[ https://issues.apache.org/jira/browse/DRILL-4395?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15175574#comment-15175574 ] Taras Supyk commented on DRILL-4395: Reproduced with oracle > equi-inner join of two tables in Postgres returns null one of the projected > columns > --- > > Key: DRILL-4395 > URL: https://issues.apache.org/jira/browse/DRILL-4395 > Project: Apache Drill > Issue Type: Bug > Components: Server >Affects Versions: 1.5.0 >Reporter: N Campbell >Assignee: Taras Supyk > > This query should return 1,2,3,4 in both columns but returns null in the > second column. Both tables are in a Postgres 9.5 server mapped under Drill > select tint.rnum, tbint.rnum from postgres.public.tint , > postgres.public.tbint where tint.cint = tbint.cbint > create table TINT ( RNUM integer not null , CINT integer ) ; > insert into TINT(RNUM, CINT) values ( 0, NULL); > insert into TINT(RNUM, CINT) values ( 1, -1); > insert into TINT(RNUM, CINT) values ( 2, 0); > insert into TINT(RNUM, CINT) values ( 3, 1); > insert into TINT(RNUM, CINT) values ( 4, 10); > create table TBINT ( RNUM integer not null , CBINT bigint ) ; > insert into TBINT(RNUM, CBINT) values ( 0, NULL); > insert into TBINT(RNUM, CBINT) values ( 1, -1); > insert into TBINT(RNUM, CBINT) values ( 2, 0); > insert into TBINT(RNUM, CBINT) values ( 3, 1); > insert into TBINT(RNUM, CBINT) values ( 4, 10); -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (DRILL-4402) pushing unsupported full outer join to Postgres
[ https://issues.apache.org/jira/browse/DRILL-4402?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15175553#comment-15175553 ] Taras Supyk commented on DRILL-4402: In oracle this query causes SYSTEM ERROR: IllegalStateException: Memory was leaked by query. > pushing unsupported full outer join to Postgres > --- > > Key: DRILL-4402 > URL: https://issues.apache.org/jira/browse/DRILL-4402 > Project: Apache Drill > Issue Type: Bug > Components: Server >Affects Versions: 1.5.0 >Reporter: N Campbell >Assignee: Taras Supyk > > Error: DATA_READ ERROR: The JDBC storage plugin failed while trying setup the > SQL query. > sql SELECT * > FROM "public"."tjoin1" > FULL JOIN "public"."tjoin2" ON "tjoin1"."c1" < "tjoin2"."c1" > plugin postgres > Fragment 0:0 > [Error Id: bc54cf76-f4ff-474c-b3df-fa357bdf0ff8 on centos1:31010] > (org.postgresql.util.PSQLException) ERROR: FULL JOIN is only supported with > merge-joinable or hash-joinable join conditions > org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse():2182 > org.postgresql.core.v3.QueryExecutorImpl.processResults():1911 > org.postgresql.core.v3.QueryExecutorImpl.execute():173 > org.postgresql.jdbc.PgStatement.execute():622 > org.postgresql.jdbc.PgStatement.executeWithFlags():458 > org.postgresql.jdbc.PgStatement.executeQuery():374 > org.apache.commons.dbcp.DelegatingStatement.executeQuery():208 > org.apache.commons.dbcp.DelegatingStatement.executeQuery():208 > org.apache.drill.exec.store.jdbc.JdbcRecordReader.setup():177 > org.apache.drill.exec.physical.impl.ScanBatch.():108 > org.apache.drill.exec.physical.impl.ScanBatch.():136 > org.apache.drill.exec.store.jdbc.JdbcBatchCreator.getBatch():40 > org.apache.drill.exec.store.jdbc.JdbcBatchCreator.getBatch():33 > org.apache.drill.exec.physical.impl.ImplCreator.getRecordBatch():147 > org.apache.drill.exec.physical.impl.ImplCreator.getChildren():170 > org.apache.drill.exec.physical.impl.ImplCreator.getRecordBatch():127 > org.apache.drill.exec.physical.impl.ImplCreator.getChildren():170 > org.apache.drill.exec.physical.impl.ImplCreator.getRecordBatch():127 > org.apache.drill.exec.physical.impl.ImplCreator.getChildren():170 > org.apache.drill.exec.physical.impl.ImplCreator.getRootExec():101 > org.apache.drill.exec.physical.impl.ImplCreator.getExec():79 > org.apache.drill.exec.work.fragment.FragmentExecutor.run():230 > org.apache.drill.common.SelfCleaningRunnable.run():38 > java.util.concurrent.ThreadPoolExecutor.runWorker():1142 > java.util.concurrent.ThreadPoolExecutor$Worker.run():617 > java.lang.Thread.run():745 > SQLState: null > ErrorCode: 0 > create table TJOIN1 (RNUM integer not null , C1 integer, C2 integer); > insert into TJOIN1 (RNUM, C1, C2) values ( 0, 10, 15); > insert into TJOIN1 (RNUM, C1, C2) values ( 1, 20, 25); > insert into TJOIN1 (RNUM, C1, C2) values ( 2, NULL, 50); > create table TJOIN2 (RNUM integer not null , C1 integer, C2 char(2)); > insert into TJOIN2 (RNUM, C1, C2) values ( 0, 10, 'BB'); > insert into TJOIN2 (RNUM, C1, C2) values ( 1, 15, 'DD'); > insert into TJOIN2 (RNUM, C1, C2) values ( 2, NULL, 'EE'); > insert into TJOIN2 (RNUM, C1, C2) values ( 3, 10, 'FF'); -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (DRILL-4403) AssertionError: Internal error: Conversion to relational algebra failed to preserve datatypes
[ https://issues.apache.org/jira/browse/DRILL-4403?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15175546#comment-15175546 ] Taras Supyk commented on DRILL-4403: Reproduced with oracle > AssertionError: Internal error: Conversion to relational algebra failed to > preserve datatypes > -- > > Key: DRILL-4403 > URL: https://issues.apache.org/jira/browse/DRILL-4403 > Project: Apache Drill > Issue Type: Bug > Components: Server >Affects Versions: 1.5.0 >Reporter: N Campbell >Assignee: Taras Supyk > > select rnum, c1, c2, c3, stddev_pop( c3 ) over(partition by c1) from > postgres.public.tolap > Error: SYSTEM ERROR: AssertionError: Internal error: Conversion to relational > algebra failed to preserve datatypes: > validated type: > RecordType(INTEGER NOT NULL rnum, CHAR(3) CHARACTER SET "ISO-8859-1" COLLATE > "ISO-8859-1$en_US$primary" c1, CHAR(2) CHARACTER SET "ISO-8859-1" COLLATE > "ISO-8859-1$en_US$primary" c2, INTEGER c3, INTEGER EXPR$4) NOT NULL > converted type: > RecordType(INTEGER NOT NULL rnum, CHAR(3) CHARACTER SET "ISO-8859-1" COLLATE > "ISO-8859-1$en_US$primary" c1, CHAR(2) CHARACTER SET "ISO-8859-1" COLLATE > "ISO-8859-1$en_US$primary" c2, INTEGER c3, DOUBLE EXPR$4) NOT NULL > rel: > LogicalProject(rnum=[$0], c1=[$1], c2=[$2], c3=[$3], > EXPR$4=[POWER(/(CastHigh(-(SUM(*(CastHigh($3), CastHigh($3))) OVER (PARTITION > BY $1 RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING), > /(*(SUM(CastHigh($3)) OVER (PARTITION BY $1 RANGE BETWEEN UNBOUNDED PRECEDING > AND UNBOUNDED FOLLOWING), SUM(CastHigh($3)) OVER (PARTITION BY $1 RANGE > BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)), COUNT(CastHigh($3)) > OVER (PARTITION BY $1 RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED > FOLLOWING, COUNT(CastHigh($3)) OVER (PARTITION BY $1 RANGE BETWEEN > UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)), 0.5)]) > LogicalTableScan(table=[[postgres, public, tolap]]) > [Error Id: 61be4aa1-6486-4118-a82b-86c22b551bb5 on centos1:31010] > (org.apache.drill.exec.work.foreman.ForemanException) Unexpected exception > during fragment initialization: Internal error: Conversion to relational > algebra failed to preserve datatypes: > validated type: > RecordType(INTEGER NOT NULL rnum, CHAR(3) CHARACTER SET "ISO-8859-1" COLLATE > "ISO-8859-1$en_US$primary" c1, CHAR(2) CHARACTER SET "ISO-8859-1" COLLATE > "ISO-8859-1$en_US$primary" c2, INTEGER c3, INTEGER EXPR$4) NOT NULL > converted type: > RecordType(INTEGER NOT NULL rnum, CHAR(3) CHARACTER SET "ISO-8859-1" COLLATE > "ISO-8859-1$en_US$primary" c1, CHAR(2) CHARACTER SET "ISO-8859-1" COLLATE > "ISO-8859-1$en_US$primary" c2, INTEGER c3, DOUBLE EXPR$4) NOT NULL > rel: > LogicalProject(rnum=[$0], c1=[$1], c2=[$2], c3=[$3], > EXPR$4=[POWER(/(CastHigh(-(SUM(*(CastHigh($3), CastHigh($3))) OVER (PARTITION > BY $1 RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING), > /(*(SUM(CastHigh($3)) OVER (PARTITION BY $1 RANGE BETWEEN UNBOUNDED PRECEDING > AND UNBOUNDED FOLLOWING), SUM(CastHigh($3)) OVER (PARTITION BY $1 RANGE > BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)), COUNT(CastHigh($3)) > OVER (PARTITION BY $1 RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED > FOLLOWING, COUNT(CastHigh($3)) OVER (PARTITION BY $1 RANGE BETWEEN > UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)), 0.5)]) > LogicalTableScan(table=[[postgres, public, tolap]]) > org.apache.drill.exec.work.foreman.Foreman.run():261 > java.util.concurrent.ThreadPoolExecutor.runWorker():1142 > java.util.concurrent.ThreadPoolExecutor$Worker.run():617 > java.lang.Thread.run():745 > Caused By (java.lang.AssertionError) Internal error: Conversion to > relational algebra failed to preserve datatypes: > validated type: > RecordType(INTEGER NOT NULL rnum, CHAR(3) CHARACTER SET "ISO-8859-1" COLLATE > "ISO-8859-1$en_US$primary" c1, CHAR(2) CHARACTER SET "ISO-8859-1" COLLATE > "ISO-8859-1$en_US$primary" c2, INTEGER c3, INTEGER EXPR$4) NOT NULL > converted type: > RecordType(INTEGER NOT NULL rnum, CHAR(3) CHARACTER SET "ISO-8859-1" COLLATE > "ISO-8859-1$en_US$primary" c1, CHAR(2) CHARACTER SET "ISO-8859-1" COLLATE > "ISO-8859-1$en_US$primary" c2, INTEGER c3, DOUBLE EXPR$4) NOT NULL > rel: > LogicalProject(rnum=[$0], c1=[$1], c2=[$2], c3=[$3], > EXPR$4=[POWER(/(CastHigh(-(SUM(*(CastHigh($3), CastHigh($3))) OVER (PARTITION > BY $1 RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING), > /(*(SUM(CastHigh($3)) OVER (PARTITION BY $1 RANGE BETWEEN UNBOUNDED PRECEDING > AND UNBOUNDED FOLLOWING), SUM(CastHigh($3)) OVER (PARTITION BY $1 RANGE > BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)), COUNT(CastHigh($3)) > OVER (PARTITION BY $1 RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED > FOL
[jira] [Commented] (DRILL-4405) invalid Postgres SQL generated for CONCAT (literal, literal)
[ https://issues.apache.org/jira/browse/DRILL-4405?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15175543#comment-15175543 ] Taras Supyk commented on DRILL-4405: Reproduced with oracle > invalid Postgres SQL generated for CONCAT (literal, literal) > - > > Key: DRILL-4405 > URL: https://issues.apache.org/jira/browse/DRILL-4405 > Project: Apache Drill > Issue Type: Bug > Components: Server >Affects Versions: 1.5.0 >Reporter: N Campbell >Assignee: Taras Supyk > > select concat( 'FF' , 'FF' ) from postgres.public.tversion > Error: DATA_READ ERROR: The JDBC storage plugin failed while trying setup the > SQL query. > sql SELECT CAST('' AS ANY) AS "EXPR$0" > FROM "public"."tversion" > plugin postgres > Fragment 0:0 > [Error Id: c3f24106-8d75-4a57-a638-ac5f0aca0769 on centos1:31010] > (org.postgresql.util.PSQLException) ERROR: syntax error at or near "ANY" > Position: 23 > org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse():2182 > org.postgresql.core.v3.QueryExecutorImpl.processResults():1911 > org.postgresql.core.v3.QueryExecutorImpl.execute():173 > org.postgresql.jdbc.PgStatement.execute():622 > org.postgresql.jdbc.PgStatement.executeWithFlags():458 > org.postgresql.jdbc.PgStatement.executeQuery():374 > org.apache.commons.dbcp.DelegatingStatement.executeQuery():208 > org.apache.commons.dbcp.DelegatingStatement.executeQuery():208 > org.apache.drill.exec.store.jdbc.JdbcRecordReader.setup():177 > org.apache.drill.exec.physical.impl.ScanBatch.():108 > org.apache.drill.exec.physical.impl.ScanBatch.():136 > org.apache.drill.exec.store.jdbc.JdbcBatchCreator.getBatch():40 > org.apache.drill.exec.store.jdbc.JdbcBatchCreator.getBatch():33 > org.apache.drill.exec.physical.impl.ImplCreator.getRecordBatch():147 > org.apache.drill.exec.physical.impl.ImplCreator.getChildren():170 > org.apache.drill.exec.physical.impl.ImplCreator.getRootExec():101 > org.apache.drill.exec.physical.impl.ImplCreator.getExec():79 > org.apache.drill.exec.work.fragment.FragmentExecutor.run():230 > org.apache.drill.common.SelfCleaningRunnable.run():38 > java.util.concurrent.ThreadPoolExecutor.runWorker():1142 > java.util.concurrent.ThreadPoolExecutor$Worker.run():617 > java.lang.Thread.run():745 > SQLState: null > ErrorCode: 0 -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (DRILL-4406) extract () Error: SYSTEM ERROR: ClassCastException. Caused By (java.lang.ClassCastException)
[ https://issues.apache.org/jira/browse/DRILL-4406?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15175542#comment-15175542 ] Taras Supyk commented on DRILL-4406: Reproduced with oracle > extract () Error: SYSTEM ERROR: ClassCastException. Caused By > (java.lang.ClassCastException) > - > > Key: DRILL-4406 > URL: https://issues.apache.org/jira/browse/DRILL-4406 > Project: Apache Drill > Issue Type: Bug > Components: Server >Affects Versions: 1.5.0 >Reporter: N Campbell >Assignee: Taras Supyk > Fix For: 1.5.0 > > > Trying to extract( ) from a Postgres timestamp column fails > create table TTS ( RNUM integer not null , CTS timestamp(3 ) ) ; > Error: SYSTEM ERROR: ClassCastException > [Error Id: 4a6a1f6e-1caa-42c4-b44c-8db62146 on centos1:31010] > (org.apache.drill.exec.work.foreman.ForemanException) Unexpected exception > during fragment initialization: null > org.apache.drill.exec.work.foreman.Foreman.run():261 > java.util.concurrent.ThreadPoolExecutor.runWorker():1142 > java.util.concurrent.ThreadPoolExecutor$Worker.run():617 > java.lang.Thread.run():745 > Caused By (java.lang.ClassCastException) null > SQLState: null > ErrorCode: 0 -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (DRILL-4407) Group by subquery causes Java NPE
[ https://issues.apache.org/jira/browse/DRILL-4407?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15175536#comment-15175536 ] Taras Supyk commented on DRILL-4407: Reproduced in oracle > Group by subquery causes Java NPE > - > > Key: DRILL-4407 > URL: https://issues.apache.org/jira/browse/DRILL-4407 > Project: Apache Drill > Issue Type: Bug > Components: Server >Affects Versions: 1.5.0 >Reporter: N Campbell >Assignee: Taras Supyk > > select count(*) from postgres.public.tjoin2 group by ( select c1 from > postgres.public.tjoin1 where rnum = 0) > Error: VALIDATION ERROR: java.lang.NullPointerException > [Error Id: d3453085-d77c-484e-8df7-f5fadc7bcc7d on centos1:31010] > (org.apache.calcite.tools.ValidationException) > java.lang.NullPointerException > org.apache.calcite.prepare.PlannerImpl.validate():189 > org.apache.calcite.prepare.PlannerImpl.validateAndGetType():198 > > org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.validateNode():451 > > org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.validateAndConvert():198 > org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.getPlan():167 > org.apache.drill.exec.planner.sql.DrillSqlWorker.getPlan():199 > org.apache.drill.exec.work.foreman.Foreman.runSQL():924 > org.apache.drill.exec.work.foreman.Foreman.run():250 > java.util.concurrent.ThreadPoolExecutor.runWorker():1142 > java.util.concurrent.ThreadPoolExecutor$Worker.run():617 > java.lang.Thread.run():745 > Caused By (java.lang.NullPointerException) null > > org.apache.calcite.sql.validate.SqlValidatorUtil$ExpansionAndDeepCopier.visit():633 > > org.apache.calcite.sql.validate.SqlValidatorUtil$ExpansionAndDeepCopier.visit():619 > org.apache.calcite.sql.SqlIdentifier.accept():274 > org.apache.calcite.sql.validate.SqlValidatorUtil$DeepCopier.visit():676 > org.apache.calcite.sql.validate.SqlValidatorUtil$DeepCopier.visit():663 > org.apache.calcite.sql.SqlNodeList.accept():152 > > org.apache.calcite.sql.util.SqlShuttle$CallCopyingArgHandler.visitChild():134 > > org.apache.calcite.sql.util.SqlShuttle$CallCopyingArgHandler.visitChild():101 > org.apache.calcite.sql.SqlOperator.acceptCall():720 > org.apache.calcite.sql.SqlSelectOperator.acceptCall():128 > > org.apache.calcite.sql.validate.SqlValidatorUtil$DeepCopier.visitScoped():686 > org.apache.calcite.sql.validate.SqlScopedShuttle.visit():50 > org.apache.calcite.sql.validate.SqlScopedShuttle.visit():32 > org.apache.calcite.sql.SqlCall.accept():130 > org.apache.calcite.sql.validate.SqlValidatorUtil$DeepCopier.visit():676 > org.apache.calcite.sql.validate.SqlValidatorUtil$DeepCopier.visit():663 > org.apache.calcite.sql.SqlNodeList.accept():152 > > org.apache.calcite.sql.validate.SqlValidatorUtil$ExpansionAndDeepCopier.copy():626 > org.apache.calcite.sql.validate.AggregatingSelectScope.():92 > org.apache.calcite.sql.validate.SqlValidatorImpl.registerQuery():2200 > org.apache.calcite.sql.validate.SqlValidatorImpl.registerQuery():2122 > > org.apache.calcite.sql.validate.SqlValidatorImpl.validateScopedExpression():835 > org.apache.calcite.sql.validate.SqlValidatorImpl.validate():551 > org.apache.calcite.prepare.PlannerImpl.validate():187 > org.apache.calcite.prepare.PlannerImpl.validateAndGetType():198 > > org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.validateNode():451 > > org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.validateAndConvert():198 > org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.getPlan():167 > org.apache.drill.exec.planner.sql.DrillSqlWorker.getPlan():199 > org.apache.drill.exec.work.foreman.Foreman.runSQL():924 > org.apache.drill.exec.work.foreman.Foreman.run():250 > java.util.concurrent.ThreadPoolExecutor.runWorker():1142 > java.util.concurrent.ThreadPoolExecutor$Worker.run():617 > java.lang.Thread.run():745 > SQLState: null > ErrorCode: 0 > create table TJOIN1 (RNUM integer not null , C1 integer, C2 integer); > create table TJOIN2 (RNUM integer not null , C1 integer, C2 char(2)); -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (DRILL-4408) re-written query projecting an aggregate on a boolean not supported by Postgres
[ https://issues.apache.org/jira/browse/DRILL-4408?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15175533#comment-15175533 ] Taras Supyk commented on DRILL-4408: Reproduced in oracle > re-written query projecting an aggregate on a boolean not supported by > Postgres > --- > > Key: DRILL-4408 > URL: https://issues.apache.org/jira/browse/DRILL-4408 > Project: Apache Drill > Issue Type: Bug > Components: Server >Affects Versions: 1.5.0 >Reporter: N Campbell >Assignee: Taras Supyk > > select rnum, c1, c2 from postgres.public.tset1 as t1 where exists ( select c1 > from postgres.public.tset2 where c1 = t1.c1 ) > Error: DATA_READ ERROR: The JDBC storage plugin failed while trying setup the > SQL query. > sql SELECT * > FROM "public"."tset1" > INNER JOIN (SELECT "c10", MIN("$f0") AS "$f1" > FROM (SELECT "t0"."c1" AS "c10", TRUE AS "$f0" > FROM "public"."tset2" > INNER JOIN (SELECT "c1" > FROM (SELECT "c1" > FROM "public"."tset1") AS "t" > GROUP BY "c1") AS "t0" ON "tset2"."c1" = "t0"."c1") AS "t1" > GROUP BY "c10") AS "t2" ON "tset1"."c1" = "t2"."c10" > plugin postgres > Fragment 0:0 > [Error Id: a00cd446-f168-463c-b2b9-bb3d6b43e729 on centos1:31010] > (org.postgresql.util.PSQLException) ERROR: function min(boolean) does not > exist > Hint: No function matches the given name and argument types. You might need > to add explicit type casts. > Position: 58 > org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse():2182 > org.postgresql.core.v3.QueryExecutorImpl.processResults():1911 > org.postgresql.core.v3.QueryExecutorImpl.execute():173 > org.postgresql.jdbc.PgStatement.execute():622 > org.postgresql.jdbc.PgStatement.executeWithFlags():458 > org.postgresql.jdbc.PgStatement.executeQuery():374 > org.apache.commons.dbcp.DelegatingStatement.executeQuery():208 > org.apache.commons.dbcp.DelegatingStatement.executeQuery():208 > org.apache.drill.exec.store.jdbc.JdbcRecordReader.setup():177 > org.apache.drill.exec.physical.impl.ScanBatch.():108 > org.apache.drill.exec.physical.impl.ScanBatch.():136 > org.apache.drill.exec.store.jdbc.JdbcBatchCreator.getBatch():40 > org.apache.drill.exec.store.jdbc.JdbcBatchCreator.getBatch():33 > org.apache.drill.exec.physical.impl.ImplCreator.getRecordBatch():147 > org.apache.drill.exec.physical.impl.ImplCreator.getChildren():170 > org.apache.drill.exec.physical.impl.ImplCreator.getRecordBatch():127 > org.apache.drill.exec.physical.impl.ImplCreator.getChildren():170 > org.apache.drill.exec.physical.impl.ImplCreator.getRecordBatch():127 > org.apache.drill.exec.physical.impl.ImplCreator.getChildren():170 > org.apache.drill.exec.physical.impl.ImplCreator.getRootExec():101 > org.apache.drill.exec.physical.impl.ImplCreator.getExec():79 > org.apache.drill.exec.work.fragment.FragmentExecutor.run():230 > org.apache.drill.common.SelfCleaningRunnable.run():38 > java.util.concurrent.ThreadPoolExecutor.runWorker():1142 > java.util.concurrent.ThreadPoolExecutor$Worker.run():617 > java.lang.Thread.run():745 > SQLState: null > ErrorCode: 0 > create table TSET1 (RNUM integer not null , C1 integer, C2 char(3)); > create table TSET2 (RNUM integer not null , C1 integer, C2 char(3)); -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (DRILL-4436) Result data gets mixed up when various tables have a column "label"
[ https://issues.apache.org/jira/browse/DRILL-4436?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15175433#comment-15175433 ] Taras Supyk commented on DRILL-4436: Confirmed Reproducing steps: insert into Gender values(1, 'genderLabel'); insert into Civility values (1, 'cv', 'civilityLabel'); select Gender.label as label from Gender join Civility on genderId = civilityId; returns civilityLabel instead of genderLabel > Result data gets mixed up when various tables have a column "label" > --- > > Key: DRILL-4436 > URL: https://issues.apache.org/jira/browse/DRILL-4436 > Project: Apache Drill > Issue Type: Bug > Components: Storage - JDBC >Affects Versions: 1.5.0 > Environment: Drill 1.5.0 with Zookeeper on CentOS 7.0 >Reporter: Vincent Uribe >Assignee: Taras Supyk > > We have two tables in a MySQL database: > CREATE TABLE `Gender` ( > `genderId` bigint(20) NOT NULL AUTO_INCREMENT, > `label` varchar(15) NOT NULL, > PRIMARY KEY (`genderId`) > ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1; > CREATE TABLE `Civility` ( > `civilityId` bigint(20) NOT NULL AUTO_INCREMENT, > `abbreviation` varchar(15) NOT NULL, > `label` varchar(60) DEFAULT NULL > PRIMARY KEY (`civilityId`) > ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1; > With a query on these two tables with Gender.label as 'gender' and > Civility.label as 'civility', we obtain, depending of the query : > * gender in civility > * civility in the gender > * NULL in the other column (gender or civility) > if we drop the table Gender and recreate it with like this: > CREATE TABLE `Gender` ( > `genderId` bigint(20) NOT NULL AUTO_INCREMENT, > `label2` varchar(15) NOT NULL, > PRIMARY KEY (`genderId`)select * from Gender > ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1; > Everything is fine. > I guess something is wrong with the metadata... -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Updated] (DRILL-4436) Result data gets mixed up when various tables have a column "label"
[ https://issues.apache.org/jira/browse/DRILL-4436?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Taras Supyk updated DRILL-4436: --- Description: We have two tables in a MySQL database: CREATE TABLE `Gender` ( `genderId` bigint(20) NOT NULL AUTO_INCREMENT, `label` varchar(15) NOT NULL, PRIMARY KEY (`genderId`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1; CREATE TABLE `Civility` ( `civilityId` bigint(20) NOT NULL AUTO_INCREMENT, `abbreviation` varchar(15) NOT NULL, `label` varchar(60) DEFAULT NULL PRIMARY KEY (`civilityId`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1; With a query on these two tables with Gender.label as 'gender' and Civility.label as 'civility', we obtain, depending of the query : * gender in civility * civility in the gender * NULL in the other column (gender or civility) if we drop the table Gender and recreate it with like this: CREATE TABLE `Gender` ( `genderId` bigint(20) NOT NULL AUTO_INCREMENT, `label2` varchar(15) NOT NULL, PRIMARY KEY (`genderId`)select * from Gender ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1; Everything is fine. I guess something is wrong with the metadata... was: We have two tables in a MySQL database: CREATE TABLE `Gender` ( `genderId` bigint(20) NOT NULL AUTO_INCREMENT, `label` varchar(15) NOT NULL, PRIMARY KEY (`genderId`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1; CREATE TABLE `Civility` ( `civilityId` bigint(20) NOT NULL AUTO_INCREMENT, `abbreviation` varchar(15) NOT NULL, `label` varchar(60) DEFAULT NULL PRIMARY KEY (`civilityId`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1; With a query on these two tables with Gender.label as 'gender' and Civility.label as 'civility', we obtain, depending of the query : * gender in civility * civility in the gender * NULL in the other column (gender or civility) if we drop the table Gender and recreate it with like this: CREATE TABLE `Gender` ( `genderId` bigint(20) NOT NULL AUTO_INCREMENT, `label2` varchar(15) NOT NULL, PRIMARY KEY (`genderId`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1; Everything is fine. I guess something is wrong with the metadata... > Result data gets mixed up when various tables have a column "label" > --- > > Key: DRILL-4436 > URL: https://issues.apache.org/jira/browse/DRILL-4436 > Project: Apache Drill > Issue Type: Bug > Components: Storage - JDBC >Affects Versions: 1.5.0 > Environment: Drill 1.5.0 with Zookeeper on CentOS 7.0 >Reporter: Vincent Uribe >Assignee: Taras Supyk > > We have two tables in a MySQL database: > CREATE TABLE `Gender` ( > `genderId` bigint(20) NOT NULL AUTO_INCREMENT, > `label` varchar(15) NOT NULL, > PRIMARY KEY (`genderId`) > ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1; > CREATE TABLE `Civility` ( > `civilityId` bigint(20) NOT NULL AUTO_INCREMENT, > `abbreviation` varchar(15) NOT NULL, > `label` varchar(60) DEFAULT NULL > PRIMARY KEY (`civilityId`) > ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1; > With a query on these two tables with Gender.label as 'gender' and > Civility.label as 'civility', we obtain, depending of the query : > * gender in civility > * civility in the gender > * NULL in the other column (gender or civility) > if we drop the table Gender and recreate it with like this: > CREATE TABLE `Gender` ( > `genderId` bigint(20) NOT NULL AUTO_INCREMENT, > `label2` varchar(15) NOT NULL, > PRIMARY KEY (`genderId`)select * from Gender > ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1; > Everything is fine. > I guess something is wrong with the metadata... -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (DRILL-4404) Java NPE. Unexpected exception during fragment initialization
[ https://issues.apache.org/jira/browse/DRILL-4404?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15171855#comment-15171855 ] Taras Supyk commented on DRILL-4404: Can't reproduce > Java NPE. Unexpected exception during fragment initialization > -- > > Key: DRILL-4404 > URL: https://issues.apache.org/jira/browse/DRILL-4404 > Project: Apache Drill > Issue Type: Bug > Components: Server >Affects Versions: 1.5.0 >Reporter: N Campbell >Assignee: Taras Supyk > > Error: SYSTEM ERROR: NullPointerException > [Error Id: a290df2c-d3ff-4229-bf26-50d0b6992d77 on centos1:31010] > (org.apache.drill.exec.work.foreman.ForemanException) Unexpected exception > during fragment initialization: Internal error: Error while applying rule > ReduceExpressionsRule_Project, args > [rel#830960:LogicalProject.NONE.ANY([]).[](input=rel#830959:Subset#0.JDBC.postgres.ANY([]).[],EXPR$0=-(*(2, > 2)))] > org.apache.drill.exec.work.foreman.Foreman.run():261 > java.util.concurrent.ThreadPoolExecutor.runWorker():1142 > java.util.concurrent.ThreadPoolExecutor$Worker.run():617 > java.lang.Thread.run():745 > Caused By (java.lang.AssertionError) Internal error: Error while applying > rule ReduceExpressionsRule_Project, args > [rel#830960:LogicalProject.NONE.ANY([]).[](input=rel#830959:Subset#0.JDBC.postgres.ANY([]).[],EXPR$0=-(*(2, > 2)))] > org.apache.calcite.util.Util.newInternal():792 > org.apache.calcite.plan.volcano.VolcanoRuleCall.onMatch():251 > org.apache.calcite.plan.volcano.VolcanoPlanner.findBestExp():808 > org.apache.calcite.tools.Programs$RuleSetProgram.run():303 > org.apache.calcite.prepare.PlannerImpl.transform():313 > > org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.doLogicalPlanning():542 > > org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.convertToDrel():218 > > org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.convertToDrel():252 > org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.getPlan():172 > org.apache.drill.exec.planner.sql.DrillSqlWorker.getPlan():199 > org.apache.drill.exec.work.foreman.Foreman.runSQL():924 > org.apache.drill.exec.work.foreman.Foreman.run():250 > java.util.concurrent.ThreadPoolExecutor.runWorker():1142 > java.util.concurrent.ThreadPoolExecutor$Worker.run():617 > java.lang.Thread.run():745 > Caused By (java.lang.NullPointerException) null > > org.apache.drill.exec.planner.logical.DrillOptiq$RexToDrill.visitCall():131 > org.apache.drill.exec.planner.logical.DrillOptiq$RexToDrill.visitCall():79 > org.apache.calcite.rex.RexCall.accept():107 > org.apache.drill.exec.planner.logical.DrillOptiq.toDrill():76 > org.apache.drill.exec.planner.logical.DrillConstExecutor.reduce():162 > org.apache.calcite.rel.rules.ReduceExpressionsRule.reduceExpressions():499 > org.apache.calcite.rel.rules.ReduceExpressionsRule$1.onMatch():241 > org.apache.calcite.plan.volcano.VolcanoRuleCall.onMatch():228 > org.apache.calcite.plan.volcano.VolcanoPlanner.findBestExp():808 > org.apache.calcite.tools.Programs$RuleSetProgram.run():303 > org.apache.calcite.prepare.PlannerImpl.transform():313 > > org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.doLogicalPlanning():542 > > org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.convertToDrel():218 > > org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.convertToDrel():252 > org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.getPlan():172 > org.apache.drill.exec.planner.sql.DrillSqlWorker.getPlan():199 > org.apache.drill.exec.work.foreman.Foreman.runSQL():924 > org.apache.drill.exec.work.foreman.Foreman.run():250 > java.util.concurrent.ThreadPoolExecutor.runWorker():1142 > java.util.concurrent.ThreadPoolExecutor$Worker.run():617 > java.lang.Thread.run():745 > SQLState: null > ErrorCode: 0 -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (DRILL-4399) query using OVERLAPS function executes and returns 0 rows
[ https://issues.apache.org/jira/browse/DRILL-4399?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15171714#comment-15171714 ] Taras Supyk commented on DRILL-4399: Confirmed, Same behavior as in DRILL-4409 > query using OVERLAPS function executes and returns 0 rows > - > > Key: DRILL-4399 > URL: https://issues.apache.org/jira/browse/DRILL-4399 > Project: Apache Drill > Issue Type: Bug > Components: Server >Affects Versions: 1.5.0 >Reporter: N Campbell >Assignee: Taras Supyk > > Doc set makes not mention of this, but parses and executes > select 1 from postgres.public.tdt where (date '1999-12-01' , date > '2001-12-31' ) overlaps ( date '2001-01-01' , tdt.cdt ) and rnum=0 > This query executed by Postgres would return 1 row > create table TDT ( RNUM integer not null , CDT date ) ; > comment on table TDT is 'This describes table TDT.'; > grant select on table TDT to public; > insert into TDT(RNUM, CDT) values ( 0, NULL); > insert into TDT(RNUM, CDT) values ( 1, DATE '1996-01-01'); > insert into TDT(RNUM, CDT) values ( 2, DATE '2000-01-01'); > insert into TDT(RNUM, CDT) values ( 3, DATE '2000-12-31'); -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (DRILL-4409) projecting literal will result in an empty resultset
[ https://issues.apache.org/jira/browse/DRILL-4409?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15171711#comment-15171711 ] Taras Supyk commented on DRILL-4409: Confirmed > projecting literal will result in an empty resultset > > > Key: DRILL-4409 > URL: https://issues.apache.org/jira/browse/DRILL-4409 > Project: Apache Drill > Issue Type: Bug > Components: Server >Affects Versions: 1.5.0 >Reporter: N Campbell >Assignee: Taras Supyk > > A query which projects a literal as shown against a Postgres table will > result in an empty result set being returned. > select 'BB' from postgres.public.tversion -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (DRILL-4405) invalid Postgres SQL generated for CONCAT (literal, literal)
[ https://issues.apache.org/jira/browse/DRILL-4405?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15171710#comment-15171710 ] Taras Supyk commented on DRILL-4405: Confirmed > invalid Postgres SQL generated for CONCAT (literal, literal) > - > > Key: DRILL-4405 > URL: https://issues.apache.org/jira/browse/DRILL-4405 > Project: Apache Drill > Issue Type: Bug > Components: Server >Affects Versions: 1.5.0 >Reporter: N Campbell >Assignee: Taras Supyk > > select concat( 'FF' , 'FF' ) from postgres.public.tversion > Error: DATA_READ ERROR: The JDBC storage plugin failed while trying setup the > SQL query. > sql SELECT CAST('' AS ANY) AS "EXPR$0" > FROM "public"."tversion" > plugin postgres > Fragment 0:0 > [Error Id: c3f24106-8d75-4a57-a638-ac5f0aca0769 on centos1:31010] > (org.postgresql.util.PSQLException) ERROR: syntax error at or near "ANY" > Position: 23 > org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse():2182 > org.postgresql.core.v3.QueryExecutorImpl.processResults():1911 > org.postgresql.core.v3.QueryExecutorImpl.execute():173 > org.postgresql.jdbc.PgStatement.execute():622 > org.postgresql.jdbc.PgStatement.executeWithFlags():458 > org.postgresql.jdbc.PgStatement.executeQuery():374 > org.apache.commons.dbcp.DelegatingStatement.executeQuery():208 > org.apache.commons.dbcp.DelegatingStatement.executeQuery():208 > org.apache.drill.exec.store.jdbc.JdbcRecordReader.setup():177 > org.apache.drill.exec.physical.impl.ScanBatch.():108 > org.apache.drill.exec.physical.impl.ScanBatch.():136 > org.apache.drill.exec.store.jdbc.JdbcBatchCreator.getBatch():40 > org.apache.drill.exec.store.jdbc.JdbcBatchCreator.getBatch():33 > org.apache.drill.exec.physical.impl.ImplCreator.getRecordBatch():147 > org.apache.drill.exec.physical.impl.ImplCreator.getChildren():170 > org.apache.drill.exec.physical.impl.ImplCreator.getRootExec():101 > org.apache.drill.exec.physical.impl.ImplCreator.getExec():79 > org.apache.drill.exec.work.fragment.FragmentExecutor.run():230 > org.apache.drill.common.SelfCleaningRunnable.run():38 > java.util.concurrent.ThreadPoolExecutor.runWorker():1142 > java.util.concurrent.ThreadPoolExecutor$Worker.run():617 > java.lang.Thread.run():745 > SQLState: null > ErrorCode: 0 -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (DRILL-4406) extract () Error: SYSTEM ERROR: ClassCastException. Caused By (java.lang.ClassCastException)
[ https://issues.apache.org/jira/browse/DRILL-4406?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15171706#comment-15171706 ] Taras Supyk commented on DRILL-4406: Confirmed, select extract(hour from cts) from public.tts; causes ClassCastException: org.apache.calcite.avatica.util.TimeUnitRange cannot be cast to org.apache.calcite.sql.SqlLiteral$SqlSymbol > extract () Error: SYSTEM ERROR: ClassCastException. Caused By > (java.lang.ClassCastException) > - > > Key: DRILL-4406 > URL: https://issues.apache.org/jira/browse/DRILL-4406 > Project: Apache Drill > Issue Type: Bug > Components: Server >Affects Versions: 1.5.0 >Reporter: N Campbell >Assignee: Taras Supyk > Fix For: 1.5.0 > > > Trying to extract( ) from a Postgres timestamp column fails > create table TTS ( RNUM integer not null , CTS timestamp(3 ) ) ; > Error: SYSTEM ERROR: ClassCastException > [Error Id: 4a6a1f6e-1caa-42c4-b44c-8db62146 on centos1:31010] > (org.apache.drill.exec.work.foreman.ForemanException) Unexpected exception > during fragment initialization: null > org.apache.drill.exec.work.foreman.Foreman.run():261 > java.util.concurrent.ThreadPoolExecutor.runWorker():1142 > java.util.concurrent.ThreadPoolExecutor$Worker.run():617 > java.lang.Thread.run():745 > Caused By (java.lang.ClassCastException) null > SQLState: null > ErrorCode: 0 -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (DRILL-4402) pushing unsupported full outer join to Postgres
[ https://issues.apache.org/jira/browse/DRILL-4402?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15171698#comment-15171698 ] Taras Supyk commented on DRILL-4402: Confirmed > pushing unsupported full outer join to Postgres > --- > > Key: DRILL-4402 > URL: https://issues.apache.org/jira/browse/DRILL-4402 > Project: Apache Drill > Issue Type: Bug > Components: Server >Affects Versions: 1.5.0 >Reporter: N Campbell >Assignee: Taras Supyk > > Error: DATA_READ ERROR: The JDBC storage plugin failed while trying setup the > SQL query. > sql SELECT * > FROM "public"."tjoin1" > FULL JOIN "public"."tjoin2" ON "tjoin1"."c1" < "tjoin2"."c1" > plugin postgres > Fragment 0:0 > [Error Id: bc54cf76-f4ff-474c-b3df-fa357bdf0ff8 on centos1:31010] > (org.postgresql.util.PSQLException) ERROR: FULL JOIN is only supported with > merge-joinable or hash-joinable join conditions > org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse():2182 > org.postgresql.core.v3.QueryExecutorImpl.processResults():1911 > org.postgresql.core.v3.QueryExecutorImpl.execute():173 > org.postgresql.jdbc.PgStatement.execute():622 > org.postgresql.jdbc.PgStatement.executeWithFlags():458 > org.postgresql.jdbc.PgStatement.executeQuery():374 > org.apache.commons.dbcp.DelegatingStatement.executeQuery():208 > org.apache.commons.dbcp.DelegatingStatement.executeQuery():208 > org.apache.drill.exec.store.jdbc.JdbcRecordReader.setup():177 > org.apache.drill.exec.physical.impl.ScanBatch.():108 > org.apache.drill.exec.physical.impl.ScanBatch.():136 > org.apache.drill.exec.store.jdbc.JdbcBatchCreator.getBatch():40 > org.apache.drill.exec.store.jdbc.JdbcBatchCreator.getBatch():33 > org.apache.drill.exec.physical.impl.ImplCreator.getRecordBatch():147 > org.apache.drill.exec.physical.impl.ImplCreator.getChildren():170 > org.apache.drill.exec.physical.impl.ImplCreator.getRecordBatch():127 > org.apache.drill.exec.physical.impl.ImplCreator.getChildren():170 > org.apache.drill.exec.physical.impl.ImplCreator.getRecordBatch():127 > org.apache.drill.exec.physical.impl.ImplCreator.getChildren():170 > org.apache.drill.exec.physical.impl.ImplCreator.getRootExec():101 > org.apache.drill.exec.physical.impl.ImplCreator.getExec():79 > org.apache.drill.exec.work.fragment.FragmentExecutor.run():230 > org.apache.drill.common.SelfCleaningRunnable.run():38 > java.util.concurrent.ThreadPoolExecutor.runWorker():1142 > java.util.concurrent.ThreadPoolExecutor$Worker.run():617 > java.lang.Thread.run():745 > SQLState: null > ErrorCode: 0 > create table TJOIN1 (RNUM integer not null , C1 integer, C2 integer); > insert into TJOIN1 (RNUM, C1, C2) values ( 0, 10, 15); > insert into TJOIN1 (RNUM, C1, C2) values ( 1, 20, 25); > insert into TJOIN1 (RNUM, C1, C2) values ( 2, NULL, 50); > create table TJOIN2 (RNUM integer not null , C1 integer, C2 char(2)); > insert into TJOIN2 (RNUM, C1, C2) values ( 0, 10, 'BB'); > insert into TJOIN2 (RNUM, C1, C2) values ( 1, 15, 'DD'); > insert into TJOIN2 (RNUM, C1, C2) values ( 2, NULL, 'EE'); > insert into TJOIN2 (RNUM, C1, C2) values ( 3, 10, 'FF'); -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (DRILL-4391) browsing metadata via SQLSquirrel shows Postgres indexes, primary and foreign keys as tables
[ https://issues.apache.org/jira/browse/DRILL-4391?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15171691#comment-15171691 ] Taras Supyk commented on DRILL-4391: Reproduce steps: create sequence user_ids; create table users (id integer primary key default nextval('user_ids'), login char(64); In drill console: show tables in psql.public; user_ids and users_pkey are also shown in result as tables > browsing metadata via SQLSquirrel shows Postgres indexes, primary and foreign > keys as tables > > > Key: DRILL-4391 > URL: https://issues.apache.org/jira/browse/DRILL-4391 > Project: Apache Drill > Issue Type: Bug > Components: Metadata >Affects Versions: 1.4.0 >Reporter: N Campbell >Assignee: Taras Supyk > > Apache Drill has storage defined to access a Postgres database > A schema in the database has several tables which either have indexes, > primary keys, foreign keys or combination of them all. > When SQLSquirrel presents metadata from the Drill JDBC driver the list of > tables will include entries which correspond to the indexes, primary or > foreign keys in the schema. The implication being that non-standard JDBC > metadata methods to obtain information is being used. -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (DRILL-4391) browsing metadata via SQLSquirrel shows Postgres indexes, primary and foreign keys as tables
[ https://issues.apache.org/jira/browse/DRILL-4391?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15171677#comment-15171677 ] Taras Supyk commented on DRILL-4391: Confirmed > browsing metadata via SQLSquirrel shows Postgres indexes, primary and foreign > keys as tables > > > Key: DRILL-4391 > URL: https://issues.apache.org/jira/browse/DRILL-4391 > Project: Apache Drill > Issue Type: Bug > Components: Metadata >Affects Versions: 1.4.0 >Reporter: N Campbell >Assignee: Taras Supyk > > Apache Drill has storage defined to access a Postgres database > A schema in the database has several tables which either have indexes, > primary keys, foreign keys or combination of them all. > When SQLSquirrel presents metadata from the Drill JDBC driver the list of > tables will include entries which correspond to the indexes, primary or > foreign keys in the schema. The implication being that non-standard JDBC > metadata methods to obtain information is being used. -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Comment Edited] (DRILL-4391) browsing metadata via SQLSquirrel shows Postgres indexes, primary and foreign keys as tables
[ https://issues.apache.org/jira/browse/DRILL-4391?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15171677#comment-15171677 ] Taras Supyk edited comment on DRILL-4391 at 2/29/16 10:03 AM: -- Confirmed in Drill 1.4 and 1.5 was (Author: dr-wolf): Confirmed > browsing metadata via SQLSquirrel shows Postgres indexes, primary and foreign > keys as tables > > > Key: DRILL-4391 > URL: https://issues.apache.org/jira/browse/DRILL-4391 > Project: Apache Drill > Issue Type: Bug > Components: Metadata >Affects Versions: 1.4.0 >Reporter: N Campbell >Assignee: Taras Supyk > > Apache Drill has storage defined to access a Postgres database > A schema in the database has several tables which either have indexes, > primary keys, foreign keys or combination of them all. > When SQLSquirrel presents metadata from the Drill JDBC driver the list of > tables will include entries which correspond to the indexes, primary or > foreign keys in the schema. The implication being that non-standard JDBC > metadata methods to obtain information is being used. -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (DRILL-4396) Generates invalid cast specification in re-written query to Postgres
[ https://issues.apache.org/jira/browse/DRILL-4396?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15169050#comment-15169050 ] Taras Supyk commented on DRILL-4396: Confirmed > Generates invalid cast specification in re-written query to Postgres > > > Key: DRILL-4396 > URL: https://issues.apache.org/jira/browse/DRILL-4396 > Project: Apache Drill > Issue Type: Bug > Components: Server >Affects Versions: 1.5.0 >Reporter: N Campbell >Assignee: Taras Supyk > > select vint.rnum, tflt.rnum from postgres.public.vint , postgres.public.tflt > where vint.cint = tflt.cflt > Error: DATA_READ ERROR: The JDBC storage plugin failed while trying setup the > SQL query. > sql SELECT * > FROM (SELECT "rnum", CAST("cint" AS DOUBLE) AS "$f2" > FROM "public"."vint") AS "t" > INNER JOIN "public"."tflt" ON "t"."$f2" = "tflt"."cflt" > plugin postgres > Fragment 0:0 > [Error Id: 9985ca6b-1faf-43e0-9465-b7a6e8876c6d on centos1:31010] > (org.postgresql.util.PSQLException) ERROR: type "double" does not exist > Position: 46 > org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse():2182 > org.postgresql.core.v3.QueryExecutorImpl.processResults():1911 > org.postgresql.core.v3.QueryExecutorImpl.execute():173 > org.postgresql.jdbc.PgStatement.execute():622 > org.postgresql.jdbc.PgStatement.executeWithFlags():458 > org.postgresql.jdbc.PgStatement.executeQuery():374 > org.apache.commons.dbcp.DelegatingStatement.executeQuery():208 > org.apache.commons.dbcp.DelegatingStatement.executeQuery():208 > org.apache.drill.exec.store.jdbc.JdbcRecordReader.setup():177 > org.apache.drill.exec.physical.impl.ScanBatch.():108 > org.apache.drill.exec.physical.impl.ScanBatch.():136 > org.apache.drill.exec.store.jdbc.JdbcBatchCreator.getBatch():40 > org.apache.drill.exec.store.jdbc.JdbcBatchCreator.getBatch():33 > org.apache.drill.exec.physical.impl.ImplCreator.getRecordBatch():147 > org.apache.drill.exec.physical.impl.ImplCreator.getChildren():170 > org.apache.drill.exec.physical.impl.ImplCreator.getRecordBatch():127 > org.apache.drill.exec.physical.impl.ImplCreator.getChildren():170 > org.apache.drill.exec.physical.impl.ImplCreator.getRecordBatch():127 > org.apache.drill.exec.physical.impl.ImplCreator.getChildren():170 > org.apache.drill.exec.physical.impl.ImplCreator.getRootExec():101 > org.apache.drill.exec.physical.impl.ImplCreator.getExec():79 > org.apache.drill.exec.work.fragment.FragmentExecutor.run():230 > org.apache.drill.common.SelfCleaningRunnable.run():38 > java.util.concurrent.ThreadPoolExecutor.runWorker():1142 > java.util.concurrent.ThreadPoolExecutor$Worker.run():617 > java.lang.Thread.run():745 > SQLState: null > ErrorCode: 0 > create table TINT ( RNUM integer not null , CINT integer ) ; > create view VINT as select * from TINT; > create table TFLT ( RNUM integer not null , CFLT float ) ; > create view VFLT as select * from TFLT; -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (DRILL-4408) re-written query projecting an aggregate on a boolean not supported by Postgres
[ https://issues.apache.org/jira/browse/DRILL-4408?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15169040#comment-15169040 ] Taras Supyk commented on DRILL-4408: Confirmed > re-written query projecting an aggregate on a boolean not supported by > Postgres > --- > > Key: DRILL-4408 > URL: https://issues.apache.org/jira/browse/DRILL-4408 > Project: Apache Drill > Issue Type: Bug > Components: Server >Affects Versions: 1.5.0 >Reporter: N Campbell >Assignee: Taras Supyk > > select rnum, c1, c2 from postgres.public.tset1 as t1 where exists ( select c1 > from postgres.public.tset2 where c1 = t1.c1 ) > Error: DATA_READ ERROR: The JDBC storage plugin failed while trying setup the > SQL query. > sql SELECT * > FROM "public"."tset1" > INNER JOIN (SELECT "c10", MIN("$f0") AS "$f1" > FROM (SELECT "t0"."c1" AS "c10", TRUE AS "$f0" > FROM "public"."tset2" > INNER JOIN (SELECT "c1" > FROM (SELECT "c1" > FROM "public"."tset1") AS "t" > GROUP BY "c1") AS "t0" ON "tset2"."c1" = "t0"."c1") AS "t1" > GROUP BY "c10") AS "t2" ON "tset1"."c1" = "t2"."c10" > plugin postgres > Fragment 0:0 > [Error Id: a00cd446-f168-463c-b2b9-bb3d6b43e729 on centos1:31010] > (org.postgresql.util.PSQLException) ERROR: function min(boolean) does not > exist > Hint: No function matches the given name and argument types. You might need > to add explicit type casts. > Position: 58 > org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse():2182 > org.postgresql.core.v3.QueryExecutorImpl.processResults():1911 > org.postgresql.core.v3.QueryExecutorImpl.execute():173 > org.postgresql.jdbc.PgStatement.execute():622 > org.postgresql.jdbc.PgStatement.executeWithFlags():458 > org.postgresql.jdbc.PgStatement.executeQuery():374 > org.apache.commons.dbcp.DelegatingStatement.executeQuery():208 > org.apache.commons.dbcp.DelegatingStatement.executeQuery():208 > org.apache.drill.exec.store.jdbc.JdbcRecordReader.setup():177 > org.apache.drill.exec.physical.impl.ScanBatch.():108 > org.apache.drill.exec.physical.impl.ScanBatch.():136 > org.apache.drill.exec.store.jdbc.JdbcBatchCreator.getBatch():40 > org.apache.drill.exec.store.jdbc.JdbcBatchCreator.getBatch():33 > org.apache.drill.exec.physical.impl.ImplCreator.getRecordBatch():147 > org.apache.drill.exec.physical.impl.ImplCreator.getChildren():170 > org.apache.drill.exec.physical.impl.ImplCreator.getRecordBatch():127 > org.apache.drill.exec.physical.impl.ImplCreator.getChildren():170 > org.apache.drill.exec.physical.impl.ImplCreator.getRecordBatch():127 > org.apache.drill.exec.physical.impl.ImplCreator.getChildren():170 > org.apache.drill.exec.physical.impl.ImplCreator.getRootExec():101 > org.apache.drill.exec.physical.impl.ImplCreator.getExec():79 > org.apache.drill.exec.work.fragment.FragmentExecutor.run():230 > org.apache.drill.common.SelfCleaningRunnable.run():38 > java.util.concurrent.ThreadPoolExecutor.runWorker():1142 > java.util.concurrent.ThreadPoolExecutor$Worker.run():617 > java.lang.Thread.run():745 > SQLState: null > ErrorCode: 0 > create table TSET1 (RNUM integer not null , C1 integer, C2 char(3)); > create table TSET2 (RNUM integer not null , C1 integer, C2 char(3)); -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (DRILL-4407) Group by subquery causes Java NPE
[ https://issues.apache.org/jira/browse/DRILL-4407?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15168916#comment-15168916 ] Taras Supyk commented on DRILL-4407: Confirmed > Group by subquery causes Java NPE > - > > Key: DRILL-4407 > URL: https://issues.apache.org/jira/browse/DRILL-4407 > Project: Apache Drill > Issue Type: Bug > Components: Server >Affects Versions: 1.5.0 >Reporter: N Campbell >Assignee: Taras Supyk > > select count(*) from postgres.public.tjoin2 group by ( select c1 from > postgres.public.tjoin1 where rnum = 0) > Error: VALIDATION ERROR: java.lang.NullPointerException > [Error Id: d3453085-d77c-484e-8df7-f5fadc7bcc7d on centos1:31010] > (org.apache.calcite.tools.ValidationException) > java.lang.NullPointerException > org.apache.calcite.prepare.PlannerImpl.validate():189 > org.apache.calcite.prepare.PlannerImpl.validateAndGetType():198 > > org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.validateNode():451 > > org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.validateAndConvert():198 > org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.getPlan():167 > org.apache.drill.exec.planner.sql.DrillSqlWorker.getPlan():199 > org.apache.drill.exec.work.foreman.Foreman.runSQL():924 > org.apache.drill.exec.work.foreman.Foreman.run():250 > java.util.concurrent.ThreadPoolExecutor.runWorker():1142 > java.util.concurrent.ThreadPoolExecutor$Worker.run():617 > java.lang.Thread.run():745 > Caused By (java.lang.NullPointerException) null > > org.apache.calcite.sql.validate.SqlValidatorUtil$ExpansionAndDeepCopier.visit():633 > > org.apache.calcite.sql.validate.SqlValidatorUtil$ExpansionAndDeepCopier.visit():619 > org.apache.calcite.sql.SqlIdentifier.accept():274 > org.apache.calcite.sql.validate.SqlValidatorUtil$DeepCopier.visit():676 > org.apache.calcite.sql.validate.SqlValidatorUtil$DeepCopier.visit():663 > org.apache.calcite.sql.SqlNodeList.accept():152 > > org.apache.calcite.sql.util.SqlShuttle$CallCopyingArgHandler.visitChild():134 > > org.apache.calcite.sql.util.SqlShuttle$CallCopyingArgHandler.visitChild():101 > org.apache.calcite.sql.SqlOperator.acceptCall():720 > org.apache.calcite.sql.SqlSelectOperator.acceptCall():128 > > org.apache.calcite.sql.validate.SqlValidatorUtil$DeepCopier.visitScoped():686 > org.apache.calcite.sql.validate.SqlScopedShuttle.visit():50 > org.apache.calcite.sql.validate.SqlScopedShuttle.visit():32 > org.apache.calcite.sql.SqlCall.accept():130 > org.apache.calcite.sql.validate.SqlValidatorUtil$DeepCopier.visit():676 > org.apache.calcite.sql.validate.SqlValidatorUtil$DeepCopier.visit():663 > org.apache.calcite.sql.SqlNodeList.accept():152 > > org.apache.calcite.sql.validate.SqlValidatorUtil$ExpansionAndDeepCopier.copy():626 > org.apache.calcite.sql.validate.AggregatingSelectScope.():92 > org.apache.calcite.sql.validate.SqlValidatorImpl.registerQuery():2200 > org.apache.calcite.sql.validate.SqlValidatorImpl.registerQuery():2122 > > org.apache.calcite.sql.validate.SqlValidatorImpl.validateScopedExpression():835 > org.apache.calcite.sql.validate.SqlValidatorImpl.validate():551 > org.apache.calcite.prepare.PlannerImpl.validate():187 > org.apache.calcite.prepare.PlannerImpl.validateAndGetType():198 > > org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.validateNode():451 > > org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.validateAndConvert():198 > org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.getPlan():167 > org.apache.drill.exec.planner.sql.DrillSqlWorker.getPlan():199 > org.apache.drill.exec.work.foreman.Foreman.runSQL():924 > org.apache.drill.exec.work.foreman.Foreman.run():250 > java.util.concurrent.ThreadPoolExecutor.runWorker():1142 > java.util.concurrent.ThreadPoolExecutor$Worker.run():617 > java.lang.Thread.run():745 > SQLState: null > ErrorCode: 0 > create table TJOIN1 (RNUM integer not null , C1 integer, C2 integer); > create table TJOIN2 (RNUM integer not null , C1 integer, C2 char(2)); -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (DRILL-4395) equi-inner join of two tables in Postgres returns null one of the projected columns
[ https://issues.apache.org/jira/browse/DRILL-4395?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15168882#comment-15168882 ] Taras Supyk commented on DRILL-4395: Confirmed in psql 9.3.11 > equi-inner join of two tables in Postgres returns null one of the projected > columns > --- > > Key: DRILL-4395 > URL: https://issues.apache.org/jira/browse/DRILL-4395 > Project: Apache Drill > Issue Type: Bug > Components: Server >Affects Versions: 1.5.0 >Reporter: N Campbell >Assignee: Taras Supyk > > This query should return 1,2,3,4 in both columns but returns null in the > second column. Both tables are in a Postgres 9.5 server mapped under Drill > select tint.rnum, tbint.rnum from postgres.public.tint , > postgres.public.tbint where tint.cint = tbint.cbint > create table TINT ( RNUM integer not null , CINT integer ) ; > insert into TINT(RNUM, CINT) values ( 0, NULL); > insert into TINT(RNUM, CINT) values ( 1, -1); > insert into TINT(RNUM, CINT) values ( 2, 0); > insert into TINT(RNUM, CINT) values ( 3, 1); > insert into TINT(RNUM, CINT) values ( 4, 10); > create table TBINT ( RNUM integer not null , CBINT bigint ) ; > insert into TBINT(RNUM, CBINT) values ( 0, NULL); > insert into TBINT(RNUM, CBINT) values ( 1, -1); > insert into TBINT(RNUM, CBINT) values ( 2, 0); > insert into TBINT(RNUM, CBINT) values ( 3, 1); > insert into TBINT(RNUM, CBINT) values ( 4, 10); -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (DRILL-4398) SYSTEM ERROR: IllegalStateException: Memory was leaked by query
[ https://issues.apache.org/jira/browse/DRILL-4398?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15168879#comment-15168879 ] Taras Supyk commented on DRILL-4398: Confirmed in both queries > SYSTEM ERROR: IllegalStateException: Memory was leaked by query > --- > > Key: DRILL-4398 > URL: https://issues.apache.org/jira/browse/DRILL-4398 > Project: Apache Drill > Issue Type: Bug > Components: Server >Affects Versions: 1.5.0 >Reporter: N Campbell >Assignee: Taras Supyk > > Several queries fail with memory leaked errors > select tjoin2.rnum, tjoin1.c1, tjoin2.c1 as c1j2, tjoin2.c2 as c2j2 from > postgres.public.tjoin1 full outer join postgres.public.tjoin2 on tjoin1.c1 = > tjoin2.c1 > select tjoin1.rnum, tjoin1.c1, tjoin2.c1 as c1j2, tjoin2.c2 from > postgres.public.tjoin1, lateral ( select tjoin2.c1, tjoin2.c2 from > postgres.public.tjoin2 where tjoin1.c1=tjoin2.c1) tjoin2 > SYSTEM ERROR: IllegalStateException: Memory was leaked by query. Memory > leaked: (40960) > Allocator(op:0:0:3:JdbcSubScan) 100/40960/135168/100 > (res/actual/peak/limit) > create table TJOIN1 (RNUM integer not null , C1 integer, C2 integer); > insert into TJOIN1 (RNUM, C1, C2) values ( 0, 10, 15); > insert into TJOIN1 (RNUM, C1, C2) values ( 1, 20, 25); > insert into TJOIN1 (RNUM, C1, C2) values ( 2, NULL, 50); > create table TJOIN2 (RNUM integer not null , C1 integer, C2 char(2)); > insert into TJOIN2 (RNUM, C1, C2) values ( 0, 10, 'BB'); > insert into TJOIN2 (RNUM, C1, C2) values ( 1, 15, 'DD'); > insert into TJOIN2 (RNUM, C1, C2) values ( 2, NULL, 'EE'); > insert into TJOIN2 (RNUM, C1, C2) values ( 3, 10, 'FF'); -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (DRILL-4403) AssertionError: Internal error: Conversion to relational algebra failed to preserve datatypes
[ https://issues.apache.org/jira/browse/DRILL-4403?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15168877#comment-15168877 ] Taras Supyk commented on DRILL-4403: Confirmed > AssertionError: Internal error: Conversion to relational algebra failed to > preserve datatypes > -- > > Key: DRILL-4403 > URL: https://issues.apache.org/jira/browse/DRILL-4403 > Project: Apache Drill > Issue Type: Bug > Components: Server >Affects Versions: 1.5.0 >Reporter: N Campbell >Assignee: Taras Supyk > > select rnum, c1, c2, c3, stddev_pop( c3 ) over(partition by c1) from > postgres.public.tolap > Error: SYSTEM ERROR: AssertionError: Internal error: Conversion to relational > algebra failed to preserve datatypes: > validated type: > RecordType(INTEGER NOT NULL rnum, CHAR(3) CHARACTER SET "ISO-8859-1" COLLATE > "ISO-8859-1$en_US$primary" c1, CHAR(2) CHARACTER SET "ISO-8859-1" COLLATE > "ISO-8859-1$en_US$primary" c2, INTEGER c3, INTEGER EXPR$4) NOT NULL > converted type: > RecordType(INTEGER NOT NULL rnum, CHAR(3) CHARACTER SET "ISO-8859-1" COLLATE > "ISO-8859-1$en_US$primary" c1, CHAR(2) CHARACTER SET "ISO-8859-1" COLLATE > "ISO-8859-1$en_US$primary" c2, INTEGER c3, DOUBLE EXPR$4) NOT NULL > rel: > LogicalProject(rnum=[$0], c1=[$1], c2=[$2], c3=[$3], > EXPR$4=[POWER(/(CastHigh(-(SUM(*(CastHigh($3), CastHigh($3))) OVER (PARTITION > BY $1 RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING), > /(*(SUM(CastHigh($3)) OVER (PARTITION BY $1 RANGE BETWEEN UNBOUNDED PRECEDING > AND UNBOUNDED FOLLOWING), SUM(CastHigh($3)) OVER (PARTITION BY $1 RANGE > BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)), COUNT(CastHigh($3)) > OVER (PARTITION BY $1 RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED > FOLLOWING, COUNT(CastHigh($3)) OVER (PARTITION BY $1 RANGE BETWEEN > UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)), 0.5)]) > LogicalTableScan(table=[[postgres, public, tolap]]) > [Error Id: 61be4aa1-6486-4118-a82b-86c22b551bb5 on centos1:31010] > (org.apache.drill.exec.work.foreman.ForemanException) Unexpected exception > during fragment initialization: Internal error: Conversion to relational > algebra failed to preserve datatypes: > validated type: > RecordType(INTEGER NOT NULL rnum, CHAR(3) CHARACTER SET "ISO-8859-1" COLLATE > "ISO-8859-1$en_US$primary" c1, CHAR(2) CHARACTER SET "ISO-8859-1" COLLATE > "ISO-8859-1$en_US$primary" c2, INTEGER c3, INTEGER EXPR$4) NOT NULL > converted type: > RecordType(INTEGER NOT NULL rnum, CHAR(3) CHARACTER SET "ISO-8859-1" COLLATE > "ISO-8859-1$en_US$primary" c1, CHAR(2) CHARACTER SET "ISO-8859-1" COLLATE > "ISO-8859-1$en_US$primary" c2, INTEGER c3, DOUBLE EXPR$4) NOT NULL > rel: > LogicalProject(rnum=[$0], c1=[$1], c2=[$2], c3=[$3], > EXPR$4=[POWER(/(CastHigh(-(SUM(*(CastHigh($3), CastHigh($3))) OVER (PARTITION > BY $1 RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING), > /(*(SUM(CastHigh($3)) OVER (PARTITION BY $1 RANGE BETWEEN UNBOUNDED PRECEDING > AND UNBOUNDED FOLLOWING), SUM(CastHigh($3)) OVER (PARTITION BY $1 RANGE > BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)), COUNT(CastHigh($3)) > OVER (PARTITION BY $1 RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED > FOLLOWING, COUNT(CastHigh($3)) OVER (PARTITION BY $1 RANGE BETWEEN > UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)), 0.5)]) > LogicalTableScan(table=[[postgres, public, tolap]]) > org.apache.drill.exec.work.foreman.Foreman.run():261 > java.util.concurrent.ThreadPoolExecutor.runWorker():1142 > java.util.concurrent.ThreadPoolExecutor$Worker.run():617 > java.lang.Thread.run():745 > Caused By (java.lang.AssertionError) Internal error: Conversion to > relational algebra failed to preserve datatypes: > validated type: > RecordType(INTEGER NOT NULL rnum, CHAR(3) CHARACTER SET "ISO-8859-1" COLLATE > "ISO-8859-1$en_US$primary" c1, CHAR(2) CHARACTER SET "ISO-8859-1" COLLATE > "ISO-8859-1$en_US$primary" c2, INTEGER c3, INTEGER EXPR$4) NOT NULL > converted type: > RecordType(INTEGER NOT NULL rnum, CHAR(3) CHARACTER SET "ISO-8859-1" COLLATE > "ISO-8859-1$en_US$primary" c1, CHAR(2) CHARACTER SET "ISO-8859-1" COLLATE > "ISO-8859-1$en_US$primary" c2, INTEGER c3, DOUBLE EXPR$4) NOT NULL > rel: > LogicalProject(rnum=[$0], c1=[$1], c2=[$2], c3=[$3], > EXPR$4=[POWER(/(CastHigh(-(SUM(*(CastHigh($3), CastHigh($3))) OVER (PARTITION > BY $1 RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING), > /(*(SUM(CastHigh($3)) OVER (PARTITION BY $1 RANGE BETWEEN UNBOUNDED PRECEDING > AND UNBOUNDED FOLLOWING), SUM(CastHigh($3)) OVER (PARTITION BY $1 RANGE > BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)), COUNT(CastHigh($3)) > OVER (PARTITION BY $1 RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED > FOLLOWING, C
[jira] [Comment Edited] (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 edited comment on DRILL-4374 at 2/23/16 3:00 PM: - 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. And this query: select u.id from public.users u join public.groups g on g.id = u.group_id join public.books b on b.id = u.book_id group by g.id, u.id; I have tried earlier and works good even in current jdbc driver version was (Author: dr-wolf): 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=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=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=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)