[HIVE-12808] Logical PPD: Push filter clauses through PTF(Windowing) into TS

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 1.2.1, 2.0.0
• Fix Version/s: 2.1.0
• Component/s: CBO, Logical Optimizer
• Labels: TODOC2.1
• Assignee: Laljo John Pullokkaran
• Reporter: Gopal Vijayaraghavan
• Created: 07/Jan/16 23:42
• Updated: 02/Oct/17 20:11
• Resolved: 24/Feb/16 19:52

Description

Simplified repro case of HCC #8880, with the slow query showing the push-down miss.

And the manually rewritten query to indicate the expected one.

Part of the problem could be the window range not being split apart for PPD, but the FIL is not pushed down even if the rownum filter is removed.

create temporary table positions (regionid string, id bigint, deviceid string, ts string);

insert into positions values('1d6a0be1-6366-4692-9597-ebd5cd0f01d1', 1422792010, '6c5d1a30-2331-448b-a726-a380d6b3a432', '2016-01-01'),
('1d6a0be1-6366-4692-9597-ebd5cd0f01d1', 1422792010, '6c5d1a30-2331-448b-a726-a380d6b3a432', '2016-01-01'),
('1d6a0be1-6366-4692-9597-ebd5cd0f01d1', 1422792010, '6c5d1a30-2331-448b-a726-a380d6b3a432', '2016-01-02'),
('1d6a0be1-6366-4692-9597-ebd5cd0f01d1', 1422792010, '6c5d1a30-2331-448b-a726-a380d6b3a432', '2016-01-02');


-- slow query
explain
WITH t1 AS 
( 
         SELECT   *, 
                  Row_number() over ( PARTITION BY regionid, id, deviceid ORDER BY ts DESC) AS rownos
         FROM     positions ), 
latestposition as ( 
       SELECT * 
       FROM   t1 
       WHERE  rownos = 1) 
SELECT * 
FROM   latestposition 
WHERE  regionid='1d6a0be1-6366-4692-9597-ebd5cd0f01d1' 
AND    id=1422792010 
AND    deviceid='6c5d1a30-2331-448b-a726-a380d6b3a432';

-- fast query
explain
WITH t1 AS 
( 
         SELECT   *, 
                  Row_number() over ( PARTITION BY regionid, id, deviceid ORDER BY ts DESC) AS rownos
         FROM     positions 
         WHERE  regionid='1d6a0be1-6366-4692-9597-ebd5cd0f01d1' 
         AND    id=1422792010 
         AND    deviceid='6c5d1a30-2331-448b-a726-a380d6b3a432'
),latestposition as ( 
       SELECT * 
       FROM   t1 
       WHERE  rownos = 1) 
SELECT * 
FROM   latestposition 
;

Issue Links

• is related to: HIVE-17668 Push filter clauses through PTF(Windowing) does not work in some cases