[CALCITE-1665] HAVING support in RelToSqlConverter

• Type: Bug
• Status: Closed
• Priority: Minor
• Resolution: Fixed
• Affects Version/s: 1.11.0
• Fix Version/s: 1.12.0
• Component/s: jdbc-adapter
• Assignee: Zhiqiang He
• Reporter: David Evans
• Created: 01/Mar/17 16:18
• Updated: 23/May/17 04:07
• Resolved: 10/Mar/17 14:23

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