[jira] [Comment Edited] (CALCITE-5708) Change SUBSTRING result if either of parameters is NULL

["Evgeny Stanilovsky (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-5708?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17725091#comment-17725091 ] 

Evgeny Stanilovsky edited comment on CALCITE-5708 at 6/2/23 7:40 AM:
---------------------------------------------------------------------

I additionally change PR.

Talking about SUBSTRING case:
{noformat} select substring(s from i for l) from (values ('abc', null, 2)) as t (s, i, l); {noformat}
is differ form 

{noformat}select substring(s from i for l) from (values ('abc', 1, 2), ('abc', null, 2)) as t (s, i, l);{noformat}

from the point of view of inferred params:

1.  expr : {noformat}select SUBSTRING('text' FROM NULL FOR 2);{noformat}
type for NULL literal will not inferred [1]
[1] https://github.com/apache/calcite/blob/9c33d7aeefe082bf5f7be617ef231e1285418a6c/core/src/main/java/org/apache/calcite/sql/type/FamilyOperandTypeChecker.java#L88
thus we can`t reject NULL literals and generate null result from runtime, as i can see, so SqlTypeUtil.inSameFamilyOrNull is required

2.  expr : {noformat}select substring(s from i for l) from (values ('abc', null, 2)) as t (s, i, l);{noformat}
further call : FamilyOperandTypeChecker#checkOperandTypes and 
https://github.com/apache/calcite/blob/9c33d7aeefe082bf5f7be617ef231e1285418a6c/core/src/main/java/org/apache/calcite/sql/type/FamilyOperandTypeChecker.java#L138
will bring additional cast (main branch) and unsupported method signature as a result:
SUBSTRING(String, int, int) vs SUBSTRING(String, BigInteger, int)
SqlTypeFamily#getDefaultConcreteType ...
{noformat}case NUMERIC:
      return SqlTypeUtil.getMaxPrecisionScaleDecimal(factory);{noformat}
i change it in my PR and seems it correct, (i can rely only into my knowledge and tests:) ) and further runtime generation in :
AbstractRexCallImplementor#implement ->
AbstractRexCallImplementor#genValueStatement
input_isNull || cast_isNull || input_isNull1 ? (String) null : org.apache.calcite.runtime.SqlFunctions.substring(input_value, cast_value.intValue(), input_value1)
will bring correct result

As about other functions ... i check :
{noformat}ENDS_WITH, STARTS_WITH, STRPOS{noformat}  and seems there are tests for null literals exists and all correct there.



was (Author: zstan):
I additionally change PR.

Talking about SUBSTRING case:
0. select substring(s from i for l) from (values ('abc', null, 2)) as t (s, i, l); 
differ form 
select substring(s from i for l) from (values *('abc', 1, 2)*, ('abc', null, 2)) as t (s, i, l);
from the point of view of inferred params

1.  expr : _select SUBSTRING('text' FROM NULL FOR 2);_ 
type for NULL literal will not inferred [1]
[1] https://github.com/apache/calcite/blob/9c33d7aeefe082bf5f7be617ef231e1285418a6c/core/src/main/java/org/apache/calcite/sql/type/FamilyOperandTypeChecker.java#L88
thus we can`t reject NULL literals and generate null result from runtime, as i can see, so SqlTypeUtil.inSameFamilyOrNull is required

2.  expr : _select substring(s from i for l) from (values ('abc', null, 2)) as t (s, i, l);_
further call : FamilyOperandTypeChecker#checkOperandTypes and 
https://github.com/apache/calcite/blob/9c33d7aeefe082bf5f7be617ef231e1285418a6c/core/src/main/java/org/apache/calcite/sql/type/FamilyOperandTypeChecker.java#L138
will bring additional cast (main branch) and unsupported method signature as a result:
SUBSTRING(String, int, int) vs SUBSTRING(String, BigInteger, int)
SqlTypeFamily#getDefaultConcreteType ...
_case NUMERIC:
      return SqlTypeUtil.getMaxPrecisionScaleDecimal(factory);_
i change it in my PR and seems it correct, (i can rely only into my knowledge and tests:) ) and further runtime generation in :
AbstractRexCallImplementor#implement ->
AbstractRexCallImplementor#genValueStatement
input_isNull || cast_isNull || input_isNull1 ? (String) null : org.apache.calcite.runtime.SqlFunctions.substring(input_value, cast_value.intValue(), input_value1)
will bring correct result

As about other functions ... i check :
_ends_with, STRPOS_  and seems there are tests for null literals exists and all correct there.


> Change SUBSTRING result if either of parameters is NULL
> -------------------------------------------------------
>
>                 Key: CALCITE-5708
>                 URL: https://issues.apache.org/jira/browse/CALCITE-5708
>             Project: Calcite
>          Issue Type: Bug
>          Components: core
>    Affects Versions: 1.34.0
>            Reporter: Evgeny Stanilovsky
>            Assignee: Evgeny Stanilovsky
>            Priority: Major
>              Labels: patch-available, pull-request-available
>
> According to standard:
> {noformat}
> 6.18 <string value function>
> ...
> 3)If <character substring function> is specified, then:
> ...
> c) If either C, S, or L is the null value, then the result of the <character substring function> is
> the null value.
> {noformat}
> calcite not follow this rule for now.



--
This message was sent by Atlassian Jira
(v8.20.10#820010)