asterixdb-notifications mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Yingyi Bu (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (ASTERIXDB-1558) Possible minor glitch in UNKNOWN value related predicates/handling
Date Mon, 01 Aug 2016 15:49:20 GMT

    [ 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)

Mime
View raw message