Details

    • New Feature
    • Status: Resolved
    • Minor
    • Resolution: Won't Fix
    • 2.2.0
    • None
    • SQL
    • None

    Description

      Purpose

      LNNVL provides a concise way to evaluate a condition when one or both operands of the condition may be null. The function can be used only in the WHERE clause of a query. It takes as an argument a condition and returns TRUE if the condition is FALSE or UNKNOWN and FALSE if the condition is TRUE. LNNVL can be used anywhere a scalar expression can appear, even in contexts where the IS (NOT) NULL, AND, or OR conditions are not valid but would otherwise be required to account for potential nulls. Oracle Database sometimes uses the LNNVL function internally in this way to rewrite NOT IN conditions as NOT EXISTS conditions. In such cases, output from EXPLAIN PLAN shows this operation in the plan table output. The condition can evaluate any scalar values but cannot be a compound condition containing AND, OR, or BETWEEN.

      The table that follows shows what LNNVL returns given that a = 2 and b is null.

      https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions078.htm

      Attachments

        1. Capture1.JPG
          35 kB
          Ruslan Dautkhanov

        Activity

          Why we need to support oracle-specific functions as first-class?

          maropu Takeshi Yamamuro added a comment - Why we need to support oracle-specific functions as first-class?
          srowen Sean R. Owen added a comment -

          Agree, if it's just one of many non-standard SQL functions, we shouldn't add it.
          One of the points of Spark SQL is mixing code and SQL, which makes many little utility methods, useful in SQL-only environments, not very useful.

          srowen Sean R. Owen added a comment - Agree, if it's just one of many non-standard SQL functions, we shouldn't add it. One of the points of Spark SQL is mixing code and SQL, which makes many little utility methods, useful in SQL-only environments, not very useful.
          gurwls223 Hyukjin Kwon added a comment - - edited

          I think we can use null-safe equality comparison to cover this case. I am not sure too if we need this.

          gurwls223 Hyukjin Kwon added a comment - - edited I think we can use null-safe equality comparison to cover this case. I am not sure too if we need this.

          Example 1)

          select * from products where LNNVL(qty >= reorder_level)
          

          without LNNVL:

          select * from products where NVL(qty, -1) >= NVL(reorder_level, 0)
          

          Example 2)

          SELECT empno, comm FROM emp WHERE LNNVL ( comm > 0 )
          

          without LNNVL:

          SELECT empno, comm FROM emp WHERE NOT ( comm > 0 )    OR COMM IS NULL
          

          Is LNNVL essential? Nope. Is it helpful? Sometimes a lot.

          Oracle had LNNVL for ages, although they documented it reletively recently - in Oracle 11g. Hope it'll get to ANSI SQL sometimes.

          Some other helpful NULL-related Oracle functions: https://oracle-base.com/articles/misc/null-related-functions

          Tagar Ruslan Dautkhanov added a comment - Example 1) select * from products where LNNVL(qty >= reorder_level) without LNNVL: select * from products where NVL(qty, -1) >= NVL(reorder_level, 0) Example 2) SELECT empno, comm FROM emp WHERE LNNVL ( comm > 0 ) without LNNVL: SELECT empno, comm FROM emp WHERE NOT ( comm > 0 ) OR COMM IS NULL Is LNNVL essential? Nope. Is it helpful? Sometimes a lot. Oracle had LNNVL for ages, although they documented it reletively recently - in Oracle 11g. Hope it'll get to ANSI SQL sometimes. Some other helpful NULL-related Oracle functions: https://oracle-base.com/articles/misc/null-related-functions

          People

            Unassigned Unassigned
            Tagar Ruslan Dautkhanov
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved: