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

["Vladimir Steshin (Jira)" <ji...@apache.org>]

Vladimir Steshin created CALCITE-5154:
-----------------------------------------

             Summary: 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


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. Not sure, 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.





--
This message was sent by Atlassian Jira
(v8.20.7#820007)