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

["Julian Hyde (JIRA)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/CALCITE-1665?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Julian Hyde reassigned CALCITE-1665:
------------------------------------

    Assignee: Zhiqiang He  (was: Julian Hyde)

[~ransom], Assigning to you. Thanks for taking this on.

> 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: Zhiqiang He
>
> 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):
> {code}
> 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
> {code}
> 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"):
> {code}
> 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);
> {code}
> Java:
> {code}
> 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");
>     }
> }
> {code}



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