Hi calciter,
I run first example as describe in https://calcite.incubator.apache.org/docs/.
and this is some code
SchemaPlus rootSchema = calciteConnection.getRootSchema();
rootSchema.add("hr", new ReflectiveSchema(new HrSchema()));
Statement statement = calciteConnection.createStatement();
ResultSet resultSet = statement.executeQuery(
"select d.deptno, min(e.empid) empid "
+ "from hr.emps as e "
+ "join hr.depts as d "
+ "on e.deptno = d.deptno "
+ "group by d.deptno "
+ "having count(*) > 1");
while(resultSet.next()){
int deptno = resultSet.getInt("deptno");
int minEmp = resultSet.getInt("empid");
System.out.println(deptno + "->" + minEmp);
}
and this is my fake data
public static class HrSchema {
public final Employee[] emps = {
new Employee(100, "Bill",1),
new Employee(200, "Eric",1),
new Employee(150, "Sebastian",3),
};
public final Department[] depts = {
new Department(1, "LeaderShip"),
new Department(2, "TestGroup"),
new Department(3, "Development")
};
}
as the data above, deptno=1 has two employees(100 Bill,and 200 Eric), deptno=3
has only one emploee.
so the sql above execution should return (1,100).
because only deptno=1 having count(*)>1, and min(empid)=100
But I run in my idea, the result is :
3->150
Is't strange?. As I know, sql statement:group by deptno having count(*)>1
means after group by deptno, the count(*) number of this group should >1.