Dec2023 (11.49)

The Dec2023 documentation can be found here.

Dec2023-SP1 Bugfix Release (11.49.5)

  • The copyright for the MonetDB software has been transferred to the newly established MonetDB Foundation, a not-for-profit foundation with the express goal of furthering the MonetDB database system. The license for the software does not change: MonetDB remains fully open source.

Client Package

  • Fixed an issue where mclient wouldn’t exit if the server it had connected to exited for whatever reason while the client was waiting for a query result.

MonetDB Common

  • The internal hash function for floating point types has been changed. It is now no longer based on the bit representation, but on the value, meaning that +0 and -0 (yes, they both exist in floating point) now hash to the same value.

  • Fixed a regression where bats weren’t always cleaned up when they weren’t needed anymore. In particular, after a DELETE FROM table query without a WHERE clause (which deletes all rows from the table), the bats for the table get replaced by new ones, and the old, now unused, bats weren’t removed from the database.

Geom Module

  • We switched over to using the reentrant interface of the geos library. This fixed a number of bugs that would occur sporadically.

SQL Frontend

  • performance improvement of ‘startswith’ and ’endswith’ filter functions for join operators

  • The function json.isvalid(json) incorrectly returned true if the argument was null. It should return null.

Bug Fixes

  • 6800: Please add information_schema (ANSI SQL norm)

  • 7152: Occasional dbfarm corruption upon database restart

  • 7390: Some MonetDB Server crashes found

  • 7412: MonetDB server crashes in vscanf

  • 7415: MonetDB server crashes in HEAP_malloc

  • 7416: MonetDB server crashes in atom_get_int

  • 7417: MonetDB server crashes in trimchars.

  • 7418: MonetDB server crashes in bind_col_exp

  • 7420: Performance issue with lower(string)

  • 7425: The last statement, execution error, is a false positive?

  • 7426: Unexpected result for INNER JOIN with IS NOT NULL

  • 7428: Unexpected result when using BETWEEN operator

  • 7429: Unexpected result when using CASE WHEN

  • 7430: Unexpected result when using AND and IS NOT NULL

  • 7431: [bug] Error code found, please confirm

  • 7432: MonetDB server crashes in dameraulevenshtein

  • 7433: MonetDB server crashes in exp_atom

  • 7434: MonetDB server crashes in exp_bin

  • 7435: MonetDB server crashes in exp_copy

  • 7436: MonetDB server crashes in exp_ref

  • 7437: MonetDB server crashes in exp_values_set_supertype

  • 7438: MonetDB server crashes in exps_bind_column

  • 7439: MonetDB server crashes in exps_card

  • 7440: MonetDB server crashes in gc_col

  • 7441: MonetDB server crashes in is_column_unique

  • 7442: MonetDB server crashes in mat_join2

  • 7443: MonetDB server crashes in merge_table_prune_and_unionize

  • 7444: [bug] the table cannot be created because the reserved word is incorrectly set

  • 7447: Unexpected result when using BETWEEN in INNER JOIN

  • 7448: Unexpected result when using AND/OR chain

  • 7450: Unexpected result when CREATE VIEW with WHERE NULL

  • 7451: Unexpected result when using BETWEEN and CAST

  • 7453: Cannot recover an msqldump

  • 7455: Unexpected result when using BETWEEN with BOOLEAN values

  • 7456: Crash when INNER JOIN with VIEW

  • 7457: Unexpected result when using AND with INTEGER

  • 7458: Unexpected result when using SIGN

  • 7461: Crash by potentially use of bad escape characters

  • 7462: Crash when using BETWEEN AND

  • 7465: Unexpected result when using NULL in BETWEEN

Dec2023 Feature Release (11.49.1)

  • All binary packages are now signed with a new key with key fingerprint DBCE 5625 94D7 1959 7B54 CE85 3F1A D47F 5521 A603.

  • The ranges of merge partitions are now pushed down into the low level GDK operations, giving them a handle to sometimes execute more efficiently.

  • Removed the PYTHON MAP external language option, as after a fork the synchronization primitives could be in any state, leading to deadlocks. During the upgrade function definitions will fallback to the normal PYTHON language option.

  • Implemented direct masking for strimp construction. The strimps datastructure now keeps an array of 65K 64-bit integers that is zero everywhere except at the indexes that correspond to header pairs. The entry for the nth pair in order has the nth bit of the bitstring on. These can be used to quickly construct bitstrings.

ODBC Driver

  • Corrected the output value of column CHAR_OCTET_LENGTH of ODBC functions SQLColumns() and SQLProcedureColumns().

MonetDB Common

  • Fixed a (rare) race condition between copying a bat (COLcopy) and updates happening in parallel to that same bat. This may only be an actual problem with string bats, and then only in very particular circumstances.

  • Introduced options wal_max_dropped, wal_max_file_age and wal_max_file_size that control the write-ahead log file rotation.

  • Removed function BATroles to set column names on BATs.

  • Removed the compiled-in limit on the number of threads that can be used. The number of threads are still limited, but the limit is dictated solely by the operating system and the availability of enough memory.

  • We now prevent accidental upgrades from a database without 128 bit integers to one with 128 bit integers (also known as HUGEINT) from happening. Upgrades will only be done if the server is started with the option –set allow_hge_upgrade=yes.

Geom Module

  • Because recent changes to the geom module require the use of geos 3.10, the geom module is no longer available in older versions of Debian and Ubuntu. Specifically, Debian 10 and 11 (buster and bullseye) and Ubuntu 20.04 (Focal Fossa) are affected. There is no automatic upgrade available for databases that were geom enabled to databases that are not, so dump + restore is the only option (if no geom types are actually used).

  • Implements Rtree index in GDK layer based on librtree. The index is used in the implementation of the filter functions ST_Intersects and ST_Dwithin for geometric points.

  • Improves shapefile support by replacing functions SHPattach, SHPpartialimport, ahd SHPimport with SHPload.

  • Introduces functions ST_DistanceGeographic, ST_DwithinGeographic, ST_IntersectsGeographic, ST_CoversGeographic, ST_Collects with geodesic semantics. ST_Transform can be used to convert geodetic into geographic data using libPROJ.

MonetDB5 Server

  • Removed MAL functions bat.reuse and bat.reuseMap.

  • The MAL functions io.import and io.export have been removed.

  • Removed MAL functions bat.getRole and bat.setColumn since the underlying function BATroles was removed.

  • Change how json is stored in the database: We now normalize json strings after parsing, removing whitespace and eliminating duplicate keys in objects.

  • The function json.filter now properly returns json scalars instead of wrapping them in an array.

  • Removed the MAL tokenizer module. It was never usable from SQL and in this form never would be.

SQL Frontend

  • Introduction of table returning function persist_unlogged(schema string, table string) that attempts to persist data in disk if “schema”.“table” is unlogged table in insert only mode. If persist attempt is successful, the count of the persisted rows is returned, otherwise the count is 0.

  • Added ISO/IEC 9075-11 SQL/Schemata (SQL:2011) with SQL system views: information_schema.schemata information_schema.tables information_schema.views information_schema.columns information_schema.character_sets information_schema.check_constraints information_schema.table_constraints information_schema.referential_constraints information_schema.routines information_schema.parameters information_schema.sequences For details see https://www.monetdb.org/documentation/user-guide/sql-catalog/information_schema/ Most views have been extended (after the standard columns) with MonetDB specific information columns such as schema_id, table_id, column_id, etc. This simplifies filtering and joins with system tables/views in sys schema when needed. Note: MonetDB does NOT support catalog qualifiers in object names, so all the CATALOG columns in these information_schema views will always return NULL.

  • Added support for generated column syntax: GENERATED BY DEFAULT AS IDENTITY … This allows the user to override the default generated sequence value during inserts.

  • Added SQL support for: <result offset clause> and <fetch first clause> in <query expression> ::= [ <with clause> ] <query expression body> [ <order by clause> ] [ <result offset clause> ] [ <fetch first clause> ] [ <sample clause> ] <result offset clause> ::= OFFSET <offset row count> [ {ROW|ROWS} ] <fetch first clause> ::= FETCH {FIRST|NEXT} <fetch first row count> {ROW|ROWS} ONLY

Bug Fixes

  • 6933: Add support for scalar function IFNULL(expr1, expr2)

  • 7044: Improve error message regarding 3-level SQL names

  • 7261: Misleading error message

  • 7274: Aggregate function ST_Collect crashes mserver5

  • 7376: Concurrency Issue: Second Python UDF Awaits Completion of First UDF

  • 7391: SQL 2023 : greatest/least functions with unlimited arguments (not only 2)

  • 7403: Join not recognized between two row_number() columns

  • 7413: MonetDB server crashes in BATcalcbetween_intern