[ https://issues.apache.org/jira/browse/PHOENIX-5065?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Richard Antal updated PHOENIX-5065: ----------------------------------- Attachment: PHOENIX-5065.master.v2.patch > 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 > Attachments: PHOENIX-5065.master.v1.patch, > PHOENIX-5065.master.v2.patch > > > 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. Note that FOO does not exist, and is just a nonsense string > 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 (v8.3.4#803005)