Uploaded image for project: 'Calcite'
  1. Calcite
  2. CALCITE-1996

Executing RelBuilder.values with a projection sometimes fails

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Minor
    • Resolution: Fixed
    • Affects Version/s: 1.14.0
    • Fix Version/s: 1.15.0
    • Component/s: core
    • Labels:
      None
    • Environment:

      MacOS 10.12.6

      Description

      When executing a query created with RelBuilder.values() that includes a projection, it will fail if it has been executed after another query which accesses an underlying data store (in this case, the SCOTT HSQL database).

      Interesting, if you first perform the values query, then access the data store, and then perform the values query again, it works fine. It seems like in this case, the query is executed directly in-memory.

      The test case pasted below fails with the exception:

      JUnit Test Output
       org.apache.calcite.test.RelBuilderBugTest
      testRelBuilderProjectBug(org.apache.calcite.test.RelBuilderBugTest)  Time elapsed: 0.186 sec  <<< ERROR!
      java.sql.SQLException: 
      exception while executing query: while executing SQL [SELECT "a"
      FROM (VALUES  ('X', 1),
       ('Y', 2))]
      	at org.apache.calcite.test.RelBuilderBugTest.testRelBuilderProjectBug(RelBuilderBugTest.java:65)
      Caused by: java.lang.RuntimeException: 
      while executing SQL [SELECT "a"
      FROM (VALUES  ('X', 1),
       ('Y', 2))]
      	at org.apache.calcite.test.RelBuilderBugTest.testRelBuilderProjectBug(RelBuilderBugTest.java:65)
      Caused by: java.sql.SQLSyntaxErrorException: user lacks privilege or object not found: a
      	at org.apache.calcite.test.RelBuilderBugTest.testRelBuilderProjectBug(RelBuilderBugTest.java:65)
      Caused by: org.hsqldb.HsqlException: user lacks privilege or object not found: a
      	at org.apache.calcite.test.RelBuilderBugTest.testRelBuilderProjectBug(RelBuilderBugTest.java:65)
      

      However, it only fails when run individually with:

      mvn test -pl core -Dtest=RelBuilderBugTest

      When run as part of the entire test suite, it passes, making one suspect that there might be some global state issue at play.

      RelBuilderBugTest.java
      package org.apache.calcite.test;
      
      import org.apache.calcite.adapter.jdbc.JdbcSchema;
      import org.apache.calcite.jdbc.CalciteConnection;
      import org.apache.calcite.rel.RelNode;
      import org.apache.calcite.tools.FrameworkConfig;
      import org.apache.calcite.tools.Frameworks;
      import org.apache.calcite.tools.RelBuilder;
      import org.apache.calcite.tools.RelRunner;
      
      import org.junit.Test;
      
      import java.sql.Connection;
      import java.sql.DriverManager;
      import java.util.Properties;
      import javax.sql.DataSource;
      
      /** Test case for a RelBuilder bug */
      public class RelBuilderBugTest {
      
        /** Tests a bug view. */
        @Test public void testRelBuilderProjectBug() throws Exception {
          Class.forName("org.apache.calcite.jdbc.Driver");
          Connection connection = DriverManager
              .getConnection("jdbc:calcite:", new Properties());
          CalciteConnection conn = connection.unwrap(CalciteConnection.class);
      
          DataSource source = JdbcSchema.dataSource("jdbc:hsqldb:res:scott",
              "org.hsqldb.jdbcDriver", "SCOTT", "TIGER");
          JdbcSchema schema = JdbcSchema.create(conn.getRootSchema(),
              "SCOTT", source, null, null);
          conn.getRootSchema().add("SCOTT", schema);
      
          FrameworkConfig config = Frameworks.newConfigBuilder()
              .defaultSchema(conn.getRootSchema())
              .build();
          RelBuilder builder = RelBuilder.create(config);
          RelRunner runner = conn.unwrap(RelRunner.class);
      
          RelNode values = builder.values(new String[]{"a", "b"}, "X", 1, "Y", 2)
              .project(builder.field("a")).build();
      
          // run this *before* the scott query and it works fine
          // runner.prepare(values).executeQuery();
      
          runner.prepare(builder.scan("SCOTT", "EMP").build()).executeQuery();
          builder.clear();
      
          // running this after the scott query causes the exception
          runner.prepare(values).executeQuery();
        }
      }
      

        Issue Links

          Activity

          Hide
          julianhyde Julian Hyde added a comment -

          Resolved in release 1.15.0 (2017-12-11).

          Show
          julianhyde Julian Hyde added a comment - Resolved in release 1.15.0 (2017-12-11).
          Hide
          julianhyde Julian Hyde added a comment -

          Fixed generation of VALUES in http://git-wip-us.apache.org/repos/asf/calcite/commit/fb760a6f, and included a similar test case. The stateful behavior of the planner is not fixed, but it can be addressed in other issues.

          Show
          julianhyde Julian Hyde added a comment - Fixed generation of VALUES in http://git-wip-us.apache.org/repos/asf/calcite/commit/fb760a6f , and included a similar test case. The stateful behavior of the planner is not fixed, but it can be addressed in other issues.
          Hide
          julianhyde Julian Hyde added a comment - - edited

          I see what's going on.

          RelBuilder is doing the right thing as far as row types and field names are concerned. So, it is producing the same RelNode tree each time, and that tree is correct.

          However, RelBuilder contains a RelOptCluster, and that contains state that affects the planning process.

          Before you execute the query against Hsqldb, the planner rules convert LogicalValues to EnumerableValues, and it gets executed by generating Java code. After you have executed a query against Hsqldb, the planner contains a JdbcConvention and a set of JDBC planner rules including JdbcValuesRule, so LogicalValues gets converted to JdbcValues.

          Problem 1 is that we don't convert

          LogicalProject(a=[$0])
            LogicalValues(tuples=[[{ 'X', 1 }, { 'Y', 2 }]], rowType="a: CHAR(1), b: INTEGER")
          

          to valid SQL. We generate

          SELECT "a"
          FROM (VALUES  ('X', 1),
           ('Y', 2))

          whereas we should generate

          SELECT "a"
          FROM (VALUES  ('X', 1),
           ('Y', 2)) AS t("a", "b")

          Problem 2 is the planner state. All queries use the same RelOptCluster instance (because a RelBuilder has a RelOptCluster), and so when a JdbcConvention instance is registered for the "select * from emp" query, it is retained for the "values ..." query. This is what CALCITE-1536 is trying to solve: reducing the amount of state that goes into RelOptCluster.

          Show
          julianhyde Julian Hyde added a comment - - edited I see what's going on. RelBuilder is doing the right thing as far as row types and field names are concerned. So, it is producing the same RelNode tree each time, and that tree is correct. However, RelBuilder contains a RelOptCluster, and that contains state that affects the planning process. Before you execute the query against Hsqldb, the planner rules convert LogicalValues to EnumerableValues, and it gets executed by generating Java code. After you have executed a query against Hsqldb, the planner contains a JdbcConvention and a set of JDBC planner rules including JdbcValuesRule, so LogicalValues gets converted to JdbcValues. Problem 1 is that we don't convert LogicalProject(a=[$0]) LogicalValues(tuples=[[{ 'X', 1 }, { 'Y', 2 }]], rowType="a: CHAR(1), b: INTEGER") to valid SQL. We generate SELECT "a" FROM (VALUES ('X', 1), ('Y', 2)) whereas we should generate SELECT "a" FROM (VALUES ('X', 1), ('Y', 2)) AS t( "a" , "b" ) Problem 2 is the planner state. All queries use the same RelOptCluster instance (because a RelBuilder has a RelOptCluster), and so when a JdbcConvention instance is registered for the "select * from emp" query, it is retained for the "values ..." query. This is what CALCITE-1536 is trying to solve: reducing the amount of state that goes into RelOptCluster.
          Hide
          julianhyde Julian Hyde added a comment -

          Thanks, I can reproduce the problem. Totally weird, though.

          Show
          julianhyde Julian Hyde added a comment - Thanks, I can reproduce the problem. Totally weird, though.

            People

            • Assignee:
              julianhyde Julian Hyde
              Reporter:
              mprudhom Marc Prud'hommeaux
            • Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development