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

On Tue, Apr 23, 2013 at 11:28 AM, Stefan Manegold wrote:

"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 ?

Sorry about that -- it's a BIGINT

Or did you create the table as result of a SQL query (as you suggest?)?

Yes, I did..

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.

..inside the clause: COUNT( CASE WHEN MY_CODE IN ('01','02') THEN UNIQUE_ROW_IDENTIFIER END ) as rx_cso_num both UNIQUE_ROW_IDENTIFIER and MY_CODE are VARCHAR(255). I hope that's what you're asking for?

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).

I ran trace and explain in front of all five queries. Of those ten queries, the four not shown print much more detail (that would be more helpful for troubleshooting).. but I don't understand the output, so I'm not comfortable with sending it. I'm sorry that these six results aren't terribly helpful.. sql>trace select median(rx_cso_num) from x07; overflow in conversion of 133 to bte. sql>trace select stddev(rx_cso_num) from x07; SELECT: no such unary operator 'stddev(wrd)' sql>explain select stddev(rx_cso_num) from x07; SELECT: no such unary operator 'stddev(wrd)' sql>trace select stddev_pop(rx_cso_num) from x07; SELECT: no such unary operator 'stddev_pop(wrd)' sql>explain select stddev_pop(rx_cso_num) from x07; SELECT: no such unary operator 'stddev_pop(wrd)' sql>trace select prod(rx_cso_num) from x07; overflow in conversion of 133 to bte.

Am I right that this clause is embedded in a SQL statement like

create table ... as select ... <clause> ... from <table> with data; where <table> has columns UNIQUE_ROW_IDENTIFIER and MY_CODE ?

Yes, precisely. :)

I'm sorry for the flood of e-mails. When I add a DISTINCT to the creation clause.. count( distinct case when gear in ( 3 , 4 ) then mpg end )*1 ..then median() and prod() break as well! Here is the EXPLAIN and TRACE output. Sorry, I should've tried this on fake data sooner! Now that I know I can re-create this error with public data, should I file a bug report? Thanks!!!! sql>explain select median( gears ) from mtcars4; +--------------------------------------------------------------------+ | mal | +====================================================================+ | function user.s2_2{autoCommit=true}():void; | | X_17 := nil:lng; | | barrier X_34 := language.dataflow(); | | X_2 := sql.mvc(); | | X_3:bat[:oid,:oid] := sql.tid(X_2,"sys","mtcars4"); | | X_6 := sql.bind(X_2,"sys","mtcars4","gears",0); | | (X_9,r1_9) := sql.bind(X_2,"sys","mtcars4","gears",2); | | X_12 := sql.bind(X_2,"sys","mtcars4","gears",1); | | X_14 := sql.delta(X_6,X_9,r1_9,X_12); | | X_15 := algebra.leftfetchjoin(X_3,X_14); | | X_16 := algebra.selectNotNil(X_15); | | X_17 := aggr.median(X_16); | | exit X_34; | | sql.exportValue(1,"sys.mtcars4","L1","bigint",53,0,6,X_17,""); | | end s2_2; | +--------------------------------------------------------------------+ 15 tuples (2.267ms) sql>explain select prod( gears ) from mtcars4; +--------------------------------------------------------------------+ | mal | +====================================================================+ | function user.s3_2{autoCommit=true}():void; | | X_17:lng := nil:lng; | | barrier X_34 := language.dataflow(); | | X_2 := sql.mvc(); | | X_3:bat[:oid,:oid] := sql.tid(X_2,"sys","mtcars4"); | | X_6 := sql.bind(X_2,"sys","mtcars4","gears",0); | | (X_9,r1_9) := sql.bind(X_2,"sys","mtcars4","gears",2); | | X_12 := sql.bind(X_2,"sys","mtcars4","gears",1); | | X_14 := sql.delta(X_6,X_9,r1_9,X_12); | | X_15 := algebra.leftfetchjoin(X_3,X_14); | | X_16 := algebra.selectNotNil(X_15); | | X_17:lng := aggr.prod(X_16); | | exit X_34; | | sql.exportValue(1,"sys.mtcars4","L1","bigint",53,0,6,X_17,""); | | end s3_2; | +--------------------------------------------------------------------+ 15 tuples (2.173ms) sql>trace select prod( gears ) from mtcars4; +------+ | L1 | +======+ | 0 | +------+ 1 tuple (38.356ms) +------+----------------------------------------------------------------------+ | tick | stmt | : s : : +======+======================================================================+ | 1 | X_17:lng := nil:lng; | | 3 | X_2 := sql.mvc(); | | 15 | X_3:bat[:oid,:oid] =[6] := sql.tid(X_2=0,"sys","mtcars4"); | | 16 | X_12=[0] := sql.bind(X_2=0,"sys","mtcars4","gears",1); | | 15 | (X_9=[0],r1_9=[0]) := sql.bind(X_2=0,"sys","mtcars4" | : : ,"gears",2); : | 5 | X_6=[6] := sql.bind(X_2=0,"sys","mtcars4","gears",0); | | 4 | X_14=[6] := sql.delta(X_6=[6],X_9=[0],r1 | : : _9=[0],X_12=[0]); : | 16 | X_15=[6] := algebra.leftfetchjoin(X_3=:bat[:oid, | : : :oid][6],X_14=[6]); : | 5 | X_16=[6] := algebra.selectNotNil(X_15=[6]); | | 25 | X_17:lng := aggr.prod(X_16=[6]); | | 877 | barrier X_34 := language.dataflow(); | | 7 | sql.exportValue(1,"sys.mtcars4","L1","bigint",53,0,6,X_17=0:lng,""); | | 2 | end s3_2; | | 1073 | X_5:void := user.s3_2(); | +------+----------------------------------------------------------------------+ 14 tuples (43.881ms) sql>trace select median( gears ) from mtcars4; +------+ | L1 | +======+ | 3 | +------+ 1 tuple (5.458ms) +------+----------------------------------------------------------------------+ | tick | stmt | : s : : +======+======================================================================+ | 2 | X_17 := nil:lng; | | 5 | X_2 := sql.mvc(); | | 72 | X_3:bat[:oid,:oid] =[6] := sql.tid(X_2=0,"sys","mtcars4"); | | 19 | X_12=[0] := sql.bind(X_2=0,"sys","mtcars4","gears",1); | | 19 | (X_9=[0],r1_9=[0]) := sql.bind(X_2=0,"sys","mtcars4" | : : ,"gears",2); : | 10 | X_6=[6] := sql.bind(X_2=0,"sys","mtcars4","gears",0); | | 7 | X_14=[6] := sql.delta(X_6=[6],X_9=[0],r1 | : : _9=[0],X_12=[0]); : | 28 | X_15=[6] := algebra.leftfetchjoin(X_3=:bat[:oid, | : : :oid][6],X_14=[6]); : | 12 | X_16=[6] := algebra.selectNotNil(X_15=[6]); | | 63 | bn=[1] := aggr.submedian(b=[6],false); | | 7 | return X_4 := algebra.fetch(bn=[1],0); | | 145 | X_17 := aggr.median(X_16=[6]); | | 1280 | barrier X_34 := language.dataflow(); | | 11 | sql.exportValue(1,"sys.mtcars4","L1","bigint",53,0,6,X_17=3:lng,""); | | 3 | end s2_2; | | 1614 | X_5:void := user.s2_2(); | +------+----------------------------------------------------------------------+ 16 tuples (16.329ms) sql> On Wed, Apr 24, 2013 at 5:00 PM, Anthony Damico wrote:

I wrote an R script (for Windows) that created some dummy data and reproduced *three* of the five errors. median and prod still do not work in my confidential data. sign, stddev, and stddev_pop do not work when using the R script pasted below. I created this the same way as before:

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

The starting data set I used is the `mtcars` data.frame that you can view if you open up R and type `mtcars`.. but I've pasted it below so you can see it's nothing special.

Here's the three EXPLAIN commands (run in mclient).. below that, I've pasted R code to precisely re-create the database as I have it on my computer. :)

When I add a *1 on the end of the count( case when gear in ( 3 , 4 ) then mpg end )*1 clause, I get the same errors..

Thanks!!!!

sql>explain select sign( gears ) from mtcars2; TypeException:user.s1_2[16]:'bat.insert' undefined in: _53:any := bat.insert(_43 :bat[:oid,:int], _51:oid, _50:bte) +-------------------------------------------------------------+ | mal | +=============================================================+ | function user.s1_2{autoCommit=true}():void; | | X_15 := nil:bat[:oid,:wrd]; | | barrier X_36 := language.dataflow(); | | X_2 := sql.mvc(); | | X_3:bat[:oid,:oid] := sql.tid(X_2,"sys","mtcars2"); | | X_6 := sql.bind(X_2,"sys","mtcars2","gears",0); | | (X_9,r1_9) := sql.bind(X_2,"sys","mtcars2","gears",2); | | X_12 := sql.bind(X_2,"sys","mtcars2","gears",1); | | X_14 := sql.delta(X_6,X_9,r1_9,X_12); | | X_15 := algebra.leftfetchjoin(X_3,X_14); | | exit X_36; | | X_41 := bat.reverse(X_15); | | X_43 := bat.new(nil:oid,nil:int); | | barrier (X_47,X_48) := iterator.new(X_15); | | X_50 := calc.sign(X_48); | | X_51 := algebra.fetch(X_41,X_47); | | bat.insert(X_43,X_51,X_50); | | redo (X_47,X_48) := iterator.next(X_15); | | exit (X_47,X_48); | | X_16:bat[:oid,:int] := X_43; | | X_20 := sql.resultSet(1,1,X_16); | | sql.rsColumn(X_20,"sys.","sign_gears","int",32,0,X_16); | | X_25 := io.stdout(); | | sql.exportResult(X_25,X_20); | | end s1_2; | +-------------------------------------------------------------+ 25 tuples (2.451ms) sql>explain select stddev( gears ) from mtcars2;

SELECT: no such unary operator 'stddev(wrd)' sql>explain select stddev_pop( gears ) from mtcars2;

SELECT: no such unary operator 'stddev_pop(wrd)' sql>

============================ R code to reproduce mtcars2 in Windows (shouldn't require much of a modification to get it working on other OSes) ============================

install.packages("MonetDB.R")

require(MonetDB.R) # load the MonetDB.R package (connects r to a monet database)

batfile <- monetdb.server.setup(

# set the path to the directory where the initialization batch file and all data will be stored database.directory = "C:/My Directory/MonetDB" , # must be empty or not exist

# find the main path to the monetdb installation program monetdb.program.path = "C:/Program Files/MonetDB/MonetDB5" ,

# choose a database name dbname = "test" ,

# choose a database port # this port should not conflict with other monetdb databases # on your local computer. two databases with the same port number # cannot be accessed at the same time dbport = 50000 )

dbname <- "test" dbport <- 50000

# run the MonetDB server pid <- monetdb.server.start( batfile )

# connect to the MonetDB server monet.url <- paste0( "monetdb://localhost:" , dbport , "/" , dbname ) db <- dbConnect( MonetDB.R() , monet.url )

# write the example mtcars data.frame to monetdb dbWriteTable( db , 'mtcars' , mtcars )

# create a new table the same way as before dbSendUpdate( db , 'create table mtcars2 as select carb , count( case when gear in ( 3 , 4 ) then mpg end ) as gears from mtcars group by carb with data' )

# this works now, still doesn't work in my confidential data dbGetQuery( db , 'select median( gears ) from mtcars2' ) dbGetQuery( db , 'select prod( gears ) from mtcars2' )

# this *does not* work dbGetQuery( db , 'select sign( gears ) from mtcars2' ) dbGetQuery( db , 'select stddev( gears ) from mtcars2' ) dbGetQuery( db , 'select stddev_pop( gears ) from mtcars2' )

============================ here's the entire mtcars table ============================

mpg cyl disp hp drat wt qsec vs am gear carb Mazda RX4 21.0 6 160.0 110 3.90 2.620 16.46 0 1 4 4 Mazda RX4 Wag 21.0 6 160.0 110 3.90 2.875 17.02 0 1 4 4 Datsun 710 22.8 4 108.0 93 3.85 2.320 18.61 1 1 4 1 Hornet 4 Drive 21.4 6 258.0 110 3.08 3.215 19.44 1 0 3 1 Hornet Sportabout 18.7 8 360.0 175 3.15 3.440 17.02 0 0 3 2 Valiant 18.1 6 225.0 105 2.76 3.460 20.22 1 0 3 1 Duster 360 14.3 8 360.0 245 3.21 3.570 15.84 0 0 3 4 Merc 240D 24.4 4 146.7 62 3.69 3.190 20.00 1 0 4 2 Merc 230 22.8 4 140.8 95 3.92 3.150 22.90 1 0 4 2 Merc 280 19.2 6 167.6 123 3.92 3.440 18.30 1 0 4 4 Merc 280C 17.8 6 167.6 123 3.92 3.440 18.90 1 0 4 4 Merc 450SE 16.4 8 275.8 180 3.07 4.070 17.40 0 0 3 3 Merc 450SL 17.3 8 275.8 180 3.07 3.730 17.60 0 0 3 3 Merc 450SLC 15.2 8 275.8 180 3.07 3.780 18.00 0 0 3 3 Cadillac Fleetwood 10.4 8 472.0 205 2.93 5.250 17.98 0 0 3 4 Lincoln Continental 10.4 8 460.0 215 3.00 5.424 17.82 0 0 3 4 Chrysler Imperial 14.7 8 440.0 230 3.23 5.345 17.42 0 0 3 4 Fiat 128 32.4 4 78.7 66 4.08 2.200 19.47 1 1 4 1 Honda Civic 30.4 4 75.7 52 4.93 1.615 18.52 1 1 4 2 Toyota Corolla 33.9 4 71.1 65 4.22 1.835 19.90 1 1 4 1 Toyota Corona 21.5 4 120.1 97 3.70 2.465 20.01 1 0 3 1 Dodge Challenger 15.5 8 318.0 150 2.76 3.520 16.87 0 0 3 2 AMC Javelin 15.2 8 304.0 150 3.15 3.435 17.30 0 0 3 2 Camaro Z28 13.3 8 350.0 245 3.73 3.840 15.41 0 0 3 4 Pontiac Firebird 19.2 8 400.0 175 3.08 3.845 17.05 0 0 3 2 Fiat X1-9 27.3 4 79.0 66 4.08 1.935 18.90 1 1 4 1 Porsche 914-2 26.0 4 120.3 91 4.43 2.140 16.70 0 1 5 2 Lotus Europa 30.4 4 95.1 113 3.77 1.513 16.90 1 1 5 2 Ford Pantera L 15.8 8 351.0 264 4.22 3.170 14.50 0 1 5 4 Ferrari Dino 19.7 6 145.0 175 3.62 2.770 15.50 0 1 5 6 Maserati Bora 15.0 8 301.0 335 3.54 3.570 14.60 0 1 5 8 Volvo 142E 21.4 4 121.0 109 4.11 2.780 18.60 1 1 4 2

On Tue, Apr 23, 2013 at 3:34 PM, Anthony Damico wrote:

...

...

Am I right that this clause is embedded in a SQL statement like

create table ... as select ... <clause> ... from <table> with data; where <table> has columns UNIQUE_ROW_IDENTIFIER and MY_CODE ?

Yes, precisely. :)