drill-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Hanifi Gunes (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DRILL-1147) Nested where clauses cause an error
Date Thu, 31 Jul 2014 22:19:38 GMT

    [ https://issues.apache.org/jira/browse/DRILL-1147?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14081587#comment-14081587
] 

Hanifi Gunes commented on DRILL-1147:
-------------------------------------

Cool.

> Nested where clauses cause an error
> -----------------------------------
>
>                 Key: DRILL-1147
>                 URL: https://issues.apache.org/jira/browse/DRILL-1147
>             Project: Apache Drill
>          Issue Type: Bug
>          Components: Query Planning & Optimization
>            Reporter: Norris Lee
>            Assignee: Hanifi Gunes
>             Fix For: 0.5.0
>
>
> When using CustomSQL in Tableau, Tableau will wrap your SQL query with a select from
where clause as such: 
> {code}
> SELECT * FROM ( your_query ) `TableauSQL` WHERE (0 = 1)
> {code}
> If your query happens to have a where clause, then the query will not execute.
> When using SQLLine to execute
> {code}
> SELECT * FROM ( select column1 from `hive43.default`.`integer_table` where column1 >
10 ) `TableauSQL` WHERE (0 = 1)
> {code}
> The resulting error is:
> {code}
> Query failed: org.apache.drill.exec.rpc.RpcException: Remote failure while running query.[error_id:
"aeab569f-c1f9-407e-8fc7-03d8ce81c600"
> endpoint {
>   address: "192.168.39.43"
>   user_port: 31010
>   control_port: 31011
>   data_port: 31012
> }
> error_type: 0
> message: "Failure while parsing sql. < CannotPlanException:[ Node [rel#1198:Subset#13.PHYSICAL.SINGLETON([]).[]]
could not be implemented; planner state:
> Root: rel#1198:Subset#13.PHYSICAL.SINGLETON([]).[]
> Original rel:
> AbstractConverter(subset=[rel#1198:Subset#13.PHYSICAL.SINGLETON([]).[]], convention=[PHYSICAL],
DrillDistributionTraitDef=[SINGLETON([])], sort=[[]]): rowcount = 1.0, cumulative cost = {inf},
id = 1200
>   DrillScreenRel(subset=[rel#1197:Subset#13.LOGICAL.ANY([]).[]]): rowcount = 1.0, cumulative
cost = {0.1 rows, 0.1 cpu, 0.0 io, 0.0 network}, id = 1196
>     DrillProjectRel(subset=[rel#1195:Subset#12.LOGICAL.ANY([]).[]], column1=[$1]): rowcount
= 1.0, cumulative cost = {1.0 rows, 4.0 cpu, 0.0 io, 0.0 network}, id = 1194
>       DrillFilterRel(subset=[rel#1193:Subset#11.LOGICAL.ANY([]).[]], condition=[=(0,
1)]): rowcount = 1.0, cumulative cost = {1.0 rows, 4.0 cpu, 0.0 io, 0.0 network}, id = 1192
>         DrillFilterRel(subset=[rel#1191:Subset#10.LOGICAL.ANY([]).[]], condition=[>($1,
10)]): rowcount = 1.0, cumulative cost = {1.0 rows, 4.0 cpu, 0.0 io, 0.0 network}, id = 1190
>           DrillScanRel(subset=[rel#1189:Subset#9.LOGICAL.ANY([]).[]], table=[[hive43.default,
integer_table]], groupscan=[HiveScan [table=Table(tableName:integer_table, dbName:default,
owner:root, createTime:1400090744, lastAccessTime:0, retention:0, sd:StorageDescriptor(cols:[FieldSchema(name:keycolumn,
type:string, comment:null), FieldSchema(name:column1, type:int, comment:null)], location:maprfs:/user/hive/warehouse/integer_table,
inputFormat:org.apache.hadoop.mapred.TextInputFormat, outputFormat:org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat,
compressed:false, numBuckets:-1, serdeInfo:SerDeInfo(name:null, serializationLib:org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe,
parameters:{serialization.format=,, field.delim=,}), bucketCols:[], sortCols:[], parameters:{},
skewedInfo:SkewedInfo(skewedColNames:[], skewedColValues:[], skewedColValueLocationMaps:{}),
storedAsSubDirectories:false), partitionKeys:[], parameters:{numPartitions=0, numFiles=1,
transient_lastDdlTime=1400090745, numRows=0, totalSize=262, rawDataSize=0}, viewOriginalText:null,
viewExpandedText:null, tableType:MANAGED_TABLE), inputSplits=[maprfs:/user/hive/warehouse/integer_table/Integer_Table.csv:0+262],
columns=null]]): rowcount = 1.0, cumulative cost = {0.0 rows, 0.0 cpu, 0.0 io, 0.0 network},
id = 1149
> Sets:
> Set#9, type: RecordType(VARCHAR(65535) keycolumn, INTEGER column1)
> 	rel#1189:Subset#9.LOGICAL.ANY([]).[], best=rel#1149, importance=0.5904900000000001
> 		rel#1149:DrillScanRel.LOGICAL.ANY([]).[](table=[hive43.default, integer_table],groupscan=HiveScan
[table=Table(tableName:integer_table, dbName:default, owner:root, createTime:1400090744, lastAccessTime:0,
retention:0, sd:StorageDescriptor(cols:[FieldSchema(name:keycolumn, type:string, comment:null),
FieldSchema(name:column1, type:int, comment:null)], location:maprfs:/user/hive/warehouse/integer_table,
inputFormat:org.apache.hadoop.mapred.TextInputFormat, outputFormat:org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat,
compressed:false, numBuckets:-1, serdeInfo:SerDeInfo(name:null, serializationLib:org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe,
parameters:{serialization.format=,, field.delim=,}), bucketCols:[], sortCols:[], parameters:{},
skewedInfo:SkewedInfo(skewedColNames:[], skewedColValues:[], skewedColValueLocationMaps:{}),
storedAsSubDirectories:false), partitionKeys:[], parameters:{numPartitions=0, numFiles=1,
transient_lastDdlTime=1400090745, numRows=0, totalSize=262, rawDataSize=0}, viewOriginalText:null,
viewExpandedText:null, tableType:MANAGED_TABLE), inputSplits=[maprfs:/user/hive/warehouse/integer_table/Integer_Table.csv:0+262],
columns=null]), rowcount=1.0, cumulative cost={tiny}
> 		rel#1210:AbstractConverter.LOGICAL.ANY([]).[](child=rel#1209:Subset#9.PHYSICAL.ANY([]).[],convention=LOGICAL,DrillDistributionTraitDef=ANY([]),sort=[]),
rowcount=1.0, cumulative cost={inf}
> 		rel#1221:AbstractConverter.LOGICAL.ANY([]).[](child=rel#1220:Subset#9.PHYSICAL.SINGLETON([]).[],convention=LOGICAL,DrillDistributionTraitDef=ANY([]),sort=[]),
rowcount=1.0, cumulative cost={inf}
> 	rel#1209:Subset#9.PHYSICAL.ANY([]).[], best=rel#1219, importance=0.531441
> 		rel#1211:AbstractConverter.PHYSICAL.ANY([]).[](child=rel#1189:Subset#9.LOGICAL.ANY([]).[],convention=PHYSICAL,DrillDistributionTraitDef=ANY([]),sort=[]),
rowcount=1.0, cumulative cost={inf}
> 		rel#1222:AbstractConverter.PHYSICAL.ANY([]).[](child=rel#1220:Subset#9.PHYSICAL.SINGLETON([]).[],convention=PHYSICAL,DrillDistributionTraitDef=ANY([]),sort=[]),
rowcount=1.0, cumulative cost={inf}
> 		rel#1223:AbstractConverter.PHYSICAL.SINGLETON([]).[](child=rel#1189:Subset#9.LOGICAL.ANY([]).[],convention=PHYSICAL,DrillDistributionTraitDef=SINGLETON([]),sort=[]),
rowcount=1.0, cumulative cost={inf}
> 		rel#1224:AbstractConverter.PHYSICAL.SINGLETON([]).[](child=rel#1209:Subset#9.PHYSICAL.ANY([]).[],convention=PHYSICAL,DrillDistributionTraitDef=SINGLETON([]),sort=[]),
rowcount=1.0, cumulative cost={inf}
> 		rel#1219:ScanPrel.PHYSICAL.SINGLETON([]).[](groupscan=HiveScan [table=Table(tableName:integer_table,
dbName:default, owner:root, createTime:1400090744, lastAccessTime:0, retention:0, sd:StorageDescriptor(cols:[FieldSchema(name:keycolumn,
type:string, comment:null), FieldSchema(name:column1, type:int, comment:null)], location:maprfs:/user/hive/warehouse/integer_table,
inputFormat:org.apache.hadoop.mapred.TextInputFormat, outputFormat:org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat,
compressed:false, numBuckets:-1, serdeInfo:SerDeInfo(name:null, serializationLib:org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe,
parameters:{serialization.format=,, field.delim=,}), bucketCols:[], sortCols:[], parameters:{},
skewedInfo:SkewedInfo(skewedColNames:[], skewedColValues:[], skewedColValueLocationMaps:{}),
storedAsSubDirectories:false), partitionKeys:[], parameters:{numPartitions=0, numFiles=1,
transient_lastDdlTime=1400090745, numRows=0, totalSize=262, rawDataSize=0}, viewOriginalText:null,
viewExpandedText:null, tableType:MANAGED_TABLE), inputSplits=[maprfs:/user/hive/warehouse/integer_table/Integer_Table.csv:0+262],
columns=null]), rowcount=1.0, cumulative cost={tiny}
> 	rel#1220:Subset#9.PHYSICAL.SINGLETON([]).[], best=rel#1219, importance=0.4782969000000001
> 		rel#1223:AbstractConverter.PHYSICAL.SINGLETON([]).[](child=rel#1189:Subset#9.LOGICAL.ANY([]).[],convention=PHYSICAL,DrillDistributionTraitDef=SINGLETON([]),sort=[]),
rowcount=1.0, cumulative cost={inf}
> 		rel#1224:AbstractConverter.PHYSICAL.SINGLETON([]).[](child=rel#1209:Subset#9.PHYSICAL.ANY([]).[],convention=PHYSICAL,DrillDistributionTraitDef=SINGLETON([]),sort=[]),
rowcount=1.0, cumulative cost={inf}
> 		rel#1219:ScanPrel.PHYSICAL.SINGLETON([]).[](groupscan=HiveScan [table=Table(tableName:integer_table,
dbName:default, owner:root, createTime:1400090744, lastAccessTime:0, retention:0, sd:StorageDescriptor(cols:[FieldSchema(name:keycolumn,
type:string, comment:null), FieldSchema(name:column1, type:int, comment:null)], location:maprfs:/user/hive/warehouse/integer_table,
inputFormat:org.apache.hadoop.mapred.TextInputFormat, outputFormat:org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat,
compressed:false, numBuckets:-1, serdeInfo:SerDeInfo(name:null, serializationLib:org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe,
parameters:{serialization.format=,, field.delim=,}), bucketCols:[], sortCols:[], parameters:{},
skewedInfo:SkewedInfo(skewedColNames:[], skewedColValues:[], skewedColValueLocationMaps:{}),
storedAsSubDirectories:false), partitionKeys:[], parameters:{numPartitions=0, numFiles=1,
transient_lastDdlTime=1400090745, numRows=0, totalSize=262, rawDataSize=0}, viewOriginalText:null,
viewExpandedText:null, tableType:MANAGED_TABLE), inputSplits=[maprfs:/user/hive/warehouse/integer_table/Integer_Table.csv:0+262],
columns=null]), rowcount=1.0, cumulative cost={tiny}
> Set#10, type: RecordType(VARCHAR(65535) keycolumn, INTEGER column1)
> 	rel#1191:Subset#10.LOGICAL.ANY([]).[], best=rel#1190, importance=0.6561
> 		rel#1190:DrillFilterRel.LOGICAL.ANY([]).[](child=rel#1189:Subset#9.LOGICAL.ANY([]).[],condition=>($1,
10)), rowcount=1.0, cumulative cost={2.0 rows, 5.0 cpu, 0.0 io, 0.0 network}
> 		rel#1214:AbstractConverter.LOGICAL.ANY([]).[](child=rel#1213:Subset#10.PHYSICAL.ANY([]).[],convention=LOGICAL,DrillDistributionTraitDef=ANY([]),sort=[]),
rowcount=1.0, cumulative cost={inf}
> 		rel#1227:AbstractConverter.LOGICAL.ANY([]).[](child=rel#1226:Subset#10.PHYSICAL.SINGLETON([]).[],convention=LOGICAL,DrillDistributionTraitDef=ANY([]),sort=[]),
rowcount=1.0, cumulative cost={inf}
> 	rel#1213:Subset#10.PHYSICAL.ANY([]).[], best=rel#1212, importance=0.5904900000000001
> 		rel#1215:AbstractConverter.PHYSICAL.ANY([]).[](child=rel#1191:Subset#10.LOGICAL.ANY([]).[],convention=PHYSICAL,DrillDistributionTraitDef=ANY([]),sort=[]),
rowcount=1.0, cumulative cost={inf}
> 		rel#1212:FilterPrel.PHYSICAL.ANY([]).[](child=rel#1209:Subset#9.PHYSICAL.ANY([]).[],condition=>($1,
10)), rowcount=1.0, cumulative cost={2.0 rows, 5.0 cpu, 0.0 io, 0.0 network}
> 		rel#1228:AbstractConverter.PHYSICAL.ANY([]).[](child=rel#1226:Subset#10.PHYSICAL.SINGLETON([]).[],convention=PHYSICAL,DrillDistributionTraitDef=ANY([]),sort=[]),
rowcount=1.0, cumulative cost={inf}
> 		rel#1229:AbstractConverter.PHYSICAL.SINGLETON([]).[](child=rel#1191:Subset#10.LOGICAL.ANY([]).[],convention=PHYSICAL,DrillDistributionTraitDef=SINGLETON([]),sort=[]),
rowcount=1.0, cumulative cost={inf}
> 		rel#1230:AbstractConverter.PHYSICAL.SINGLETON([]).[](child=rel#1213:Subset#10.PHYSICAL.ANY([]).[],convention=PHYSICAL,DrillDistributionTraitDef=SINGLETON([]),sort=[]),
rowcount=1.0, cumulative cost={inf}
> 		rel#1225:FilterPrel.PHYSICAL.SINGLETON([]).[](child=rel#1209:Subset#9.PHYSICAL.ANY([]).[],condition=>($1,
10)), rowcount=1.0, cumulative cost={2.0 rows, 5.0 cpu, 0.0 io, 0.0 network}
> 	rel#1226:Subset#10.PHYSICAL.SINGLETON([]).[], best=rel#1225, importance=0.531441
> 		rel#1229:AbstractConverter.PHYSICAL.SINGLETON([]).[](child=rel#1191:Subset#10.LOGICAL.ANY([]).[],convention=PHYSICAL,DrillDistributionTraitDef=SINGLETON([]),sort=[]),
rowcount=1.0, cumulative cost={inf}
> 		rel#1230:AbstractConverter.PHYSICAL.SINGLETON([]).[](child=rel#1213:Subset#10.PHYSICAL.ANY([]).[],convention=PHYSICAL,DrillDistributionTraitDef=SINGLETON([]),sort=[]),
rowcount=1.0, cumulative cost={inf}
> 		rel#1225:FilterPrel.PHYSICAL.SINGLETON([]).[](child=rel#1209:Subset#9.PHYSICAL.ANY([]).[],condition=>($1,
10)), rowcount=1.0, cumulative cost={2.0 rows, 5.0 cpu, 0.0 io, 0.0 network}
> Set#11, type: RecordType(VARCHAR(65535) keycolumn, INTEGER column1)
> 	rel#1193:Subset#11.LOGICAL.ANY([]).[], best=rel#1192, importance=0.7290000000000001
> 		rel#1192:DrillFilterRel.LOGICAL.ANY([]).[](child=rel#1191:Subset#10.LOGICAL.ANY([]).[],condition==(0,
1)), rowcount=1.0, cumulative cost={3.0 rows, 9.0 cpu, 0.0 io, 0.0 network}
> 		rel#1207:AbstractConverter.LOGICAL.ANY([]).[](child=rel#1206:Subset#11.PHYSICAL.ANY([]).[],convention=LOGICAL,DrillDistributionTraitDef=ANY([]),sort=[]),
rowcount=1.0, cumulative cost={inf}
> 	rel#1206:Subset#11.PHYSICAL.ANY([]).[], best=rel#1216, importance=0.6561
> 		rel#1208:AbstractConverter.PHYSICAL.ANY([]).[](child=rel#1193:Subset#11.LOGICAL.ANY([]).[],convention=PHYSICAL,DrillDistributionTraitDef=ANY([]),sort=[]),
rowcount=1.0, cumulative cost={inf}
> 		rel#1216:FilterPrel.PHYSICAL.ANY([]).[](child=rel#1213:Subset#10.PHYSICAL.ANY([]).[],condition==(0,
1)), rowcount=1.0, cumulative cost={3.0 rows, 9.0 cpu, 0.0 io, 0.0 network}
> Set#12, type: RecordType(INTEGER column1)
> 	rel#1195:Subset#12.LOGICAL.ANY([]).[], best=rel#1194, importance=0.81
> 		rel#1194:DrillProjectRel.LOGICAL.ANY([]).[](child=rel#1193:Subset#11.LOGICAL.ANY([]).[],column1=$1),
rowcount=1.0, cumulative cost={4.0 rows, 13.0 cpu, 0.0 io, 0.0 network}
> 		rel#1202:AbstractConverter.LOGICAL.ANY([]).[](child=rel#1201:Subset#12.PHYSICAL.SINGLETON([]).[],convention=LOGICAL,DrillDistributionTraitDef=ANY([]),sort=[]),
rowcount=1.7976931348623157E308, cumulative cost={inf}
> 	rel#1201:Subset#12.PHYSICAL.SINGLETON([]).[], best=null, importance=0.7290000000000001
> 		rel#1203:AbstractConverter.PHYSICAL.SINGLETON([]).[](child=rel#1195:Subset#12.LOGICAL.ANY([]).[],convention=PHYSICAL,DrillDistributionTraitDef=SINGLETON([]),sort=[]),
rowcount=1.0, cumulative cost={inf}
> Set#13, type: RecordType(INTEGER column1)
> 	rel#1197:Subset#13.LOGICAL.ANY([]).[], best=rel#1196, importance=0.9
> 		rel#1196:DrillScreenRel.LOGICAL.ANY([]).[](child=rel#1195:Subset#12.LOGICAL.ANY([]).[]),
rowcount=1.0, cumulative cost={4.1 rows, 13.1 cpu, 0.0 io, 0.0 network}
> 		rel#1199:AbstractConverter.LOGICAL.ANY([]).[](child=rel#1198:Subset#13.PHYSICAL.SINGLETON([]).[],convention=LOGICAL,DrillDistributionTraitDef=ANY([]),sort=[]),
rowcount=1.7976931348623157E308, cumulative cost={inf}
> 	rel#1198:Subset#13.PHYSICAL.SINGLETON([]).[], best=null, importance=1.0
> 		rel#1200:AbstractConverter.PHYSICAL.SINGLETON([]).[](child=rel#1197:Subset#13.LOGICAL.ANY([]).[],convention=PHYSICAL,DrillDistributionTraitDef=SINGLETON([]),sort=[]),
rowcount=1.0, cumulative cost={inf}
> 		rel#1204:ScreenPrel.PHYSICAL.SINGLETON([]).[](child=rel#1201:Subset#12.PHYSICAL.SINGLETON([]).[]),
rowcount=1.7976931348623157E308, cumulative cost={inf}
>  ]"
> ]
> Error: exception while executing query (state=,code=0)
> {code}



--
This message was sent by Atlassian JIRA
(v6.2#6252)

Mime
View raw message