/** Removes the carets from the SQL string. Useful if you want to run * a test once at a conformance level where it fails, then run it again * at a conformance level where it succeeds. */ public Sql sansCarets() { return new Sql(sql.replace("^", ""), expression); } }
.ok("ALTER SYSTEM SET `a number` = 1") .node(isDdl()); check("alter system set flag = false", "ALTER SYSTEM SET `FLAG` = FALSE"); "ALTER SYSTEM SET `a`.`number` = 1"); sql("set approx = -12.3450") .ok("SET `APPROX` = -12.3450") .node(isDdl()); "ALTER SYSTEM RESET `FLAG`"); sql("reset onOff") .ok("RESET `ONOFF`") .node(isDdl()); check("reset \"this\".\"is\".\"sparta\"", "RESET `this`.`is`.`sparta`");
.ok("ALTER SYSTEM SET `a number` = 1") .node(isDdl()); check("alter system set flag = false", "ALTER SYSTEM SET `FLAG` = FALSE"); "ALTER SYSTEM SET `a`.`number` = 1"); sql("set approx = -12.3450") .ok("SET `APPROX` = -12.3450") .node(isDdl()); "ALTER SYSTEM RESET `FLAG`"); sql("reset onOff") .ok("RESET `ONOFF`") .node(isDdl()); check("reset \"this\".\"is\".\"sparta\"", "RESET `this`.`is`.`sparta`");
final String expected = "SELECT (ROW(`T1A`, `T2A`))\n" + "FROM `T1`"; sql(selectRow).sansCarets().ok(expected); conformance = SqlConformanceEnum.LENIENT; sql(selectRow).sansCarets().ok(expected); sql(selectRow).fails(pattern); conformance = SqlConformanceEnum.ORACLE_12; sql(selectRow).fails(pattern); conformance = SqlConformanceEnum.STRICT_2003; sql(selectRow).fails(pattern); conformance = SqlConformanceEnum.SQL_SERVER_2008; sql(selectRow).fails(pattern); + "WHERE ((ROW(`X`, `Y`)) < (ROW(`A`, `B`)))"; conformance = SqlConformanceEnum.DEFAULT; sql(whereRow).sansCarets().ok(whereExpected); conformance = SqlConformanceEnum.SQL_SERVER_2008; sql(whereRow).fails(pattern); sql(whereRow2).sansCarets().ok(whereExpected); if (this instanceof SqlUnParserTest) { sql(whereRow2).sansCarets().ok(whereExpected);
final String expected = "SELECT (ROW(`T1A`, `T2A`))\n" + "FROM `T1`"; sql(selectRow).sansCarets().ok(expected); conformance = SqlConformanceEnum.LENIENT; sql(selectRow).sansCarets().ok(expected); sql(selectRow).fails(pattern); conformance = SqlConformanceEnum.ORACLE_12; sql(selectRow).fails(pattern); conformance = SqlConformanceEnum.STRICT_2003; sql(selectRow).fails(pattern); conformance = SqlConformanceEnum.SQL_SERVER_2008; sql(selectRow).fails(pattern); + "WHERE ((ROW(`X`, `Y`)) < (ROW(`A`, `B`)))"; conformance = SqlConformanceEnum.DEFAULT; sql(whereRow).sansCarets().ok(whereExpected); conformance = SqlConformanceEnum.SQL_SERVER_2008; sql(whereRow).fails(pattern); sql(whereRow2).sansCarets().ok(whereExpected); sql(whereRow2).sansCarets().ok(whereExpected);
@Test public void testLateral() { // Bad: LATERAL table sql("select * from ^lateral^ emp") .fails("(?s)Encountered \"lateral emp\" at .*"); sql("select * from lateral table ^emp^ as e") .fails("(?s)Encountered \"emp\" at .*"); // Bad: LATERAL TABLE schema.table sql("select * from lateral table ^scott^.emp") .fails("(?s)Encountered \"scott\" at .*"); final String expected = "SELECT *\n" + "FROM LATERAL TABLE(`RAMP`(1))"; // Good: LATERAL TABLE function(arg, arg) sql("select * from lateral table(ramp(1))").ok(expected); sql("select * from lateral table(ramp(1)) as t") .ok(expected + " AS `T`"); sql("select * from lateral table(ramp(1)) as t(x)") .ok(expected + " AS `T` (`X`)"); // Bad: Parentheses make it look like a sub-query sql("select * from lateral (^table^(ramp(1)))") .fails("(?s)Encountered \"table \\(\" at .*"); // Good: LATERAL (subQuery) final String expected2 = "SELECT *\n" + "FROM LATERAL((SELECT *\n" + "FROM `EMP`))"; sql("select * from lateral (select * from emp)").ok(expected2); sql("select * from lateral (select * from emp) as t") .ok(expected2 + " AS `T`"); sql("select * from lateral (select * from emp) as t(x)") .ok(expected2 + " AS `T` (`X`)"); }
@Test public void testLateral() { // Bad: LATERAL table sql("select * from ^lateral^ emp") .fails("(?s)Encountered \"lateral emp\" at .*"); sql("select * from lateral table ^emp^ as e") .fails("(?s)Encountered \"emp\" at .*"); // Bad: LATERAL TABLE schema.table sql("select * from lateral table ^scott^.emp") .fails("(?s)Encountered \"scott\" at .*"); final String expected = "SELECT *\n" + "FROM LATERAL TABLE(`RAMP`(1))"; // Good: LATERAL TABLE function(arg, arg) sql("select * from lateral table(ramp(1))").ok(expected); sql("select * from lateral table(ramp(1)) as t") .ok(expected + " AS `T`"); sql("select * from lateral table(ramp(1)) as t(x)") .ok(expected + " AS `T` (`X`)"); // Bad: Parentheses make it look like a sub-query sql("select * from lateral (^table^(ramp(1)))") .fails("(?s)Encountered \"table \\(\" at .*"); // Good: LATERAL (subQuery) final String expected2 = "SELECT *\n" + "FROM LATERAL((SELECT *\n" + "FROM `EMP`))"; sql("select * from lateral (select * from emp)").ok(expected2); sql("select * from lateral (select * from emp) as t") .ok(expected2 + " AS `T`"); sql("select * from lateral (select * from emp) as t(x)") .ok(expected2 + " AS `T` (`X`)"); }
@Test public void testGroupingSets() { sql("select deptno from emp\n" + "group by grouping sets (deptno, (deptno, gender), ())") .ok("SELECT `DEPTNO`\n" + "FROM `EMP`\n" + "GROUP BY GROUPING SETS(`DEPTNO`, (`DEPTNO`, `GENDER`), ())"); // Grouping sets must have parentheses sql("select deptno from emp\n" + "group by grouping sets ^deptno^, (deptno, gender), ()") .fails("(?s).*Encountered \"deptno\" at line 2, column 24.\n" + "Was expecting:\n" + " \"\\(\" .*"); // Nested grouping sets, cube, rollup, grouping sets all OK sql("select deptno from emp\n" + "group by grouping sets (deptno, grouping sets (e, d), (),\n" + " cube (x, y), rollup(p, q))\n" + "order by a") .ok("SELECT `DEPTNO`\n" + "FROM `EMP`\n" + "GROUP BY GROUPING SETS(`DEPTNO`, GROUPING SETS(`E`, `D`), (), CUBE(`X`, `Y`), ROLLUP(`P`, `Q`))\n" + "ORDER BY `A`"); sql("select deptno from emp\n" + "group by grouping sets (())") .ok("SELECT `DEPTNO`\n" + "FROM `EMP`\n" + "GROUP BY GROUPING SETS(())"); }
@Test public void testGroupingSets() { sql("select deptno from emp\n" + "group by grouping sets (deptno, (deptno, gender), ())") .ok("SELECT `DEPTNO`\n" + "FROM `EMP`\n" + "GROUP BY GROUPING SETS(`DEPTNO`, (`DEPTNO`, `GENDER`), ())"); // Grouping sets must have parentheses sql("select deptno from emp\n" + "group by grouping sets ^deptno^, (deptno, gender), ()") .fails("(?s).*Encountered \"deptno\" at line 2, column 24.\n" + "Was expecting:\n" + " \"\\(\" .*"); // Nested grouping sets, cube, rollup, grouping sets all OK sql("select deptno from emp\n" + "group by grouping sets (deptno, grouping sets (e, d), (),\n" + " cube (x, y), rollup(p, q))\n" + "order by a") .ok("SELECT `DEPTNO`\n" + "FROM `EMP`\n" + "GROUP BY GROUPING SETS(`DEPTNO`, GROUPING SETS(`E`, `D`), (), CUBE(`X`, `Y`), ROLLUP(`P`, `Q`))\n" + "ORDER BY `A`"); sql("select deptno from emp\n" + "group by grouping sets (())") .ok("SELECT `DEPTNO`\n" + "FROM `EMP`\n" + "GROUP BY GROUPING SETS(())"); }
@Test public void testUnnest() { check( "select*from unnest(x)", "SELECT *\n" + "FROM (UNNEST(`X`))"); check( "select*from unnest(x) AS T", "SELECT *\n" + "FROM (UNNEST(`X`)) AS `T`"); // UNNEST cannot be first word in query checkFails( "^unnest^(x)", "(?s)Encountered \"unnest\" at.*"); // UNNEST with more than one argument final String sql = "select * from dept,\n" + "unnest(dept.employees, dept.managers)"; final String expected = "SELECT *\n" + "FROM `DEPT`,\n" + "(UNNEST(`DEPT`.`EMPLOYEES`, `DEPT`.`MANAGERS`))"; sql(sql).ok(expected); // LATERAL UNNEST is not valid sql("select * from dept, ^lateral^ unnest(dept.employees)") .fails("(?s)Encountered \"lateral unnest\" at .*"); }
@Test public void testSome() { final String sql = "select * from emp\n" + "where sal > some (select comm from emp)"; final String expected = "SELECT *\n" + "FROM `EMP`\n" + "WHERE (`SAL` > SOME (SELECT `COMM`\n" + "FROM `EMP`))"; sql(sql).ok(expected); // ANY is a synonym for SOME final String sql2 = "select * from emp\n" + "where sal > any (select comm from emp)"; sql(sql2).ok(expected); final String sql3 = "select * from emp\n" + "where name like (select ^some^ name from emp)"; sql(sql3).fails("(?s).*Encountered \"some\" at .*"); final String sql4 = "select * from emp\n" + "where name ^like^ some (select name from emp)"; sql(sql4).fails("(?s).*Encountered \"like some\" at .*"); final String sql5 = "select * from emp where empno = any (10,20)"; final String expected5 = "SELECT *\n" + "FROM `EMP`\n" + "WHERE (`EMPNO` = SOME (10, 20))"; sql(sql5).ok(expected5); }
@Test public void testDefault() { sql("select ^DEFAULT^ from emp") .fails("(?s)Encountered \"DEFAULT\" at .*"); sql("select cast(empno ^+^ DEFAULT as double) from emp") .fails("(?s)Encountered \"\\+ DEFAULT\" at .*"); sql("select empno ^+^ DEFAULT + deptno from emp") .fails("(?s)Encountered \"\\+ DEFAULT\" at .*"); sql("select power(0, DEFAULT ^+^ empno) from emp") .fails("(?s)Encountered \"\\+\" at .*"); sql("select * from emp join dept ^on^ DEFAULT") .fails("(?s)Encountered \"on DEFAULT\" at .*"); sql("select * from emp where empno ^>^ DEFAULT or deptno < 10") .fails("(?s)Encountered \"> DEFAULT\" at .*"); sql("select * from emp order by ^DEFAULT^ desc") .fails("(?s)Encountered \"DEFAULT\" at .*"); final String expected = "INSERT INTO `DEPT` (`NAME`, `DEPTNO`)\n" + "VALUES (ROW('a', DEFAULT))"; sql("insert into dept (name, deptno) values ('a', DEFAULT)") .ok(expected); sql("insert into dept (name, deptno) values ('a', 1 ^+^ DEFAULT)") .fails("(?s)Encountered \"\\+ DEFAULT\" at .*"); sql("insert into dept (name, deptno) select 'a'^,^ DEFAULT from (values 0)") .fails("(?s)Encountered \", DEFAULT\" at .*"); }
@Test public void testDefault() { sql("select ^DEFAULT^ from emp") .fails("(?s)Encountered \"DEFAULT\" at .*"); sql("select cast(empno ^+^ DEFAULT as double) from emp") .fails("(?s)Encountered \"\\+ DEFAULT\" at .*"); sql("select empno ^+^ DEFAULT + deptno from emp") .fails("(?s)Encountered \"\\+ DEFAULT\" at .*"); sql("select power(0, DEFAULT ^+^ empno) from emp") .fails("(?s)Encountered \"\\+\" at .*"); sql("select * from emp join dept ^on^ DEFAULT") .fails("(?s)Encountered \"on DEFAULT\" at .*"); sql("select * from emp where empno ^>^ DEFAULT or deptno < 10") .fails("(?s)Encountered \"> DEFAULT\" at .*"); sql("select * from emp order by ^DEFAULT^ desc") .fails("(?s)Encountered \"DEFAULT\" at .*"); final String expected = "INSERT INTO `DEPT` (`NAME`, `DEPTNO`)\n" + "VALUES (ROW('a', DEFAULT))"; sql("insert into dept (name, deptno) values ('a', DEFAULT)") .ok(expected); sql("insert into dept (name, deptno) values ('a', 1 ^+^ DEFAULT)") .fails("(?s)Encountered \"\\+ DEFAULT\" at .*"); sql("insert into dept (name, deptno) select 'a'^,^ DEFAULT from (values 0)") .fails("(?s)Encountered \", DEFAULT\" at .*"); }
@Test public void testUnnest() { check( "select*from unnest(x)", "SELECT *\n" + "FROM (UNNEST(`X`))"); check( "select*from unnest(x) AS T", "SELECT *\n" + "FROM (UNNEST(`X`)) AS `T`"); // UNNEST cannot be first word in query checkFails( "^unnest^(x)", "(?s)Encountered \"unnest\" at.*"); // UNNEST with more than one argument final String sql = "select * from dept,\n" + "unnest(dept.employees, dept.managers)"; final String expected = "SELECT *\n" + "FROM `DEPT`,\n" + "(UNNEST(`DEPT`.`EMPLOYEES`, `DEPT`.`MANAGERS`))"; sql(sql).ok(expected); // LATERAL UNNEST is not valid sql("select * from dept, ^lateral^ unnest(dept.employees)") .fails("(?s)Encountered \"lateral unnest\" at .*"); }
@Test public void testFunctionDefaultArgument() { sql("foo(1, DEFAULT, default, 'default', \"default\", 3)").expression() .ok("`FOO`(1, DEFAULT, DEFAULT, 'default', `default`, 3)"); sql("foo(DEFAULT)").expression() .ok("`FOO`(DEFAULT)"); sql("foo(x => 1, DEFAULT)").expression() .ok("`FOO`(`X` => 1, DEFAULT)"); sql("foo(y => DEFAULT, x => 1)").expression() .ok("`FOO`(`Y` => DEFAULT, `X` => 1)"); sql("foo(x => 1, y => DEFAULT)").expression() .ok("`FOO`(`X` => 1, `Y` => DEFAULT)"); sql("select sum(DISTINCT DEFAULT) from t group by x") .ok("SELECT SUM(DISTINCT DEFAULT)\n" + "FROM `T`\n" + "GROUP BY `X`"); checkExpFails("foo(x ^+^ DEFAULT)", "(?s).*Encountered \"\\+ DEFAULT\" at .*"); checkExpFails("foo(0, x ^+^ DEFAULT + y)", "(?s).*Encountered \"\\+ DEFAULT\" at .*"); checkExpFails("foo(0, DEFAULT ^+^ y)", "(?s).*Encountered \"\\+\" at .*"); }
@Test public void testFunctionDefaultArgument() { sql("foo(1, DEFAULT, default, 'default', \"default\", 3)").expression() .ok("`FOO`(1, DEFAULT, DEFAULT, 'default', `default`, 3)"); sql("foo(DEFAULT)").expression() .ok("`FOO`(DEFAULT)"); sql("foo(x => 1, DEFAULT)").expression() .ok("`FOO`(`X` => 1, DEFAULT)"); sql("foo(y => DEFAULT, x => 1)").expression() .ok("`FOO`(`Y` => DEFAULT, `X` => 1)"); sql("foo(x => 1, y => DEFAULT)").expression() .ok("`FOO`(`X` => 1, `Y` => DEFAULT)"); sql("select sum(DISTINCT DEFAULT) from t group by x") .ok("SELECT SUM(DISTINCT DEFAULT)\n" + "FROM `T`\n" + "GROUP BY `X`"); checkExpFails("foo(x ^+^ DEFAULT)", "(?s).*Encountered \"\\+ DEFAULT\" at .*"); checkExpFails("foo(0, x ^+^ DEFAULT + y)", "(?s).*Encountered \"\\+ DEFAULT\" at .*"); checkExpFails("foo(0, DEFAULT ^+^ y)", "(?s).*Encountered \"\\+\" at .*"); }
/** Tests MINUS, which is equivalent to EXCEPT but only supported in some * conformance levels (e.g. ORACLE). */ @Test public void testSetMinus() { final String pattern = "MINUS is not allowed under the current SQL conformance level"; final String sql = "select col1 from table1 MINUS select col1 from table2"; sql(sql).fails(pattern); conformance = SqlConformanceEnum.ORACLE_10; final String expected = "(SELECT `COL1`\n" + "FROM `TABLE1`\n" + "EXCEPT\n" + "SELECT `COL1`\n" + "FROM `TABLE2`)"; sql(sql).ok(expected); final String sql2 = "select col1 from table1 MINUS ALL select col1 from table2"; final String expected2 = "(SELECT `COL1`\n" + "FROM `TABLE1`\n" + "EXCEPT ALL\n" + "SELECT `COL1`\n" + "FROM `TABLE2`)"; sql(sql2).ok(expected2); }
@Test public void testSome() { final String sql = "select * from emp\n" + "where sal > some (select comm from emp)"; final String expected = "SELECT *\n" + "FROM `EMP`\n" + "WHERE (`SAL` > SOME (SELECT `COMM`\n" + "FROM `EMP`))"; sql(sql).ok(expected); // ANY is a synonym for SOME final String sql2 = "select * from emp\n" + "where sal > any (select comm from emp)"; sql(sql2).ok(expected); final String sql3 = "select * from emp\n" + "where name like (select ^some^ name from emp)"; sql(sql3).fails("(?s).*Encountered \"some\" at .*"); final String sql4 = "select * from emp\n" + "where name ^like^ some (select name from emp)"; sql(sql4).fails("(?s).*Encountered \"like some\" at .*"); final String sql5 = "select * from emp where empno = any (10,20)"; final String expected5 = "SELECT *\n" + "FROM `EMP`\n" + "WHERE (`EMPNO` = SOME (10, 20))"; sql(sql5).ok(expected5); }
@Test public void testMergeSelectSource() { final String sql = "merge into emps e " + "using (select * from tempemps where deptno is null) t " + "on e.empno = t.empno " + "when matched then update " + "set name = t.name, deptno = t.deptno, salary = t.salary * .1 " + "when not matched then insert (name, dept, salary) " + "values(t.name, 10, t.salary * .15)"; final String expected = "MERGE INTO `EMPS` AS `E`\n" + "USING (SELECT *\n" + "FROM `TEMPEMPS`\n" + "WHERE (`DEPTNO` IS NULL)) AS `T`\n" + "ON (`E`.`EMPNO` = `T`.`EMPNO`)\n" + "WHEN MATCHED THEN UPDATE SET `NAME` = `T`.`NAME`\n" + ", `DEPTNO` = `T`.`DEPTNO`\n" + ", `SALARY` = (`T`.`SALARY` * 0.1)\n" + "WHEN NOT MATCHED THEN INSERT (`NAME`, `DEPT`, `SALARY`) " + "(VALUES (ROW(`T`.`NAME`, 10, (`T`.`SALARY` * 0.15))))"; sql(sql).ok(expected) .node(not(isDdl())); }