- users-list - monetdb.org

Aggregation on double column in different monetdb version
by imad hajj chahine 04 Aug '22

04 Aug '22

Hi, I am currently migrating monetdb from an old version to the current release, and I noticed that sum on double precision columns is returning a slightly different result between both versions. The only difference between the system is HugeInt enabled on the newer version. ex: To reproduce the mismatch (subset of values extracted from the table) SELECT sum(cast(c1 as double)) --, sum(c1) FROM (VALUES (218.5792349726776),(792.3497267759564),(846.9945355191256),(819.6721311475409),(846.9945355191256),(819.6721311475409),(846.9945355191256),(846.9945355191256),(819.6721311475409),(846.9945355191256),(819.6721311475409),(846.9945355191256),(657.5342465753424),(4109.58904109589),(4234.972677595628),(3961.748633879781),(4234.972677595628),(4098.360655737704),(4234.972677595628),(4098.360655737704),(4234.972677595628),(4234.972677595628),(4098.360655737704),(4234.972677595628),(4098.360655737704),(273.224043715847),(219.1780821917808),(821.917808219178),(849.3150684931506),(846.9945355191256),(792.3497267759564),(846.9945355191256),(819.6721311475409),(846.9945355191256),(819.6721311475409),(846.9945355191256),(846.9945355191256),(819.6721311475409),(655.7377049180328),(249.3150684931507),(1104.109589041096),(1101.092896174863),(1030.054644808743),(1101.092896174863),(1065.573770491803),(1101.092896174863),(1065.573770491803),(1101.092896174863),(1101.092896174863),(1065.573770491803),(1101.092896174863),(852.4590163934427),(219.1780821917808),(1698.630136986301),(1643.835616438356),(1698.630136986301),(1693.989071038251),(1584.699453551912),(1693.989071038251),(1639.344262295081),(1693.989071038251),(1639.344262295081),(1693.989071038251),(1693.989071038251),(1475.409836065573),(60.27397260273972),(1868.493150684931),(1808.219178082191),(1868.493150684931),(1863.387978142076),(1743.169398907103),(1863.387978142076),(1803.27868852459),(1863.387978142076),(1803.27868852459),(1863.387978142076),(1863.387978142076),(1803.27868852459),(258.9041095890410),(381.1475409836065),(356.5573770491803),(381.1475409836065),(368.8524590163934),(381.1475409836065),(368.8524590163934),(381.1475409836065),(381.1475409836065),(368.8524590163934),(381.1475409836065),(368.8524590163934),(135.2459016393442),(542.4657534246575),(904.1095890410959),(934.2465753424657),(931.6939890710382),(871.5846994535519),(931.6939890710382),(901.639344262295),(931.6939890710382),(901.639344262295) ) t1 (c1) old-V11.27.11: 137756.87551463424 new-V11.43.15: 137756.8755146343 ExactValue: 137756.87551463430452 The aggregation over double columns should return the same result regardless of the engine versions, as the double precision type did not change, unless there was a bug reported and it was fixed. Any idea what could affect the result difference. Regards.

2 2

Define a cast function from day_interval to int
by imad hajj chahine 03 Aug '22

03 Aug '22

Hi, I am currently migrating monetdb from an old version to the current release, and I noticed that the difference of 2 dates return a day_interval and cannot be cast to an int and I have to use the "day" function for this purpose. This is fine for newly written scripts, but for old scripts I am hoping to avoid a rewrite. ex: cast(date1-date2 as int) return an error: types day_interval(4,0) and int(32,0) are not equal How to define a cast function from day_interval to int to avoid any changes to the old scripts?

2 2

Procedure vanishing
by hyemi.jeong＠obzen.com 21 Jul '22

21 Jul '22

Hello, I have just started to use monetDB and something strange has happened. I've made 5 procedures and 2 of them are just gone... I don't know what happened, they've just disappeared. Does this normally happen? Is there any possible explanation for this? I cannot possibly imagine what causes for this vanishing. Hope to hearing from you. Thanks.

1 0

Monetdb odbc driver
by imad hajj chahine 22 Jun '22

22 Jun '22

Hi, Is it possible to use 2 versions of the odbc driver simultaneously on the same windows machine? Since the windows installer does not allow this, I copied the installation folder from another machine and register the driver using the following command: odbcconf /A {INSTALLDRIVER "MonetDB ODBC Driver Old|driver=C:\Program Files\MonetDB\MonetDB ODBC Driver Old\lib\MonetODBC.dll|Setup=C:\Program Files\MonetDB\MonetDB ODBC Driver Old\lib\MonetODBCs.dll|APILevel=1|ConnectFunctions=YYY|DriverODBCVer=03.52|SQLLevel=3"} In the registry and the ODBC datasources the driver "MonetDB ODBC Driver Old" is registered correctly, but when I try to create a DSN for the new driver I am getting Error "Invalid Driver name". Does the Monetdb odbc driver need to have the exact name "MonetDB ODBC Driver"? Thanks.

2 2

Variable scope in Mal function
by imad hajj chahine 16 Jun '22

16 Jun '22

Hi, I have a mal function defined as following: function halfofyear(pdate:bat[:date]):bat[:int]; hym:= batmtime.month(pdate); hy:= batcalc.-(hym, 1, nil:bat); hy:= batcalc./(hy, 6, nil:bat); return halfofyear:= batcalc.+(hy, 1, nil:bat); end halfofyear; and I am calling it twice on 2 date columns: select halfofyear(colDate1), halfofyear(colDate2) from table. My concern is the scope of variables defined (hym, hy), as I was expecting to have unique naming when executing. Is it a guarantee that internally they are different variables or these are shared across the calls from the same query. bellow is the trace produced: hym=[57530]:bat[:int] := batmtime.month(pdate=[57530]:bat[:date]); hym=[57530]:bat[:int] := batmtime.month(pdate=[57530]:bat[:date]); hy=[57530]:bat[:int] := batcalc.-(hym=[57530]:bat[:int], 1:int, nil:BAT); hy=[57530]:bat[:int] := batcalc.-(hym=[57530]:bat[:int], 1:int, nil:BAT); hy=[57530]:bat[:int] := batcalc./(hy=[57530]:bat[:int], 6:int, nil:BAT); hy=[57530]:bat[:int] := batcalc./(hy=[57530]:bat[:int], 6:int, nil:BAT); return halfofyear=[57530]:bat[:int] := batcalc.+(hy=[57530]:bat[:int], 1:int, nil:BAT); return halfofyear=nil:bat[:int] := batcalc.+(hy=nil:bat[:int], 1:int, nil:BAT); function batudf.halfofyear(pdate:bat[:date]):bat[:int]; X_22=[57530]:bat[:int] := batudf.halfofyear(X_20=[57530]:bat[:date]); return halfofyear=[57530]:bat[:int] := batcalc.+(hy=[57530]:bat[:int], 1:int, nil:BAT); return halfofyear=nil:bat[:int] := batcalc.+(hy=nil:bat[:int], 1:int, nil:BAT); function batudf.halfofyear(pdate:bat[:date]):bat[:int]; X_25=[57530]:bat[:int] := batudf.halfofyear(X_21=[57530]:bat[:date]); barrier X_76=false:bit := language.dataflow(); X_26=33:int := sql.resultSet(X_27=[2]:bat[:str], X_28=[2]:bat[:str], X_29=[2]:bat[:str], X_30=[2]:bat[:int], X_31=[2]:bat[:int], X_22=[57530]:bat[:int], X_25=[57530]:bat[:int]); Thanks.

2 3

change Mal function definition without server restart
by imad hajj chahine 16 Jun '22

16 Jun '22

Hi, Is it possible to change the Mal function definition while the server is running without restarting? I tried connecting with mclient -lmal and running the function in an attempt to override the previous definition with no luck. any similar syntax to sql create or replace in Mal? Thanks.

1 0

Possible Loss of data (11.27.9)
by imad hajj chahine 16 Jun '22

16 Jun '22

Hi, I still have a server running 2017-sp2 (11.27.9) version, we had several reports of tables becoming empty after no activity for a period of one month or more. These tables are loaded from csv using the copy command, and at the time of loading the tables contain the correct data. Querying sys.storage showed that the oid column has a count of 210852 while the others are empty. Digging into the release-notes i only found the following alarming issue: https://github.com/MonetDB/MonetDB/issues/6465 I know that release is not supported anymore and I am planning a migration. Any idea what's the reason behind this behavior, was this reported before? [image: image.png] Thanks. Regards.

2 2

Announcement: New Jan2022-SP3 Bugfix release of MonetDB suite
by Sjoerd Mullender 31 May '22

31 May '22

The MonetDB team at MonetDB Solutions BV is pleased to announce the Jan2022-SP3 bugfix release of the MonetDB suite of programs. More information about MonetDB can be found on our website at <https://www.monetdb.org/>. For details on this release, please see the release notes at <https://www.monetdb.org/documentation/admin-guide/release-notes/jan2022/>. As usual, the download location is <https://www.monetdb.org/downloads/>. ### MonetDB Common - All accesses to the BACKUP directory need to be protected by the same lock. The lock already existed (GDKtmLock), but wasn't used consistently. This is now fixed. Hopefully this makes the hot snapshot code more reliable. - When exiting, long running instructions are aborted using the same mechanism that is used for query timeouts. ### SQL Frontend - GLOBAL TEMPORARY tables are now treated like LOCAL TEMPORARY tables as far as the table content is concerned. The schema information stays global. This fixes an issue with concurrent access and cleanup of stale data. - The NO CONSTRAINT option of the COPY INTO query has been removed. It didn't work and it was never a good idea anyway. ### Bug Fixes - [7036](https://github.com/MonetDB/MonetDB/issues/7036): Generate column names instead of labels

1 0

DELETE INTO?
by Marc Lowenthal 25 Apr '22

25 Apr '22

Hi all -- Does anyone know if MonetDB has a way of doing "DELETE INTO". The use case is to delete records from a table and save those results into another table with a single query. Thanks! --- Marc [cid:image001.png@01D85569.F9F48BB0]<http://www.scifinsys.com/> Marc Lowenthal Chief Product Officer marc(a)scifinsys.com<mailto:marc@scifinsys.com> [facebook]<https://www.facebook.com/Scientific-Financial-Systems-105163315113238> [linkedin]<https://www.linkedin.com/company/scientific-financial-systems-inc/> [/e9a7975e-dda8-4271-afa6-992bc461e648/]<https://scifinsys.com/> SFS is hiring! https://www.linkedin.com/jobs/view/2995084707/?refId=W7U9EW8bJ8ZxuymdxbxPqw… The entire content of this email message is confidential. This also applies to any files attached to it. This email is intended for an individual or entity to whom they are addressed. In case you are not the addressee of this email, and you have received it in error, immediately contact the system manager. The information in this email is very sensitive, and it is intended for the specific addressee. This email should not be disseminated, distributed or copied. If you have received this email and it was not for you, make sure to immediately notify the sender by email and afterwards delete this email from your system. Disclosing, copying, distributing, or taking any action in reliance to the email content is strictly prohibited. Consider yourself notified.

2 1

CHANGE COLUMN DATA TYPE?
by Alberto Ferrari 21 Apr '22

21 Apr '22

Hi all, I need to change the data type of a column. (I know the trick of creating a new temp column, copy the data from old, etc...) But I expected newer versions already have a command equivalent to MySql "CHANGE COLUMN" or similar, but I can't found in monetdb documentation. All examples of "ALTER COLUMN" are for setting other attributes. Anybody knows a specific command to do what I need? Kind regards, Alberto

2 4

Announcement: New Jan2022-SP2 Bugfix release of MonetDB suite
by Sjoerd Mullender 08 Apr '22

08 Apr '22

The MonetDB team at MonetDB Solutions BV is pleased to announce the Jan2022-SP2 bugfix release of the MonetDB suite of programs. More information about MonetDB can be found on our website at <https://www.monetdb.org/>. For details on this release, please see the release notes at <https://www.monetdb.org/documentation/admin-guide/release-notes/jan2022/>. As usual, the download location is <https://www.monetdb.org/downloads/>. ### Client Package - Improved the handling of the \r (internal pager) command in mclient. It now properly counts the header of table, and when a (very) long table is being printed and aborted part way in the built-in pager, not all data is transferred to the client (and then discarded). Instead at most 1000 rows are transferred. ### MonetDB Common - Improved speed of BATappend to empty varsized bat: we now just copy the heaps instead of inserting individual values. - Improved speed of projection (BATproject) on varsized bats by sharing the data heap (vheap). - Fixed a race condition which could cause a too large size being written for a .theap file to the BBP.dir file after the correct size file had been saved to disk. - We now ignore the size and capacity columns in the BBP.dir file. These values are essential during run time, but not useful in the on-disk image of the database. - Fixed a bug in the append code for msk (bit mask) bats. - Conversions from floating point types to integral types that involve multiplication now use the "long double" as intermediate type, thereby loosing as few significant bits as is feasible. - Found and fixed another source for the now infamous BBPcheckbats error that sometimes occurs at startup of the server. ### MonetDB5 Server - Improved parsing speed of blob values, especially on Windows. On Windows, using the locale-aware functions isdigit and isxdigit is comparatively very slow, so we avoid them. ### Bug Fixes - [7252](https://github.com/MonetDB/MonetDB/issues/7252): Segmentation fault on second run - [7253](https://github.com/MonetDB/MonetDB/issues/7253): Extremely slow INSERT INTO <table> SELECT - [7254](https://github.com/MonetDB/MonetDB/issues/7254): Commit with deletions is very slow - [7263](https://github.com/MonetDB/MonetDB/issues/7263): PRIMARY KEY constraint is not persistent through server restarts - [7267](https://github.com/MonetDB/MonetDB/issues/7267): Update after delete does not update some rows - [7278](https://github.com/MonetDB/MonetDB/issues/7278): BUG when there is more than one field/filter in the having clause

1 0

wrong command on deb package installation page
by Manuel Pagliai 04 Mar '22

04 Mar '22

Hello, I was following these instructions: https://www.monetdb.org/downloads/deb/ I think there is a mistake in the page and the command sudo wget --output-document=/etc/apt/trusted.gpg.d/monetdb.gpg /downloads/MonetDB-GPG-KEY.gpg should be replaced with sudo wget --output-document=/etc/apt/trusted.gpg.d/monetdb.gpg https://www.monetdb.org/downloads/MonetDB-GPG-KEY.gpg Best regards, *Manuel Pagliai* Visokio Ltd www.visokio.com +44 20 7351 4646

2 1

Iterations in MonetDB
by Oxana Dolmatova 23 Feb '22

23 Feb '22

Hello MonetDB Team, I want to integrate iterations into MonetDB using the barrier symbols provided by MAL (here the variable list_of_updates is repeated body of the iteration and ex_cond is the exit condition): ======================================= res_body = stmt_list(sql->sa, list_of_updates); stmt *s_begin = stmt_begin(sql->sa); list_append(res_list, s_begin); list_append(res_list, ex_cond); stmt *s_check = stmt_check(sql->sa, ex_cond, s_begin); list_append(res_list, s_check); list_append(res_list, res_body); list_append(res_list, stmt_redo(sql->sa, s_check)); list_append(res_list, stmt_exit(sql->sa, s_begin)); return stmt_list(sql->sa, res_list); ======================================== Currently the implementation works, when the exit condition is true, but unfortunately never stops, if the exit condition is false (I assume that this happens due to non-updating computation of ex_cond). Could you please give the reference point in the documentation or code where I could look to check if my assumption is correct? Or point me to a proper mailing-list if this is the wrong one for such questions? :) Thank you and best regards, Oksana

1 0

Fwd: COPY SELECT INTO with problems on new version 11.43.5 (Jan2022)
by Ariel Abadi 16 Feb '22

16 Feb '22

Hi All! I'm resending to the new user list. BR Ariel ---------- Forwarded message --------- From: Ariel Abadi <aabadi(a)starbi.com> Date: Fri, Feb 4, 2022, 18:44 Subject: COPY SELECT INTO with problems on new version 11.43.5 (Jan2022) To: Communication channel for MonetDB users <users-list(a)monetdb.org> Hi All! We found a problem on the new version 11.43.5 (Jan2022). We have some processes that run the COPY SELECT INTO to "move" data from monetdb to other different databases. We are expecting to receive an "answer" from monetdb affected, when the export finishes, like in the example below that informes "3 affected rows" sql>COPY SELECT * FROM cars INTO '/tmp/cars_out.csv' USING DELIMITERS ',' , '\n' , '"';*3 affected rows* While we run the same query on the new server, mclient/monetdb is not returning anything like the image below. sql>COPY SELECT * FROM cars INTO '/tmp/cars_out.csv' USING DELIMITERS ',' , '\n' , '"'; sql> Does anybody have the same issue? Am i doing something wrong? Thks a lot in advance! Ariel

2 1

Announcement: New Jan2022-SP1 Bugfix release of MonetDB suite
by Sjoerd Mullender 14 Feb '22

14 Feb '22

The MonetDB team at MonetDB Solutions BV is pleased to announce the Jan2022-SP1 bugfix release of the MonetDB suite of programs. More information about MonetDB can be found on our website at <https://www.monetdb.org/>. For details on this release, please see the release notes at <https://www.monetdb.org/documentation/admin-guide/release-notes/jan2022/>. As usual, the download location is <https://www.monetdb.org/downloads/>. ### SQL Frontend - [This feature was already released in Jan2022 (11.43), but the ChangeLog was missing] Added SQL procedures sys.vacuum(sname string, tname string, cname string), sys.vacuum(sname string, tname string, cname string, interval int), sys.stop_vacuum(sname string, tname string, cname string). These can be used to vacuum string columns. ### Bug Fixes - [7228](https://github.com/MonetDB/MonetDB/issues/7228): COMMIT: transaction is aborted because of concurrency conflicts, will ROLLBACK instead - [7230](https://github.com/MonetDB/MonetDB/issues/7230): Prepared statement of INSERT with SELECT fails when types difer - [7232](https://github.com/MonetDB/MonetDB/issues/7232): False conflicts when inserting in a not null field - [7237](https://github.com/MonetDB/MonetDB/issues/7237): SELECT with concurrent writes rarely returns corrupt data - [7238](https://github.com/MonetDB/MonetDB/issues/7238): query with system function: "index"(varchar, boolean) fails with GDK error or assertion failure. - [7241](https://github.com/MonetDB/MonetDB/issues/7241): Replacing a view by a query on the view itself crashes the server.

1 0

mailing list software updated
by Sjoerd Mullender 10 Feb '22

10 Feb '22

I have updated the mailing list software on monetdb.org from mailman2 to mailman3. This means that the old accounts for managing list subscriptions do not work anymore. In order to manage your subscription using a web interface, you will need to create a new account at https://www.monetdb.org/lists/. You can associate multiple email addresses to this account and manage all your subscriptions at monetdb.org using a single account, even if they use different email addresses. Unlike in mailman2, in mailman3 the passwords are not saved in plaintext, and therefore they cannot be sent as a monthly reminder. Mailing list archives have been converted to the new system as well. The old web addresses (https://mail.monetdb.org/, https://www.monetdb.org/mailman/ and https://www.monetdb.org/pipermail/) now all get redirected to the new interface. -- Sjoerd Mullender

1 0

None
by None 10 Feb '22

10 Feb '22

keeping the BAT open (I always use "bat("name").XXX", or keep a ref variable in a block. So, as far as I can tell, I should not have 7500 BATs open at once. Am I misunderstanding? Or is there some underlying component/module keeping the BATs open? Or is this just the amount of memory MonetDB will use? I do not have a BAT of BATs (as I was sure that this would cause problems, and from your description, will indeed). Thanks! Ed

1 0

None
by None 10 Feb '22

10 Feb '22

columns in a BAT (ie, BAT[OID, any]), is this just the first BUN? For update operations, does this mean that in the middle of inserting into a set of BATs, that as the BUN count is different, a concurrent query may not realize that the two BATs are synced? (this gets back to multiple client isolation for changes) Thanks for your time! Regards! Ed

1 0

None
by None 10 Feb '22

10 Feb '22

"This is implemented by storing a very large OID for each column. An update to the column destroys this OID."

1 0

None
by None 10 Feb '22

10 Feb '22

"For BAT-sets that contain exactly the same sets of OIDs, those OID-columns can be marked synced(). This information is then used to use positional lookup (the i-th element in BAT1 corresponds with the i-th in BAT2) and to turn semijoin- into very cheap copy-operations, etc." almost makes it sound as if you need synched() to actual test and set this bit. Is this true? How is the "syncedness" ensured when I have a BAT[OID, any], or determined?

1 0

New MonetDB JDBC driver v3.2 and JdbcClient released
by Martin van Dinther 03 Feb '22

03 Feb '22

The MonetDB team at MonetDB BV is pleased to announce a feature release of the MonetDB JDBC driver (monetdb-jdbc-3.2.jre8.jar) and JdbcClient CLI program (jdbcclient.jre8.jar). The new release adds the capability to use the SQL: COPY ... INTO ... ON CLIENT statements via Java from the client side. This allows users and programmers to use the much faster COPY statements to load large data sets into MonetDB from a local client side CSV file or stream. The JDBC MonetConnection API has been extended with methods to register and use Upload and Download Handlers to stream the data from the Java client application to the server and/or from the server to the Java client. See <https://www.monetdb.org/downloads/Java/onclient.txt> for details. The new functionality has also been added to the JdbcClient CLI program, so you can now also use COPY ... INTO ... ON CLIENT statements from this CLI program, provided you have started it with a --csvdir argument. The CSV data files may also be gzip compressed (file name ends with: .gz). Besides this new functionality we also improved both the JDBC driver and JdbcClient to work optimal with the new MonetDB Jan2022 server release. For a list of all changes please read: <https://www.monetdb.org/downloads/Java/ChangeLog>. We recommend all users of MonetDB JDBC driver and/or JdbcClient program to upgrade to this new release. Especially if you use the new MonetDB Jan2022 server release, you should use the new jar files. The download location is as usual: <https://www.monetdb.org/downloads/Java/>. For information and tips on using the MonetDB JDBC driver, read: <https://www.monetdb.org/downloads/Java/release.txt>. Reminder: as of release monetdb-jdbc-3.0.jre8.jar the MonetDB JDBC Driver class name has been changed to: org.monetdb.jdbc.MonetDriver It used to be nl.cwi.monetdb.jdbc.MonetDriver which is marked as deprecated. Please use the new driver class name asap in your configuration files/scripts and/or Java code. The old driver class name is currently still included in the jdbc jar file but only to ease the transition for existing deployments. The old driver class will be removed in a future release of the MonetDB JDBC driver.

1 0

Announcement: New Jan2022 Feature release of MonetDB suite
by Sjoerd Mullender 24 Jan '22

24 Jan '22

The MonetDB team at MonetDB BV is pleased to announce the Jan2022 feature release of the MonetDB suite of programs. More information about MonetDB can be found on our website at <https://www.monetdb.org/>. For details on this release, please see the release notes at <https://www.monetdb.org/Downloads/ReleaseNotes>. As usual, the download location is <https://www.monetdb.org/downloads/>. ## Jan2022 Feature Release (11.43.5) ### - A couple of concurrency issues have been fixed. ### Windows Packaging - We now build Windows binaries using Visual Studio 2022. ### Client Package - A new output formatting mode was added to mclient. Use -fcsv-noquote to produce a CSV (comma-separated values) output where the quote characters have not been escapes. This can be useful when producing a single column string output that should be saved as is, e.g. when using the sys.dump_database() function. ### MonetDB Common - Implement string imprints (strimps for short) a pre-filter structure for strings in order to accelerate LIKE queries. If a strimp exists for a specific string column the strings are pre-filtered, rejecting strings that cannot possibly match, before the more expensive and accurate matching algorithms run. - On Windows, files and directories we create now get the attribute FILE_ATTIBUTE_NOT_CONTENT_INDEXED, meaning that they should not be indexed by indexing or search services. - Many (most) low level functions that could take a long time (such as BATjoin) can now be aborted with a timeout. When the function takes too long, the function will fail, and hence the whole SQL query will fail. - At some point in the past, string heaps were created where the hash value of the string was stored in the heap before the string. This hasn't been used in a long time. Now the code that could still read those old heaps has been removed. Bats that used the old format are converted automatically. - Some small interface changes to the atom functions: the atomPut function now returns (var_t) -1 on error instead of 0; the atomHeap function now returns success or failure as a gdk_return value. ### General - Reliance on the OpenSSL library has been removed. OpenSSL was used for the hash algorithms it contained (e.g. SHA512 and RIPEMD160) and for producing random numbers. The hash functions have been replaced by the original published functions, and random numbers are generated using system-specific random sources (i.e. not simply pseudo-random number generators). ### MonetDB5 Server - The storage cleanup in the 11.41.5 (Jul2021) release made the OLTP optimizer pipeline obsolete, thus it was removed. ### SQL Frontend - Add string imprints to the existing imprints index creation syntax. On string column "col" of a table "tbl" marked read only ("ALTER TABLE tbl SET READ ONLY") the user can create a string imprint using the syntax: "CREATE IMPRINTS INDEX index_name ON tbl(col);". - With the storage cleanup in the 11.41.5 (Jul2021) release, the ANALYZE statement was updated to accomodate those changes. The SAMPLE parameter is now ignored because ANALYZE generated statistics used by relational operators, are required to be precise. - In order to mitigate the I/O required to update the 'statistics' table, this table is no longer persisted. Alternately, it was changed into a computed view every time when queried. The 'stamp' and 'sample' fields were removed for the aforementioned reasons. The 'schema', 'table' and 'column' fields were added for convenience. - In previous versions there was no check that the INCREMENT BY value of a SEQUENCE was not zero. During the automatic upgrade of a database, INCREMENT BY values that are zero are set to one. - The method to compute the 'side_effect' effect property was changed for SQL functions defined in the backend engine (eg. ``CREATE FUNCTION ... EXTERNAL NAME "module"."function"''). It was changed from being computed by the SQL layer to the backend engine itself. As a consequence, the computed 'side_effect' value may be different, thus bringing incompatibilities. After an upgrade, if a 'side_effect' incompatibility arises, either the 'side_effect' value in the backend should be changed or the function should be re-created in SQL. - Removed deprecated system view sys.systemfunctions. It was marked as deprecated from release Apr2019 (11.33.3). Use query: select id as function_id from sys.functions where system; to get the same data as the old view. - Extended SQL system catalog with lookup table sys.fkey_actions and view sys.fkeys to provide user friendly querying of existing foreign keys and their ON UPDATE and ON DELETE referential action specifications. - Many improvements were done for REMOTE table plans. As a consequence, master or slave servers from this feature release are not compatible with older releases. - The view sys.ids has been changed to give more information about the objects in the system. In particular, there is an extra column added at the end that indicates whether the object is a system object. - The example modules opt_sql_append and udf are no longer loaded by default and no longer part of the binary release. If installed, they can be loaded using the --loadmodule option. - The built-in SQL functions to produce a dump that were added as a proof-of-concept in the previous release have been improved and are now usable. Use the query ``SELECT stmt FROM sys.dump_database(FALSE) ORDER BY o'' to produce a dump. The dump code built into mclient and msqldump is probably still more efficient, though. - The sys.epoch function has always been confusing. There are two versions, one with an INTEGER argument, and one with a BIGINT argument. The former accepted values as seconds, whereas the latter expected milliseconds. Also, the construct EXTRACT(EPOCH FROM value) returns a BIGINT with millisecond precision. This has now been overhauled. There is no longer a function sys.epoch with BIGINT argument, but instead there is a new function sys.epoch with DECIMAL(18,3) argument. The argument is seconds, but with 3 decimals, it provides millisecond accuracy. Also the EXTRACT(EPOCH FROM value) now returns a DECIMAL(18,3), again seconds with 3 decimals giving millisecond accuracy. Note that the internal, binary representation of DECIMAL(18,3) interpreted as seconds with 3 decimals and BIGINT with millisecond precision is exactly the same. ### Merovingian - Disabled logging into merovingian.log of next info message types: - proxying client <host>:<port> for database '<dbname>' to <url> - target connection is on local UNIX domain socket, passing on filedescriptor instead of proxying These messages were written to the log file at each connection. In most cases this information is not used. The disabling reduces the log file size. - Removed the deprecated monetdb commands `profilerstart` and `profilerstop`. ### Bug Fixes - [7168](https://github.com/MonetDB/MonetDB/issues/7168): Loosing the documentation - [7180](https://github.com/MonetDB/MonetDB/issues/7180): GROUP BY-subquery crashes MonetDb - [7182](https://github.com/MonetDB/MonetDB/issues/7182): Queries against sys.querylog_catalog, sys.querylog_calls or sys.querylog_history fail after restore of a db created using call sys.hot_snapshot(R'\path\file.tar'); - [7201](https://github.com/MonetDB/MonetDB/issues/7201): Selection of a subquery with a LEFT JOIN returns the wrong result set - [7202](https://github.com/MonetDB/MonetDB/issues/7202): DISTINCT does not work when sorting by additional columns - [7215](https://github.com/MonetDB/MonetDB/issues/7215): ODBC Driver SQLStatistics returns duplicate rows/rows for other tables

1 0

BATsetaccess() returns NULL
by Roberto Cornacchia 30 Dec '21

30 Dec '21

Hi there, I'm moving some code to Jul2021, and I got a bit stuck on this. The issue can be simplified to this (make a copy and, append another bat, and make the result read-only): bn = COLcopy(b, b->ttype, true, TRANSIENT); ret = BATappend(bn,u, s, force ? *force : false); bn = BATsetaccess(bn, BAT_READ); The only superficial difference from Oct2020 is that now BATaccess returns the modified bat instead of a gdk_return. The issue I see is that BATsetaccess() returns NULL, due to m1 == STORE_INVALID. Am I using this wrong? Any help is appreciated. Cheers, Roberto

1 1

SELinux problems
by Arjen P. de Vries 29 Dec '21

29 Dec '21

Hi all, Not really a bug report because I did not manage to figure out the cause. However, after upgrading from FC31 to FC32 I could not login any more, due to SELinux problems. Auto-relabeling did not work, nothing really... ... until I did dnf uninstall MonetDB-selinux. I came to this point because trying to give systemd services the correct labels with restorecon failed with an error referencing a monetdb specific file. I do not have the details unfortunately, but if you get problems, beware that MonetDB SELinux package and systemd may interfere in some way beyond my knowledge of these services. Best regards, Arjen PS: Some output from logs: sudo ausearch -c monetdb -m AVC,SELINUX_ERR [..] ---- time->Sat May 2 20:57:01 2020 type=AVC msg=audit(1588445821.693:203): avc: denied { open } for pid=1232 comm="monetdbd" path="/etc/resolv.conf" dev="dm-0" ino=3409775 scontext=system_u:system_r:init_t:s0 tcontext=system_u:object_r:default_t:s0 tclass=file permissive=1 ---- time->Sat May 2 21:12:56 2020 type=AVC msg=audit(1588446776.043:1194): avc: denied { execute } for pid=2861 comm="(monetdbd)" name="monetdbd" dev="dm-0" ino=2147256 scontext=system_u:system_r:init_t:s0 tcontext=system_u:object_r:unlabeled_t:s0 tclass=file permissive=1 trawcon="unconfined_u:object_r:monetdbd_exec_t:s0" ---- time->Sat May 2 21:12:56 2020 type=AVC msg=audit(1588446776.043:1195): avc: denied { execute_no_trans } for pid=2861 comm="(monetdbd)" path="/usr/bin/monetdbd" dev="dm-0" ino=2147256 scontext=system_u:system_r:init_t:s0 tcontext=system_u:object_r:unlabeled_t:s0 tclass=file permissive=1 trawcon="unconfined_u:object_r:monetdbd_exec_t:s0" ---- time->Sat May 2 21:12:56 2020 type=AVC msg=audit(1588446776.044:1196): avc: denied { map } for pid=2861 comm="monetdbd" path="/usr/bin/monetdbd" dev="dm-0" ino=2147256 scontext=system_u:system_r:init_t:s0 tcontext=system_u:object_r:unlabeled_t:s0 tclass=file permissive=1 trawcon="unconfined_u:object_r:monetdbd_exec_t:s0" ---- time->Sat May 2 21:12:56 2020 type=AVC msg=audit(1588446776.714:1197): avc: denied { remove_name } for pid=1232 comm="monetdbd" name="merovingian.pid" dev="tmpfs" ino=34369 scontext=system_u:system_r:init_t:s0 tcontext=system_u:object_r:unlabeled_t:s0 tclass=dir permissive=1 trawcon="system_u:object_r:monetdbd_var_run_t:s0" ---- time->Sat May 2 21:12:56 2020 type=AVC msg=audit(1588446776.714:1198): avc: denied { unlink } for pid=1232 comm="monetdbd" name="merovingian.pid" dev="tmpfs" ino=34369 scontext=system_u:system_r:init_t:s0 tcontext=system_u:object_r:unlabeled_t:s0 tclass=file permissive=1 ---- time->Sat May 2 21:12:56 2020 type=AVC msg=audit(1588446776.714:1199): avc: denied { write } for pid=1232 comm="monetdbd" name=".merovingian_lock" dev="dm-0" ino=5899443 scontext=system_u:system_r:init_t:s0 tcontext=system_u:object_r:unlabeled_t:s0 tclass=file permissive=1 trawcon="system_u:object_r:monetdbd_lock_t:s0" ---- time->Sat May 2 21:13:15 2020 type=AVC msg=audit(1588446795.214:1209): avc: denied { read } for pid=2925 comm="(monetdbd)" name="passwd" dev="dm-0" ino=524514 scontext=system_u:system_r:init_t:s0 tcontext=system_u:object_r:var_t:s0 tclass=file permissive=1 ---- time->Sat May 2 21:13:15 2020 type=AVC msg=audit(1588446795.214:1210): avc: denied { open } for pid=2925 comm="(monetdbd)" path="/var/lib/sss/mc/passwd" dev="dm-0" ino=524514 scontext=system_u:system_r:init_t:s0 tcontext=system_u:object_r:var_t:s0 tclass=file permissive=1 ---- time->Sat May 2 21:13:15 2020 type=AVC msg=audit(1588446795.214:1211): avc: denied { map } for pid=2925 comm="(monetdbd)" path="/var/lib/sss/mc/passwd" dev="dm-0" ino=524514 scontext=system_u:system_r:init_t:s0 tcontext=system_u:object_r:var_t:s0 tclass=file permissive=1 ---- time->Sat May 2 21:14:24 2020 type=AVC msg=audit(1588446864.487:1281): avc: denied { read } for pid=3072 comm="(monetdbd)" name="passwd" dev="dm-0" ino=524514 scontext=system_u:system_r:init_t:s0 tcontext=system_u:object_r:var_t:s0 tclass=file permissive=1 ---- time->Sat May 2 21:14:24 2020 type=AVC msg=audit(1588446864.487:1282): avc: denied { open } for pid=3072 comm="(monetdbd)" path="/var/lib/sss/mc/passwd" dev="dm-0" ino=524514 scontext=system_u:system_r:init_t:s0 tcontext=system_u:object_r:var_t:s0 tclass=file permissive=1 ---- time->Sat May 2 21:14:24 2020 type=AVC msg=audit(1588446864.487:1283): avc: denied { map } for pid=3072 comm="(monetdbd)" path="/var/lib/sss/mc/passwd" dev="dm-0" ino=524514 scontext=system_u:system_r:init_t:s0 tcontext=system_u:object_r:var_t:s0 tclass=file permissive=1 -- ==================================================================== ICIS, office M1.00.05 Radboud University Mercator 1 Faculty of Science Toernooiveld 212 arjen(a)cs.ru.nl NL-6525 EC Nijmegen, The Netherlands +31-(0)24-365 2354 ===================== http://www.informagus.nl/ ==================== -- ==================================================================== ICIS, office M1.00.05 Radboud University Mercator 1 Faculty of Science Toernooiveld 212 arjen(a)cs.ru.nl NL-6525 EC Nijmegen, The Netherlands +31-(0)24-365 2354 ===================== http://www.informagus.nl/ ====================

2 6

Announcement: New Jul2021-SP2 Bugfix release of MonetDB suite
by Sjoerd Mullender 21 Dec '21

21 Dec '21

The MonetDB team at MonetDB BV is pleased to announce the Jul2021-SP2 bugfix release of the MonetDB suite of programs. More information about MonetDB can be found on our website at <https://www.monetdb.org/>. For details on this release, please see the release notes at <https://www.monetdb.org/Downloads/ReleaseNotes>. As usual, the download location is <https://www.monetdb.org/downloads/>. Jul2021-SP2 Bugfix Release (11.41.13) Client Package * Dumping the database now also dumps the read-only and insert-only states of tables. MonetDB Common * Sometimes when the server was restarted, it wouldn't start anymore due to an error from BBPcheckbats. We finally found and fixed a (hopefully "the") cause of this problem. SQL Frontend * Number parsing for SQL was fixed. If a number was immediately followed by letters (i.e. without a space), the number was accepted and the alphanumeric string starting with the letter was interpreted as an alias (if aliases were allowed in that position). Bug Fixes * 7163: Multiple sql.mvc() invocations in the same query * 7167: sys.shutdown() problems * 7184: Insert into query blocks all other queries * 7185: GROUPING SETS on groups with aliases provided in the SELECT returns empty result * 7186: data files created with COPY SELECT .. INTO 'file.csv' fail to be loaded using COPY INTO .. FROM 'file.csv' when double quoted string data contains the field values delimiter character * 7191: [MonetDBe] monetdbe_cleanup_statement() with bound NULLs on variable-sized types bug * 7196: BATproject2: does not match always * 7198: Suboptimal query plan for query containing JSON access filter and two negative string comparisons * 7200: PRIMARY KEY unique constraint is violated with concurrent inserts * 7206: Python UDF fails when returning an empty table as a dictionary

1 0