Gopal V created HIVE-12808:
------------------------------
Summary: Logical PPD: Push filter clauses through PTF into TS
Key: HIVE-12808
URL: https://issues.apache.org/jira/browse/HIVE-12808
Project: Hive
Issue Type: Bug
Components: Logical Optimizer
Affects Versions: 1.2.1, 2.0.0
Reporter: Gopal V
Simplified repro case, 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.
{code}
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
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
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
;
{code}
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
