You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@ignite.apache.org by "Vladimir Steshin (Jira)" <ji...@apache.org> on 2022/05/16 15:26:00 UTC
[jira] [Commented] (IGNITE-15593) Calcite. Fail on inner join on non-equality subquery.
[ https://issues.apache.org/jira/browse/IGNITE-15593?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17537602#comment-17537602 ]
Vladimir Steshin commented on IGNITE-15593:
-------------------------------------------
The case could be simplified:
{code:sql}
SELECT s1.i FROM integers s1 INNER JOIN integers s2 ON (SELECT s1.i=1)
{code}
This case is similar to IGNITE-15609 and some others. The trouble is in struct data type (RelDataType.isStruct()) and Calcite's hard-coded type checking in validateSelect() / validateWhereOn():
{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}
The record type only of one BOOLEAN param is not considered as bool in result. The type family is not boolean.
Possible solutions (IgniteSqlValidator):
{code:java}
/** {@inheritDoc} */
@Override
protected RelDataType validateSelectList(SqlNodeList selectItems, SqlSelect select, RelDataType targetRowType) {
RelDataType type = super.validateSelectList(selectItems, select, targetRowType);
if (type.isStruct() && type.getFieldCount() == 1)
return type.getFieldList().get(0).getType();
return type;
}
/** {@inheritDoc} */
@Override protected void validateWhereOrOn(SqlValidatorScope scope, SqlNode condition, String clause) {
final RelDataType type = deriveType(scope, condition);
if (!SqlTypeUtil.inBooleanFamily(type)) {
if (type.isStruct() && type.getFieldCount() == 1){
final SqlValidatorNamespace ns = getNamespace(condition);
ns.setType(type.getFieldList().get(0).getType());
setValidatedNodeType(condition, type.getFieldList().get(0).getType());
}
}
super.validateWhereOrOn(scope, condition, clause);
}
/** {@inheritDoc} */
@Override public RelDataType deriveType(SqlValidatorScope scope, SqlNode expr) {
RelDataType type = super.deriveType(scope, expr);
if (type.isStruct() && type.getFieldCount() == 1)
return type.getFieldList().get(0).getType();
return type;
}
{code}
But they can fix the issue partly. The following problems can arise even for recognized/derived BOOLEAn type:
{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)
at org.apache.ignite.internal.processors.query.calcite.prepare.IgniteSqlToRelConvertor.convertQueryRecursive(IgniteSqlToRelConvertor.java:70)
at org.apache.calcite.sql2rel.SqlToRelConverter.convertQuery(SqlToRelConverter.java:589)
at org.apache.ignite.internal.processors.query.calcite.prepare.IgnitePlanner.rel(IgnitePlanner.java:227)
at org.apache.ignite.internal.processors.query.calcite.prepare.PlannerHelper.optimize(PlannerHelper.java:67)
at org.apache.ignite.internal.processors.query.calcite.prepare.PrepareServiceImpl.prepareQuery(PrepareServiceImpl.java:152)
at org.apache.ignite.internal.processors.query.calcite.prepare.PrepareServiceImpl.prepareSingle(PrepareServiceImpl.java:88)
at org.apache.ignite.internal.processors.query.calcite.CalciteQueryProcessor.lambda$null$4(CalciteQueryProcessor.java:346)
at org.apache.ignite.internal.processors.query.calcite.prepare.QueryPlanCacheImpl.lambda$queryPlan$0(QueryPlanCacheImpl.java:80)
at java.base/java.util.concurrent.ConcurrentMap.computeIfAbsent(ConcurrentMap.java:330)
at org.apache.ignite.internal.processors.query.calcite.prepare.QueryPlanCacheImpl.queryPlan(QueryPlanCacheImpl.java:80)
at org.apache.ignite.internal.processors.query.calcite.CalciteQueryProcessor.lambda$query$5(CalciteQueryProcessor.java:344)
at org.apache.ignite.internal.processors.query.calcite.CalciteQueryProcessor.executeQuery(CalciteQueryProcessor.java:431)
{code}
or Calcite can fail in record-type checking (the type must be record, not simple sql type):
{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();
}
return !validationError;
}
...
{code}
I think we need some fix in Calcite to be able correctly check result type of select at least. And some fixes with correlates namespaces are probably required.
> Calcite. Fail on inner join on non-equality subquery.
> -----------------------------------------------------
>
> Key: IGNITE-15593
> URL: https://issues.apache.org/jira/browse/IGNITE-15593
> Project: Ignite
> Issue Type: Bug
> Components: sql
> Reporter: Evgeny Stanilovsky
> Priority: Major
> Labels: calcite, calcite2-required, calcite3-required, ignite-3
>
> {noformat}
> statement ok
> CREATE TABLE integers(i INTEGER)
> statement ok
> INSERT INTO integers VALUES (1), (2), (3), (NULL)
> query II
> SELECT * FROM integers s1 INNER JOIN integers s2 ON (SELECT s1.i=s2.i) ORDER BY s1.i;
> ----
> 1 1
> 2 2
> 3 3
> {noformat}
> {noformat}
> /subquery/scalar/test_complex_correlated_subquery.test[_ignore]
> {noformat}
> {noformat}
> org.apache.calcite.runtime.CalciteContextException: From line 1, column 54 to line 1, column 69: ON clause must be a condition
> at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
> at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
> at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
> at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
> at org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:506)
> at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:917)
> at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:902)
> {noformat}
--
This message was sent by Atlassian Jira
(v8.20.7#820007)