@Test public void testUnparseSqlIntervalQualifierMsSql() { String queryDatePlus = "select * from \"employee\" where \"hire_date\" +" + "INTERVAL '19800' SECOND(5) > TIMESTAMP '2005-10-17 00:00:00' "; String expectedDatePlus = "SELECT *\n" + "FROM [foodmart].[employee]\n" + "WHERE DATEADD(SECOND, 19800, [hire_date]) > '2005-10-17 00:00:00'"; sql(queryDatePlus) .withMssql() .ok(expectedDatePlus); String queryDateMinus = "select * from \"employee\" where \"hire_date\" -" + "INTERVAL '19800' SECOND(5) > TIMESTAMP '2005-10-17 00:00:00' "; String expectedDateMinus = "SELECT *\n" + "FROM [foodmart].[employee]\n" + "WHERE DATEADD(SECOND, -19800, [hire_date]) > '2005-10-17 00:00:00'"; sql(queryDateMinus) .withMssql() .ok(expectedDateMinus); String queryDateMinusNegate = "select * from \"employee\" " + "where \"hire_date\" -INTERVAL '-19800' SECOND(5)" + " > TIMESTAMP '2005-10-17 00:00:00' "; String expectedDateMinusNegate = "SELECT *\n" + "FROM [foodmart].[employee]\n" + "WHERE DATEADD(SECOND, 19800, [hire_date]) > '2005-10-17 00:00:00'"; sql(queryDateMinusNegate) .withMssql() .ok(expectedDateMinusNegate); }
@Test public void testUnparseSqlIntervalQualifierMsSql() { String queryDatePlus = "select * from \"employee\" where \"hire_date\" +" + "INTERVAL '19800' SECOND(5) > TIMESTAMP '2005-10-17 00:00:00' "; String expectedDatePlus = "SELECT *\n" + "FROM [foodmart].[employee]\n" + "WHERE DATEADD(SECOND, 19800, [hire_date]) > '2005-10-17 00:00:00'"; sql(queryDatePlus) .withMssql() .ok(expectedDatePlus); String queryDateMinus = "select * from \"employee\" where \"hire_date\" -" + "INTERVAL '19800' SECOND(5) > TIMESTAMP '2005-10-17 00:00:00' "; String expectedDateMinus = "SELECT *\n" + "FROM [foodmart].[employee]\n" + "WHERE DATEADD(SECOND, -19800, [hire_date]) > '2005-10-17 00:00:00'"; sql(queryDateMinus) .withMssql() .ok(expectedDateMinus); String queryDateMinusNegate = "select * from \"employee\" " + "where \"hire_date\" -INTERVAL '-19800' SECOND(5)" + " > TIMESTAMP '2005-10-17 00:00:00' "; String expectedDateMinusNegate = "SELECT *\n" + "FROM [foodmart].[employee]\n" + "WHERE DATEADD(SECOND, 19800, [hire_date]) > '2005-10-17 00:00:00'"; sql(queryDateMinusNegate) .withMssql() .ok(expectedDateMinusNegate); }
@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 testFloorMssqlWeek() { String query = "SELECT floor(\"hire_date\" TO WEEK) FROM \"employee\""; String expected = "SELECT CONVERT(DATETIME, CONVERT(VARCHAR(10), " + "DATEADD(day, - (6 + DATEPART(weekday, [hire_date] )) % 7, [hire_date] ), 126))\n" + "FROM [foodmart].[employee]"; sql(query) .withMssql() .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 case for * <a href="https://issues.apache.org/jira/browse/CALCITE-2715">[CALCITE-2715] * MS SQL Server does not support character set as part of data type</a>. */ @Test public void testMssqlCharacterSet() { String query = "select \"hire_date\", cast(\"hire_date\" as varchar(10))\n" + "from \"foodmart\".\"reserve_employee\""; final String expected = "SELECT [hire_date], CAST([hire_date] AS VARCHAR(10))\n" + "FROM [foodmart].[reserve_employee]"; sql(query).withMssql().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 testFloorMssqlWeek() { String query = "SELECT floor(\"hire_date\" TO WEEK) FROM \"employee\""; String expected = "SELECT CONVERT(DATETIME, CONVERT(VARCHAR(10), " + "DATEADD(day, - (6 + DATEPART(weekday, [hire_date] )) % 7, [hire_date] ), 126))\n" + "FROM [foodmart].[employee]"; sql(query) .withMssql() .ok(expected); }