Date: 2012-11-09 20:57:30 +0100
From: Brandon Jackson <>
To: clients devs <>
Version: 11.13.3 (Oct2012)
CC: monetdbuser, @njnes

Last updated: 2012-11-21 14:49:39 +0100

Comment 17928

Date: 2012-11-09 20:57:30 +0100
From: Brandon Jackson <>

User-Agent: Mozilla/5.0 (X11; Linux i686) AppleWebKit/537.11 (KHTML, like Gecko) Chrome/23.0.1271.64 Safari/537.11
Build Identifier:

When using the JDBC driver with SQuirreL 3.4 and Pentaho Mondrian 3.5 the column/field metadata returned when using aggregate functions, ex. SUM(whatever_column), always return precision and decimal as 0,0 instead of some precision derived from the input types used in the aggregate function itself.

When not using an aggregate function and simply "Select My_measure FROM table", the metadata for My_measure is returned correctly with types DECIMAL and NUMERIC.

See the attached screen captures of the testing.
The same test was performed against MySQL using their JDBC driver, which alludes to what proper output looks similar to.

Reproducible: Always

Steps to Reproduce:

1. Use SQuirreL to execute a simple query Select SUM(some_dec_or_num_col) FROM x
2. Look at the "Columns" tab to see the metadata reported back.
3. It will incorrectly report DECIMAL(0,0) etc if used on a numeric column.

Actual Results:

Mondrian having seen no precision casts the result to an int. The sums come back as 0. Squirrel is a bit smarter and guesses that there is precision and returns a valid answer.

Expected Results:

Expected when using aggregate functions that metadata returned about the dynamically created field/column would at least match the types put in.

Comment 17929

Date: 2012-11-09 21:00:47 +0100
From: Brandon Jackson <>

Created attachment 154
Precision and scale not returned using aggregate functions see M0 in the picture

Attached file: Aggregate functions with decimal or numeric results do not return precision or scale.png (image/png, 51928 bytes)
Description: Precision and scale not returned using aggregate functions see M0 in the picture

Comment 17930

Date: 2012-11-09 21:01:39 +0100
From: Brandon Jackson <>

Created attachment 155
Normal columns, no agg functions return proper precision and scale

Attached file: Non-aggregate queries return correct metadata.png (image/png, 46358 bytes)
Description: Normal columns, no agg functions return proper precision and scale

Comment 17931

Date: 2012-11-09 21:02:21 +0100
From: Brandon Jackson <>

Created attachment 156
mclient showing the proper precision and scale of the table in question

Attached file: mclient - table description.png (image/png, 23625 bytes)
Description: mclient showing the proper precision and scale of the table in question

Comment 17932

Date: 2012-11-09 21:02:56 +0100
From: Brandon Jackson <>

Created attachment 157
MySQL example showing proper scale and precision

Attached file: Result from MySQL via JDBC reporting scale and precision on aggregate function use.png (image/png, 31262 bytes)
Description: MySQL example showing proper scale and precision

Comment 17933

Date: 2012-11-12 12:44:59 +0100
From: @grobian

sql>create table type_test ( dval double, nval numeric(5,3));
operation successful (117.142ms)
sql>insert into type_test values (5.4, 5.4),(1.3,1.3),(8.252, 8.252);
3 affected rows (16.731ms)
sql>select * from type_test;
+--------------------------+---------+
| dval | nval |
+==========================+=========+
| 5.4000000000000004 | 5.400 |
| 1.3 | 1.300 |
| 8.2520000000000007 | 8.252 |
+--------------------------+---------+
3 tuples (1.678ms)
sql>select count(dval), count(nval) from type_test;
+------+-------------+
| L1 | scale_up_L2 |
+======+=============+
| 3 | 3000 |
+------+-------------+
1 tuple (2.053ms)
sql>select sum(dval), sum(nval) from type_test;
+--------------------------+---------+
| L1 | L2 |
+==========================+=========+
| 14.952000000000002 | 14.952 |
+--------------------------+---------+
1 tuple (1.674ms)
sql>

For the count on numeric, I wouldn't expect any scale_up or anything, it simply should return the number of rows (hence integer-like type). The correction made is obviously wrong.

Comment 17938

Date: 2012-11-12 23:11:29 +0100
From: @njnes

added test aggregate_incorrect_precision_scale.Bug-3182.sql

fixed in rel_aggr, fix scale isn't needed here.

Comment 17939

Date: 2012-11-12 23:12:43 +0100
From: @njnes

Changeset 31329234b6ea made by Niels Nes niels@cwi.nl in the MonetDB repo, refers to this bug.

For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=31329234b6ea

Changeset description:

added test for bug #3182

cleaned up some leftover comments

Comment 17973

Date: 2012-11-21 14:49:39 +0100
From: @sjoerdmullender

Oct2012-SP1 has been released.