- users-list - monetdb.org

bool from aggregate
by Stefano Fioravanzo 25 Jan '17

25 Jan '17

Hello, I would like to know if there is a way to replicate the behavior of the bool_and function of Postgresql. An example of its use is the following: SELECT bool_and(A) FROM mytable GROUP BY B; bool_and will return true if all values in the group are true, false otherwise. I would like to do the same thing in monetdb. Thanks Stefano

2 1

Case insensitive version of the IN operator?
by Kevork Vartanian 13 Jan '17

13 Jan '17

Hi, I was wondering if there is a case insensitive version of the IN operator, similar to ILIKE. Or is the IN itself case insensitive? It's mainly to avoid using nested OR's and ILIKE's if a case insensitive IN exists, to make the queries much simpler. Also, I don't want to use functions like lower as it's a big data set and the use of functions may make the queries even slower. Please advise. Thank you. Regards, Kevork Vartanian Technical Solutions Consultant kevork.vartanian(a)innotta.com.au | +61 406 776 789 | www.innotta.com.au

3 2

mclient COPY INFO Local files to a remote MonetDB Server Error
by ping gao 高平(0) 11 Jan '17

11 Jan '17

Hi ALL mclient COPY INFO Local files to a remote MonetDB Server Error. Mclient how to copy a local file to a remote MonetDB Server? Known to use: mclient COPY INFO - < file ; Will appear: syntax error, unexpected sqlINT in: Error Error Info :could not open file ‘xxxxx’ : No such file or directory

3 4

COPY INFO ERROR
by ping gao 高平(0) 10 Jan '17

10 Jan '17

Hi ALL I encountered a problem using a COPY INFO. Strange thing is my IP is 59, execute the following statement is not a problem on the machine.So freak! IP:192.168.110.59 (Database Server) [hotel-revenue-online@bjs-hotel-RevenueCompute-02 data_visual_monetdb]$ mclient -u monetdb -d hotel-revenue -s "COPY 2 RECORDS INTO hotel_revenue_octopus_booking_order FROM '/home/hotel-revenue-online/ping.gao/data_visual_monetdb/hotel_revenue_octopus_booking_order.sql' USING DELIMITERS ',','\\n'<smb://n'>" password: 2 affected rows IP:192.168.110.179 Error 1: [hotel-revenue-online@bjs-hotel-RevenueCompute-01 datavisual_service]$ mclient -h 192.168.110.59 -p 50001 -d hotel-revenue -s "COPY 2 RECORDS INTO hotel_revenue_octopus_hotel_info FROM /home/hotel-revenue-online/datavisual_service/data_tmp/hotel_revenue_octopus_hotel_info.data USING DELIMITERS '\\001','\\n'<smb://001','//n'>” syntax error, unexpected '/', expecting STRING or '(' or STDIN in: "copy 2 records into hotel_revenue_octopus_hotel_info from /“ Error 2: [hotel-revenue-online@bjs-hotel-RevenueCompute-01 datavisual_service]$ mclient -h 192.168.110.59 -p 50001 -d hotel-revenue -s "COPY 2 RECORDS INTO hotel_revenue_octopus_hotel_info FROM '/home/hotel-revenue-online/datavisual_service/data_tmp/hotel_revenue_octopus_hotel_info.data' USING DELIMITERS '\\001<smb://001>','\\n'<smb://n'>" could not open file '/home/hotel-revenue-online/datavisual_service/data_tmp/hotel_revenue_octopus_hotel_info.data': No such file or directory Error 3: [hotel-revenue-online@bjs-hotel-RevenueCompute-01 datavisual_service]$ mclient -h 192.168.110.59 -p 50001 -d hotel-revenue -s "COPY 2 RECORDS INTO hotel_revenue_octopus_hotel_info FROM STDIN DELIMITERS '\\001','\\n'<smb://001','//n'>" - < /home/hotel-revenue-online/datavisual_service/data_tmp/hotel_revenue_octopus_hotel_info.data 2 affected rows syntax error, unexpected sqlINT in: "90564318"

2 2

RE: COPY INFO ERROR
by Stefan Manegold 10 Jan '17

10 Jan '17

Not strange at all. Quotes are missing around file path (string). Sent from my Samsung Galaxy smartphone. -------- Original message --------From: "ping gao 高平(0)" <ping.gao(a)corp.elong.com> Date: 1/10/17 08:07 (GMT+01:00) To: Communication channel for MonetDB users <users-list(a)monetdb.org> Subject: COPY INFO ERROR Hi ALL I encountered a problem using a COPY INFO. Strange thing is my IP is 59, execute the following statement is not a problem on the machine.So freak! IP:192.168.110.59 (Database Server) [hotel-revenue-online@bjs-hotel-RevenueCompute-02 data_visual_monetdb]$ mclient -u monetdb -d hotel-revenue -s "COPY 2 RECORDS INTO hotel_revenue_octopus_booking_order FROM '/home/hotel-revenue-online/ping.gao/data_visual_monetdb/hotel_revenue_octopus_booking_order.sql' USING DELIMITERS ',','\\n'" password: 2 affected rows IP:192.168.110.179 Error 1: [hotel-revenue-online@bjs-hotel-RevenueCompute-01 datavisual_service]$ mclient -h 192.168.110.59 -p 50001 -d hotel-revenue -s "COPY 2 RECORDS INTO hotel_revenue_octopus_hotel_info FROM /home/hotel-revenue-online/datavisual_service/data_tmp/hotel_revenue_octopus_hotel_info.data USING DELIMITERS '\\001','\\n'” syntax error, unexpected '/', expecting STRING or '(' or STDIN in: "copy 2 records into hotel_revenue_octopus_hotel_info from /“ Error 2: [hotel-revenue-online@bjs-hotel-RevenueCompute-01 datavisual_service]$ mclient -h 192.168.110.59 -p 50001 -d hotel-revenue -s "COPY 2 RECORDS INTO hotel_revenue_octopus_hotel_info FROM '/home/hotel-revenue-online/datavisual_service/data_tmp/hotel_revenue_octopus_hotel_info.data' USING DELIMITERS '\\001','\\n'" could not open file '/home/hotel-revenue-online/datavisual_service/data_tmp/hotel_revenue_octopus_hotel_info.data': No such file or directory Error 3: [hotel-revenue-online@bjs-hotel-RevenueCompute-01 datavisual_service]$ mclient -h 192.168.110.59 -p 50001 -d hotel-revenue -s "COPY 2 RECORDS INTO hotel_revenue_octopus_hotel_info FROM STDIN DELIMITERS '\\001','\\n'" - < /home/hotel-revenue-online/datavisual_service/data_tmp/hotel_revenue_octopus_hotel_info.data 2 affected rows syntax error, unexpected sqlINT in: "90564318"

2 1

MAL function syntax
by Moritz Bruder 09 Jan '17

09 Jan '17

Hi, I'm having problems with the exact syntax of MAL functions. I used the syntax from the documentation, which is working for some functions, but my hand-written example doesn't work (neither when pasted in the REPL, nor as input file): "^<identifier> expected" at the end of every line. Any idea what could cause this? (I'm using a fairly recent development version.) Regards, Moritz

2 1

C UDF
by imad hajj chahine 06 Jan '17

06 Jan '17

Hi, After reviewing all the other alternatives like SQL and Python UDF, I was either stuck on performance with SQL UDF or on usability with Python UDF (unable to use with aggregation, and not such great performance with dates), so I decided to go the hard way with C functions, as a bonus it will give me the possibility to change the functionalities without worrying about dependencies, which was not the case in other languages. The purpose is to create a set of formatting functions for Year, Quarter, Month, Week and Day brackets, and of course i need to create the bulk version of each function for performance. Starting from the MTIMEdate_extract_year_bulk, now i have the simple function working, and successfully calling it from mclient: *str* *UDFyearbracket(str *ret, const date *v)* *{* * if (*v == date_nil) {* * *ret = GDKstrdup(str_nil);* * } else {* * int year;* * fromdate(*v, NULL, NULL, &year);* * *ret = (str) GDKmalloc(15);* * sprintf(*ret, "%d", year);* * }* * return MAL_SUCCEED;* *}* For the bulk version i get an error in the log: gdk_atoms.c:1345: strPut: Assertion `(v[i] & 0x80) == 0' failed. *str* *UDFBATyearbracket(bat *ret, const bat *bid)* *{* * BAT *b, *bn;* * BUN i,n;* * str *y;* * const date *t;* * if ((b = BATdescriptor(*bid)) == NULL)* * throw(MAL, "UDF.BATyearbracket", "Cannot access descriptor");* * n = BATcount(b);* * bn = COLnew(b->hseqbase, TYPE_str, BATcount(b), TRANSIENT);* * if (bn == NULL) {* * BBPunfix(b->batCacheid);* * throw(MAL, "UDF.BATyearbracket", "memory allocation failure");* * }* * bn->tnonil = 1;* * bn->tnil = 0;* * t = (const date *) Tloc(b, 0);* * y = (str *) Tloc(bn, 0);* * for (i = 0; i < n; i++) {* * if (*t == date_nil) {* * *y = GDKstrdup(str_nil);* * } else* * UDFyearbracket(y, t);* * if (strcmp(*y, str_nil) == 0) {* * bn->tnonil = 0;* * bn->tnil = 1;* * }* * y++;* * t++;* * }* * BATsetcount(bn, (BUN) (y - (str *) Tloc(bn, 0)));* * bn->tsorted = BATcount(bn)<2;* * bn->trevsorted = BATcount(bn)<2;* * BBPkeepref(*ret = bn->batCacheid);* * BBPunfix(b->batCacheid);* * return MAL_SUCCEED;* *}* PS: I am not a c expert but i can find my way with basic operations and pointers. Any help or suggestions is appreciated. Thank you.

4 17

Upgraded to lastest MonetDB, now get error
by Bryan Senseman 04 Jan '17

04 Jan '17

Ok, I upgraded my Windows version of MonetDB to Dec2016 from Jun2016-SP1 and I get this error. !FATAL: BBPinit: head column must be VOID (ID = 3). I was able to successfully downgrade (uninstall/reinstall) Jun2016-SP1; do I need to install SP2 first? -- ------------------------------ This transmission is confidential and intended solely for the use of the recipient named above. It may contain confidential, proprietary, or legally privileged information. If you are not the intended recipient, you are hereby notified that any unauthorized review, use, disclosure or distribution is strictly prohibited. If you have received this transmission in error, please contact the sender by reply e-mail and delete the original transmission and all copies from your system.

1 0

COPY INFO Exception
by ping gao 高平(0) 03 Jan '17

03 Jan '17

Hi ALL I encountered a problem using a COPY INFO The command to execute: [hotel@02 data_visual_monetdb]$ mclient -u monetdb -d hotel-revenue -s "COPY 1727300 RECORDS INTO hotel_revenue_octopus_hotel_info FROM '/home/ping.gao/data_visual_monetdbinfo.data' USING DELIMITERS ',','\\n'<smb://n'>,'\"'" password: Failed to import table Leftover data '"0”' Data Format(Line separator —> \n): “test”,”test”,”test”,”test”

2 2

File System Backups
by Vincent Sheffer 30 Dec '16

30 Dec '16

I’m trying to do automated incremental backups following these instructions: https://www.monetdb.org/Documentation/UserGuide/FastDumpRestore My question is whether why isn’t it enough to just stop the DB? Is the waiting for open sessions a courtesy or is it required that no statements are executing prior to shutdown? Is it similar for the shutdown command? It gives people in a session time to exit or something? Again, these are to be automated at a time of night that queries should not be being executed. Bottom line question: is it enough to stop and lock a database to ensure the binary backup will have no errors that prevent the backup from being restored or are the other steps required? Thanks, Vince The information transmitted, including any attachments, is intended only for the individual or entity to which it is addressed, and may contain confidential and/or privileged information. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by individuals or entities other than the intended recipient is prohibited, and all liability arising therefrom is disclaimed. If you have received this communication in error, please delete the information from any computer and notify the sender.

2 1