The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.
Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

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

Comments

  • Jim_KnicelyJim_Knicely - Select Field - Administrator
    edited June 2019

    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.

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file