NULL predicate does not behave according to the SQL standard (and e.g. PostgreSQL)

lukaseder

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

Jim_Knicely

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.