Examples of org.eigenbase.sql.test.SqlTester$ResultChecker

org.eigenbase.sql.test.SqlTester
SqlTester defines a callback for testing SQL queries and expressions.
The idea is that when you define an operator (or another piece of SQL functionality), you can define the logical behavior of that operator once, as part of that operator. Later you can define one or more physical implementations of that operator, and test them all using the same set of tests.
Specific implementations of SqlTester might evaluate the queries in different ways, for example, using a C++ versus Java calculator. An implementation might even ignore certain calls altogether.

    sensitive.checkQuery(sql1);
  }


  /** 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)");
  }

View Full Code Here

        + " null,"
        + " null}");
  }


  @Test public void testBrackets() {
    final SqlTester tester1 = tester.withQuoting(Quoting.BRACKET);
    tester1.checkResultType(
        "select [e].EMPNO from [EMP] as [e]",
        "RecordType(INTEGER NOT NULL EMPNO) NOT NULL");


    tester1.checkQueryFails(
        "select ^e^.EMPNO from [EMP] as [e]",
        "Table 'E' not found");


    tester1.checkQueryFails(
        "select ^x^ from (\n"
        + "  select [e].EMPNO as [x] from [EMP] as [e])",
        "Column 'X' not found in any table");


    tester1.checkQueryFails(
        "select EMP.^\"x\"^ from EMP",
        "(?s).*Encountered \"\\. \\\\\"\" at line .*");


    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");
  }

View Full Code Here

        + "  select [e].EMPNO as [x[y]] z ] from [EMP] as [e])",
        "RecordType(INTEGER NOT NULL x[y] z ) NOT NULL");
  }


  @Test public void testLexJava() {
    final SqlTester tester1 = tester.withLex(Lex.JAVA);
    tester1.checkResultType(
        "select e.EMPNO from EMP as e",
        "RecordType(INTEGER NOT NULL EMPNO) NOT NULL");


    tester1.checkQueryFails(
        "select ^e^.EMPNO from EMP as E",
        "Table 'e' not found");


    tester1.checkQueryFails(
        "select ^E^.EMPNO from EMP as e",
        "Table 'E' not found");


    tester1.checkQueryFails(
        "select ^x^ from (\n"
        + "  select e.EMPNO as X from EMP as e)",
        "Column 'x' not found in any table");


    // double-quotes are not valid in this lexical convention
    tester1.checkQueryFails(
        "select EMP.^\"x\"^ from EMP",
        "(?s).*Encountered \"\\. \\\\\"\" at line .*");


    // in Java mode, creating identifiers with spaces is not encouraged, but you
    // can use back-ticks 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");
  }

View Full Code Here


  /** Test case for
   * <a href="https://github.com/julianhyde/optiq/issues/145">optiq-145,
   * "Unexpected upper-casing of keywords when using java lexer"</a>. */
  @Test public void testLexJavaKeyword() {
    final SqlTester tester1 = tester.withLex(Lex.JAVA);
    tester1.checkResultType(
        "select path, x from (select 1 as path, 2 as x from (values (true)))",
        "RecordType(INTEGER NOT NULL path, INTEGER NOT NULL x) NOT NULL");
    tester1.checkResultType(
        "select path, x from (select 1 as `path`, 2 as x from (values (true)))",
        "RecordType(INTEGER NOT NULL path, INTEGER NOT NULL x) NOT NULL");
    tester1.checkResultType(
        "select `path`, x from (select 1 as path, 2 as x from (values (true)))",
        "RecordType(INTEGER NOT NULL path, INTEGER NOT NULL x) NOT NULL");
    tester1.checkFails(
        "select ^PATH^ from (select 1 as path from (values (true)))",
        "Column 'PATH' not found in any table",
        false);
    tester1.checkFails(
        "select t.^PATH^ from (select 1 as path from (values (true))) as t",
        "Column 'PATH' not found in table 't'",
        false);
    tester1.checkQueryFails(
        "select t.x, t.^PATH^ from (values (true, 1)) as t(path, x)",
        "Column 'PATH' not found in table 't'");


    // Built-in functions can be written in any case, even those with no args,
    // and regardless of spaces between function name and open parenthesis.
    tester1.checkQuery("values (current_timestamp, floor(2.5), ceil (3.5))");
    tester1.checkQuery("values (CURRENT_TIMESTAMP, FLOOR(2.5), CEIL (3.5))");
    tester1.checkResultType(
        "values (CURRENT_TIMESTAMP, CEIL (3.5))",
        "RecordType(TIMESTAMP(0) NOT NULL CURRENT_TIMESTAMP, DECIMAL(2, 0) NOT NULL EXPR$1) NOT NULL");
  }

View Full Code Here

        "values (CURRENT_TIMESTAMP, CEIL (3.5))",
        "RecordType(TIMESTAMP(0) NOT NULL CURRENT_TIMESTAMP, DECIMAL(2, 0) NOT NULL EXPR$1) NOT NULL");
  }


  @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");
  }

View Full Code Here

        "RecordType(INTEGER NOT NULL x[y] z ) NOT NULL");
  }


  /** Tests using case-insensitive matching of identifiers. */
  @Test public void testCaseInsensitive() {
    final SqlTester tester1 = tester
        .withCaseSensitive(false)
        .withQuoting(Quoting.BRACKET);
    final SqlTester tester2 = tester.withQuoting(Quoting.BRACKET);


    tester1.checkQuery("select EMPNO from EMP");
    tester1.checkQuery("select empno from emp");
    tester1.checkQuery("select [empno] from [emp]");
    tester1.checkQuery("select [E].[empno] from [emp] as e");
    tester1.checkQuery("select t.[x] from (\n"
        + "  select [E].[empno] as x from [emp] as e) as [t]");


    // correlating variable
    tester1.checkQuery(
        "select * from emp as [e] where exists (\n"
        + "select 1 from dept where dept.deptno = [E].deptno)");
    tester2.checkQueryFails(
        "select * from emp as [e] where exists (\n"
        + "select 1 from dept where dept.deptno = ^[E]^.deptno)",
        "(?s).*Table 'E' not found");


    checkFails("select count(1), ^empno^ from emp",
        "Expression 'EMPNO' is not being grouped");


    // 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'
    tester2.checkQuery("select count(*) from dept as [D], dept as [d]");
    if (!Bug.upgrade("fix case sensitivity bug")) {
      return;
    }
    tester1.checkQueryFails("select count(*) from dept as [D], dept as [d]",
        "xxx");

View Full Code Here

        "xxx");
  }


  /** Tests matching of built-in operator names. */
  @Test public void testUnquotedBuiltInFunctionNames() {
    final SqlTester mysql = tester
        .withUnquotedCasing(Casing.UNCHANGED)
        .withQuoting(Quoting.BACK_TICK)
        .withCaseSensitive(false);
    final SqlTester oracle = tester
        .withUnquotedCasing(Casing.TO_UPPER)
        .withCaseSensitive(true);


    // Built-in functions are always case-insensitive.
    oracle.checkQuery("select count(*), sum(deptno), floor(2.5) from dept");
    oracle.checkQuery("select COUNT(*), FLOOR(2.5) from dept");
    oracle.checkQuery("select cOuNt(*), FlOOr(2.5) from dept");
    oracle.checkQuery("select cOuNt (*), FlOOr (2.5) from dept");
    oracle.checkQuery("select current_time from dept");
    oracle.checkQuery("select Current_Time from dept");
    oracle.checkQuery("select CURRENT_TIME from dept");


    mysql.checkQuery("select sum(deptno), floor(2.5) from dept");
    mysql.checkQuery("select count(*), sum(deptno), floor(2.5) from dept");
    mysql.checkQuery("select COUNT(*), FLOOR(2.5) from dept");
    mysql.checkQuery("select cOuNt(*), FlOOr(2.5) from dept");
    mysql.checkQuery("select cOuNt (*), FlOOr (2.5) from dept");
    mysql.checkQuery("select current_time from dept");
    mysql.checkQuery("select Current_Time from dept");
    mysql.checkQuery("select CURRENT_TIME from dept");


    // MySQL assumes that a quoted function name is not a built-in.
    //
    // mysql> select `sum`(`day`) from days;
    // ERROR 1630 (42000): FUNCTION foodmart.sum does not exist. Check the
    //   'Function Name Parsing and Resolution' section in the Reference Manual
    // mysql> select `SUM`(`day`) from days;
    // ERROR 1630 (42000): FUNCTION foodmart.SUM does not exist. Check the
    //   'Function Name Parsing and Resolution' section in the Reference Manual
    // mysql> select SUM(`day`) from days;
    // +------------+
    // | SUM(`day`) |
    // +------------+
    // |         28 |
    // +------------+
    // 1 row in set (0.00 sec)
    //
    // We do not follow MySQL in this regard. `count` is preserved in
    // lower-case, and is matched case-insensitively because it is a built-in.
    // So, the query succeeds.
    oracle.checkQuery("select \"count\"(*) from dept");
    mysql.checkQuery("select `count`(*) from dept");
  }

View Full Code Here

  }


  /** 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");
  }

View Full Code Here


  /** Tests referencing columns from a sub-query that has duplicate column
   * names. (The standard says it should be an error, but we don't right
   * now.) */
  @Test public void testCaseInsensitiveSubQuery() {
    final SqlTester insensitive = tester
        .withCaseSensitive(false)
        .withQuoting(Quoting.BRACKET);
    final SqlTester sensitive = tester
        .withCaseSensitive(true)
        .withQuoting(Quoting.BRACKET);
    String sql = "select [e] from (\n"
        + "select empno as [e], deptno as d, 1 as [e] from EMP)";
    sensitive.checkQuery(sql);
    insensitive.checkQuery(sql);
    String sql1 = "select e from (\n"
        + "select empno as [e], deptno as d, 1 as [E] from EMP)";
    insensitive.checkQuery(sql1);
    sensitive.checkQuery(sql1);
  }

View Full Code Here

        + " null,"
        + " null}");
  }


  @Test public void testBrackets() {
    final SqlTester tester1 = tester.withQuoting(Quoting.BRACKET);
    tester1.checkResultType(
        "select [e].EMPNO from [EMP] as [e]",
        "RecordType(INTEGER NOT NULL EMPNO) NOT NULL");


    tester1.checkQueryFails(
        "select ^e^.EMPNO from [EMP] as [e]",
        "Table 'E' not found");


    tester1.checkQueryFails(
        "select ^x^ from (\n"
        + "  select [e].EMPNO as [x] from [EMP] as [e])",
        "Column 'X' not found in any table");


    tester1.checkQueryFails(
        "select EMP.^\"x\"^ from EMP",
        "(?s).*Encountered \"\\. \\\\\"\" at line .*");


    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");
  }

View Full Code Here

0 1

TOP

Related Classes of org.eigenbase.sql.test.SqlTester$ResultChecker

org.eigenbase.test.SqlValidatorTest

java.io.FileInputStream

java.beans.XMLDecoder

All source code are property of their respective owners. Java is a trademark of Sun Microsystems, Inc and owned by ORACLE Inc. Contact coftware#gmail.com.