[
https://issues.apache.org/jira/browse/CALCITE-1665?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15891102#comment-15891102
]
Jess Balint commented on CALCITE-1665:
--------------------------------------
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.
> 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):
> {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)
