@Test public void testWithinGroup4() { // filter in AggregateCall is not unparsed final String query = "select \"product_class_id\", collect(\"net_weight\") " + "within group (order by \"net_weight\" desc) filter (where \"net_weight\" > 0)" + "from \"product\" group by \"product_class_id\""; final String expected = "SELECT \"product_class_id\", COLLECT(\"net_weight\") " + "WITHIN GROUP (ORDER BY \"net_weight\" DESC)\n" + "FROM \"foodmart\".\"product\"\n" + "GROUP BY \"product_class_id\""; sql(query).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 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 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 testHiveSelectQueryWithOrderByAscAndNullsLastShouldBeEmulated() { 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 IS NULL, product_id"; sql(query).dialect(HiveSqlDialect.DEFAULT).ok(expected); }
@Test public void testSelectQueryWithLimitClause() { String query = "select \"product_id\" from \"product\" limit 100 offset 10"; final String expected = "SELECT product_id\n" + "FROM foodmart.product\n" + "LIMIT 100\nOFFSET 10"; sql(query).withHive().ok(expected); }
@Test public void testSelectQueryWithOrderByClause() { String query = "select \"product_id\" from \"product\" order by \"net_weight\""; final String expected = "SELECT \"product_id\", \"net_weight\"\n" + "FROM \"foodmart\".\"product\"\n" + "ORDER BY \"net_weight\""; sql(query).ok(expected); }
@Test public void testSelectQueryWithGroupByAndProjectList1() { String query = "select count(*) from \"product\" group by \"product_class_id\", \"product_id\""; final String expected = "SELECT COUNT(*)\n" + "FROM \"foodmart\".\"product\"\n" + "GROUP BY \"product_class_id\", \"product_id\""; sql(query).ok(expected); }
/** Test case for * <a href="https://issues.apache.org/jira/browse/CALCITE-2713">[CALCITE-2713] * JDBC adapter may generate casts on PostgreSQL for VARCHAR type exceeding * max length</a>. */ @Test public void testCastLongVarchar2() { final String query = "select cast(\"store_id\" as VARCHAR(175))\n" + " from \"expense_fact\""; final String expected = "SELECT CAST(\"store_id\" AS VARCHAR(175))\n" + "FROM \"foodmart\".\"expense_fact\""; sql(query).withPostgresqlModifiedTypeSystem().ok(expected); }
@Test public void testSelectQueryWithSumAggregateFunction() { String query = "select sum(\"net_weight\") from \"product\" group by \"product_class_id\" "; final String expected = "SELECT SUM(\"net_weight\")\n" + "FROM \"foodmart\".\"product\"\n" + "GROUP BY \"product_class_id\""; sql(query).ok(expected); }
@Test public void testSelectQueryWithGroupBy() { String query = "select count(*) from \"product\" group by \"product_class_id\", \"product_id\""; final String expected = "SELECT COUNT(*)\n" + "FROM \"foodmart\".\"product\"\n" + "GROUP BY \"product_class_id\", \"product_id\""; sql(query).ok(expected); }
@Test public void testSubQueryInWithExpand() { String query = "select \"product_name\" from \"product\" a " + "where \"product_id\" in (select \"product_id\" " + "from \"sales_fact_1997\"b " + "where b.\"product_id\" = a.\"product_id\")"; String expected = "SELECT \"product_name\"\n" + "FROM \"foodmart\".\"product\"\n" + "WHERE \"product_id\" IN (SELECT \"product_id\"\n" + "FROM \"foodmart\".\"sales_fact_1997\"\n" + "WHERE \"product_id\" = \"product\".\"product_id\")"; sql(query).config(NO_EXPAND_CONFIG).ok(expected); }
@Test public void testFloorMysqlHour() { String query = "SELECT floor(\"hire_date\" TO HOUR) FROM \"employee\""; String expected = "SELECT DATE_FORMAT(`hire_date`, '%Y-%m-%d %H:00:00')\n" + "FROM `foodmart`.`employee`"; sql(query) .withMysql() .ok(expected); }
@Test public void testFloorMssqlMonth() { String query = "SELECT floor(\"hire_date\" TO MONTH) FROM \"employee\""; String expected = "SELECT CONVERT(DATETIME, CONVERT(VARCHAR(7), [hire_date] , 126)+'-01')\n" + "FROM [foodmart].[employee]"; sql(query) .withMssql() .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 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 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 testSelectQueryWithLimitClause() { String query = "select \"product_id\" from \"product\" limit 100 offset 10"; final String expected = "SELECT product_id\n" + "FROM foodmart.product\n" + "LIMIT 100\nOFFSET 10"; sql(query).withHive().ok(expected); }
@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 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); }