[ https://issues.apache.org/jira/browse/DRILL-4400?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15156144#comment-15156144 ]
N Campbell commented on DRILL-4400: ----------------------------------- Hi, did not check current ISO spec but in terms of vendors ORACLE -> https://docs.oracle.com/database/121/SQLRF/conditions001.htm#SQLRF52103 SQL Server -> https://msdn.microsoft.com/en-us/library/ms190276.aspx Postgres -> http://www.postgresql.org/docs/9.5/static/sql-syntax-lexical.html#SQL-PRECEDENCE-TABLE Netezza -> https://www-01.ibm.com/support/knowledgecenter/SSULQD_7.2.0/com.ibm.nz.dbu.doc/r_dbuser_functions_expressions_operators.html Cheers > Cannot apply 'NOT' to arguments of type 'NOT<CHAR(2)>'. > ------------------------------------------------------- > > Key: DRILL-4400 > URL: https://issues.apache.org/jira/browse/DRILL-4400 > Project: Apache Drill > Issue Type: Bug > Components: Server > Affects Versions: 1.5.0 > Reporter: N Campbell > > select tjoin2.rnum, tjoin1.c1, tjoin1.c2, tjoin2.c2 as c2j2 from > postgres.public.tjoin1 inner join postgres.public.tjoin2 on ( tjoin1.c1 = > tjoin2.c1 and not tjoin2.c2 = 'AA' ) > Error: VALIDATION ERROR: From line 1, column 154 to line 1, column 163: > Cannot apply 'NOT' to arguments of type 'NOT<CHAR(2)>'. Supported form(s): > 'NOT<BOOLEAN>' > [Error Id: f781f07a-2361-4f3d-8f03-0a3f1ddec8f0 on centos1:31010] > (org.apache.calcite.tools.ValidationException) > org.apache.calcite.runtime.CalciteContextException: From line 1, column 154 > to line 1, column 163: Cannot apply 'NOT' to arguments of type > 'NOT<CHAR(2)>'. Supported form(s): 'NOT<BOOLEAN>' > org.apache.calcite.prepare.PlannerImpl.validate():189 > org.apache.calcite.prepare.PlannerImpl.validateAndGetType():198 > > org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.validateNode():451 > > org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.validateAndConvert():198 > org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.getPlan():167 > org.apache.drill.exec.planner.sql.DrillSqlWorker.getPlan():199 > org.apache.drill.exec.work.foreman.Foreman.runSQL():924 > org.apache.drill.exec.work.foreman.Foreman.run():250 > java.util.concurrent.ThreadPoolExecutor.runWorker():1142 > java.util.concurrent.ThreadPoolExecutor$Worker.run():617 > java.lang.Thread.run():745 > Caused By (org.apache.calcite.runtime.CalciteContextException) From line 1, > column 154 to line 1, column 163: Cannot apply 'NOT' to arguments of type > 'NOT<CHAR(2)>'. Supported form(s): 'NOT<BOOLEAN>' > sun.reflect.GeneratedConstructorAccessor66.newInstance():-1 > sun.reflect.DelegatingConstructorAccessorImpl.newInstance():45 > java.lang.reflect.Constructor.newInstance():422 > org.apache.calcite.runtime.Resources$ExInstWithCause.ex():405 > org.apache.calcite.sql.SqlUtil.newContextException():714 > org.apache.calcite.sql.SqlUtil.newContextException():702 > org.apache.calcite.sql.validate.SqlValidatorImpl.newValidationError():3931 > org.apache.calcite.sql.SqlCallBinding.newValidationSignatureError():275 > > org.apache.calcite.sql.type.FamilyOperandTypeChecker.checkSingleOperandType():92 > > org.apache.calcite.sql.type.FamilyOperandTypeChecker.checkOperandTypes():109 > org.apache.calcite.sql.SqlOperator.checkOperandTypes():563 > org.apache.calcite.sql.SqlOperator.validateOperands():420 > org.apache.calcite.sql.SqlOperator.deriveType():487 > > org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit():4268 > > org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit():4255 > org.apache.calcite.sql.SqlCall.accept():130 > org.apache.calcite.sql.validate.SqlValidatorImpl.deriveTypeImpl():1495 > org.apache.calcite.sql.validate.SqlValidatorImpl.deriveType():1478 > org.apache.calcite.sql.type.InferTypes$1.inferOperandTypes():51 > org.apache.calcite.sql.validate.SqlValidatorImpl.inferUnknownTypes():1672 > org.apache.calcite.sql.validate.SqlValidatorImpl.inferUnknownTypes():1678 > org.apache.calcite.sql.validate.SqlValidatorImpl.validateWhereOrOn():3370 > org.apache.calcite.sql.validate.SqlValidatorImpl.validateJoin():2814 > org.apache.calcite.sql.validate.SqlValidatorImpl.validateFrom():2772 > org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect():2986 > org.apache.calcite.sql.validate.SelectNamespace.validateImpl():60 > org.apache.calcite.sql.validate.AbstractNamespace.validate():86 > org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace():877 > org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery():863 > org.apache.calcite.sql.SqlSelect.validate():210 > > org.apache.calcite.sql.validate.SqlValidatorImpl.validateScopedExpression():837 > org.apache.calcite.sql.validate.SqlValidatorImpl.validate():551 > org.apache.calcite.prepare.PlannerImpl.validate():187 > org.apache.calcite.prepare.PlannerImpl.validateAndGetType():198 > > org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.validateNode():451 > > org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.validateAndConvert():198 > org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.getPlan():167 > org.apache.drill.exec.planner.sql.DrillSqlWorker.getPlan():199 > org.apache.drill.exec.work.foreman.Foreman.runSQL():924 > org.apache.drill.exec.work.foreman.Foreman.run():250 > java.util.concurrent.ThreadPoolExecutor.runWorker():1142 > java.util.concurrent.ThreadPoolExecutor$Worker.run():617 > java.lang.Thread.run():745 > Caused By (org.apache.calcite.sql.validate.SqlValidatorException) Cannot > apply 'NOT' to arguments of type 'NOT<CHAR(2)>'. Supported form(s): > 'NOT<BOOLEAN>' > sun.reflect.GeneratedConstructorAccessor65.newInstance():-1 > sun.reflect.DelegatingConstructorAccessorImpl.newInstance():45 > java.lang.reflect.Constructor.newInstance():422 > org.apache.calcite.runtime.Resources$ExInstWithCause.ex():405 > org.apache.calcite.runtime.Resources$ExInst.ex():514 > org.apache.calcite.sql.SqlUtil.newContextException():714 > org.apache.calcite.sql.SqlUtil.newContextException():702 > org.apache.calcite.sql.validate.SqlValidatorImpl.newValidationError():3931 > org.apache.calcite.sql.SqlCallBinding.newValidationSignatureError():275 > > org.apache.calcite.sql.type.FamilyOperandTypeChecker.checkSingleOperandType():92 > > org.apache.calcite.sql.type.FamilyOperandTypeChecker.checkOperandTypes():109 > org.apache.calcite.sql.SqlOperator.checkOperandTypes():563 > org.apache.calcite.sql.SqlOperator.validateOperands():420 > org.apache.calcite.sql.SqlOperator.deriveType():487 > > org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit():4268 > > org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit():4255 > org.apache.calcite.sql.SqlCall.accept():130 > org.apache.calcite.sql.validate.SqlValidatorImpl.deriveTypeImpl():1495 > org.apache.calcite.sql.validate.SqlValidatorImpl.deriveType():1478 > org.apache.calcite.sql.type.InferTypes$1.inferOperandTypes():51 > org.apache.calcite.sql.validate.SqlValidatorImpl.inferUnknownTypes():1672 > org.apache.calcite.sql.validate.SqlValidatorImpl.inferUnknownTypes():1678 > org.apache.calcite.sql.validate.SqlValidatorImpl.validateWhereOrOn():3370 > org.apache.calcite.sql.validate.SqlValidatorImpl.validateJoin():2814 > org.apache.calcite.sql.validate.SqlValidatorImpl.validateFrom():2772 > org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect():2986 > org.apache.calcite.sql.validate.SelectNamespace.validateImpl():60 > org.apache.calcite.sql.validate.AbstractNamespace.validate():86 > org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace():877 > org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery():863 > org.apache.calcite.sql.SqlSelect.validate():210 > > org.apache.calcite.sql.validate.SqlValidatorImpl.validateScopedExpression():837 > org.apache.calcite.sql.validate.SqlValidatorImpl.validate():551 > org.apache.calcite.prepare.PlannerImpl.validate():187 > org.apache.calcite.prepare.PlannerImpl.validateAndGetType():198 > > org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.validateNode():451 > > org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.validateAndConvert():198 > org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.getPlan():167 > org.apache.drill.exec.planner.sql.DrillSqlWorker.getPlan():199 > org.apache.drill.exec.work.foreman.Foreman.runSQL():924 > org.apache.drill.exec.work.foreman.Foreman.run():250 > java.util.concurrent.ThreadPoolExecutor.runWorker():1142 > java.util.concurrent.ThreadPoolExecutor$Worker.run():617 > java.lang.Thread.run():745 > SQLState: null > ErrorCode: 0 > create table TJOIN1 (RNUM integer not null , C1 integer, C2 integer); > insert into TJOIN1 (RNUM, C1, C2) values ( 0, 10, 15); > insert into TJOIN1 (RNUM, C1, C2) values ( 1, 20, 25); > insert into TJOIN1 (RNUM, C1, C2) values ( 2, NULL, 50); > create table TJOIN2 (RNUM integer not null , C1 integer, C2 char(2)); > insert into TJOIN2 (RNUM, C1, C2) values ( 0, 10, 'BB'); > insert into TJOIN2 (RNUM, C1, C2) values ( 1, 15, 'DD'); > insert into TJOIN2 (RNUM, C1, C2) values ( 2, NULL, 'EE'); > insert into TJOIN2 (RNUM, C1, C2) values ( 3, 10, 'FF'); -- This message was sent by Atlassian JIRA (v6.3.4#6332)