[jira] [Created] (SPARK-21931) add LNNVL function

["Ruslan Dautkhanov (JIRA)" <ji...@apache.org>]

Ruslan Dautkhanov created SPARK-21931:
-----------------------------------------

             Summary: add LNNVL function
                 Key: SPARK-21931
                 URL: https://issues.apache.org/jira/browse/SPARK-21931
             Project: Spark
          Issue Type: New Feature
          Components: SQL
    Affects Versions: 2.2.0, 2.3.0
            Reporter: Ruslan Dautkhanov


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.





--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

---------------------------------------------------------------------
To unsubscribe, e-mail: issues-unsubscribe@spark.apache.org
For additional commands, e-mail: issues-help@spark.apache.org