[jira] [Created] (ASTERIXDB-2504) There is not a clean way of unnesting function return value

["Xikui Wang (JIRA)" <ji...@apache.org>]

Xikui Wang created ASTERIXDB-2504:
-------------------------------------

             Summary: There is not a clean way of unnesting function return value
                 Key: ASTERIXDB-2504
                 URL: https://issues.apache.org/jira/browse/ASTERIXDB-2504
             Project: Apache AsterixDB
          Issue Type: Bug
            Reporter: Xikui Wang


Given 
[code]

drop  dataverse feeds if exists;
create  dataverse feeds;

use feeds;

create type TweetMessageType as
 open {
  id : bigint,
  text : string
};

create type KeywordType as {
    kid: int,
    country: string,
    word: string
};

create dataset Tweets(TweetMessageType) primary key id;
create dataset Keywords(KeywordType) primary key kid;


CREATE FUNCTION tweetsSafetyCheck(t) {
  LET safetyCheckFlag = CASE EXISTS(SELECT VALUE k FROM Keywords k WHERE t.country = k.country AND contains(t.text, k.word))
    WHEN true THEN "Red" ELSE "Green"
    END
  SELECT t.*, safetyCheckFlag
};
[code]

If one want to query against the function result, he/she would have to unnest the result explicitly like this:
[code]
SELECT tweet.country Country, count(tweet) TweetNum FROM Tweets tweet
LET enrichedTweet = tweetsSafetyCheck(tweet)*[0]*
WHERE enrichedTweet.safetyCheckFlag = "Red"
GROUP BY tweet.country;
[code]
It would be nice to allow writing such query without *[0]*, since one might expect the function returns a single object instead of a list which is generated by a subquery.



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