Date: 2013-08-15 00:53:49 +0200
From: Tim H. <<monetdb.bug.reporter>>
To: SQL devs <>
Version: 11.15.7 (Feb2013-SP2)
CC: @njnes

Last updated: 2013-09-27 13:47:13 +0200

Comment 19001

Date: 2013-08-15 00:53:49 +0200
From: Tim H. <<monetdb.bug.reporter>>

User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:22.0) Gecko/20100101 Firefox/22.0
Build Identifier:

There is no sys.median function overload for the decimal type, so calling select on sys.median on a decimal column causes MonetDB to try to find an automatic conversion. Unfortunately it chooses tinyint instead of decimal for the conversion, and the query fails at runtime.

Reproducible: Always

Steps to Reproduce:

1. Connect as user monetdb in mclient
2. Run the following commands
   create table part (p_partkey integer not null, p_name varchar(55) not null, p_mfgr char(25) not null, p_brand char(10) not null, p_type varchar(25) not null, p_size integer not null, p_container char(10) not null, p_retailprice decimal(12,2) not null, p_comment varchar(23) not null);
   copy 5 records into part from stdin;
   1|goldenrod lavender spring chocolate lace|Manufacturer1|Brand13|PROMO BURNISHED COPPER|7|JUMBO PKG|901.00|ly. slyly ironi|
   2|maroon sky cream royal snow|Manufacturer1|Brand13|LARGE BRUSHED BRASS|1|LG CASE|902.00|lar accounts amo|
   3|brown blue puff midnight black|Manufacturer4|Brand42|STANDARD POLISHED BRASS|21|WRAP CASE|903.00|egular deposits hag|
   4|orange goldenrod peach misty seashell|Manufacturer3|Brand34|SMALL PLATED BRASS|14|MED DRUM|904.00|p furiously r|
   5|midnight linen almond tomato plum|Manufacturer3|Brand32|STANDARD POLISHED TIN|15|SM PKG|905.00| wake carefully |

select sys.median(cast (p_retailprice as double)) from part;
select sys.median(p_retailprice) from part;

Actual Results:

value (901) exceeds limits of type bte

Expected Results:

+--------------------------+
| L1 |
+==========================+
| 903 |
+--------------------------+

The issue is in the _rel_aggr function in rel_select.c. After not finding an exact match for sys.median(decimal), the code calls sql_find_aggr. This returns only one sys.median definition (there are 10), which is the sys.median defined for tinyint.

Comment 19027

Date: 2013-08-18 23:05:14 +0200
From: MonetDB Mercurial Repository <>

Changeset 46f634cbe540 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=46f634cbe540

Changeset description:

fixed bug #3342, ie cast a numeric up when a direct aggregation function isn't found.

Comment 19028

Date: 2013-08-18 23:08:03 +0200
From: @njnes

fixed, when a direct aggregation function is missing we now cast numerics to a super type.