Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

QUANTILE() treats NULL as if it is zero #6120

Closed
monetdb-team opened this issue Nov 30, 2020 · 0 comments
Closed

QUANTILE() treats NULL as if it is zero #6120

monetdb-team opened this issue Nov 30, 2020 · 0 comments

Comments

@monetdb-team
Copy link

@monetdb-team monetdb-team commented Nov 30, 2020

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.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
1 participant