[ https://issues.apache.org/jira/browse/DRILL-4941?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15728518#comment-15728518 ]
Khurram Faraaz commented on DRILL-4941: --------------------------------------- {noformat} 0: jdbc:drill:schema=dfs.tmp> select NULLIF(-1,null) from (values(1)) as foo; Error: VALIDATION ERROR: class org.apache.calcite.sql.SqlLiteral: NULL SQL Query null [Error Id: fbf496c1-5021-4929-a2a7-e68b37ffdde1 on centos-01.qa.lab:31010] (state=,code=0) {noformat} Postgres returns -1 {noformat} postgres=# select NULLIF(-1,null) from (values(1)) as foo; nullif -------- -1 (1 row) {noformat} Stack trace from drillbit.log {noformat} 2016-12-07 11:15:55,269 [27b81093-a998-d593-3332-4cec6da4511e:foreman] INFO o.a.drill.exec.work.foreman.Foreman - Query text for query id 27b81093-a998-d593-3332-4cec6da4511e: select NULLIF(-1,null) from (values(1)) as foo 2016-12-07 11:15:55,285 [27b81093-a998-d593-3332-4cec6da4511e:foreman] INFO o.a.d.exec.planner.sql.SqlConverter - User Error Occurred org.apache.drill.common.exceptions.UserException: VALIDATION ERROR: class org.apache.calcite.sql.SqlLiteral: NULL SQL Query null [Error Id: fbf496c1-5021-4929-a2a7-e68b37ffdde1 ] at org.apache.drill.common.exceptions.UserException$Builder.build(UserException.java:543) ~[drill-common-1.9.0.jar:1.9.0] at org.apache.drill.exec.planner.sql.SqlConverter.validate(SqlConverter.java:178) [drill-java-exec-1.9.0.jar:1.9.0] at org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.validateNode(DefaultSqlHandler.java:622) [drill-java-exec-1.9.0.jar:1.9.0] at org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.validateAndConvert(DefaultSqlHandler.java:192) [drill-java-exec-1.9.0.jar:1.9.0] at org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.getPlan(DefaultSqlHandler.java:164) [drill-java-exec-1.9.0.jar:1.9.0] at org.apache.drill.exec.planner.sql.DrillSqlWorker.getPhysicalPlan(DrillSqlWorker.java:123) [drill-java-exec-1.9.0.jar:1.9.0] at org.apache.drill.exec.planner.sql.DrillSqlWorker.getPlan(DrillSqlWorker.java:97) [drill-java-exec-1.9.0.jar:1.9.0] at org.apache.drill.exec.work.foreman.Foreman.runSQL(Foreman.java:1008) [drill-java-exec-1.9.0.jar:1.9.0] at org.apache.drill.exec.work.foreman.Foreman.run(Foreman.java:264) [drill-java-exec-1.9.0.jar:1.9.0] at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) [na:1.8.0_91] at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) [na:1.8.0_91] at java.lang.Thread.run(Thread.java:745) [na:1.8.0_91] 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-r19.jar:1.4.0-drill-r19] at org.apache.calcite.sql.validate.SqlValidatorImpl.getValidatedNodeType(SqlValidatorImpl.java:1426) ~[calcite-core-1.4.0-drill-r19.jar:1.4.0-drill-r19] at org.apache.calcite.sql.SqlBinaryOperator.adjustType(SqlBinaryOperator.java:103) ~[calcite-core-1.4.0-drill-r19.jar:1.4.0-drill-r19] at org.apache.calcite.sql.SqlOperator.deriveType(SqlOperator.java:511) ~[calcite-core-1.4.0-drill-r19.jar:1.4.0-drill-r19] at org.apache.calcite.sql.SqlBinaryOperator.deriveType(SqlBinaryOperator.java:143) ~[calcite-core-1.4.0-drill-r19.jar:1.4.0-drill-r19] at org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:4337) ~[calcite-core-1.4.0-drill-r19.jar:1.4.0-drill-r19] at org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:4324) ~[calcite-core-1.4.0-drill-r19.jar:1.4.0-drill-r19] at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:130) ~[calcite-core-1.4.0-drill-r19.jar:1.4.0-drill-r19] at org.apache.calcite.sql.validate.SqlValidatorImpl.deriveTypeImpl(SqlValidatorImpl.java:1501) ~[calcite-core-1.4.0-drill-r19.jar:1.4.0-drill-r19] at org.apache.calcite.sql.validate.SqlValidatorImpl.deriveType(SqlValidatorImpl.java:1484) ~[calcite-core-1.4.0-drill-r19.jar:1.4.0-drill-r19] at org.apache.calcite.sql.fun.SqlCaseOperator.checkOperandTypes(SqlCaseOperator.java:178) ~[calcite-core-1.4.0-drill-r19.jar:1.4.0-drill-r19] at org.apache.calcite.sql.SqlOperator.validateOperands(SqlOperator.java:430) ~[calcite-core-1.4.0-drill-r19.jar:1.4.0-drill-r19] at org.apache.calcite.sql.fun.SqlCaseOperator.deriveType(SqlCaseOperator.java:164) ~[calcite-core-1.4.0-drill-r19.jar:1.4.0-drill-r19] at org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:4337) ~[calcite-core-1.4.0-drill-r19.jar:1.4.0-drill-r19] at org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:4324) ~[calcite-core-1.4.0-drill-r19.jar:1.4.0-drill-r19] at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:130) ~[calcite-core-1.4.0-drill-r19.jar:1.4.0-drill-r19] at org.apache.calcite.sql.validate.SqlValidatorImpl.deriveTypeImpl(SqlValidatorImpl.java:1501) ~[calcite-core-1.4.0-drill-r19.jar:1.4.0-drill-r19] at org.apache.calcite.sql.validate.SqlValidatorImpl.deriveType(SqlValidatorImpl.java:1484) ~[calcite-core-1.4.0-drill-r19.jar:1.4.0-drill-r19] at org.apache.calcite.sql.validate.SqlValidatorImpl.expandSelectItem(SqlValidatorImpl.java:446) ~[calcite-core-1.4.0-drill-r19.jar:1.4.0-drill-r19] at org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelectList(SqlValidatorImpl.java:3480) ~[calcite-core-1.4.0-drill-r19.jar:1.4.0-drill-r19] at org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect(SqlValidatorImpl.java:3023) ~[calcite-core-1.4.0-drill-r19.jar:1.4.0-drill-r19] at org.apache.calcite.sql.validate.SelectNamespace.validateImpl(SelectNamespace.java:60) ~[calcite-core-1.4.0-drill-r19.jar:1.4.0-drill-r19] at org.apache.calcite.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:86) ~[calcite-core-1.4.0-drill-r19.jar:1.4.0-drill-r19] at org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:883) ~[calcite-core-1.4.0-drill-r19.jar:1.4.0-drill-r19] at org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:869) ~[calcite-core-1.4.0-drill-r19.jar:1.4.0-drill-r19] at org.apache.calcite.sql.SqlSelect.validate(SqlSelect.java:210) ~[calcite-core-1.4.0-drill-r19.jar:1.4.0-drill-r19] at org.apache.calcite.sql.validate.SqlValidatorImpl.validateScopedExpression(SqlValidatorImpl.java:843) ~[calcite-core-1.4.0-drill-r19.jar:1.4.0-drill-r19] at org.apache.calcite.sql.validate.SqlValidatorImpl.validate(SqlValidatorImpl.java:557) ~[calcite-core-1.4.0-drill-r19.jar:1.4.0-drill-r19] at org.apache.drill.exec.planner.sql.SqlConverter.validate(SqlConverter.java:164) [drill-java-exec-1.9.0.jar:1.9.0] ... 10 common frames omitted {noformat} > 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)