[jira] [Commented] (PHOENIX-5065) Inconsistent treatment of NULL and empty string

["James Taylor (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/PHOENIX-5065?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16713423#comment-16713423 ] 

James Taylor commented on PHOENIX-5065:
---------------------------------------

Oracle treats the empty string the same as null, so there is a precedence. The above queries look correct to me. Null is different than a value so WHERE tenant_id = '' is the same as WHERE tenant_id = null which will never return anything. If you want to find null values, you have to use WHERE tenant_id IS NULL. The IN is like an OR, so same thing.

You can read more about the somewhat strange NULL trinary logic here: https://en.wikipedia.org/wiki/Three-valued_logic

> Inconsistent treatment of NULL and empty string
> -----------------------------------------------
>
>                 Key: PHOENIX-5065
>                 URL: https://issues.apache.org/jira/browse/PHOENIX-5065
>             Project: Phoenix
>          Issue Type: Bug
>    Affects Versions: 4.14.1
>            Reporter: Geoffrey Jacoby
>            Priority: Major
>
> Phoenix doesn't handle NULLs consistently with other SQL dialects, and it doesn't handle them consistently internally either. 
> In PHOENIX-2422, [~jamestaylor] mentioned that Phoenix's intended behavior is for empty string and NULL to be equivalent. That's inconsistent with other SQL dialects (in which NULL is never equal to anything, including itself), but if that's our documented behavior, then that's fine unless PHOENIX-2422 to change it is ever worked. 
> But consider the following queries:
> {code:java}
> SELECT COUNT(*) FROM SYSTEM.CATALOG WHERE TENANT_ID = '';
> -- Returns 0 rows
> SELECT COUNT(*) FROM SYSTEM.CATALOG WHERE TENANT_ID IS NULL;
> -- Returns some number of rows. Call it N
> SELECT COUNT(*) FROM SYSTEM.CATALOG WHERE TENANT_ID IN ('');
> -- Returns 0 rows
> SELECT COUNT(*) FROM SYSTEM.CATALOG WHERE TENANT_ID IN ('', 'FOO');
> -- Returns N rows. 
> {code}



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