[
https://issues.apache.org/jira/browse/CALCITE-5154?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Vladimir Steshin updated CALCITE-5154:
--------------------------------------
Description:
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.
was:
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, 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.
> 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)
