[HIVE-22658] calite for hive struct Ineffective optimization

• Type: Bug
• Status: Open
• Priority: Blocker
• Resolution: Unresolved
• Affects Version/s: 2.3.2, 2.3.3, 3.1.1
• Component/s: CBO
• Assignee: Unassigned
• Reporter: hezhang
• Created: 18/Dec/19 11:40
• Updated: 01/Feb/20 08:30

Description

student table：

CREATE TABLE `student`(
  `id` int, 
  `info` struct<name:string,age:int>)
ROW FORMAT SERDE 
  'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe' 
WITH SERDEPROPERTIES ( 
  'colelction.delim'=':', 
  'field.delim'=',', 
  'serialization.format'=',', 
  'serialization.null.format'='NULL')  

the sql:

 

select * from student where info is not null;

result:

 

1       {"name":"zhou","age":30}
2       {"name":"yan","age":30}
3       {"name":"chen","age":20}
4       {"name":"li","age":80}
NULL    NULL
NULL    {"name":null,"age":null}

cause:calcite Ineffective optimization

 cbo execute plan:

HiveProject(id=[$0], info=[$1]) 
  HiveTableScan(table=[[default.student]], table:alias=[student])

after fix RelRecordType isNullable,the plan is

cbo execute plan:

HiveProject(id=[$0], info=[$1])
  HiveFilter(condition=[IS NOT NULL($1)])
    HiveTableScan(table=[[default.student]], table:alias=[student])

then the result is:

1 {"name":"zhou","age":30} 
2 {"name":"yan","age":30} 
3 {"name":"chen","age":20} 
4 {"name":"li","age":80}
NULL    {"name":null,"age":null}

 

Issue Links

• Blocked: CALCITE-3609 Wrong query results in Hive due to wrong struct nullability