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

Example 16 with SqlTester

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

the class SqlValidatorTest method testCaseInsensitiveInsert.

/**
 * Tests that it is an error to insert into the same column twice, even using
 * case-insensitive matching.
 */
@Test
public void testCaseInsensitiveInsert() {
    final SqlTester tester1 = tester.withCaseSensitive(false).withQuoting(Quoting.BRACKET);
    tester1.checkQueryFails("insert into EMP ([EMPNO], deptno, ^[empno]^)\n" + " values (1, 1, 1)", "Target column 'EMPNO' is assigned more than once");
}

Example 17 with SqlTester

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

the class SqlValidatorTest method testAliasInHaving.

/**
 * Tests validation of the aliases in HAVING.
 *
 * @see SqlConformance#isHavingAlias()
 */
@Test
public void testAliasInHaving() {
    final SqlTester lenient = tester.withConformance(SqlConformanceEnum.LENIENT);
    final SqlTester strict = tester.withConformance(SqlConformanceEnum.STRICT_2003);
    sql("select count(empno) as e from emp having ^e^ > 10").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^ having e > 10").tester(strict).fails("Column 'E' not found in any table").tester(lenient).sansCarets().ok();
    sql("select emp.empno as e from emp group by empno having ^e^ > 10").tester(strict).fails("Column 'E' not found in any table").tester(lenient).sansCarets().ok();
    sql("select e.empno from emp as e group by 1 having ^e.empno^ > 10").tester(strict).fails("Expression 'E.EMPNO' is not being grouped").tester(lenient).sansCarets().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").tester(strict).fails("Expression 'DEPTNO' is not being grouped").tester(lenient).sansCarets().ok();
    // Alias in aggregate is not allowed.
    sql("select empno as e from emp having max(^e^) > 10").tester(strict).fails("Column 'E' not found in any table").tester(lenient).fails("Column 'E' not found in any table");
    sql("select count(empno) as e from emp having ^e^ > 10").tester(strict).fails("Column 'E' not found in any table").tester(lenient).sansCarets().ok();
}

Example 18 with SqlTester

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

the class SqlValidatorTest method testCaseInsensitiveTableAliasInGroupBy.

/**
 * Test case for
 * <a href="https://issues.apache.org/jira/browse/CALCITE-1305">[CALCITE-1305]
 * Case-insensitive table aliases and GROUP BY</a>.
 */
@Test
public void testCaseInsensitiveTableAliasInGroupBy() {
    final SqlTester tester1 = tester.withCaseSensitive(false).withUnquotedCasing(Casing.UNCHANGED);
    tester1.checkQuery("select deptno, count(*) from EMP AS emp\n" + "group by eMp.deptno");
    tester1.checkQuery("select deptno, count(*) from EMP AS EMP\n" + "group by eMp.deptno");
    tester1.checkQuery("select deptno, count(*) from EMP\n" + "group by eMp.deptno");
    tester1.checkQuery("select * from EMP where exists (\n" + "  select 1 from dept\n" + "  group by eMp.deptno)");
    tester1.checkQuery("select deptno, count(*) from EMP group by DEPTNO");
}

Example 19 with SqlTester

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

the class SqlValidatorTest method testLexAndQuoting.

@Test
public void testLexAndQuoting() {
    final SqlTester tester1 = tester.withLex(Lex.JAVA).withQuoting(Quoting.DOUBLE_QUOTE);
    // in Java mode, creating identifiers with spaces is not encouraged, but you
    // can use double-quote if you really have to
    tester1.checkResultType("select \"x[y] z \" from (\n" + "  select e.EMPNO as \"x[y] z \" from EMP as e)", "RecordType(INTEGER NOT NULL x[y] z ) NOT NULL");
}

Example 20 with SqlTester

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

the class SqlValidatorTest method testCaseInsensitiveTableAlias.

/**
 * Test case for
 * <a href="https://issues.apache.org/jira/browse/CALCITE-319">[CALCITE-319]
 * Table aliases should follow case-sensitivity policy</a>.
 */
@Test
public void testCaseInsensitiveTableAlias() {
    final SqlTester tester1 = tester.withCaseSensitive(false).withQuoting(Quoting.BRACKET);
    final SqlTester tester2 = tester.withQuoting(Quoting.BRACKET);
    // Table aliases should follow case-sensitivity preference.
    // 
    // In MySQL, table aliases are case-insensitive:
    // mysql> select `D`.day from DAYS as `d`, DAYS as `D`;
    // ERROR 1066 (42000): Not unique table/alias: 'D'
    tester1.checkQueryFails("select count(*) from dept as [D], ^dept as [d]^", "Duplicate relation name 'd' in FROM clause");
    tester2.checkQuery("select count(*) from dept as [D], dept as [d]");
    tester2.checkQueryFails("select count(*) from dept as [D], ^dept as [D]^", "Duplicate relation name 'D' in FROM clause");
}