protected void check( String sql, String expected) { sql(sql).ok(expected); }
/** MINUS is a <b>reserved</b> keyword in Calcite in all conformances, even * in the default conformance, where it is not allowed as an alternative to * EXCEPT. (It is reserved in Oracle but not in any version of the SQL * standard.) */ @Test public void testMinusIsReserved() { sql("select ^minus^ from t") .fails("(?s).*Encountered \"minus from\" at .*"); sql("select ^minus^ select") .fails("(?s).*Encountered \"minus select\" at .*"); sql("select * from t ^as^ minus where x < y") .fails("(?s).*Encountered \"as minus\" at .*"); }
@Test public void testGroupByRollup() { sql("select deptno from emp\n" + "group by rollup (deptno, deptno + 1, gender)") .ok("SELECT `DEPTNO`\n" + "FROM `EMP`\n" + "GROUP BY ROLLUP(`DEPTNO`, (`DEPTNO` + 1), `GENDER`)"); // Nested rollup not ok sql("select deptno from emp\n" + "group by rollup (deptno^, rollup(e, d))") .fails("(?s)Encountered \", rollup\" at .*"); }
@Test public void testAliasedStar() { // OK in parser; validator will give error sql("select emp.* as foo from emp") .ok("SELECT `EMP`.* AS `FOO`\n" + "FROM `EMP`"); }
@Test public void testCollectionTableWithLateral2() { final String sql = "select * from dept as d\n" + "cross join lateral table(ramp(dept.deptno)) as r"; final String expected = "SELECT *\n" + "FROM `DEPT` AS `D`\n" + "CROSS JOIN LATERAL TABLE(`RAMP`(`DEPT`.`DEPTNO`)) AS `R`"; sql(sql).ok(expected); }
@Test public void testTimestampDiff() { final String sql = "select * from t\n" + "where timestampdiff(frac_second, 5, hiredate) < curdate"; final String expected = "SELECT *\n" + "FROM `T`\n" + "WHERE (TIMESTAMPDIFF(MICROSECOND, 5, `HIREDATE`) < `CURDATE`)"; sql(sql).ok(expected); }
@Test public void testAggregateFilter() { sql("select sum(sal) filter (where gender = 'F') as femaleSal,\n" + " sum(sal) filter (where true) allSal,\n" + " count(distinct deptno) filter (where (deptno < 40))\n" + "from emp") .ok("SELECT (SUM(`SAL`) FILTER (WHERE (`GENDER` = 'F'))) AS `FEMALESAL`," + " (SUM(`SAL`) FILTER (WHERE TRUE)) AS `ALLSAL`," + " (COUNT(DISTINCT `DEPTNO`) FILTER (WHERE (`DEPTNO` < 40)))\n" + "FROM `EMP`"); }
@Test public void testGroupByCube() { sql("select deptno from emp\n" + "group by cube ((a, b), (c, d))") .ok("SELECT `DEPTNO`\n" + "FROM `EMP`\n" + "GROUP BY CUBE((`A`, `B`), (`C`, `D`))"); }
/** Even in SQL Server conformance mode, we do not yet support * 'function(args)' as an abbreviation for 'table(function(args)'. */ @Test public void testOuterApplyFunctionFails() { conformance = SqlConformanceEnum.SQL_SERVER_2008; final String sql = "select * from dept outer apply ramp(deptno^)^)"; sql(sql).fails("(?s).*Encountered \"\\)\" at .*"); }
@Test public void testCrossOuterApply() { conformance = SqlConformanceEnum.SQL_SERVER_2008; final String sql = "select * from dept\n" + "cross apply table(ramp(deptno)) as t(a)\n" + "outer apply table(ramp2(a))"; final String expected = "SELECT *\n" + "FROM `DEPT`\n" + "CROSS JOIN LATERAL TABLE(`RAMP`(`DEPTNO`)) AS `T` (`A`)\n" + "LEFT JOIN LATERAL TABLE(`RAMP2`(`A`)) ON TRUE"; sql(sql).ok(expected); }
@Test public void testCatalogSchemaTableStar() { sql("select cat.schem.emp.* from cat.schem.emp") .ok("SELECT `CAT`.`SCHEM`.`EMP`.*\n" + "FROM `CAT`.`SCHEM`.`EMP`"); }
@Test public void testCompoundStar() { final String sql = "select sales.emp.address.zipcode,\n" + " sales.emp.address.*\n" + "from sales.emp"; final String expected = "SELECT `SALES`.`EMP`.`ADDRESS`.`ZIPCODE`," + " `SALES`.`EMP`.`ADDRESS`.*\n" + "FROM `SALES`.`EMP`"; sql(sql).ok(expected); }
@Test public void testExplain() { final String sql = "explain plan for select * from emps"; final String expected = "EXPLAIN PLAN" + " INCLUDING ATTRIBUTES WITH IMPLEMENTATION FOR\n" + "SELECT *\n" + "FROM `EMPS`"; sql(sql).ok(expected); }
@Test public void testWithinGroupClause1() { final String sql = "select col1,\n" + " collect(col2) within group (order by col3)\n" + "from t\n" + "order by col1 limit 10"; final String expected = "SELECT `COL1`," + " (COLLECT(`COL2`) WITHIN GROUP (ORDER BY `COL3`))\n" + "FROM `T`\n" + "ORDER BY `COL1`\n" + "FETCH NEXT 10 ROWS ONLY"; sql(sql).ok(expected); }
@Test public void testAggregateFilter() { sql("select sum(sal) filter (where gender = 'F') as femaleSal,\n" + " sum(sal) filter (where true) allSal,\n" + " count(distinct deptno) filter (where (deptno < 40))\n" + "from emp") .ok("SELECT (SUM(`SAL`) FILTER (WHERE (`GENDER` = 'F'))) AS `FEMALESAL`," + " (SUM(`SAL`) FILTER (WHERE TRUE)) AS `ALLSAL`," + " (COUNT(DISTINCT `DEPTNO`) FILTER (WHERE (`DEPTNO` < 40)))\n" + "FROM `EMP`"); }
@Test public void testCollectionTableWithLateral() { final String sql = "select * from dept, lateral table(ramp(dept.deptno))"; final String expected = "SELECT *\n" + "FROM `DEPT`,\n" + "LATERAL TABLE(`RAMP`(`DEPT`.`DEPTNO`))"; sql(sql).ok(expected); }
@Test public void testCollectionTableWithLateral3() { // LATERAL before first table in FROM clause doesn't achieve anything, but // it's valid. final String sql = "select * from lateral table(ramp(dept.deptno)), dept"; final String expected = "SELECT *\n" + "FROM LATERAL TABLE(`RAMP`(`DEPT`.`DEPTNO`)),\n" + "`DEPT`"; sql(sql).ok(expected); }
@Test public void testTimestampAdd() { final String sql = "select * from t\n" + "where timestampadd(sql_tsi_month, 5, hiredate) < curdate"; final String expected = "SELECT *\n" + "FROM `T`\n" + "WHERE (TIMESTAMPADD(MONTH, 5, `HIREDATE`) < `CURDATE`)"; sql(sql).ok(expected); }
@Test public void testInsertSelect() { final String expected = "INSERT INTO `EMPS`\n" + "(SELECT *\n" + "FROM `EMPS`)"; sql("insert into emps select * from emps") .ok(expected) .node(not(isDdl())); }