.withVertica() .ok(expectedVertica) .withPostgresql() .ok(expectedPostgresql);
.withVertica() .ok(expectedVertica) .withPostgresql() .ok(expectedPostgresql);
/** 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 public void testSubstringWithFor() { final String query = "select substring(\"brand_name\" from 2 for 3) " + "from \"product\"\n"; final String expectedOracle = "SELECT SUBSTR(\"brand_name\", 2, 3)\n" + "FROM \"foodmart\".\"product\""; final String expectedPostgresql = "SELECT SUBSTRING(\"brand_name\" FROM 2 FOR 3)\n" + "FROM \"foodmart\".\"product\""; final String expectedMysql = "SELECT SUBSTRING(`brand_name` FROM 2 FOR 3)\n" + "FROM `foodmart`.`product`"; final String expectedMssql = "SELECT SUBSTRING([brand_name], 2, 3)\n" + "FROM [foodmart].[product]"; sql(query) .withOracle() .ok(expectedOracle) .withPostgresql() .ok(expectedPostgresql) .withMysql() .ok(expectedMysql) .withMssql() .ok(expectedMssql); }
@Test public void testSubstringWithFor() { final String query = "select substring(\"brand_name\" from 2 for 3) " + "from \"product\"\n"; final String expectedOracle = "SELECT SUBSTR(\"brand_name\", 2, 3)\n" + "FROM \"foodmart\".\"product\""; final String expectedPostgresql = "SELECT SUBSTRING(\"brand_name\" FROM 2 FOR 3)\n" + "FROM \"foodmart\".\"product\""; final String expectedMysql = "SELECT SUBSTRING(`brand_name` FROM 2 FOR 3)\n" + "FROM `foodmart`.`product`"; final String expectedMssql = "SELECT SUBSTRING([brand_name], 2, 3)\n" + "FROM [foodmart].[product]"; sql(query) .withOracle() .ok(expectedOracle) .withPostgresql() .ok(expectedPostgresql) .withMysql() .ok(expectedMysql) .withMssql() .ok(expectedMssql); }
@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"); }
/** As {@link #testSum0BecomesCoalesce()} but for windowed aggregates. */ @Test public void testWindowedSum0BecomesCoalesce() { final String query = "select\n" + " AVG(\"net_weight\") OVER (order by \"product_id\" rows 3 preceding)\n" + "from \"foodmart\".\"product\""; final String expectedPostgresql = "SELECT CASE WHEN (COUNT(\"net_weight\")" + " OVER (ORDER BY \"product_id\" ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)) > 0 " + "THEN CAST(COALESCE(SUM(\"net_weight\")" + " OVER (ORDER BY \"product_id\" ROWS BETWEEN 3 PRECEDING AND CURRENT ROW), 0)" + " AS DOUBLE PRECISION) " + "ELSE NULL END / (COUNT(\"net_weight\")" + " OVER (ORDER BY \"product_id\" ROWS BETWEEN 3 PRECEDING AND CURRENT ROW))\n" + "FROM \"foodmart\".\"product\""; sql(query) .withPostgresql() .ok(expectedPostgresql); }
@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 testFloorPostgres() { String query = "SELECT floor(\"hire_date\" TO MINUTE) FROM \"employee\""; String expected = "SELECT DATE_TRUNC('MINUTE', \"hire_date\")\nFROM \"foodmart\".\"employee\""; sql(query) .withPostgresql() .ok(expected); }
@Test public void testFloorPostgres() { String query = "SELECT floor(\"hire_date\" TO MINUTE) FROM \"employee\""; String expected = "SELECT DATE_TRUNC('MINUTE', \"hire_date\")\nFROM \"foodmart\".\"employee\""; sql(query) .withPostgresql() .ok(expected); }