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

stddev_pop inconsistent behaviour #6627

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

stddev_pop inconsistent behaviour #6627

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

Comments

@monetdb-team
Copy link

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

Date: 2018-07-18 19:08:18 +0200
From: Manuel <>
To: SQL devs <>
Version: 11.29.7 (Mar2018-SP1)

Last updated: 2018-08-31 13:23:09 +0200

Comment 26553

Date: 2018-07-18 19:08:18 +0200
From: Manuel <>

User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/67.0.3396.99 Safari/537.36
Build Identifier:

The function stddev_pop has inconsistent behaviours depending on the groupings.
I will attach the sql used in the example:

The query:

select stddev_pop("C"), "A" from "unitTestDontDelete" group by "A";

returns

L2 A


2.82843 Cat1
2.23607 Cat2

i.e., stddev_pop returns null, if all the values for that group have a null value.

The query

select stddev_pop("C"), "A", "B" from "unitTestDontDelete" group by "A", "B";

returns

L2 A B

0
0 Cat1 0
0 Cat2 1
0 Cat1 2
0 Cat2 3
0 Cat1 4
0 Cat2 5
0 Cat1 6
0 Cat2 7
0 Cat1 8

i.e., stddev_pop returns 0, if all the values for that group have a null value.

Reproducible: Always

Steps to Reproduce:

  1. Import the table unitTestDontDelete from the attached sql file
  2. run the two queries

Actual Results:

L2 A

and

L2 A B

0

Expected Results:

consistent results, regardless of the groupings.

Comment 26554

Date: 2018-07-18 19:08:49 +0200
From: Manuel <>

Created attachment 606
sql file used to create the table used in the exampes

Attached file: unitTestDontDelete.sql (text/plain, 1176 bytes)
Description: sql file used to create the table used in the exampes

Comment 26564

Date: 2018-07-23 14:54:32 +0200
From: MonetDB Mercurial Repository <>

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

For complete details, see https//devmonetdborg/hg/MonetDB?cmd=changeset;node=a64e00558d71

Changeset description:

Population standard deviation for singleton groups is 0 unless value is nil.

There was a shortcut for singleton groups (as many groups as input
values) that returned 0 for all groups (for stddev_pop()), but it
didn't take into consideration that one or more values might be nil
(NULL) (in which case the result for that group should be nil as
well).

This fixes bug #6627.

Comment 26565

Date: 2018-07-23 14:54:36 +0200
From: MonetDB Mercurial Repository <>

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

For complete details, see https//devmonetdborg/hg/MonetDB?cmd=changeset;node=1a27ca5c032c

Changeset description:

Test for bug #6627.

Comment 26566

Date: 2018-07-23 15:05:01 +0200
From: @sjoerdmullender

Should be fixed now.

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