You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by yiifeger wu <va...@gmail.com> on 2018/01/23 06:15:30 UTC
Query Avg on integer column returns wrong result
Hi,
The AVG Function of an integer column will return a double or decimal
result in general databases, like MySQL or HIVE. But Calcite will return
an integer result which I test in org.apache.calcite.test.CsvTest
the SQL:
sql("model", "select avg(DEPTNO) from EMPS").ok();
the Result:
21
the content of table EMPS
EMPNO:long,NAME:string,DEPTNO:int,GENDER:string,CITY:string,EMPID:int,AGE:int,SLACKER:boolean,MANAGER:boolean,JOINEDAT:date
100,"Fred",10,,,30,25,true,false,"1996-08-03"
110,"Eric",20,"M","San Francisco",3,80,,false,"2001-01-01"
110,"John",40,"M","Vancouver",2,,false,true,"2002-05-03"
120,"Wilma",20,"F",,1,5,,true,"2005-09-07"
130,"Alice",40,"F","Vancouver",2,,false,true,"2007-01-01"
140,"Alice",0,"F","Vancouver",2,,false,true,"2007-01-01"
Secondly, I try the sql:
select avg(cast (DEPTNO as double)) from EMPS
it can get the right result 21.666...
But it also looks like weired, the return type of AVG(Integer) is integer.
Re: Query Avg on integer column returns wrong result
Posted by yiifeger wu <va...@gmail.com>.
Thanks!
2018-01-23 14:25 GMT+08:00 Julian Hyde <jh...@apache.org>:
> This has been discussed before. See [1] and [2].
>
> Julian
>
> [1] https://mail-archives.apache.org/mod_mbox/calcite-dev/
> 201710.mbox/%3C3A407BE1-EACD-4B21-8BB2-B00632137C44@apache.org%3E
>
> [2] https://issues.apache.org/jira/browse/CALCITE-1945
>
> On Mon, Jan 22, 2018 at 10:15 PM, yiifeger wu <va...@gmail.com>
> wrote:
> > Hi,
> > The AVG Function of an integer column will return a double or decimal
> > result in general databases, like MySQL or HIVE. But Calcite will return
> > an integer result which I test in org.apache.calcite.test.CsvTest
> >
> > the SQL:
> >
> > sql("model", "select avg(DEPTNO) from EMPS").ok();
> >
> >
> > the Result:
> > 21
> >
> > the content of table EMPS
> >
> > EMPNO:long,NAME:string,DEPTNO:int,GENDER:string,CITY:string,
> EMPID:int,AGE:int,SLACKER:boolean,MANAGER:boolean,JOINEDAT:date
> > 100,"Fred",10,,,30,25,true,false,"1996-08-03"
> > 110,"Eric",20,"M","San Francisco",3,80,,false,"2001-01-01"
> > 110,"John",40,"M","Vancouver",2,,false,true,"2002-05-03"
> > 120,"Wilma",20,"F",,1,5,,true,"2005-09-07"
> > 130,"Alice",40,"F","Vancouver",2,,false,true,"2007-01-01"
> >
> > 140,"Alice",0,"F","Vancouver",2,,false,true,"2007-01-01"
> >
> >
> > Secondly, I try the sql:
> >
> > select avg(cast (DEPTNO as double)) from EMPS
> >
> > it can get the right result 21.666...
> >
> >
> > But it also looks like weired, the return type of AVG(Integer) is
> integer.
>
Re: Query Avg on integer column returns wrong result
Posted by Julian Hyde <jh...@apache.org>.
This has been discussed before. See [1] and [2].
Julian
[1] https://mail-archives.apache.org/mod_mbox/calcite-dev/201710.mbox/%3C3A407BE1-EACD-4B21-8BB2-B00632137C44@apache.org%3E
[2] https://issues.apache.org/jira/browse/CALCITE-1945
On Mon, Jan 22, 2018 at 10:15 PM, yiifeger wu <va...@gmail.com> wrote:
> Hi,
> The AVG Function of an integer column will return a double or decimal
> result in general databases, like MySQL or HIVE. But Calcite will return
> an integer result which I test in org.apache.calcite.test.CsvTest
>
> the SQL:
>
> sql("model", "select avg(DEPTNO) from EMPS").ok();
>
>
> the Result:
> 21
>
> the content of table EMPS
>
> EMPNO:long,NAME:string,DEPTNO:int,GENDER:string,CITY:string,EMPID:int,AGE:int,SLACKER:boolean,MANAGER:boolean,JOINEDAT:date
> 100,"Fred",10,,,30,25,true,false,"1996-08-03"
> 110,"Eric",20,"M","San Francisco",3,80,,false,"2001-01-01"
> 110,"John",40,"M","Vancouver",2,,false,true,"2002-05-03"
> 120,"Wilma",20,"F",,1,5,,true,"2005-09-07"
> 130,"Alice",40,"F","Vancouver",2,,false,true,"2007-01-01"
>
> 140,"Alice",0,"F","Vancouver",2,,false,true,"2007-01-01"
>
>
> Secondly, I try the sql:
>
> select avg(cast (DEPTNO as double)) from EMPS
>
> it can get the right result 21.666...
>
>
> But it also looks like weired, the return type of AVG(Integer) is integer.