May 2016 - users-list

embedded R function not working
by Stefano Fioravanzo 30 May '16

30 May '16

Hello, I have the following query: CREATE TABLE mytable AS ( SELECT <fields….> FROM table1 AS a, my_R_function(a.field1, ...) AS asd WHERE <some conditions> ) WITH DATA; MonetDB says: SELECT: no such column ‘a.field1’ The issue is in how I call the R function, since when I remove it everything’s just fine. My aim is to call the function for each row of table1. The new table will have a couple of fields from table1 and a bunch of other fields computed from the R function. How am I supposed to do that? Thank you, Stefano

2 1

MonetDB and KNIME compatibility
by Roman S 26 May '16

26 May '16

Hello. Maybe someone have experience with this kind of problem: I’ve downloaded drivers from http://dev.monetdb.org/downloads/Java/Jul2015-SP4/ <http://dev.monetdb.org/downloads/Java/Jul2015-SP4/> added them to KNIME and now, when i’m trying to make something more difficult than just counting rows in table, I see an error: ERROR Database Reader 0:3 SQL Exception reading Object of type "2005": Operation getCharacterStream() currently not supported - all further errors are suppressed and reported on debug level only ERROR Database Reader 0:3 Execute failed: Cell at index 1 is null!

1 0

storing -1 in a tinyint
by Lynn Carol Johnson 25 May '16

25 May '16

Hi all - I apologize for all the emails, but I am banging on monetdb a lot lately. Today I've run into a problem storing a -1 in a "tinyint" column. My java code writes bytes to a binary file. This file will be loaded via binary bulk load into a "tinyint" column. The values in this column range from -1 to 3. I am writing the negative value to the byte file as below: byte missingData = (byte)-1; writer.writeByte(missingData); The data loads correctly into monetdb, and I can print it: sql>\d minusonetest CREATE TABLE "sys"."minusonetest" ( "chr" INTEGER, "pos" INTEGER, "coelorachis" TINYINT ); sql> sql>select * from minusonetest where chr=2 limit 10; +------+------+-------------+ | chr | pos | coelorachis | +======+======+=============+ | 2 | 1 | -1 | | 2 | 2 | -1 | | 2 | 3 | -1 | | 2 | 4 | -1 | | 2 | 5 | -1 | | 2 | 6 | -1 | | 2 | 7 | -1 | | 2 | 8 | -1 | | 2 | 9 | -1 | | 2 | 10 | -1 | +------+------+-------------+ 10 tuples (10.7s) sql> BUT, when I run the select statement below, it tosses me out of the DB and prints errors to the log file: select * from minusone test where chr=2 and coelorachis > -1; The select fails for any value in the "coelorachis > x" clause above (i.e., > -1, > 0, > 1, etc) If I run the above select and have NOT written any negative numbers to the column, all is good. It only fails if there are negative numbers stored in the tinyint column. The merovingian.log file has these messages: 2016-05-25 12:47:23 ERR testDB[21730]: *** Error in `/usr/bin/mserver5': free(): invalid next size (fast): 0x00007fb0ac003920 *** 2016-05-25 12:47:23 ERR testDB[21730]: ======= Backtrace: ========= 2016-05-25 12:47:23 ERR testDB[21730]: /lib64/libc.so.6(+0x7d023)[0x7fb0df3bc023] 2016-05-25 12:47:23 ERR testDB[21730]: /lib64/libbat.so.12(GDKfree+0x13)[0x7fb0e1b21233] 2016-05-25 12:47:23 ERR testDB[21730]: /lib64/libbat.so.12(BATsubunique+0x18a9)[0x7fb0e1d23f69] 2016-05-25 12:47:23 ERR testDB[21730]: /lib64/libbat.so.12(BATsubunique+0xbe7)[0x7fb0e1d232a7] 2016-05-25 12:47:23 ERR testDB[21730]: /lib64/libbat.so.12(BATimprints+0x581)[0x7fb0e1cef791] 2016-05-25 12:47:23 ERR testDB[21730]: /lib64/libbat.so.12(BATsubselect+0x4ecf)[0x7fb0e1ac532f] 2016-05-25 12:47:23 ERR testDB[21730]: /lib64/libbat.so.12(BATthetasubselect+0x14b)[0x7fb0e1ac78eb] 2016-05-25 12:47:23 ERR testDB[21730]: /lib64/libmonetdb5.so.19(ALGthetasubselect2+0x1c4)[0x7fb0e210db64] 2016-05-25 12:47:23 ERR testDB[21730]: /lib64/libmonetdb5.so.19(runMALsequence+0xdeb)[0x7fb0e20ac55b] 2016-05-25 12:47:23 ERR testDB[21730]: /lib64/libmonetdb5.so.19(+0x45e5b)[0x7fb0e20ade5b] 2016-05-25 12:47:23 ERR testDB[21730]: /lib64/libpthread.so.0(+0x7dc5)[0x7fb0df707dc5] 2016-05-25 12:47:23 ERR testDB[21730]: /lib64/libc.so.6(clone+0x6d)[0x7fb0df43528d] 2016-05-25 12:47:23 ERR testDB[21730]: ======= Memory map: ======== 2016-05-25 12:47:23 ERR testDB[21730]: 00400000-00405000 r-xp 00000000 08:03 22036485 /usr/bin/mserver5 2016-05-25 12:47:23 ERR testDB[21730]: 00604000-00605000 r--p 00004000 08:03 22036485 /usr/bin/mserver5 2016-05-25 12:47:23 ERR testDB[21730]: 00605000-00606000 rw-p 00005000 08:03 22036485 /usr/bin/mserver5 2016-05-25 12:47:23 ERR testDB[21730]: 00606000-00608000 rw-p 00000000 00:00 0 2016-05-25 12:47:23 ERR testDB[21730]: 00892000-01e3e000 rw-p 00000000 00:00 0 [heap] 2016-05-25 12:47:23 ERR testDB[21730]: 7fabab28a000-7fabab29f000 r-xp 00000000 08:03 22020111 /usr/lib64/libgcc_s-4.8.3-20140911.so.1 2016-05-25 12:47:23 ERR testDB[21730]: 7fabab29f000-7fabab49e000 ---p 00015000 08:03 22020111 /usr/lib64/libgcc_s-4.8.3-20140911.so.1 2016-05-25 12:47:23 ERR testDB[21730]: 7fabab49e000-7fabab49f000 r--p 00014000 08:03 22020111 /usr/lib64/libgcc_s-4.8.3-20140911.so.1 2016-05-25 12:47:23 ERR testDB[21730]: 7fabab49f000-7fabab4a0000 rw-p 00015000 08:03 22020111 /usr/lib64/libgcc_s-4.8.3-20140911.so.1 Any ideas? There is no problem if I use Byte.MIN_VALUE instead of -1. This translates to NULL and is selectable. Lynn

1 1

I WAS WRONG ! Re: additional details on IS NULL problem
by Lynn Carol Johnson 24 May '16

24 May '16

After all this, it seems I was doing something stupid. I had originally used Float.MIN_VALUE, which is the smallest POSITIVE minimum value. I also tried hard coding -3.40282e+38, but this had issues as Stephan pointed out. What I neglected to try, and what works for float is public static float flt_nil = -Float.MAX_VALUE; Sorry for all the confusion, but I learned something. Null values for float types DO work. Thanks for your patience and help - Lynn On 5/24/16, 10:44 AM, "users-list on behalf of Lynn Carol Johnson" <users-list-bounces+lcj34=cornell.edu(a)monetdb.org on behalf of lcj34(a)cornell.edu> wrote: >Yes, we’re using float as 32 bit, storing into a real. > >Still no sunshine with your suggestions below. Java doesn’t allow me to >assign > public static float flt_nil = (float) >-340282346638528859811704183484516925440; >It complains “the literal -340282346638528859811704183484516925440 of type >int is out of range”. This isn’t surprising as 32 bits will only hold up >to -2^31 (-2,147,483,648) > >Using static float flt_nil = (float) 0xff7fffff; gives me the value >-8388609.0, which is incorrect. > >Does storing null work for you using “float” types? Is this just a >java-to-C issue? Still looks like we’ll need to pick a default other than >NULL for our “real” data. > >Thanks - Lynn > > >On 5/24/16, 9:53 AM, "users-list on behalf of Stefan Manegold" ><users-list-bounces+lcj34=cornell.edu(a)monetdb.org on behalf of >Stefan.Manegold(a)cwi.nl> wrote: > >>Hi Lynn, >> >>public static float flt_nil = (float) -3.40282e+38; >> >>does not necessarily create the exact bit pattern >>as C macro -FLT_MAX, mainly as "-3.40282e+38" is only an >>approximation of the exact value: >>"-340282346638528859811704183484516925440" >> >>You can either try >> >>public static float flt_nil = (float) >>-340282346638528859811704183484516925440; >> >>or use the actual bit pattern in hex notation, e.g., >> >>public static float flt_nil = (float) 0xff7fffff; >> >>Please take my Java(?) code snippets with a grain of salt; >>I'm everything but a Java expert --- or better, I'm a Java >>illiterate ... >> >>Be also aware that >>C type float, MAL typr :flt and SQL type REAL are 32-bit, >>while >>C type double, MAL type :dbl, and SQL types FLOAT & DOUBLE are 64-bit >> >>Hence, for 64-bit types you'd need to use one of >> >>public static double dbl_nil = (double) >>-179769313486231570814527423731704356798070567525844996598917476803157260 >>7 >>8002853876058955863276687817154045895351438246423432132688946418276846754 >>6 >>7035375169860499105765512820762454900903893289440758685084551339423045832 >>3 >>6903222948165808559332123348274797826204144723168738177180919299881250404 >>0 >>26184124858368; >> >>public static double dbl_nil = (double) 0xffefffffffffffff; >> >> >>Hope this helps ... >> >>Best, >>Stefan >> >>----- On May 24, 2016, at 3:42 PM, Lynn Carol Johnson lcj34(a)cornell.edu >>wrote: >> >>> Thanks, Stephan. >>> >>> Short response good news: using int_nil = ‹2147483648 (-2^31) is >>> translated properly to NULL for int values. The IS NULL and IS NOT >>>NULL >>> queries are fine. >>> >>> Short response bad news: I am unable to get NULL to work for Float >>> regardless of what value I used. Details below: >>> >>> I replaced my Float.MIN_VALUE with flt_nil defined as below : >>> >>> public static float flt_nil = (float) -3.40282e+38; >>> >>> Ran again with just 50 rows (created as binary files). At this point >>>my >>> belief is NULL does not work at all for float with binary files. There >>>are >>> 16 of the >>> 50 values for column amesbyd_weir that are null, but I can only select >>> those values if I check for the column < -3.40282e+38. Here are the >>>sql >>> results: >>> >>> sql>select count(amesbyd_weir) from weirtestnull; >>> +------+ >>>| L1 | >>> +======+ >>>| 50 | >>> +------+ >>> 1 tuple (1.933ms) >>> sql>select chr,pos,amesbyd_weir from weirtestnull where amesbyd_weir IS >>> NULL; >>> +-----+-----+--------------+ >>>| chr | pos | amesbyd_weir | >>> +=====+=====+==============+ >>> +-----+-----+--------------+ >>> 0 tuples (1.291ms) >>> sql>select chr,pos,amesbyd_weir from weirtestnull where amesbyd_weir = >>> -3.40282e+38; >>> +-----+-----+--------------+ >>>| chr | pos | amesbyd_weir | >>> +=====+=====+==============+ >>> +-----+-----+--------------+ >>> 0 tuples (0.528ms) >>> sql> >>> sql>select chr,pos,amesbyd_weir from weirtestnull where amesbyd_weir < >>> -3.40282e+38; >>> +------+--------+-----------------+ >>>| chr | pos | amesbyd_weir | >>> +======+========+=================+ >>>| 10 | 228864 | -3.40282e+38 | >>>| 10 | 228962 | -3.40282e+38 | >>>| 10 | 228972 | -3.40282e+38 | >>>| 10 | 229011 | -3.40282e+38 | >>>| 10 | 229025 | -3.40282e+38 | >>>| 10 | 229187 | -3.40282e+38 | >>>| 10 | 229367 | -3.40282e+38 | >>>| 10 | 229405 | -3.40282e+38 | >>>| 10 | 229444 | -3.40282e+38 | >>>| 10 | 229593 | -3.40282e+38 | >>>| 10 | 229615 | -3.40282e+38 | >>>| 10 | 229671 | -3.40282e+38 | >>>| 10 | 229829 | -3.40282e+38 | >>>| 10 | 230000 | -3.40282e+38 | >>>| 10 | 230052 | -3.40282e+38 | >>>| 10 | 230120 | -3.40282e+38 | >>> +------+--------+-----------------+ >>> 16 tuples (2.999ms) >>> sql> >>> >>> >>> NOTE: Dumping the 50 row database to a .txt file, dropping the table >>>and >>> reloading into monetdb did NOT fix it as it did when I used Float.NaN >>> values. That is actually irrelevant for us as our db tables are large >>> (2.1 Billion rows in 1 table, 80M in another) and as we add additional >>> columns, binary loading is the only way to go. We love the binary load >>> option, and have been very happy with the speed of monetdb processing. >>> >>> For now, we¹re going to follow PLINK convention and use -99 for our >>>Float >>> null values. >>> >>> Thanks - Lynn >>> >>> >>> On 5/23/16, 4:06 PM, "users-list on behalf of Stefan Manegold" >>> <users-list-bounces+lcj34=cornell.edu(a)monetdb.org on behalf of >>> Stefan.Manegold(a)cwi.nl> wrote: >>> >>>>Hi Lynn, >>>> >>>>thanks for diving into this! >>>> >>>>The information that you loaded your data using from binary files using >>>>copy *binary* into is crucial for us to understand and investigate the >>>>problem! >>>> >>>>In fact, I recently discovered and fixed a similar bug; cf., >>>>https://www.monetdb.org/bugzilla/show_bug.cgi?id=3937 >>>> >>>>This fix is in the Jul2013-SP3 bug-fix release. >>>>Hence, I'm wondering why you (still) experience this (or a similar) >>>>problem. >>>> >>>>Of what type are your data / columns? >>>>Integer? decimal? floating point? string? >>>> >>>>If more then one type, do you experience these problems with all types >>>>or just with some? >>>>If only some, which? >>>> >>>>If the problem occurs only with floating point numbers, >>>>are you sure you're using the exact NULL representation that MonetDB >>>>uses? >>>> >>>>Otherwise a potential source of the problem --- I'm just speculating >>>>--- >>>>could be the following: >>>> >>>>You might have "non-valid" floating point numbers like NaN or INF in >>>>your >>>>data >>>>that are not known to SQL and hence MonetDB. >>>>If so, >>>>When parsed from text, MonetDB does (seems to?) silently turn them into >>>>(MonetDB's >>>>internal representation of) NULL. >>>>However, when loding your binary data, MonetDB stores these values >>>>"as-is" >>>>not recognizing them as NULL; then processing does not recognize them >>>>as >>>>NULL, either; >>>>only rendering produces NULL from the "non-valid" internal >>>>representation. >>>> >>>>For the correct internal representation of NULL values in MonetDB per >>>>type see >>>>https://www.monetdb.org/wiki/MonetDB_type_system >>>> >>>>If my speculation is true, we'd need to consider making our copy binary >>>>into >>>>bulk-loading for floating point numbers (more) fail-save by actually >>>>scanning >>>>each column and checking for "invalid" floating point number --- >>>>whatever >>>>that >>>>might cost ... >>>> >>>>Best, >>>>Stefan >>>> >>>>----- On May 23, 2016, at 8:37 PM, Lynn Carol Johnson lcj34(a)cornell.edu >>>>wrote: >>>> >>>>> Hi Anthony - >>>>> >>>>> I¹ve narrowed down the problem. The bug isn¹t related to size, but >>>>>rather how >>>>> the table data was created/loaded. Loading from a .txt file gives >>>>>good >>>>> behavior, loading from binary does not. >>>>> >>>>> Normally I create binary files for each column and use the binary >>>>>bulk >>>>>loader to >>>>> load. That is what was failing. When I first tested with 50 columns, >>>>>I >>>>>created >>>>> the test file from sql via the command >>>>> copy (select * Š) into Œ/workdir/ŠkellyFirst50.txt using delimiters >>>>>Œ\t¹,¹\n¹; >>>>> >>>>> Then I created a new table called kellyFirst50 and used the ³COPY >>>>>INTO >>>>> kellyfirst50 from Œ/workdirŠkellyFirst50.txt¹ using delimiters >>>>>Œ\t,¹\n¹; ² >>>>> command. Checking for ³IS NULL² worked, so I thought it was a size >>>>>problem. >>>>> >>>>> But, when trying to figure out at what point the file size mattered, >>>>>I >>>>>ran java >>>>> scripts to create binary files of ever decreasing sizes, then loaded >>>>>them via >>>>> the COPY BINARY commands. Checking for IS NULL always failed here, >>>>>even >>>>>when I >>>>> only loaded 50 values. >>>>> >>>>> Here are the results when loading from binary files into monetdb. >>>>>Using >>>>>IS NOT >>>>> NULL works, but IS NULL does not: >>>>> >>>>> >>>>> >>>>> sql>\</workdir/lcj34/monetdbFiles/isNull_test/create_weirFSTTest.sql >>>>> >>>>> operation successful >>>>> >>>>> sql>\</workdir/lcj34/monetdbFiles/isNull_test/copy_weirFSTTest.sql >>>>> >>>>> 50 affected rows >>>>> >>>>> >>>>> >>>>> sql> >>>>> >>>>> >>>>> >>>>> >>>>> sql>select count(*) from weirtestnull; >>>>> >>>>> +------+ >>>>> >>>>>| L1 | >>>>> >>>>> +======+ >>>>> >>>>>| 50 | >>>>> >>>>> +------+ >>>>> >>>>> 1 tuple (2.014ms) >>>>> >>>>> sql>select count(amesbyd_weir) from weirtestnull; >>>>> >>>>> +------+ >>>>> >>>>>| L1 | >>>>> >>>>> +======+ >>>>> >>>>>| 50 | >>>>> >>>>> +------+ >>>>> >>>>> >>>>> >>>>> 1 tuple (0.865ms) >>>>> >>>>> sql>select count(amesbyd_weir) from weirtestnull where amesbyd_weir >>>>>IS >>>>>NOT NULL; >>>>> >>>>> +------+ >>>>> >>>>>| L1 | >>>>> >>>>> +======+ >>>>> >>>>>| 34 | >>>>> >>>>> +------+ >>>>> >>>>> 1 tuple (3.005ms) >>>>> >>>>> >>>>> >>>>> sql> >>>>> >>>>> sql>select * from weirtestnull where amesbyd_weir IS NULL; >>>>> >>>>> >>>>>+-----+-----+---------------+--------------+--------------+----------- >>>>>- >>>>>-- >>>>>--+--------------------+------------------+----------------+ >>>>> >>>>>| chr | pos | amesbycn_weir | amesbyd_weir | amesbyf_weir | >>>>>amesbynam_weir | >>>>>| amesbyturkeyp_weir | dentbyflint_weir | dentbynam_weir |> >>>>> >>>>> >>>>>+=====+=====+===============+==============+==============+=========== >>>>>= >>>>>== >>>>>==+====================+==================+================+ >>>>> >>>>> >>>>>+-----+-----+---------------+--------------+--------------+----------- >>>>>- >>>>>-- >>>>>--+--------------------+------------------+----------------+ >>>>> >>>>> 0 tuples (4.041ms) !4 columns dropped! >>>>> >>>>> >>>>> >>>>> sql> >>>>> >>>>> >>>>> Dump the data into a .txt file: >>>>> >>>>> >>>>> >>>>> sql>copy (select * from weirtestnull) into >>>>> '/workdir/db_outfiles/weirtestnull.txt' using delimiters '\t','\n'; >>>>> >>>>> 50 affected rows (3.809ms) >>>>> >>>>> sql> >>>>> >>>>> Now drop the table, recreate it, but this time load from the txt file >>>>>created >>>>> above: >>>>> >>>>> >>>>> >>>>> sql>drop table weirtestnull; >>>>> >>>>> operation successful (1.948ms) >>>>> >>>>> sql>\</workdir/lcj34/monetdbFiles/isNull_test/create_weirFSTTest.sql >>>>> >>>>> operation successful >>>>> >>>>> sql>copy into weirtestnull from >>>>>'/workdir/db_outfiles/weirtestnull.txt' >>>>>using >>>>> delimiters '\t','\n'; >>>>> >>>>> 50 affected rows (57.643ms) >>>>> >>>>> sql> >>>>> >>>>> >>>>> Re-run the commands I now get correct values for IS NULL: >>>>> >>>>> >>>>> >>>>> sql>select count(*) from weirtestnull; >>>>> >>>>> +------+ >>>>> >>>>>| L1 | >>>>> >>>>> +======+ >>>>> >>>>>| 50 | >>>>> >>>>> +------+ >>>>> >>>>> 1 tuple (1.851ms) >>>>> >>>>> sql>select count(amesbyd_weir) from weirtestnull; >>>>> >>>>> +------+ >>>>> >>>>>| L1 | >>>>> >>>>> +======+ >>>>> >>>>>| 34 | >>>>> >>>>> +------+ >>>>> >>>>> 1 tuple (0.983ms) >>>>> >>>>> sql> >>>>> >>>>> sql>select * from weirtestnull where amesbyd_weir IS NULL; >>>>> >>>>> >>>>>+------+--------+------------+------------+------------+------------+- >>>>>- >>>>>-- >>>>>---------+-------------+-------------+-------------+-------------+---- >>>>>- >>>>>-- >>>>>------+ >>>>> >>>>>| chr | pos | amesbycn_w | amesbyd_we | amesbyf_we | amesbynam_ | >>>>>amesbyturke | >>>>>| dentbyflint | dentbynam_w | dentbyturke | flintbynam_ | flintbyturk >>>>>|> >>>>> >>>>> : : : eir : ir : ir : weir : yp_weir : _weir : eir : ypen_weir : weir >>>>>: >>>>> eypen_weir :> >>>>> >>>>> >>>>>+======+========+============+============+============+============+= >>>>>= >>>>>== >>>>>=========+=============+=============+=============+=============+==== >>>>>= >>>>>== >>>>>======+ >>>>> >>>>>| 10 | 228864 | 2.95496e-0 | null | null | -0.0001263 | null | null | >>>>>-0.00028749 >>>>>| | null | -0.00029885 | null | >>>>> >>>>> : : : 5 : : : 25 : : : 4 : : 1 : : >>>>> >>>>>| 10 | 228962 | null | null | null | null | null | null | null | null >>>>>| >>>>>null | >>>>>| null | >>>>> >>>>>| 10 | 228972 | null | null | null | null | null | null | null | null >>>>>| >>>>>null | >>>>>| null | >>>>> >>>>>| 10 | 229011 | null | null | null | null | null | null | null | null >>>>>| >>>>>null | >>>>>| null | >>>>> >>>>>| 10 | 229025 | null | null | null | null | null | null | null | null >>>>>| >>>>>null | >>>>>| null | >>>>> >>>>>| 10 | 229187 | null | null | null | null | null | null | null | null >>>>>| >>>>>null | >>>>>| null | >>>>> >>>>>| 10 | 229367 | null | null | null | null | null | null | null | null >>>>>| >>>>>null | >>>>>| null | >>>>> >>>>>| 10 | 229405 | null | null | null | null | null | null | null | null >>>>>| >>>>>null | >>>>>| null | >>>>> >>>>>| 10 | 229444 | null | null | null | null | null | null | null | null >>>>>| >>>>>null | >>>>>| null | >>>>> >>>>>| 10 | 229593 | null | null | null | null | null | null | null | null >>>>>| >>>>>null | >>>>>| null | >>>>> >>>>>| 10 | 229615 | null | null | null | null | null | null | null | null >>>>>| >>>>>null | >>>>>| null | >>>>> >>>>>| 10 | 229671 | null | null | null | null | null | null | null | null >>>>>| >>>>>null | >>>>>| null | >>>>> >>>>>| 10 | 229829 | null | null | null | null | null | null | null | null >>>>>| >>>>>null | >>>>>| null | >>>>> >>>>>| 10 | 230000 | null | null | null | null | null | null | null | null >>>>>| >>>>>null | >>>>>| null | >>>>> >>>>>| 10 | 230052 | null | null | null | null | null | null | null | null >>>>>| >>>>>null | >>>>>| null | >>>>> >>>>>| 10 | 230120 | null | null | null | null | null | null | null | null >>>>>| >>>>>null | >>>>>| null | >>>>> >>>>> >>>>>+------+--------+------------+------------+------------+------------+- >>>>>- >>>>>-- >>>>>---------+-------------+-------------+-------------+-------------+---- >>>>>- >>>>>-- >>>>>------+ >>>>> >>>>> >>>>> >>>>> 16 tuples (5.2 >>>>> >>>>> >>>>> My java program uses Float.NaN and Integer.MIN_VALUE, Byte.MIN_VALUE >>>>>to >>>>>get >>>>> ³null² stored in the db. >>>>> >>>>> When we load directly from binary, are the null values created >>>>>differently? Is >>>>> something not marked that gets marked when loading from text files? >>>>> >>>>> With this information, can you reproduce, or do you want me to give >>>>>you >>>>>my >>>>> binary files in a dropbox somewhere? >>>>> >>>>> Thanks - Lynn >>>>> >>>>> >>>>> _______________________________________________ >>>>> users-list mailing list >>>>> users-list(a)monetdb.org >>>>> https://www.monetdb.org/mailman/listinfo/users-list >>>> >>>>-- >>>>| Stefan.Manegold(a)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(a)monetdb.org >>>>https://www.monetdb.org/mailman/listinfo/users-list >>> >>> _______________________________________________ >>> users-list mailing list >>> users-list(a)monetdb.org >>> https://www.monetdb.org/mailman/listinfo/users-list >> >>-- >>| Stefan.Manegold(a)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(a)monetdb.org >>https://www.monetdb.org/mailman/listinfo/users-list > >_______________________________________________ >users-list mailing list >users-list(a)monetdb.org >https://www.monetdb.org/mailman/listinfo/users-list

1 0

additional details on IS NULL problem
by Lynn Carol Johnson 24 May '16

24 May '16

2 7

issue with IS NULL
by Lynn Carol Johnson 23 May '16

23 May '16

Good morning - I am having an issue identifying columns with a null value in the July2015-SP3 monetdb version. A sample of my table with null values is below: sql>select * from kelly limit 10; +------+-------+------------+-------------+-------------+-------------+-------------+ | chr | pos | bkn22vsbkn | bkn22vsbkn3 | bkn22vsbkn4 | bkn22vstemp | bkn22vstrop |> : : : 16_weir_an : 5_weir_and_ : 0_weir_and_ : erate_cinta : ical_cinta_ :> : : : d_cockerha : cockerham_f : cockerham_f : _weir_and_c : weir_and_co :> : : : m_fst : st : st : ockerham_fs : ckerham_fst :> : : : : : : t : :> +======+=======+============+=============+=============+=============+=============+ | 1 | 10004 | null | null | null | null | null | | 1 | 10045 | 0.050576 | 0 | 0.154324 | 0.130628 | 0.0398957 | | 1 | 10077 | null | null | null | null | null | | 1 | 10084 | null | null | null | null | null | | 1 | 10097 | null | null | 0.0823631 | 0 | null | | 1 | 10112 | null | null | null | null | null | | 1 | 10128 | 0.00185251 | 0 | 0 | 0.00272515 | 0.00594128 | | 1 | 10162 | null | null | null | null | null | | 1 | 10270 | null | null | null | null | null | | 1 | 10299 | null | null | null | null | null | +------+-------+------------+-------------+-------------+-------------+-------------+ 10 tuples (18.9s) !144 columns dropped! note: to disable dropping columns and/or truncating fields use \w-1 sql> Verify the number of values in the table: sql>select count(1) from kelly; +----------+ | L1 | +==========+ | 83153144 | +----------+ 1 tuple (479.905ms) sql> I would like to pull all values that are NOT null from a specific column. My understanding is that running a “count” on a column should return the count of the non-null values. When I run a count on bkn22vsbkn40_weir_and_cockerham_fst I get the following result, which is the same value as the total number of entries above: sql>select count(bkn22vsbkn40_weir_and_cockerham_fst) from kelly; +----------+ | L1 | +==========+ | 83153144 | +----------+ 1 tuple (860.533ms) sql> The number returned is the same as the total number of entries, but it should be less. However, if I run a query to count the values that are NOT NULL, it counts them: sql>select count(1) from kelly where bkn22vsbkn40_weir_and_cockerham_fst IS NOT NULL; +----------+ | L1 | +==========+ | 41754484 | +----------+ 1 tuple (1.0s) sql> When I try to select column values based on my selected column having a null value, no results are returned. sql>select * from kelly where bkn22vsbkn40_weir_and_cockerham_fst IS NULL limit 10; +-----+-----+-------------------------------------+-------------------------------------+-------------------------------------+ | chr | pos | bkn22vsbkn16_weir_and_cockerham_fst | bkn22vsbkn35_weir_and_cockerham_fst | bkn22vsbkn40_weir_and_cockerham_fst |> +=====+=====+=====================================+=====================================+=====================================+ +-----+-----+-------------------------------------+-------------------------------------+-------------------------------------+ 0 tuples (1.1s) !146 columns dropped! sql> When I select column values based on my selected column value NOT null, I get values. sql>select * from kelly where bkn22vsbkn40_weir_and_cockerham_fst IS NOT NULL limit 10; +------+-------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+--------------+--------------+ | chr | pos | bkn22vsbkn1 | bkn22vsbkn3 | bkn22vsbkn4 | bkn22vstemp | bkn22vstrop | bkn33vsbkn1 | bkn33vsbkn2 | bkn33vsbkn35 | bkn33vsbkn40 |> : : : 6_weir_and_ : 5_weir_and_ : 0_weir_and_ : erate_cinta : ical_cinta_ : 6_weir_and_ : 2_weir_and_ : _weir_and_co : _weir_and_co :> : : : cockerham_f : cockerham_f : cockerham_f : _weir_and_c : weir_and_co : cockerham_f : cockerham_f : ckerham_fst : ckerham_fst :> : : : st : st : st : ockerham_fs : ckerham_fst : st : st : : :> : : : : : : t : : : : : :> +======+=======+=============+=============+=============+=============+=============+=============+=============+==============+==============+ | 1 | 10045 | 0.050576 | 0 | 0.154324 | 0.130628 | 0.0398957 | 0.0289548 | 0 | 0 | 0.124971 | | 1 | 10097 | null | null | 0.0823631 | 0 | null | 0.0406325 | 0.0226116 | 0.0226116 | 0 | | 1 | 10128 | 0.00185251 | 0 | 0 | 0.00272515 | 0.00594128 | null | 0 | null | null | | 1 | 10707 | 0.380632 | 0 | 0 | 0 | 0 | 0.44799 | 0.0206532 | 0.0111431 | 0.0234156 | | 1 | 10751 | 0.0462002 | 0.0439648 | 0.0084101 | 0.0505068 | 0 | 0.0475206 | 0 | 0.0453142 | 0.00522015 | | 1 | 10755 | 0.00147929 | 0 | 0.00290363 | 0.00560938 | 0 | null | 0 | 0 | null | | 1 | 10760 | 0 | 0.011568 | 0 | 0 | 0.0882033 | 0.00590868 | 0 | 0 | 0 | | 1 | 10814 | null | null | 0 | null | null | null | null | null | 0 | | 1 | 10838 | 0.133482 | 0 | 0 | 0.00616907 | 0.146222 | 0.0587818 | 0 | 0 | 0.0621118 | | 1 | 10839 | 0.133482 | 0 | 0 | 0.00616907 | 0.146222 | 0.0639509 | 0 | 0 | 0.0558049 | +------+-------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+--------------+--------------+ 10 tuples (2m 36s) !140 columns dropped! sql> My question: AM I doing something wrong, or is this a bug? I am running the july2015 sp3: [lcj34@cbsudc01 monetdbFiles]$ monetdb -v MonetDB Database Server Toolkit v1.1 (Jul2015-SP3) [lcj34@cbsudc01 monetdbFiles]$ Thanks - Lynn

2 3

Column level 3 problems
by wilco draijer 23 May '16

23 May '16

Hello, When I try to convert my monetdb sql database to rdf using d2rq I get the following error:Problem accessing /directory/genelocation_genelocation. Reason: TODO: column names of level >= 3:SELECT genelocation"."genelocation"."entrez_id" F 50 (E0) This has to do with the bug described in https://www.monetdb.org/bugzilla/show_bug.cgi?id=2526 Is there a way to get around this? Best regards, Wilco

2 1

BLOB
by Shmagi Kavtaradze 22 May '16

22 May '16

Hello community, currently I am using HUGEINT to store data and using UDF I perform bitwise AND on them. I want to store my data in BLOB and perform same actions, but in data mapping table https://www.monetdb.org/wiki/MonetDB_type_system, I was not able to find BLOB mapping for C and MAL. So: 1) What are C and MAL mappings for UDF, if they exist? 2) Is it possible to perform Bitwise AND on blob?

1 0

MonetDB cannot find file to load
by João Victor Risso 19 May '16

19 May '16

Hi All, I am benchmarking MonetDB against other databases, in special the current version of CitusDB's Postgres, to find out which one is a good fit for our OLAP application. In order to do that, I've decided to use a standard benchmark, TPC-H. However, I did not manage to load the data generated by dbgen (one of the tools included in the benchmark) into MonetDB. Here is the SQL statement I've tried to run: > COPY INTO region FROM '/home/user/tpch/dbgen/region.tbl' USING DELIMITERS '|', '\n' LOCKED; and it generated an error which says the file could not be found: > could not open file '/home/user/tpch/dbgen/region.tbl': No such file or directory even though the file exists. I'm running the MonetDB provided in the official repository and the version reported by mclient is: > MonetDB v11.21.19 (Jul2015-SP4) Any help would be appreciated. -- João Victor Tozatti Risso C3SL - Centro de Computação Científica e Software Livre Universidade Federal do Paraná Please consider using PGP encryption to make our communications more secure. You can find my public key at https://pgp.mit.edu/ | Key ID: 395192B8 If you want to know more about PGP encryption, check the following link: https://emailselfdefense.fsf.org/en/

2 1

Monetdb Keep alive in Pentaho
by Ariel Abadi 18 May '16

18 May '16

Hi All! We are using Pentaho (BA Server) and have created a JNDI connection using Monetdb. We are facing some "errors". It seems that the connection to pentaho sometimes drops, therefore wanted to know which will be the correct sintax (if there is one) to send a "keep alive" in the JNDI connection. Thks in advance

1 0