Uploaded image for project: 'Calcite'
  1. Calcite
  2. CALCITE-5154

Boolean clause is not recognized as boolean in Join-On's subquery.

    XMLWordPrintableJSON

Details

    • Bug
    • Status: Open
    • Minor
    • Resolution: Unresolved
    • 1.30.0
    • None
    • core
    • None

    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.

      Attachments

        Issue Links

          Dependency

          Bug - A problem which impairs or prevents the functions of the product. IGNITE-15593 Calcite. Fail on inner join on non-equality subquery.

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. IGNITE-15609 Calcite. Error WHERE clause must be a condition.

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Resolved
          relates to

          Bug - A problem which impairs or prevents the functions of the product. CALCITE-5138 Join on condition generates wrong plan when the condition is sub-query

          • Major - Major loss of function.
          • Closed

          Activity

            People

              Unassigned Unassigned
              vladsz83 Vladimir Steshin
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

                Created:
                Updated: