[
https://issues.apache.org/jira/browse/ASTERIXDB-1558?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15402275#comment-15402275
]
Yingyi Bu edited comment on ASTERIXDB-1558 at 8/1/16 3:49 PM:
--------------------------------------------------------------
[~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.)
was (Author: buyingyi):
[~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)
