[ https://issues.apache.org/jira/browse/IGNITE-15609?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17516866#comment-17516866 ]
Vladimir Steshin edited comment on IGNITE-15609 at 4/4/22 2:42 PM: ------------------------------------------------------------------- The standard says BOOLEAN type accepds 'True' / 'False' values or NULL. PostgreSQL raises the same "_argument of WHERE must be type boolean, not type integer"_. Other engines might auto-convert. I think it is up to DB to convert to boolean or not. We could add to sql validator as example: {code:java} @Override protected void validateWhereOrOn(SqlValidatorScope scope, SqlNode condition, String clause) { if(condition.getKind() == SqlKind.SCALAR_QUERY && !SqlTypeUtil.inBooleanFamily(deriveType(scope, condition))){ SqlBasicCall selCall = (SqlBasicCall)condition; SqlNode desired = SqlStdOperatorTable.CAST.createCall( SqlParserPos.ZERO, selCall.operand(0), new SqlDataTypeSpec(new SqlBasicTypeNameSpec(SqlTypeName.BOOLEAN, SqlParserPos.ZERO), SqlParserPos.ZERO)); selCall.setOperand(0, desired); } super.validateWhereOrOn(scope, condition, clause); {code} But, on next validation, it falls into unmodifiable `SqlTypeUtil.canCastFrom(returnType, validatedNodeType, true)`: {code:java} Caused by: org.apache.calcite.runtime.CalciteContextException: From line 0, column 0 to line 1, column 37: Cast function cannot convert value of type RecordType(INTEGER 1) to type BOOLEAN at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:490) at org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:505) at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:932) at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:917) at org.apache.calcite.sql.validate.SqlValidatorImpl.newValidationError(SqlValidatorImpl.java:5266) at org.apache.calcite.sql.SqlCallBinding.newError(SqlCallBinding.java:374) at org.apache.calcite.sql.fun.SqlCastFunction.checkOperandTypes(SqlCastFunction.java:149) at org.apache.calcite.sql.SqlOperator.validateOperands(SqlOperator.java:499) at org.apache.calcite.sql.SqlFunction.deriveType(SqlFunction.java:335) at org.apache.calcite.sql.SqlFunction.deriveType(SqlFunction.java:231) at org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:6277) at org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:6264) at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:161) at org.apache.calcite.sql.validate.SqlValidatorImpl.deriveTypeImpl(SqlValidatorImpl.java:1862) at org.apache.calcite.sql.validate.SqlValidatorImpl.deriveType(SqlValidatorImpl.java:1847) at org.apache.calcite.sql.SqlNode.validateExpr(SqlNode.java:276) at org.apache.calcite.sql.SqlOperator.validateCall(SqlOperator.java:474) at org.apache.calcite.sql.validate.SqlValidatorImpl.validateCall(SqlValidatorImpl.java:5971) {code} Looks like calcite engine is in solidarity with PotgresSQL. was (Author: vladsz83): The standard says BOOLEAN type accepds 'True' / 'False' values or NULL. PostgreSQL raises the same "_argument of WHERE must be type boolean, not type integer"_. Other engines might auto-convert. I think it is up to DB to convert to boolean or not. We could add to sql validator as example: {code:java} @Override protected void validateWhereOrOn(SqlValidatorScope scope, SqlNode condition, String clause) { if(condition.getKind() == SqlKind.SCALAR_QUERY && !SqlTypeUtil.inBooleanFamily(deriveType(scope, condition))){ SqlBasicCall selCall = (SqlBasicCall)condition; SqlNode desired = SqlStdOperatorTable.CAST.createCall( SqlParserPos.ZERO, selCall.operand(0), new SqlDataTypeSpec(new SqlBasicTypeNameSpec(SqlTypeName.BOOLEAN, SqlParserPos.ZERO), SqlParserPos.ZERO)); selCall.setOperand(0, desired); } super.validateWhereOrOn(scope, condition, clause); {code:java} But, on next validation, it falls into unmodifiable `SqlTypeUtil.canCastFrom(returnType, validatedNodeType, true)`: {code:java} Caused by: org.apache.calcite.runtime.CalciteContextException: From line 0, column 0 to line 1, column 37: Cast function cannot convert value of type RecordType(INTEGER 1) to type BOOLEAN at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:490) at org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:505) at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:932) at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:917) at org.apache.calcite.sql.validate.SqlValidatorImpl.newValidationError(SqlValidatorImpl.java:5266) at org.apache.calcite.sql.SqlCallBinding.newError(SqlCallBinding.java:374) at org.apache.calcite.sql.fun.SqlCastFunction.checkOperandTypes(SqlCastFunction.java:149) at org.apache.calcite.sql.SqlOperator.validateOperands(SqlOperator.java:499) at org.apache.calcite.sql.SqlFunction.deriveType(SqlFunction.java:335) at org.apache.calcite.sql.SqlFunction.deriveType(SqlFunction.java:231) at org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:6277) at org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:6264) at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:161) at org.apache.calcite.sql.validate.SqlValidatorImpl.deriveTypeImpl(SqlValidatorImpl.java:1862) at org.apache.calcite.sql.validate.SqlValidatorImpl.deriveType(SqlValidatorImpl.java:1847) at org.apache.calcite.sql.SqlNode.validateExpr(SqlNode.java:276) at org.apache.calcite.sql.SqlOperator.validateCall(SqlOperator.java:474) at org.apache.calcite.sql.validate.SqlValidatorImpl.validateCall(SqlValidatorImpl.java:5971) {code} Looks like calcite engine is in solidarity with PotgresSQL. > Calcite. Error WHERE clause must be a condition. > ------------------------------------------------ > > Key: IGNITE-15609 > URL: https://issues.apache.org/jira/browse/IGNITE-15609 > Project: Ignite > Issue Type: Bug > Components: sql > Reporter: Evgeny Stanilovsky > Priority: Minor > Labels: calcite, calcite2-required, calcite3-required, ignite-3 > > {noformat} > statement ok > CREATE TABLE item(i_manufact INTEGER) > query I > SELECT * FROM item i1 WHERE (SELECT count(*) AS item_cnt FROM item WHERE > (i_manufact = i1.i_manufact AND i_manufact=3) OR (i_manufact = i1.i_manufact > AND i_manufact=3)) ORDER BY 1 LIMIT 100; > ---- > {noformat} > {noformat} > org.apache.calcite.runtime.CalciteContextException: From line 1, column 30 to > line 1, column 167: WHERE clause must be a condition > at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) > at > sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) > at > sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) > at java.lang.reflect.Constructor.newInstance(Constructor.java:423) > at > org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:506) > at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:917) > at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:902) > at > org.apache.calcite.sql.validate.SqlValidatorImpl.newValidationError(SqlValidatorImpl.java:5271) > at > org.apache.calcite.sql.validate.SqlValidatorImpl.validateWhereOrOn(SqlValidatorImpl.java:4350) > at > org.apache.calcite.sql.validate.SqlValidatorImpl.validateWhereClause(SqlValidatorImpl.java:4334) > {noformat} > {noformat} > /subquery/scalar/test_tpcds_correlated_subquery.test[_ignore] > {noformat} > tested with mysql, all ok there. -- This message was sent by Atlassian Jira (v8.20.1#820001)