[
https://issues.apache.org/jira/browse/CALCITE-5708?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17724423#comment-17724423
]
Julian Hyde edited comment on CALCITE-5708 at 5/19/23 9:54 PM:
---------------------------------------------------------------
I wouldn't call it coercion exactly. SQL has a problem in general with NULL
literals, because it can't in general infer the type. For example, 'SELECT NULL
FROM t' is invalid, but 'INSERT INTO t (c) VALUES (NULL)' is valid, because it
can infer the type.
I suspect that there's a general problem inferring the type of function
arguments, and I suspect that it's not limited to SUBSTRING. So I would like
you to clarify in the summary that this is about NULL literals, not NULL
values, and also to investigate whether there is a general problem. Does this
scenario work for other functions, and if so, how do they solve it? Does this
scenario fail for other functions, and if so, you should broaden this case to
cover those functions as well.
Dealing with untyped NULL literals is difficult to solve in general because of
overloading. {{SUBSTRING(NULL FROM 2 FOR 5)}} is ambiguous, because I believe
there are overloads of {{SUBSTRING}} that take {{VARCHAR}} and {{VARBINARY}}.
In that case, the validator should give a good message.
was (Author: julianhyde):
I wouldn't call it coercion exactly. SQL has a problem in general with NULL
literals, because it can't in general infer the type. For example, 'SELECT NULL
FROM t' is invalid, but 'INSERT INTO t (c) VALUES (NULL)' is valid, because it
can infer the type.
I suspect that there's a general problem inferring the type of function
arguments, and I suspect that it's not limited to SUBSTRING. So I would like
you to clarify in the summary that this is about NULL literals, not NULL
values, and also to investigate whether there is a general problem. Does this
scenario work for other functions, and if so, how do they solve it? Does this
scenario fail for other functions, and if so, you should broaden this case to
cover those functions as well.
> 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
>
> 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)
