This is an automated email from the ASF dual-hosted git repository.
tledkov pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/ignite.git
The following commit(s) were added to refs/heads/master by this push:
new dce3ed6 IGNITE-14358 GridSubqueryJoinOptimizer fixes and tests.
(#9441)
dce3ed6 is described below
commit dce3ed61d81abe005a590c79f94112d0e2d648dd
Author: Vladimir Ermakov <[email protected]>
AuthorDate: Wed Oct 20 14:06:11 2021 +0300
IGNITE-14358 GridSubqueryJoinOptimizer fixes and tests. (#9441)
---
.../query/h2/GridSubqueryJoinOptimizer.java | 78 +++++++--
.../h2/GridSubqueryJoinOptimizerSelfTest.java | 185 ++++++++++++++++++++-
2 files changed, 246 insertions(+), 17 deletions(-)
diff --git
a/modules/indexing/src/main/java/org/apache/ignite/internal/processors/query/h2/GridSubqueryJoinOptimizer.java
b/modules/indexing/src/main/java/org/apache/ignite/internal/processors/query/h2/GridSubqueryJoinOptimizer.java
index 6f5889d..fa3fd72 100644
---
a/modules/indexing/src/main/java/org/apache/ignite/internal/processors/query/h2/GridSubqueryJoinOptimizer.java
+++
b/modules/indexing/src/main/java/org/apache/ignite/internal/processors/query/h2/GridSubqueryJoinOptimizer.java
@@ -301,6 +301,18 @@ public class GridSubqueryJoinOptimizer {
if (aggFinder.findNext() != null)
return false;
+
+ // In case of query like "SELECT * FROM (SELECT i||j FROM t) u;",
where subquery contains pure operation
+ // without an alias, we cannot determine which generated alias in
the parent query the original expression
+ // belongs to. So the best we can do is skip the case.
+ ASTNodeFinder operationFinder = new ASTNodeFinder(
+ col,
+ (p, c) -> p instanceof GridSqlOperation,
+ ast -> false
+ );
+
+ if (operationFinder.findNext() != null)
+ return false;
}
return true;
@@ -383,11 +395,21 @@ public class GridSubqueryJoinOptimizer {
else
target.child(childInd, subTbl);
- if (subSel.where() != null)
- parent.where(parent.where() == null ? subSel.where() : new
GridSqlOperation(AND, parent.where(), subSel.where()));
+ GridSqlAst where = subSel.where();
+
+ if (where != null) {
+ if (target != null) {
+ GridSqlJoin join = (GridSqlJoin)target;
+
+ join.child(GridSqlJoin.ON_CHILD, new GridSqlOperation(AND,
join.on(), where));
+ }
+ else
+ parent.where(parent.where() == null ? where : new
GridSqlOperation(AND, parent.where(), where));
+ }
remapColumns(
parent,
+ subSel,
// reference equality used intentionally here
col -> wrappedSubQry == col.expressionInFrom(),
subTbl
@@ -399,13 +421,14 @@ public class GridSubqueryJoinOptimizer {
/**
* Remap all columns that satisfy the predicate such they be referred to
the given table.
*
- * @param ast Tree where to search columns.
+ * @param parent Tree where to search columns.
+ * @param subSelect Tree where to search column aliases.
* @param colPred Collection predicate.
* @param tbl Table.
*/
- private static void remapColumns(GridSqlAst ast, Predicate<GridSqlColumn>
colPred, GridSqlAlias tbl) {
+ private static void remapColumns(GridSqlAst parent, GridSqlAst subSelect,
Predicate<GridSqlColumn> colPred, GridSqlAlias tbl) {
ASTNodeFinder colFinder = new ASTNodeFinder(
- ast,
+ parent,
(p, c) -> c instanceof GridSqlColumn &&
colPred.test((GridSqlColumn)c)
);
@@ -413,20 +436,45 @@ public class GridSubqueryJoinOptimizer {
while ((res = colFinder.findNext()) != null) {
GridSqlColumn oldCol = res.getEl().child(res.getIdx());
- res.getEl().child(
- res.getIdx(),
- new GridSqlColumn(
- oldCol.column(),
- tbl,
- oldCol.schema(),
- tbl.alias(),
- oldCol.columnName()
- )
- );
+ BiPredicate<GridSqlAst, GridSqlAst> constPred = (p, c) ->
+ c != null && c.getSQL().equals(oldCol.columnName());
+
+ BiPredicate<GridSqlAst, GridSqlAst> aliasPred = (p, c) ->
+ c instanceof GridSqlAlias &&
((GridSqlAlias)c).alias().equals(oldCol.columnName());
+
+ ASTNodeFinder.Result aliasOrPred = findNode(subSelect,
constPred.or(aliasPred));
+
+ if (aliasOrPred != null)
+ res.getEl().child(res.getIdx(),
GridSqlAlias.unwrap(aliasOrPred.getEl().child(aliasOrPred.getIdx())));
+ else {
+ res.getEl().child(
+ res.getIdx(),
+ new GridSqlColumn(
+ oldCol.column(),
+ tbl,
+ oldCol.schema(),
+ tbl.alias(),
+ oldCol.columnName()
+ )
+ );
+ }
}
}
/**
+ * Searches for first node in AST tree according to the given parameters.
+ *
+ * @param tree Parent ast.
+ * @param pred Filter predicate.
+ * @return Found node or null.
+ */
+ private static ASTNodeFinder.Result findNode(GridSqlAst tree,
BiPredicate<GridSqlAst, GridSqlAst> pred) {
+ ASTNodeFinder colFinder = new ASTNodeFinder(tree, pred);
+
+ return colFinder.findNext();
+ }
+
+ /**
* Pull out sub-select from SELECT clause to the parent select level.
* <p>
* Example:
diff --git
a/modules/indexing/src/test/java/org/apache/ignite/internal/processors/query/h2/GridSubqueryJoinOptimizerSelfTest.java
b/modules/indexing/src/test/java/org/apache/ignite/internal/processors/query/h2/GridSubqueryJoinOptimizerSelfTest.java
index 03b3638..5c4dd54 100644
---
a/modules/indexing/src/test/java/org/apache/ignite/internal/processors/query/h2/GridSubqueryJoinOptimizerSelfTest.java
+++
b/modules/indexing/src/test/java/org/apache/ignite/internal/processors/query/h2/GridSubqueryJoinOptimizerSelfTest.java
@@ -17,15 +17,19 @@
package org.apache.ignite.internal.processors.query.h2;
+import java.util.ArrayList;
import java.util.Comparator;
import java.util.Iterator;
import java.util.List;
import java.util.Random;
import org.apache.ignite.IgniteCache;
import org.apache.ignite.IgniteSystemProperties;
+import org.apache.ignite.cache.query.FieldsQueryCursor;
import org.apache.ignite.cache.query.SqlFieldsQuery;
import org.apache.ignite.configuration.CacheConfiguration;
import org.apache.ignite.internal.IgniteEx;
+import org.apache.ignite.internal.processors.cache.query.QueryCursorEx;
+import org.apache.ignite.internal.processors.query.GridQueryFieldMetadata;
import org.apache.ignite.testframework.GridTestUtils;
import org.apache.ignite.testframework.junits.common.GridCommonAbstractTest;
import org.junit.Assert;
@@ -547,6 +551,162 @@ public class GridSubqueryJoinOptimizerSelfTest extends
GridCommonAbstractTest {
}
/**
+ * Case with alias in subqueries in union.
+ */
+ @Test
+ public void testOptimizationAliasUnion() {
+ String outerSqlTemplate = "SELECT d FROM (%s) u union all SELECT d
FROM (%s) z;";
+ String subSql = "SELECT id + id * id as d FROM dep";
+
+ String resSql = String.format(outerSqlTemplate, subSql, subSql);
+
+ check(resSql, 1);
+ }
+
+ /**
+ * Case with alias in subquery.
+ */
+ @Test
+ public void testOptimizationAlias1() {
+ String outerSqlTemplate = "SELECT d FROM (%s) u;";
+ String subSql = "SELECT id + id * id as d FROM dep";
+
+ String resSql = String.format(outerSqlTemplate, subSql);
+
+ check(resSql, 1);
+ }
+
+ /**
+ * Case with the same alias as column name in subquery.
+ */
+ @Test
+ public void testOptimizationAlias2() {
+ String outerSqlTemplate = "SELECT id FROM (%s) u;";
+ String subSql = "SELECT id + id * id as id FROM dep";
+
+ String resSql = String.format(outerSqlTemplate, subSql);
+
+ check(resSql, 1);
+ }
+
+ /**
+ * Case with a double aliases in the query and subquery.
+ */
+ @Test
+ public void testOptimizationAlias3() {
+ String outerSqlTemplate = "SELECT d1, d1 as p1, d2 as p2, d3::VARCHAR
as p3, d2::VARCHAR as p4 FROM (%s) u;";
+ String subSql = "SELECT id as d1, id + 1 as d2, 2 + 2 as d3 FROM dep";
+
+ String resSql = String.format(outerSqlTemplate, subSql);
+
+ check(resSql, 1);
+ }
+
+ /**
+ * Case with a sum of a set of variables with aliases and different types
(pure column, constant, sum).
+ */
+ @Test
+ public void testOptimizationAlias4() {
+ String outerSqlTemplate = "SELECT (d1 + d2 + d3 + id) as p FROM (%s)
u;";
+ String subSql = "SELECT id, id as d1, id + 1 as d2, 2 + 2 as d3 FROM
dep";
+
+ String resSql = String.format(outerSqlTemplate, subSql);
+
+ check(resSql, 1);
+ }
+
+ /**
+ * Case with constants in subquery without aliases.
+ */
+ @Test
+ public void testOptimizationConstant1() {
+ String outerSqlTemplate = "SELECT * FROM (%s) u;";
+ String subSql = "SELECT 2 + 2, '1+1', 3.14::DECIMAL, extract(year from
CURRENT_DATE()) FROM dep";
+
+ String resSql = String.format(outerSqlTemplate, subSql);
+
+ check(resSql, 1);
+ }
+
+ /**
+ * Case with constant as column name.
+ */
+ @Test
+ public void testOptimizationConstant2() {
+ String outerSqlTemplate = "SELECT \"42\" as p FROM (%s) u;";
+ String subSql = "SELECT 42 FROM dep";
+
+ String resSql = String.format(outerSqlTemplate, subSql);
+
+ check(resSql, 1);
+ }
+
+ /**
+ * Case with WHEN-THEN-ELSE construction in subquery.
+ */
+ @Test
+ public void testOptimizationCaseWhen() {
+ String outerSqlTemplate = "SELECT * FROM (%s) u;";
+ String subSql = "SELECT Case id When 1 Then 4 Else 3 End as A, Case id
When 1 Then 3 Else 4 End as B FROM dep";
+
+ String resSql = String.format(outerSqlTemplate, subSql);
+
+ check(resSql, 1);
+ }
+
+ /**
+ * Case with CAST function with alias in subquery.
+ */
+ @Test
+ public void testOptimizationCastFunction() {
+ String outerSqlTemplate = "SELECT z FROM (%s) u;";
+ String subSql = "SELECT CAST(3.14 as DECIMAL) z FROM dep";
+
+ String resSql = String.format(outerSqlTemplate, subSql);
+
+ check(resSql, 1);
+ }
+
+ /**
+ * Case with jeft join with WHERE FALSE condition inside.
+ */
+ @Test
+ public void testOptimizationLeftJoinWhereFalse() {
+ String outerSqlTemplate = "SELECT * FROM dep AS t1 LEFT JOIN (%s) AS
t2 ON t1.id = t2.id;";
+ String subSql = "SELECT * FROM dep2 WHERE false";
+
+ String resSql = String.format(outerSqlTemplate, subSql);
+
+ check(resSql, 1);
+ }
+
+ /**
+ * Case with jeft join with false where condition inside.
+ */
+ @Test
+ public void testOptimizationLeftJoinWhereFalse2() {
+ String outerSqlTemplate = "SELECT * FROM dep AS t1 LEFT JOIN (%s) AS
t2 ON t1.id = t2.id;";
+ String subSql = "SELECT * FROM dep2 WHERE dep2.id IS NULL";
+
+ String resSql = String.format(outerSqlTemplate, subSql);
+
+ check(resSql, 1);
+ }
+
+ /**
+ * Case with jeft join with false where condition inside.
+ */
+ @Test
+ public void testOptimizationLeftJoinWhereFalse3() {
+ String outerSqlTemplate = "SELECT * FROM dep AS t1 LEFT JOIN (%s) AS
t2 ON t1.id = true;";
+ String subSql = "SELECT * FROM dep2 WHERE dep2.id IS NULL";
+
+ String resSql = String.format(outerSqlTemplate, subSql);
+
+ check(resSql, 1);
+ }
+
+ /**
* Test should verify all cases where subquery should not be rewrited.
*/
@Test
@@ -644,18 +804,39 @@ public class GridSubqueryJoinOptimizerSelfTest extends
GridCommonAbstractTest {
private void check(String sql, int expSelectClauses) {
optimizationEnabled(false);
- List<List<?>> exp = cache.query(new SqlFieldsQuery(sql)).getAll();
+ FieldsQueryCursor<List<?>> qry = cache.query(new SqlFieldsQuery(sql));
+
+ List<GridQueryFieldMetadata> expMetaList =
((QueryCursorEx<List<?>>)qry).fieldsMeta();
+
+ List<List<?>> exp = qry.getAll();
exp.sort(ROW_COMPARATOR);
optimizationEnabled(true);
- List<List<?>> act = cache.query(new
SqlFieldsQuery(sql).setEnforceJoinOrder(true)).getAll();
+ FieldsQueryCursor<List<?>> optQry = cache.query(new
SqlFieldsQuery(sql).setEnforceJoinOrder(true));
+
+ List<GridQueryFieldMetadata> actMetaList =
((QueryCursorEx<List<?>>)optQry).fieldsMeta();
+
+ List<List<?>> act = optQry.getAll();
act.sort(ROW_COMPARATOR);
Assert.assertEquals("Result set mismatch", exp, act);
+ List<String> expFieldTypes = new ArrayList<>();
+ List<String> actualFieldTypes = new ArrayList<>();
+
+ for (int i = 0; i < expMetaList.size(); i++) {
+ GridQueryFieldMetadata expMeta = expMetaList.get(i);
+ GridQueryFieldMetadata actMeta = actMetaList.get(i);
+
+ expFieldTypes.add(expMeta.fieldName() + ":" +
expMeta.fieldTypeName());
+ actualFieldTypes.add(actMeta.fieldName() + ":" +
actMeta.fieldTypeName());
+ }
+
+ Assert.assertEquals("Result set field names or field types mismatch",
expFieldTypes, actualFieldTypes);
+
String plan = cache.query(new SqlFieldsQuery("explain " +
sql)).getAll().get(0).get(0).toString();
System.out.println(plan);
