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 <[email protected]> Authored: Sat Nov 24 12:43:24 2018 +0530 Committer: Andrei Sereda <[email protected]> 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); } }
