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 <[email protected]> Sent: Sunday, January 14, 2024 11:57 PM To: [email protected] <[email protected]> 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 <[email protected]> > Sent: Friday, January 12, 2024 11:52 AM > To: [email protected] <[email protected]> > 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 <[email protected]> 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 <[email protected]> >> Sent: Friday, January 12, 2024 4:23 AM >> To: [email protected] <[email protected]> >> 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 !
