Return-Path: X-Original-To: archive-asf-public-internal@cust-asf2.ponee.io Delivered-To: archive-asf-public-internal@cust-asf2.ponee.io Received: from cust-asf.ponee.io (cust-asf.ponee.io [163.172.22.183]) by cust-asf2.ponee.io (Postfix) with ESMTP id F2D3E200C77 for ; Tue, 11 Apr 2017 03:38:02 +0200 (CEST) Received: by cust-asf.ponee.io (Postfix) id F193C160BB3; Tue, 11 Apr 2017 01:38:02 +0000 (UTC) Delivered-To: archive-asf-public@cust-asf.ponee.io Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by cust-asf.ponee.io (Postfix) with SMTP id F0E89160B99 for ; Tue, 11 Apr 2017 03:38:01 +0200 (CEST) Received: (qmail 1664 invoked by uid 500); 11 Apr 2017 01:38:01 -0000 Mailing-List: contact commits-help@ignite.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: dev@ignite.apache.org Delivered-To: mailing list commits@ignite.apache.org Received: (qmail 1652 invoked by uid 99); 11 Apr 2017 01:38:01 -0000 Received: from git1-us-west.apache.org (HELO git1-us-west.apache.org) (140.211.11.23) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 11 Apr 2017 01:38:01 +0000 Received: by git1-us-west.apache.org (ASF Mail Server at git1-us-west.apache.org, from userid 33) id 163B6E9638; Tue, 11 Apr 2017 01:38:01 +0000 (UTC) Content-Type: text/plain; charset="us-ascii" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit From: dmagda@apache.org To: commits@ignite.apache.org Date: Tue, 11 Apr 2017 01:38:01 -0000 Message-Id: <4a19f1c5824c45d59379118d4b0ab2de@git.apache.org> X-Mailer: ASF-Git Admin Mailer Subject: [01/50] [abbrv] ignite git commit: ignite-2913 - SQL: EXISTS support added archived-at: Tue, 11 Apr 2017 01:38:03 -0000 Repository: ignite Updated Branches: refs/heads/ignite-1192 9017ab483 -> 362700c6a ignite-2913 - SQL: EXISTS support added Project: http://git-wip-us.apache.org/repos/asf/ignite/repo Commit: http://git-wip-us.apache.org/repos/asf/ignite/commit/f726dc48 Tree: http://git-wip-us.apache.org/repos/asf/ignite/tree/f726dc48 Diff: http://git-wip-us.apache.org/repos/asf/ignite/diff/f726dc48 Branch: refs/heads/ignite-1192 Commit: f726dc483a9e406b0149a6afffc38026b43ccbc5 Parents: f66be1a Author: Sergi Vladykin Authored: Sun Apr 2 15:35:04 2017 +0300 Committer: Sergi Vladykin Committed: Sun Apr 2 15:35:04 2017 +0300 ---------------------------------------------------------------------- .../query/h2/sql/GridSqlOperationType.java | 23 +++++++++-- .../query/h2/sql/GridSqlQueryParser.java | 19 ++++++++- .../query/IgniteSqlSplitterSelfTest.java | 43 ++++++++++++++++++++ .../query/h2/sql/GridQueryParsingTest.java | 27 +++++++++++- 4 files changed, 105 insertions(+), 7 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/ignite/blob/f726dc48/modules/indexing/src/main/java/org/apache/ignite/internal/processors/query/h2/sql/GridSqlOperationType.java ---------------------------------------------------------------------- diff --git a/modules/indexing/src/main/java/org/apache/ignite/internal/processors/query/h2/sql/GridSqlOperationType.java b/modules/indexing/src/main/java/org/apache/ignite/internal/processors/query/h2/sql/GridSqlOperationType.java index 07a6f6b..5009c0c 100644 --- a/modules/indexing/src/main/java/org/apache/ignite/internal/processors/query/h2/sql/GridSqlOperationType.java +++ b/modules/indexing/src/main/java/org/apache/ignite/internal/processors/query/h2/sql/GridSqlOperationType.java @@ -31,7 +31,7 @@ public enum GridSqlOperationType { MULTIPLY(2, new BiExpressionSqlGenerator("*")), DIVIDE(2, new BiExpressionSqlGenerator("/")), MODULUS(2, new BiExpressionSqlGenerator("%")), - NEGATE(1, new PrefixSqlGenerator("-")), + NEGATE(1, new PrefixSqlGenerator("-", true)), // from org.h2.expression.Comparison EQUAL(2, new BiExpressionSqlGenerator("=")), @@ -47,7 +47,7 @@ public enum GridSqlOperationType { IS_NULL(1, new SuffixSqlGenerator("IS NULL")), IS_NOT_NULL(1, new SuffixSqlGenerator("IS NOT NULL")), - NOT(1, new PrefixSqlGenerator("NOT")), + NOT(1, new PrefixSqlGenerator("NOT", true)), // from org.h2.expression.ConditionAndOr AND(2, new BiExpressionSqlGenerator("AND")), @@ -58,6 +58,7 @@ public enum GridSqlOperationType { LIKE(2, new BiExpressionSqlGenerator("LIKE")), IN(-1, new ConditionInSqlGenerator()), + EXISTS(1, new PrefixSqlGenerator("EXISTS", false)), ; /** */ @@ -145,18 +146,32 @@ public enum GridSqlOperationType { /** */ private final String text; + /** */ + private final boolean addSpace; + /** * @param text Text. + * @param addSpace Add space char after the prefix. */ - private PrefixSqlGenerator(String text) { + private PrefixSqlGenerator(String text, boolean addSpace) { this.text = text; + this.addSpace = addSpace; } /** {@inheritDoc} */ @Override public String getSql(GridSqlOperation operation) { assert operation.operationType().childrenCnt == 1; - return '(' + text + ' ' + operation.child(0).getSQL() + ')'; + StringBuilder b = new StringBuilder(); + + b.append('(').append(text); + + if (addSpace) + b.append(' '); + + b.append(operation.child(0).getSQL()).append(')'); + + return b.toString(); } } http://git-wip-us.apache.org/repos/asf/ignite/blob/f726dc48/modules/indexing/src/main/java/org/apache/ignite/internal/processors/query/h2/sql/GridSqlQueryParser.java ---------------------------------------------------------------------- diff --git a/modules/indexing/src/main/java/org/apache/ignite/internal/processors/query/h2/sql/GridSqlQueryParser.java b/modules/indexing/src/main/java/org/apache/ignite/internal/processors/query/h2/sql/GridSqlQueryParser.java index 16d7105..0f940e9 100644 --- a/modules/indexing/src/main/java/org/apache/ignite/internal/processors/query/h2/sql/GridSqlQueryParser.java +++ b/modules/indexing/src/main/java/org/apache/ignite/internal/processors/query/h2/sql/GridSqlQueryParser.java @@ -45,6 +45,7 @@ import org.h2.expression.Alias; import org.h2.expression.CompareLike; import org.h2.expression.Comparison; import org.h2.expression.ConditionAndOr; +import org.h2.expression.ConditionExists; import org.h2.expression.ConditionIn; import org.h2.expression.ConditionInConstantSet; import org.h2.expression.ConditionInSelect; @@ -78,6 +79,7 @@ import static org.apache.ignite.internal.processors.query.h2.sql.GridSqlOperatio import static org.apache.ignite.internal.processors.query.h2.sql.GridSqlOperationType.DIVIDE; import static org.apache.ignite.internal.processors.query.h2.sql.GridSqlOperationType.EQUAL; import static org.apache.ignite.internal.processors.query.h2.sql.GridSqlOperationType.EQUAL_NULL_SAFE; +import static org.apache.ignite.internal.processors.query.h2.sql.GridSqlOperationType.EXISTS; import static org.apache.ignite.internal.processors.query.h2.sql.GridSqlOperationType.IN; import static org.apache.ignite.internal.processors.query.h2.sql.GridSqlOperationType.IS_NOT_NULL; import static org.apache.ignite.internal.processors.query.h2.sql.GridSqlOperationType.IS_NULL; @@ -184,7 +186,10 @@ public class GridSqlQueryParser { "compareType"); /** */ - private static final Getter QUERY = getter(ConditionInSelect.class, "query"); + private static final Getter QUERY_IN = getter(ConditionInSelect.class, "query"); + + /** */ + private static final Getter QUERY_EXISTS = getter(ConditionExists.class, "query"); /** */ private static final Getter LEFT = getter(CompareLike.class, "left"); @@ -940,7 +945,7 @@ public class GridSqlQueryParser { res.addChild(parseExpression(LEFT_CIS.get((ConditionInSelect)expression), calcTypes)); - Query qry = QUERY.get((ConditionInSelect)expression); + Query qry = QUERY_IN.get((ConditionInSelect)expression); res.addChild(parseQueryExpression(qry)); @@ -1043,6 +1048,16 @@ public class GridSqlQueryParser { return res; } + if (expression instanceof ConditionExists) { + Query qry = QUERY_EXISTS.get((ConditionExists)expression); + + GridSqlOperation res = new GridSqlOperation(EXISTS); + + res.addChild(parseQueryExpression(qry)); + + return res; + } + throw new IgniteException("Unsupported expression: " + expression + " [type=" + expression.getClass().getSimpleName() + ']'); } http://git-wip-us.apache.org/repos/asf/ignite/blob/f726dc48/modules/indexing/src/test/java/org/apache/ignite/internal/processors/query/IgniteSqlSplitterSelfTest.java ---------------------------------------------------------------------- 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 8c880a3..2e1887e 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 @@ -21,9 +21,11 @@ import java.io.Serializable; import java.util.ArrayList; import java.util.Collections; import java.util.HashMap; +import java.util.HashSet; import java.util.List; import java.util.Map; import java.util.Random; +import java.util.Set; import java.util.concurrent.Callable; import java.util.concurrent.atomic.AtomicLong; import javax.cache.CacheException; @@ -191,6 +193,47 @@ public class IgniteSqlSplitterSelfTest extends GridCommonAbstractTest { } } + @SuppressWarnings("SuspiciousMethodCalls") + public void testExists() { + IgniteCache x = ignite(0).getOrCreateCache(cacheConfig("x", true, + Integer.class, Person2.class)); + IgniteCache y = ignite(0).getOrCreateCache(cacheConfig("y", true, + Integer.class, Person2.class)); + + try { + GridRandom rnd = new GridRandom(); + + Set intersects = new HashSet<>(); + + for (int i = 0; i < 3000; i++) { + int r = rnd.nextInt(3); + + if (r != 0) + x.put(i, new Person2(i, "pers_x_" + i)); + + if (r != 1) + y.put(i, new Person2(i, "pers_y_" + i)); + + if (r == 2) + intersects.add(i); + } + + assertFalse(intersects.isEmpty()); + + List> res = x.query(new SqlFieldsQuery("select _key from \"x\".Person2 px " + + "where exists(select 1 from \"y\".Person2 py where px._key = py._key)")).getAll(); + + assertEquals(intersects.size(), res.size()); + + for (List row : res) + assertTrue(intersects.contains(row.get(0))); + } + finally { + x.destroy(); + y.destroy(); + } + } + /** * @throws Exception If failed. */ http://git-wip-us.apache.org/repos/asf/ignite/blob/f726dc48/modules/indexing/src/test/java/org/apache/ignite/internal/processors/query/h2/sql/GridQueryParsingTest.java ---------------------------------------------------------------------- diff --git a/modules/indexing/src/test/java/org/apache/ignite/internal/processors/query/h2/sql/GridQueryParsingTest.java b/modules/indexing/src/test/java/org/apache/ignite/internal/processors/query/h2/sql/GridQueryParsingTest.java index 1c2ffd7..477451a 100644 --- a/modules/indexing/src/test/java/org/apache/ignite/internal/processors/query/h2/sql/GridQueryParsingTest.java +++ b/modules/indexing/src/test/java/org/apache/ignite/internal/processors/query/h2/sql/GridQueryParsingTest.java @@ -114,6 +114,12 @@ public class GridQueryParsingTest extends GridCommonAbstractTest { * @throws Exception If failed. */ public void testParseSelectAndUnion() throws Exception { + checkQuery("select 1 from Person p where addrIds in ((1,2,3), (3,4,5))"); + checkQuery("select 1 from Person p where addrId in ((1,))"); + checkQuery("select 1 from Person p " + + "where p.addrId in (select a.id from Address a)"); + checkQuery("select 1 from Person p " + + "where exists(select 1 from Address a where p.addrId = a.id)"); checkQuery("select 42"); checkQuery("select ()"); checkQuery("select (1)"); @@ -289,7 +295,8 @@ public class GridQueryParsingTest extends GridCommonAbstractTest { * @throws Exception If failed. */ public void testParseTableFilter() throws Exception { - Prepared prepared = parse("select Person.old, p1.old from Person, Person p1"); + Prepared prepared = parse("select Person.old, p1.old, p1.addrId from Person, Person p1 " + + "where exists(select 1 from Address a where a.id = p1.addrId)"); GridSqlSelect select = (GridSqlSelect)new GridSqlQueryParser(false).parse(prepared); @@ -312,6 +319,21 @@ public class GridQueryParsingTest extends GridCommonAbstractTest { // Alias in FROM must be included in column. assertSame(tbl2Alias, col2.expressionInFrom()); + + // In EXISTS we must correctly reference the column from the outer query. + GridSqlAst exists = select.where(); + GridSqlSubquery subqry = exists.child(); + GridSqlSelect subSelect = subqry.child(); + + GridSqlColumn p1AddrIdCol = (GridSqlColumn)select.column(2); + + assertEquals("ADDRID", p1AddrIdCol.column().getName()); + assertSame(tbl2Alias, p1AddrIdCol.expressionInFrom()); + + GridSqlColumn p1AddrIdColExists = subSelect.where().child(1); + assertEquals("ADDRID", p1AddrIdCol.column().getName()); + + assertSame(tbl2Alias, p1AddrIdColExists.expressionInFrom()); } /** */ @@ -501,6 +523,9 @@ public class GridQueryParsingTest extends GridCommonAbstractTest { @QuerySqlField(index = true) public int addrId; + @QuerySqlField + public Integer[] addrIds; + @QuerySqlField(index = true) public int old; }