median function breaks when the column contains NULL values

[monetdb-team]

Date: 2013-05-02 21:20:08 +0200
From: Anthony Damico <>
To: SQL devs <>
Version: 11.15.1 (Feb2013)
CC: ajdamico

Last updated: 2013-07-03 08:48:02 +0200

Comment 18705

Date: 2013-05-02 21:20:08 +0200
From: Anthony Damico <>

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

avg(), sum(), min(), max() all work when a column contains some NULL values. median does not (at least not in the very simple example below)

Reproducible: Always

Steps to Reproduce:

Steps to Reproduce:

1. download a simple, reproducible data file
   http://forge.scilab.org/index.php/p/rdataset/source/file/master/csv/datasets/mtcars.csv

2. run these commands

CREATE TABLE mtcars ( rownames VARCHAR(255) , mpg DOUBLE PRECISION , cyl DOUBLE PRECISION , disp DOUBLE PRECISION , hp DOUBLE PRECISION , drat DOUBLE PRECISION , wt DOUBLE PRECISION , qsec DOUBLE PRECISION , vs DOUBLE PRECISION , am DOUBLE PRECISION , gear DOUBLE PRECISION , carb DOUBLE PRECISION ) ;

COPY 33 offset 2 records into mtcars from 'c:\users\anthonyd.kff\desktop\mtcars.csv' USING DELIMITERS ',','\n','"' NULL as '' ;

UPDATE mtcars SET mpg = NULL WHERE cyl = 6 ;

select avg( mpg ) from mtcars ;
select sum( mpg ) , min( mpg ) , max( mpg ) from mtcars ;

select median( mpg ) from mtcars ;
b must be dense-headed

Actual Results:

explain select median( mpg ) from mtcars ;
+-------------------------------------------------------------------+
| mal |
+===================================================================+
| function user.s6_2{autoCommit=true}():void; |
| X_17 := nil:dbl; |
| barrier X_34 := language.dataflow(); |
| X_2 := sql.mvc(); |
| X_3:bat[:oid,:oid] := sql.tid(X_2,"sys","mtcars"); |
| X_6 := sql.bind(X_2,"sys","mtcars","mpg",0); |
| (X_9,r1_9) := sql.bind(X_2,"sys","mtcars","mpg",2); |
| X_12 := sql.bind(X_2,"sys","mtcars","mpg",1); |
| X_14 := sql.delta(X_6,X_9,r1_9,X_12); |
| X_15 := algebra.leftfetchjoin(X_3,X_14); |
| X_16 := algebra.selectNotNil(X_15); |
| X_17 := aggr.median(X_16); |
| exit X_34; |
| sql.exportValue(1,"sys.mtcars","L1","double",53,0,9,X_17,""); |
| end s6_2; |
+-------------------------------------------------------------------+

plan select median( mpg ) from mtcars ;
+-----------------------------------------------------------------------------+
| rel |
+=============================================================================+
| project ( |
| | group by ( |
| | | table(sys.mtcars) [ mtcars.rownames, mtcars.mpg, mtcars.%TID% NOT NULL |
: ] COUNT :
| | ) [ ] [ sys.median no nil (mtcars.mpg) as L1 ] |
| ) [ L1 ] |
+-----------------------------------------------------------------------------+
5 tuples (0.660ms)
sql>

Expected Results:

returned the median() value for mpg column of the mtcars data table

Comment 18706

Date: 2013-05-02 21:31:14 +0200
From: Anthony Damico <>

sorry, should've included:

MonetDB 5 server v11.15.7 "Feb2013-SP2"
Serving database 'test', using 8 threads
Compiled for x86_64-pc-winnt/64bit with 64bit OIDs dynamically linked
Found 7.860 GiB available main-memory.
Copyright (c) 1993-July 2008 CWI.
Copyright (c) August 2008-2013 MonetDB B.V., all rights reserved
Visit http://www.monetdb.org/ for further information
Listening for connection requests on mapi:monetdb://127.0.0.1:50000/
MonetDB/JAQL module loaded
MonetDB/SQL module loaded

Comment 18708

Date: 2013-05-03 10:07:08 +0200
From: MonetDB Mercurial Repository <>

Changeset 0768b9f3fc76 made by Sjoerd Mullender sjoerd@acm.org in the MonetDB repo, refers to this bug.

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

Changeset description:

Fix bug #3280: sometimes allow non-dense heads in subaggr.

Comment 18709

Date: 2013-05-03 10:07:56 +0200
From: @sjoerdmullender

Thanks for the report and the test.
The bug is fixed for the next release.