[
https://issues.apache.org/jira/browse/CALCITE-5154?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17537873#comment-17537873
]
xiong duan commented on CALCITE-5154:
-------------------------------------
[~julianhyde] Not all, CALCITE-5138 focus on addressing the normal sub-query,
not including the Correlate sub-query. For example:
{code:java}
select empno
from emp as r
left join dept as s on (select count(true) > 0 from emp)
where empno = 7369;{code}
> 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)
