Thinking about this a little more I have reached the conclusion that this is
indeed a bug in Calcite. But it does affect multiple functions, not just UPPER.
Mihai
________________________________
From: stanilovsky evgeny <estanilovs...@gridgain.com>
Sent: Tuesday, January 16, 2024 9:29 PM
To: dev@calcite.apache.org <dev@calcite.apache.org>
Subject: Re: [Question] Derived type from string expression with NULL parameter
got it, thanks for clarifications.
> There is no clear spec of what the type should be, so it's hard to say
> whether this is a bug. The NULL type that is inferred can be interpreted
> as correct given the context, that expression indeed will evaluate to
> NULL. It is more specific than VARCHAR, though. But if this function is
> wrong, many other functions are wrong.
>
> Mihai
> ________________________________
> From: stanilovsky evgeny <estanilovs...@gridgain.com>
> Sent: Sunday, January 14, 2024 11:57 PM
> To: dev@calcite.apache.org <dev@calcite.apache.org>
> Subject: Re: [Question] Derived type from string expression with NULL
> parameter
>
> Thanks for reply, so can i interpret it as a bug and fill an issue ?
>
>> So maybe the bug is in the fact that UPPER uses the constructor for
>> SqlFunction (well, almost a constructor) which doesn't supply an operand
>> type inference argument.
>>
>> Still, if the operand type checker requires a CHARACTER family, why is a
>> NULL literal always accepted?
>>
>> Mihai
>>
>> ________________________________
>> From: Julian Hyde <jhyde.apa...@gmail.com>
>> Sent: Friday, January 12, 2024 11:52 AM
>> To: dev@calcite.apache.org <dev@calcite.apache.org>
>> Subject: Re: [Question] Derived type from string expression with NULL
>> parameter
>>
>> OperandTypeChecker just makes sure that arguments of known type are
>> compatible; another interface, SqlOperandTypeInference, infers the types
>> of arguments of unknown type.
>>
>> Hopefully it’s now a little less mysterious.
>>
>>> On Jan 12, 2024, at 11:12 AM, Mihai Budiu <mbu...@gmail.com> wrote:
>>>
>>> inference in Calcite is still mysterious to me.
>>> Here it infers the type of the argument of UPPER to be NULL instead of
>>> VARCHAR.
>>> That's why the result type of UPPER is also NULL.
>>> The fact that UPPER has an OperandTypeChecker of
>>> "OperandTypes.CHARACTER" is completely ignored:
>>>
>>> public static final SqlFunction UPPER =
>>> SqlBasicFunction.create("UPPER",
>>> ReturnTypes.ARG0_NULLABLE,
>>> OperandTypes.CHARACTER,
>>> SqlFunctionCategory.STRING);
>>>
>>> Mihai
>>> ________________________________
>>> From: stanilovsky evgeny <estanilovs...@gridgain.com>
>>> Sent: Friday, January 12, 2024 4:23 AM
>>> To: dev@calcite.apache.org <dev@calcite.apache.org>
>>> Subject: [Question] Derived type from string expression with NULL
>>> parameter
>>>
>>> Plz help me to understand is it a bug ?
>>> From standard:
>>> 6.18 <string value function>
>>> ...cut...
>>> <fold> ::= { UPPER | LOWER } <left paren> <character value expression>
>>> <right paren>
>>>
>>> 6)If <fold> is specified, then:
>>> a) The declared type of the result of <fold> is the declared type of
>>> the
>>> !!!<character value expression>!!!.
>>>
>>> thus i expect that return type from: SELECT UPPER(null) will be VARCHAR
>>> but calcite show :
>>> SqlValidatorTest
>>>
>>> @Test void testTypeOfUpper() {
>>> sql("SELECT UPPER(NULL)")
>>> .columnType("???");
>>> }
>>>
>>> Expected: is "???"
>>> but: was "NULL"
>>>
>>> I think it need to be fixed ?
>>>
>>> Thanks !
