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

Example 6 with SqlTester

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

the class SqlValidatorTest method testInsertShouldNotCheckForDefaultValue.

/**
 * Test case for
 * <a href="https://issues.apache.org/jira/browse/CALCITE-1510">[CALCITE-1510]
 * INSERT/UPSERT should allow fewer values than columns</a>,
 * check for default value only when target field is null.
 */
@Test
public void testInsertShouldNotCheckForDefaultValue() {
    final int c = MockCatalogReader.CountingFactory.THREAD_CALL_COUNT.get().get();
    final SqlTester pragmaticTester = tester.withConformance(SqlConformanceEnum.PRAGMATIC_2003);
    final String sql1 = "insert into emp values(1, 'nom', 'job', 0, " + "timestamp '1970-01-01 00:00:00', 1, 1, 1, false)";
    pragmaticTester.checkQuery(sql1);
    assertThat("Should not check for default value if column is in INSERT", MockCatalogReader.CountingFactory.THREAD_CALL_COUNT.get().get(), is(c));
    // Now add a list of target columns, keeping the query otherwise the same.
    final String sql2 = "insert into emp (empno, ename, job, mgr, hiredate,\n" + "  sal, comm, deptno, slacker)\n" + "values(1, 'nom', 'job', 0,\n" + "  timestamp '1970-01-01 00:00:00', 1, 1, 1, false)";
    pragmaticTester.checkQuery(sql2);
    assertThat("Should not check for default value if column is in INSERT", MockCatalogReader.CountingFactory.THREAD_CALL_COUNT.get().get(), is(c));
    // Now remove SLACKER, which is NOT NULL, from the target list.
    final String sql3 = "insert into ^emp^ (empno, ename, job, mgr, hiredate,\n" + "  sal, comm, deptno)\n" + "values(1, 'nom', 'job', 0,\n" + "  timestamp '1970-01-01 00:00:00', 1, 1, 1)";
    pragmaticTester.checkQueryFails(sql3, "Column 'SLACKER' has no default value and does not allow NULLs");
    assertThat("Should not check for default value, even if if column is missing" + "from INSERT and nullable", MockCatalogReader.CountingFactory.THREAD_CALL_COUNT.get().get(), is(c));
    // Now remove DEPTNO, which has a default value, from the target list.
    // Will generate an extra call to newColumnDefaultValue at sql-to-rel time,
    // just not yet.
    final String sql4 = "insert into ^emp^ (empno, ename, job, mgr, hiredate,\n" + "  sal, comm, slacker)\n" + "values(1, 'nom', 'job', 0,\n" + "  timestamp '1970-01-01 00:00:00', 1, 1, false)";
    pragmaticTester.checkQuery(sql4);
    assertThat("Missing DEFAULT column generates a call to factory", MockCatalogReader.CountingFactory.THREAD_CALL_COUNT.get().get(), is(c));
}

Example 7 with SqlTester

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

the class SqlValidatorTest method testOrdinalInGroupBy.

/**
 * Tests validation of ordinals in GROUP BY.
 *
 * @see SqlConformance#isGroupByOrdinal()
 */
@Test
public void testOrdinalInGroupBy() {
    final SqlTester lenient = tester.withConformance(SqlConformanceEnum.LENIENT);
    final SqlTester strict = tester.withConformance(SqlConformanceEnum.STRICT_2003);
    sql("select ^empno^,deptno from emp group by 1, deptno").tester(strict).fails("Expression 'EMPNO' is not being grouped").tester(lenient).sansCarets().ok();
    sql("select ^emp.empno^ as e from emp group by 1").tester(strict).fails("Expression 'EMP.EMPNO' is not being grouped").tester(lenient).sansCarets().ok();
    sql("select 2 + ^emp.empno^ + 3 as e from emp group by 1").tester(strict).fails("Expression 'EMP.EMPNO' is not being grouped").tester(lenient).sansCarets().ok();
    sql("select ^e.empno^ from emp as e group by 1").tester(strict).fails("Expression 'E.EMPNO' is not being grouped").tester(lenient).sansCarets().ok();
    sql("select e.empno from emp as e group by 1, empno").tester(strict).ok().tester(lenient).sansCarets().ok();
    sql("select ^e.empno^ as eno from emp as e group by 1").tester(strict).fails("Expression 'E.EMPNO' is not being grouped").tester(lenient).sansCarets().ok();
    sql("select ^deptno^ as dno from emp group by cube(1)").tester(strict).fails("Expression 'DEPTNO' is not being grouped").tester(lenient).sansCarets().ok();
    sql("select 1 as dno from emp group by cube(1)").tester(strict).ok().tester(lenient).sansCarets().ok();
    sql("select deptno as dno, ename name, sum(sal) from emp\n" + "group by grouping sets ((1), (^name^, deptno))").tester(strict).fails("Column 'NAME' not found in any table").tester(lenient).sansCarets().ok();
    sql("select ^e.deptno^ from emp as e\n" + "join dept as d on e.deptno = d.deptno group by 1").tester(strict).fails("Expression 'E.DEPTNO' is not being grouped").tester(lenient).sansCarets().ok();
    sql("select ^deptno^,(select empno from emp) eno from dept" + " group by 1,2").tester(strict).fails("Expression 'DEPTNO' is not being grouped").tester(lenient).sansCarets().ok();
    sql("select ^empno^, count(*) from emp group by 1 order by 1").tester(strict).fails("Expression 'EMPNO' is not being grouped").tester(lenient).sansCarets().ok();
    sql("select ^empno^ eno, count(*) from emp group by 1 order by 1").tester(strict).fails("Expression 'EMPNO' is not being grouped").tester(lenient).sansCarets().ok();
    sql("select count(*) from (select 1 from emp" + " group by substring(ename from 2 for 3))").tester(strict).ok().tester(lenient).sansCarets().ok();
    sql("select deptno from emp group by deptno, ^100^").tester(lenient).fails("Ordinal out of range").tester(strict).sansCarets().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").tester(lenient).fails("Ordinal out of range").tester(strict).sansCarets().ok();
}

Example 8 with SqlTester

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

the class SqlValidatorTest method testCaseInsensitiveTables.

/**
 * Tests using case-insensitive matching of table names.
 */
@Test
public void testCaseInsensitiveTables() {
    final SqlTester tester1 = tester.withLex(Lex.SQL_SERVER);
    tester1.checkQuery("select eMp.* from (select * from emp) as EmP");
    tester1.checkQueryFails("select ^eMp^.* from (select * from emp as EmP)", "Unknown identifier 'eMp'");
    tester1.checkQuery("select eMp.* from (select * from emP) as EmP");
    tester1.checkQuery("select eMp.empNo from (select * from emP) as EmP");
    tester1.checkQuery("select empNo from (select Empno from emP) as EmP");
    tester1.checkQuery("select empNo from (select Empno from emP)");
}

Example 9 with SqlTester

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

the class SqlValidatorTest method testInsertBindSubset.

@Test
public void testInsertBindSubset() {
    final SqlTester pragmaticTester = tester.withConformance(SqlConformanceEnum.PRAGMATIC_2003);
    // VALUES
    final String sql0 = "insert into empnullables \n" + "values (?, ?, ?)";
    sql(sql0).tester(pragmaticTester).ok().bindType("RecordType(INTEGER ?0, VARCHAR(20) ?1, VARCHAR(10) ?2)");
    // multiple VALUES
    final String sql1 = "insert into empnullables\n" + "values (?, 'Pat', 'Tailor'), (2, ?, ?),\n" + " (3, 'Tod', ?), (4, 'Arthur', null)";
    sql(sql1).tester(pragmaticTester).ok().bindType("RecordType(INTEGER ?0, VARCHAR(20) ?1, VARCHAR(10) ?2, VARCHAR(10) ?3)");
    // VALUES with expression
    sql("insert into empnullables values (? + 1, ?)").tester(pragmaticTester).ok().bindType("RecordType(INTEGER ?0, VARCHAR(20) ?1)");
    // SELECT
    sql("insert into empnullables select ?, ? from (values (1))").tester(pragmaticTester).ok().bindType("RecordType(INTEGER ?0, VARCHAR(20) ?1)");
    // WITH
    final String sql3 = "insert into empnullables \n" + "with v as (values ('a'))\n" + "select ?, ? from (values (1))";
    sql(sql3).tester(pragmaticTester).ok().bindType("RecordType(INTEGER ?0, VARCHAR(20) ?1)");
    // UNION
    final String sql2 = "insert into empnullables \n" + "select ?, ? from (values (1))\n" + "union all\n" + "select ?, ? from (values (time '1:2:3'))";
    final String expected2 = "RecordType(INTEGER ?0, VARCHAR(20) ?1," + " INTEGER ?2, VARCHAR(20) ?3)";
    sql(sql2).tester(pragmaticTester).ok().bindType(expected2);
}

Example 10 with SqlTester

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

the class SqlValidatorTest method testInsertBindSubsetWithCustomInitializerExpressionFactory.

@Test
public void testInsertBindSubsetWithCustomInitializerExpressionFactory() {
    final SqlTester pragmaticTester = tester.withConformance(SqlConformanceEnum.PRAGMATIC_2003);
    sql("insert into empdefaults values (101, ?)").tester(pragmaticTester).ok().bindType("RecordType(VARCHAR(20) ?0)");
    pragmaticTester.checkQueryFails("insert into empdefaults ^values (null, ?)^", "Column 'EMPNO' has no default value and does not allow NULLs");
}