Tests that project and filter based on nested fields (upgrades to Optiq 0.2 for map and late-binding functionality).
Signed-off-by: Jacques Nadeau <[email protected]> Project: http://git-wip-us.apache.org/repos/asf/incubator-drill/repo Commit: http://git-wip-us.apache.org/repos/asf/incubator-drill/commit/7c292ddc Tree: http://git-wip-us.apache.org/repos/asf/incubator-drill/tree/7c292ddc Diff: http://git-wip-us.apache.org/repos/asf/incubator-drill/diff/7c292ddc Branch: refs/heads/execwork Commit: 7c292ddc2a3ee16f79d3c7bbe9b36afee8c4d77a Parents: 673a96d Author: Julian Hyde <[email protected]> Authored: Sun Mar 3 01:46:13 2013 -0800 Committer: Jacques Nadeau <[email protected]> Committed: Thu Jun 6 11:06:42 2013 -0700 ---------------------------------------------------------------------- sandbox/prototype/sqlparser/pom.xml | 2 +- .../java/org/apache/drill/jdbc/DrillTable.java | 12 ++- .../java/org/apache/drill/jdbc/test/JdbcTest.java | 82 ++++++++++++++- 3 files changed, 85 insertions(+), 11 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/incubator-drill/blob/7c292ddc/sandbox/prototype/sqlparser/pom.xml ---------------------------------------------------------------------- diff --git a/sandbox/prototype/sqlparser/pom.xml b/sandbox/prototype/sqlparser/pom.xml index 4bc576b..2e65585 100644 --- a/sandbox/prototype/sqlparser/pom.xml +++ b/sandbox/prototype/sqlparser/pom.xml @@ -29,7 +29,7 @@ <dependency> <groupId>net.hydromatic</groupId> <artifactId>optiq</artifactId> - <version>0.1</version> + <version>0.2</version> </dependency> <dependency> <groupId>net.hydromatic</groupId> http://git-wip-us.apache.org/repos/asf/incubator-drill/blob/7c292ddc/sandbox/prototype/sqlparser/src/main/java/org/apache/drill/jdbc/DrillTable.java ---------------------------------------------------------------------- diff --git a/sandbox/prototype/sqlparser/src/main/java/org/apache/drill/jdbc/DrillTable.java b/sandbox/prototype/sqlparser/src/main/java/org/apache/drill/jdbc/DrillTable.java index f530ea8..cb0512d 100644 --- a/sandbox/prototype/sqlparser/src/main/java/org/apache/drill/jdbc/DrillTable.java +++ b/sandbox/prototype/sqlparser/src/main/java/org/apache/drill/jdbc/DrillTable.java @@ -59,8 +59,7 @@ public class DrillTable extends BaseQueryable<Object> RelDataType rowType, String name, StorageEngineConfig storageEngineConfig, - Object selection - ) { + Object selection) { super(schema.getQueryProvider(), elementType, expression); this.schema = schema; this.name = name; @@ -78,10 +77,13 @@ public class DrillTable extends BaseQueryable<Object> final RelDataType rowType = typeFactory.createStructType( Collections.singletonList( - typeFactory.createSqlType(SqlTypeName.ANY)), - Collections.singletonList("_extra")); + typeFactory.createMapType( + typeFactory.createSqlType(SqlTypeName.VARCHAR), + typeFactory.createSqlType(SqlTypeName.ANY))), + Collections.singletonList("_MAP")); final DrillTable table = - new DrillTable(schema, Object.class, call, rowType, name, storageEngineConfig, selection); + new DrillTable(schema, Object.class, call, rowType, name, + storageEngineConfig, selection); schema.addTable(name, table); } http://git-wip-us.apache.org/repos/asf/incubator-drill/blob/7c292ddc/sandbox/prototype/sqlparser/src/test/java/org/apache/drill/jdbc/test/JdbcTest.java ---------------------------------------------------------------------- diff --git a/sandbox/prototype/sqlparser/src/test/java/org/apache/drill/jdbc/test/JdbcTest.java b/sandbox/prototype/sqlparser/src/test/java/org/apache/drill/jdbc/test/JdbcTest.java index eb0b51a..24aa707 100644 --- a/sandbox/prototype/sqlparser/src/test/java/org/apache/drill/jdbc/test/JdbcTest.java +++ b/sandbox/prototype/sqlparser/src/test/java/org/apache/drill/jdbc/test/JdbcTest.java @@ -23,6 +23,13 @@ import java.sql.*; /** Unit tests for Drill's JDBC driver. */ public class JdbcTest extends TestCase { + private static final String EXPECTED = + "_MAP={donuts={batters={batter=[{id=1001, type=Regular}, {id=1002, type=Chocolate}, {id=1003, type=Blueberry}, {id=1004, type=Devil's Food}]}, id=0001, name=Cake, ppu=0.55, sales=35, topping=[{id=5001, type=None}, {id=5002, type=Glazed}, {id=5005, type=Sugar}, {id=5007, type=Powdered Sugar}, {id=5006, type=Chocolate with Sprinkles}, {id=5003, type=Chocolate}, {id=5004, type=Maple}], type=donut}}\n" + + "_MAP={donuts={batters={batter=[{id=1001, type=Regular}]}, id=0002, name=Raised, ppu=0.69, sales=145, topping=[{id=5001, type=None}, {id=5002, type=Glazed}, {id=5005, type=Sugar}, {id=5003, type=Chocolate}, {id=5004, type=Maple}], type=donut}}\n" + + "_MAP={donuts={batters={batter=[{id=1001, type=Regular}, {id=1002, type=Chocolate}]}, id=0003, name=Old Fashioned, ppu=0.55, sales=300, topping=[{id=5001, type=None}, {id=5002, type=Glazed}, {id=5003, type=Chocolate}, {id=5004, type=Maple}], type=donut}}\n" + + "_MAP={donuts={batters={batter=[{id=1001, type=Regular}, {id=1002, type=Chocolate}, {id=1003, type=Blueberry}, {id=1004, type=Devil's Food}]}, filling=[{id=6001, type=None}, {id=6002, type=Raspberry}, {id=6003, type=Lemon}, {id=6004, type=Chocolate}, {id=6005, type=Kreme}], id=0004, name=Filled, ppu=0.69, sales=14, topping=[{id=5001, type=None}, {id=5002, type=Glazed}, {id=5005, type=Sugar}, {id=5007, type=Powdered Sugar}, {id=5006, type=Chocolate with Sprinkles}, {id=5003, type=Chocolate}, {id=5004, type=Maple}], type=donut}}\n" + + "_MAP={donuts={batters={batter=[{id=1001, type=Regular}]}, id=0005, name=Apple Fritter, ppu=1.0, sales=700, topping=[{id=5002, type=Glazed}], type=donut}}\n"; + /** Load driver. */ public void testLoadDriver() throws ClassNotFoundException { Class.forName("org.apache.drill.jdbc.Driver"); @@ -56,17 +63,82 @@ public class JdbcTest extends TestCase { final ResultSet resultSet = statement.executeQuery( "select * from donuts"); assertEquals( - "_extra={donuts={batters={batter=[{id=1001, type=Regular}, {id=1002, type=Chocolate}, {id=1003, type=Blueberry}, {id=1004, type=Devil's Food}]}, id=0001, name=Cake, ppu=0.55, sales=35, topping=[{id=5001, type=None}, {id=5002, type=Glazed}, {id=5005, type=Sugar}, {id=5007, type=Powdered Sugar}, {id=5006, type=Chocolate with Sprinkles}, {id=5003, type=Chocolate}, {id=5004, type=Maple}], type=donut}}\n" - + "_extra={donuts={batters={batter=[{id=1001, type=Regular}]}, id=0002, name=Raised, ppu=0.69, sales=145, topping=[{id=5001, type=None}, {id=5002, type=Glazed}, {id=5005, type=Sugar}, {id=5003, type=Chocolate}, {id=5004, type=Maple}], type=donut}}\n" - + "_extra={donuts={batters={batter=[{id=1001, type=Regular}, {id=1002, type=Chocolate}]}, id=0003, name=Old Fashioned, ppu=0.55, sales=300, topping=[{id=5001, type=None}, {id=5002, type=Glazed}, {id=5003, type=Chocolate}, {id=5004, type=Maple}], type=donut}}\n" - + "_extra={donuts={batters={batter=[{id=1001, type=Regular}, {id=1002, type=Chocolate}, {id=1003, type=Blueberry}, {id=1004, type=Devil's Food}]}, filling=[{id=6001, type=None}, {id=6002, type=Raspberry}, {id=6003, type=Lemon}, {id=6004, type=Chocolate}, {id=6005, type=Kreme}], id=0004, name=Filled, ppu=0.69, sales=14, topping=[{id=5001, type=None}, {id=5002, type=Glazed}, {id=5005, type=Sugar}, {id=5007, type=Powdered Sugar}, {id=5006, type=Chocolate with Sprinkles}, {id=5003, type=Chocolate}, {id=5004, type=Maple}], type=donut}}\n" - + "_extra={donuts={batters={batter=[{id=1001, type=Regular}]}, id=0005, name=Apple Fritter, ppu=1.0, sales=700, topping=[{id=5002, type=Glazed}], type=donut}}\n", + EXPECTED, toString(resultSet)); resultSet.close(); statement.close(); connection.close(); } + /** Query with project list. No field references yet. */ + public void testProjectConstant() throws Exception { + assertSqlReturns( + "select 1 + 3 as c from donuts", + "C=4\n" + + "C=4\n" + + "C=4\n" + + "C=4\n" + + "C=4\n"); + } + + /** Query that projects an element from the map. */ + public void testProject() throws Exception { + assertSqlReturns( + "select _MAP['donuts']['ppu'] as ppu from donuts", + "PPU=0.55\n" + + "PPU=0.69\n" + + "PPU=0.55\n" + + "PPU=0.69\n" + + "PPU=1.0\n"); + } + + /** Query with subquery, filter, and projection of one real and one + * nonexistent field from a map field. */ + public void testProjectFilterSubquery() throws Exception { + assertSqlReturns( + "select d['name'] as name, d['xx'] as xx from (\n" + + " select _MAP['donuts'] as d from donuts)\n" + + "where cast(d['ppu'] as double) > 0.6", + "NAME=Raised; XX=null\n" + + "NAME=Filled; XX=null\n" + + "NAME=Apple Fritter; XX=null\n"); + } + + /** Query that projects one field. (Disabled; uses sugared syntax.) */ + public void _testProjectNestedFieldSugared() throws Exception { + assertSqlReturns( + "select donuts.ppu from donuts", + "C=4\n" + + "C=4\n" + + "C=4\n" + + "C=4\n" + + "C=4\n"); + } + + /** Query with filter. No field references yet. */ + public void testFilterConstant() throws Exception { + assertSqlReturns( + "select * from donuts where 3 > 4", + ""); + assertSqlReturns( + "select * from donuts where 3 < 4", + EXPECTED); + } + + private void assertSqlReturns(String sql, + String expected) throws ClassNotFoundException, SQLException + { + Class.forName("org.apache.drill.jdbc.Driver"); + final Connection connection = DriverManager.getConnection( + "jdbc:drill:schema=DONUTS;tables=DONUTS"); + try (Statement statement = connection.createStatement(); + ResultSet resultSet = statement.executeQuery(sql)) { + assertEquals(expected, toString(resultSet)); + } finally { + connection.close(); + } + } + static String toString(ResultSet resultSet) throws SQLException { StringBuilder buf = new StringBuilder(); while (resultSet.next()) {
