Qualify rank not applied?

Database

Qualify rank not applied?

Hi,

I'm trying to use QUALIFY RANK() to limit the number of returned rows, but I'm struggeling to understand why it's not working as expected

Consider the following table:

CREATE VOLATILE TABLE qrank_test
(
a_ID INT
, a_rl_code CHAR(5)
, fld_rep INT
, vld_fm_tms TIMESTAMP(0)
, vld_to_tms TIMESTAMP(0)
)
ON COMMIT PRESERVE ROWS;

INSERT INTO qrank_test VALUES(1848138,'ADMIN', 18681, timestamp '2012-01-28 00:00:00', timestamp '2013-01-28 00:00:00');
INSERT INTO qrank_test VALUES(1848138,'ADMIN', 18683, timestamp '2013-01-28 00:00:00', timestamp '2014-03-05 00:00:00');
INSERT INTO qrank_test VALUES(1848138,'ADMIN', 6767, timestamp '2014-03-05 00:00:00', timestamp '9999-12-31 23:59:59');

I wish to use EXPAND ON PERIOD to get one row for each month. Now the months January 2013 and March 2014 will each have two rows (due to row 1 and 2 both cover January 2013, and rows 2 and 3 cover March 2014).

2014-03-01 00:00:00.0+00:00 1848138 ADMIN     6767 2014-03-05 00:00:00.0 9999-12-31 23:59:59.0

2014-03-01 00:00:00.0+00:00 1848138 ADMIN     18683 2013-01-28 00:00:00.0 2014-03-05 00:00:00.0

If I run the following query (note that the RANK() in the select is commented out) I still get two rows for the above mentioned months.

SELECT
b.*
--, RANK() OVER(PARTITION BY b.validDay, b.a_ID, b.a_rl_code ORDER BY b.Vld_Fm_Tms ASC) AS ranking
FROM
(
SELECT
BEGIN(validPeriod) AS validDay
, a.*
FROM qrank_test AS a
EXPAND ON PERIOD(ADD_MONTHS(a.Vld_Fm_Tms,-1),a.Vld_To_Tms/*+INTERVAL '1' DAY*/) AS validPeriod BY anchor month_begin FOR PERIOD(timestamp '2012-01-01 00:00:00', current_timestamp(0))
) AS b

QUALIFY RANK() OVER(PARTITION BY b.validDay, b.a_ID, b.a_rl_code ORDER BY b.Vld_Fm_Tms ASC)=1

ORDER BY a_rl_code, validDay, a_ID, fld_rep
;

2014-03-01 00:00:00.0+00:00 1848138 ADMIN     6767 2014-03-05 00:00:00.0 9999-12-31 23:59:59.0

2014-03-01 00:00:00.0+00:00 1848138 ADMIN     18683 2013-01-28 00:00:00.0 2014-03-05 00:00:00.0

The above query with QUALIFY RANK commented out give:

2014-02-01 00:00:00.0+00:00 1848138 ADMIN     18683 2013-01-28 00:00:00.0 2014-03-05 00:00:00.0 1

2014-03-01 00:00:00.0+00:00 1848138 ADMIN     6767 2014-03-05 00:00:00.0 9999-12-31 23:59:59.0 2

2014-03-01 00:00:00.0+00:00 1848138 ADMIN     18683 2013-01-28 00:00:00.0 2014-03-05 00:00:00.0 1

2014-04-01 00:00:00.0+00:00 1848138 ADMIN     6767 2014-03-05 00:00:00.0 9999-12-31 23:59:59.0 1

So it's clear the RANK is working as intented.

However, if I uncomment the RANK() in the SELECT (code below) the correct row for each of the two months is kept.

SELECT
b.*
, RANK() OVER(PARTITION BY b.validDay, b.a_ID, b.a_rl_code ORDER BY b.Vld_Fm_Tms ASC) AS ranking
FROM
(
SELECT
BEGIN(validPeriod) AS validDay
, a.*
FROM qrank_test AS a
EXPAND ON PERIOD(ADD_MONTHS(a.Vld_Fm_Tms,-1),a.Vld_To_Tms/*+INTERVAL '1' DAY*/) AS validPeriod BY anchor month_begin FOR PERIOD(timestamp '2012-01-01 00:00:00', current_timestamp(0))
) AS b

QUALIFY RANK() OVER(PARTITION BY b.validDay, b.a_ID, b.a_rl_code ORDER BY b.Vld_Fm_Tms ASC)=1

ORDER BY a_rl_code, validDay, a_ID, fld_rep
;

2014-02-01 00:00:00.0+00:00 1848138 ADMIN     18683 2013-01-28 00:00:00.0 2014-03-05 00:00:00.0 1

2014-03-01 00:00:00.0+00:00 1848138 ADMIN     18683 2013-01-28 00:00:00.0 2014-03-05 00:00:00.0 1

2014-04-01 00:00:00.0+00:00 1848138 ADMIN     6767 2014-03-05 00:00:00.0 9999-12-31 23:59:59.0 1

Also, if I specify a WHERE clause on validDay I will get the correct/expected rows:

SELECT
b.*
--, RANK() OVER(PARTITION BY b.validDay, b.a_ID, b.a_rl_code ORDER BY b.Vld_Fm_Tms ASC) AS ranking
FROM
(
SELECT
BEGIN(validPeriod) AS validDay
, a.*
FROM qrank_test AS a
EXPAND ON PERIOD(ADD_MONTHS(a.Vld_Fm_Tms,-1),a.Vld_To_Tms/*+INTERVAL '1' DAY*/) AS validPeriod BY anchor month_begin FOR PERIOD(timestamp '2012-01-01 00:00:00', current_timestamp(0))
) AS b

QUALIFY RANK() OVER(PARTITION BY b.validDay, b.a_ID, b.a_rl_code ORDER BY b.Vld_Fm_Tms ASC)=1

WHERE
b.validDay BETWEEN '2014-01-01 00:00:00' AND '2014-05-01 00:00:00'

ORDER BY a_rl_code, validDay, a_ID, fld_rep
;

So the question is; why isn't QUALIFY RANK on its own returning the expected results? What is it I'm not seeing?

1 REPLY
Senior Apprentice

Re: Qualify rank not applied?

It's a bug, if you Explain both queries you will notice that the EXPAND ON is in step #1 in one case and #3 in the other. If you're a customer open an incident.