[
https://issues.apache.org/jira/browse/ASTERIXDB-1558?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15402275#comment-15402275
]
Yingyi Bu commented on ASTERIXDB-1558:
--------------------------------------
[~dtabass], originally I had the same thought as yours and implemented what you
expected. The table was from one SQL++ meeting minutes in last Dec. I couldn't
recall how we reached the consensus on this issue. IMO, both approaches
(explanations) make some sense to me:
-- option1. {"a":1}.b IS NULL returns FALSE: field b's value is MISSING and
hence it is not the NULL value.
-- option2. {"a":1}.b is NULL returns MISSING: IS (NOT) NULL kind of assumes
that the field presents in the record. When the field doesn't exist in the
record, SQL-92 throws an error for IS (NOT) NULL because the question doesn't
make sense. Now, in SQL++, it does not error out the query but the question of
IS (NOT) NULL for the value of field "b" (which doesn't present in the record)
still doesn't make sense. Returning a MISSING kind of signals that it's not a
valid question.
Here are some use cases of option2.
Q1. SELECT * FROM foo WHERE foo.bar IS NOT NULL; (Since we admit that
MISSING is more "unknown" than NULL(e.g., MISSING AND NULL returns MISSING), it
seems a bit surprising that {"name": "a"} shows up in the result set?)
Q2. SELECT COUNT(CASE WHEN foo.bar IS NOT NULL THEN 1 ELSE 0) FROM foo; (The
user intent of query is to count the number of foos that have a valid, non-null
bar value. For the foos that do not have the bar value are not even qualified
for the question and hence probably shouldn't be put into the THEN branch.)
> Possible minor glitch in UNKNOWN value related predicates/handling
> ------------------------------------------------------------------
>
> Key: ASTERIXDB-1558
> URL: https://issues.apache.org/jira/browse/ASTERIXDB-1558
> Project: Apache AsterixDB
> Issue Type: Bug
> Components: AsterixDB, Translator - AQL
> Reporter: Michael J. Carey
> Assignee: Yingyi Bu
> Priority: Minor
>
> The following evaluates to TRUE:
> {
> 'project': 'AsterixDB',
> 'members': [ 'vinayakb', 'dtabass', 'chenli', 'tsotras' ]
> }.member IS MISSING;
> As, desirably, does:
> {
> 'project': 'AsterixDB',
> 'members': [ 'vinayakb', 'dtabass', 'chenli', 'tsotras' ]
> }.member IS UNKNOWN;
> But the following evaluates to NULL (and it seems to me that FALSE would be
> the proper expected result):
> {
> 'project': 'AsterixDB',
> 'members': [ 'vinayakb', 'dtabass', 'chenli', 'tsotras' ]
> }.member IS NULL;
> Of course, I could be MISSING something here, as a SQL++ newbie....
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
