Strange Error: SUM or AVG on wrong data type for SUM(ZERO) [90015-44];

[Pavel]

This is the error I got for the following test case.

public class CreateTest extends TestCase {
private Connection ca;

protected void setUp() throws Exception {
Class.forName("org.h2.Driver");
ca = DriverManager.getConnection("jdbc:h2:mem:");
}

protected void tearDown() throws Exception {
ca.close();
}

public void testLog() throws Exception {

Statement statement = ca.createStatement();
statement.execute("CREATE SCHEMA WORK");
statement.execute("CREATE TABLE WORK.NUMBERS(NUMBER NUMBER)");
String s1 = "create view work.withlog as\n"+
"select numbers.number as number,\n"+
"(sign(number) = -1) as negative,\n"+
"(number=0) as zero,\n"+
"log(abs(number)) as log\n"+
"from work.numbers";
statement.execute(s1);
String s2 = "create view work.product as\n"+
"select exp(sum(log))*(sum(zero)=0) *\n"+
"((mod(sum(negative),2)=0) +\n"+
"((mod(sum(negative),2)=1)*-1)) as result\n"+
"from work.withlog";
statement.execute(s2);
statement.execute(s2);

}

}

org.h2.jdbc.JdbcSQLException: SUM or AVG on wrong data type for
SUM(ZERO) [90015-44]; SQL statement: create view work.product as
select sum(log) as logsum,
exp(sum(log)) as logprod,
exp(sum(log))*(sum(zero)=0) as logprodz,
mod(sum(negative),2)=0 as pos,
mod(sum(negative),2)=1 as neg,
exp(sum(log)) *
((mod(sum(negative),2)=0) +
((mod(sum(negative),2)=1)*-1)) as resultnz,
exp(sum(log))*(sum(zero)=0) *
((mod(sum(negative),2)=0) +
((mod(sum(negative),2)=1)*-1)) as result
from work.withlog
at org.h2.message.Message.getSQLException(Message.java:65)
at org.h2.message.Message.getSQLException(Message.java:47)
at org.h2.expression.Aggregate.optimize(Aggregate.java:268)
at org.h2.expression.Comparison.optimize(Comparison.java:86)
at org.h2.expression.Operation.optimize(Operation.java:136)
at org.h2.expression.Alias.optimize(Alias.java:46)
at org.h2.command.dml.Select.prepare(Select.java:461)
at org.h2.command.Parser.parseCreateView(Parser.java:3251)
at org.h2.command.Parser.parseCreate(Parser.java:2855)
at org.h2.command.Parser.parse(Parser.java:238)
at org.h2.command.Parser.parse(Parser.java:184)
at org.h2.command.Parser.prepareCommand(Parser.java:155)
at org.h2.engine.Session.prepareLocal(Session.java:175)
at org.h2.engine.Session.prepareCommand(Session.java:157)
at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:993)
at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:143)
at org.example.CreateTest.testLog(CreateTest.java:79)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:
39)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:
25)
at
com.intellij.rt.execution.junit2.JUnitStarter.main(JUnitStarter.java:
32)

[Pavel]

Another simper example which gives the same error. The example is
coming from legacy database system.

public void testComplexSum() throws Exception {
Class.forName("org.h2.Driver");
Connection ca = DriverManager.getConnection("jdbc:h2:mem:");

Statement sa = ca.createStatement();
sa.execute("CREATE TABLE A(X NUMBER, Y CHAR)");
sa.executeQuery("SELECT SUM(X BETWEEN 10 AND 20) FROM A GROUP
BY Y");
ca.close();
}

[Thomas Mueller]

Hi,

The problem is you use the function SUM on a boolean value:

SELECT SUM(X BETWEEN 10 AND 20) FROM A GROUP BY Y

means sum of: false, false, true, true, ... , false. Probably the
query is wrong (I don't know what you want to calculate here). Maybe
this?

SELECT A, SUM(X) FROM A WHERE X BETWEEN 10 AND 20 GROUP BY Y;

The same (SUM on boolean values) is the problem with the NUMBERS case.

Thomas