calcite-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Julian Hyde <jh...@apache.org>
Subject Re: calcite having count(*)>1 execute result not expected
Date Mon, 13 Jul 2015 04:04:22 GMT
Yes, that's a bug. In the generated code there is

/*  60 */               if
(((org.apache.calcite.test.JdbcTest.Department)
inputEnumerator.current()).deptno > 1) {

which suggests an off-by-one error during sql-to-rel translation (it
should be referencing the count, not deptno). Maybe due to there being
two aggregate functions (one projected, the other in the having
clause).

Please log a jira case.

Julian


On Sun, Jul 12, 2015 at 1:40 AM, 郑奇煌 <qihuang.zheng@fraudmetrix.cn> wrote:
> 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
View raw message