[
https://issues.apache.org/jira/browse/HIVE-25856?focusedWorklogId=706775&page=com.atlassian.jira.plugin.system.issuetabpanels:worklog-tabpanel#worklog-706775
]
ASF GitHub Bot logged work on HIVE-25856:
-----------------------------------------
Author: ASF GitHub Bot
Created on: 11/Jan/22 10:57
Start Date: 11/Jan/22 10:57
Worklog Time Spent: 10m
Work Description: kasakrisz commented on pull request #2932:
URL: https://github.com/apache/hive/pull/2932#issuecomment-1009846688
@zabetak Nice catch!
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
Issue Time Tracking
-------------------
Worklog Id: (was: 706775)
Time Spent: 40m (was: 0.5h)
> Intermittent null ordering in plans of queries with GROUP BY and LIMIT
> ----------------------------------------------------------------------
>
> Key: HIVE-25856
> URL: https://issues.apache.org/jira/browse/HIVE-25856
> Project: Hive
> Issue Type: Bug
> Components: CBO
> Reporter: Stamatis Zampetakis
> Assignee: Stamatis Zampetakis
> Priority: Major
> Labels: pull-request-available
> Time Spent: 40m
> Remaining Estimate: 0h
>
> {code:sql}
> CREATE TABLE person (id INTEGER, country STRING);
> EXPLAIN CBO SELECT country, count(1) FROM person GROUP BY country LIMIT 5;
> {code}
> The {{EXPLAIN}} query produces a slightly different plan (ordering of nulls)
> from one execution to another.
> {noformat}
> CBO PLAN:
> HiveSortLimit(sort0=[$1], dir0=[ASC-nulls-first], fetch=[5])
> HiveProject(country=[$0], $f1=[$1])
> HiveAggregate(group=[{1}], agg#0=[count()])
> HiveTableScan(table=[[default, person]], table:alias=[person])
> {noformat}
> {noformat}
> CBO PLAN:
> HiveSortLimit(sort0=[$1], dir0=[ASC], fetch=[5])
> HiveProject(country=[$0], $f1=[$1])
> HiveAggregate(group=[{1}], agg#0=[count()])
> HiveTableScan(table=[[default, person]], table:alias=[person])
> {noformat}
> This is unlikely to cause wrong results cause most aggregate functions (not
> all) do not return nulls thus null ordering doesn't matter much but it can
> lead to other problems such as:
> * intermittent CI failures
> * query/plan caching
> I bumped into this problem after investigating test failures in CI. The
> following query in
> [offset_limit_ppd_optimizer.q|https://github.com/apache/hive/blob/9cfdac44975bf38193de7449fc21b9536109daea/ql/src/test/queries/clientpositive/offset_limit_ppd_optimizer.q]
> returns different plan when it runs individually and when it runs along with
> some other qtest files.
> {code:sql}
> explain
> select * from
> (select key, count(1) from src group by key order by key limit 10,20) subq
> join
> (select key, count(1) from src group by key limit 20,20) subq2
> on subq.key=subq2.key limit 3,5;
> {code}
--
This message was sent by Atlassian Jira
(v8.20.1#820001)
