/** 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); }
+ "WHERE (`hire_date` - INTERVAL '19800' SECOND)" + " > TIMESTAMP '2005-10-17 00:00:00'"; sql(sql0).withMysql().ok(expect0); + "WHERE (`hire_date` + INTERVAL '10' HOUR)" + " > TIMESTAMP '2005-10-17 00:00:00'"; sql(sql1).withMysql().ok(expect1); + "WHERE (`hire_date` + INTERVAL '1-2' YEAR_MONTH)" + " > TIMESTAMP '2005-10-17 00:00:00'"; sql(sql2).withMysql().ok(expect2); + "WHERE (`hire_date` + INTERVAL '39:12' MINUTE_SECOND)" + " > TIMESTAMP '2005-10-17 00:00:00'"; sql(sql3).withMysql().ok(expect3);
@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"); }
@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 testSelectQueryWithGroupByEmpty() { final String sql0 = "select count(*) from \"product\" group by ()"; final String sql1 = "select count(*) from \"product\""; final String expected = "SELECT COUNT(*)\n" + "FROM \"foodmart\".\"product\""; final String expectedMySql = "SELECT COUNT(*)\n" + "FROM `foodmart`.`product`"; sql(sql0) .ok(expected) .withMysql() .ok(expectedMySql); sql(sql1) .ok(expected) .withMysql() .ok(expectedMySql); }
@Test public void testFloorMysqlSecond() { String query = "SELECT floor(\"hire_date\" TO SECOND) FROM \"employee\""; String expected = "SELECT DATE_FORMAT(`hire_date`, '%Y-%m-%d %H:%i:%s')\n" + "FROM `foodmart`.`employee`"; sql(query) .withMysql() .ok(expected); }
@Test public void testFloorMysqlWeek() { String query = "SELECT floor(\"hire_date\" TO WEEK) FROM \"employee\""; String expected = "SELECT STR_TO_DATE(DATE_FORMAT(`hire_date` , '%x%v-1'), '%x%v-%w')\n" + "FROM `foodmart`.`employee`"; sql(query) .withMysql() .ok(expected); }
@Test public void testFloorMysqlMonth() { String query = "SELECT floor(\"hire_date\" TO MONTH) FROM \"employee\""; String expected = "SELECT DATE_FORMAT(`hire_date`, '%Y-%m-01')\n" + "FROM `foodmart`.`employee`"; sql(query) .withMysql() .ok(expected); }
@Test public void testMysqlCastToInteger() { // MySQL does not allow cast to INTEGER; instead cast to SIGNED. final String query = "select \"employee_id\",\n" + " cast(\"salary_paid\" * 10000 as integer)\n" + "from \"salary\""; final String expected = "SELECT `employee_id`," + " CAST(`salary_paid` * 10000 AS SIGNED)\n" + "FROM `foodmart`.`salary`"; sql(query).withMysql().ok(expected); }
@Test public void testFloorMysqlWeek() { String query = "SELECT floor(\"hire_date\" TO WEEK) FROM \"employee\""; String expected = "SELECT STR_TO_DATE(DATE_FORMAT(`hire_date` , '%x%v-1'), '%x%v-%w')\n" + "FROM `foodmart`.`employee`"; sql(query) .withMysql() .ok(expected); }
@Test public void testSelectQueryWithGroupByEmpty2() { final String query = "select 42 as c from \"product\" group by ()"; final String expected = "SELECT 42 AS \"C\"\n" + "FROM \"foodmart\".\"product\"\n" + "GROUP BY ()"; final String expectedMySql = "SELECT 42 AS `C`\n" + "FROM `foodmart`.`product`\n" + "GROUP BY ()"; sql(query) .ok(expected) .withMysql() .ok(expectedMySql); }
@Test public void testFloorMysqlSecond() { String query = "SELECT floor(\"hire_date\" TO SECOND) FROM \"employee\""; String expected = "SELECT DATE_FORMAT(`hire_date`, '%Y-%m-%d %H:%i:%s')\n" + "FROM `foodmart`.`employee`"; sql(query) .withMysql() .ok(expected); }
@Test public void testFloorMysqlMinute() { String query = "SELECT floor(\"hire_date\" TO MINUTE) FROM \"employee\""; String expected = "SELECT DATE_FORMAT(`hire_date`, '%Y-%m-%d %H:%i:00')\n" + "FROM `foodmart`.`employee`"; sql(query) .withMysql() .ok(expected); }
@Test public void testFloorMysqlMonth() { String query = "SELECT floor(\"hire_date\" TO MONTH) FROM \"employee\""; String expected = "SELECT DATE_FORMAT(`hire_date`, '%Y-%m-01')\n" + "FROM `foodmart`.`employee`"; sql(query) .withMysql() .ok(expected); }
@Test public void testFloorMysqlMinute() { String query = "SELECT floor(\"hire_date\" TO MINUTE) FROM \"employee\""; String expected = "SELECT DATE_FORMAT(`hire_date`, '%Y-%m-%d %H:%i:00')\n" + "FROM `foodmart`.`employee`"; sql(query) .withMysql() .ok(expected); }
@Test public void testMysqlCastToBigint() { // MySQL does not allow cast to BIGINT; instead cast to SIGNED. final String query = "select cast(\"product_id\" as bigint) from \"product\""; final String expected = "SELECT CAST(`product_id` AS SIGNED)\n" + "FROM `foodmart`.`product`"; sql(query).withMysql().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 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); }