Jan2022 (11.43)

The Jan2022 documentation can be found here.

Jan2022-SP2 Bugfix Release (11.43.13)

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: Segmentation fault on second run

  • 7253: Extremely slow INSERT INTO <table> SELECT

  • 7254: Commit with deletions is very slow

  • 7263: PRIMARY KEY constraint is not persistent through server restarts

  • 7267: Update after delete does not update some rows

  • 7278: BUG when there is more than one field/filter in the having clause

Jan2022-SP1 Bugfix Release (11.43.9)

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: COMMIT: transaction is aborted because of concurrency conflicts, will ROLLBACK instead

  • 7230: Prepared statement of INSERT with SELECT fails when types difer

  • 7232: False conflicts when inserting in a not null field

  • 7237: SELECT with concurrent writes rarely returns corrupt data

  • 7238: query with system function: "index"(varchar, boolean) fails with GDK error or assertion failure.

  • 7241: Replacing a view by a query on the view itself crashes the server.

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.


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


  • 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: Loosing the documentation

  • 7180: GROUP BY-subquery crashes MonetDb

  • 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: Selection of a subquery with a LEFT JOIN returns the wrong result set

  • 7202: DISTINCT does not work when sorting by additional columns

  • 7215: ODBC Driver SQLStatistics returns duplicate rows/rows for other tables