/** Test case for * <a href="https://issues.apache.org/jira/browse/CALCITE-1826">[CALCITE-1826] * JDBC dialect-specific FLOOR fails when in GROUP BY</a>. */ @Test public void testFloorWithGroupBy() { final String query = "SELECT floor(\"hire_date\" TO MINUTE)\n" + "FROM \"employee\"\n" + "GROUP BY floor(\"hire_date\" TO MINUTE)"; final String expected = "SELECT TRUNC(hire_date, 'MI')\n" + "FROM foodmart.employee\n" + "GROUP BY TRUNC(hire_date, 'MI')"; final String expectedOracle = "SELECT TRUNC(\"hire_date\", 'MINUTE')\n" + "FROM \"foodmart\".\"employee\"\n" + "GROUP BY TRUNC(\"hire_date\", 'MINUTE')"; final String expectedPostgresql = "SELECT DATE_TRUNC('MINUTE', \"hire_date\")\n" + "FROM \"foodmart\".\"employee\"\n" + "GROUP BY DATE_TRUNC('MINUTE', \"hire_date\")"; final String expectedMysql = "SELECT" + " DATE_FORMAT(`hire_date`, '%Y-%m-%d %H:%i:00')\n" + "FROM `foodmart`.`employee`\n" + "GROUP BY DATE_FORMAT(`hire_date`, '%Y-%m-%d %H:%i:00')"; sql(query) .withHsqldb() .ok(expected) .withOracle() .ok(expectedOracle) .withPostgresql() .ok(expectedPostgresql) .withMysql() .ok(expectedMysql); }
/** Test case for * <a href="https://issues.apache.org/jira/browse/CALCITE-1826">[CALCITE-1826] * JDBC dialect-specific FLOOR fails when in GROUP BY</a>. */ @Test public void testFloorWithGroupBy() { final String query = "SELECT floor(\"hire_date\" TO MINUTE)\n" + "FROM \"employee\"\n" + "GROUP BY floor(\"hire_date\" TO MINUTE)"; final String expected = "SELECT TRUNC(hire_date, 'MI')\n" + "FROM foodmart.employee\n" + "GROUP BY TRUNC(hire_date, 'MI')"; final String expectedOracle = "SELECT TRUNC(\"hire_date\", 'MINUTE')\n" + "FROM \"foodmart\".\"employee\"\n" + "GROUP BY TRUNC(\"hire_date\", 'MINUTE')"; final String expectedPostgresql = "SELECT DATE_TRUNC('MINUTE', \"hire_date\")\n" + "FROM \"foodmart\".\"employee\"\n" + "GROUP BY DATE_TRUNC('MINUTE', \"hire_date\")"; final String expectedMysql = "SELECT" + " DATE_FORMAT(`hire_date`, '%Y-%m-%d %H:%i:00')\n" + "FROM `foodmart`.`employee`\n" + "GROUP BY DATE_FORMAT(`hire_date`, '%Y-%m-%d %H:%i:00')"; sql(query) .withHsqldb() .ok(expected) .withOracle() .ok(expectedOracle) .withPostgresql() .ok(expectedPostgresql) .withMysql() .ok(expectedMysql); }
@Test public void testValues() { final String sql = "select \"a\"\n" + "from (values (1, 'x'), (2, 'yy')) as t(\"a\", \"b\")"; final String expectedHsqldb = "SELECT a\n" + "FROM (VALUES (1, 'x '),\n" + " (2, 'yy')) AS t (a, b)"; final String expectedPostgresql = "SELECT \"a\"\n" + "FROM (VALUES (1, 'x '),\n" + " (2, 'yy')) AS \"t\" (\"a\", \"b\")"; final String expectedOracle = "SELECT \"a\"\n" + "FROM (SELECT 1 \"a\", 'x ' \"b\"\n" + "FROM \"DUAL\"\n" + "UNION ALL\n" + "SELECT 2 \"a\", 'yy' \"b\"\n" + "FROM \"DUAL\")"; sql(sql) .withHsqldb() .ok(expectedHsqldb) .withPostgresql() .ok(expectedPostgresql) .withOracle() .ok(expectedOracle); }
@Test public void testValues() { final String sql = "select \"a\"\n" + "from (values (1, 'x'), (2, 'yy')) as t(\"a\", \"b\")"; final String expectedHsqldb = "SELECT a\n" + "FROM (VALUES (1, 'x '),\n" + " (2, 'yy')) AS t (a, b)"; final String expectedPostgresql = "SELECT \"a\"\n" + "FROM (VALUES (1, 'x '),\n" + " (2, 'yy')) AS \"t\" (\"a\", \"b\")"; final String expectedOracle = "SELECT \"a\"\n" + "FROM (SELECT 1 \"a\", 'x ' \"b\"\n" + "FROM \"DUAL\"\n" + "UNION ALL\n" + "SELECT 2 \"a\", 'yy' \"b\"\n" + "FROM \"DUAL\")"; sql(sql) .withHsqldb() .ok(expectedHsqldb) .withPostgresql() .ok(expectedPostgresql) .withOracle() .ok(expectedOracle); }
/** Test case for * <a href="https://issues.apache.org/jira/browse/CALCITE-1798">[CALCITE-1798] * Generate dialect-specific SQL for FLOOR operator</a>. */ @Test public void testFloor() { String query = "SELECT floor(\"hire_date\" TO MINUTE) FROM \"employee\""; String expected = "SELECT TRUNC(hire_date, 'MI')\nFROM foodmart.employee"; sql(query) .withHsqldb() .ok(expected); }
private void checkLiteral2(String expression, String expected) { sql("VALUES " + expression) .withHsqldb() .ok("SELECT *\n" + "FROM (VALUES (" + expected + ")) AS t (EXPR$0)"); }
/** Test case for * <a href="https://issues.apache.org/jira/browse/CALCITE-1798">[CALCITE-1798] * Generate dialect-specific SQL for FLOOR operator</a>. */ @Test public void testFloor() { String query = "SELECT floor(\"hire_date\" TO MINUTE) FROM \"employee\""; String expected = "SELECT TRUNC(hire_date, 'MI')\nFROM foodmart.employee"; sql(query) .withHsqldb() .ok(expected); }
private void checkLiteral2(String expression, String expected) { sql("VALUES " + expression) .withHsqldb() .ok("SELECT *\n" + "FROM (VALUES (" + expected + ")) AS t (EXPR$0)"); }