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