[jira] [Created] (CALCITE-1665) Aggregates and HAVING cannot be combined

["David Evans (JIRA)" <ji...@apache.org>]

David Evans created CALCITE-1665:
------------------------------------

             Summary: Aggregates and HAVING cannot be combined
                 Key: CALCITE-1665
                 URL: https://issues.apache.org/jira/browse/CALCITE-1665
             Project: Calcite
          Issue Type: Bug
          Components: jdbc-adapter
    Affects Versions: 1.11.0
            Reporter: David Evans
            Assignee: Julian Hyde


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



--
This message was sent by Atlassian JIRA
(v6.3.15#6346)