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
