calcite-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From 郑奇煌 <qihuang.zh...@fraudmetrix.cn>
Subject calcite having count(*)>1 execute result not expected
Date Sun, 12 Jul 2015 08:40:31 GMT
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.

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message