[
https://issues.apache.org/jira/browse/DRILL-4941?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15728760#comment-15728760
]
Serhii Harnyk commented on DRILL-4941:
--------------------------------------
Julian Hyde mentioned:
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.
> UnsupportedOperationException : CASE WHEN true or null then 1 else 0 end
> ------------------------------------------------------------------------
>
> Key: DRILL-4941
> URL: https://issues.apache.org/jira/browse/DRILL-4941
> Project: Apache Drill
> Issue Type: Bug
> Components: Execution - Flow
> Reporter: Khurram Faraaz
> Assignee: Serhii Harnyk
> Fix For: 1.9.0
>
>
> Below case expression results in UnsupportedOperationException on Drill 1.9.0
> git commit ID: 4edabe7a
> {noformat}
> 0: jdbc:drill:schema=dfs.tmp> SELECT (CASE WHEN true or null then 1 else 0
> end) from (VALUES(1));
> Error: VALIDATION ERROR: class org.apache.calcite.sql.SqlLiteral: NULL
> SQL Query null
> [Error Id: 822ec7b0-3630-478c-b82a-0acedc39a560 on centos-01.qa.lab:31010]
> (state=,code=0)
> -- changing null to "not null" in the search condition causes Drill to return
> results
> 0: jdbc:drill:schema=dfs.tmp> SELECT (CASE WHEN true or not null then 1 else
> 0 end) from (VALUES(1));
> +---------+
> | EXPR$0 |
> +---------+
> | 1 |
> +---------+
> 1 row selected (0.11 seconds)
> {noformat}
> Stack trace from drillbit.log
> {noformat}
> Caused by: java.lang.UnsupportedOperationException: class
> org.apache.calcite.sql.SqlLiteral: NULL
> at org.apache.calcite.util.Util.needToImplement(Util.java:920)
> ~[calcite-core-1.4.0-drill-r18.jar:1.4.0-drill-r18]
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.getValidatedNodeType(SqlValidatorImpl.java:1426)
> ~[calcite-core-1.4.0-drill-r18.jar:1.4.0-drill-r18]
> at
> org.apache.calcite.sql.SqlBinaryOperator.adjustType(SqlBinaryOperator.java:103)
> ~[calcite-core-1.4.0-drill-r18.jar:1.4.0-drill-r18]
> at
> org.apache.calcite.sql.SqlOperator.deriveType(SqlOperator.java:511)
> ~[calcite-core-1.4.0-drill-r18.jar:1.4.0-drill-r18]
> at
> org.apache.calcite.sql.SqlBinaryOperator.deriveType(SqlBinaryOperator.java:143)
> ~[calcite-core-1.4.0-drill-r18.jar:1.4.0-drill-r18]
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:4337)
> ~[calcite-core-1.4.0-drill-r18.jar:1.4.0-drill-r18]
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:4324)
> ~[calcite-core-1.4.0-drill-r18.jar:1.4.0-drill-r18]
> at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:130)
> ~[calcite-core-1.4.0-drill-r18.jar:1.4.0-drill-r18]
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.deriveTypeImpl(SqlValidatorImpl.java:1501)
> ~[calcite-core-1.4.0-drill-r18.jar:1.4.0-drill-r18]
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.deriveType(SqlValidatorImpl.java:1484)
> ~[calcite-core-1.4.0-drill-r18.jar:1.4.0-drill-r18]
> at
> org.apache.calcite.sql.fun.SqlCaseOperator.checkOperandTypes(SqlCaseOperator.java:178)
> ~[calcite-core-1.4.0-drill-r18.jar:1.4.0-drill-r18]
> at
> org.apache.calcite.sql.SqlOperator.validateOperands(SqlOperator.java:430)
> ~[calcite-core-1.4.0-drill-r18.jar:1.4.0-drill-r18]
> at
> org.apache.calcite.sql.fun.SqlCaseOperator.deriveType(SqlCaseOperator.java:164)
> ~[calcite-core-1.4.0-drill-r18.jar:1.4.0-drill-r18]
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:4337)
> ~[calcite-core-1.4.0-drill-r18.jar:1.4.0-drill-r18]
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:4324)
> ~[calcite-core-1.4.0-drill-r18.jar:1.4.0-drill-r18]
> at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:130)
> ~[calcite-core-1.4.0-drill-r18.jar:1.4.0-drill-r18]
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.deriveTypeImpl(SqlValidatorImpl.java:1501)
> ~[calcite-core-1.4.0-drill-r18.jar:1.4.0-drill-r18]
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.deriveType(SqlValidatorImpl.java:1484)
> ~[calcite-core-1.4.0-drill-r18.jar:1.4.0-drill-r18]
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.expandSelectItem(SqlValidatorImpl.java:446)
> ~[calcite-core-1.4.0-drill-r18.jar:1.4.0-drill-r18]
> {noformat}
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
