[
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 9:06 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}LOWER, 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:
{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.
> 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)
