/** Initiates a test case with a given SQL query. */ private Sql sql(String sql) { return new Sql(CalciteAssert.SchemaSpec.JDBC_FOODMART, sql, CalciteSqlDialect.DEFAULT, DEFAULT_REL_CONFIG, ImmutableList.of()); }
Sql throws_(String errorMessage) { try { final String s = exec(); throw new AssertionError("Expected exception with message `" + errorMessage + "` but nothing was thrown; got " + s); } catch (Exception e) { assertThat(e.getMessage(), is(errorMessage)); return this; } }
/** Test case for * <a href="https://issues.apache.org/jira/browse/CALCITE-2652">[CALCITE-2652] * SqlNode to SQL conversion fails if the join condition references a BOOLEAN * column</a>. */ @Test public void testJoinOnBoolean() { final String sql = "SELECT 1\n" + "from emps\n" + "join emp on (emp.deptno = emps.empno and manager)"; final String s = sql(sql).schema(CalciteAssert.SchemaSpec.POST).exec(); assertThat(s, notNullValue()); // sufficient that conversion did not throw }
/** Test case for * <a href="https://issues.apache.org/jira/browse/CALCITE-1586">[CALCITE-1586] * JDBC adapter generates wrong SQL if UNION has more than two inputs</a>. */ @Test public void testThreeQueryUnion() { String query = "SELECT \"product_id\" FROM \"product\" " + " UNION ALL " + "SELECT \"product_id\" FROM \"sales_fact_1997\" " + " UNION ALL " + "SELECT \"product_class_id\" AS product_id FROM \"product_class\""; String expected = "SELECT \"product_id\"\n" + "FROM \"foodmart\".\"product\"\n" + "UNION ALL\n" + "SELECT \"product_id\"\n" + "FROM \"foodmart\".\"sales_fact_1997\"\n" + "UNION ALL\n" + "SELECT \"product_class_id\" AS \"PRODUCT_ID\"\n" + "FROM \"foodmart\".\"product_class\""; final HepProgram program = new HepProgramBuilder().addRuleClass(UnionMergeRule.class).build(); final RuleSet rules = RuleSets.ofList(UnionMergeRule.INSTANCE); sql(query) .optimize(rules, new HepPlanner(program)) .ok(expected); }
/** Test case for * <a href="https://issues.apache.org/jira/browse/CALCITE-1372">[CALCITE-1372] * JDBC adapter generates SQL with wrong field names</a>. */ @Test public void testJoinPlan2() { final String sql = "SELECT v1.deptno, v2.deptno\n" + "FROM dept v1 LEFT JOIN emp v2 ON v1.deptno = v2.deptno\n" + "WHERE v2.job LIKE 'PRESIDENT'"; final String expected = "SELECT \"DEPT\".\"DEPTNO\"," + " \"EMP\".\"DEPTNO\" AS \"DEPTNO0\"\n" + "FROM \"JDBC_SCOTT\".\"DEPT\"\n" + "LEFT JOIN \"JDBC_SCOTT\".\"EMP\"" + " ON \"DEPT\".\"DEPTNO\" = \"EMP\".\"DEPTNO\"\n" + "WHERE \"EMP\".\"JOB\" LIKE 'PRESIDENT'"; final String expected2 = "SELECT DEPT.DEPTNO, EMP.DEPTNO AS DEPTNO0\n" + "FROM JDBC_SCOTT.DEPT AS DEPT\n" + "LEFT JOIN JDBC_SCOTT.EMP AS EMP ON DEPT.DEPTNO = EMP.DEPTNO\n" + "WHERE EMP.JOB LIKE 'PRESIDENT'"; sql(sql) .schema(CalciteAssert.SchemaSpec.JDBC_SCOTT) .ok(expected) .withDb2() .ok(expected2); }
/** Test case for * <a href="https://issues.apache.org/jira/browse/CALCITE-1586">[CALCITE-1586] * JDBC adapter generates wrong SQL if UNION has more than two inputs</a>. */ @Test public void testThreeQueryUnion() { String query = "SELECT \"product_id\" FROM \"product\" " + " UNION ALL " + "SELECT \"product_id\" FROM \"sales_fact_1997\" " + " UNION ALL " + "SELECT \"product_class_id\" AS product_id FROM \"product_class\""; String expected = "SELECT \"product_id\"\n" + "FROM \"foodmart\".\"product\"\n" + "UNION ALL\n" + "SELECT \"product_id\"\n" + "FROM \"foodmart\".\"sales_fact_1997\"\n" + "UNION ALL\n" + "SELECT \"product_class_id\" AS \"PRODUCT_ID\"\n" + "FROM \"foodmart\".\"product_class\""; final HepProgram program = new HepProgramBuilder().addRuleClass(UnionMergeRule.class).build(); final RuleSet rules = RuleSets.ofList(UnionMergeRule.INSTANCE); sql(query) .optimize(rules, new HepPlanner(program)) .ok(expected); }
/** Test case for * <a href="https://issues.apache.org/jira/browse/CALCITE-1372">[CALCITE-1372] * JDBC adapter generates SQL with wrong field names</a>. */ @Test public void testJoinPlan2() { final String sql = "SELECT v1.deptno, v2.deptno\n" + "FROM dept v1 LEFT JOIN emp v2 ON v1.deptno = v2.deptno\n" + "WHERE v2.job LIKE 'PRESIDENT'"; final String expected = "SELECT \"DEPT\".\"DEPTNO\"," + " \"EMP\".\"DEPTNO\" AS \"DEPTNO0\"\n" + "FROM \"JDBC_SCOTT\".\"DEPT\"\n" + "LEFT JOIN \"JDBC_SCOTT\".\"EMP\"" + " ON \"DEPT\".\"DEPTNO\" = \"EMP\".\"DEPTNO\"\n" + "WHERE \"EMP\".\"JOB\" LIKE 'PRESIDENT'"; final String expected2 = "SELECT DEPT.DEPTNO, EMP.DEPTNO AS DEPTNO0\n" + "FROM JDBC_SCOTT.DEPT AS DEPT\n" + "LEFT JOIN JDBC_SCOTT.EMP AS EMP ON DEPT.DEPTNO = EMP.DEPTNO\n" + "WHERE EMP.JOB LIKE 'PRESIDENT'"; sql(sql) .schema(CalciteAssert.SchemaSpec.JDBC_SCOTT) .ok(expected) .withDb2() .ok(expected2); }
@Test public void testSubstring() { final String query = "select substring(\"brand_name\" from 2) " + "from \"product\"\n"; final String expectedOracle = "SELECT SUBSTR(\"brand_name\", 2)\n" + "FROM \"foodmart\".\"product\""; final String expectedPostgresql = "SELECT SUBSTRING(\"brand_name\" FROM 2)\n" + "FROM \"foodmart\".\"product\""; final String expectedMysql = "SELECT SUBSTRING(`brand_name` FROM 2)\n" + "FROM `foodmart`.`product`"; sql(query) .withOracle() .ok(expectedOracle) .withPostgresql() .ok(expectedPostgresql) .withMysql() .ok(expectedMysql) .withMssql() // mssql does not support this syntax and so should fail .throws_("MSSQL SUBSTRING requires FROM and FOR arguments"); }
@Test public void testUnparseSqlIntervalQualifierDb2() { String queryDatePlus = "select * from \"employee\" where \"hire_date\" + " + "INTERVAL '19800' SECOND(5) > TIMESTAMP '2005-10-17 00:00:00' "; String expectedDatePlus = "SELECT *\n" + "FROM foodmart.employee AS employee\n" + "WHERE (employee.hire_date + 19800 SECOND)" + " > TIMESTAMP '2005-10-17 00:00:00'"; sql(queryDatePlus) .withDb2() .ok(expectedDatePlus); String queryDateMinus = "select * from \"employee\" where \"hire_date\" - " + "INTERVAL '19800' SECOND(5) > TIMESTAMP '2005-10-17 00:00:00' "; String expectedDateMinus = "SELECT *\n" + "FROM foodmart.employee AS employee\n" + "WHERE (employee.hire_date - 19800 SECOND)" + " > TIMESTAMP '2005-10-17 00:00:00'"; sql(queryDateMinus) .withDb2() .ok(expectedDateMinus); }
@Test public void testSubstring() { final String query = "select substring(\"brand_name\" from 2) " + "from \"product\"\n"; final String expectedOracle = "SELECT SUBSTR(\"brand_name\", 2)\n" + "FROM \"foodmart\".\"product\""; final String expectedPostgresql = "SELECT SUBSTRING(\"brand_name\" FROM 2)\n" + "FROM \"foodmart\".\"product\""; final String expectedMysql = "SELECT SUBSTRING(`brand_name` FROM 2)\n" + "FROM `foodmart`.`product`"; sql(query) .withOracle() .ok(expectedOracle) .withPostgresql() .ok(expectedPostgresql) .withMysql() .ok(expectedMysql) .withMssql() // mssql does not support this syntax and so should fail .throws_("MSSQL SUBSTRING requires FROM and FOR arguments"); }
/** Test case for * <a href="https://issues.apache.org/jira/browse/CALCITE-1849">[CALCITE-1849] * Support sub-queries (RexSubQuery) in RelToSqlConverter</a>. */ @Test public void testExistsWithExpand() { String query = "select \"product_name\" from \"product\" a " + "where exists (select count(*) " + "from \"sales_fact_1997\"b " + "where b.\"product_id\" = a.\"product_id\")"; String expected = "SELECT \"product_name\"\n" + "FROM \"foodmart\".\"product\"\n" + "WHERE EXISTS (SELECT COUNT(*)\n" + "FROM \"foodmart\".\"sales_fact_1997\"\n" + "WHERE \"product_id\" = \"product\".\"product_id\")"; sql(query).config(NO_EXPAND_CONFIG).ok(expected); }
Sql dialect(SqlDialect dialect) { return new Sql(schema, sql, dialect, config, transforms); }
@Test public void testDb2DialectSelectQueryWithGroup() { String query = "select count(*), sum(\"employee_id\") " + "from \"reserve_employee\" " + "where \"hire_date\" > '2015-01-01' " + "and (\"position_title\" = 'SDE' or \"position_title\" = 'SDM') " + "group by \"store_id\", \"position_title\""; final String expected = "SELECT" + " COUNT(*), SUM(reserve_employee.employee_id)\n" + "FROM foodmart.reserve_employee AS reserve_employee\n" + "WHERE reserve_employee.hire_date > '2015-01-01' " + "AND (reserve_employee.position_title = 'SDE' OR " + "reserve_employee.position_title = 'SDM')\n" + "GROUP BY reserve_employee.store_id, reserve_employee.position_title"; sql(query).withDb2().ok(expected); }
Sql dialect(SqlDialect dialect) { return new Sql(schema, sql, dialect, config, transforms); }
@Test public void testDb2DialectSelectQueryWithGroup() { String query = "select count(*), sum(\"employee_id\") " + "from \"reserve_employee\" " + "where \"hire_date\" > '2015-01-01' " + "and (\"position_title\" = 'SDE' or \"position_title\" = 'SDM') " + "group by \"store_id\", \"position_title\""; final String expected = "SELECT" + " COUNT(*), SUM(reserve_employee.employee_id)\n" + "FROM foodmart.reserve_employee AS reserve_employee\n" + "WHERE reserve_employee.hire_date > '2015-01-01' " + "AND (reserve_employee.position_title = 'SDE' OR " + "reserve_employee.position_title = 'SDM')\n" + "GROUP BY reserve_employee.store_id, reserve_employee.position_title"; sql(query).withDb2().ok(expected); }
/** Test case for * <a href="https://issues.apache.org/jira/browse/CALCITE-1636">[CALCITE-1636] * JDBC adapter generates wrong SQL for self join with sub-query</a>. */ @Test public void testSubQueryAlias() { String query = "select t1.\"customer_id\", t2.\"customer_id\" \n" + "from (select \"customer_id\" from \"sales_fact_1997\") as t1 \n" + "inner join (select \"customer_id\" from \"sales_fact_1997\") t2 \n" + "on t1.\"customer_id\" = t2.\"customer_id\""; final String expected = "SELECT *\n" + "FROM (SELECT sales_fact_1997.customer_id\n" + "FROM foodmart.sales_fact_1997 AS sales_fact_1997) AS t\n" + "INNER JOIN (SELECT sales_fact_19970.customer_id\n" + "FROM foodmart.sales_fact_1997 AS sales_fact_19970) AS t0 ON t.customer_id = t0.customer_id"; sql(query).withDb2().ok(expected); }
/** Test case for * <a href="https://issues.apache.org/jira/browse/CALCITE-1849">[CALCITE-1849] * Support sub-queries (RexSubQuery) in RelToSqlConverter</a>. */ @Test public void testExistsWithExpand() { String query = "select \"product_name\" from \"product\" a " + "where exists (select count(*) " + "from \"sales_fact_1997\"b " + "where b.\"product_id\" = a.\"product_id\")"; String expected = "SELECT \"product_name\"\n" + "FROM \"foodmart\".\"product\"\n" + "WHERE EXISTS (SELECT COUNT(*)\n" + "FROM \"foodmart\".\"sales_fact_1997\"\n" + "WHERE \"product_id\" = \"product\".\"product_id\")"; sql(query).config(NO_EXPAND_CONFIG).ok(expected); }
Sql config(SqlToRelConverter.Config config) { return new Sql(schema, sql, dialect, config, transforms); }
/** Test case for * <a href="https://issues.apache.org/jira/browse/CALCITE-1636">[CALCITE-1636] * JDBC adapter generates wrong SQL for self join with sub-query</a>. */ @Test public void testSubQueryAlias() { String query = "select t1.\"customer_id\", t2.\"customer_id\" \n" + "from (select \"customer_id\" from \"sales_fact_1997\") as t1 \n" + "inner join (select \"customer_id\" from \"sales_fact_1997\") t2 \n" + "on t1.\"customer_id\" = t2.\"customer_id\""; final String expected = "SELECT *\n" + "FROM (SELECT sales_fact_1997.customer_id\n" + "FROM foodmart.sales_fact_1997 AS sales_fact_1997) AS t\n" + "INNER JOIN (SELECT sales_fact_19970.customer_id\n" + "FROM foodmart.sales_fact_1997 AS sales_fact_19970) AS t0 ON t.customer_id = t0.customer_id"; sql(query).withDb2().ok(expected); }
Sql config(SqlToRelConverter.Config config) { return new Sql(schema, sql, dialect, config, transforms); }