MEDIAN: no such operator 'median(decimal)'

[monetdb-team]

Date: 2013-08-02 22:17:11 +0200
From: Tim H. <<monetdb.bug.reporter>>
To: SQL devs <>
Version: 11.15.11 (Feb2013-SP3)
CC: @njnes

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

Comment 18970

Date: 2013-08-02 22:17:11 +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:

Using the TPCH orders table with the SF1 data set loaded, I cannot compute the median of any column. E.g.

sql>select median(o_totalprice) from orders;
MEDIAN: no such operator 'median(decimal)'
sql>select median(o_shippriority) from orders;
MEDIAN: no such operator 'median(int)'

Reproducible: Always

Steps to Reproduce:

1. Create the orders table from the TPCH schema
2. Load the data for scale factor 1 (SF1)
3. Log in as user other than monetdb
4. select median(o_totalprice) from orders;

Actual Results:

MEDIAN: no such operator 'median(decimal)'

Expected Results:

+--------------------------+
| L1 |
+==========================+
| {the answer} |
+--------------------------+

Sometimes I see a different error: value ({some number}) exceeds limits of type bte

Example:
sql>select max(o_totalprice), min (o_totalprice), avg (o_totalprice), median(o_totalprice) from pub.orders;
value (130445) exceeds limits of type bte

Comment 18982

Date: 2013-08-08 21:30:41 +0200
From: @njnes

Using a cast it should work okay.

Comment 18990

Date: 2013-08-12 22:08:48 +0200
From: Tim H. <<monetdb.bug.reporter>>

The issue appears to be that aggregation functions aren't resolved correctly when a non-admin user is using a different default schema.

Here are the steps to reproduce:

As monetdb:
CREATE ROLE "test_role";
CREATE SCHEMA "test" WITH AUTHORIZATION "test_role";
CREATE USER "admin" WITH PASSWORD '12345' NAME 'Admin' SCHEMA "test";
GRANT "test_role" to "admin";

As admin do the following commands in mclient (use the attached files):
sql>< /path/to/part.sql
sql>copy into "part" from '/path/to/part.tbl';

Then try the following queries (still as the admin user):
select median(cast (p_retailprice AS double)) from part;
select sys.median(cast (p_retailprice AS double)) from part;
select sys."median"(cast (p_retailprice AS double)) from part;

Comment 18991

Date: 2013-08-12 22:09:48 +0200
From: Tim H. <<monetdb.bug.reporter>>

Created attachment 216
Script to create the example table referenced in the repro steps.

Attached file: part.sql (application/octet-stream, 287 bytes)
Description: Script to create the example table referenced in the repro steps.

Comment 18992

Date: 2013-08-12 22:10:51 +0200
From: Tim H. <<monetdb.bug.reporter>>

Created attachment 217
Data file referenced in the repro steps

Attached file: part.tbl (application/octet-stream, 591 bytes)
Description: Data file referenced in the repro steps

Comment 18997

Date: 2013-08-14 17:14:44 +0200
From: @njnes

fixed in feb2013, we now handle qualified names (ie schema.aggregationname) properly.

Comment 18998

Date: 2013-08-14 17:15:46 +0200
From: MonetDB Mercurial Repository <>

Changeset e4557e11b80c 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=e4557e11b80c

Changeset description:

added test for bug #3332