spark-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Ruslan Dautkhanov (JIRA)" <j...@apache.org>
Subject [jira] [Updated] (SPARK-21931) add LNNVL function
Date Wed, 06 Sep 2017 04:08:00 GMT

     [ https://issues.apache.org/jira/browse/SPARK-21931?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

Ruslan Dautkhanov updated SPARK-21931:
--------------------------------------
    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.

!Capture1.JPG!

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

  was:
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.

!Capture1.JPG!


> 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
>         Attachments: Capture1.JPG
>
>
> 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.
> !Capture1.JPG!
> https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions078.htm 



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


Mime
View raw message