You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
Date: 2014-11-27 19:34:15 +0100
From: Jerry Evans <>
To: SQL devs <>
Version: 11.19.7 (Oct2014-SP1)
Last updated: 2015-01-29 14:07:41 +0100
Comment 20461
Date: 2014-11-27 19:34:15 +0100
From: Jerry Evans <>
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/39.0.2171.65 Safari/537.36
Build Identifier:
Script that demonstrates problem
-----------------------------snip-----------------------
Create a tiny table to demonstrate
CREATE TABLE "tm_bug" (
"uid" integer NOT NULL,
"weight" double NOT NULL default 1.0,
"filter" integer NOT NULL default -1
);
insert values.
INSERT INTO "tm_bug" VALUES (1,1.0,1);
INSERT INTO "tm_bug" VALUES (2,1.0,1);
INSERT INTO "tm_bug" VALUES (3,1.0,2);
these queries return expectd data
select count(uid) as cnt1,stddev_pop(weight) as f1 from tm_bug where filter = 1;
select count(uid) as cnt2,stddev_pop(weight) as f2 from tm_bug where filter = 2;
this will return type mismatch
select count(uid) as cnt3,stddev_pop(weight) as f3 from tm_bug where filter = 3;
-----------------------------snip-----------------------
Reproducible: Always
Steps to Reproduce:
The script above contains the repeatable error but the select that fails is here:
select count(uid) as cnt3,stddev_pop(weight) as f3 from tm_bug where filter = 3;
Actual Results:
Type mismatch
Expected Results:
One of 2 possibilities:
returned 2 columns, with cnt3 = 0 and f3 either null or 0.
or raised a divide by zero error as this is most likely the root cause of the problem given that stddev_pop would in this case have a 0 valued denominator
it would be good to have this behaviour configurable. As things stand it is a problem when used in more complex multi-term queries like this (which might well be made on the grounds of efficiency)
select count(uid), sum(weight), avg(weight), stddev_pop(weight) from tm_bug where filter = 3;
returns 'type mismatch'
select count(uid), sum(weight), avg(weight) from tm_bug where filter = 3;
Not a division by 0 but just a lack of distinguising bad input (wrong type for calculating standard deviation) from no input.
I think the result for an "empty" standard deviation and variance should be null, so that's what it is. But if the SQL standard says differently, we can change it.
Comment 20474
Date: 2014-11-28 12:49:00 +0100
From: Jerry Evans <>
Thanks for the attention to this. I agree. It makes much more sense to return null - this also greatly simplifies client code.
Date: 2014-11-27 19:34:15 +0100
From: Jerry Evans <>
To: SQL devs <>
Version: 11.19.7 (Oct2014-SP1)
Last updated: 2015-01-29 14:07:41 +0100
Comment 20461
Date: 2014-11-27 19:34:15 +0100
From: Jerry Evans <>
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/39.0.2171.65 Safari/537.36
Build Identifier:
Script that demonstrates problem
-----------------------------snip-----------------------
Create a tiny table to demonstrate
CREATE TABLE "tm_bug" (
"uid" integer NOT NULL,
"weight" double NOT NULL default 1.0,
"filter" integer NOT NULL default -1
);
insert values.
INSERT INTO "tm_bug" VALUES (1,1.0,1);
INSERT INTO "tm_bug" VALUES (2,1.0,1);
INSERT INTO "tm_bug" VALUES (3,1.0,2);
these queries return expectd data
select count(uid) as cnt1,stddev_pop(weight) as f1 from tm_bug where filter = 1;
select count(uid) as cnt2,stddev_pop(weight) as f2 from tm_bug where filter = 2;
this will return type mismatch
select count(uid) as cnt3,stddev_pop(weight) as f3 from tm_bug where filter = 3;
-----------------------------snip-----------------------
Reproducible: Always
Steps to Reproduce:
The script above contains the repeatable error but the select that fails is here:
select count(uid) as cnt3,stddev_pop(weight) as f3 from tm_bug where filter = 3;
Actual Results:
Type mismatch
Expected Results:
One of 2 possibilities:
returned 2 columns, with cnt3 = 0 and f3 either null or 0.
or raised a divide by zero error as this is most likely the root cause of the problem given that stddev_pop would in this case have a 0 valued denominator
it would be good to have this behaviour configurable. As things stand it is a problem when used in more complex multi-term queries like this (which might well be made on the grounds of efficiency)
select count(uid), sum(weight), avg(weight), stddev_pop(weight) from tm_bug where filter = 3;
returns 'type mismatch'
select count(uid), sum(weight), avg(weight) from tm_bug where filter = 3;
returns 0,null,null
which somehow makes more sense intuitively ...?
Comment 20464
Date: 2014-11-28 09:59:36 +0100
From: MonetDB Mercurial Repository <>
Changeset c11095d21be5 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=c11095d21be5
Changeset description:
Comment 20465
Date: 2014-11-28 10:02:23 +0100
From: @sjoerdmullender
Not a division by 0 but just a lack of distinguising bad input (wrong type for calculating standard deviation) from no input.
I think the result for an "empty" standard deviation and variance should be null, so that's what it is. But if the SQL standard says differently, we can change it.
Comment 20474
Date: 2014-11-28 12:49:00 +0100
From: Jerry Evans <>
Thanks for the attention to this. I agree. It makes much more sense to return null - this also greatly simplifies client code.
Comment 20608
Date: 2015-01-29 14:07:41 +0100
From: @sjoerdmullender
Oct2014-SP2 has been released.
The text was updated successfully, but these errors were encountered: