You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@ignite.apache.org by ag...@apache.org on 2017/04/06 10:46:02 UTC
[25/50] [abbrv] ignite git commit: ignite-2913 - SQL: EXISTS support
added
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-3477-master
Commit: f726dc483a9e406b0149a6afffc38026b43ccbc5
Parents: f66be1a
Author: Sergi Vladykin <se...@gmail.com>
Authored: Sun Apr 2 15:35:04 2017 +0300
Committer: Sergi Vladykin <se...@gmail.com>
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<ConditionInSelect, Query> QUERY = getter(ConditionInSelect.class, "query");
+ private static final Getter<ConditionInSelect, Query> QUERY_IN = getter(ConditionInSelect.class, "query");
+
+ /** */
+ private static final Getter<ConditionExists, Query> QUERY_EXISTS = getter(ConditionExists.class, "query");
/** */
private static final Getter<CompareLike, Expression> 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<Integer,Person2> x = ignite(0).getOrCreateCache(cacheConfig("x", true,
+ Integer.class, Person2.class));
+ IgniteCache<Integer,Person2> y = ignite(0).getOrCreateCache(cacheConfig("y", true,
+ Integer.class, Person2.class));
+
+ try {
+ GridRandom rnd = new GridRandom();
+
+ Set<Integer> 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<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;
}