Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

A bug with query Message='IllegalStateException: null', sqlState='HY000', infoMessages=None, stat

A bug with query Message='IllegalStateException: null', sqlState='HY000', infoMessages=None, stat

Expert Contributor

Hi, I have a query

 

select x from Y

join (Z) as z_1

 

1. Select x from Y works separately

2. Z query works separately

3. the whole query Z works fine in Hive (CDH 4.3)

 

4. The whole query In Impala doesn't work:

Your query has the following error(s):

Bad status for request TExecuteStatementReq(confOverlay={}, sessionHandle=TSessionHandle(sessionId=THandleIdentifier(secret='\xca\x1f\xa1\x04\xd0\xa1DA\x83~\xbb\xc9\x12\x9d\x82:', guid='\xa9WK&&FC\x87\xa4ua\x0bk/\x19\\')), statement=u'select med_re.tile_id, med_re.zone_col, med_re.zone_row, \r\n (med_re.center_lon_sum/med_re.cnt_msisdn) as med_lon , \r\n (med_re.center_lat_sum/med_re.cnt_msisdn) as med_lat, \r\n med_re.cnt_msisdn as total_cnt_of_msisdns,\r\n dist_cnt_msisdn.dist_msisdn_count as unique_visitors\r\n from (\r\n select count(msisdn) as cnt_msisdn, tile_id, zone_col, zone_row, sum(center_lon) as center_lon_sum, sum(center_lat) as center_lat_sum\r\n from route_elements\r\n group by tile_id, zone_col, zone_row\r\n \r\n ) \r\n med_re\r\njoin\r\n (\r\n select count(distinct msisdn) as dist_msisdn_count, tile_id, zone_col, zone_row\r\n from route_elements\r\n group by tile_id, zone_col, zone_row\r\n) dist_cnt_msisdn\r\n\r\nwhere dist_cnt_msisdn.tile_id = med_re.tile_id and \r\n dist_cnt_msisdn.zone_col = med_re.zone_col and \r\n dist_cnt_msisdn.zone_row = med_re.zone_row'): TExecuteStatementResp(status=TStatus(errorCode=None, errorMessage='IllegalStateException: null', sqlState='HY000', infoMessages=None, statusCode=3), operationHandle=None)

click the Error Log tab below for details

 

Here is a log from service:

 

14:15:00.182INFOcom.cloudera.impala.analysis.AggregateInfo
agg info:
AggregateInfo{grouping_exprs=(SlotRef{tblName=null, col=tile_id, id=19} SlotRef{tblName=null, col=zone_col, id=20} SlotRef{tblName=null, col=zone_row, id=21} SlotRef{tblName=null, col=msisdn, id=18}), aggregate_exprs=, agg_tuple=TupleDescriptor{id=4, tbl=null, byte_size=0, is_materialized=true, slots=[SlotDescriptor{id=22, col=null, type=INT, materialized=false, byteSize=0, byteOffset=-1, nullIndicatorByte=0, nullIndicatorBit=0, slotIdx=0}, SlotDescriptor{id=23, col=null, type=INT, materialized=false, byteSize=0, byteOffset=-1, nullIndicatorByte=0, nullIndicatorBit=0, slotIdx=0}, SlotDescriptor{id=24, col=null, type=INT, materialized=false, byteSize=0, byteOffset=-1, nullIndicatorByte=0, nullIndicatorBit=0, slotIdx=0}, SlotDescriptor{id=25, col=null, type=BIGINT, materialized=false, byteSize=0, byteOffset=-1, nullIndicatorByte=0, nullIndicatorBit=0, slotIdx=0}]}, smap=smap(SlotRef{tblName=null, col=tile_id, id=19}:SlotRef{tblName=null, col=null, id=22} SlotRef{tblName=null, col=zone_col, id=20}:SlotRef{tblName=null, col=null, id=23} SlotRef{tblName=null, col=zone_row, id=21}:SlotRef{tblName=null, col=null, id=24} SlotRef{tblName=null, col=msisdn, id=18}:SlotRef{tblName=null, col=null, id=25})}
mergeAggInfo:
AggregateInfo{grouping_exprs=(SlotRef{tblName=null, col=null, id=22} SlotRef{tblName=null, col=null, id=23} SlotRef{tblName=null, col=null, id=24} SlotRef{tblName=null, col=null, id=25}), aggregate_exprs=, agg_tuple=TupleDescriptor{id=4, tbl=null, byte_size=0, is_materialized=true, slots=[SlotDescriptor{id=22, col=null, type=INT, materialized=false, byteSize=0, byteOffset=-1, nullIndicatorByte=0, nullIndicatorBit=0, slotIdx=0}, SlotDescriptor{id=23, col=null, type=INT, materialized=false, byteSize=0, byteOffset=-1, nullIndicatorByte=0, nullIndicatorBit=0, slotIdx=0}, SlotDescriptor{id=24, col=null, type=INT, materialized=false, byteSize=0, byteOffset=-1, nullIndicatorByte=0, nullIndicatorBit=0, slotIdx=0}, SlotDescriptor{id=25, col=null, type=BIGINT, materialized=false, byteSize=0, byteOffset=-1, nullIndicatorByte=0, nullIndicatorBit=0, slotIdx=0}]}, smap=smap(SlotRef{tblName=null, col=tile_id, id=19}:SlotRef{tblName=null, col=null, id=22} SlotRef{tblName=null, col=zone_col, id=20}:SlotRef{tblName=null, col=null, id=23} SlotRef{tblName=null, col=zone_row, id=21}:SlotRef{tblName=null, col=null, id=24} SlotRef{tblName=null, col=msisdn, id=18}:SlotRef{tblName=null, col=null, id=25})}
secondPhaseDistinctAggInfo:
AggregateInfo{grouping_exprs=(SlotRef{tblName=null, col=null, id=22} SlotRef{tblName=null, col=null, id=23} SlotRef{tblName=null, col=null, id=24}), aggregate_exprs=(AggregateExpr{op=COUNT, isStar=false, isDistinct=false, (SlotRef{tblName=null, col=null, id=25})}), agg_tuple=TupleDescriptor{id=5, tbl=null, byte_size=0, is_materialized=true, slots=[SlotDescriptor{id=26, col=null, type=INT, materialized=false, byteSize=0, byteOffset=-1, nullIndicatorByte=0, nullIndicatorBit=0, slotIdx=0}, SlotDescriptor{id=27, col=null, type=INT, materialized=false, byteSize=0, byteOffset=-1, nullIndicatorByte=0, nullIndicatorBit=0, slotIdx=0}, SlotDescriptor{id=28, col=null, type=INT, materialized=false, byteSize=0, byteOffset=-1, nullIndicatorByte=0, nullIndicatorBit=0, slotIdx=0}, SlotDescriptor{id=29, col=null, type=BIGINT, materialized=false, byteSize=0, byteOffset=-1, nullIndicatorByte=0, nullIndicatorBit=0, slotIdx=0}]}, smap=smap(SlotRef{tblName=null, col=tile_id, id=19}:SlotRef{tblName=null, col=null, id=26} SlotRef{tblName=null, col=zone_col, id=20}:SlotRef{tblName=null, col=null, id=27} SlotRef{tblName=null, col=zone_row, id=21}:SlotRef{tblName=null, col=null, id=28} AggregateExpr{op=COUNT, isStar=false, isDistinct=true, (SlotRef{tblName=null, col=msisdn, id=18})}:SlotRef{tblName=null, col=null, id=29})}
mergeAggInfo:
AggregateInfo{grouping_exprs=(SlotRef{tblName=null, col=null, id=26} SlotRef{tblName=null, col=null, id=27} SlotRef{tblName=null, col=null, id=28}), aggregate_exprs=(AggregateExpr{op=SUM, isStar=false, isDistinct=false, (SlotRef{tblName=null, col=null, id=29})}), agg_tuple=TupleDescriptor{id=5, tbl=null, byte_size=0, is_materialized=true, slots=[SlotDescriptor{id=26, col=null, type=INT, materialized=false, byteSize=0, byteOffset=-1, nullIndicatorByte=0, nullIndicatorBit=0, slotIdx=0}, SlotDescriptor{id=27, col=null, type=INT, materialized=false, byteSize=0, byteOffset=-1, nullIndicatorByte=0, nullIndicatorBit=0, slotIdx=0}, SlotDescriptor{id=28, col=null, type=INT, materialized=false, byteSize=0, byteOffset=-1, nullIndicatorByte=0, nullIndicatorBit=0, slotIdx=0}, SlotDescriptor{id=29, col=null, type=BIGINT, materialized=false, byteSize=0, byteOffset=-1, nullIndicatorByte=0, nullIndicatorBit=0, slotIdx=0}]}, smap=smap(SlotRef{tblName=null, col=tile_id, id=19}:SlotRef{tblName=null, col=null, id=26} SlotRef{tblName=null, col=zone_col, id=20}:SlotRef{tblName=null, col=null, id=27} SlotRef{tblName=null, col=zone_row, id=21}:SlotRef{tblName=null, col=null, id=28} AggregateExpr{op=COUNT, isStar=false, isDistinct=true, (SlotRef{tblName=null, col=msisdn, id=18})}:SlotRef{tblName=null, col=null, id=29})}
14:15:00.186INFOcom.cloudera.impala.analysis.BinaryPredicate
dist_cnt_msisdn.tile_id = med_re.tile_id selectivity: 0.1
14:15:00.187INFOcom.cloudera.impala.analysis.BinaryPredicate
dist_cnt_msisdn.zone_col = med_re.zone_col selectivity: 0.1
14:15:00.187INFOcom.cloudera.impala.analysis.CompoundPredicate
dist_cnt_msisdn.tile_id = med_re.tile_id AND dist_cnt_msisdn.zone_col = med_re.zone_col selectivity: 0.010000000000000002
14:15:00.187INFOcom.cloudera.impala.analysis.BinaryPredicate
dist_cnt_msisdn.zone_row = med_re.zone_row selectivity: 0.1
14:15:00.187INFOcom.cloudera.impala.analysis.CompoundPredicate
dist_cnt_msisdn.tile_id = med_re.tile_id AND dist_cnt_msisdn.zone_col = med_re.zone_col AND dist_cnt_msisdn.zone_row = med_re.zone_row selectivity: 0.0010000000000000002
14:15:00.188INFOcom.cloudera.impala.service.Frontend
create plan
14:15:00.189INFOcom.cloudera.impala.planner.Planner
create single-node plan
14:15:00.189INFOcom.cloudera.impala.planner.AggregationNode
grouping expr: tile_id #distinct=-1
14:15:00.189INFOcom.cloudera.impala.planner.AggregationNode
Agg: cardinality=-1
14:15:00.189INFOcom.cloudera.impala.planner.AggregationNode
stats Agg: cardinality=-1
14:15:00.189INFOcom.cloudera.impala.planner.AggregationNode
grouping expr: tile_id #distinct=-1
14:15:00.189INFOcom.cloudera.impala.planner.AggregationNode
Agg: cardinality=-1
14:15:00.189INFOcom.cloudera.impala.planner.AggregationNode
stats Agg: cardinality=-1
14:15:00.189INFOcom.cloudera.impala.planner.AggregationNode
grouping expr: <slot 22> #distinct=-1
14:15:00.189INFOcom.cloudera.impala.planner.AggregationNode
Agg: cardinality=-1
14:15:00.189INFOcom.cloudera.impala.planner.AggregationNode
stats Agg: cardinality=-1
14:15:00.191INFOstatus.cc:42
IllegalStateException: null
@ 0x829f2d (unknown)
@ 0x6a3c5c (unknown)
@ 0x6ae9b2 (unknown)
@ 0x6aed87 (unknown)
@ 0x6ee912 (unknown)
@ 0x86fb9f (unknown)
@ 0x867924 (unknown)
@ 0x6bb76e (unknown)
@ 0x125cd69 (unknown)
@ 0x124df8f (unknown)
@ 0x124fed4 (unknown)
@ 0x1262d12 (unknown)
@ 0x33f0407851 (unknown)
@ 0x33f00e811d (unknown)

 

 

 

 

11 REPLIES 11

Re: A bug with query Message='IllegalStateException: null', sqlState='HY000', infoMessages=None, sta

Cloudera Employee

What version of impala are you running?

 

Looks like the exact query is this.

select med_re.tile_id,
       med_re.zone_col,
       med_re.zone_row,
       (med_re.center_lon_sum/med_re.cnt_msisdn) as med_lon,
       (med_re.center_lat_sum/med_re.cnt_msisdn) as med_lat,
       med_re.cnt_msisdn as total_cnt_of_msisdns,
       dist_cnt_msisdn.dist_msisdn_count as unique_visitors
from
  (select count(msisdn) as cnt_msisdn,
          tile_id,
          zone_col,
          zone_row,
          sum(center_lon) as center_lon_sum,
          sum(center_lat) as center_lat_sum
   from route_elements
   group by tile_id,
            zone_col,
            zone_row) med_re
join
  (select count(distinct msisdn) as dist_msisdn_count,
          tile_id,
          zone_col,
          zone_row
   from route_elements
   group by tile_id,
            zone_col,
            zone_row) dist_cnt_msisdn
where dist_cnt_msisdn.tile_id = med_re.tile_id
  and dist_cnt_msisdn.zone_col = med_re.zone_col
  and dist_cnt_msisdn.zone_row = med_re.zone_row

 Could you also post the DDL for the route_elements table?  This will allow us to try to reproduce the issue.

 

Re: A bug with query Message='IllegalStateException: null', sqlState='HY000', infoMessages=None, sta

Expert Contributor

I didn't get notification for the topic update :(

 

"Email me when someone replies" is turned on :(

 

Here is the query:

select med_re.tile_id, med_re.zone_col, med_re.zone_row, 
      (med_re.center_lon_sum/med_re.cnt_msisdn) as med_lon , 
      (med_re.center_lat_sum/med_re.cnt_msisdn) as med_lat, 
       med_re.cnt_msisdn as total_cnt_of_msisdns,
      dist_cnt_msisdn.dist_msisdn_count as unique_visitors
        from (
           select count(msisdn) as cnt_msisdn, tile_id, zone_col, zone_row, sum(center_lon) as center_lon_sum, sum(center_lat) as center_lat_sum
           from route_elements
           group by tile_id, zone_col, zone_row
        
        )  
        med_re
join
 (
    select count(distinct msisdn) as dist_msisdn_count, tile_id, zone_col, zone_row
    from route_elements
    group by tile_id, zone_col, zone_row
) dist_cnt_msisdn

where dist_cnt_msisdn.tile_id  =  med_re.tile_id and 
      dist_cnt_msisdn.zone_col = med_re.zone_col and  
      dist_cnt_msisdn.zone_row = med_re.zone_row

 

Here is the DDL:

CREATE EXTERNAL TABLE route_elements

ROW FORMAT SERDE
'org.apache.hadoop.hive.serde2.avro.AvroSerDe'
WITH SERDEPROPERTIES (
'avro.schema.url'='hdfs:////applications/analytics/some_task/avro_schema/route_elements.avsc')
STORED as INPUTFORMAT
'org.apache.hadoop.hive.ql.io.avro.AvroContainerInputFormat'
OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.avro.AvroContainerOutputFormat'

LOCATION '/user/hdfs/tmp/hive/route_elements'

 

Here is avro schema:

{"name": "route_elements", "doc": "version 0.0.1", "type": "record", "fields": [
{"name": "msisdn", "type": "long"},
{"name": "ts", "type": "int"},

{"name": "center_lon", "type": "double"},
{"name": "center_lat", "type": "double"},

{"name": "lac", "type": "int"},
{"name": "cid", "type": "int"},
{"name": "cell_type", "type": "string"},
{"name": "is_active", "type": "boolean"},

{"name": "tile_id", "type": "int"},
{"name": "zone_col", "type": "int"},
{"name": "zone_row", "type": "int"},

{"name": "is_end_point", "type": ["null", "boolean"]},
{"name": "end_point_type", "type": ["null", "string"]},
{"name": "route_segment_type","type": "string"}
]}

Re: A bug with query Message='IllegalStateException: null', sqlState='HY000', infoMessages=None, sta

Master Collaborator

Sergey,

 

Is it possible that the notification email went to your spam folder?

Re: A bug with query Message='IllegalStateException: null', sqlState='HY000', infoMessages=None, sta

Expert Contributor

No, they are not there.

I've started to get notifications from friday. Looks like it was a kind of bug. Doesn't matter.

We are waiting for update from your side. Query works in hive2 (cdh. 4.3), but we prefer to use impala for such easy cases.

Highlighted

Re: A bug with query Message='IllegalStateException: null', sqlState='HY000', infoMessages=None, sta

Cloudera Employee

I'll try and repro the issue in-house.  Stay tuned...

 

Re: A bug with query Message='IllegalStateException: null', sqlState='HY000', infoMessages=None, sta

Cloudera Employee

Can you also include the version of Impala you are running. e.g.

select version();

Re: A bug with query Message='IllegalStateException: null', sqlState='HY000', infoMessages=None, sta

Expert Contributor
impalad version 1.0 RELEASE (build d1bf0d1dac339af3692ffa17a5e3fdae0aed751f) Built on Sun, 28 Apr 2013 15:58:47 PST

Re: A bug with query Message='IllegalStateException: null', sqlState='HY000', infoMessages=None, sta

Expert Contributor
One more query:
select zd.top_left_lon, zd.top_left_lat, zd.bottom_right_lon, zd.bottom_right_lat,
zd.tile_id, zd.zone_col, zd.zone_row,
zd_unique.dist_msisdn_count
from zone_dimensions zd

join
(
select count(distinct msisdn) as dist_msisdn_count, tile_id, zone_col, zone_row
from route_elements_voronezh_07
group by tile_id, zone_col, zone_row
) zd_unique

where zd.tile_id = zd_unique.tile_id and
zd.zone_col = zd_unique.zone_col and
zd.zone_row = zd_unique.zone_row

It works in hive. If I do remove "zd_unique.dist_msisdn_count" from projection is starts to work.

Re: A bug with query Message='IllegalStateException: null', sqlState='HY000', infoMessages=None, sta

Expert Contributor

I've changed a little query:

select re_u.dist_msisdn,
       re_u.tile_id, re_u.zone_col, re_u.zone_row,
       zd.top_left_lon, zd.top_left_lat, zd.bottom_right_lon, zd.bottom_right_lat
from (
       select count(distinct msisdn) as dist_msisdn, tile_id, zone_col, zone_row
       from route_elements_voronezh_07
       group by tile_id, zone_col, zone_row)
re_u, zone_dimensions zd
where
zd.tile_id in (46052, 46053, 46054,
               45653, 45654, 45655,
               45253, 45254, 45255) and
      zd.tile_id  =  re_u.tile_id   and
      zd.zone_col = re_u.zone_col   and
      zd.zone_row = re_u.zone_row

 

I hope it does do the same. IN condidtion is just to trunc join table.

It works both in impala and hive