[ https://issues.apache.org/jira/browse/CALCITE-5154?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Vladimir Steshin updated CALCITE-5154: -------------------------------------- Description: Not the best example, I believe, but shows strange type checking: {code:sql} SELECT d.deptno FROM dept d INNER JOIN dept d2 ON (SELECT d.deptno=d2.deptno) {code} Or simplier {code:sql} SELECT d.deptno FROM dept d INNER JOIN dept d2 ON (SELECT d.deptno=1) {code} Works in Postgres at least. The problem is that RelDataType (isStruct() == true) of single boolean is not considered as Boolean. Whereas the condition actually says true and should work. {code:java} protected void validateWhereOrOn( SqlValidatorScope scope, SqlNode condition, String clause) { validateNoAggs(aggOrOverOrGroupFinder, condition, clause); inferUnknownTypes( booleanType, scope, condition); condition.validate(this, scope); final RelDataType type = deriveType(scope, condition); if (!SqlTypeUtil.inBooleanFamily(type)) { throw newValidationError(condition, RESOURCE.condMustBeBoolean(clause)); } } {code} I’ve tried to re-implement validateWhereOrOn(), or validateSelectList(), or deriveType(). To derive/cast type of namespace/condition to BasicSqlType(Boolean). But even for the recognized bool-clause, Calcite could fail further on different namespaces: {code:java} java.lang.AssertionError: All correlation variables should resolve to the same namespace. Prev ns=org.apache.calcite.sql.validate.IdentifierNamespace@3765a411, new ns=org.apache.calcite.sql.validate.IdentifierNamespace@58593307 at org.apache.calcite.sql2rel.SqlToRelConverter.getCorrelationUse(SqlToRelConverter.java:2867) at org.apache.calcite.sql2rel.SqlToRelConverter.createJoin(SqlToRelConverter.java:2777) at org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.register(SqlToRelConverter.java:4710) at org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.reRegister(SqlToRelConverter.java:4765) at org.apache.calcite.sql2rel.SqlToRelConverter.convertOnCondition(SqlToRelConverter.java:3112) at org.apache.calcite.sql2rel.SqlToRelConverter.convertJoin(SqlToRelConverter.java:3034) at org.apache.calcite.sql2rel.SqlToRelConverter.convertFrom(SqlToRelConverter.java:2245) at org.apache.calcite.sql2rel.SqlToRelConverter.convertFrom(SqlToRelConverter.java:2133) at org.apache.calcite.sql2rel.SqlToRelConverter.convertSelectImpl(SqlToRelConverter.java:683) at org.apache.calcite.sql2rel.SqlToRelConverter.convertSelect(SqlToRelConverter.java:664) at org.apache.calcite.sql2rel.SqlToRelConverter.convertQueryRecursive(SqlToRelConverter.java:3589) {code} Or, depending on the query, struct type could be strictly required instead of derived Boolean-BasicSqlType: {code:java} public static final SqlOperandTypeChecker RECORD_TO_SCALAR = new SqlSingleOperandTypeChecker() { @Override public boolean checkSingleOperandType( SqlCallBinding callBinding, SqlNode node, int iFormalOperand, boolean throwOnFailure) { assert 0 == iFormalOperand; RelDataType type = SqlTypeUtil.deriveType(callBinding, node); boolean validationError = false; if (!type.isStruct()) { validationError = true; } else if (type.getFieldList().size() != 1) { validationError = true; } if (validationError && throwOnFailure) { throw callBinding.newValidationSignatureError(); … {code} So, the type derivation seems not to be a solution. However, maybe there should be a way to re-implement, inherit or extend somehow this type checking. Like moving {code:java} SqlTypeUtil.inBooleanFamily() {code} into SqlValidator, or to TypeCoercion, or to RelDataTypeSystem and making it protected. was: Not the best example, I believe, but shows strange type checking: {code:sql} SELECT d.deptno FROM dept d INNER JOIN dept d2 ON (SELECT d.deptno=d2.deptno) {code} Or simplier {code:sql} SELECT d.deptno FROM dept d INNER JOIN dept d2 ON (SELECT d.deptno=1) {code} Works in Postgres at least. The problem is that RelDataType (isStruct() == true) of single boolean is not considered as Boolean. Whereas the condition actually says true and should work. {code:java} protected void validateWhereOrOn( SqlValidatorScope scope, SqlNode condition, String clause) { validateNoAggs(aggOrOverOrGroupFinder, condition, clause); inferUnknownTypes( booleanType, scope, condition); condition.validate(this, scope); final RelDataType type = deriveType(scope, condition); if (!SqlTypeUtil.inBooleanFamily(type)) { throw newValidationError(condition, RESOURCE.condMustBeBoolean(clause)); } } {code} I’ve tried to re-implement validateWhereOrOn(), or validateSelectList(), or deriveType(). To derive/cast type of namespace/condition to BasicSqlType(Boolean). But even for the recognized bool-clause, Calcite could fail further on different namespaces: {code:java} java.lang.AssertionError: All correlation variables should resolve to the same namespace. Prev ns=org.apache.calcite.sql.validate.IdentifierNamespace@3765a411, new ns=org.apache.calcite.sql.validate.IdentifierNamespace@58593307 at org.apache.calcite.sql2rel.SqlToRelConverter.getCorrelationUse(SqlToRelConverter.java:2867) at org.apache.calcite.sql2rel.SqlToRelConverter.createJoin(SqlToRelConverter.java:2777) at org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.register(SqlToRelConverter.java:4710) at org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.reRegister(SqlToRelConverter.java:4765) at org.apache.calcite.sql2rel.SqlToRelConverter.convertOnCondition(SqlToRelConverter.java:3112) at org.apache.calcite.sql2rel.SqlToRelConverter.convertJoin(SqlToRelConverter.java:3034) at org.apache.calcite.sql2rel.SqlToRelConverter.convertFrom(SqlToRelConverter.java:2245) at org.apache.calcite.sql2rel.SqlToRelConverter.convertFrom(SqlToRelConverter.java:2133) at org.apache.calcite.sql2rel.SqlToRelConverter.convertSelectImpl(SqlToRelConverter.java:683) at org.apache.calcite.sql2rel.SqlToRelConverter.convertSelect(SqlToRelConverter.java:664) at org.apache.calcite.sql2rel.SqlToRelConverter.convertQueryRecursive(SqlToRelConverter.java:3589) {code} Or, depending on the query, struct type could be strictly required instead of derived Boolean-BasicSqlType: {code:java} public static final SqlOperandTypeChecker RECORD_TO_SCALAR = new SqlSingleOperandTypeChecker() { @Override public boolean checkSingleOperandType( SqlCallBinding callBinding, SqlNode node, int iFormalOperand, boolean throwOnFailure) { assert 0 == iFormalOperand; RelDataType type = SqlTypeUtil.deriveType(callBinding, node); boolean validationError = false; if (!type.isStruct()) { validationError = true; } else if (type.getFieldList().size() != 1) { validationError = true; } if (validationError && throwOnFailure) { throw callBinding.newValidationSignatureError(); … {code} So, the type derivation seems not to be a solution.However, but there should be a way to re-implement, inherit or extend somehow this type checking. Like moving {code:java} SqlTypeUtil.inBooleanFamily() {code} into SqlValidator, or to TypeCoercion, or to RelDataTypeSystem and making it protected. > Boolean clause is not recognized as boolean in Join-On's subquery. > ------------------------------------------------------------------ > > Key: CALCITE-5154 > URL: https://issues.apache.org/jira/browse/CALCITE-5154 > Project: Calcite > Issue Type: Bug > Components: core > Affects Versions: 1.30.0 > Reporter: Vladimir Steshin > Priority: Minor > > Not the best example, I believe, but shows strange type checking: > {code:sql} > SELECT d.deptno FROM dept d INNER JOIN dept d2 ON (SELECT d.deptno=d2.deptno) > {code} > Or simplier > {code:sql} > SELECT d.deptno FROM dept d INNER JOIN dept d2 ON (SELECT d.deptno=1) > {code} > Works in Postgres at least. The problem is that RelDataType (isStruct() == > true) of single boolean is not considered as Boolean. Whereas the condition > actually says true and should work. > {code:java} > protected void validateWhereOrOn( > SqlValidatorScope scope, > SqlNode condition, > String clause) { > validateNoAggs(aggOrOverOrGroupFinder, condition, clause); > inferUnknownTypes( > booleanType, > scope, > condition); > condition.validate(this, scope); > final RelDataType type = deriveType(scope, condition); > if (!SqlTypeUtil.inBooleanFamily(type)) { > throw newValidationError(condition, RESOURCE.condMustBeBoolean(clause)); > } > } > {code} > I’ve tried to re-implement validateWhereOrOn(), or validateSelectList(), or > deriveType(). To derive/cast type of namespace/condition to > BasicSqlType(Boolean). But even for the recognized bool-clause, Calcite could > fail further on different namespaces: > {code:java} > java.lang.AssertionError: All correlation variables should resolve to the > same namespace. Prev > ns=org.apache.calcite.sql.validate.IdentifierNamespace@3765a411, new > ns=org.apache.calcite.sql.validate.IdentifierNamespace@58593307 > at > org.apache.calcite.sql2rel.SqlToRelConverter.getCorrelationUse(SqlToRelConverter.java:2867) > at > org.apache.calcite.sql2rel.SqlToRelConverter.createJoin(SqlToRelConverter.java:2777) > at > org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.register(SqlToRelConverter.java:4710) > at > org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.reRegister(SqlToRelConverter.java:4765) > at > org.apache.calcite.sql2rel.SqlToRelConverter.convertOnCondition(SqlToRelConverter.java:3112) > at > org.apache.calcite.sql2rel.SqlToRelConverter.convertJoin(SqlToRelConverter.java:3034) > at > org.apache.calcite.sql2rel.SqlToRelConverter.convertFrom(SqlToRelConverter.java:2245) > at > org.apache.calcite.sql2rel.SqlToRelConverter.convertFrom(SqlToRelConverter.java:2133) > at > org.apache.calcite.sql2rel.SqlToRelConverter.convertSelectImpl(SqlToRelConverter.java:683) > at > org.apache.calcite.sql2rel.SqlToRelConverter.convertSelect(SqlToRelConverter.java:664) > at > org.apache.calcite.sql2rel.SqlToRelConverter.convertQueryRecursive(SqlToRelConverter.java:3589) > {code} > Or, depending on the query, struct type could be strictly required instead of > derived Boolean-BasicSqlType: > {code:java} > public static final SqlOperandTypeChecker RECORD_TO_SCALAR = > new SqlSingleOperandTypeChecker() { > @Override public boolean checkSingleOperandType( > SqlCallBinding callBinding, > SqlNode node, > int iFormalOperand, > boolean throwOnFailure) { > assert 0 == iFormalOperand; > RelDataType type = SqlTypeUtil.deriveType(callBinding, node); > boolean validationError = false; > if (!type.isStruct()) { > validationError = true; > } else if (type.getFieldList().size() != 1) { > validationError = true; > } > if (validationError && throwOnFailure) { > throw callBinding.newValidationSignatureError(); > … > {code} > So, the type derivation seems not to be a solution. However, maybe there > should be a way to re-implement, inherit or extend somehow this type > checking. Like moving > {code:java} > SqlTypeUtil.inBooleanFamily() > {code} > into SqlValidator, or to TypeCoercion, or to RelDataTypeSystem and making it > protected. -- This message was sent by Atlassian Jira (v8.20.7#820007)