You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@calcite.apache.org by "David Evans (JIRA)" <ji...@apache.org> on 2017/03/01 16:20:45 UTC
[jira] [Updated] (CALCITE-1665) Aggregates and HAVING cannot be
combined
[ https://issues.apache.org/jira/browse/CALCITE-1665?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
David Evans updated CALCITE-1665:
---------------------------------
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):
{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}
was:
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");
}
}
```
> 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)