java - 方解石 : table not found when using Postgres

Question

我正在尝试使用 Apache Calcite 查询 Postgres 数据库。我可以通过 Calcite JDBC 驱动程序细读有关数据库的元数据，因此我绝对能够连接到它，但是每当我查询一个表时，Calcite 总是以“找不到表 x”作为响应。如果我更改我的代码以改用 Hsqldb，一切正常。下面是代码(改编自这个 Calcite 测试用例:https://github.com/apache/calcite/blob/master/core/src/test/java/org/apache/calcite/test/MultiJdbcSchemaJoinTest.java)

package org.apache.calcite;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.sql.DataSource;

import org.apache.calcite.adapter.jdbc.JdbcSchema;
import org.apache.calcite.jdbc.CalciteConnection;
import org.apache.calcite.schema.SchemaPlus;

/**
 * This class demonstrates Calcite unable to recognize tables in Postgres on 
 * Mac OS X 10.11.5 with Calcite 1.7.0, Postgres 9.5.2.0 and Java 1.8.0_77.
 * 
 * Before you run this class, you must create the user and database in 
 * Postgres by executing the following SQL:
 * 
 *    create user johnsnow with password 'password';
 *    create database db1 with owner johnsnow;
 * 
 */
public class TableNotFoundMain {
    public static void main(String... args) throws SQLException, ClassNotFoundException {
        final String dbUrl = "jdbc:postgresql://localhost/db1";

        Connection con = DriverManager.getConnection(dbUrl, "johnsnow", "password");
        Statement stmt1 = con.createStatement();
        stmt1.execute("drop table if exists table1");
        stmt1.execute("create table table1(id varchar not null primary key, field1 varchar)");
        stmt1.execute("insert into table1 values('a', 'aaaa')");
        con.close();

        Connection connection = DriverManager.getConnection("jdbc:calcite:");
        CalciteConnection calciteConnection = connection.unwrap(CalciteConnection.class);
        SchemaPlus rootSchema = calciteConnection.getRootSchema();
        final DataSource ds = JdbcSchema.dataSource(dbUrl, "org.postgresql.Driver", "johnsnow", "password");
        rootSchema.add("DB1", JdbcSchema.create(rootSchema, "DB1", ds, null, null));

        Statement stmt3 = connection.createStatement();
        ResultSet rs = stmt3.executeQuery("select * from db1.table1");

        while (rs.next()) {
            System.out.println(rs.getString(1) + '=' + rs.getString(2));
        }
    }
}

下面是我得到的错误:

Exception in thread "main" java.sql.SQLException: Error while executing SQL "select * from db1.table1": From line 1, column 15 to line 1, column 24: Table 'DB1.TABLE1' not found
    at org.apache.calcite.avatica.Helper.createException(Helper.java:56)
    at org.apache.calcite.avatica.Helper.createException(Helper.java:41)
    at org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:143)
    at org.apache.calcite.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:186)
    at org.apache.calcite.TableNotFoundMain.main(TableNotFoundMain.java:45)
Caused by: org.apache.calcite.runtime.CalciteContextException: From line 1, column 15 to line 1, column 24: Table 'DB1.TABLE1' not found
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
    at org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:405)
    at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:768)
    at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:753)
    at org.apache.calcite.sql.validate.SqlValidatorImpl.newValidationError(SqlValidatorImpl.java:3929)
    at org.apache.calcite.sql.validate.IdentifierNamespace.validateImpl(IdentifierNamespace.java:106)
    at org.apache.calcite.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:86)
    at org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:845)
    at org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:831)
    at org.apache.calcite.sql.validate.SqlValidatorImpl.validateFrom(SqlValidatorImpl.java:2754)
    at org.apache.calcite.sql.validate.SqlValidatorImpl.validateFrom(SqlValidatorImpl.java:2739)
    at org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect(SqlValidatorImpl.java:2957)
    at org.apache.calcite.sql.validate.SelectNamespace.validateImpl(SelectNamespace.java:60)
    at org.apache.calcite.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:86)
    at org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:845)
    at org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:831)
    at org.apache.calcite.sql.SqlSelect.validate(SqlSelect.java:208)
    at org.apache.calcite.sql.validate.SqlValidatorImpl.validateScopedExpression(SqlValidatorImpl.java:807)
    at org.apache.calcite.sql.validate.SqlValidatorImpl.validate(SqlValidatorImpl.java:523)
    at org.apache.calcite.sql2rel.SqlToRelConverter.convertQuery(SqlToRelConverter.java:577)
    at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:224)
    at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:193)
    at org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_(CalcitePrepareImpl.java:720)
    at org.apache.calcite.prepare.CalcitePrepareImpl.prepare_(CalcitePrepareImpl.java:587)
    at org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql(CalcitePrepareImpl.java:557)
    at org.apache.calcite.jdbc.CalciteConnectionImpl.parseQuery(CalciteConnectionImpl.java:214)
    at org.apache.calcite.jdbc.CalciteMetaImpl.prepareAndExecute(CalciteMetaImpl.java:573)
    at org.apache.calcite.avatica.AvaticaConnection.prepareAndExecuteInternal(AvaticaConnection.java:581)
    at org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:135)
    ... 2 more
Caused by: org.apache.calcite.sql.validate.SqlValidatorException: Table 'DB1.TABLE1' not found
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
    at org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:405)
    at org.apache.calcite.runtime.Resources$ExInst.ex(Resources.java:514)
    ... 29 more

我看到其他帖子 ( table not found with apache calcite ) 表明这些类型的错误通常是由大小写问题引起的，所以我尝试了我能想到的所有大小写组合:

ResultSet rs = stmt3.executeQuery("select * from DB1.TABLE1");

或

ResultSet rs = stmt3.executeQuery("select * from \"DB1.table1\"");

或

ResultSet rs = stmt3.executeQuery("select * from \"db1.table1\"");

但无济于事。

这是我的 pom 文件，因此您可以运行代码:

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <groupId>org.apache</groupId>
    <artifactId>calcite-table-not-found</artifactId>
    <version>0.0.1-SNAPSHOT</version>

    <dependencies>
        <dependency>
            <groupId>org.apache.calcite</groupId>
            <artifactId>calcite-core</artifactId>
            <version>1.7.0</version>
        </dependency>
        <dependency>
            <groupId>org.postgresql</groupId>
            <artifactId>postgresql</artifactId>
            <version>9.4.1208.jre7</version>
        </dependency>
    </dependencies>
</project>

这里的最终用例是能够跨多个数据库进行查询。

Answer 1

确实是大小写问题，正确的语法如下:

ResultSet rs = stmt3.executeQuery("select * from db1.\"table1\"");

感谢 Calcite 项目团队成员 Julian Hyde 在 Calcite 开发邮件列表中提供了答案。