[
https://issues.apache.org/jira/browse/ASTERIXDB-1676?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15550400#comment-15550400
]
Yingyi Bu commented on ASTERIXDB-1676:
--------------------------------------
[~vignesh.raghunathan], does the query hang or just return an empty result set?
It seems to me that an empty result set might be fine, depending on what data
you have and whether you have customer_total_return tuples that satisfy the
filter.
> Query with aggregate functions in with and where clause doesn't return result
> -----------------------------------------------------------------------------
>
> Key: ASTERIXDB-1676
> URL: https://issues.apache.org/jira/browse/ASTERIXDB-1676
> Project: Apache AsterixDB
> Issue Type: Bug
> Environment: Master branch with latest commit c21de4d89a06726851ea9a54
> Reporter: Vignesh Raghunathan
> Assignee: Yingyi Bu
>
> To reproduce the issue, run the following sql++ statements:
> {code}
> drop dataverse tpcds if exists;
> create dataverse tpcds;
> use tpcds;
> create type tpcds.store_returns_type as
> closed {
> sr_customer_sk : int64,
> sr_fee : double?
> }
> create dataset store_returns (store_returns_type) primary key sr_customer_sk;
> with customer_total_return as
> (select sr_customer_sk as ctr_customer_sk
> ,sum(sr_fee) as ctr_total_return
> from store_returns
> group by sr_customer_sk)
> select ctr_customer_sk
> from customer_total_return ctr1
> where ctr1.ctr_total_return > (
> select value (avg(ctr2.ctr_total_return)*1.2)
> from customer_total_return ctr2
> where ctr1.ctr_store_sk = ctr2.ctr_store_sk)[0]
> limit 1;
> {code}
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
