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

count( case when COL1 in ( NUM1 , NUM2 ) then COL2 end ) cause problems in functions #3277

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

Comments

@monetdb-team
Copy link

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

Date: 2013-04-25 21:36:16 +0200
From: Anthony Damico <>
To: SQL devs <>
Version: 11.15.7 (Feb2013-SP2)
CC: ajdamico

Last updated: 2013-07-03 08:48:02 +0200

Comment 18696

Date: 2013-04-25 21:36:16 +0200
From: Anthony Damico <>

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

Variables created with the query..

create table mtcars2 as select carb , count( distinct case when gear in ( 3 , 4 ) then mpg end ) as prob1 from mtcars group by carb with data ;

..seem to disrupt some of the functions in MonetDB.

on the reproducible example below, these break--
select sign( prob1 ) from mtcars2;
select stddev( prob1 ) from mtcars2;
select stddev_pop( prob1 ) from mtcars2;

--and these work, but they do NOT work on similarly-created data I cannot share.
select median( prob1 ) from mtcars2;
select prod( prob1 ) from mtcars2;

Reproducible: Always

Steps to Reproduce:

  1. download a simple, reproducible data file
    http://forge.scilab.org/index.php/p/rdataset/source/file/master/csv/datasets/mtcars.csv

  2. run these commands

CREATE TABLE mtcars ( rownames VARCHAR(255) , mpg DOUBLE PRECISION , cyl DOUBLE PRECISION , disp DOUBLE PRECISION , hp DOUBLE PRECISION , drat DOUBLE PRECISION , wt DOUBLE PRECISION , qsec DOUBLE PRECISION , vs DOUBLE PRECISION , am DOUBLE PRECISION , gear DOUBLE PRECISION , carb DOUBLE PRECISION ) ;

COPY 33 offset 2 records into mtcars from 'c:\users\anthonyd.kff\desktop\mtcars.csv' USING DELIMITERS ',','\n','"' NULL as '' ;

create table mtcars2 as select carb , count( distinct case when gear in ( 3 , 4 ) then mpg end ) as prob1 from mtcars group by carb with data ;

*THESE BREAK;
select sign( prob1 ) from mtcars2;
select stddev( prob1 ) from mtcars2;
select stddev_pop( prob1 ) from mtcars2;

*THESE WORK;
select median( prob1 ) from mtcars2;
select prod( prob1 ) from mtcars2;

Actual Results:

sql>select sign( prob1 ) from mtcars2;
TypeException:user.s6_1[16]:'bat.insert' undefined in: _53:any := bat.insert(_43
:bat[:oid,:int], _51:oid, _50:bte)
program contains errors
sql>select stddev( prob1 ) from mtcars2;
SELECT: no such unary operator 'stddev(wrd)'
sql>select stddev_pop( prob1 ) from mtcars2;
SELECT: no such unary operator 'stddev_pop(wrd)'

Expected Results:

not those.

Additional discussion on monetdb users list under the subject

"All tested on the same column, some functions work, some don't?"

Thanks!!! :)

Comment 18697

Date: 2013-04-26 11:50:24 +0200
From: MonetDB Mercurial Repository <>

Changeset 181e76a702af made by Stefan Manegold Stefan.Manegold@cwi.nl in the MonetDB repo, refers to this bug.

For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=181e76a702af

Changeset description:

added test for bug #3277:

The problem is threefold:

1)
As of the Feb2013 release of MonetDB,
stddev() has been replaced by stddev_pop() & stddev_samp(),
i.e., there is no stddev() any more.
We might have failed to communicate this change clearly.
Sorry!

2)
stddev_pop() & stddev_samp() fail on the results of count(),
as the latter returns MonetDB internal type wrd, which is
not a standard/valid SQL type, and (thus?) there is not
implementation of stddev_pop() & stddev_samp() for type wrd.
Explicitly casting the result of count() to a valid standard
SQL integer type (tinying, smallint, integer, bigint) makes
these functions work fine.
Open question is, whether we should simple "treat the symptom"
by adding implementations for stddev_pop() & stddev_samp() on
type wrd, or rather cure the cause by making count() return a
valid standard SQL type, e.g., integer on 32-bit systems and
bigint on 64-bit systems.

3)
sign() seems to fail as the created physical plan tries to
insert a value of type bte into a BAT with tail type int,
and while this should be no problem, there seems to be
no signature that allows this.
We'll need to investigate whether simply adding that
signature is a valid solution, or whether there is a need
to fix the translation of sign().

Comment 18698

Date: 2013-04-26 13:15:43 +0200
From: Anthony Damico <>

Sorry I missed the stddev -> stddev_samp, I should've known that. :)

If you're trying to decide on treating the sympton vs. a long-term cure, I hope I can convince you that the cure is much more desirable -- now with the R package MonetDB.R, a lot more statisticians are going to start using MonetDB alongside the R language.. and that means these statistical functions (median and prod too) are going to see a lot more everyday use than they probably currently do.

Thanks for looking at this!! :)

Comment 18710

Date: 2013-05-03 10:59:58 +0200
From: MonetDB Mercurial Repository <>

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

Changeset description:

Fix bug #3277: add aggregates on wrd, fix output type of sign.
aggr.sign returns a bat of type bte, so tell SQL about that.
The count aggregate returns a bat of type wrd, so in order to use
aggregates on that result, we need to have those.
Also incorporate test data into test and approve the results.

Comment 18711

Date: 2013-05-03 11:03:06 +0200
From: @sjoerdmullender

There were two unrelated bugs. The output type of the sign function was marked incorrectly as integer instead of tinyint (i.e. int instead of bte). And the aggregate functions working on the (internal) wrd type were missing. The wrd types leaks out in the result of the count aggregate.

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