phoenix-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Geoffrey Jacoby (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (PHOENIX-5065) Inconsistent treatment of NULL and empty string
Date Sat, 08 Dec 2018 00:44:00 GMT

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

Geoffrey Jacoby commented on PHOENIX-5065:
------------------------------------------

[~jamestaylor] - The first two queries are as I expect them to be, since I don't have any
tenants named empty-string and NULL is never equal to anything. But even if we go with an
empty-string / NULL equivalence, the combination of the bottom 3 seem wrong to me.

Take the 3rd and 4th queries. Either the third query should return a non-zero row count, or
the fourth query shouldn't. The presence of an extra non-null term in the IN parentheses (which
is known to not exist)  shouldn't affect how the other terms in the IN clause are evaluated.


This is especially the case since I just tested a 5th query (now included above) where it
doesn't return rows if you look for empty string OR some other non-existent value using equal
rather than IN. 


> 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. 
> SELECT COUNT(*) FROM SYSTEM.CATALOG WHERE TENANT_ID = '' OR TENANT_ID = 'FOO'
> --Returns 0 rows, but slowly
> {code}



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

Mime
View raw message