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
Highlighted

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.

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