[
https://issues.apache.org/jira/browse/CALCITE-1531?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16819697#comment-16819697
]
Lai Zhou commented on CALCITE-1531:
-----------------------------------
[~julianhyde],
Considerin_g_ follow sql:
{code:java}
select if(1<0,0,null) as c1
{code}
this sql can work in Mysql and Hive , I think we can infer the type for the
Null operand of `if` SqlCall as long as one of it‘s non-condition operands is
not NULL.
The Null operand type will be DECIMAL here.
In Hive, the second and the third arguments of function IF should have the same
type.
But for another sql:
{code:java}
select if(1<0,null,null) as c1
{code}
the result type will be unknown , this case is not usual in real business, we
can ignore it.
I find a way to resolve the type of naked NULL literals from context :
add a abstract method to SqlOperator
{code:java}
RelDataType dynamicResolveNullLiteralOperandType(List<SqlNode> operands, int
nullLiteralOperandIndex);{code}
when inferring UnknownTypes for a SqlNode ,
[https://github.com/apache/calcite/blob/395aa85085e0dad1ba14d8761aeb79b2b11c3809/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java#L1777|https://github.com/apache/calcite/blob/395aa85085e0dad1ba14d8761aeb79b2b11c3809/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java#L1777]
if the operands of the SqlCall contains naked NULL literals , we can resolve
other operands first, and then resolve the naked NULL literals at the end.
> SqlValidatorException when boolean operators are used with NULL
> ---------------------------------------------------------------
>
> Key: CALCITE-1531
> URL: https://issues.apache.org/jira/browse/CALCITE-1531
> Project: Calcite
> Issue Type: Bug
> Reporter: Serhii Harnyk
> Assignee: Julian Hyde
> Priority: Major
> Fix For: 1.11.0
>
>
> SqlValidatorException when we use boolean AND, OR operators with null.
> {noformat}
> 0: jdbc:calcite:localhost> SELECT (CASE WHEN true or null then 1 else 0 end)
> from (VALUES(1));
> 2016-12-06 17:12:47,622 [main] ERROR -
> org.apache.calcite.sql.validate.SqlValidatorException: Illegal use of 'NULL'
> 2016-12-06 17:12:47,623 [main] ERROR -
> org.apache.calcite.runtime.CalciteContextException: From line 1, column 27 to
> line 1, column 30: Illegal use of 'NULL'
> Error: Error while executing SQL "SELECT (CASE WHEN true or null then 1 else
> 0 end) from (VALUES(1))": From line 1, column 27 to line 1, column 30:
> Illegal use of 'NULL' (state=,code=0)
> {noformat}
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
