[KYLIN-3643] Derived column from windowSpec not working in where

• Type: Bug
• Status: Closed
• Priority: Critical
• Resolution: Fixed
• Fix Version/s: v2.6.0
• Environment: Kylin 2.5.0
• Assignee: Chao Long
• Reporter: May Zhou
• Created: 23/Oct/18 07:49
• Updated: 27/Jan/19 14:57
• Resolved: 26/Dec/18 06:27

Description

Derived column from windowSpec not working in where with error message of 

-1 while executing SQL: "SELECT * FROM ( SELECT ROW_NUMBER() OVER ( PARTITION BY BUYER_ID,SELLER_ID ORDER BY PRICE ) AS ROW_NUM ,TRANS_ID ,BUYER_ID ,PRICE ,SELLER_ID ,PART_DT FROM KYLIN_SALES ) T WHERE T.ROW_NUM =1 LIMIT 50000"
taking kylin sample dataset as an example,

 Here I've taken kylin sample dataset as example.

 
SELECT ROW_NUMBER() OVER (
PARTITION BY BUYER_ID,SELLER_ID ORDER BY PRICE
) AS ROW_NUM
,TRANS_ID
,BUYER_ID
,PRICE
,SELLER_ID
,PART_DT
FROM KYLIN_SALES
 

 works good.

 

But 

 

SELECT *
FROM (
SELECT ROW_NUMBER() OVER (
PARTITION BY BUYER_ID,SELLER_ID ORDER BY PRICE
) AS ROW_NUM
,TRANS_ID
,BUYER_ID
,PRICE
,SELLER_ID
,PART_DT
FROM KYLIN_SALES
) T
WHERE T.ROW_NUM =1

throws error messages