This is an automated email from the ASF dual-hosted git repository.
zstan 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 f18cae5ff04 IGNITE-28341 Fix incorrect WHERE condition pushdown for
LEFT OUTER JOIN in GridSqlQuerySplitter- #13502
f18cae5ff04 is described below
commit f18cae5ff04a1441de3e34f44396f049c3b28c4b
Author: Evgeniy Stanilovskiy <[email protected]>
AuthorDate: Mon Aug 24 11:37:37 2026 +0300
IGNITE-28341 Fix incorrect WHERE condition pushdown for LEFT OUTER JOIN in
GridSqlQuerySplitter- #13502
Co-authored-by: Kirill Anisimov <[email protected]>
---
.../benchmarks/jmh/sql/JmhSqlJoinBenchmark.java | 35 +++++++++++++
.../query/h2/sql/GridSqlQuerySplitter.java | 52 +++++++++++++++++--
.../query/IgniteSqlSplitterSelfTest.java | 60 ++++++++++++++++++++++
3 files changed, 144 insertions(+), 3 deletions(-)
diff --git
a/modules/benchmarks/src/main/java/org/apache/ignite/internal/benchmarks/jmh/sql/JmhSqlJoinBenchmark.java
b/modules/benchmarks/src/main/java/org/apache/ignite/internal/benchmarks/jmh/sql/JmhSqlJoinBenchmark.java
index 1293420d1ed..02858a4ed37 100644
---
a/modules/benchmarks/src/main/java/org/apache/ignite/internal/benchmarks/jmh/sql/JmhSqlJoinBenchmark.java
+++
b/modules/benchmarks/src/main/java/org/apache/ignite/internal/benchmarks/jmh/sql/JmhSqlJoinBenchmark.java
@@ -21,6 +21,7 @@ import java.util.List;
import java.util.concurrent.ThreadLocalRandom;
import org.apache.ignite.internal.benchmarks.jmh.runner.JmhIdeBenchmarkRunner;
import org.openjdk.jmh.annotations.Benchmark;
+import org.openjdk.jmh.infra.Blackhole;
/**
* Benchmark JOIN queries.
@@ -67,6 +68,40 @@ public class JmhSqlJoinBenchmark extends
JmhSqlAbstractBenchmark {
throw new AssertionError("Unexpected result size: " + res.size());
}
+ /**
+ * LEFT JOIN with DISTINCT subquery - regression test for query splitter
optimization.
+ * DISTINCT is applied to the smaller table (dept), then LEFT JOINed with
the larger table (emp),
+ * and filtered by a condition on the right table.
+ */
+ @Benchmark
+ public void leftJoinDistinctRegression(Blackhole bh) {
+ List<List<?>> res = executeSql(
+ "SELECT d.deptid, d.name, e.empid " +
+ "FROM (SELECT DISTINCT * FROM dept) d " +
+ "LEFT JOIN emp e ON d.deptid = e.deptid " +
+ "WHERE e.name = ?",
+ "Employee 5"
+ );
+
+ bh.consume(res);
+ }
+
+ /**
+ * LEFT JOIN without DISTINCT subquery - baseline for comparison.
+ */
+ @Benchmark
+ public void leftJoinNoDistinctBaseline(Blackhole bh) {
+ List<List<?>> res = executeSql(
+ "SELECT e.empid, e.name, d.deptid " +
+ "FROM emp e " +
+ "LEFT JOIN dept d ON e.deptid = d.deptid " +
+ "WHERE d.name = ?",
+ "Department 5"
+ );
+
+ bh.consume(res);
+ }
+
/**
* Run benchmarks.
*
diff --git
a/modules/indexing/src/main/java/org/apache/ignite/internal/processors/query/h2/sql/GridSqlQuerySplitter.java
b/modules/indexing/src/main/java/org/apache/ignite/internal/processors/query/h2/sql/GridSqlQuerySplitter.java
index 0bc44b5935b..77e18ab2aa6 100644
---
a/modules/indexing/src/main/java/org/apache/ignite/internal/processors/query/h2/sql/GridSqlQuerySplitter.java
+++
b/modules/indexing/src/main/java/org/apache/ignite/internal/processors/query/h2/sql/GridSqlQuerySplitter.java
@@ -718,7 +718,7 @@ public class GridSqlQuerySplitter {
pushDownSelectColumns(tblAliases, cols, wrapAlias, select);
// Move all the related WHERE conditions to wrap query.
- pushDownWhereConditions(tblAliases, cols, wrapAlias, select);
+ pushDownWhereConditions(tblAliases, cols, wrapAlias, select, model,
begin, end);
// Push down to a subquery all the JOIN elements and process ON
conditions.
pushDownJoins(tblAliases, cols, model, begin, end, wrapAlias);
@@ -1048,6 +1048,46 @@ public class GridSqlQuerySplitter {
return uniqueTblAlias + "__" + colName;
}
+ /**
+ * Return table aliases whose WHERE conditions are safe to push down.
+ * For LEFT OUTER JOIN, only aliases from the left branch are safe.
+ */
+ private static Set<GridSqlAlias> tblAliasesToPushdownConditions(
+ SplitterQueryModel model,
+ int begin,
+ int end,
+ Set<GridSqlAlias> tblAliases
+ ) {
+ int leftBranchEnd = -1;
+
+ for (int i = 1; i <= end; i++) {
+ if (model.findJoin(i).isLeftOuter()) {
+ leftBranchEnd = i - 1;
+ break;
+ }
+ }
+
+ if (leftBranchEnd == -1)
+ return tblAliases;
+
+ Set<GridSqlAlias> aliases = U.newIdentityHashSet();
+
+ int safeEnd = Math.min(end, leftBranchEnd);
+
+ if (begin > safeEnd)
+ return aliases;
+
+ for (int i = begin; i <= safeEnd; i++) {
+ GridSqlAlias uniqueTblAlias = model.childModel(i).uniqueAlias();
+
+ assert uniqueTblAlias != null : model.ast().getSQL();
+
+ aliases.add(uniqueTblAlias);
+ }
+
+ return aliases;
+ }
+
/**
* @param tblAliases Table aliases for push down.
* @param cols Columns with generated aliases.
@@ -1058,11 +1098,17 @@ public class GridSqlQuerySplitter {
Set<GridSqlAlias> tblAliases,
Map<String, GridSqlAlias> cols,
GridSqlAlias wrapAlias,
- GridSqlSelect select
+ GridSqlSelect select,
+ SplitterQueryModel model,
+ int begin,
+ int end
) {
if (select.where() == null)
return;
+ Set<GridSqlAlias> tblAliasesToPushdownConditions =
+ tblAliasesToPushdownConditions(model, begin, end, tblAliases);
+
GridSqlSelect wrapSelect =
GridSqlAlias.<GridSqlSubquery>unwrap(wrapAlias).subquery();
List<SplitterAndCondition> andConditions = new ArrayList<>();
@@ -1073,7 +1119,7 @@ public class GridSqlQuerySplitter {
SplitterAndCondition c = andConditions.get(i);
GridSqlAst condition = c.ast();
- if (isAllRelatedToTables(tblAliases, U.newIdentityHashSet(),
condition)) {
+ if (isAllRelatedToTables(tblAliasesToPushdownConditions,
U.newIdentityHashSet(), condition)) {
if (!SplitterUtils.isTrue(condition)) {
// Replace the original condition with `true` and move it
to the wrap query.
c.parent().child(c.childIndex(), TRUE);
diff --git
a/modules/indexing/src/test/java/org/apache/ignite/internal/processors/query/IgniteSqlSplitterSelfTest.java
b/modules/indexing/src/test/java/org/apache/ignite/internal/processors/query/IgniteSqlSplitterSelfTest.java
index 3c1262d4ea6..429d17f3fce 100644
---
a/modules/indexing/src/test/java/org/apache/ignite/internal/processors/query/IgniteSqlSplitterSelfTest.java
+++
b/modules/indexing/src/test/java/org/apache/ignite/internal/processors/query/IgniteSqlSplitterSelfTest.java
@@ -2047,6 +2047,66 @@ public class IgniteSqlSplitterSelfTest extends
AbstractIndexingCommonTest {
}
}
+ /**
+ * Verifies LEFT JOIN behavior when the left side is a subquery with
DISTINCT and
+ * the result is filtered by a condition on the right table.
+ * The test checks that no rows are returned when there is no matching
department name.
+ *
+ * @throws Exception If failed.
+ */
+ @Test
+ public void testLeftJoinWithSubquery() throws Exception {
+ IgniteCache<?, ?> cache = ignite(0).getOrCreateCache(
+ cacheConfig("psLeftJoinSubquery", true, Integer.class,
Person.class)
+ );
+
+ String personTbl = "PERSON_LEFT_JOIN_SUBQUERY";
+ String depTbl = "DEPARTMENT_LEFT_JOIN_SUBQUERY";
+
+ try {
+ cache.query(new SqlFieldsQuery(
+ "CREATE TABLE " + personTbl + " (" +
+ "id INT PRIMARY KEY, " +
+ "depId INT, " +
+ "name VARCHAR" +
+ ')'
+ )).getAll();
+
+ cache.query(new SqlFieldsQuery(
+ "CREATE TABLE " + depTbl + " (" +
+ "id INT PRIMARY KEY, " +
+ "name VARCHAR" +
+ ')'
+ )).getAll();
+
+ cache.query(new SqlFieldsQuery(
+ "INSERT INTO " + personTbl + "(id, depId, name) VALUES (1,
1, 'Emma')"
+ )).getAll();
+
+ cache.query(new SqlFieldsQuery(
+ "INSERT INTO " + depTbl + "(id, name) VALUES (2, 'TX')"
+ )).getAll();
+
+ List<List<?>> res = cache.query(new SqlFieldsQuery(
+ "SELECT p.id AS person_id, p.name AS person_name, o.id AS
department_id " +
+ "FROM (SELECT DISTINCT * FROM " + personTbl + ") p
" +
+ "LEFT JOIN " + depTbl + " o ON p.depId = o.id " +
+ "WHERE o.name = 'SQL'"
+ )).getAll();
+
+ assertTrue(res.isEmpty());
+ }
+ finally {
+ try {
+ cache.query(new SqlFieldsQuery("DROP TABLE IF EXISTS " +
personTbl)).getAll();
+ cache.query(new SqlFieldsQuery("DROP TABLE IF EXISTS " +
depTbl)).getAll();
+ }
+ finally {
+ cache.destroy();
+ }
+ }
+ }
+
/**
* Check avg() with various data types.
*
