Norris Lee created DRILL-1147:
---------------------------------
Summary: 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
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)
