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
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;
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.
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.
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.
The text was updated successfully, but these errors were encountered:
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:
download a simple, reproducible data file
http://forge.scilab.org/index.php/p/rdataset/source/file/master/csv/datasets/mtcars.csv
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:
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:
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.
The text was updated successfully, but these errors were encountered: