[
https://issues.apache.org/jira/browse/DRILL-1147?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Jacques Nadeau resolved DRILL-1147.
-----------------------------------
Resolution: Fixed
> 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: DrillCommitter
> 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)
