Details

      Description

      nvl2(x,y,z)
      Returns the second argument if the first argument is not null, otherwise it returns the third argument.
      nvl2 is short hand for the case expression “case when x is not null then y else z end.”

      References:
      http://docs.oracle.com/database/122/SQLRF/NVL2.htm#SQLRF00685

        Activity

        Hide
        grahn Greg Rahn added a comment -

        Alexander Behm - As we discussed, a SQL rewrite is better here given that the input types vary. I checked Vertica and Netezza and both do this and do not implement it as a function.

        Netezza:

        explain verbose select nvl2(s_city, s_county, s_state) from store;
        
        QUERY VERBOSE PLAN:
        
        Node 1.
          [SPU Sequential Scan table "STORE" {(STORE.S_STORE_SK)}]
              -- Estimated Rows = 12, Width = 19, Cost = 0.0 .. 0.0, Conf = 100.0
              Projections:
                1:CASE WHEN (STORE.S_CITY NOTNULL) THEN STORE.S_COUNTY ELSE STORE.S_STATE END
          [SPU Return]
          [Host Return]
        

        Vertica:

        explain select nvl2(s_city, s_county,s_state) from store;
        
        ExprEval: CASE WHEN (store.s_city IS NOT NULL) THEN store.s_county ELSE (store.s_state) END
        
        
        Show
        grahn Greg Rahn added a comment - Alexander Behm - As we discussed, a SQL rewrite is better here given that the input types vary. I checked Vertica and Netezza and both do this and do not implement it as a function. Netezza: explain verbose select nvl2(s_city, s_county, s_state) from store; QUERY VERBOSE PLAN: Node 1. [SPU Sequential Scan table "STORE" {(STORE.S_STORE_SK)}] -- Estimated Rows = 12, Width = 19, Cost = 0.0 .. 0.0, Conf = 100.0 Projections: 1:CASE WHEN (STORE.S_CITY NOTNULL) THEN STORE.S_COUNTY ELSE STORE.S_STATE END [SPU Return] [Host Return] Vertica: explain select nvl2(s_city, s_county,s_state) from store; ExprEval: CASE WHEN (store.s_city IS NOT NULL) THEN store.s_county ELSE (store.s_state) END
        Hide
        alex.behm Alexander Behm added a comment -

        Greg Rahn based on a discussion on the Apache dev list, I concluded that just stamping out all variants would be easiest for now. The rewrite solution requires code to first accept NVL2() during analysis, and then rewrite it later. I'd prefer to avoid the first part if possible. Of course, we can revisit later if more of similar cases for rewrite pile up.

        Show
        alex.behm Alexander Behm added a comment - Greg Rahn based on a discussion on the Apache dev list, I concluded that just stamping out all variants would be easiest for now. The rewrite solution requires code to first accept NVL2() during analysis, and then rewrite it later. I'd prefer to avoid the first part if possible. Of course, we can revisit later if more of similar cases for rewrite pile up.
        Hide
        thundergun Vincent Tran added a comment -

        Author: Vincent Tran <vttran@cloudera.com>
        AuthorDate: 2017-05-20 07:39:04 -0400
        Commit 3e58bf4074d0ff22696127d7ec726fa0b6a6fd9d

        IMPALA-5030: Adds support for NVL2() function

        This change adds support to the function NVL2(expr1, expr2, expr3).
        This function returns expr2 if expr1 is not null, else it returns
        expr3. NVL2() is converted to IF() prior to analysis.

        Change-Id: I32b03e9864f46c9c5e482280d1aa676ff7f02644
        Reviewed-on: http://gerrit.cloudera.org:8080/6962
        Reviewed-by: Alex Behm <alex.behm@cloudera.com>
        Tested-by: Impala Public Jenkins

        Show
        thundergun Vincent Tran added a comment - Author: Vincent Tran <vttran@cloudera.com> AuthorDate: 2017-05-20 07:39:04 -0400 Commit 3e58bf4074d0ff22696127d7ec726fa0b6a6fd9d IMPALA-5030 : Adds support for NVL2() function This change adds support to the function NVL2(expr1, expr2, expr3). This function returns expr2 if expr1 is not null, else it returns expr3. NVL2() is converted to IF() prior to analysis. Change-Id: I32b03e9864f46c9c5e482280d1aa676ff7f02644 Reviewed-on: http://gerrit.cloudera.org:8080/6962 Reviewed-by: Alex Behm <alex.behm@cloudera.com> Tested-by: Impala Public Jenkins
        Hide
        thundergun Vincent Tran added a comment -

        Author: Vincent Tran <vttran@cloudera.com>
        AuthorDate: 2017-05-20 07:39:04 -0400
        Commit 1b9cb04f7997eb8f741b0708a2b2e755e8c75aed

        IMPALA-5030: [TESTS] Adds support for NVL2() function

        This change adds value tests to expr-test.cc to ensure
        that NVL2() function is rewritten correctly to IF().

        Change-Id: Ide0352d611322de637a61a0004c39f5663192c52
        Reviewed-on: http://gerrit.cloudera.org:8080/7000
        Reviewed-by: Tim Armstrong <tarmstrong@cloudera.com>
        Tested-by: Impala Public Jenkins

        Show
        thundergun Vincent Tran added a comment - Author: Vincent Tran <vttran@cloudera.com> AuthorDate: 2017-05-20 07:39:04 -0400 Commit 1b9cb04f7997eb8f741b0708a2b2e755e8c75aed IMPALA-5030 : [TESTS] Adds support for NVL2() function This change adds value tests to expr-test.cc to ensure that NVL2() function is rewritten correctly to IF(). Change-Id: Ide0352d611322de637a61a0004c39f5663192c52 Reviewed-on: http://gerrit.cloudera.org:8080/7000 Reviewed-by: Tim Armstrong <tarmstrong@cloudera.com> Tested-by: Impala Public Jenkins
        Hide
        jrussell John Russell added a comment -

        The "rewrite as if()" aspect is visible in the header of the result set, for example:

        select x, nvl2(x, 999, 0) from nvl2_demo;
         ------ --------------------------- 
        | x    | if(x is not null, 999, 0) |
         ------ --------------------------- 
        | NULL | 0                         |
        

        For nvl(), the original - not the rewritten - function call is shown in the result set header.

        Show
        jrussell John Russell added a comment - The "rewrite as if()" aspect is visible in the header of the result set, for example: select x, nvl2(x, 999, 0) from nvl2_demo; ------ --------------------------- | x | if (x is not null , 999, 0) | ------ --------------------------- | NULL | 0 | For nvl(), the original - not the rewritten - function call is shown in the result set header.

          People

          • Assignee:
            thundergun Vincent Tran
            Reporter:
            grahn Greg Rahn
          • Votes:
            0 Vote for this issue
            Watchers:
            5 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development