[
https://issues.apache.org/jira/browse/ASTERIXDB-1632?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15468198#comment-15468198
]
Yingyi Bu commented on ASTERIXDB-1632:
--------------------------------------
[~vignesh.raghunathan], this is expected -- any SELECT subquery in SQL++
returns a collection instead of a scalar value.
Also, comparing un-comparable types returns a null, for instance:
1>[1];
returns null.
Hence, the where filter in your query always return null and all input tuples
are dis-qualified.
For your query, you might want to say:
with samptab as (
select id from samptable
)
select st1.id
from samptab st1
where st1.id > (select value avg(st2.id) from samptab st2)[0];
> Query having subquery referring to with clause returns incorrect results
> ------------------------------------------------------------------------
>
> Key: ASTERIXDB-1632
> URL: https://issues.apache.org/jira/browse/ASTERIXDB-1632
> Project: Apache AsterixDB
> Issue Type: Bug
> Environment: Master branch with latest commit 2b95d9ac95aa6f4dd847
> Reporter: Vignesh Raghunathan
>
> To reproduce the issue, run the following sqlpp statements:
> {code}
> drop dataverse sampdb if exists;
> create dataverse sampdb;
> use sampdb;
> drop dataset samptable if exists;
> drop type samptabletype if exists;
> create type samptabletype as closed {
> id: int8
> };
> create dataset samptable(samptabletype) primary key id;
> insert into samptable ({'id' : 1});
> insert into samptable ({'id' : 2});
> insert into samptable ({'id' : 3});
> with samptab as (
> select id from samptable
> )
> select st1.id
> from samptab st1
> where st1.id > (select value avg(st2.id) from samptab st2);
> {code}
> The query doesn't return {id:3}
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
