Hi all,

I am currently testing on the average results for int and smallint, using 
h2-1.3.175 (also simulated on h2-1.4.182). However, the results are not 
rounded accurately.

Test case :
     create table testAvgDistinct ( colInt int, colSINT smallint);
     insert into testAvgDistinct (colInt, colSINT)  values (NULL,NULL);
     insert into testAvgDistinct (colInt, colSINT)  values (-1,-1);
     insert into testAvgDistinct (colInt, colSINT)  values (0,0);
     insert into testAvgDistinct (colInt, colSINT)  values (1,1);
     insert into testAvgDistinct (colInt, colSINT)  values (10,10);

     select sum(colInt) as sumINT, count(colInt) as countINT, avg(colInt) 
as avgINT, sum(colSInt) as sumSINT, count(colSInt) as countSINT, 
avg(colSInt) as avgSINT from testAvgDistinct;

For both int and smallint, sum = 10, count = 4, avg = 2 . Mathematically, 
avg is 2.5 and should be rounded to 3. However, the result given is 2.

The same goes for avg(distinct).

Thanks!



-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.
package org.H2.samples;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

import org.h2.tools.DeleteDbFiles;

public class TestAvgDistinct {

	public static void main(String[] args) throws Exception {
		// delete the database named 'test' in the user home directory
		DeleteDbFiles.execute("~", "test", true);

		Class.forName("org.h2.Driver");
		Connection conn = DriverManager.getConnection("jdbc:h2:~/test;MV_STORE=FALSE");
		Statement stat = conn.createStatement();

		// this line would initialize the database
		// from the SQL script file 'init.sql'
		// stat.execute("runscript from 'init.sql'");
		stat.execute("create table testAvgDistinct ( colInt int, colSInt smallint)");
		stat.execute("insert into testAvgDistinct (colInt, colSInt)  values (NULL,NULL)");
		stat.execute("insert into testAvgDistinct (colInt, colSInt)  values (-1,-1)");
		stat.execute("insert into testAvgDistinct (colInt, colSInt)  values (0,0)");
		stat.execute("insert into testAvgDistinct (colInt, colSInt)  values (1,1)");
		stat.execute("insert into testAvgDistinct (colInt, colSInt)  values (10,10)");
		ResultSet rs = stat
				.executeQuery("select sum(colInt) as sumINT, count(colInt) as countINT, avg(colInt) as avgINT, sum(colSInt) as sumSINT, count(colSInt) as countSINT, avg(colSInt) as avgSINT from testAvgDistinct");

		while (rs.next()) {
			for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++) {
				System.out.println("Column Name :"
						+ rs.getMetaData().getColumnName(i));
				System.out.println("Column Value :" + rs.getString(i));
			}
		}

		stat.close();
		conn.close();
	}
	
}

Reply via email to