Can you try
ResultSet rs = stmt3.executeQuery("select * from db1.\”table1\"”);
I don’t recall PostgreSQL’s case-sensitivity policy, but if table1 is
lower-case in PostgreSQL’s catalog then it will be lower-case in Calcite’s too.
Calcite’s default case-sensitity policy, like Oracle’s, is to convert unquoted
identifiers to upper-case, so in your original query
ResultSet rs = stmt3.executeQuery("select * from db1.table1”);
it will be looking for TABLE1 in schema DB1.
Julian
PS I noticed that you asked the same question on SO.
http://stackoverflow.com/questions/37683607/calcite-table-not-found-when-using-postgres
<http://stackoverflow.com/questions/37683607/calcite-table-not-found-when-using-postgres>
I’m not going to carry on the same conversation in two threads, but let’s be
sure to update SO when we find an answer.
> On Jun 8, 2016, at 8:45 AM, Dan Payne <[email protected]> wrote:
>
> I'm new to Calcite and trying to query across multiple databases in
> Postgres. But first I'm just trying to get a simple use case working where
> I query a single Postgres DB via Calcite and JDBC. However, any 'select'
> query executed against Postgres always throws an exception with 'table x
> not found'. I modeled my code after this Calcite test class that queries
> across two datasources in Hsqldb:
>
> https://github.com/apache/calcite/blob/master/core/src/test/java/org/apache/calcite/test/MultiJdbcSchemaJoinTest.java
>
>
> When I run that test everything works fine. However, when I change it to
> use Postgres instead, I get the 'table not found' error.
>
> I'm using Mac OS X 10.11.5, Calcite 1.7.0, Postgres 9.5.2.0 and Java
> 1.8.0_77.
>
> I boiled the test case and my code down to just the minimum which
> demonstrates the issue. The class is below and pom file follows and then
> finally the error itself. Any help is much appreciated.
>
> 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));
> }
> }
> }
>
>
> <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>
>
> 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
