@Test public void testFunctionWithDistinct() { checkExp("count(DISTINCT 1)", "COUNT(DISTINCT 1)"); checkExp("count(ALL 1)", "COUNT(ALL 1)"); checkExp("count(1)", "COUNT(1)"); check( "select count(1), count(distinct 2) from emp", "SELECT COUNT(1), COUNT(DISTINCT 2)\n" + "FROM `EMP`"); }
@Test public void testTabStop() { check( "SELECT *\n\tFROM mytable", "SELECT *\n" + "FROM `MYTABLE`"); // make sure that the tab stops do not affect the placement of the // error tokens checkFails( "SELECT *\tFROM mytable\t\tWHERE x ^=^ = y AND b = 1", "(?s).*Encountered \"= =\" at line 1, column 32\\..*"); }
@Test public void testJoinCross() { check( "select * from a as a2 cross join b", "SELECT *\n" + "FROM `A` AS `A2`\n" + "CROSS JOIN `B`"); }
@Test public void testSelectDistinct() { check( "select distinct foo from bar", "SELECT DISTINCT `FOO`\n" + "FROM `BAR`"); }
@Test public void testWhere() { check( "select * from emp where empno > 5 and gender = 'F'", "SELECT *\n" + "FROM `EMP`\n" + "WHERE ((`EMPNO` > 5) AND (`GENDER` = 'F'))"); }
@Test public void testCollectionTableWithCursorParam() { check( "select * from table(dedup(cursor(select * from emps),'name'))", "SELECT *\n" + "FROM TABLE(`DEDUP`((CURSOR ((SELECT *\n" + "FROM `EMPS`))), 'name'))"); }
@Test public void testExplain() { check( "explain plan for select * from emps", "EXPLAIN PLAN INCLUDING ATTRIBUTES WITH IMPLEMENTATION FOR\n" + "SELECT *\n" + "FROM `EMPS`"); }
@Test public void testExplainWithoutImpl() { check( "explain plan without implementation for select * from emps", "EXPLAIN PLAN INCLUDING ATTRIBUTES WITHOUT IMPLEMENTATION FOR\n" + "SELECT *\n" + "FROM `EMPS`"); }
@Test public void testWindowInSubquery() { check( "select * from ( select sum(x) over w, sum(y) over w from s window w as (range interval '1' minute preceding))", "SELECT *\n" + "FROM (SELECT (SUM(`X`) OVER `W`), (SUM(`Y`) OVER `W`)\n" + "FROM `S`\n" + "WINDOW `W` AS (RANGE INTERVAL '1' MINUTE PRECEDING))"); }
@Test public void testColumnAliasWithAs() { check( "select 1 as foo from emp", "SELECT 1 AS `FOO`\n" + "FROM `EMP`"); }
@Test public void testColumnAliasWithoutAs() { check( "select 1 foo from emp", "SELECT 1 AS `FOO`\n" + "FROM `EMP`"); }
@Test public void testNot() { check( "select not true, not false, not null, not unknown from t", "SELECT (NOT TRUE), (NOT FALSE), (NOT NULL), (NOT UNKNOWN)\n" + "FROM `T`"); }
@Test public void testHavingAfterGroup() { check( "select deptno from emp group by deptno, emp having count(*) > 5 and 1 = 2 order by 5, 2", "SELECT `DEPTNO`\n" + "FROM `EMP`\n" + "GROUP BY `DEPTNO`, `EMP`\n" + "HAVING ((COUNT(*) > 5) AND (1 = 2))\n" + "ORDER BY 5, 2"); }
@Test public void testNaturalCrossJoin() { check( "select * from a natural cross join b", "SELECT *\n" + "FROM `A`\n" + "NATURAL CROSS JOIN `B`"); }
@Test public void testOrder() { check( "select * from emp order by empno, gender desc, deptno asc, empno asc, name desc", "SELECT *\n" + "FROM `EMP`\n" + "ORDER BY `EMPNO`, `GENDER` DESC, `DEPTNO`, `EMPNO`, `NAME` DESC"); }
@Test public void testOrderNullsFirst() { check( "select * from emp order by gender desc nulls last, deptno asc nulls first, empno nulls last", "SELECT *\n" + "FROM `EMP`\n" + "ORDER BY `GENDER` DESC NULLS LAST, `DEPTNO` NULLS FIRST, `EMPNO` NULLS LAST"); }
@Test public void testScalarQueryInSelect() { check( "select x, (select count(*) from dept where dept.deptno = emp.deptno) from emp", "SELECT `X`, (SELECT COUNT(*)\n" + "FROM `DEPT`\n" + "WHERE (`DEPT`.`DEPTNO` = `EMP`.`DEPTNO`))\n" + "FROM `EMP`"); }
@Test public void testSelectList() { check( "select * from emp, dept", "SELECT *\n" + "FROM `EMP`,\n" + "`DEPT`"); }
@Test public void testSelectFromExplicitTable() { check( "select * from (table emp)", "SELECT *\n" + "FROM (TABLE `EMP`)"); }
@Test public void testCollectionTableWithCursorParam() { check( "select * from table(dedup(cursor(select * from emps),'name'))", "SELECT *\n" + "FROM TABLE(`DEDUP`((CURSOR ((SELECT *\n" + "FROM `EMPS`))), 'name'))"); }