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

"wrd" is an internal MonetDB type. Which SQL type did you use in you your create table statement? I.e., what does \d x07 say about the type of column rx_cso_num ? Or did you create the table as result of a SQL query (as you suggest?)? Then, the clause(s) of the query that determine column rx_cso_num as well as the (SQL) types of the related columns of the source table (if any) would be useful. Also, could you run the failing queries in mclient prefixed with "explain" and "trace" (i.e., run it twice), and send us the output (in case of "trace", only the second result set is of interest). Thanks! Stefan On Tue, Apr 23, 2013 at 11:01:43AM -0400, Anthony Damico wrote:

Oh, bizarre - I've never seen this type before. The type is "wrd"? Maybe that gives a clue?

I would rather not specify the exact maximum, but the approximate range of this column is very boring: 0 to 999.

If it makes any difference, this column was created with a command structured like this one:

COUNT( CASE WHEN MY_CODE IN ('01','02') THEN UNIQUE_ROW_IDENTIFIER END ) as rx_cso_num

I had assumed that construction would create an `int` or at least `double` but apparently not.

I suppose I could re-run the whole thing with the *1 (shown below) instead? - and that should convert the resultant column to an integer? Still, it seems odd..

COUNT( CASE WHEN MY_CODE IN ('01','02') THEN UNIQUE_ROW_IDENTIFIER END )*1 as rx_cso_num

Thanks! :)

On Tue, Apr 23, 2013 at 10:41 AM, Stefan Manegold wrote:

...

Also, the min and max value of that column might be of help.

----- Original Message -----

...

Hi Anthony,

since all the error messages appear to be type related, coul you please share the schema of your "x07" table with us, or at least the type of your "rx_cso_num" column?

Thanks! Stefan

----- Original Message -----

...

Hi, I'm using MonetDB on Windows with the MonetDB.R connector. I'm getting some very strange errors on a particular column (called `rx_cso_num`) where *some* of the math functions work and others do not? I tested the median() function using mclient - I get the same error - so this has nothing to do with the fact that I'm connecting through R.

I'm running these queries on a confidential data set, so unfortunately I cannot provide a reproducible example with this.. I'm sorry for the hassle, and hoping the error messages below might provide enough of a clue to resolve the issue? If this really isn't enough, I can send some snipped mclient -X output and/or try my best to create some fake data that also creates some of these problems?

Thanks!! :)

Here's a list of functions that *work* on the column without a problem:

"count","distinct","min", "max",

"sum","avg","abs","sqrt","floor","ceiling","exp","log","cos","sin","tan","acos","asin","atan","cosh","sinh","tanh"

...

Here's the errors I get for each of the functions that do not:

[1] "median" Error in dbGetQuery(db, paste0("select ", i, "(rx_cso_num) from x07")) : Unable to execute statement 'select median(rx_cso_num) from x07'. Server says '!22003!overflow in conversion of 133 to bte.'.

[1] "sign" Error in dbGetQuery(db, paste0("select ", i, "(rx_cso_num) from x07")) : Unable to execute statement 'select sign(rx_cso_num) from x07'. Server says '!TypeException:user.s5_1[25]:'bat.insert' undefined in: _93:any := bat.insert(_83:bat[:oid,:int], _91:oid, _90:bte)'.

[1] "stddev_pop" Error in dbGetQuery(db, paste0("select ", i, "(rx_cso_num) from x07")) : Unable to execute statement 'select stddev_pop(rx_cso_num) from x07'. Server says '!SELECT: no such unary operator 'stddev_pop(wrd)''.

[1] "stddev" Error in dbGetQuery(db, paste0("select ", i, "(rx_cso_num) from x07")) : Unable to execute statement 'select stddev(rx_cso_num) from x07'. Server says '!SELECT: no such unary operator 'stddev(wrd)''.

[1] "prod" Error in dbGetQuery(db, paste0("select ", i, "(rx_cso_num) from x07")) : Unable to execute statement 'select prod(rx_cso_num) from x07'. Server says '!22003!overflow in conversion of 133 to bte.'.

Here's my mserver window:

# MonetDB 5 server v11.15.3 "Feb2013-SP1" # Serving database 'medicare_sample', using 8 threads # Compiled for x86_64-pc-winnt/64bit with 64bit OIDs dynamically linked # Found 7.860 GiB available main-memory. # Copyright (c) 1993-July 2008 CWI. # Copyright (c) August 2008-2013 MonetDB B.V., all rights reserved # Visit http://www.monetdb.org/ for further information # Listening for connection requests on mapi:monetdb://127.0.0.1:49800/ # MonetDB/JAQL module loaded # MonetDB/SQL module loaded

...

Thanks!!!

_______________________________________________ users-list mailing list users-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list

-- | Stefan.Manegold@CWI.nl | DB Architectures (DA) | | www.CWI.nl/~manegold/ | Science Park 123 (L321) | | +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) |

_______________________________________________ users-list mailing list users-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list

-- | Stefan.Manegold@CWI.nl | DB Architectures (DA) | | www.CWI.nl/~manegold/ | Science Park 123 (L321) | | +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) |

_______________________________________________ users-list mailing list users-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list

_______________________________________________ users-list mailing list users-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list

-- | Stefan.Manegold@CWI.nl | DB Architectures (DA) | | www.CWI.nl/~manegold | Science Park 123 (L321) | | +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) |