[HIVE-24690] GlobalLimitOptimizer Fails To Identify Some Queries With LIMIT Operator

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 1.1.0, 2.1.0, 3.1.0
• Component/s: Query Planning
• Target Version/s: 4.0.0
• Assignee: Syed Shameerur Rahman
• Reporter: Syed Shameerur Rahman
• Created: 27/Jan/21 11:58
• Updated: 28/Jan/21 04:26

Description

As per https://github.com/apache/hive/blob/master/ql/src/java/org/apache/hadoop/hive/ql/optimizer/GlobalLimitOptimizer.java#L88 queries like

CREATE TABLE ... AS SELECT col1, col2 FROM tbl LIMIT ..
INSERT OVERWRITE TABLE ... SELECT col1, hash(col2), split(col1) FROM ... LIMIT...

falls under the category of qualified list, But after HIVE-9444 it is not.

On investigating this issue, It is found that for

CREATE TABLE ... AS SELECT col1, col2 FROM tbl LIMIT 

query the operator tree looks like TS -> SEL -> LIM -> RS -> SEL -> LIM -> FS

Since only only LIMIT operator is allowed as per https://github.com/apache/hive/blob/master/ql/src/java/org/apache/hadoop/hive/ql/optimizer/GlobalLimitOptimizer.java#L196 , The GlobalLimitOptimizer fails to identify such queries.

Steps To Reproduce

set hive.limit.optimize.enable=true;
create table t1 (a int);
create table t2 as select * from t1 LIMIT 10;

Issue Links

• is caused by: HIVE-9444 CBO (Calcite Return Path): Rewrite GlobalLimitOptimizer