@Test public void testUpsertValues() { final String expected = "UPSERT INTO `EMPS`\n" + "VALUES (ROW(1, 'Fredkin'))"; sql("upsert into emps values (1,'Fredkin')") .ok(expected) .node(not(isDdl())); }
@Test public void testExplainInsert() { final String expected = "EXPLAIN PLAN INCLUDING ATTRIBUTES" + " WITH IMPLEMENTATION FOR\n" + "INSERT INTO `EMPS1`\n" + "(SELECT *\n" + "FROM `EMPS2`)"; sql("explain plan for insert into emps1 select * from emps2") .ok(expected) .node(not(isDdl())); }
@Test public void testInsertValues() { final String expected = "INSERT INTO `EMPS`\n" + "VALUES (ROW(1, 'Fredkin'))"; sql("insert into emps values (1,'Fredkin')") .ok(expected) .node(not(isDdl())); }
@Test public void testInsertUnion() { final String expected = "INSERT INTO `EMPS`\n" + "(SELECT *\n" + "FROM `EMPS1`\n" + "UNION\n" + "SELECT *\n" + "FROM `EMPS2`)"; sql("insert into emps select * from emps1 union select * from emps2") .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 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 testCatalogSchemaTableStar() { sql("select cat.schem.emp.* from cat.schem.emp") .ok("SELECT `CAT`.`SCHEM`.`EMP`.*\n" + "FROM `CAT`.`SCHEM`.`EMP`"); }
@Test public void testStarAsFails() { sql("select * as x from emp") .ok("SELECT * AS `X`\n" + "FROM `EMP`"); }
@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 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 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 testCatalogSchemaTableStar() { sql("select cat.schem.emp.* from cat.schem.emp") .ok("SELECT `CAT`.`SCHEM`.`EMP`.*\n" + "FROM `CAT`.`SCHEM`.`EMP`"); }
@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 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`))"); }
@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`"); }
protected void check( String sql, String expected) { 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 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 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 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 .*"); }