Examples with SqlConformanceEnum org.apache.calcite.sql.validate.SqlConformanceEnum used on opensource projects

Example 1 with SqlConformanceEnum

use of org.apache.calcite.sql.validate.SqlConformanceEnum in project calcite by apache.

the class SqlValidatorTest method testOrdinalInGroupBy.

/**
 * Tests validation of ordinals in GROUP BY.
 *
 * @see SqlConformance#isGroupByOrdinal()
 */
@Test
void testOrdinalInGroupBy() {
    final SqlConformanceEnum lenient = SqlConformanceEnum.LENIENT;
    final SqlConformanceEnum strict = SqlConformanceEnum.STRICT_2003;
    sql("select ^empno^,deptno from emp group by 1, deptno").withConformance(strict).fails("Expression 'EMPNO' is not being grouped").withConformance(lenient).ok();
    sql("select ^emp.empno^ as e from emp group by 1").withConformance(strict).fails("Expression 'EMP.EMPNO' is not being grouped").withConformance(lenient).ok();
    sql("select 2 + ^emp.empno^ + 3 as e from emp group by 1").withConformance(strict).fails("Expression 'EMP.EMPNO' is not being grouped").withConformance(lenient).ok();
    sql("select ^e.empno^ from emp as e group by 1").withConformance(strict).fails("Expression 'E.EMPNO' is not being grouped").withConformance(lenient).ok();
    sql("select e.empno from emp as e group by 1, empno").withConformance(strict).ok().withConformance(lenient).ok();
    sql("select ^e.empno^ as eno from emp as e group by 1").withConformance(strict).fails("Expression 'E.EMPNO' is not being grouped").withConformance(lenient).ok();
    sql("select ^deptno^ as dno from emp group by cube(1)").withConformance(strict).fails("Expression 'DEPTNO' is not being grouped").withConformance(lenient).ok();
    sql("select 1 as dno from emp group by cube(1)").withConformance(strict).ok().withConformance(lenient).ok();
    sql("select deptno as dno, ename name, sum(sal) from emp\n" + "group by grouping sets ((1), (^name^, deptno))").withConformance(strict).fails("Column 'NAME' not found in any table").withConformance(lenient).ok();
    sql("select ^e.deptno^ from emp as e\n" + "join dept as d on e.deptno = d.deptno group by 1").withConformance(strict).fails("Expression 'E.DEPTNO' is not being grouped").withConformance(lenient).ok();
    sql("select ^deptno^,(select empno from emp) eno from dept" + " group by 1,2").withConformance(strict).fails("Expression 'DEPTNO' is not being grouped").withConformance(lenient).ok();
    sql("select ^empno^, count(*) from emp group by 1 order by 1").withConformance(strict).fails("Expression 'EMPNO' is not being grouped").withConformance(lenient).ok();
    sql("select ^empno^ eno, count(*) from emp group by 1 order by 1").withConformance(strict).fails("Expression 'EMPNO' is not being grouped").withConformance(lenient).ok();
    sql("select count(*) from (select 1 from emp" + " group by substring(ename from 2 for 3))").withConformance(strict).ok().withConformance(lenient).ok();
    sql("select deptno from emp group by deptno, ^100^").withConformance(lenient).fails("Ordinal out of range").withConformance(strict).ok();
    // Calcite considers integers in GROUP BY to be constants, so test passes.
    // Postgres considers them ordinals and throws out of range position error.
    sql("select deptno from emp group by ^100^, deptno").withConformance(lenient).fails("Ordinal out of range").withConformance(strict).ok();
}

Example 2 with SqlConformanceEnum

use of org.apache.calcite.sql.validate.SqlConformanceEnum in project calcite by apache.

the class SqlValidatorTest method testAliasInHaving.

/**
 * Tests validation of the aliases in HAVING.
 *
 * @see SqlConformance#isHavingAlias()
 */
@Test
void testAliasInHaving() {
    final SqlConformanceEnum lenient = SqlConformanceEnum.LENIENT;
    final SqlConformanceEnum strict = SqlConformanceEnum.STRICT_2003;
    sql("select count(empno) as e from emp having ^e^ > 10").withConformance(strict).fails("Column 'E' not found in any table").withConformance(lenient).ok();
    sql("select emp.empno as e from emp group by ^e^ having e > 10").withConformance(strict).fails("Column 'E' not found in any table").withConformance(lenient).ok();
    sql("select emp.empno as e from emp group by empno having ^e^ > 10").withConformance(strict).fails("Column 'E' not found in any table").withConformance(lenient).ok();
    sql("select e.empno from emp as e group by 1 having ^e.empno^ > 10").withConformance(strict).fails("Expression 'E.EMPNO' is not being grouped").withConformance(lenient).ok();
    // When alias is equal to one or more columns in the query then giving
    // priority to alias, but PostgreSQL throws ambiguous column error or gives
    // priority to column name.
    sql("select count(empno) as deptno from emp having ^deptno^ > 10").withConformance(strict).fails("Expression 'DEPTNO' is not being grouped").withConformance(lenient).ok();
    // Alias in aggregate is not allowed.
    sql("select empno as e from emp having max(^e^) > 10").withConformance(strict).fails("Column 'E' not found in any table").withConformance(lenient).fails("Column 'E' not found in any table");
    sql("select count(empno) as e from emp having ^e^ > 10").withConformance(strict).fails("Column 'E' not found in any table").withConformance(lenient).ok();
}

Example 3 with SqlConformanceEnum

use of org.apache.calcite.sql.validate.SqlConformanceEnum in project calcite by apache.

the class SqlValidatorTest method testInsertBindWithCustomColumnResolving.

@Test
void testInsertBindWithCustomColumnResolving() {
    final SqlConformanceEnum pragmatic = SqlConformanceEnum.PRAGMATIC_2003;
    final String sql = "insert into struct.t\n" + "values (?, ?, ?, ?, ?, ?, ?, ?, ?)";
    final String expected = "RecordType(VARCHAR(20) ?0, VARCHAR(20) ?1," + " INTEGER ?2, BOOLEAN ?3, INTEGER ?4, INTEGER ?5, INTEGER ?6," + " INTEGER ?7, INTEGER ?8)";
    sql(sql).ok().assertBindType(is(expected));
    final String sql2 = "insert into struct.t_nullables (c0, c2, c1) values (?, ?, ?)";
    final String expected2 = "RecordType(INTEGER ?0, INTEGER ?1, VARCHAR(20) ?2)";
    sql(sql2).withConformance(pragmatic).ok().assertBindType(is(expected2));
    final String sql3 = "insert into struct.t_nullables (f1.c0, f1.c2, f0.c1) values (?, ?, ?)";
    final String expected3 = "RecordType(INTEGER ?0, INTEGER ?1, INTEGER ?2)";
    sql(sql3).withConformance(pragmatic).ok().assertBindType(is(expected3));
    sql("insert into struct.t_nullables (c0, ^c4^, c1) values (?, ?, ?)").withConformance(pragmatic).fails("Unknown target column 'C4'");
    sql("insert into struct.t_nullables (^a0^, c2, c1) values (?, ?, ?)").withConformance(pragmatic).fails("Unknown target column 'A0'");
    final String sql4 = "insert into struct.t_nullables (\n" + "  f1.c0, ^f0.a0^, f0.c1) values (?, ?, ?)";
    sql(sql4).withConformance(pragmatic).fails("Unknown target column 'F0.A0'");
    final String sql5 = "insert into struct.t_nullables (\n" + "  f1.c0, f1.c2, ^f1.c0^) values (?, ?, ?)";
    sql(sql5).withConformance(pragmatic).fails("Target column '\"F1\".\"C0\"' is assigned more than once");
}

Example 4 with SqlConformanceEnum

use of org.apache.calcite.sql.validate.SqlConformanceEnum 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
void testAliasInGroupBy() {
    final SqlConformanceEnum lenient = SqlConformanceEnum.LENIENT;
    final SqlConformanceEnum strict = SqlConformanceEnum.STRICT_2003;
    // Group by
    sql("select empno as e from emp group by ^e^").withConformance(strict).fails("Column 'E' not found in any table").withConformance(lenient).ok();
    sql("select empno as e from emp group by ^e^").withConformance(strict).fails("Column 'E' not found in any table").withConformance(lenient).ok();
    sql("select emp.empno as e from emp group by ^e^").withConformance(strict).fails("Column 'E' not found in any table").withConformance(lenient).ok();
    sql("select e.empno from emp as e group by e.empno").withConformance(strict).ok().withConformance(lenient).ok();
    sql("select e.empno as eno from emp as e group by ^eno^").withConformance(strict).fails("Column 'ENO' not found in any table").withConformance(lenient).ok();
    sql("select deptno as dno from emp group by cube(^dno^)").withConformance(strict).fails("Column 'DNO' not found in any table").withConformance(lenient).ok();
    sql("select deptno as dno, ename name, sum(sal) from emp\n" + "group by grouping sets ((^dno^), (name, deptno))").withConformance(strict).fails("Column 'DNO' not found in any table").withConformance(lenient).ok();
    sql("select ename as deptno from emp as e join dept as d on " + "e.deptno = d.deptno group by ^deptno^").withConformance(lenient).ok();
    sql("select t.e, count(*) from (select empno as e from emp) t group by e").withConformance(strict).ok().withConformance(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^").withConformance(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").withConformance(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").withConformance(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").withConformance(lenient).fails(ERR_AGG_IN_GROUP_BY).withConformance(strict).ok();
    sql("select deptno,(select empno + 1 from emp) eno\n" + "from dept group by deptno,^eno^").withConformance(strict).fails("Column 'ENO' not found in any table").withConformance(lenient).ok();
    sql("select empno as e, deptno as e\n" + "from emp group by ^e^").withConformance(lenient).fails("Column 'E' is ambiguous");
    sql("select empno, ^count(*)^ c from emp group by empno, c").withConformance(lenient).fails(ERR_AGG_IN_GROUP_BY);
    sql("select deptno + empno as d, deptno + empno + mgr from emp" + " group by d,mgr").withConformance(lenient).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").withConformance(lenient).ok();
    sql("select count(*) from " + "(select ename AS deptno FROM emp, dept GROUP BY deptno) t").withConformance(lenient).ok();
    sql("select empno + deptno AS \"z\" FROM emp GROUP BY \"Z\"").withConformance(lenient).withCaseSensitive(false).ok();
    sql("select empno + deptno as c, ^c^ + mgr as d from emp group by c, d").withConformance(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^").withConformance(strict).fails("Column 'E' not found in any table");
}