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)