/** Test case for * <a href="https://issues.apache.org/jira/browse/CALCITE-1332">[CALCITE-1332] * DB2 should always use aliases for tables: x.y.z AS z</a>. */ @Test public void testDb2DialectJoinStar() { String query = "select * " + "from \"foodmart\".\"employee\" A " + "join \"foodmart\".\"department\" B\n" + "on A.\"department_id\" = B.\"department_id\""; final String expected = "SELECT *\n" + "FROM foodmart.employee AS employee\n" + "INNER JOIN foodmart.department AS department " + "ON employee.department_id = department.department_id"; 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 public void testDb2DialectJoinWhere() { String query = "select A.\"employee_id\", B.\"department_id\" " + "from \"foodmart\".\"employee\" A join \"foodmart\".\"department\" B\n" + "on A.\"department_id\" = B.\"department_id\" " + "where A.\"employee_id\" < 1000"; final String expected = "SELECT" + " employee.employee_id, department.department_id\n" + "FROM foodmart.employee AS employee\n" + "INNER JOIN foodmart.department AS department " + "ON employee.department_id = department.department_id\n" + "WHERE employee.employee_id < 1000"; sql(query).withDb2().ok(expected); }
@Test public void testDb2DialectSelfJoinWhere() { String query = "select A.\"employee_id\", B.\"employee_id\" from " + "\"foodmart\".\"employee\" A join \"foodmart\".\"employee\" B\n" + "on A.\"department_id\" = B.\"department_id\" " + "where B.\"employee_id\" < 2000"; final String expected = "SELECT " + "employee.employee_id, employee0.employee_id AS employee_id0\n" + "FROM foodmart.employee AS employee\n" + "INNER JOIN foodmart.employee AS employee0 " + "ON employee.department_id = employee0.department_id\n" + "WHERE employee0.employee_id < 2000"; sql(query).withDb2().ok(expected); }
@Test public void testDb2DialectSelfJoinWhere() { String query = "select A.\"employee_id\", B.\"employee_id\" from " + "\"foodmart\".\"employee\" A join \"foodmart\".\"employee\" B\n" + "on A.\"department_id\" = B.\"department_id\" " + "where B.\"employee_id\" < 2000"; final String expected = "SELECT " + "employee.employee_id, employee0.employee_id AS employee_id0\n" + "FROM foodmart.employee AS employee\n" + "INNER JOIN foodmart.employee AS employee0 " + "ON employee.department_id = employee0.department_id\n" + "WHERE employee0.employee_id < 2000"; sql(query).withDb2().ok(expected); }
@Test public void testDb2DialectJoinWhere() { String query = "select A.\"employee_id\", B.\"department_id\" " + "from \"foodmart\".\"employee\" A join \"foodmart\".\"department\" B\n" + "on A.\"department_id\" = B.\"department_id\" " + "where A.\"employee_id\" < 1000"; final String expected = "SELECT" + " employee.employee_id, department.department_id\n" + "FROM foodmart.employee AS employee\n" + "INNER JOIN foodmart.department AS department " + "ON employee.department_id = department.department_id\n" + "WHERE employee.employee_id < 1000"; sql(query).withDb2().ok(expected); }
@Test public void testDb2DialectJoin() { String query = "select A.\"employee_id\", B.\"department_id\" " + "from \"foodmart\".\"employee\" A join \"foodmart\".\"department\" B\n" + "on A.\"department_id\" = B.\"department_id\""; final String expected = "SELECT" + " employee.employee_id, department.department_id\n" + "FROM foodmart.employee AS employee\n" + "INNER JOIN foodmart.department AS department " + "ON employee.department_id = department.department_id"; sql(query).withDb2().ok(expected); }
@Test public void testDb2DialectSelectQueryComplex() { String query = "select count(*), \"units_per_case\" " + "from \"product\" where \"cases_per_pallet\" > 100 " + "group by \"product_id\", \"units_per_case\" " + "order by \"units_per_case\" desc"; final String expected = "SELECT COUNT(*), product.units_per_case\n" + "FROM foodmart.product AS product\n" + "WHERE product.cases_per_pallet > 100\n" + "GROUP BY product.product_id, product.units_per_case\n" + "ORDER BY product.units_per_case DESC"; sql(query).withDb2().ok(expected); }
@Test public void testDb2DialectSelectQueryWithGroupByHaving() { String query = "select count(*) from \"product\" " + "group by \"product_class_id\", \"product_id\" " + "having \"product_id\" > 10"; final String expected = "SELECT COUNT(*)\n" + "FROM foodmart.product AS product\n" + "GROUP BY product.product_class_id, product.product_id\n" + "HAVING product.product_id > 10"; sql(query).withDb2().ok(expected); }
@Test public void testDb2DialectSelfJoinStar() { String query = "select * " + "from \"foodmart\".\"employee\" A join \"foodmart\".\"employee\" B\n" + "on A.\"department_id\" = B.\"department_id\""; final String expected = "SELECT *\n" + "FROM foodmart.employee AS employee\n" + "INNER JOIN foodmart.employee AS employee0 " + "ON employee.department_id = employee0.department_id"; sql(query).withDb2().ok(expected); }
@Test public void testDb2DialectCast() { String query = "select \"hire_date\", cast(\"hire_date\" as varchar(10)) " + "from \"foodmart\".\"reserve_employee\""; final String expected = "SELECT reserve_employee.hire_date, " + "CAST(reserve_employee.hire_date AS VARCHAR(10))\n" + "FROM foodmart.reserve_employee AS reserve_employee"; sql(query).withDb2().ok(expected); }
@Test public void testDb2DialectSelfJoin() { String query = "select A.\"employee_id\", B.\"employee_id\" from " + "\"foodmart\".\"employee\" A join \"foodmart\".\"employee\" B\n" + "on A.\"department_id\" = B.\"department_id\""; final String expected = "SELECT" + " employee.employee_id, employee0.employee_id AS employee_id0\n" + "FROM foodmart.employee AS employee\n" + "INNER JOIN foodmart.employee AS employee0 " + "ON employee.department_id = employee0.department_id"; sql(query).withDb2().ok(expected); }
@Test public void testDb2DialectJoin() { String query = "select A.\"employee_id\", B.\"department_id\" " + "from \"foodmart\".\"employee\" A join \"foodmart\".\"department\" B\n" + "on A.\"department_id\" = B.\"department_id\""; final String expected = "SELECT" + " employee.employee_id, department.department_id\n" + "FROM foodmart.employee AS employee\n" + "INNER JOIN foodmart.department AS department " + "ON employee.department_id = department.department_id"; sql(query).withDb2().ok(expected); }
@Test public void testDb2DialectSelectQueryComplex() { String query = "select count(*), \"units_per_case\" " + "from \"product\" where \"cases_per_pallet\" > 100 " + "group by \"product_id\", \"units_per_case\" " + "order by \"units_per_case\" desc"; final String expected = "SELECT COUNT(*), product.units_per_case\n" + "FROM foodmart.product AS product\n" + "WHERE product.cases_per_pallet > 100\n" + "GROUP BY product.product_id, product.units_per_case\n" + "ORDER BY product.units_per_case DESC"; sql(query).withDb2().ok(expected); }
@Test public void testDb2DialectCast() { String query = "select \"hire_date\", cast(\"hire_date\" as varchar(10)) " + "from \"foodmart\".\"reserve_employee\""; final String expected = "SELECT reserve_employee.hire_date, " + "CAST(reserve_employee.hire_date AS VARCHAR(10))\n" + "FROM foodmart.reserve_employee AS reserve_employee"; sql(query).withDb2().ok(expected); }
@Test public void testDb2DialectWhere() { String query = "select A.\"employee_id\" from " + "\"foodmart\".\"employee\" A where A.\"department_id\" < 1000"; final String expected = "SELECT employee.employee_id\n" + "FROM foodmart.employee AS employee\n" + "WHERE employee.department_id < 1000"; sql(query).withDb2().ok(expected); }
@Test public void testDb2DialectSelfJoin() { String query = "select A.\"employee_id\", B.\"employee_id\" from " + "\"foodmart\".\"employee\" A join \"foodmart\".\"employee\" B\n" + "on A.\"department_id\" = B.\"department_id\""; final String expected = "SELECT" + " employee.employee_id, employee0.employee_id AS employee_id0\n" + "FROM foodmart.employee AS employee\n" + "INNER JOIN foodmart.employee AS employee0 " + "ON employee.department_id = employee0.department_id"; sql(query).withDb2().ok(expected); }
@Test public void testDb2DialectSelectQueryWithGroupByHaving() { String query = "select count(*) from \"product\" " + "group by \"product_class_id\", \"product_id\" " + "having \"product_id\" > 10"; final String expected = "SELECT COUNT(*)\n" + "FROM foodmart.product AS product\n" + "GROUP BY product.product_class_id, product.product_id\n" + "HAVING product.product_id > 10"; sql(query).withDb2().ok(expected); }
@Test public void testDb2DialectWhere() { String query = "select A.\"employee_id\" from " + "\"foodmart\".\"employee\" A where A.\"department_id\" < 1000"; final String expected = "SELECT employee.employee_id\n" + "FROM foodmart.employee AS employee\n" + "WHERE employee.department_id < 1000"; sql(query).withDb2().ok(expected); }
@Test public void testDb2DialectSelfJoinStar() { String query = "select * " + "from \"foodmart\".\"employee\" A join \"foodmart\".\"employee\" B\n" + "on A.\"department_id\" = B.\"department_id\""; final String expected = "SELECT *\n" + "FROM foodmart.employee AS employee\n" + "INNER JOIN foodmart.employee AS employee0 " + "ON employee.department_id = employee0.department_id"; sql(query).withDb2().ok(expected); }