Yes, this is a bug. There are many, many such bugs in Calcite. For the bug
"UPPER should be able to deduce that its first argument should be a string” you
could replace ‘UPPER’ and ‘first’ and ‘string’ with hundreds of other values,
each of which is a bug, and each of which is a small amount of work to fix.
Contributions are most welcome. Hopefully we can get out of the way to allow
the contributions to land with the minimum of friction.
Julian
> On Jan 22, 2024, at 1:42 PM, Mihai Budiu <mbu...@gmail.com> wrote:
>
> 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 !
