Date: 2016-11-16 15:10:09 +0100
From: Anthony Damico <>
To: SQL devs <>
Version: 11.23.13 (Jun2016-SP2)
CC: ajdamico

Last updated: 2016-12-21 13:07:55 +0100

Comment 24687

Date: 2016-11-16 15:10:09 +0100
From: Anthony Damico <>

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

these pairs of statements produce the same statistical result, but they should not?

UPDATE mtcars SET mpg = 0 WHERE cyl = 4
SELECT QUANTILE( mpg , 0.5 ) FROM mtcars

UPDATE mtcars SET mpg = NULL WHERE cyl = 4
SELECT QUANTILE( mpg , 0.5 ) FROM mtcars

Reproducible: Always

Steps to Reproduce:

code to copy & paste into R console

i have noted the expected and actual results below

library(DBI)
library(MonetDBLite)

db <- dbConnect( MonetDBLite() )

dbWriteTable( db , 'mtcars' , mtcars )

 CORRECT: 14.7
dbSendQuery( db , "UPDATE mtcars SET mpg = 0 WHERE cyl = 4" )
dbGetQuery( db , "SELECT MEDIAN( mpg ) FROM mtcars" )

 CORRECT: 16.4
dbSendQuery( db , "UPDATE mtcars SET mpg = NULL WHERE cyl = 4" )
dbGetQuery( db , "SELECT MEDIAN( mpg ) FROM mtcars" )

 CORRECT: 14.7
dbSendQuery( db , "UPDATE mtcars SET mpg = 0 WHERE cyl = 4" )
dbGetQuery( db , "SELECT QUANTILE( mpg , 0.5 ) FROM mtcars" )

 INCORRECT: 14.7
dbSendQuery( db , "UPDATE mtcars SET mpg = NULL WHERE cyl = 4" )
dbGetQuery( db , "SELECT QUANTILE( mpg , 0.5 ) FROM mtcars" )

Actual Results:

NULL values treated as zero

Expected Results:

NULL values removed from query

MEDIAN() works as expected. did not check other functions on

https://www.monetdb.org/Documentation/SQLreference/StatisticFunctions

thanks!!

Comment 24734

Date: 2016-11-29 17:18:07 +0100
From: MonetDB Mercurial Repository <>

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

Changeset description:

Skip nils when calculating quantiles, just as we do for median.
This fixes bug #6120.