Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

SQL: add NULL-specific functions #34956

Closed
5 tasks done
costin opened this issue Oct 29, 2018 · 3 comments
Closed
5 tasks done

SQL: add NULL-specific functions #34956

costin opened this issue Oct 29, 2018 · 3 comments
Assignees

Comments

@costin
Copy link
Member

costin commented Oct 29, 2018

With NULL a first class citizen, we need to add NULL specific functions, some standard, some extensions, to ease its handling. This is a meta-ticket around the functions planned:

  • COALESCE SQL: Implement COALESCE function #35060
    The most generic form of null checking with fallback.
  • IFNULL/ISNULL/NVL
    2 argument variant of COALESCE (IFNULL is a MySQL variant and an ODBC system function, ISNULL is a MS-SQL server variant and NVL comes from Oracle). NVL in particular needs to pay attention to the Oracle semantics.
  • NULLIF
    Takes 2 arguments and returns NULL if the 2 arguments are equal, otherwise it returns the 1st one.
  • <=> or null-safe equals
    This needs investigating as the standard SQL for it seems to be IS (NOT) DISTINCT FROM. H2 has a variation of it x IS (NOT) y.
    MySQL variant is the most popular though.
  • GREATEST/LEAST
    Non-standard SQL but implemented by a number of DBs.
@elasticmachine
Copy link
Collaborator

Pinging @elastic/es-search-aggs

@matriv
Copy link
Contributor

matriv commented Nov 21, 2018

For the time being, we decided not to implement other variants of NVL (e.g.: NVL2) used by Oracle which you can see them listed here: https://oracle-base.com/articles/misc/null-related-functions#nvl2.
NVL2 for example is a particular case of COALESCE that adds some specific handling related to datatypes.

@matriv
Copy link
Contributor

matriv commented Nov 26, 2018

All functions are implemented.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

4 participants