[DRILL-1147] Nested where clauses cause an error

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 0.5.0
• Component/s: Query Planning & Optimization
• Assignee: DrillCommitter
• Reporter: Norris Lee
• Created: 15/Jul/14 22:33
• Updated: 06/Aug/14 21:15
• Resolved: 06/Aug/14 21:15

Description

When using CustomSQL in Tableau, Tableau will wrap your SQL query with a select from where clause as such:

SELECT * FROM ( your_query ) `TableauSQL` WHERE (0 = 1)

If your query happens to have a where clause, then the query will not execute.
When using SQLLine to execute

SELECT * FROM ( select column1 from `hive43.default`.`integer_table` where column1 > 10 ) `TableauSQL` WHERE (0 = 1)

The resulting error is:

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)