Examples with SqlTester org.apache.calcite.sql.test.SqlTester used on opensource projects

Example 26 with SqlTester

use of org.apache.calcite.sql.test.SqlTester in project calcite by apache.

the class SqlValidatorTest method testAliasInGroupBy.

/**
 * Tests validation of the aliases in GROUP BY.
 *
 * <p>Test case for
 * <a href="https://issues.apache.org/jira/browse/CALCITE-1306">[CALCITE-1306]
 * Allow GROUP BY and HAVING to reference SELECT expressions by ordinal and
 * alias</a>.
 *
 * @see SqlConformance#isGroupByAlias()
 */
@Test
public void testAliasInGroupBy() {
    final SqlTester lenient = tester.withConformance(SqlConformanceEnum.LENIENT);
    final SqlTester strict = tester.withConformance(SqlConformanceEnum.STRICT_2003);
    // Group by
    sql("select empno as e from emp group by ^e^").tester(strict).fails("Column 'E' not found in any table").tester(lenient).sansCarets().ok();
    sql("select empno as e from emp group by ^e^").tester(strict).fails("Column 'E' not found in any table").tester(lenient).sansCarets().ok();
    sql("select emp.empno as e from emp group by ^e^").tester(strict).fails("Column 'E' not found in any table").tester(lenient).sansCarets().ok();
    sql("select e.empno from emp as e group by e.empno").tester(strict).ok().tester(lenient).ok();
    sql("select e.empno as eno from emp as e group by ^eno^").tester(strict).fails("Column 'ENO' not found in any table").tester(lenient).sansCarets().ok();
    sql("select deptno as dno from emp group by cube(^dno^)").tester(strict).fails("Column 'DNO' not found in any table").tester(lenient).sansCarets().ok();
    sql("select deptno as dno, ename name, sum(sal) from emp\n" + "group by grouping sets ((^dno^), (name, deptno))").tester(strict).fails("Column 'DNO' not found in any table").tester(lenient).sansCarets().ok();
    sql("select ename as deptno from emp as e join dept as d on " + "e.deptno = d.deptno group by ^deptno^").tester(lenient).sansCarets().ok();
    sql("select t.e, count(*) from (select empno as e from emp) t group by e").tester(strict).ok().tester(lenient).ok();
    // The following 2 tests have the same SQL but fail for different reasons.
    sql("select t.e, count(*) as c from " + " (select empno as e from emp) t group by e,^c^").tester(strict).fails("Column 'C' not found in any table");
    sql("select t.e, ^count(*)^ as c from " + " (select empno as e from emp) t group by e,c").tester(lenient).fails(ERR_AGG_IN_GROUP_BY);
    sql("select t.e, e + ^count(*)^ as c from " + " (select empno as e from emp) t group by e,c").tester(lenient).fails(ERR_AGG_IN_GROUP_BY);
    sql("select t.e, e + ^count(*)^ as c from " + " (select empno as e from emp) t group by e,2").tester(lenient).fails(ERR_AGG_IN_GROUP_BY).tester(strict).sansCarets().ok();
    sql("select deptno,(select empno + 1 from emp) eno\n" + "from dept group by deptno,^eno^").tester(strict).fails("Column 'ENO' not found in any table").tester(lenient).sansCarets().ok();
    sql("select empno as e, deptno as e\n" + "from emp group by ^e^").tester(lenient).fails("Column 'E' is ambiguous");
    sql("select empno, ^count(*)^ c from emp group by empno, c").tester(lenient).fails(ERR_AGG_IN_GROUP_BY);
    sql("select deptno + empno as d, deptno + empno + mgr from emp" + " group by d,mgr").tester(lenient).sansCarets().ok();
    // When alias is equal to one or more columns in the query then giving
    // priority to alias. But Postgres may throw ambiguous column error or give
    // priority to column name.
    sql("select count(*) from (\n" + "  select ename AS deptno FROM emp GROUP BY deptno) t").tester(lenient).sansCarets().ok();
    sql("select count(*) from " + "(select ename AS deptno FROM emp, dept GROUP BY deptno) t").tester(lenient).sansCarets().ok();
    sql("select empno + deptno AS \"z\" FROM emp GROUP BY \"Z\"").tester(lenient.withCaseSensitive(false)).sansCarets().ok();
    sql("select empno + deptno as c, ^c^ + mgr as d from emp group by c, d").tester(lenient).fails("Column 'C' not found in any table");
    // Group by alias with strict conformance should fail.
    sql("select empno as e from emp group by ^e^").tester(strict).fails("Column 'E' not found in any table");
}