[
https://issues.apache.org/jira/browse/CALCITE-1531?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15726646#comment-15726646
]
Julian Hyde commented on CALCITE-1531:
--------------------------------------
The SQL standard allows NULL values if you can determine the type, e.g.
{code}
INSERT INTO Emp (empno, commission) VALUES (100, NULL)
{code}
but not naked NULLs:
{code}
SELECT empno, NULL FROM Emp
{code}
You have to cast them:
{code}
SELECT empno, CAST(NULL AS INTEGER) From Emp
{code}
You may think that we should allow NULL in places where we can deduce the
intended type because there is only one overloaded operator that matches. This
would be the case with say
{code}
SELECT * FROM Emp
WHERE empno > 100 OR NULL
{code}
But the SQL standard doesn't allow it. Postgres goes beyond the standard.
Calcite goes beyond the standard in some cases, and I'd consider going beyond
it here. But to make a special case just for BOOLEAN doesn't make sense.
> 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
>
> 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
(v6.3.4#6332)
