Order of execution in Qualify clause

General

Order of execution in Qualify clause

CREATE MULTISET VOLATILE TABLE V_CURRENT_DATE
(
ID integer,
ID_VALUE VARCHAR(15) CHARACTER SET LATIN NOT CASESPECIFIC
)PRIMARY INDEX(ID)ON COMMIT PRESERVE ROWS;

insert into V_CURRENT_DATE
values (10,'one');
insert into V_CURRENT_DATE
values (11,'two');
insert into V_CURRENT_DATE
values (12,'three');
insert into V_CURRENT_DATE
values (13,'four');
insert into V_CURRENT_DATE
values (14,'five');

select * from V_CURRENT_DATE
qualify
row_number() over(partition by 'A' order by id)=1  and id > 12

0 records

Hi,

I am bit confused about the order of execution within qualify clause when analytical function is used along with a filter on direct column as shown above. It looks like filter over analytical function is applied first and then the filter over column.

I looked into the manual page but I dont think such example is mentioned hence can some one confirm on this behaviour.

Thanks

Zulfi

Tags (1)
2 REPLIES
Senior Apprentice

Re: Order of execution in Qualify clause

It's probably hidden somewhere in the manuals :)

OLAP-functions are calculated after GROUP BY/HAVING and QUALIFY is applied after OLAP, it's similar to FROM->WHERE, GROUP BY->HAVING.

Itzik Ben-Gan wrote some nice articles (for SQL Server: http://sqlmag.com/sql-server/logical-query-processing-what-it-and-what-it-means-you) of course Teradata's proprietary QUALIFY is missing.

Teradata Employee

Re: Order of execution in Qualify clause

The order of evaluation within the Qualify clause is not important because all conditions in qualify are executed after all of the ordered analytics have been calculated. 

If if what is desired is select the rows with ID>12, rownumber the remaining rows then qualify only the first then the ID>12 condition should be placed in the where clause to say that.