Division result exceeds numeric precision constraint #3731
Description
The problem
We use version 2.1.214, and it fails on the current master as well.
To reproduce the problem, please run this SQL script:
create table TEST (
ID INT,
VALUE_SUM INTEGER,
VALUE_COUNT NUMERIC(100000, 0)
);
insert into TEST VALUES(1, 23116, 11069533);
select sum(VALUE_SUM) *100000 / min(VALUE_COUNT) from TEST;Last line will trigger following error:
[22001][22001] Value too long for column "NUMERIC": "208.8254310276684662306892260043851895107047424674554924765118817568907378477484... (100003)"; SQL statement: select sum(VALUE_SUM) *100000 / min(VALUE_COUNT) from TEST [22001-214]
I debugged the org.h2.expression.BinaryOperation#optimizeNumeric; it creates type info with precision and scale set to 100000. The produced result will stick to the defined scale, but it fails as the division result is bigger than one.
I see that performing additional rounding in org.h2.value.ValueNumeric#divide could help, but I wonder if it's in line with the desired behavior.
Please let me know if I can help you with the bug fix. I am eager to contribute.
I am unsure how relevant this might be, but PostgreSQL can deal with this script after changing the precision to the accepted maximum value of 1000.
Code
Code to reproduce the issue:
public class PrecisionBug {
public static final Logger LOGGER = Logger.getLogger(PrecisionBug.class.getName());
private static final String QUERY = "select sum(VALUE_SUM) *100000 / min(VALUE_COUNT) from TEST;";
private static final String CREATE = "create table TEST (ID INT, VALUE_SUM INTEGER, VALUE_COUNT NUMERIC(100000, 0));";
private static final String INSERT = "insert into TEST VALUES(1, 23116, 11069533)";
public static void main(String... args) throws Exception {
Class.forName("org.h2.Driver");
try (Connection connection = DriverManager.getConnection("jdbc:h2:mem:test")) {
Statement st = connection.createStatement();
st.execute(CREATE);
st.execute(INSERT);
st.executeQuery(QUERY);
} catch (Exception e) {
LOGGER.log(Level.SEVERE, "Query error.", e);
}
}
}