[CALCITE-2709] In Geode adapter, allow filtering on DATE, TIME, TIMESTAMP fields (Sandeep Chada)
GeodeFilter changes to support filtering with datetime types (DATE, TIME, TIMESTAMP). Close apache/calcite#952 Project: http://git-wip-us.apache.org/repos/asf/calcite/repo Commit: http://git-wip-us.apache.org/repos/asf/calcite/commit/be540471 Tree: http://git-wip-us.apache.org/repos/asf/calcite/tree/be540471 Diff: http://git-wip-us.apache.org/repos/asf/calcite/diff/be540471 Branch: refs/heads/master Commit: be5404713bbcd8cdc19f2651af33707b895d2c04 Parents: 25c332d Author: chadasa <[email protected]> Authored: Sat Dec 1 20:44:09 2018 +0530 Committer: Julian Hyde <[email protected]> Committed: Mon Dec 3 10:57:14 2018 -0800 ---------------------------------------------------------------------- .../calcite/adapter/geode/rel/GeodeFilter.java | 24 +- .../geode/rel/GeodeAllDataTypesTest.java | 233 ++++++++++++++++--- .../calcite/adapter/geode/rel/JsonLoader.java | 9 +- 3 files changed, 225 insertions(+), 41 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/calcite/blob/be540471/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 9887306..91435fe 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 @@ -30,6 +30,9 @@ import org.apache.calcite.rex.RexInputRef; import org.apache.calcite.rex.RexLiteral; import org.apache.calcite.rex.RexNode; import org.apache.calcite.sql.SqlKind; +import org.apache.calcite.util.DateString; +import org.apache.calcite.util.TimeString; +import org.apache.calcite.util.TimestampString; import org.apache.calcite.util.Util; import com.google.common.base.Preconditions; @@ -98,10 +101,23 @@ public class GeodeFilter extends Filter implements GeodeRel { * @return String representation of the literal */ private static String literalValue(RexLiteral literal) { - Object value = literal.getValue3(); - StringBuilder buf = new StringBuilder(); - buf.append(value); - return buf.toString(); + final Comparable valueComparable = literal.getValueAs(Comparable.class); + + switch (literal.getTypeName()) { + case TIMESTAMP: + case TIMESTAMP_WITH_LOCAL_TIME_ZONE: + assert valueComparable instanceof TimestampString; + return "TIMESTAMP '" + valueComparable.toString() + "'"; + case DATE: + assert valueComparable instanceof DateString; + return "DATE '" + valueComparable.toString() + "'"; + case TIME: + case TIME_WITH_LOCAL_TIME_ZONE: + assert valueComparable instanceof TimeString; + return "TIME '" + valueComparable.toString() + "'"; + default: + return String.valueOf(literal.getValue3()); + } } /** http://git-wip-us.apache.org/repos/asf/calcite/blob/be540471/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 index 9af131a..41700e5 100644 --- 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 @@ -23,6 +23,9 @@ import org.apache.calcite.test.CalciteAssert; import org.apache.geode.cache.Cache; import org.apache.geode.cache.Region; +import com.google.common.collect.ImmutableList; +import com.google.common.collect.ImmutableMap; + import org.junit.BeforeClass; import org.junit.Test; @@ -32,9 +35,7 @@ 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; @@ -45,42 +46,42 @@ public class GeodeAllDataTypesTest extends AbstractGeodeTest { @BeforeClass public static void setUp() { - Cache cache = POLICY.cache(); - Region<?, ?> region = cache.<String, Object>createRegionFactory().create("allDataTypesRegion"); + final Cache cache = POLICY.cache(); + final Region<?, ?> region = + cache.<String, Object>createRegionFactory() + .create("allDataTypesRegion"); - List<Map> mapList = createMapList(); + final List<Map<String, Object>> 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 static List<Map<String, Object>> createMapList() { + return ImmutableList.of( + ImmutableMap.<String, Object>builder() + .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) + .build(), + ImmutableMap.<String, Object>builder() + .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) + .build(), + ImmutableMap.<String, Object>builder() + .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) + .build()); } private CalciteAssert.ConnectionFactory newConnectionFactory() { @@ -140,6 +141,172 @@ public class GeodeAllDataTypesTest extends AbstractGeodeTest { + "stringValue IN SET('abc', 'def') OR floatValue IN SET(1.5678, null) " + "OR booleanValue IN SET(true, false, null)")); } + + @Test + public void testSqlSingleDateWhereFilter() { + calciteAssert() + .query("SELECT dateValue\n" + + "FROM geode.allDataTypesRegion\n" + + "WHERE dateValue = DATE '2018-02-03'") + .returnsCount(1) + .queryContains( + GeodeAssertions.query("SELECT dateValue AS dateValue " + + "FROM /allDataTypesRegion " + + "WHERE dateValue = DATE '2018-02-03'")); + + calciteAssert() + .query("SELECT dateValue\n" + + "FROM geode.allDataTypesRegion\n" + + "WHERE dateValue > DATE '2018-02-03'") + .returnsCount(2) + .queryContains( + GeodeAssertions.query("SELECT dateValue AS dateValue " + + "FROM /allDataTypesRegion " + + "WHERE dateValue > DATE '2018-02-03'")); + + calciteAssert() + .query("SELECT dateValue\n" + + "FROM geode.allDataTypesRegion\n" + + "WHERE dateValue < DATE '2018-02-03'") + .returnsCount(0) + .queryContains( + GeodeAssertions.query("SELECT dateValue AS dateValue " + + "FROM /allDataTypesRegion " + + "WHERE dateValue < DATE '2018-02-03'")); + } + + @Test + public void testSqlMultipleDateWhereFilter() { + calciteAssert() + .query("SELECT dateValue\n" + + "FROM geode.allDataTypesRegion\n" + + "WHERE dateValue = DATE '2018-02-03'\n" + + " OR dateValue = DATE '2018-02-04'") + .returnsCount(2) + .queryContains( + GeodeAssertions.query("SELECT dateValue AS dateValue " + + "FROM /allDataTypesRegion " + + "WHERE dateValue IN SET(DATE '2018-02-03'," + + " DATE '2018-02-04')")); + } + + @Test + public void testSqlSingleTimeWhereFilter() { + calciteAssert() + .query("SELECT timeValue\n" + + "FROM geode.allDataTypesRegion\n" + + "WHERE timeValue = TIME '02:22:23'") + .returnsCount(1) + .queryContains( + GeodeAssertions.query("SELECT timeValue AS timeValue " + + "FROM /allDataTypesRegion " + + "WHERE timeValue = TIME '02:22:23'")); + + calciteAssert() + .query("SELECT timeValue\n" + + "FROM geode.allDataTypesRegion\n" + + "WHERE timeValue > TIME '02:22:23'") + .returnsCount(2) + .queryContains( + GeodeAssertions.query("SELECT timeValue AS timeValue " + + "FROM /allDataTypesRegion " + + "WHERE timeValue > TIME '02:22:23'")); + + calciteAssert() + .query("SELECT timeValue\n" + + "FROM geode.allDataTypesRegion\n" + + "WHERE timeValue < TIME '02:22:23'") + .returnsCount(0) + .queryContains( + GeodeAssertions.query("SELECT timeValue AS timeValue " + + "FROM /allDataTypesRegion " + + "WHERE timeValue < TIME '02:22:23'")); + } + + @Test + public void testSqlMultipleTimeWhereFilter() { + calciteAssert() + .query("SELECT timeValue\n" + + "FROM geode.allDataTypesRegion\n" + + "WHERE timeValue = TIME '02:22:23'\n" + + " OR timeValue = TIME '03:22:23'") + .returnsCount(2) + .queryContains( + GeodeAssertions.query("SELECT timeValue AS timeValue " + + "FROM /allDataTypesRegion " + + "WHERE timeValue IN SET(TIME '02:22:23', TIME '03:22:23')")); + } + + @Test + public void testSqlSingleTimestampWhereFilter() { + calciteAssert() + .query("SELECT timestampValue\n" + + "FROM geode.allDataTypesRegion\n" + + "WHERE timestampValue = TIMESTAMP '2018-02-03 02:22:33'") + .returnsCount(1) + .queryContains( + GeodeAssertions.query("SELECT timestampValue AS timestampValue " + + "FROM /allDataTypesRegion " + + "WHERE timestampValue = TIMESTAMP '2018-02-03 02:22:33'")); + + calciteAssert() + .query("SELECT timestampValue\n" + + "FROM geode.allDataTypesRegion\n" + + "WHERE timestampValue > TIMESTAMP '2018-02-03 02:22:33'") + .returnsCount(2) + .queryContains( + GeodeAssertions.query("SELECT timestampValue AS timestampValue " + + "FROM /allDataTypesRegion " + + "WHERE timestampValue > TIMESTAMP '2018-02-03 02:22:33'")); + + calciteAssert() + .query("SELECT timestampValue\n" + + "FROM geode.allDataTypesRegion\n" + + "WHERE timestampValue < TIMESTAMP '2018-02-03 02:22:33'") + .returnsCount(0) + .queryContains( + GeodeAssertions.query("SELECT timestampValue AS timestampValue " + + "FROM /allDataTypesRegion " + + "WHERE timestampValue < TIMESTAMP '2018-02-03 02:22:33'")); + } + + @Test + public void testSqlMultipleTimestampWhereFilter() { + calciteAssert() + .query("SELECT timestampValue\n" + + "FROM geode.allDataTypesRegion\n" + + "WHERE timestampValue = TIMESTAMP '2018-02-03 02:22:33'\n" + + " OR timestampValue = TIMESTAMP '2018-02-05 04:22:33'") + .returnsCount(2) + .queryContains( + GeodeAssertions.query("SELECT timestampValue AS timestampValue " + + "FROM /allDataTypesRegion " + + "WHERE timestampValue IN SET(" + + "TIMESTAMP '2018-02-03 02:22:33', " + + "TIMESTAMP '2018-02-05 04:22:33')")); + } + + @Test + public void testSqlWhereWithMultipleOrForAllFields() { + calciteAssert() + .query("SELECT stringValue " + + "FROM geode.allDataTypesRegion WHERE (stringValue = 'abc' OR stringValue = 'def') OR " + + "(floatValue = 1.5678 OR floatValue = null) OR " + + "(dateValue = DATE '2018-02-05' OR dateValue = DATE '2018-02-06' ) OR " + + "(timeValue = TIME '03:22:23' OR timeValue = TIME '07:22:23') OR " + + "(timestampValue = TIMESTAMP '2018-02-05 04:22:33' OR " + + "timestampValue = TIMESTAMP '2017-02-05 04:22:33') 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 dateValue " + + "IN SET(DATE '2018-02-05', DATE '2018-02-06') OR timeValue " + + "IN SET(TIME '03:22:23', TIME '07:22:23') OR timestampValue " + + "IN SET(TIMESTAMP '2018-02-05 04:22:33', TIMESTAMP '2017-02-05 04:22:33') " + + "OR booleanValue IN SET(true, false, null)")); + } } // End GeodeAllDataTypesTest.java http://git-wip-us.apache.org/repos/asf/calcite/blob/be540471/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 90bb560..235e3e3 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 @@ -53,18 +53,19 @@ class JsonLoader { private void load(Reader reader) throws IOException { Objects.requireNonNull(reader, "reader"); try (BufferedReader br = new BufferedReader(reader)) { - List<Map> mapList = new ArrayList<>(); + List<Map<String, Object>> mapList = new ArrayList<>(); for (String line; (line = br.readLine()) != null;) { - Map jsonMap = mapper.readValue(line, Map.class); + @SuppressWarnings("unchecked") + Map<String, Object> jsonMap = mapper.readValue(line, Map.class); mapList.add(jsonMap); } loadMapList(mapList); } } - void loadMapList(List<Map> mapList) { + void loadMapList(List<Map<String, Object>> mapList) { int key = 0; - for (Map jsonMap : mapList) { + for (Map<String, Object> jsonMap : mapList) { PdxInstance pdxInstance = mapToPdx(rootPackage, jsonMap); region.put(key++, pdxInstance); }
