The MonetDB Foundation is pleased to announce the Mar2025 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/release-notes/mar2025/>. As usual, the download location is <https://www.monetdb.org/downloads/>. The Mar2025 documentation can be found [here](https://www.monetdb.org/documentation-Mar2025/). ## Mar2025 Feature Release (11.53.3) ### - There is a new shared library called libmutils that contains some utility functions that are used by several programs. - Hot snapshot: allow member files larger than 64 GiB. By member files we mean the files inside the resulting .tar file, not the tar file itself. Huge member files are written using a GNU tar extension to the original tar format, which doesn't support more than 8 GiB. ### Client Package - Support for dumping databases from servers from before Jul2021 (11.41.X) has been removed. ### MonetDB Common - The implementation for the imprints index on numeric columns has been removed. It hasn't been used in years, and when it is enabled, it doesn't really make queries go faster. ### Geom Module - Removed type geometryA (geometry array). It was deprecated in the Jun2023 release (11.47.X) because there was no use for the type. ### MonetDB5 Server - Removed function bat.attach since it wasn't used. - Removed the MAL type "identifier" and supporting functions. There has never been an SQL interface to this type. - Removed the MAL type "color" and supporting functions. There has never been an SQL interface to this type. ### SQL Frontend - Added scalar functions: dayname(d date) and monthname(d date) returns varchar(10). - ranking window functions are now optimized into topn's For the grouped case we added the missing grouped/heap based topn implementation. - Added support for reading external data in a generic way via table returning function: proto_loader(string uri). The uri string value must start with the scheme name, ending with : character. Supported schemes are: monetdb: and odbc:. The monetdb scheme allows you to connect to a remote MonetDB server and retrieve the data of a specific table or view in a specific schema. The uri syntax: monetdb://[<host>[:<port>]]/<database>/<schema>/<table> Example: SELECT * FROM proto_loader('monetdb://127.0.0.1:50000/demo_db/sys/tables'); The odbc scheme allows you to connect to any ODBC data source via an ODBC driver and retrieve the data of a supplied query. The uri syntax: odbc:{{DSN|FILEDSN}=<data source name>|DRIVER=<path_to_driver>}; [<ODBC connection parameters>;]QUERY=<SQL query> For ODBC you normally configure a data source first. This is done using the ODBC administrator (on windows: odbcad32.exe, on linux: odbcinst). Once a data source for a specific ODBC driver has been setup using a unique name, you can reference it as: DSN=my_bigdata; or FILE_DSN=/home/usernm/dsns/my_bigdata.dsn; If you do not want to setup a data source, you can use DRIVER=...; to specify the ODBC driver program to use. However this also means you have to specify all the required connection parameters yourself, such as UID=...;PWD=...;DATABASE=...; etc. The QUERY=<SQL query> part is mandatory and must be specified at the end of the uri string, after the optional ODBC connection parameters. Examples: SELECT * FROM proto_loader( 'odbc:DSN=Postgres;UID=claude;PWD=monet;QUERY=SELECT * FROM customers'); SELECT * FROM proto_loader('odbc:DRIVER=/usr/lib64/libsqlite3odbc.so; Database=/home/martin/sqlite3/chinook.db;QUERY=SELECT * FROM customers'); Note that the 'odbc:' scheme is experimental and not enabled by default. To enable it, the MonetDB server has to be started with argument: --loadmodule odbc_loader - Extended the constant aggregate optimizer in order to eliminate aggregates with constant arguments whenever possible. - REMOTE TABLES and REPLICA TABLES now fully support the monetdb:// and monetdbs:// URL's introduced in Aug2024. Any mapi:monetdb:// URL's are normalized to the new style. - Add function sa_msettings_create() to allocate an msettings object using the arena allocator. - Unused helper function mapiuri_database() has been removed from rel_remote.h. - Added support for aggregates which order within the group such as quantile and which potentially order within the group such as group_concat. The ordering for such operators in now handled once in the relational plan. For this the create function statements can now have an optional order specification, using the keywords 'ORDERED' and 'WITH ORDER'. - Added support for recursive CTE's. - The SQL parser was cleaned up. This resulted in some keywords being used more strictly. If any of these keywords are to be used as column names, they have to be quoted using double quotes: AS, TABLE, COLUMN, DISTINCT, EXEC, EXECUTE. - Introduce the RETURNING clause for INSERT, UPDATE and DELETE statements. Specifying a RETURNING clause causes the SQL statement to return the modified records which can be queried using SELECT like expressions in the RETURNING clause. Aggregate functions are allowed. This is a common non-standard SQL extension. Examples: INSERT INTO foo values (1,10), (-1,-10) RETURNING i+2*j AS bar ---- 21 -21 UPDATE foo SET i = -i WHERE i >0 RETURNING sum(j), count(j) ---- -60|3 - Introduce division_min_scale SQL environment variable for specifying minimum scale of the division result. The default value is 3. ### Bug Fixes - [7101](https://github.com/MonetDB/MonetDB/issues/7101): Feature request: nextafter() in SQL - [7159](https://github.com/MonetDB/MonetDB/issues/7159): CREATE LOCAL TEMPORARY VIEW - [7331](https://github.com/MonetDB/MonetDB/issues/7331): Support RETURNING clause - [7578](https://github.com/MonetDB/MonetDB/issues/7578): explain result in Mal is truncated in large UDFs and their input bats is not shown - [7609](https://github.com/MonetDB/MonetDB/issues/7609): Upgrade 11.49.11 to 11.51.7 issues - [7611](https://github.com/MonetDB/MonetDB/issues/7611): Not possible to create table with multiple composite UNIQUE NULLS NOT DISTINCT constraints - [7614](https://github.com/MonetDB/MonetDB/issues/7614): Filter function creates a cartesian product when used with a view - [7615](https://github.com/MonetDB/MonetDB/issues/7615): Filter function creates a cartesian product when used with a view (2) - [7616](https://github.com/MonetDB/MonetDB/issues/7616): Filter function disappears - [7618](https://github.com/MonetDB/MonetDB/issues/7618): Tables loose their columns - [7619](https://github.com/MonetDB/MonetDB/issues/7619): Resource leak in prepared statements - [7621](https://github.com/MonetDB/MonetDB/issues/7621): crash on aggregate with case statement - [7622](https://github.com/MonetDB/MonetDB/issues/7622): In PREPARE queries with many parameters, information about parameters is truncated when sent to client. - [7623](https://github.com/MonetDB/MonetDB/issues/7623): Database crashed when using UPDATE xxx SET xxx RETURNING xx