Problem of using Calcite with PostgreSQL

[Ken <na...@gmail.com>]

Hi all,

I'm now using Calcite with PostgreSQL but cannot make it run. Here are my
settings:
- The version of Calcite I'm using is 1.3.0-incubating
- Running PostgreSQL 9.4.1 on Mac Yosemite
- What I'm trying to do is very easy: connect postgresql with jdbc:
calcite, and execute a query. But it seems that Calcite cannot find the
table in database. Code is here:

// creates a calcite driver so queries go through it
Class.forName("org.postgresql.Driver");
Connection connection =
        DriverManager.getConnection("jdbc:calcite:");
CalciteConnection calciteConnection =
        connection.unwrap(CalciteConnection.class);

// creating postgresql connection
Class.forName("org.postgresql.Driver");
BasicDataSource dataSource = new BasicDataSource();
dataSource.setUrl("jdbc:postgresql://127.0.0.1/demo");

dataSource.setUsername("tester");
dataSource.setPassword("tester");
dataSource.setDefaultCatalog("demo");
JdbcSchema jdbcSchema =
JdbcSchema.create(calciteConnection.getRootSchema(), "demo",
dataSource, null, "demo");

// adding schema to connection
SchemaPlus rootSchema = calciteConnection.getRootSchema();
rootSchema.add("demo", jdbcSchema);

// creating statement and executing
Statement statement = connection.createStatement();
ResultSet resultSet =
        statement.executeQuery("select *\n"
                + "from \"demo\".\"Tenants\"");
final StringBuilder buf = new StringBuilder();
while (resultSet.next()) {
    int n = resultSet.getMetaData().getColumnCount();
    for (int i = 1; i <= n; i++) {
        buf.append(i > 1 ? "; " : "")
                .append(resultSet.getMetaData().getColumnLabel(i))
                .append("=")
                .append(resultSet.getObject(i));
    }
    System.out.println(buf.toString());
    buf.setLength(0);
}
resultSet.close();
statement.close();
connection.close();


Error log is here:
SEVERE: org.apache.calcite.sql.validate.SqlValidatorException: Table
'demo.Tenants' not found
Jul 02, 2015 2:01:47 PM org.apache.calcite.runtime.CalciteException <init>
SEVERE: org.apache.calcite.runtime.CalciteContextException: From line 2,
column 6 to line 2, column 21: Table 'demo.Tenants' not found
Exception in thread "main" java.sql.SQLException: error while executing SQL
"select *
from "demo"."Tenants"": From line 2, column 6 to line 2, column 21: Table
'demo.Tenants' not found
at org.apache.calcite.avatica.Helper.createException(Helper.java:41)
at
org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:112)
at
org.apache.calcite.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:130)
at
org.ethz.systemsgroup.calcite.CalcitePostgreSQL.main(CalcitePostgreSQL.java:39)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:483)
at com.intellij.rt.execution.application.AppMain.main(AppMain.java:134)
Caused by: org.apache.calcite.runtime.CalciteContextException: From line 2,
column 6 to line 2, column 21: Table 'demo.Tenants' 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:408)
at
org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:405)
at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:685)
at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:673)
at
org.apache.calcite.sql.validate.SqlValidatorImpl.newValidationError(SqlValidatorImpl.java:3859)
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:843)
at
org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:829)
at
org.apache.calcite.sql.validate.SqlValidatorImpl.validateFrom(SqlValidatorImpl.java:2743)
at
org.apache.calcite.sql.validate.SqlValidatorImpl.validateFrom(SqlValidatorImpl.java:2728)
at
org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect(SqlValidatorImpl.java:2946)
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:843)
at
org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:829)
at org.apache.calcite.sql.SqlSelect.validate(SqlSelect.java:207)
at
org.apache.calcite.sql.validate.SqlValidatorImpl.validateScopedExpression(SqlValidatorImpl.java:803)
at
org.apache.calcite.sql.validate.SqlValidatorImpl.validate(SqlValidatorImpl.java:517)
at
org.apache.calcite.sql2rel.SqlToRelConverter.convertQuery(SqlToRelConverter.java:532)
at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:221)
at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:188)
at
org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_(CalcitePrepareImpl.java:669)
at
org.apache.calcite.prepare.CalcitePrepareImpl.prepare_(CalcitePrepareImpl.java:570)
at
org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql(CalcitePrepareImpl.java:539)
at
org.apache.calcite.jdbc.CalciteConnectionImpl.parseQuery(CalciteConnectionImpl.java:173)
at
org.apache.calcite.jdbc.CalciteMetaImpl.prepareAndExecute(CalciteMetaImpl.java:504)
at
org.apache.calcite.avatica.AvaticaConnection.prepareAndExecuteInternal(AvaticaConnection.java:477)
at
org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:109)
... 7 more
Caused by: org.apache.calcite.sql.validate.SqlValidatorException: Table
'demo.Tenants' 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:408)
at
org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:405)
at org.apache.calcite.runtime.Resources$ExInst.ex(Resources.java:514)
... 34 more

Thanks for your time and help.
-Ken

Re: Problem of using Calcite with PostgreSQL

[Vladimir Sitnikov <si...@gmail.com>]

Ken,

Calcite works with PostgreSQL.
Not sure what is wrong with your setup, though.

I can suggest two ways:
1) You can try running Calcite's integration tests against PostgresSQL.
Here's relevant documentation:
http://calcite.incubator.apache.org/docs/howto.html#running-tests ,
http://calcite.incubator.apache.org/docs/howto.html#running-integration-tests

The connection itself is here:
https://github.com/apache/incubator-calcite/blob/master/core/src/test/java/org/apache/calcite/test/CalciteAssert.java#L644-L650

There is an automated script that creates virtual machine with
PostgreSQL, so you can inspire.

2) We can look into the error if you a sample github project that
shows the error you are running into.

Vladimir