The Aug2024 documentation can be found here.
Improve casting to generic decimal type by choosing a better fit for precision and scale instead of defaulting to 18 and 3, respectively.
When for whatever reason the upgrade code produces an error, we now exit the server. Before the server would limp on with what is basically a broken database.
Improved the violation message of CHECK constraints when violated. It now includes the schema name of the constraint and the check clause.
Increase the buffer size used by hot snapshot from 64kiB to 1MiB, and make it configurable through setting ‘hot_snapshot_buffer_size’. It must be a multiple of 512.
7281: UDFs defined at compile time in a user schema should not become system functions
7563: Unexpected result when using IS DISTINCT FROM in VIEW
7567: creating remote table from subquery crashes the server
7569: Column of temporary table changes when another is updated
7570: BUG in the “str_to_timestamp” function
7571: Crash when integer overflow in ORDER BY
7572: column max length is not stored as specified and accepted at creation time
7575: Incorrect BAT properties after mmapped BAT “leaks” to disk with restart.
7576: unescaping UTF-16 code units goes wrong in json.text
7577: Crash when using CHECK constraint
7580: statistics optimizer handles date difference incorrectly
7582: SIGSEGV when creating a SQL function with RETURN CASE WHEN EXISTS (..)
7583: Query slowdown after deleting rows from large table
7584: SO_KEEPALIVE should be configured sensibly
7585: rel2bin_join: Assertion `sql->session->status == -10’ failed.
7587: Line/row numbers get out of sync with COPY INTO .. BEST EFFORT
The CMake configuration files for building extensions have now been included in the various MonetDB development RPMs and debs.
Removed upgrade code for versions before Jul2021.
The shared library (.dll aka .so files) now have the version number as part of the name. This should allow the building of compatibility versions that can be installed in parallel to the latest version.
Some of the Debian/Ubuntu packages have been renamed. The old monetdb5 names have been changed to plain monetdb, and libmonetdb5-server-* packages have been renamed monetdb-*.
The names of some of the provided RPM files have been changed. References to the old MonetDB5 name have been removed. All packages are now just MonetDB.
Msqldump now accepts –output and –outputdir options. When the –outputdir option is used, the dump is placed in the file dump.sql in the specified directory and all tables are dumped to separate CSV files. In this way it is feasible to edit the dump script by hand if needed, even for a large database.
The –table (-t) option of msqldump now accepts SQL-style % wildcard characters to dump all tables that match the pattern. E.g. -t %test%.%test% dumps all tables with ’test’ in both the schema and table name.
Implemented interrupt handling in mclient. When using mclient interactively, an interrupt (usually control-C) stops whatever the client is doing. When editing a line, the line is discarded; when editing a second or later line of a query, the whole query is discarded; when a query is being executed, the server is asked to stop the query at its earliest convenience. Stopping a running query can only be done with an up-to-date server. All of this does not work on Windows.
Extended ODBC Data Source Setup program on Windows with fields to specify optional Client Information such as Application Name and Client Remark.
Extended ODBC Data Source Setup program on Windows with fields to specify new TLS Connection settings.
Extended ODBC Data Source Setup program on Windows with a Test-button to quickly test connectivity to a MonetDB server.
ODBC now supports TLS. It can be configured through the following DSN- or Connection String attributes (canonical name / user friendly name): TLS / Encrypt = ON/OFF CERT / Server Certificate = PATH CERTHASH / Server Certificate Hash = sha256:HEXDIGITS CLIENTKEY / Client Key = PATH CLIENTCERT / Client Certificate = PATH
Several more connection properties have been made configurable: SCHEMA / Schema = NAME TIMEZONE / Time Zone = Minutes East Of UTC REPLYSIZE / Reply Size = NUMBER LOGFILE / Log File = PATH LOGINTIMEOUT / Login Timeout = MILLISECONDS CONNECTIONTIMEOUT / Connection Timeout = MILLISECONDS AUTOCOMMIT / Autocommit = ON/OFF SOCK / Unix Socket = PATH (unix only)
SQLBrowseConnect() adds On/Off suggestions to boolean settings and prioritizes the DATABASE attribute if it notices monetdbd requires one. Apart from that only UID/User and PWD/Password are required, all others have sensible defaults.
ICONV support has been removed from the stream library. The server itself only needs UTF-8 support. The client (mclient) does have iconv support.
CURL support has been removed from the stream library. If support is needed, look at the source code in either streamcat.c or mclient.c.
Various changes were made having to do with things like case-insensitive comparisons and converting to upper or lower case. Case insensitive comparison (i.e. the ILIKE operator) uses case folding which is similar to converting to lower case, but changes more characters, also sometimes to multiple characters (e.g. German sharp s (ß) compares equal to SS).
Made some changes to how BAT descriptors are allocated. They are now allocated in bulk, meaning fewer malloc/free calls during processing.
Removed macro BBP_cache and its associated code. Checking whether a BAT is cached (loaded in memory) can be done by checking the BBPLOADED bit in the BBP_status value. Getting a pointer to the BAT descriptor can be done by using BBP_desc.
The SQL transaction ID is no longer saved in the BBP.dir file.
Made some changes to the TIMEOUT macros. Most importantly, they now get a pointer to a QryCtx structure as argument instead of the timeout value.
The “phash” column in the sys.storage() table now indicates whether a hash exists. If the hash is not loaded but there is a hash available on disk, the phash value is “true”, but the “hashes” value is 0.
Added execution privilege on all sys.generate_series(first, limit) and sys.generate_series(first, limit, stepsize) functions to public, so all users can now call these table producing generator functions.
Extend CREATE USER MAX_MEMORY and ALTER USER MAX_MEMORY to accept strings of the form ‘10MiB’, ‘10G’, etc.
Extended view sys.sessions and function sys.sessions() with new columns: language, peer, hostname, application, client, clientpid and remark.
All users now have SELECT privilege on view sys.sessions, but non-admin users only see their own sessions.
Added procedure sys.setclientinfo(property string, value string) to allow the client application to set a specific client info property.
Added system table sys.clientinfo_properties that lists the supported client info properties and their associated column name in sys.sessions view. It contains property names: ClientHostname, ApplicationName, ClientLibrary, ClientPid and ClientRemark.
Extended sys.generate_series() to generate dates. Added 2 new functions: sys.generate_series(first date, “limit” date, stepsize interval month) and sys.generate_series(first date, “limit” date, stepsize interval day).
Added support for select exp, count(*) group by 1 order by 1; ie. using numeric references in group by clause.
Added support for GROUP BY ALL. This finds all expressions from the selections which aren’t aggregations and groups on those. At least one aggregation must be specified. The ALL keyword can also be replaced by ‘*’, so: GROUP BY *.
Added support for ORDER BY ALL. This orders on all columns of the selection. The ALL keyword can also be replaced by ‘*’, so: ORDER BY *.
Removed the obsolete ANALYZE statement syntax options: SAMPLE nn and MINMAX. Both options have been ignored since release Jan2022. Now they are no longer accepted in the ANALYZE statement.
The ANALYZE statement can now be used in procedures, functions and triggers.
Added support for CHECK constraints in CREATE TABLE and ALTER TABLE ADD CONSTRAINT statements. Columns and tables can have multiple CHECK constraints provided their names are unique within the schema of the table. The syntax is: [ CONSTRAINT a_name ] CHECK ( boolean_expression ). When no constraint name is specified, a constraint name will be generated.
Make schema renaming more permissive. A schema can be renamed if it does not contain objects that are a dependency for objects outside the schema. If such dependencies exist, they are shown in the table sys.dependencies.
Add a DECIMAL AS clause to COPY INTO that configures the decimal separator and thousands separator for decimals, temporal types and floats.
Introduce IS [NOT] DISTINCT FROM syntax. The syntax allows two values to be compared. The comparison always returns boolean FALSE or TRUE never NULL.
SQL2023 feature: Introduce UNIQUE NULLS [NOT] DISTINCT syntax which allows for NULLS to be treated as unique, i.e. a column with this constraint can have one NULL value at most.
SQL2023 feature: Allow project and ORDER BY expressions on UNIQUE constrained columns when the primary key column is used in a GROUP BY expression.
7045: A value filtered in a subquery finds its way to a later filter in the outer query
7097: Add an ‘ANY’ or ‘ARBITRARY’ aggregate function
7245: monetdbe silently importing 0 rows, at random
7265: COPY INTO Not Reading in all records of fixed width delimited file
7272: Missed rewrite to bulk operators in simple SQL UDF
7312: Test Button for ODBC Driver
7332: Support IS [NOT] DISTINCT FROM predicate
7353: INTERVAL SECOND columns become incorrect when an INTERVAL HOUR column is present
7367: Libraries linked more than once?
7370: timestamp_to_str function not aware of timezone
7374: Different date and time returns
7392: Evaluate Profile-Guided Optimization
7424: Performance issue in select-joins
7459: Crash when using CONTAINS in ORDER BY clause
7460: Crash when using CAST and BETWEEN AND
7463: Unexpected result when using CONTAINS and type casting
7466: Crash when INNER JOIN with CONTAINS
7467: Conversion TIMESTAMPTZ to TIME does not take session TZ into account
7474: MonetDB server crashes in VLTgenerator_table_
7475: MonetDB server crashes in __nss_database_lookup
7476: MonetDB server crashes in subrel_bin
7477: MonetDB server crashes in atom_cmp
7480: MonetDB server crashes in get_rel_count
7481: MonetDB server crashes in mvc_row_result_wrap
7482: MonetDB server crashes in bin_find_smallest_column
7483: MonetDB server crashes in rel_get_statistics_
7484: MonetDB server crashes in rel_optimize_projections_
7485: MonetDB server crashes in exp_setalias
7486: MonetDB server crashes in ALGgroupby
7488: MonetDB server crashes in strCmp
7497: Multi-column IN clause with value list produces syntax error
7500: request: ANALYZE statement should be allowed to be used in the body of a procedure or function or trigger.
7514: Nonexistent window function raises ParseException:SQLparser:42000!Query too complex: running out of stack space
7517: UNLOGGED tables don’t get cleaned up properly when DROPped.
7521: Unexpected result when using IS DISTINCT FROM
7522: Crash when creating view with HAVING
7523: Assertion failure when using CONTAINS
7524: Unexpected error when using NATURAL RIGHT JOIN
7525: Related to bug #7422 (variadic arguments in aggregate UDFs)
7527: Unexpected result when using IS DISTINCT FROM with RIGHT JOIN
7528: Assertion failure when using JAROWINKLER in ORDER BY clause
7529: Finding minimum value in reverse sorted column with NULL values at the end gives wrong result.
7530: Assertion failure when using JAROWINKLER with empty string
7533: DROP of a schema with CASCADE option drops tables, but bats are not removed.
7534: Unexpected result when using IS DISTINCT FROM with AND
7535: Assertion failure when using GROUP BY when CREATE VIEW
7538: BUG with decimal values
7539: Crash when using IS DISTINCT FROM with SIN
7540: Assertion failure when using STARTSWITH
7542: Nested query triggers an assert
7543: Unexpected result when using IS DISTINCT FROM with constants
7544: Unexpected result when using STARTSWITH
7545: Crash when creating view with GROUP BY
7547: drop login trigger causes server crash
7550: non-admin user can no longer query sys.statistics or information_schema.tables
7552: Unexpected result when using NULL constant in comparison
7553: Assertion failure when using INNER JOIN on STARTSWITH
7554: Unexpected result when using range comparison with NULL
7555: Unexpected result when casting integer to boolean in comparison
7556: Assertion failure when using STARTSWITH with view
7562: Assertion failure when comparing INTERVAL value
7566: After a while, all new sessions (connections) may get refused