Re: Derived column from windowSpec not working in where

2018-10-23 Thread DDDQ 
Hi Yi,
I have reproduced this issue using the sample dataset of kylin, and I've
opened a JIRA to track this issue.
https://issues.apache.org/jira/browse/KYLIN-3643

Best Regards,
May

--
Sent from: http://apache-kylin.74782.x6.nabble.com/

[jira] [Created] (KYLIN-3643) Derived column from windowSpec not working in where

2018-10-23 Thread May Zhou (JIRA) 
May Zhou created KYLIN-3643:
---

 Summary: Derived column from windowSpec not working in where
 Key: KYLIN-3643
 URL: https://issues.apache.org/jira/browse/KYLIN-3643
 Project: Kylin
  Issue Type: Bug
 Environment: Kylin 2.5.0
Reporter: May Zhou


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 5"
taking kylin sample dataset as an 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

 



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)

Derived column from windowSpec not working in where

2018-10-22 Thread yiwang 
Hello Kylin Team,

When I ran 
" with subOrder1 as ( select MEMBERID  as groupColumn, row_number()
over(partition by SUBSCRIBERFLAG order by SUBSCRIBERFLAG  Asc) rn1 from
TBLPFTEST500W_SMALL)
select groupColumn, rn1 from subOrder1 where rn1 = 1"

I got a error:
" -1 while executing SQL: "with subOrder1 as ( select MEMBERID as
groupColumn, row_number() over(partition by SUBSCRIBERFLAG order by
SUBSCRIBERFLAG Asc) rn1 from TBLPFTEST500W_SMALL ) select * from subOrder1
where rn1 =1 LIMIT 5 "

but it is good without "where rn1 = 1"

Thanks
Yi

--
Sent from: http://apache-kylin.74782.x6.nabble.com/