You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@calcite.apache.org by se...@apache.org on 2018/11/28 04:05:28 UTC
calcite git commit: [CALCITE-2671] GeodeFilter convert multiple ORs
(on same attribute) into single IN SET. (Sandeep Chada)
Repository: calcite
Updated Branches:
refs/heads/master 249ec5975 -> da57c903f
[CALCITE-2671] GeodeFilter convert multiple ORs (on same attribute) into single IN SET. (Sandeep Chada)
Geode IN SET operator has better performance than multiple ORs (even if they're equivalent)
foo = 1 or foo = 2 or foo = 3
-- equivalent to (but much faster in geode)
foo in SET(1, 2, 3)
closes apache/calcite#942
Project: http://git-wip-us.apache.org/repos/asf/calcite/repo
Commit: http://git-wip-us.apache.org/repos/asf/calcite/commit/da57c903
Tree: http://git-wip-us.apache.org/repos/asf/calcite/tree/da57c903
Diff: http://git-wip-us.apache.org/repos/asf/calcite/diff/da57c903
Branch: refs/heads/master
Commit: da57c903f335141e48d13656bafc568d8eb0d2d7
Parents: 249ec59
Author: chadasa <44...@users.noreply.github.com>
Authored: Sat Nov 24 12:43:24 2018 +0530
Committer: Andrei Sereda <25...@users.noreply.github.com>
Committed: Tue Nov 27 23:03:46 2018 -0500
----------------------------------------------------------------------
.../calcite/adapter/geode/rel/GeodeFilter.java | 145 +++++++++++++++++--
.../geode/rel/GeodeAllDataTypesTest.java | 145 +++++++++++++++++++
.../adapter/geode/rel/GeodeBookstoreTest.java | 94 +++++++++---
.../adapter/geode/rel/GeodeZipsTest.java | 110 +++++++++++++-
.../calcite/adapter/geode/rel/JsonLoader.java | 16 +-
5 files changed, 472 insertions(+), 38 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/calcite/blob/da57c903/geode/src/main/java/org/apache/calcite/adapter/geode/rel/GeodeFilter.java
----------------------------------------------------------------------
diff --git a/geode/src/main/java/org/apache/calcite/adapter/geode/rel/GeodeFilter.java b/geode/src/main/java/org/apache/calcite/adapter/geode/rel/GeodeFilter.java
index 203e5fe..9887306 100644
--- a/geode/src/main/java/org/apache/calcite/adapter/geode/rel/GeodeFilter.java
+++ b/geode/src/main/java/org/apache/calcite/adapter/geode/rel/GeodeFilter.java
@@ -29,16 +29,20 @@ import org.apache.calcite.rex.RexCall;
import org.apache.calcite.rex.RexInputRef;
import org.apache.calcite.rex.RexLiteral;
import org.apache.calcite.rex.RexNode;
-import org.apache.calcite.sql.type.SqlTypeName;
+import org.apache.calcite.sql.SqlKind;
import org.apache.calcite.util.Util;
+import com.google.common.base.Preconditions;
+
import java.util.ArrayList;
import java.util.Collections;
+import java.util.LinkedHashSet;
import java.util.List;
+import java.util.Locale;
+import java.util.Set;
+import java.util.stream.Collectors;
-import static org.apache.calcite.sql.type.SqlTypeName.BOOLEAN_TYPES;
import static org.apache.calcite.sql.type.SqlTypeName.CHAR;
-import static org.apache.calcite.sql.type.SqlTypeName.NUMERIC_TYPES;
/**
* Implementation of
@@ -94,7 +98,7 @@ public class GeodeFilter extends Filter implements GeodeRel {
* @return String representation of the literal
*/
private static String literalValue(RexLiteral literal) {
- Object value = literal.getValue2();
+ Object value = literal.getValue3();
StringBuilder buf = new StringBuilder();
buf.append(value);
return buf.toString();
@@ -131,14 +135,137 @@ public class GeodeFilter extends Filter implements GeodeRel {
return Util.toString(predicates, "", " AND ", "");
}
+ /**
+ * Get the field name for the left node to use for IN SET query
+ */
+ private String getLeftNodeFieldName(RexNode left) {
+ switch (left.getKind()) {
+ case INPUT_REF:
+ final RexInputRef left1 = (RexInputRef) left;
+ return fieldNames.get(left1.getIndex());
+ case CAST:
+ // FIXME This will not work in all cases (for example, we ignore string encoding)
+ return getLeftNodeFieldName(((RexCall) left).operands.get(0));
+ case OTHER_FUNCTION:
+ return left.accept(new GeodeRules.RexToGeodeTranslator(this.fieldNames));
+ default:
+ return null;
+ }
+ }
+
+ /**
+ * Check if we can use IN SET Query clause to improve query performance
+ */
+ private boolean useInSetQueryClause(List<RexNode> disjunctions) {
+ // Only use the in set for more than one disjunctions
+ if (disjunctions.size() <= 1) {
+ return false;
+ }
+
+ return disjunctions.stream().allMatch(node -> {
+ // IN SET query can only be used for EQUALS
+ if (node.getKind() != SqlKind.EQUALS) {
+ return false;
+ }
+
+ RexCall call = (RexCall) node;
+ final RexNode left = call.operands.get(0);
+ final RexNode right = call.operands.get(1);
+
+ // The right node should always be literal
+ if (right.getKind() != SqlKind.LITERAL) {
+ return false;
+ }
+
+ String name = getLeftNodeFieldName(left);
+ if (name == null) {
+ return false;
+ }
+
+ return true;
+ });
+ }
+
+ /**
+ * Creates OQL IN SET predicate string
+ */
+ private String translateInSet(List<RexNode> disjunctions) {
+ Preconditions.checkArgument(
+ !disjunctions.isEmpty(), "empty disjunctions");
+
+ RexNode firstNode = disjunctions.get(0);
+ RexCall firstCall = (RexCall) firstNode;
+
+ final RexNode left = firstCall.operands.get(0);
+ String name = getLeftNodeFieldName(left);
+
+ Set<String> rightLiteralValueList = new LinkedHashSet<>();
+
+ disjunctions.forEach(node -> {
+ RexCall call = (RexCall) node;
+ RexLiteral rightLiteral = (RexLiteral) call.operands.get(1);
+
+ rightLiteralValueList.add(quoteCharLiteral(rightLiteral));
+ });
+
+ return String.format(Locale.ROOT, "%s IN SET(%s)", name,
+ String.join(", ", rightLiteralValueList));
+ }
+
+ private String getLeftNodeFieldNameForNode(RexNode node) {
+ final RexCall call = (RexCall) node;
+ final RexNode left = call.operands.get(0);
+ return getLeftNodeFieldName(left);
+ }
+
+ private List<RexNode> getLeftNodeDisjunctions(RexNode node, List<RexNode> disjunctions) {
+ List<RexNode> leftNodeDisjunctions = new ArrayList<>();
+ String leftNodeFieldName = getLeftNodeFieldNameForNode(node);
+
+ if (leftNodeFieldName != null) {
+ leftNodeDisjunctions = disjunctions.stream().filter(rexNode -> {
+ RexCall rexCall = (RexCall) rexNode;
+ RexNode rexCallLeft = rexCall.operands.get(0);
+ return leftNodeFieldName.equals(getLeftNodeFieldName(rexCallLeft));
+ }).collect(Collectors.toList());
+ }
+
+ return leftNodeDisjunctions;
+ }
+
private String translateOr(List<RexNode> disjunctions) {
List<String> predicates = new ArrayList<>();
+
+ List<String> leftFieldNameList = new ArrayList<>();
+ List<String> inSetLeftFieldNameList = new ArrayList<>();
+
for (RexNode node : disjunctions) {
- if (RelOptUtil.conjunctions(node).size() > 1) {
+ final String leftNodeFieldName = getLeftNodeFieldNameForNode(node);
+ // If any one left node is processed with IN SET predicate
+ // all the nodes are already handled
+ if (inSetLeftFieldNameList.contains(leftNodeFieldName)) {
+ continue;
+ }
+
+ List<RexNode> leftNodeDisjunctions = new ArrayList<>();
+ boolean useInSetQueryClause = false;
+
+ // In case the left field node name is already processed and not applicable
+ // for IN SET query clause, we can skip the checking
+ if (!leftFieldNameList.contains(leftNodeFieldName)) {
+ leftNodeDisjunctions = getLeftNodeDisjunctions(node, disjunctions);
+ useInSetQueryClause = useInSetQueryClause(leftNodeDisjunctions);
+ }
+
+ if (useInSetQueryClause) {
+ predicates.add(translateInSet(leftNodeDisjunctions));
+ inSetLeftFieldNameList.add(leftNodeFieldName);
+ } else if (RelOptUtil.conjunctions(node).size() > 1) {
predicates.add("(" + translateMatch(node) + ")");
} else {
predicates.add(translateMatch2(node));
}
+ leftFieldNameList.add(leftNodeFieldName);
}
return Util.toString(predicates, "", " OR ", "");
@@ -224,13 +351,7 @@ public class GeodeFilter extends Filter implements GeodeRel {
* Combines a field name, operator, and literal to produce a predicate string.
*/
private String translateOp2(String op, String name, RexLiteral right) {
- String valueString = literalValue(right);
- SqlTypeName typeName = rowType.getField(name, true, false).getType().getSqlTypeName();
- if (NUMERIC_TYPES.contains(typeName) || BOOLEAN_TYPES.contains(typeName)) {
- // leave the value as it is
- } else if (typeName != SqlTypeName.CHAR) {
- valueString = "'" + valueString + "'";
- }
+ String valueString = quoteCharLiteral(right);
return name + " " + op + " " + valueString;
}
}
http://git-wip-us.apache.org/repos/asf/calcite/blob/da57c903/geode/src/test/java/org/apache/calcite/adapter/geode/rel/GeodeAllDataTypesTest.java
----------------------------------------------------------------------
diff --git a/geode/src/test/java/org/apache/calcite/adapter/geode/rel/GeodeAllDataTypesTest.java b/geode/src/test/java/org/apache/calcite/adapter/geode/rel/GeodeAllDataTypesTest.java
new file mode 100644
index 0000000..9af131a
--- /dev/null
+++ b/geode/src/test/java/org/apache/calcite/adapter/geode/rel/GeodeAllDataTypesTest.java
@@ -0,0 +1,145 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to you under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+package org.apache.calcite.adapter.geode.rel;
+
+import org.apache.calcite.jdbc.CalciteConnection;
+import org.apache.calcite.schema.SchemaPlus;
+import org.apache.calcite.test.CalciteAssert;
+
+import org.apache.geode.cache.Cache;
+import org.apache.geode.cache.Region;
+
+import org.junit.BeforeClass;
+import org.junit.Test;
+
+import java.sql.Connection;
+import java.sql.Date;
+import java.sql.DriverManager;
+import java.sql.SQLException;
+import java.sql.Time;
+import java.sql.Timestamp;
+import java.util.Arrays;
+import java.util.Collections;
+import java.util.HashMap;
+import java.util.List;
+import java.util.Map;
+
+/**
+ * Test with different types of data like boolean, time, timestamp
+ */
+public class GeodeAllDataTypesTest extends AbstractGeodeTest {
+
+ @BeforeClass
+ public static void setUp() {
+ Cache cache = POLICY.cache();
+ Region<?, ?> region = cache.<String, Object>createRegionFactory().create("allDataTypesRegion");
+
+ List<Map> mapList = createMapList();
+
+ new JsonLoader(region).loadMapList(mapList);
+ }
+
+ private static List<Map> createMapList() {
+
+ return Arrays.asList(
+ new HashMap() {{
+ put("booleanValue", true);
+ put("dateValue", Date.valueOf("2018-02-03"));
+ put("timeValue", Time.valueOf("02:22:23"));
+ put("timestampValue", Timestamp.valueOf("2018-02-03 02:22:33"));
+ put("stringValue", "abc");
+ put("floatValue", 1.5678);
+ }},
+ new HashMap() {{
+ put("booleanValue", false);
+ put("dateValue", Date.valueOf("2018-02-04"));
+ put("timeValue", Time.valueOf("03:22:23"));
+ put("timestampValue", Timestamp.valueOf("2018-02-04 04:22:33"));
+ put("stringValue", "def");
+ put("floatValue", 3.5678);
+ }},
+ new HashMap() {{
+ put("booleanValue", true);
+ put("dateValue", Date.valueOf("2018-02-05"));
+ put("timeValue", Time.valueOf("04:22:23"));
+ put("timestampValue", Timestamp.valueOf("2018-02-05 04:22:33"));
+ put("stringValue", "ghi");
+ put("floatValue", 8.9267);
+ }}
+ );
+ }
+
+ private CalciteAssert.ConnectionFactory newConnectionFactory() {
+ return new CalciteAssert.ConnectionFactory() {
+ @Override public Connection createConnection() throws SQLException {
+ final Connection connection = DriverManager.getConnection("jdbc:calcite:lex=JAVA");
+ final SchemaPlus root = connection.unwrap(CalciteConnection.class).getRootSchema();
+
+ root.add("geode",
+ new GeodeSchema(
+ POLICY.cache(),
+ Collections.singleton("allDataTypesRegion")));
+
+ return connection;
+ }
+ };
+ }
+
+ private CalciteAssert.AssertThat calciteAssert() {
+ return CalciteAssert.that()
+ .with(newConnectionFactory());
+ }
+
+ @Test
+ public void testSqlSingleBooleanWhereFilter() {
+ calciteAssert()
+ .query("SELECT booleanValue as booleanValue "
+ + "FROM geode.allDataTypesRegion WHERE booleanValue = true")
+ .returnsCount(2)
+ .queryContains(
+ GeodeAssertions.query("SELECT booleanValue AS booleanValue FROM /allDataTypesRegion "
+ + "WHERE booleanValue = true"));
+ }
+
+ @Test
+ public void testSqlMultipleBooleanWhereFilter() {
+ calciteAssert()
+ .query("SELECT booleanValue as booleanValue "
+ + "FROM geode.allDataTypesRegion WHERE booleanValue = true OR booleanValue = false")
+ .returnsCount(3)
+ .queryContains(
+ GeodeAssertions.query("SELECT booleanValue AS booleanValue FROM /allDataTypesRegion "
+ + "WHERE booleanValue IN SET(true, false)"));
+ }
+
+ @Test
+ public void testSqlWhereWithMultipleOrForLiteralFields() {
+ calciteAssert()
+ .query("SELECT stringValue "
+ + "FROM geode.allDataTypesRegion WHERE (stringValue = 'abc' OR stringValue = 'def') OR "
+ + "(floatValue = 1.5678 OR floatValue = null) OR "
+ + "(booleanValue = true OR booleanValue = false OR booleanValue = null)")
+ .returnsCount(3)
+ .queryContains(
+ GeodeAssertions.query("SELECT stringValue AS stringValue "
+ + "FROM /allDataTypesRegion WHERE "
+ + "stringValue IN SET('abc', 'def') OR floatValue IN SET(1.5678, null) "
+ + "OR booleanValue IN SET(true, false, null)"));
+ }
+}
+
+// End GeodeAllDataTypesTest.java
http://git-wip-us.apache.org/repos/asf/calcite/blob/da57c903/geode/src/test/java/org/apache/calcite/adapter/geode/rel/GeodeBookstoreTest.java
----------------------------------------------------------------------
diff --git a/geode/src/test/java/org/apache/calcite/adapter/geode/rel/GeodeBookstoreTest.java b/geode/src/test/java/org/apache/calcite/adapter/geode/rel/GeodeBookstoreTest.java
index 12cc34e..d28b33a 100644
--- a/geode/src/test/java/org/apache/calcite/adapter/geode/rel/GeodeBookstoreTest.java
+++ b/geode/src/test/java/org/apache/calcite/adapter/geode/rel/GeodeBookstoreTest.java
@@ -73,6 +73,8 @@ public class GeodeBookstoreTest extends AbstractGeodeTest {
@Test
public void testWhereEqual() {
+ String expectedQuery = "SELECT * FROM /BookMaster WHERE itemNumber = 123";
+
calciteAssert()
.query("select * from geode.BookMaster WHERE itemNumber = 123")
.returnsCount(1)
@@ -81,7 +83,8 @@ public class GeodeBookstoreTest extends AbstractGeodeTest {
+ "Treatises\n")
.explainContains("PLAN=GeodeToEnumerableConverter\n"
+ " GeodeFilter(condition=[=(CAST($0):INTEGER, 123)])\n"
- + " GeodeTableScan(table=[[geode, BookMaster]])");
+ + " GeodeTableScan(table=[[geode, BookMaster]])")
+ .queryContains(GeodeAssertions.query(expectedQuery));
}
@Test
@@ -95,11 +98,17 @@ public class GeodeBookstoreTest extends AbstractGeodeTest {
+ "Treatises\n")
.explainContains("PLAN=GeodeToEnumerableConverter\n"
+ " GeodeFilter(condition=[AND(>($0, 122), <=($0, 123))])\n"
- + " GeodeTableScan(table=[[geode, BookMaster]])");
+ + " GeodeTableScan(table=[[geode, BookMaster]])")
+ .queryContains(
+ GeodeAssertions.query("SELECT * FROM /BookMaster "
+ + "WHERE itemNumber > 122 AND itemNumber <= 123"));
}
@Test
public void testWhereWithOr() {
+ String expectedQuery = "SELECT author AS author FROM /BookMaster "
+ + "WHERE itemNumber IN SET(123, 789)";
+
calciteAssert()
.query("select author from geode.BookMaster "
+ "WHERE itemNumber = 123 OR itemNumber = 789")
@@ -109,7 +118,9 @@ public class GeodeBookstoreTest extends AbstractGeodeTest {
+ " GeodeProject(author=[$4])\n"
+ " GeodeFilter(condition=[OR(=(CAST($0):INTEGER, 123), "
+ "=(CAST($0):INTEGER, 789))])\n"
- + " GeodeTableScan(table=[[geode, BookMaster]])\n");
+ + " GeodeTableScan(table=[[geode, BookMaster]])\n")
+ .queryContains(
+ GeodeAssertions.query(expectedQuery));
}
@Test
@@ -126,7 +137,10 @@ public class GeodeBookstoreTest extends AbstractGeodeTest {
+ "=(CAST($4):VARCHAR CHARACTER SET \"ISO-8859-1\" "
+ "COLLATE \"ISO-8859-1$en_US$primary\", 'Daisy Mae West'))])\n"
+ " GeodeTableScan(table=[[geode, BookMaster]])\n"
- + "\n");
+ + "\n")
+ .queryContains(
+ GeodeAssertions.query("SELECT author AS author FROM /BookMaster "
+ + "WHERE (itemNumber > 123 AND itemNumber = 789) OR author = 'Daisy Mae West'"));
}
// TODO: Not supported YET
@@ -151,7 +165,10 @@ public class GeodeBookstoreTest extends AbstractGeodeTest {
.explainContains("PLAN=GeodeToEnumerableConverter\n"
+ " GeodeProject(author=[$4])\n"
+ " GeodeFilter(condition=[>($0, 123)])\n"
- + " GeodeTableScan(table=[[geode, BookMaster]])");
+ + " GeodeTableScan(table=[[geode, BookMaster]])")
+ .queryContains(
+ GeodeAssertions.query("SELECT author AS author "
+ + "FROM /BookMaster WHERE itemNumber > 123"));
}
@Test
@@ -413,71 +430,106 @@ public class GeodeBookstoreTest extends AbstractGeodeTest {
.explainContains("PLAN=GeodeToEnumerableConverter\n"
+ " GeodeProject(postalCode=[ITEM($3, 'postalCode')])\n"
+ " GeodeFilter(condition=[>(ITEM($3, 'postalCode'), '0')])\n"
- + " GeodeTableScan(table=[[geode, BookCustomer]])\n");
+ + " GeodeTableScan(table=[[geode, BookCustomer]])\n")
+ .queryContains(
+ GeodeAssertions.query("SELECT primaryAddress.postalCode AS postalCode "
+ + "FROM /BookCustomer WHERE primaryAddress.postalCode > '0'"));
}
@Test
- public void testSqlSimple() throws SQLException {
+ public void testSqlSimple() {
calciteAssert()
.query("SELECT itemNumber FROM geode.BookMaster WHERE itemNumber > 123")
- .runs();
+ .runs()
+ .queryContains(
+ GeodeAssertions.query("SELECT itemNumber AS itemNumber "
+ + "FROM /BookMaster WHERE itemNumber > 123"));
}
@Test
- public void testSqlSingleNumberWhereFilter() throws SQLException {
+ public void testSqlSingleNumberWhereFilter() {
calciteAssert().query("SELECT * FROM geode.BookMaster "
- + "WHERE itemNumber = 123").runs();
+ + "WHERE itemNumber = 123")
+ .runs()
+ .queryContains(
+ GeodeAssertions.query("SELECT * FROM /BookMaster "
+ + "WHERE itemNumber = 123"));
}
@Test
- public void testSqlDistinctSort() throws SQLException {
+ public void testSqlDistinctSort() {
calciteAssert().query("SELECT DISTINCT itemNumber, author "
+ "FROM geode.BookMaster ORDER BY itemNumber, author").runs();
}
@Test
- public void testSqlDistinctSort2() throws SQLException {
+ public void testSqlDistinctSort2() {
calciteAssert().query("SELECT itemNumber, author "
+ "FROM geode.BookMaster GROUP BY itemNumber, author ORDER BY itemNumber, "
+ "author").runs();
}
@Test
- public void testSqlDistinctSort3() throws SQLException {
+ public void testSqlDistinctSort3() {
calciteAssert().query("SELECT DISTINCT * FROM geode.BookMaster").runs();
}
@Test
- public void testSqlLimit2() throws SQLException {
+ public void testSqlLimit2() {
calciteAssert().query("SELECT DISTINCT * FROM geode.BookMaster LIMIT 2").runs();
}
@Test
- public void testSqlDisjunciton() throws SQLException {
+ public void testSqlDisjunction() {
+ String expectedQuery = "SELECT author AS author FROM /BookMaster "
+ + "WHERE itemNumber IN SET(789, 123)";
+
calciteAssert().query("SELECT author FROM geode.BookMaster "
- + "WHERE itemNumber = 789 OR itemNumber = 123").runs();
+ + "WHERE itemNumber = 789 OR itemNumber = 123").runs()
+ .queryContains(
+ GeodeAssertions.query(expectedQuery));
}
@Test
- public void testSqlConjunciton() throws SQLException {
+ public void testSqlConjunction() {
calciteAssert().query("SELECT author FROM geode.BookMaster "
- + "WHERE itemNumber = 789 AND author = 'Jim Heavisides'").runs();
+ + "WHERE itemNumber = 789 AND author = 'Jim Heavisides'")
+ .runs()
+ .queryContains(
+ GeodeAssertions.query("SELECT author AS author FROM /BookMaster "
+ + "WHERE itemNumber = 789 AND author = 'Jim Heavisides'"));
}
@Test
- public void testSqlBookMasterWhere() throws SQLException {
+ public void testSqlBookMasterWhere() {
calciteAssert().query("select author, title from geode.BookMaster "
+ "WHERE author = 'Jim Heavisides' LIMIT 2")
- .runs();
+ .runs()
+ .queryContains(
+ GeodeAssertions.query("SELECT author AS author, title AS title FROM /BookMaster "
+ + "WHERE author = 'Jim Heavisides' LIMIT 2"));
}
@Test
- public void testSqlBookMasterCount() throws SQLException {
+ public void testSqlBookMasterCount() {
calciteAssert().query("select count(*) from geode.BookMaster").runs();
}
+ @Test
+ public void testInSetFilterWithNestedStringField() {
+ String expectedQuery = "SELECT primaryAddress.city AS city FROM /BookCustomer "
+ + "WHERE primaryAddress.city IN SET('Topeka', 'San Francisco')";
+
+ calciteAssert()
+ .query("SELECT primaryAddress['city'] AS city\n"
+ + "FROM geode.BookCustomer\n"
+ + "WHERE primaryAddress['city'] = 'Topeka' OR primaryAddress['city'] = 'San Francisco'\n")
+ .returnsCount(3)
+ .queryContains(
+ GeodeAssertions.query(expectedQuery));
+ }
}
// End GeodeBookstoreTest.java
http://git-wip-us.apache.org/repos/asf/calcite/blob/da57c903/geode/src/test/java/org/apache/calcite/adapter/geode/rel/GeodeZipsTest.java
----------------------------------------------------------------------
diff --git a/geode/src/test/java/org/apache/calcite/adapter/geode/rel/GeodeZipsTest.java b/geode/src/test/java/org/apache/calcite/adapter/geode/rel/GeodeZipsTest.java
index 7f23957..0737014 100644
--- a/geode/src/test/java/org/apache/calcite/adapter/geode/rel/GeodeZipsTest.java
+++ b/geode/src/test/java/org/apache/calcite/adapter/geode/rel/GeodeZipsTest.java
@@ -24,6 +24,10 @@ import org.apache.calcite.test.CalciteAssert;
import org.apache.geode.cache.Cache;
import org.apache.geode.cache.Region;
+import org.apache.geode.cache.query.Query;
+import org.apache.geode.cache.query.QueryService;
+import org.apache.geode.cache.query.SelectResults;
+import org.apache.geode.cache.query.internal.StructImpl;
import org.junit.BeforeClass;
import org.junit.Ignore;
@@ -34,6 +38,10 @@ import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.Collections;
+import java.util.LinkedHashSet;
+import java.util.Locale;
+import java.util.Set;
+import java.util.stream.Collectors;
/**
* Tests based on {@code zips-min.json} dataset. Runs automatically as part of CI.
@@ -174,7 +182,10 @@ public class GeodeZipsTest extends AbstractGeodeTest {
+ " GeodeProject(lat=[ITEM($2, 0)], lon=[ITEM($2, 1)])\n"
+ " GeodeSort(fetch=[1])\n"
+ " GeodeFilter(condition=[<(ITEM($2, 0), 0)])\n"
- + " GeodeTableScan(table=[[geode, zips]])\n");
+ + " GeodeTableScan(table=[[geode, zips]])\n")
+ .queryContains(
+ GeodeAssertions.query("SELECT loc[0] AS lat, "
+ + "loc[1] AS lon FROM /zips WHERE loc[0] < 0 LIMIT 1"));
calciteAssert()
.query("SELECT loc[0] as lat, loc[1] as lon "
@@ -184,7 +195,102 @@ public class GeodeZipsTest extends AbstractGeodeTest {
+ " GeodeProject(lat=[ITEM($2, 0)], lon=[ITEM($2, 1)])\n"
+ " GeodeSort(fetch=[1])\n"
+ " GeodeFilter(condition=[>(ITEM($2, 0), 0)])\n"
- + " GeodeTableScan(table=[[geode, zips]])\n");
+ + " GeodeTableScan(table=[[geode, zips]])\n")
+ .queryContains(
+ GeodeAssertions.query("SELECT loc[0] AS lat, "
+ + "loc[1] AS lon FROM /zips WHERE loc[0] > 0 LIMIT 1"));
+ }
+
+ @Test
+ public void testWhereWithOrForStringField() {
+ String expectedQuery = "SELECT state AS state FROM /zips "
+ + "WHERE state IN SET('MA', 'RI')";
+ calciteAssert()
+ .query("SELECT state as state "
+ + "FROM view WHERE state = 'MA' OR state = 'RI'")
+ .returnsCount(6)
+ .queryContains(
+ GeodeAssertions.query(expectedQuery));
+ }
+
+ @Test
+ public void testWhereWithOrForNumericField() {
+ calciteAssert()
+ .query("SELECT pop as pop "
+ + "FROM view WHERE pop = 34035 OR pop = 40173")
+ .returnsCount(2)
+ .queryContains(
+ GeodeAssertions.query("SELECT pop AS pop FROM /zips WHERE pop IN SET(34035, 40173)"));
+ }
+
+ @Test
+ public void testWhereWithOrForNestedNumericField() {
+ String expectedQuery = "SELECT loc[1] AS lan FROM /zips "
+ + "WHERE loc[1] IN SET(43.218525, 44.098538)";
+
+ calciteAssert()
+ .query("SELECT loc[1] as lan "
+ + "FROM view WHERE loc[1] = 43.218525 OR loc[1] = 44.098538")
+ .returnsCount(2)
+ .queryContains(
+ GeodeAssertions.query(expectedQuery));
+ }
+
+ @Test
+ public void testWhereWithOrForLargeValueList() throws Exception {
+ Cache cache = POLICY.cache();
+ QueryService queryService = cache.getQueryService();
+ Query query = queryService.newQuery("select state as state from /zips");
+ SelectResults results = (SelectResults) query.execute();
+
+ Set<String> stateList = (Set<String>) results.stream().map(s -> {
+ StructImpl struct = (StructImpl) s;
+ return struct.get("state");
+ })
+ .collect(Collectors.toCollection(LinkedHashSet::new));
+
+ String stateListPredicate = stateList.stream()
+ .map(s -> String.format(Locale.ROOT, "state = '%s'", s))
+ .collect(Collectors.joining(" OR "));
+
+ String stateListStr = "'" + String.join("', '", stateList) + "'";
+
+ String queryToBeExecuted = "SELECT state as state FROM view WHERE " + stateListPredicate;
+
+ String expectedQuery = "SELECT state AS state FROM /zips WHERE state "
+ + "IN SET(" + stateListStr + ")";
+
+ calciteAssert()
+ .query(queryToBeExecuted)
+ .returnsCount(149)
+ .queryContains(
+ GeodeAssertions.query(expectedQuery));
+ }
+
+ @Test
+ public void testSqlSingleStringWhereFilter() {
+ String expectedQuery = "SELECT state AS state FROM /zips "
+ + "WHERE state = 'NY'";
+ calciteAssert()
+ .query("SELECT state as state "
+ + "FROM view WHERE state = 'NY'")
+ .returnsCount(3)
+ .queryContains(
+ GeodeAssertions.query(expectedQuery));
+ }
+
+ @Test
+ public void testWhereWithOrWithEmptyResult() {
+ String expectedQuery = "SELECT state AS state FROM /zips "
+ + "WHERE state IN SET('', null, true, false, 123, 13.892)";
+ calciteAssert()
+ .query("SELECT state as state "
+ + "FROM view WHERE state = '' OR state = null OR "
+ + "state = true OR state = false OR state = true OR "
+ + "state = 123 OR state = 13.892")
+ .returnsCount(0)
+ .queryContains(
+ GeodeAssertions.query(expectedQuery));
}
}
http://git-wip-us.apache.org/repos/asf/calcite/blob/da57c903/geode/src/test/java/org/apache/calcite/adapter/geode/rel/JsonLoader.java
----------------------------------------------------------------------
diff --git a/geode/src/test/java/org/apache/calcite/adapter/geode/rel/JsonLoader.java b/geode/src/test/java/org/apache/calcite/adapter/geode/rel/JsonLoader.java
index ea74b48..90bb560 100644
--- a/geode/src/test/java/org/apache/calcite/adapter/geode/rel/JsonLoader.java
+++ b/geode/src/test/java/org/apache/calcite/adapter/geode/rel/JsonLoader.java
@@ -28,6 +28,8 @@ import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.Reader;
import java.nio.charset.StandardCharsets;
+import java.util.ArrayList;
+import java.util.List;
import java.util.Map;
import java.util.Objects;
@@ -51,12 +53,20 @@ class JsonLoader {
private void load(Reader reader) throws IOException {
Objects.requireNonNull(reader, "reader");
try (BufferedReader br = new BufferedReader(reader)) {
- int key = 0;
+ List<Map> mapList = new ArrayList<>();
for (String line; (line = br.readLine()) != null;) {
Map jsonMap = mapper.readValue(line, Map.class);
- PdxInstance pdxInstance = mapToPdx(rootPackage, jsonMap);
- region.put(key++, pdxInstance);
+ mapList.add(jsonMap);
}
+ loadMapList(mapList);
+ }
+ }
+
+ void loadMapList(List<Map> mapList) {
+ int key = 0;
+ for (Map jsonMap : mapList) {
+ PdxInstance pdxInstance = mapToPdx(rootPackage, jsonMap);
+ region.put(key++, pdxInstance);
}
}