org.apache.calcite.sql.validate.SqlValidatorException

Question

I'm using Apache Calcite to parse a simple SQL statement and return its relational tree. I obtain a database schema using a JDBC connection to a simple SQLite database. The schema is then added using FrameworkConfig. The parser configuration is then modified to handle identifier quoting and case (not sensitive). However the SQL validator is unable to find the quoted table identifier in the SQL statement. Somehow the parser ignore the configuration settings and converts the table to UPPER CASE. A SqlValidatorException is raised, stating the the table name is not found. I suspect, the configuration is not being updated correctly? I have already validated that the table name is correctly included in the schema's meta-data.

public class ParseSQL {

public static void main(String[] args) {    

    try {
        // register the JDBC driver 
        String sDriverName = "org.sqlite.JDBC";
        Class.forName(sDriverName);

        JsonObjectBuilder builder = Json.createObjectBuilder();

        builder.add("jdbcDriver", "org.sqlite.JDBC")
                        .add("jdbcUrl", 
                           "jdbc:sqlite://calcite/students.db")
                            .add("jdbcUser", "root")
                                .add("jdbcPassword", "root");

        Map<String, JsonValue> JsonObject = builder.build();

        //argument for JdbcSchema.Factory().create(....)
        Map<String, Object> operand = new HashMap<String, Object>();

        //explicitly extract JsonString(s) and load into operand map
        for(String key : JsonObject.keySet()) {
            JsonString value = (JsonString) JsonObject.get(key);
            operand.put(key, value.getString());
         }

        final SchemaPlus rootSchema = Frameworks.createRootSchema(true);
        Schema schema =  new JdbcSchema.Factory().create(rootSchema, "students",  operand);
        rootSchema.add("students", schema);

        //build a FrameworkConfig using defaults where values aren't required
        Frameworks.ConfigBuilder configBuilder  =  Frameworks.newConfigBuilder();

        //set defaultSchema
        configBuilder.defaultSchema(rootSchema);

        //build configuration
        FrameworkConfig frameworkdConfig = configBuilder.build();

        //use SQL parser config builder to ignore case of quoted identifier
        SqlParser.configBuilder(frameworkdConfig.getParserConfig()).setQuotedCasing(Casing.UNCHANGED).build();
        //use SQL parser config builder to set SQL case sensitive = false
        SqlParser.configBuilder(frameworkdConfig.getParserConfig()).setCaseSensitive(false).build();

        //get planner
        Planner planner =  Frameworks.getPlanner(frameworkdConfig);

        //parse SQL statement
        SqlNode sql_node = planner.parse("SELECT * FROM \"Students\" WHERE age > 15.0");
        System.out.println("\n" + sql_node.toString());

        //validate SQL
        SqlNode sql_validated = planner.validate(sql_node);

        //get associated relational expression
        RelRoot relationalExpression = planner.rel(sql_validated);
        relationalExpression.toString();

    } catch (SqlParseException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    } catch (RelConversionException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    } catch (ValidationException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    } catch (ClassNotFoundException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }

}  // end main

} // end class

***** ERROR MESSAGE ****** Jan 20, 2016 8:54:51 PM org.apache.calcite.sql.validate.SqlValidatorException SEVERE: org.apache.calcite.sql.validate.SqlValidatorException: Table 'Students' not found

score 1 · Answer 1 · edited May 23 '17 at 11:50

This is a case-sensitivity issue, similar to table not found with apache calcite. Because you enclosed the table name in quotes in your SQL statement, the validator is looking for a table called "Students", and the error message attests to this. If your table is called "Students", I am surprised that Calcite can't find it.

There is a problem with how you are using the SqlParser.ConfigBuilder. When you call build(), you are not using the SqlParser.Config object that it creates. If you passed that object to Frameworks.ConfigBuilder.parserConfig, I think you would get the behavior you want.

org.apache.calcite.sql.validate.SqlValidatorException

1 Answers1