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 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

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.