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

HAVING support in RelToSqlConverter

    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");
          }
      }
      

        Attachments

          Activity

            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: