You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@ignite.apache.org by zs...@apache.org on 2022/02/02 07:22:30 UTC
[ignite-3] branch main updated: IGNITE-16347 Sql. Support for function TYPEOF. Incorrect plan provided for queries with correlated subquery in project list. - Fixes #579.
This is an automated email from the ASF dual-hosted git repository.
zstan pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/ignite-3.git
The following commit(s) were added to refs/heads/main by this push:
new f854f55 IGNITE-16347 Sql. Support for function TYPEOF. Incorrect plan provided for queries with correlated subquery in project list. - Fixes #579.
f854f55 is described below
commit f854f556f896f05d43a1032013cddd90681a9df6
Author: zstan <st...@gmail.com>
AuthorDate: Wed Feb 2 10:21:24 2022 +0300
IGNITE-16347 Sql. Support for function TYPEOF. Incorrect plan provided for queries with correlated subquery in project list. - Fixes #579.
Adopt IGNITE-15518, IGNITE-14596
Signed-off-by: zstan <st...@gmail.com>
---
.../internal/sql/engine/ItFunctionsTest.java | 26 ++++++-
.../internal/sql/engine/exec/exp/RexImpTable.java | 7 ++
.../internal/sql/engine/prepare/IgnitePlanner.java | 5 +-
.../internal/sql/engine/prepare/PlannerHelper.java | 2 +
.../sql/engine/sql/fun/IgniteSqlOperatorTable.java | 9 +++
.../sql/engine/planner/AbstractPlannerTest.java | 31 +++++++++
.../planner/CorrelatedSubqueryPlannerTest.java | 80 ++++++++++++++++++++++
.../internal/sql/engine/planner/PlannerTest.java | 4 +-
8 files changed, 157 insertions(+), 7 deletions(-)
diff --git a/modules/runner/src/integrationTest/java/org/apache/ignite/internal/sql/engine/ItFunctionsTest.java b/modules/runner/src/integrationTest/java/org/apache/ignite/internal/sql/engine/ItFunctionsTest.java
index 4cd013c..fa00aa3 100644
--- a/modules/runner/src/integrationTest/java/org/apache/ignite/internal/sql/engine/ItFunctionsTest.java
+++ b/modules/runner/src/integrationTest/java/org/apache/ignite/internal/sql/engine/ItFunctionsTest.java
@@ -26,7 +26,9 @@ import java.sql.Time;
import java.sql.Timestamp;
import java.util.List;
import java.util.function.LongFunction;
+import org.apache.calcite.sql.validate.SqlValidatorException;
import org.apache.ignite.internal.schema.configuration.SchemaConfigurationConverter;
+import org.apache.ignite.internal.testframework.IgniteTestUtils;
import org.apache.ignite.lang.IgniteException;
import org.apache.ignite.lang.IgniteStringFormatter;
import org.apache.ignite.schema.SchemaBuilders;
@@ -121,7 +123,7 @@ public class ItFunctionsTest extends AbstractBasicIntegrationTest {
assertEquals(0, sql("SELECT * FROM table(system_range(null, 1))").size());
IgniteException ex = assertThrows(IgniteException.class,
- () -> sql("SELECT * FROM table(system_range(1, 1, 0))"));
+ () -> sql("SELECT * FROM table(system_range(1, 1, 0))"), "Increment can't be 0");
assertTrue(
ex.getCause() instanceof IllegalArgumentException,
@@ -267,4 +269,26 @@ public class ItFunctionsTest extends AbstractBasicIntegrationTest {
assertQuery("SELECT null !~* null").returns(NULL_RESULT).check();
assertThrows(IgniteException.class, () -> sql("SELECT 'abcd' ~ '[a-z'"));
}
+
+ @Test
+ public void testTypeOf() {
+ assertQuery("SELECT TYPEOF(1)").returns("INTEGER").check();
+ assertQuery("SELECT TYPEOF(1.1::DOUBLE)").returns("DOUBLE").check();
+ assertQuery("SELECT TYPEOF(1.1::DECIMAL(3, 2))").returns("DECIMAL(3, 2)").check();
+ assertQuery("SELECT TYPEOF('a')").returns("CHAR(1)").check();
+ assertQuery("SELECT TYPEOF('a'::varchar(1))").returns("VARCHAR(1)").check();
+ assertQuery("SELECT TYPEOF(NULL)").returns("NULL").check();
+ assertQuery("SELECT TYPEOF(NULL::VARCHAR(100))").returns("VARCHAR(100)").check();
+ try {
+ sql("SELECT TYPEOF()");
+ } catch (Throwable e) {
+ assertTrue(IgniteTestUtils.hasCause(e, SqlValidatorException.class, "Invalid number of arguments"));
+ }
+
+ try {
+ sql("SELECT TYPEOF(1, 2)");
+ } catch (Throwable e) {
+ assertTrue(IgniteTestUtils.hasCause(e, SqlValidatorException.class, "Invalid number of arguments"));
+ }
+ }
}
diff --git a/modules/sql-engine/src/main/java/org/apache/ignite/internal/sql/engine/exec/exp/RexImpTable.java b/modules/sql-engine/src/main/java/org/apache/ignite/internal/sql/engine/exec/exp/RexImpTable.java
index d93f59a..dbd52f4 100644
--- a/modules/sql-engine/src/main/java/org/apache/ignite/internal/sql/engine/exec/exp/RexImpTable.java
+++ b/modules/sql-engine/src/main/java/org/apache/ignite/internal/sql/engine/exec/exp/RexImpTable.java
@@ -195,6 +195,7 @@ import static org.apache.calcite.sql.fun.SqlStdOperatorTable.UPPER;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.USER;
import static org.apache.ignite.internal.sql.engine.sql.fun.IgniteSqlOperatorTable.LENGTH;
import static org.apache.ignite.internal.sql.engine.sql.fun.IgniteSqlOperatorTable.SYSTEM_RANGE;
+import static org.apache.ignite.internal.sql.engine.sql.fun.IgniteSqlOperatorTable.TYPEOF;
import java.lang.reflect.Constructor;
import java.lang.reflect.InvocationTargetException;
@@ -549,6 +550,8 @@ public class RexImpTable {
map.put(CURRENT_DATE, systemFunctionImplementor);
map.put(LOCALTIME, systemFunctionImplementor);
map.put(LOCALTIMESTAMP, systemFunctionImplementor);
+
+ map.put(TYPEOF, systemFunctionImplementor);
}
private <T> Supplier<T> constructorSupplier(Class<T> klass) {
@@ -1721,6 +1724,10 @@ public class RexImpTable {
return createTableFunctionImplementor(IgniteMethod.SYSTEM_RANGE3.method())
.implement(translator, call, NullAs.NULL);
}
+ } else if (op == TYPEOF) {
+ assert call.getOperands().size() == 1 : call.getOperands();
+
+ return Expressions.constant(call.getOperands().get(0).getType().toString());
}
throw new AssertionError("unknown function " + op);
diff --git a/modules/sql-engine/src/main/java/org/apache/ignite/internal/sql/engine/prepare/IgnitePlanner.java b/modules/sql-engine/src/main/java/org/apache/ignite/internal/sql/engine/prepare/IgnitePlanner.java
index a4a9290..1e639a3 100644
--- a/modules/sql-engine/src/main/java/org/apache/ignite/internal/sql/engine/prepare/IgnitePlanner.java
+++ b/modules/sql-engine/src/main/java/org/apache/ignite/internal/sql/engine/prepare/IgnitePlanner.java
@@ -207,11 +207,8 @@ public class IgnitePlanner implements Planner, RelOptTable.ViewExpander {
@Override
public RelRoot rel(SqlNode sql) {
SqlToRelConverter sqlToRelConverter = sqlToRelConverter(validator(), catalogReader, sqlToRelConverterCfg);
- RelRoot root = sqlToRelConverter.convertQuery(sql, false, true);
- root = root.withRel(sqlToRelConverter.decorrelate(sql, root.rel));
-
- return root;
+ return sqlToRelConverter.convertQuery(sql, false, true);
}
/** {@inheritDoc} */
diff --git a/modules/sql-engine/src/main/java/org/apache/ignite/internal/sql/engine/prepare/PlannerHelper.java b/modules/sql-engine/src/main/java/org/apache/ignite/internal/sql/engine/prepare/PlannerHelper.java
index af6a596..3752095 100644
--- a/modules/sql-engine/src/main/java/org/apache/ignite/internal/sql/engine/prepare/PlannerHelper.java
+++ b/modules/sql-engine/src/main/java/org/apache/ignite/internal/sql/engine/prepare/PlannerHelper.java
@@ -85,6 +85,8 @@ public class PlannerHelper {
// Transformation chain
rel = planner.transform(PlannerPhase.HEP_DECORRELATE, rel.getTraitSet(), rel);
+ rel = planner.trimUnusedFields(root.withRel(rel)).rel;
+
rel = planner.transform(PlannerPhase.HEP_FILTER_PUSH_DOWN, rel.getTraitSet(), rel);
rel = planner.transform(PlannerPhase.HEP_PROJECT_PUSH_DOWN, rel.getTraitSet(), rel);
diff --git a/modules/sql-engine/src/main/java/org/apache/ignite/internal/sql/engine/sql/fun/IgniteSqlOperatorTable.java b/modules/sql-engine/src/main/java/org/apache/ignite/internal/sql/engine/sql/fun/IgniteSqlOperatorTable.java
index 9b6c9c6..51d0f17 100644
--- a/modules/sql-engine/src/main/java/org/apache/ignite/internal/sql/engine/sql/fun/IgniteSqlOperatorTable.java
+++ b/modules/sql-engine/src/main/java/org/apache/ignite/internal/sql/engine/sql/fun/IgniteSqlOperatorTable.java
@@ -44,6 +44,15 @@ public class IgniteSqlOperatorTable extends ReflectiveSqlOperatorTable {
public static final SqlFunction SYSTEM_RANGE = new SqlSystemRangeFunction();
+ public static final SqlFunction TYPEOF =
+ new SqlFunction(
+ "TYPEOF",
+ SqlKind.OTHER_FUNCTION,
+ ReturnTypes.VARCHAR_2000,
+ null,
+ OperandTypes.ANY,
+ SqlFunctionCategory.SYSTEM);
+
/**
* Returns the Ignite operator table, creating it if necessary.
*/
diff --git a/modules/sql-engine/src/test/java/org/apache/ignite/internal/sql/engine/planner/AbstractPlannerTest.java b/modules/sql-engine/src/test/java/org/apache/ignite/internal/sql/engine/planner/AbstractPlannerTest.java
index 7df6c4f..e002b1c 100644
--- a/modules/sql-engine/src/test/java/org/apache/ignite/internal/sql/engine/planner/AbstractPlannerTest.java
+++ b/modules/sql-engine/src/test/java/org/apache/ignite/internal/sql/engine/planner/AbstractPlannerTest.java
@@ -58,6 +58,7 @@ import org.apache.calcite.rel.type.RelDataTypeFactory;
import org.apache.calcite.rel.type.RelDataTypeField;
import org.apache.calcite.rel.type.RelDataTypeImpl;
import org.apache.calcite.rel.type.RelProtoDataType;
+import org.apache.calcite.rex.RexCall;
import org.apache.calcite.rex.RexNode;
import org.apache.calcite.schema.ColumnStrategy;
import org.apache.calcite.schema.Schema;
@@ -232,6 +233,36 @@ public abstract class AbstractPlannerTest extends IgniteAbstractTest {
}
/**
+ * Returns the first found node of given class from expression tree.
+ *
+ * @param expr Expression to search in.
+ * @param clz Target class of the node we are looking for.
+ * @param <T> Type of the node we are looking for.
+ * @return the first matching node in terms of DFS or null if there is no such node.
+ */
+ public static <T> T findFirst(RexNode expr, Class<T> clz) {
+ if (clz.isAssignableFrom(expr.getClass())) {
+ return clz.cast(expr);
+ }
+
+ if (!(expr instanceof RexCall)) {
+ return null;
+ }
+
+ RexCall call = (RexCall) expr;
+
+ for (RexNode op : call.getOperands()) {
+ T res = findFirst(op, clz);
+
+ if (res != null) {
+ return res;
+ }
+ }
+
+ return null;
+ }
+
+ /**
* Optimize the specified query and build query physical plan for a test.
*/
protected IgniteRel physicalPlan(String sql, IgniteSchema publicSchema, String... disabledRules) throws Exception {
diff --git a/modules/sql-engine/src/test/java/org/apache/ignite/internal/sql/engine/planner/CorrelatedSubqueryPlannerTest.java b/modules/sql-engine/src/test/java/org/apache/ignite/internal/sql/engine/planner/CorrelatedSubqueryPlannerTest.java
new file mode 100644
index 0000000..664fdd5
--- /dev/null
+++ b/modules/sql-engine/src/test/java/org/apache/ignite/internal/sql/engine/planner/CorrelatedSubqueryPlannerTest.java
@@ -0,0 +1,80 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to you under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.ignite.internal.sql.engine.planner;
+
+import static org.junit.jupiter.api.Assertions.assertEquals;
+import static org.junit.jupiter.api.Assertions.assertNotNull;
+
+import org.apache.calcite.rel.core.Filter;
+import org.apache.calcite.rex.RexCorrelVariable;
+import org.apache.calcite.rex.RexFieldAccess;
+import org.apache.ignite.internal.sql.engine.rel.IgniteCorrelatedNestedLoopJoin;
+import org.apache.ignite.internal.sql.engine.rel.IgniteFilter;
+import org.apache.ignite.internal.sql.engine.rel.IgniteRel;
+import org.apache.ignite.internal.sql.engine.schema.IgniteSchema;
+import org.apache.ignite.internal.sql.engine.trait.IgniteDistributions;
+import org.apache.ignite.internal.sql.engine.util.RexUtils;
+import org.junit.jupiter.api.Test;
+
+/** Tests to verify correlated subquery planning. */
+public class CorrelatedSubqueryPlannerTest extends AbstractPlannerTest {
+ /**
+ * Test verifies the row type is consistent for correlation variable and
+ * node that actually puts this variable into a context.
+ *
+ * <p>In this particular test the row type of the left input of CNLJ node should
+ * match the row type correlated variable in the filter was created with.
+ *
+ * @throws Exception In case of any unexpected error.
+ */
+ @Test
+ public void test() throws Exception {
+ IgniteSchema schema = createSchema(
+ createTable("T1", IgniteDistributions.single(), "A", Integer.class,
+ "B", Integer.class, "C", Integer.class, "D", Integer.class, "E", Integer.class)
+ );
+
+ String sql = ""
+ + "SELECT (SELECT count(*) FROM t1 AS x WHERE x.b<t1.b)\n"
+ + " FROM t1\n"
+ + " WHERE (a>e-2 AND a<e+2)\n"
+ + " OR c>d\n"
+ + " ORDER BY 1;";
+
+ IgniteRel rel = physicalPlan(sql, schema, "FilterTableScanMergeRule");
+
+ IgniteFilter filter = findFirstNode(rel, byClass(IgniteFilter.class)
+ .and(f -> RexUtils.hasCorrelation(((Filter) f).getCondition())));
+
+ RexFieldAccess fieldAccess = findFirst(filter.getCondition(), RexFieldAccess.class);
+
+ assertNotNull(fieldAccess);
+ assertEquals(
+ RexCorrelVariable.class,
+ fieldAccess.getReferenceExpr().getClass()
+ );
+
+ IgniteCorrelatedNestedLoopJoin join = findFirstNode(rel, byClass(IgniteCorrelatedNestedLoopJoin.class));
+
+ assertEquals(
+ fieldAccess.getReferenceExpr().getType(),
+ join.getLeft().getRowType()
+ );
+ }
+}
+
diff --git a/modules/sql-engine/src/test/java/org/apache/ignite/internal/sql/engine/planner/PlannerTest.java b/modules/sql-engine/src/test/java/org/apache/ignite/internal/sql/engine/planner/PlannerTest.java
index 2a8ee95..bd5f40c 100644
--- a/modules/sql-engine/src/test/java/org/apache/ignite/internal/sql/engine/planner/PlannerTest.java
+++ b/modules/sql-engine/src/test/java/org/apache/ignite/internal/sql/engine/planner/PlannerTest.java
@@ -727,8 +727,8 @@ public class PlannerTest extends AbstractPlannerTest {
assertNotNull(phys);
assertEquals(
"IgniteProject(DEPTNO=[$3], DEPTNO0=[$2])\n"
- + " IgniteCorrelatedNestedLoopJoin(condition=[=(CAST(+($3, $2)):INTEGER, 2)], "
- + "joinType=[inner], correlationVariables=[[$cor2]])\n"
+ + " IgniteCorrelatedNestedLoopJoin(condition=[=(CAST(+($3, $2)):INTEGER, 2)], joinType=[inner], "
+ + "correlationVariables=[[$cor2]])\n"
+ " IgniteTableScan(table=[[PUBLIC, EMP]])\n"
+ " IgniteTableScan(table=[[PUBLIC, DEPT]], filters=[=(CAST(+($t0, $cor2.DEPTNO)):INTEGER, 2)])\n",
RelOptUtil.toString(phys),