Details

    • Type: Bug
    • Status: Closed
    • Priority: Minor
    • Resolution: Fixed
    • Affects Version/s: 1.11.0
    • Fix Version/s: 1.12.0
    • Component/s: jdbc-adapter
    • Labels:
      None

      Description

      The example on the main docs page: https://calcite.apache.org/docs/ will generate the following SQL when run against a JDBC Schema (specifically, with a PostgreSQL target database):

      SELECT "deptno", MIN("emps"."empid")
      FROM (SELECT "depts"."deptno", MIN("emps"."empid"), COUNT(*) AS "$f2"
      FROM "hr"."depts"
      INNER JOIN "hr"."emps" ON "depts"."deptno" = "emps"."deptno"
      GROUP BY "depts"."deptno") AS "t"
      WHERE "t"."$f2" > 1
      

      This fails since the "emps" table only exists inside the inner select. It should be aliasing that result in the inner select and using the outer select as a simple pass-through. This appears to be a general issue when combining aggregates with `HAVING`

      For an MCVE:

      In postgres: (create a database named "test1"):

      CREATE SCHEMA hr;
      CREATE TABLE hr.depts (deptno SERIAL NOT NULL PRIMARY KEY); CREATE TABLE hr.emps (empid SERIAL NOT NULL PRIMARY KEY, deptno INT NOT NULL REFERENCES hr.depts (deptno));
      INSERT INTO hr.depts VALUES (1), (2);
      INSERT INTO hr.emps (deptno) VALUES (1), (1), (2);
      

      Java:

      import org.apache.calcite.adapter.jdbc.JdbcSchema;
      import org.apache.calcite.jdbc.CalciteConnection;
      import org.apache.calcite.schema.Schema;
      import org.apache.calcite.schema.SchemaPlus;
      import org.apache.commons.dbcp.BasicDataSource;
      
      import java.sql.Connection;
      import java.sql.DriverManager;
      import java.sql.ResultSet;
      import java.sql.Statement;
      import java.util.Properties;
      
      public class Main {
          public static class Employee {
              public int EMPID;
              public int DEPTNO;
          }
      
          public static class Department {
              public int DEPTNO;
          }
      
          public static class HrSchema {
              public final Employee[] EMPS = null;
              public final Department[] DEPTS = null;
          }
      
          public static void main(String[] argv) throws Exception {
              System.out.println("Setup");
      
              Class.forName("org.apache.calcite.jdbc.Driver");
              Class.forName("org.postgresql.Driver");
      
              BasicDataSource dataSource = new BasicDataSource();
              dataSource.setUrl("jdbc:postgresql://localhost:5432/test1");
              dataSource.setUsername("myUsername"); // change as required
              dataSource.setPassword("");
              Connection pgConnection = dataSource.getConnection();
      
              Statement statement = pgConnection.createStatement();
              ResultSet results = statement.executeQuery("select d.deptno, min(e.empid)\n"
                              + "from hr.emps as e\n"
                              + "join hr.depts as d\n"
                              + "  on e.deptno = d.deptno\n"
                              + "group by d.deptno\n"
                              + "having count(*) > 1");
              System.out.println("Direct to postgres results:");
              while(results.next()) {
                  System.out.println(results.getInt(1) + " : " + results.getInt(2));
              }
              System.out.println("Done");
              results.close();
              statement.close();
              pgConnection.close();
              System.out.println("Closed");
      
      
              Properties info = new Properties();
              info.setProperty("lex", "JAVA");
              Connection calConnection = DriverManager.getConnection("jdbc:calcite:", info);
              CalciteConnection calciteConnection = calConnection.unwrap(CalciteConnection.class);
              SchemaPlus rootSchema = calciteConnection.getRootSchema();
      
              Schema schema = JdbcSchema.create(rootSchema, "hr", dataSource, null, "hr");
      
              rootSchema.add("hr", schema);
              statement = calciteConnection.createStatement();
             results = statement.executeQuery(
                      "select d.deptno, min(e.empid)\n"
                              + "from hr.emps as e\n"
                              + "join hr.depts as d\n"
                              + "  on e.deptno = d.deptno\n"
                              + "group by d.deptno\n"
                              + "having count(*) > 1");
              System.out.println("Via calcite results:");
              while(results.next()) {
                  System.out.println(results.getInt(1) + " : " + results.getInt(2));
              }
              System.out.println("Done");
              results.close();
              statement.close();
              calConnection.close();
              System.out.println("Closed");
          }
      }
      

        Activity

        Hide
        julianhyde Julian Hyde added a comment -

        Resolved in release 1.12.0 (2017-03-24).

        Show
        julianhyde Julian Hyde added a comment - Resolved in release 1.12.0 (2017-03-24).
        Show
        jbalint@gmail.com Jess Balint added a comment - Fixed in http://git-wip-us.apache.org/repos/asf/calcite/commit/60777142e0b7be9302e22c1dd934020c6e64be69 . Thanks for the PR, Zhiqiang He!
        Hide
        julianhyde Julian Hyde added a comment -

        Jess Balint, Can please you review and commit? (I make a remark on the PR about formatting changes but I haven't done a proper review.)

        Show
        julianhyde Julian Hyde added a comment - Jess Balint , Can please you review and commit? (I make a remark on the PR about formatting changes but I haven't done a proper review.)
        Hide
        ransom Zhiqiang He added a comment -

        indentation already change in test case by code review comment .
        please review it. thanks.

        Show
        ransom Zhiqiang He added a comment - indentation already change in test case by code review comment . please review it. thanks.
        Hide
        jbalint@gmail.com Jess Balint added a comment -

        Thanks Zhiqiang He. I will review it.

        Show
        jbalint@gmail.com Jess Balint added a comment - Thanks Zhiqiang He. I will review it.
        Hide
        ransom Zhiqiang He added a comment -
        Show
        ransom Zhiqiang He added a comment - https://github.com/apache/calcite/pull/388 please review it. thanks.
        Hide
        julianhyde Julian Hyde added a comment -

        Zhiqiang He, Assigning to you. Thanks for taking this on.

        Show
        julianhyde Julian Hyde added a comment - Zhiqiang He , Assigning to you. Thanks for taking this on.
        Hide
        ransom Zhiqiang He added a comment -

        I'm already repeat it in reltosqlconverttest.
        it was because reltosqlconvert can not convert having clause, so it was pushed up to outer query.
        I think I can fix it .
        Julian Hyde] Jess Balint can you assign it to me.

        Show
        ransom Zhiqiang He added a comment - I'm already repeat it in reltosqlconverttest. it was because reltosqlconvert can not convert having clause, so it was pushed up to outer query. I think I can fix it . Julian Hyde ] Jess Balint can you assign it to me.
        Hide
        jbalint@gmail.com Jess Balint added a comment -

        The plan tree is probably a Filter with an Aggregate child. The RelToSqlConverter doesn't (often) look at the child and generally only deals with the currently visited node. It will generate the aggregate and when the filter is seen, we will need special logic. This code is quickly accumulating these special cases.

        Show
        jbalint@gmail.com Jess Balint added a comment - The plan tree is probably a Filter with an Aggregate child. The RelToSqlConverter doesn't (often) look at the child and generally only deals with the currently visited node. It will generate the aggregate and when the filter is seen, we will need special logic. This code is quickly accumulating these special cases.
        Hide
        julianhyde Julian Hyde added a comment -

        I have a hunch that this is a rel2sql problem. Jess Balint, Christian Tzolov, Zhiqiang He, Could one of you guys look into this? You've each worked on rel2sql recently.

        Show
        julianhyde Julian Hyde added a comment - I have a hunch that this is a rel2sql problem. Jess Balint , Christian Tzolov , Zhiqiang He , Could one of you guys look into this? You've each worked on rel2sql recently.

          People

          • Assignee:
            ransom Zhiqiang He
            Reporter:
            devans_pivotal David Evans
          • Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development