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

mclient and ODBC driver report 'type mismatch' when stddev_pop used in a select which returns 0 rows #3628

Closed
monetdb-team opened this issue Nov 30, 2020 · 0 comments
Labels
bug Something isn't working normal SQL

Comments

@monetdb-team
Copy link

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:

Distinguish type mismatch from empty bat in stddev/variance.
Also add test.
This fixes bug #3628.

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.

@monetdb-team monetdb-team added bug Something isn't working normal SQL labels Nov 30, 2020
@sjoerdmullender sjoerdmullender added this to the Ancient Release milestone Feb 7, 2022
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
bug Something isn't working normal SQL
Projects
None yet
Development

No branches or pull requests

2 participants