[ https://issues.apache.org/jira/browse/CALCITE-1665?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15900487#comment-15900487 ]
Julian Hyde commented on CALCITE-1665: -------------------------------------- [~jbal...@gmail.com], Can please you review and commit? (I make a remark on the PR about formatting changes but I haven't done a proper review.) > 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)