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:
Connect as user monetdb in mclient
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;
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.
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:
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:
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.
The text was updated successfully, but these errors were encountered: