NULL predicate does not behave according to the SQL standard (and e.g. PostgreSQL)
Consider the following statement:
SELECT (1, null) IS NOT NULL;
According to the SQL standard, and e.g. PostgreSQL, this should be false. In Vertica, this yields true. The workaround is to manually expand the predicate to
SELECT a IS NOT NULL AND b IS NOT NULL;
Is this a bug or by design? Note, I'm still on Vertica 8.1.1. From the documentation, it is unclear if this has been changed in Vertica 9:
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/LanguageElements/Predicates/NULL-predicate.htm
0
Comments
Hmm. SELECT (1, null) IS NOT NULL; seems true to me. There is a true case in that set (i.e. 1). But I get your point. There is an open Jira ticket to address this and I will keep this thread updated.