[jira] [Commented] (SPARK-21931) add LNNVL function
[ https://issues.apache.org/jira/browse/SPARK-21931?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16154898#comment-16154898 ] Takeshi Yamamuro commented on SPARK-21931: -- Why we need to support oracle-specific functions as first-class? > 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 >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-unsubscr...@spark.apache.org For additional commands, e-mail: issues-h...@spark.apache.org
[jira] [Commented] (SPARK-21931) add LNNVL function
[ https://issues.apache.org/jira/browse/SPARK-21931?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16155238#comment-16155238 ] Hyukjin Kwon commented on SPARK-21931: -- I think we can use null-safe equality comparison to cover this case. I am not sure too if we need this. > 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 >Reporter: Ruslan Dautkhanov >Priority: Minor > 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-unsubscr...@spark.apache.org For additional commands, e-mail: issues-h...@spark.apache.org
[jira] [Commented] (SPARK-21931) add LNNVL function
[
https://issues.apache.org/jira/browse/SPARK-21931?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16155797#comment-16155797
]
Ruslan Dautkhanov commented on SPARK-21931:
---
Example 1)
{code:sql}
select * from products where LNNVL(qty >= reorder_level)
{code}
without LNNVL:
{code:sql}
select * from products where NVL(qty, -1) >= NVL(reorder_level, 0)
{code}
Example 2)
{code:sql}
SELECT empno, comm FROM emp WHERE LNNVL ( comm > 0 )
{code}
without LNNVL:
{code:sql}
SELECT empno, comm FROM emp WHERE NOT ( comm > 0 )OR COMM IS NULL
{code}
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
> 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
>Reporter: Ruslan Dautkhanov
>Priority: Minor
> 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-unsubscr...@spark.apache.org
For additional commands, e-mail: issues-h...@spark.apache.org
