This has been discussed before. See [1] and [2]. Julian
[1] https://mail-archives.apache.org/mod_mbox/calcite-dev/201710.mbox/%[email protected]%3E [2] https://issues.apache.org/jira/browse/CALCITE-1945 On Mon, Jan 22, 2018 at 10:15 PM, yiifeger wu <[email protected]> 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.
