@Test public void testFunction() { check("select substring('Eggs and ham', 1, 3 + 2) || ' benedict' from emp", "SELECT (SUBSTRING('Eggs and ham' FROM 1 FOR (3 + 2)) || ' benedict')\n" + "FROM `EMP`"); checkExp( "log10(1)\r\n+power(2, mod(\r\n3\n\t\t\f\n,ln(4))*log10(5)-6*log10(7/abs(8)+9))*power(10,11)", "(LOG10(1) + (POWER(2, (((MOD(3, LN(4))) * LOG10(5)) - (6 * LOG10(((7 / ABS(8)) + 9))))) * POWER(10, 11)))"); }
@Test public void testExplicitTable() { check("table emp", "(TABLE `EMP`)"); // FIXME should fail at "123" checkFails( "^table^ 123", "(?s)Encountered \"table 123\" at line 1, column 1\\.\n.*"); }
@Test public void testColumnAliasWithAs() { check( "select 1 as foo from emp", "SELECT 1 AS `FOO`\n" + "FROM `EMP`"); }
@Test public void testFunction() { check("select substring('Eggs and ham', 1, 3 + 2) || ' benedict' from emp", "SELECT (SUBSTRING('Eggs and ham' FROM 1 FOR (3 + 2)) || ' benedict')\n" + "FROM `EMP`"); checkExp( "log10(1)\r\n+power(2, mod(\r\n3\n\t\t\f\n,ln(4))*log10(5)-6*log10(7/abs(8)+9))*power(10,11)", "(LOG10(1) + (POWER(2, (((MOD(3, LN(4))) * LOG10(5)) - (6 * LOG10(((7 / ABS(8)) + 9))))) * POWER(10, 11)))"); }
@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 testWith() { check( "with femaleEmps as (select * from emps where gender = 'F')" + "select deptno from femaleEmps", "WITH `FEMALEEMPS` AS (SELECT *\n" + "FROM `EMPS`\n" + "WHERE (`GENDER` = 'F')) (SELECT `DEPTNO`\n" + "FROM `FEMALEEMPS`)"); }
@Test public void testWithValues() { check( "with v(i,c) as (values (1, 'a'), (2, 'bb'))\n" + "select c, i from v", "WITH `V` (`I`, `C`) AS (VALUES (ROW(1, 'a')),\n" + "(ROW(2, 'bb'))) (SELECT `C`, `I`\n" + "FROM `V`)"); }
@Test public void testWithNestedInSubQuery() { // SQL standard does not allow sub-query to contain WITH but we do check("with emp2 as (select * from emp)\n" + "(\n" + " with dept2 as (select * from dept)\n" + " select 1 as uno from empDept)", "WITH `EMP2` AS (SELECT *\n" + "FROM `EMP`) (WITH `DEPT2` AS (SELECT *\n" + "FROM `DEPT`) (SELECT 1 AS `UNO`\n" + "FROM `EMPDEPT`))"); }
@Test public void testInList() { check( "select * from emp where deptno in (10, 20) and gender = 'F'", "SELECT *\n" + "FROM `EMP`\n" + "WHERE ((`DEPTNO` IN (10, 20)) AND (`GENDER` = 'F'))"); }
@Test public void testJoinOn() { check( "select * from a left join b on 1 = 1 and 2 = 2 where 3 = 3", "SELECT *\n" + "FROM `A`\n" + "LEFT JOIN `B` ON ((1 = 1) AND (2 = 2))\n" + "WHERE (3 = 3)"); }
@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 testQueryInFrom() { // one query with 'as', the other without check( "select * from (select * from emp) as e join (select * from dept) d", "SELECT *\n" + "FROM (SELECT *\n" + "FROM `EMP`) AS `E`\n" + "INNER JOIN (SELECT *\n" + "FROM `DEPT`) AS `D`"); }
@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 testCollectionTableWithColumnListParam() { check( "select * from table(dedup(cursor(select * from emps)," + "row(empno, name)))", "SELECT *\n" + "FROM TABLE(`DEDUP`((CURSOR ((SELECT *\n" + "FROM `EMPS`))), (ROW(`EMPNO`, `NAME`))))"); }
@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 testNestedSelect() { check( "select * from (select * from emp)", "SELECT *\n" + "FROM (SELECT *\n" + "FROM `EMP`)"); }
@Test public void testSelectFromExplicitTable() { check( "select * from (table emp)", "SELECT *\n" + "FROM (TABLE `EMP`)"); }
@Test public void testDeleteWhere() { check( "delete from emps where empno=12", "DELETE FROM `EMPS`\n" + "WHERE (`EMPNO` = 12)"); }