Announcement: New Sep2022 Feature release of MonetDB suite

26 Sep 2022


      The MonetDB team at MonetDB Solutions BV is pleased to announce the
Sep2022 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/documentation/admin-guide/release-notes/sep2022/.
As usual, the download location is https://www.monetdb.org/downloads/.
The Sep2022 documentation can be found [here](https://www.monetdb.org/documentation-Sep2022/).
## Sep2022 Feature Release (11.45.7)
### Client Package
- Dumping of function GRANTs was improved by adding the types of the
  function (and procedure) arguments.
### ODBC Driver
- Improved SQLSetPos(). It now allows RowNumber to be 0 when Operation
  is SQL_POSITION.
- Improved ODBC Error/Warning messages. They now include the name of the
  Data Source as required by the ODBC specification:
  [MonetDB][ODBC driver VERSION][data-source-name] data-source-supplied-text
- Improved MonetDB ODBC Data Source Configuration dialog on MS Windows by
  hiding the typed in password text. It now shows dots for the characters.
  This fixes request  https://github.com/MonetDB/MonetDB/issues/7314
- Changed output of TABLE_CAT or PROCEDURE_CAT result columns as returned
  by ODBC functions: SQLTables(), SQLColumns(), SQLPrimaryKeys(),
  SQLForeignKeys(), SQLStatistics(), SQLTablePrivileges(),
  SQLColumnPrivileges(), SQLProcedures() and SQLProcedureColumns().
  They used to return the static database name but now they will return
  NULL as MonetDB does not support CATALOG objects or qualifiers.
- Removed the possibility to retrieve or set the CURRENT_CATALOG
  via SQLGetConnectAttr(hdbc, SQL_ATTR_CURRENT_CATALOG, ...) and
  SQLSetConnectAttr(hdbc, SQL_ATTR_CURRENT_CATALOG, ...) as MonetDB does
  not support CATALOG objects (no SQL support for: CREATE CATALOG abc
  or SET CATALOG abc) and therefore there is no CURRENT_CATALOG.
- Corrected ODBC functions SQLTablePrivileges() and SQLColumnPrivileges()
  for local temporary tables located in schema tmp. They did not return
  any rows when the temporary table had privileges set. Now they do return
  rows as expected.
- Improved SQLProcedures() and SQLProcedureColumns(). They now list
  information also for all built-in system procedures and functions, not
  only those created via SQL. Also corrected the value of ORDINAL_POSITION
  for scalar function arguments. It would start at 2 instead of 1.
- Extended output of SQLProcedures() and SQLProcedureColumns() resultsets
  with an extra column SPECIFIC_NAME. This column contains the name which
  uniquely identifies this procedure or function within its schema. As
  MonetDB supports overloaded procedures and functions, the combination of
  PROCEDURE_SCHEM and PROCEDURE_NAME is not enough to uniquely identify
  a procedure or function. This extra column allows you to correctly
  match the corresponding rows returned by SQLProcedureColumns() with the
  specific rows of SQLProcedures(). This extra column SPECIFIC_NAME is
  implemented similar to the JDBC DatabaseMetaData methods getProcedures()
  and getProcedureColumns().
- For SQLForeignKeys() corrected the output of columns UPDATE_RULE and
  DELETE_RULE. These columns used to always return 3 (= SQL_NO_ACTION)
  but now they will report the action codes as specified in the FOREIGN KEY
  CONSTRAINT construction.
- Fixed issue in function SQLProcedureColumns(). When the argument ColumnName
  was not empty it generated an invalid SQL query which failed with error:
   SELECT: no such column 'c.name'. This has been resolved.
- Corrected implementation of SQLGetInfo(hdbc, SQL_MAX_DRIVER_CONNECTIONS, ...).
  It used to always return 64. Now it returns the value from the MonetDB server.
- Changed the column names case of the result sets as returned by
  SQLTables(), SQLColumns(), SQLSpecialColumns(), SQLPrimaryKeys(),
  SQLForeignKeys(), SQLStatistics(), SQLTablePrivileges(),
  SQLColumnPrivileges(), SQLProcedures() and SQLProcedureColumns(). The
  column names where all in lowercase but the ODBC specification defines
  them in uppercase, so changed them to uppercase.
- Corrected SQLSpecialColumns(..., SQL_BEST_ROWID, ...). Previously it only
  returned rows when the table had a primary key. Now it also returns
  rows when a table has a unique constraint.
- Corrected SQLStatistics(..., SQL_INDEX_ALL, ...). Previously it only
  returned rows when the table had a primary or unique key. Now it also
  returns rows for indices which are not based on a key.
- Corrected SQLStatistics(..., SQL_ENSURE). It now returns CARDINALITY
  information for columns based on a primary/unique key. Previously it
  always returned NULL for the CARDINALITY result column.
- Corrected ODBC functions SQLPrimaryKeys(), SQLSpecialColumns() and
  SQLStatistics() for local temporary tables located in schema tmp. They did
  not return any rows when the temp table had a primary or unique key or
  index. Now they do return rows as expected.
### MonetDB Common
- Fixed a bug in ORDER BY with both NULLS LAST and LIMIT when the ordering
  was on an interger or integer-like column and NULL values are present.
- The median_avg and quantile_avg returned bogus results in the
  non-grouped case (i.e. something like SELECT sys.median_avg(i) FROM t).
- The abort_on_error parameter of all GDK-level functions has been removed.
  Errors (e.g. overflow) now also results in an error.
- Implemented BC/AD (and BCE/CE) suffixes when parsing dates.
- The interface for using strimps has not changed (create an imprint index
  on a column of a read only table), but now construction happens at the
  beginning of the first query that uses the strimp and is performed in
  a multithreaded manner.
- Implemented the use of strimps for NOT LIKE queries. The idea is to
  run the corresponding LIKE query using strimps and take the complement
  of the result. We keep around NULL values both during strimp filtering
  and during the pcre part of the LIKE query so that they get discarded
  automatically when we take the complement.
- The function BBPkeepref now gets a BAT pointer as argument instead of
  a bat id.
- Get rid of macro Tsize, use -&gt;twidth instead.
- Get rid of macro BUNlast, just use BATcount instead.
- The BLOB type has been moved into the GDK layer.
- When adding or subtracting months from a date or timestamp value,
  clamp the result to the calculated month instead of wrapping to the
  beginning of the next month.  See bug [7227](https://https://github.com/MonetDB/MonetDB/issues/7227).
- The median_avg and quantile_avg returned bogus results in the
  non-grouped case (i.e. something like SELECT sys.median_avg(i) FROM t).
### MonetDB5 Server
- Disabled volcano pipeline due to known issues.
### SQL Frontend
- The function sys.tracelog is now executable by anyone.  The function
  (and view of the same name) returns the tracing information of a query
  that was prepended with the TRACE keyword.
- Fixed a bug in COPY BINARY INTO where the input wasn't checked
  thoroughly enough.
- Extended the built-in profiler to emit non-MAL events related to query
  compilation, optimization, transactions and client connections. To
  minimize, simplify and optimize the process of generating and processing
  profiler output, only the events marking the end of an operation are
  emitted in most cases and the emitted json messages themselves are
  trimmed down to their essential fields. Furthermore, the MAL instruction
  profiler.openstream now requires an integer as a single argument, "0" for
  default behaviour or "4" to turn on the profiler in minimal mode,
  which causes it to only emit general events and excludes individual MAL
  instruction execution events from the profiler streams.
  The MAL instruction profiler.openstream with zero arguments is deprecated.
- Extended system view sys.users with columns: schema_path, max_memory,
  max_workers, optimizer and default_role.
  Extended system table sys.db_user_info with columns: schema_path,
  max_memory, max_workers, optimizer, default_role and password.
  The password is encrypted. This table can only be queried when the
  user has been granted the select privilege from monetdb.
- CREATE USER statement has been extended to take more optional arguments
  like MAX_MEMORY, MAX_WORKERS, OPTIMIZER, DEFAULT ROLE. ALTER USER statement
  has also been extended with DEFAULT ROLE.
- A new function sys.url_extract_host(string, bool) which returns the
  host name from the given URL has been implemented.  The bool argument,
  if true, causes the www. prefix to be removed.
- The user authentication tables are now administered by the SQL layer
  instead of the MAL layer.  This means that changing (adding, dropping,
  altering) user and role information is now transaction-safe.
- Added the UNLOGGED TABLE feature.
- Removed functions sys.index and sys.strings.
### Merovingian
- Added "loglevel" property for monetdbd logging (to merovingian.log).
  The loglevel can be set to: error or warning or information or debug.
  The loglevel property can be changed dynamically via command:
   monetdbd set loglevel=warning /path/to/dbfarm
  Default the loglevel is set to: information
  When loglevel is error, only errors are logged.
  When loglevel is warning, errors and warnings are logged.
  When loglevel is information, errors and warnings and information messages
  are logged.  When loglevel is debug, all messages are logged.
- There is now a loadmodules property that can be used to add --loadmodule
  arguments to the mserver5 command line.  See the monetdb and mserver5
  manual pages.
- In certain cases (when an mserver5 process exits right after producing
  a message) the log message was logged over and over again, causing
  monetdbd to use 100% CPU.  This has been fixed.
### Bug Fixes
- [6982](https://github.com/MonetDB/MonetDB/issues/6982): Wrong constraint name in error message of constraint violation
- [7209](https://github.com/MonetDB/MonetDB/issues/7209): Configuration option for merovingian.log
- [7225](https://github.com/MonetDB/MonetDB/issues/7225): Invalid memory access when extending a BAT during appends
- [7227](https://github.com/MonetDB/MonetDB/issues/7227): Date calculations, bug or feature
- [7273](https://github.com/MonetDB/MonetDB/issues/7273): Concurrent reads and writes causes "BATproject2: does not match always" error
- [7282](https://github.com/MonetDB/MonetDB/issues/7282): call sys.dump_table_data(); fails
- [7285](https://github.com/MonetDB/MonetDB/issues/7285): C-UDFs: aggr_group.count has wrong value (number of input rows instead of number of groups).
- [7296](https://github.com/MonetDB/MonetDB/issues/7296): Implictly cast a timestamp string to DATE when appropriate
- [7297](https://github.com/MonetDB/MonetDB/issues/7297): Parsing partial dates behaves unpredictable
- [7306](https://github.com/MonetDB/MonetDB/issues/7306): ODBC Driver Assertion `stmt-&gt;Dbc-&gt;FirstStmt' Failed
- [7314](https://github.com/MonetDB/MonetDB/issues/7314): ODBC Driver : please mask/hide password
- [7318](https://github.com/MonetDB/MonetDB/issues/7318): distinct in a subquery not working properly

Sjoerd Mullender

tags (0)

participants (1)