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

          Activity

            People

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

              Dates

                Created:
                Updated: