[HIVE-25985] Estimate stats gives out incorrect number of columns during query planning when using predicates like c=22

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 3.0.0
• Component/s: Hive

Environment

Hive 3

• Assignee: Ashish Sharma
• Reporter: Sindhu Subhas
• Created: 25/Feb/22 14:36
• Updated: 26/Feb/22 10:30

Description

Table type: External
Stats: No stats collected.

When filter operator appeared in the plan and the row estimates went bad. Changed the original query on table and modifying the filter predicate form.

 

predicate form	optimised as	filter Op rows out	estimate quality
prd_i_tmp.type = '22'	predicate:(type = '22')	Filter Operator [FIL_12] (rows=5 width=3707) |bad
prd_i_tmp.type in ('22')	predicate:(type = '22')	Filter Operator [FIL_12] (rows=5 width=3707)	bad
prd_i_tmp.type < '23' and prd_i_tmp.type > '21'	predicate(type < '23') and (type > '21'))	Filter Operator [FIL_12] (rows=8706269 width=3707)	good
prd_i_tmp.type like '22'	predicate:(type like '22')	Filter Operator [FIL_12] (rows=39178213 width=3707)	best
prd_i_tmp.type in ('22','AA','BB')	predicate:(type) IN ('22', 'AA', 'BB')	Filter Operator [FIL_12] (rows=15 width=3707)	bad
prd_i_tmp.type rlike '22'	predicate:type regexp '22'	Filter Operator [FIL_12] (rows=39178213 width=3707)	good

Issue Links

• relates to: HIVE-21793 CBO retrieves column stats even if hive.stats.fetch.column.stats is set to false
• relates to: HIVE-22163 CBO: Enabling CBO turns on stats estimation, even when the estimation is disabled