[CALCITE-5154] Boolean clause is not recognized as boolean in Join-On's subquery.

• Type: Bug
• Status: Open
• Priority: Minor
• Resolution: Unresolved
• Affects Version/s: 1.30.0
• Component/s: core
• Assignee: Unassigned
• Reporter: Vladimir Steshin
• Created: 16/May/22 16:40
• Updated: 16/Jan/23 23:51

Description

Not the best example, I believe, but shows strange type checking:

SELECT d.deptno FROM dept d INNER JOIN dept d2 ON (SELECT d.deptno=d2.deptno)

Or simplier

SELECT d.deptno FROM dept d INNER JOIN dept d2 ON (SELECT d.deptno=1)

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.

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));
    }
  }

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:

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)

Or, depending on the query, struct type could be strictly required instead of derived Boolean-BasicSqlType:

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();
…

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

SqlTypeUtil.inBooleanFamily()

into SqlValidator, or to TypeCoercion, or to RelDataTypeSystem and making it protected.

Issue Links

• Dependency: IGNITE-15593 Calcite. Fail on inner join on non-equality subquery.
• Dependency: IGNITE-15609 Calcite. Error WHERE clause must be a condition.
• relates to: CALCITE-5138 Join on condition generates wrong plan when the condition is sub-query