Re: [Question] Derived type from string expression with NULL parameter

[stanilovsky evgeny]

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 !