You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@ignite.apache.org by am...@apache.org on 2021/09/02 15:52:47 UTC

[ignite] branch master updated: IGNITE-15407 Fix PK flag ordering in SQL AST traverse (#9366)

This is an automated email from the ASF dual-hosted git repository.

amashenkov 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 78cd7b3  IGNITE-15407 Fix PK flag ordering in SQL AST traverse (#9366)
78cd7b3 is described below

commit 78cd7b359bdd4ac83f10e22c0604d297be5b98a1
Author: Maksim Timonin <ti...@gmail.com>
AuthorDate: Thu Sep 2 18:52:18 2021 +0300

    IGNITE-15407 Fix PK flag ordering in SQL AST traverse (#9366)
---
 .../processors/query/h2/sql/SqlAstTraverser.java   |  55 +++-------
 .../sqltests/CheckWarnJoinPartitionedTables.java   | 122 +++++++++++++--------
 2 files changed, 95 insertions(+), 82 deletions(-)

diff --git a/modules/indexing/src/main/java/org/apache/ignite/internal/processors/query/h2/sql/SqlAstTraverser.java b/modules/indexing/src/main/java/org/apache/ignite/internal/processors/query/h2/sql/SqlAstTraverser.java
index dbf6227..2970965 100644
--- a/modules/indexing/src/main/java/org/apache/ignite/internal/processors/query/h2/sql/SqlAstTraverser.java
+++ b/modules/indexing/src/main/java/org/apache/ignite/internal/processors/query/h2/sql/SqlAstTraverser.java
@@ -198,7 +198,7 @@ class SqlAstTraverser {
         if (!joinIsValid) {
             log.warning(
                 String.format(
-                    "For join two partitioned tables join condition should be the equality operation of affinity keys." +
+                    "For join two partitioned tables join condition should contain the equality operation of affinity keys." +
                         " Left side: %s; right side: %s", left.getName(), right.getName())
             );
         }
@@ -297,52 +297,29 @@ class SqlAstTraverser {
         if (!(equalOp.child(1) instanceof GridSqlColumn))
             return;
 
-        String leftCol = ((GridSqlColumn) equalOp.child(0)).columnName();
-        String rightCol = ((GridSqlColumn) equalOp.child(1)).columnName();
-
         String leftTblAls = ((GridSqlColumn) equalOp.child(0)).tableAlias();
         String rightTblAls = ((GridSqlColumn) equalOp.child(1)).tableAlias();
 
-        Set<String> actLeftCols;
-        Set<String> actRightCols;
+        int leftColIdx = leftTbl.equals(leftTblAls) ? 0 : leftTbl.equals(rightTblAls) ? 1 : -1;
+        int rightColIdx = rightTbl.equals(rightTblAls) ? 1 : rightTbl.equals(leftTblAls) ? 0 : -1;
 
-        if (leftTbl.equals(leftTblAls))
-            actLeftCols = leftCols;
-        else if (leftTbl.equals(rightTblAls))
-            actLeftCols = rightCols;
-        else
+        if (leftColIdx == -1 || rightColIdx == -1)
             return;
 
-        if (rightTbl.equals(rightTblAls))
-            actRightCols = rightCols;
-        else if (rightTbl.equals(leftTblAls))
-            actRightCols = leftCols;
-        else
-            return;
+        String leftCol = ((GridSqlColumn) equalOp.child(leftColIdx)).columnName();
+        String rightCol = ((GridSqlColumn) equalOp.child(rightColIdx)).columnName();
 
         // This is part of the affinity join condition.
-        if (actLeftCols.contains(leftCol) && actRightCols.contains(rightCol)) {
-            if (pkLeft && "_KEY".equals(leftCol))
-                actLeftCols.clear();
-            else if (pkLeft) {
-                actLeftCols.remove(leftCol);
-                // Only _KEY is there.
-                if (actLeftCols.size() == 1)
-                    actLeftCols.clear();
-            }
-            else
-                actLeftCols.remove(leftCol);
-
-            if (pkRight && "_KEY".equals(rightCol))
-                actRightCols.clear();
-            else if (pkRight) {
-                actRightCols.remove(rightCol);
-                // Only _KEY is there.
-                if (actRightCols.size() == 1)
-                    actRightCols.clear();
-            }
-            else
-                actRightCols.remove(rightCol);
+        if (leftCols.contains(leftCol) && rightCols.contains(rightCol)) {
+            leftCols.remove(leftCol);
+            rightCols.remove(rightCol);
+
+            // Only _KEY is there.
+            if (pkLeft && (leftCols.size() == 1 || "_KEY".equals(leftCol)))
+                leftCols.clear();
+
+            if (pkRight && (rightCols.size() == 1 || "_KEY".equals(rightCol)))
+                rightCols.clear();
         }
     }
 
diff --git a/modules/indexing/src/test/java/org/apache/ignite/sqltests/CheckWarnJoinPartitionedTables.java b/modules/indexing/src/test/java/org/apache/ignite/sqltests/CheckWarnJoinPartitionedTables.java
index 0163fdf..eff2f7f 100644
--- a/modules/indexing/src/test/java/org/apache/ignite/sqltests/CheckWarnJoinPartitionedTables.java
+++ b/modules/indexing/src/test/java/org/apache/ignite/sqltests/CheckWarnJoinPartitionedTables.java
@@ -17,8 +17,8 @@
 
 package org.apache.ignite.sqltests;
 
+import java.util.Arrays;
 import java.util.List;
-import org.apache.ignite.Ignite;
 import org.apache.ignite.cache.query.FieldsQueryCursor;
 import org.apache.ignite.cache.query.SqlFieldsQuery;
 import org.apache.ignite.configuration.IgniteConfiguration;
@@ -27,14 +27,27 @@ import org.apache.ignite.testframework.ListeningTestLogger;
 import org.apache.ignite.testframework.LogListener;
 import org.apache.ignite.testframework.junits.common.GridCommonAbstractTest;
 import org.junit.Test;
+import org.junit.runner.RunWith;
+import org.junit.runners.Parameterized;
 
 /** Check that illegal joins of partitioned tables are warned. */
+@RunWith(Parameterized.class)
 public class CheckWarnJoinPartitionedTables extends GridCommonAbstractTest {
     /** */
     private final ListeningTestLogger testLog = new ListeningTestLogger(log);
 
     /** */
-    private Ignite crd;
+    private IgniteEx crd;
+
+    /** */
+    @Parameterized.Parameter
+    public String joinType;
+
+    /** */
+    @Parameterized.Parameters(name = "join={0}")
+    public static List<Object> params() {
+        return Arrays.asList("LEFT JOIN", "RIGHT JOIN", "INNER JOIN", "JOIN");
+    }
 
     /** {@inheritDoc} */
     @Override protected IgniteConfiguration getConfiguration(String igniteInstanceName) throws Exception {
@@ -46,41 +59,35 @@ public class CheckWarnJoinPartitionedTables extends GridCommonAbstractTest {
     }
 
     /** {@inheritDoc} */
-    @Override protected void afterTest() throws Exception {
-        stopAllGrids();
+    @Override protected void beforeTest() throws Exception {
+        crd = startGrid();
     }
 
     /** {@inheritDoc} */
-    @Override protected void beforeTest() throws Exception {
-        crd = startGrid();
+    @Override protected void afterTest() throws Exception {
+        stopAllGrids();
     }
 
     /** */
     @Test
     public void joinSameTableWithPrimaryKey() {
-        execute(crd, new SqlFieldsQuery(
+        execute(new SqlFieldsQuery(
             "CREATE TABLE A (ID INT PRIMARY KEY, TITLE VARCHAR);"));
 
-        checkSameTableWithJoinType("LEFT JOIN");
-        checkSameTableWithJoinType("RIGHT JOIN");
-        checkSameTableWithJoinType("INNER JOIN");
-        checkSameTableWithJoinType("JOIN");
+        checkSameTableWithJoinType();
     }
 
     /** */
     @Test
     public void joinSameTableWithPrimaryAffinityKey() {
-        execute(crd, new SqlFieldsQuery(
+        execute(new SqlFieldsQuery(
             "CREATE TABLE A (ID INT PRIMARY KEY, TITLE VARCHAR) with \"AFFINITY_KEY=ID\";"));
 
-        checkSameTableWithJoinType("LEFT JOIN");
-        checkSameTableWithJoinType("RIGHT JOIN");
-        checkSameTableWithJoinType("INNER JOIN");
-        checkSameTableWithJoinType("JOIN");
+        checkSameTableWithJoinType();
     }
 
     /** */
-    private void checkSameTableWithJoinType(String joinType) {
+    private void checkSameTableWithJoinType() {
         checkLogListener(false,
             "SELECT a1.* FROM A a1 " + joinType + " A a2 on a1.ID = a2.ID;");
 
@@ -192,17 +199,14 @@ public class CheckWarnJoinPartitionedTables extends GridCommonAbstractTest {
     /** */
     @Test
     public void joinSameTableWithComplexPrimaryKey() {
-        execute(crd, new SqlFieldsQuery(
+        execute(new SqlFieldsQuery(
             "CREATE TABLE A (ID INT, TITLE VARCHAR, PRICE INT, COMMENT VARCHAR, PRIMARY KEY (ID, TITLE, PRICE));"));
 
-        checkSameTableWithComplexPrimaryKeyWithJoinType("LEFT JOIN");
-        checkSameTableWithComplexPrimaryKeyWithJoinType("RIGHT JOIN");
-        checkSameTableWithComplexPrimaryKeyWithJoinType("INNER JOIN");
-        checkSameTableWithComplexPrimaryKeyWithJoinType("JOIN");
+        checkSameTableWithComplexPrimaryKeyWithJoinType();
     }
 
     /** */
-    private void checkSameTableWithComplexPrimaryKeyWithJoinType(String joinType) {
+    private void checkSameTableWithComplexPrimaryKeyWithJoinType() {
         checkLogListener(false,
             "SELECT a1.* FROM A a1 " + joinType + " A a2 on a1._KEY = a2._KEY;");
 
@@ -291,18 +295,15 @@ public class CheckWarnJoinPartitionedTables extends GridCommonAbstractTest {
     /** */
     @Test
     public void joinSameTableWithComplexPrimaryKeySingleAffKey() {
-        execute(crd, new SqlFieldsQuery(
+        execute(new SqlFieldsQuery(
             "CREATE TABLE A (ID INT, TITLE VARCHAR, PRICE INT, COMMENT VARCHAR, PRIMARY KEY (ID, TITLE, PRICE))" +
                 " with \"AFFINITY_KEY=ID\";"));
 
-        checkSameTableWithComplexPrimaryKeySingleAffKeyWithJoinType("LEFT JOIN");
-        checkSameTableWithComplexPrimaryKeySingleAffKeyWithJoinType("RIGHT JOIN");
-        checkSameTableWithComplexPrimaryKeySingleAffKeyWithJoinType("INNER JOIN");
-        checkSameTableWithComplexPrimaryKeySingleAffKeyWithJoinType("JOIN");
+        checkSameTableWithComplexPrimaryKeySingleAffKeyWithJoinType();
     }
 
     /** */
-    private void checkSameTableWithComplexPrimaryKeySingleAffKeyWithJoinType(String joinType) {
+    private void checkSameTableWithComplexPrimaryKeySingleAffKeyWithJoinType() {
         checkLogListener(false,
             "SELECT a1.* FROM A a1 " + joinType + " A a2 on a1.ID = a2.ID;");
 
@@ -373,20 +374,17 @@ public class CheckWarnJoinPartitionedTables extends GridCommonAbstractTest {
     /** */
     @Test
     public void joinWithPrimaryKey() {
-        execute(crd, new SqlFieldsQuery(
+        execute(new SqlFieldsQuery(
             "CREATE TABLE A (ID INT PRIMARY KEY, TITLE VARCHAR);"));
 
-        execute(crd, new SqlFieldsQuery(
+        execute(new SqlFieldsQuery(
             "CREATE TABLE B (ID INT PRIMARY KEY, PRICE INT);"));
 
-        checkJoinPrimaryKeyWithJoinType("LEFT JOIN");
-        checkJoinPrimaryKeyWithJoinType("RIGHT JOIN");
-        checkJoinPrimaryKeyWithJoinType("INNER JOIN");
-        checkJoinPrimaryKeyWithJoinType("JOIN");
+        checkJoinPrimaryKeyWithJoinType();
     }
 
     /** */
-    private void checkJoinPrimaryKeyWithJoinType(String joinType) {
+    private void checkJoinPrimaryKeyWithJoinType() {
         checkLogListener(false,
             "SELECT a.* FROM A a " + joinType + " B b on a.ID = b.ID;");
 
@@ -432,12 +430,51 @@ public class CheckWarnJoinPartitionedTables extends GridCommonAbstractTest {
             "SELECT a.* FROM A a " + joinType + " B b where a.ID = b.PRICE;");
     }
 
+    /** */
+    @Test
+    public void joinPrimaryKeyAndAffinityKey() {
+        execute(new SqlFieldsQuery(
+            "CREATE TABLE A (k1 VARCHAR PRIMARY KEY, v2 VARCHAR);"));
+
+        execute(new SqlFieldsQuery(
+            "CREATE TABLE B (k1 VARCHAR, ak2 VARCHAR, v3 VARCHAR, PRIMARY KEY(k1, ak2)) with \"AFFINITY_KEY=ak2\";"));
+
+        // Correct joins.
+        checkLogListener(false,
+            "SELECT * FROM A a " + joinType + " B b on a.k1 = b.ak2");
+
+        checkLogListener(false,
+            "SELECT * FROM A a " + joinType + " B b on a.k1 = b.ak2 WHERE b.k1 = ?", "1");
+
+        checkLogListener(false,
+            "SELECT * FROM A a " + joinType + " B b where a.k1 = b.ak2");
+
+        checkLogListener(false,
+            "SELECT * FROM A a " + joinType + " B b where b.ak2 = a.k1");
+
+        checkLogListener(false,
+            "SELECT * FROM A a " + joinType + " B b on a.k1 = b.ak2 WHERE a.k1 < b.ak2");
+
+        // Wrong joins.
+
+        checkLogListener(true,
+            "SELECT * FROM A a " + joinType + " B b on a.k1 = b.k1");
+
+        checkLogListener(true,
+            "SELECT * FROM A a " + joinType + " B b on a.k1 = b.k1 and a.v2 = b.ak2");
+
+        checkLogListener(true,
+            "SELECT * FROM A a " + joinType + " B b on a.k1 > b.ak2");
+
+        checkLogListener(true,
+            "SELECT * FROM A a " + joinType + " B b where a.k1 = ? and b.ak2 = ?", "1", "1");
+    }
 
     /** */
     @Test
     // This test should not pass as it contains wrong query. But current check pass it.
     public void testWrongQueryButAllAffinityKeysAreUsed() {
-        execute(crd, new SqlFieldsQuery(
+        execute(new SqlFieldsQuery(
             "CREATE TABLE A (ID INT, TITLE VARCHAR, PRICE INT, COMMENT VARCHAR, PRIMARY KEY (ID, TITLE, PRICE));"));
 
         // PRICE = ID
@@ -446,14 +483,14 @@ public class CheckWarnJoinPartitionedTables extends GridCommonAbstractTest {
     }
 
     /** */
-    private void checkLogListener(boolean shouldFindMsg, String sql) {
+    private void checkLogListener(boolean shouldFindMsg, String sql, Object... args) {
         LogListener lsnr = LogListener.matches(
-            "For join two partitioned tables join condition should be the equality operation of affinity keys"
+            "For join two partitioned tables join condition should contain the equality operation of affinity keys"
         ).build();
 
         testLog.registerListener(lsnr);
 
-        execute(crd, new SqlFieldsQuery(sql));
+        execute(new SqlFieldsQuery(sql).setArgs(args));
 
         if (shouldFindMsg)
             assertTrue(sql, lsnr.check());
@@ -466,11 +503,10 @@ public class CheckWarnJoinPartitionedTables extends GridCommonAbstractTest {
     /**
      * Execute query from node.
      *
-     * @param node node to use to perform query.
      * @param qry query.
      */
-    protected final void execute(Ignite node, SqlFieldsQuery qry) {
-        FieldsQueryCursor<List<?>> cursor = ((IgniteEx)node).context().query().querySqlFields(qry, false);
+    protected final void execute(SqlFieldsQuery qry) {
+        FieldsQueryCursor<List<?>> cursor = crd.context().query().querySqlFields(qry, false);
 
         cursor.getAll();
     }