@Test public void testLiteral() { checkLiteral("DATE '1978-05-02'"); checkLiteral2("DATE '1978-5-2'", "DATE '1978-05-02'"); checkLiteral("TIME '12:34:56'"); checkLiteral("TIME '12:34:56.78'"); checkLiteral2("TIME '1:4:6.080'", "TIME '01:04:06.080'"); checkLiteral("TIMESTAMP '1978-05-02 12:34:56.78'"); checkLiteral2("TIMESTAMP '1978-5-2 2:4:6.80'", "TIMESTAMP '1978-05-02 02:04:06.80'"); checkLiteral("'I can''t explain'"); checkLiteral("''"); checkLiteral("TRUE"); checkLiteral("123"); checkLiteral("123.45"); checkLiteral("-123.45"); checkLiteral("INTERVAL '1-2' YEAR TO MONTH"); checkLiteral("INTERVAL -'1-2' YEAR TO MONTH"); checkLiteral("INTERVAL '12-11' YEAR TO MONTH"); checkLiteral("INTERVAL '1' YEAR"); checkLiteral("INTERVAL '1' MONTH"); checkLiteral("INTERVAL '12' DAY"); checkLiteral("INTERVAL -'12' DAY"); checkLiteral2("INTERVAL '1 2' DAY TO HOUR", "INTERVAL '1 02' DAY TO HOUR"); checkLiteral2("INTERVAL '1 2:10' DAY TO MINUTE", "INTERVAL '1 02:10' DAY TO MINUTE"); checkLiteral2("INTERVAL '1 2:00' DAY TO MINUTE", "INTERVAL '1 02:00' DAY TO MINUTE"); checkLiteral2("INTERVAL '1 2:34:56' DAY TO SECOND", "INTERVAL '1 02:34:56' DAY TO SECOND");
@Test public void testJethroDataSelectQueryWithOrderByDescAndNullsFirstShouldBeEmulated() { final String query = "select \"product_id\" from \"product\"\n" + "order by \"product_id\" desc nulls first"; final String expected = "SELECT \"product_id\"\n" + "FROM \"foodmart\".\"product\"\n" + "ORDER BY \"product_id\", \"product_id\" DESC"; sql(query).dialect(jethroDataSqlDialect()).ok(expected); }
@Test public void testMySqlWithHighNullsSelectWithOrderByAscNullsLastAndNoEmulation() { final String query = "select \"product_id\" from \"product\"\n" + "order by \"product_id\" nulls last"; final String expected = "SELECT `product_id`\n" + "FROM `foodmart`.`product`\n" + "ORDER BY `product_id`"; sql(query).dialect(mySqlDialect(NullCollation.HIGH)).ok(expected); }
@Test public void testSelectQueryWithMinAggregateFunction1() { String query = "select \"product_class_id\", min(\"net_weight\") from" + " \"product\" group by \"product_class_id\""; final String expected = "SELECT \"product_class_id\", MIN(\"net_weight\")\n" + "FROM \"foodmart\".\"product\"\n" + "GROUP BY \"product_class_id\""; sql(query).ok(expected); }
/** * Tests that IN can be un-parsed. * * <p>This cannot be tested using "sql", because because Calcite's SQL parser * replaces INs with ORs or sub-queries. */ @Test public void testUnparseIn1() { final RelBuilder builder = relBuilder().scan("EMP"); final RexNode condition = builder.call(SqlStdOperatorTable.IN, builder.field("DEPTNO"), builder.literal(21)); final RelNode root = relBuilder().scan("EMP").filter(condition).build(); final String sql = toSql(root); final String expectedSql = "SELECT *\n" + "FROM \"scott\".\"EMP\"\n" + "WHERE \"DEPTNO\" IN (21)"; assertThat(sql, isLinux(expectedSql)); }
private void checkLiteral(String expression) { checkLiteral2(expression, expression); }
@Test public void testUnparseIn2() { final RexNode filter = builder.call(SqlStdOperatorTable.IN, builder.field("DEPTNO"), builder.literal(20), builder.literal(21)); final String sql = unparseRelTree(empScan.filter(filter).build()); final String expectedSql = "SELECT *\n" + "FROM \"scott\".\"EMP\"\n" + "WHERE \"DEPTNO\" IN (20, 21)"; assertThat(sql, isLinux(expectedSql)); }
/** Converts a relational expression to SQL. */ private String toSql(RelNode root) { return toSql(root, SqlDialect.DatabaseProduct.CALCITE.getDialect()); }
@Test public void testSelectQueryWithMultipleAggregateFunction() { String query = "select sum(\"net_weight\"), min(\"low_fat\"), count(*)" + " from \"product\" group by \"product_class_id\" "; final String expected = "SELECT SUM(\"net_weight\"), MIN(\"low_fat\")," + " COUNT(*)\n" + "FROM \"foodmart\".\"product\"\n" + "GROUP BY \"product_class_id\""; sql(query).ok(expected); }
@Test public void testUnparseInStruct1() { final RelBuilder builder = relBuilder().scan("EMP"); final RexNode condition = builder.call(SqlStdOperatorTable.IN, builder.call(SqlStdOperatorTable.ROW, builder.field("DEPTNO"), builder.field("JOB")), builder.call(SqlStdOperatorTable.ROW, builder.literal(1), builder.literal("PRESIDENT"))); final RelNode root = relBuilder().scan("EMP").filter(condition).build(); final String sql = toSql(root); final String expectedSql = "SELECT *\n" + "FROM \"scott\".\"EMP\"\n" + "WHERE ROW(\"DEPTNO\", \"JOB\") IN (ROW(1, 'PRESIDENT'))"; assertThat(sql, isLinux(expectedSql)); }
private void checkLiteral(String expression) { checkLiteral2(expression, expression); }
/** * Tests that IN can be un-parsed. * * <p>This cannot be tested using "sql", because because Calcite's SQL parser * replaces INs with ORs or sub-queries. */ @Test public void testUnparseIn1() { final RexNode condition = builder.call(SqlStdOperatorTable.IN, builder.field("DEPTNO"), builder.literal(21)); final String sql = unparseRelTree(empScan.filter(condition).build()); final String expectedSql = "SELECT *\n" + "FROM \"scott\".\"EMP\"\n" + "WHERE \"DEPTNO\" IN (21)"; assertThat(sql, isLinux(expectedSql)); }
@Test public void testMySqlWithHighNullsSelectWithOrderByAscNullsFirstAndNullEmulation() { final String query = "select \"product_id\" from \"product\"\n" + "order by \"product_id\" nulls first"; final String expected = "SELECT `product_id`\n" + "FROM `foodmart`.`product`\n" + "ORDER BY `product_id` IS NULL DESC, `product_id`"; sql(query).dialect(mySqlDialect(NullCollation.HIGH)).ok(expected); }
@Test public void testSelectQueryWithParameters() { String query = "select * from \"product\" " + "where \"product_id\" = ? " + "AND ? >= \"shelf_width\""; final String expected = "SELECT *\n" + "FROM \"foodmart\".\"product\"\n" + "WHERE \"product_id\" = ? " + "AND ? >= \"shelf_width\""; sql(query).ok(expected); }
@Test public void testJethroDataSelectQueryWithOrderByDescAndNullsFirstShouldBeEmulated() { final String query = "select \"product_id\" from \"product\"\n" + "order by \"product_id\" desc nulls first"; final String expected = "SELECT \"product_id\"\n" + "FROM \"foodmart\".\"product\"\n" + "ORDER BY \"product_id\", \"product_id\" DESC"; sql(query).dialect(jethroDataSqlDialect()).ok(expected); }
@Test public void testLiteral() { checkLiteral("DATE '1978-05-02'"); checkLiteral2("DATE '1978-5-2'", "DATE '1978-05-02'"); checkLiteral("TIME '12:34:56'"); checkLiteral("TIME '12:34:56.78'"); checkLiteral2("TIME '1:4:6.080'", "TIME '01:04:06.080'"); checkLiteral("TIMESTAMP '1978-05-02 12:34:56.78'"); checkLiteral2("TIMESTAMP '1978-5-2 2:4:6.80'", "TIMESTAMP '1978-05-02 02:04:06.80'"); checkLiteral("'I can''t explain'"); checkLiteral("''"); checkLiteral("TRUE"); checkLiteral("123"); checkLiteral("123.45"); checkLiteral("-123.45"); checkLiteral("INTERVAL '1-2' YEAR TO MONTH"); checkLiteral("INTERVAL -'1-2' YEAR TO MONTH"); checkLiteral("INTERVAL '12-11' YEAR TO MONTH"); checkLiteral("INTERVAL '1' YEAR"); checkLiteral("INTERVAL '1' MONTH"); checkLiteral("INTERVAL '12' DAY"); checkLiteral("INTERVAL -'12' DAY"); checkLiteral2("INTERVAL '1 2' DAY TO HOUR", "INTERVAL '1 02' DAY TO HOUR"); checkLiteral2("INTERVAL '1 2:10' DAY TO MINUTE", "INTERVAL '1 02:10' DAY TO MINUTE"); checkLiteral2("INTERVAL '1 2:00' DAY TO MINUTE", "INTERVAL '1 02:00' DAY TO MINUTE"); checkLiteral2("INTERVAL '1 2:34:56' DAY TO SECOND", "INTERVAL '1 02:34:56' DAY TO SECOND");
@Test public void testUnparseIn2() { final RelBuilder builder = relBuilder(); final RelNode rel = builder .scan("EMP") .filter( builder.call(SqlStdOperatorTable.IN, builder.field("DEPTNO"), builder.literal(20), builder.literal(21))) .build(); final String sql = toSql(rel); final String expectedSql = "SELECT *\n" + "FROM \"scott\".\"EMP\"\n" + "WHERE \"DEPTNO\" IN (20, 21)"; assertThat(sql, isLinux(expectedSql)); }
@Test public void testUnparseInStruct1() { final RexNode condition = builder.call(SqlStdOperatorTable.IN, builder.call(SqlStdOperatorTable.ROW, builder.field("DEPTNO"), builder.field("JOB")), builder.call(SqlStdOperatorTable.ROW, builder.literal(1), builder.literal("PRESIDENT"))); final String sql = unparseRelTree(empScan.filter(condition).build()); final String expectedSql = "SELECT *\n" + "FROM \"scott\".\"EMP\"\n" + "WHERE ROW(\"DEPTNO\", \"JOB\") IN (ROW(1, 'PRESIDENT'))"; assertThat(sql, isLinux(expectedSql)); }
@Test public void testMySqlWithHighNullsSelectWithOrderByAscNullsFirstAndNullEmulation() { final String query = "select \"product_id\" from \"product\"\n" + "order by \"product_id\" nulls first"; final String expected = "SELECT `product_id`\n" + "FROM `foodmart`.`product`\n" + "ORDER BY `product_id` IS NULL DESC, `product_id`"; sql(query).dialect(mySqlDialect(NullCollation.HIGH)).ok(expected); }
@Test public void testSimpleSelectQueryFromProductTable() { String query = "select \"product_id\", \"product_class_id\" from \"product\""; final String expected = "SELECT \"product_id\", \"product_class_id\"\n" + "FROM \"foodmart\".\"product\""; sql(query).ok(expected); }