- users-list - monetdb.org

Announcement: New Jun2023 Feature release of MonetDB suite
by Panagiotis Koutsourakis 29 Jun '23

29 Jun '23

The MonetDB team at MonetDB Solutions BV is pleased to announce the Jun2023 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/jun2023/>. As usual, the download location is <https://www.monetdb.org/downloads/>. The Jun2023 documentation can be found [here](https://www.monetdb.org/documentation-Jun2023/). ## Jun2023 Feature Release (11.47.3) ### Client Package - The COPY INTO from file ON CLIENT was extended to also look for a relative path name relative to the file from which the query was read. This is only possible if the name of the query file is known, so when it is specified on the command line or read using the interactive \< command. ### Mapi Library - Deprecate mapi_setfilecallback() in favor of mapi_setfilecallback2() which can handle binary downloads. For the time being, the old callback still works. ### ODBC Driver - Extended Windows MonetDB ODBC Data Source setup program with option to specify a logfile to enable tracing of ODBC Driver API calls. On other platforms users can edit the odbc.ini file and add a line: logfile=/home/username/odbctrace.log When a logfile is specified it will start logging the ODBC Driver API calls to the logfile after a new connection is made via SQLConnect() or SQLDriverConnect() or SQLBrowseConnect(). Note that enabling ODBC logging will slow down the performance of ODBC applications, so enable it only for analysing ODBC Driver problems. - Enhanced SQLTables() by adding support for table type names: 'BASE TABLE', 'GLOBAL TEMPORARY' and 'LOCAL TEMPORARY' in parameter TableType. These are synonyms of: 'TABLE', 'GLOBAL TEMPORARY TABLE' and 'LOCAL TEMPORARY TABLE'. ### MonetDB5 Server - The MAL debugger code has been removed. ### SQL Frontend - Add MAX_MEMORY and MAX_WORKERS options to the ALTER USER statement - Function 'similarity(x string, y string)' marked as deprecated and will be removed in the next release. - New functionality for string matching and similarity: startswith, endswith, contains, Levenshtein distance and Jaro-Winkler similarity. The functions startswith, endswith and contains have a version where a case insentive flag can be used. Also, there are new custom join functionality for startswith, endswith, contains, Levenshtein distance and Jaro-Winkler similarity. - Renamed previous Levenshtein distance to Damerau-Levenshtein distance. - New string function that transform from UTF-8 encoding to Ascii called asciify. - Session timeout feature improvement to start evaluating from the moment the procedure is called, instead of beginning of the session. - Queries stopped with the stop procedure are now marked as 'aborted' 'finished'. - The DEBUG statement has been removed. - SQL function sys.queue() overloaded with sys.queue(username string), SYSADMIN only, allowing to filter the global queue by username or use 'ALL' to retrieve the global queue. Calling the function without arguments returns the queue for the current user. - SQL procedures sys.pause(tag bigint), sys.resume(tag bigint), sys.stop(tag bigint) overloaded with sys.pause(tag bigint, username string), sys.resume(tag bigint, username string) and sys.stop(tag bigint, username string), SYSADMIN only, allowing to pause, resume and stop query executions by TAG, USERNAME. The call without arguments is a public procedure giving access to users to pause, resume and stop their own query executions. - Added support of ODBC escape sequences syntax to SQL layer. Now all clients (including ODBC/JDBC/pymonetdb) can use them without further processing. For details on ODBC escape sequences syntax see: https://learn.microsoft.com/en-us/sql/odbc/reference/appendixes/odbc-escape… - It is no longer allowed to create a merge table or remote table or replica table or unlogged table in schema "tmp". The tmp schema is reserved for temporary objects only, such as local/global temp tables. - System views sys.dependency_tables_on_functions and dependency_views_on_functions have been extended with column: function_id. - When loading data using COPY BINARY INTO, apply default values instead of just inserting NULLs. - When loading data using COPY BINARY INTO, validate DECIMAL(prec,scale) and VARCHAR(n) column width. - When loading data using COPY BINARY INTO, string used to have their line endings converted from CR LF to LF. Do not do this, it is the responsibility of the client. - Implemented dumping binary data using COPY SELECT ... INTO BINARY <file(s)>. - Removed code for Workload Capture and Replace, including system schemas "wlc" and "wlr" and the objects in those schemas. The code was experimental, and it didn't work out. A different approach will be taken. ### Merovingian - The monetdb get, inherit, lock and release commands are extended with ‘apply-to-all’ syntax similar to related functionality: Usage: monetdb set property=value [database ...] sets property to value for the given database(s), or all Usage: monetdb inherit property [database ...] unsets property, reverting to its inherited value from the default configuration for the given database(s), or all Usage: monetdb lock [-a] database [database...] Puts the given database in maintenance mode. Options: -a locks all known databases Usage: monetdb release [-a] database [database ...] Brings back a database from maintenance mode. Options: -a releases all known databases ### Bug Fixes - [7311](https://github.com/MonetDB/MonetDB/issues/7311): Missing `REGEXP_REPLACE` function. - [7344](https://github.com/MonetDB/MonetDB/issues/7344): Database upgrade failure due to user object dependency on system procedure - [7348](https://github.com/MonetDB/MonetDB/issues/7348): Subquery inside case always evaluated - [7378](https://github.com/MonetDB/MonetDB/issues/7378): MonetDB server crashes at sql_trans_copy_key - [7379](https://github.com/MonetDB/MonetDB/issues/7379): MonetDB server 11.46.0 crashes at cs_bind_ubat - [7380](https://github.com/MonetDB/MonetDB/issues/7380): MonetDB server 11.46.0 crashes at `BLOBcmp` - [7381](https://github.com/MonetDB/MonetDB/issues/7381): MonetDB server 11.46.0 crashes at `log_create_delta` - [7382](https://github.com/MonetDB/MonetDB/issues/7382): MonetDB server 11.46.0 crashes at `gc_col` - [7383](https://github.com/MonetDB/MonetDB/issues/7383): MonetDB server 11.46.0 crashes at `list_append` - [7384](https://github.com/MonetDB/MonetDB/issues/7384): MonetDB server 11.46.0 crashes at `__nss_database_lookup` - [7386](https://github.com/MonetDB/MonetDB/issues/7386): MonetDB server 11.46.0 crashes in `rel_deps` - [7387](https://github.com/MonetDB/MonetDB/issues/7387): MonetDB server 11.46.0 crashes in `rel_sequences`

1 0

Announcement: New Sep2022-SP3 Bugfix release of MonetDB suite
by Sjoerd Mullender 08 Jun '23

08 Jun '23

The MonetDB team at MonetDB Solutions BV is pleased to announce the Sep2022-SP3 bugfix 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/sep2022/>. As usual, the download location is <https://www.monetdb.org/downloads/>. ### Client Package - If the number of rows in mclient is set to 0 (using either --rows=0 option or \r0 on the mclient command line), the internal pager is used and it then uses the height of the terminal window. ### MonetDB Common - Warnings and informational messages are now sent to stdout instead of stderr, which means that monetdbd will now log them with the tag MSG instead of ERR. - Fixed parsing of the BBP.dir file when BAT ids grow larger than 2**24 (i.e. 100000000 in octal). - Fixed yet another occurrence of a missing .tailN file. This one could happen if a string bat was appended to in stages so that between appends the column was committed. If an append caused both a realloc of the tail heap because it was getting longer and a realloc because it got wider, the file might get removed before the GDK level commit happened. - When processing the WAL, if a to-be-destroyed object cannot be found, don't stop, but keep processing the rest of the WAL. - A race condition was fixed where certain write-ahead log messages could get intermingled, resulting in a corrupted WAL file. - If opening of a file failed when it was supposed to get memory mapped, an incorrect value was returned to indicate the failure, causing crashes later on. This has been fixed. - A race condition was fixed that could result in a missing tail file for a string bat (i.e. a file with .tail1, .tail2, or .tail4 extension). - When saving a bat failed for some reason during a low-level commit, this was logged in the log file, but the error was then subsequently ignored, possibly leading to files that are too short or even missing. - The write-ahead log (WAL) is now rotated a bit more efficiently by doing multiple log files in one go (i.e. in one low-level transaction). ### MonetDB5 Server - Client connections are cleaned up better so that we get fewer instances of clients that cannot connect. ### SQL Frontend - Fixed a regression introduced in the previous build: when a table is dropped in the same transaction where it was created, the data wasn't removed from disk. - When creating a hot snapshot, allow other clients to proceed, even with updating queries. - Increased the size of a variable counting the number of changes made to the database (e.g. in case more than 2 billion rows are added to a table). - Improved cleanup after failures such as failed memory allocations. - An insert into a table from which a column was dropped in a parallel transaction was incorrectly not flagged as a transaction conflict. - Fix bug where boolean NULLs were not recognized by COPY BINARY INTO ### Bug Fixes - [7360](https://github.com/MonetDB/MonetDB/issues/7360): Aggregates returning string crash - [7361](https://github.com/MonetDB/MonetDB/issues/7361): Table data is lost on DB restart, but only when a table has 2147483647 or more rows.

1 0

Possible BUG??
by Ariel Abadi 08 Mar '23

08 Mar '23

Hi All! I used to have a query that was working in a prior version (11.39.13), but now that I have upgraded to the newest version (11.45.13) this same query is not working any more. The query is the following: SELECT nrm_tim_date AS nrm_tim_date, ( (SELECT SUM(nrm_rse_amount_usd * exc.exc_rate) from sb_glob.exc_exchange exc where exc.exc_currency = 'USD') ) AS net_profit, SUM(nrm_rse_amount_usd) AS nrm_rse_amount_usd FROM sb_traf.cubm_202303 AS nrm GROUP BY nrm_tim_date The error Im having is "SQL Error [42000]: SELECT: subquery uses ungrouped column "nrm.nrm_rse_amount_usd" from outer query" This exact same query does work on version 11.39.13. Can someone help me with this? Thks a lot in advance ARIEL ABADI Direct: +54 11 5279.2054 Mobile: +54 9 11 6050.0101 *Email*: aabadi(a)starbi.com *Web*: www.starbi.com

2 1

Announcement: New MonetDB JDBC driver and JdbcClient released
by Martin van Dinther 02 Mar '23

02 Mar '23

The MonetDB team at MonetDB Solutions BV is pleased to announce a bug fix release of the MonetDB JDBC driver (monetdb-jdbc-3.3.jre8.jar) and JdbcClient CLI program (jdbcclient.jre8.jar). Besides bug fixes the new release improves performance and compliance. For a list of all changes see: https://www.monetdb.org/downloads/Java/ChangeLog We recommend all users of MonetDB JDBC driver and JdbcClient program to download and use the latest jar files. The download location is: https://www.monetdb.org/downloads/Java/ For information and tips on using the MonetDB JDBC driver, read: https://www.monetdb.org/downloads/Java/release.txt For information on using the MonetDB JDBC driver to execute COPY INTO <table> FROM <file> ON CLIENT ... and/or COPY <SELECT query> INTO <file> ON CLIENT ... commands for fast client side CSV file data import and export, read: https://www.monetdb.org/downloads/Java/onclient.txt Reminder: since release monetdb-jdbc-3.0.jre8.jar the MonetDB JDBC Driver class name has been changed to: org.monetdb.jdbc.MonetDriver It used to be nl.cwi.monetdb.jdbc.MonetDriver which is marked as deprecated and will be removed in a future release. Please use the new driver class name asap in your configuration files/scripts and/or Java code.

1 0

Announcement: New Sep2022-SP2 Bugfix release of MonetDB suite
by Sjoerd Mullender 31 Jan '23

31 Jan '23

The MonetDB team at MonetDB Solutions BV is pleased to announce the Sep2022-SP2 bugfix 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/sep2022/>. As usual, the download location is <https://www.monetdb.org/downloads/>. ### ODBC Driver - A crash in the ODBC driver was fixed when certain unsupported functions where used in a {fn ...} escape. - Prepare of a query where the sum of the number of parameters (question marks in the query) and the number of output columns is larger than 100 could fail with an unexpected error. This has been fixed. ### MonetDB Common - Fixed a race condition that could lead to a bat being added to the SQL catalog but nog being made persistent, causing a subsequent restart of the system to fail (and crash). - Fixed a race condition where a hash could have been created on a bat using the old bat count while in another thread the bat count got updated. This would make the hash be based on too small a size, causing failures later on. - When extending a bat failed, the capacity had been updated already and was therefore too large. This could then later cause a crash. This has been fixed by only updating the capacity if the extend succeeded. ### SQL Frontend - Fixed a regression where when there are multiple concurrent transactions, the dependencies weren't checked properly. - Added some error checking to prevent crashes. Errors would mainly occur under memory pressure. - Fixed cleanup after a failed allocation where the data being cleaned up was unitialized but still used as pointers to memory that also had to be freed. - Fixed a double cleanup after a failed allocation in COPY INTO. The double cleanup could cause a crash due to a race condition it enabled. ### Bug Fixes - [7343](https://github.com/MonetDB/MonetDB/issues/7343): GDKmmap requesting 0 virtual memory - [7347](https://github.com/MonetDB/MonetDB/issues/7347): A bug where an exception occurs even though it is a query with normal syntax (Merge Table)

1 0

Announcement: New Sep2022-SP1 Bugfix release of MonetDB suite
by Sjoerd Mullender 09 Dec '22

09 Dec '22

The MonetDB team at MonetDB Solutions BV is pleased to announce the Sep2022-SP1 bugfix 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/sep2022/>. As usual, the download location is <https://www.monetdb.org/downloads/>. ### Client Package - Also dump the new options of CREATE USER. ### ODBC Driver - Fixed issue with generated raw strings prefix when ODBC driver is used against a server older than Jun2020 (11.37). ### MonetDB Common - On Windows, use the wide-character interface of system calls when dealing with the environment (i.e. file names and getenv()). - Memory leaks have been fixed. - Improved maintenance of the estimated number of distinct values in BATs. The estimate helps in deciding which low-level algorithm to use. - Offset heaps (.tailN files) were growing too fast and unnecessarily under certain conditions. This has been fixed. Also, when such too large files are now loaded into the system, it is recognized they are too large and they are truncated to a more reasonable size. ### MonetDB5 Server - Fixed a crash when the server runs out of client contexts (i.e. more concurrent clients than the server is configured to handle). - A race condition in the SHA hash code was fixed which resulted in occasional failed connection attempts when they occurred concurrently. - Fix a bug where the MAL optimizer would use the starttime of the previous query to determine whether a query timeout occurred. ### SQL Frontend - Improved the handling of the "idle" value in the sys.sessions function and view. ### Merovingian - Stop logging references to monetdbd's logfile in said logfile. ### Bug Fixes - [7330](https://github.com/MonetDB/MonetDB/issues/7330): Creating temporary table fails after reconnect - [7333](https://github.com/MonetDB/MonetDB/issues/7333): DLLs fail to load on Windows with accented characters in path - [7336](https://github.com/MonetDB/MonetDB/issues/7336): Selecting from a literal-value table returns wrong values - [7339](https://github.com/MonetDB/MonetDB/issues/7339): MonetDB corrupted state after SIGKILL - [7342](https://github.com/MonetDB/MonetDB/issues/7342): column which datatype is double couldn't group or aggregation in version 11.45.7

1 0

Re: JSON column disk space
by Panagiotis Koutsourakis 07 Oct '22

07 Oct '22

Hello Alberto, I am looking into this issue and I would like to give you a small update and ask a couple of questions. Some background first. JSON objects are stored in MonetDB as strings. This creates overhead from the object keys and from numeric values that are now stored as strings. If I understand your issue correctly you don't have the chance to commit the reconstructed JSON column. Is this correct? From some tests I performed, I noticed that even before committing, disk usage goes up slowly. This is probably unrelated to JSON itself although I have not had a chance to verify this yet. So I think the most relevant question at this stage is how long are the keys in the JSON objects? I think that these are the most likely cause for the large overhead you are observing. Best regards, Panos. On 2022-09-28 21:17, Alberto Ferrari wrote: > Niels, > It's hard to me to create a demo script, but at leats I'd want to know > if it's a known "issue" (maybe it's not an issue, just as Monet works > internally) > > I can tell you that a table with 100 columns (ints, varchars, dates, > etc) with 30 million records, may occupy 30 Gb of disk. > When I merge some of these columns into one new json column -(i.e. 5 > single columns into one object like {col1:value, col2:value...}- and > dropped the 5 old columns (already merged in json), the table now > grows upto 100 Gb of disk. > > Thanks > > > El jue, 22 sept 2022 a la(s) 10:35, Niels Nes > (niels.nes(a)monetdbsolutions.com) escribió: >> >> >> Alberto >> >> Do you have some script which replicates this issue? >> And could you post the issue on the github issues? >> >> Niels >> >> On Thu, Sep 22, 2022 at 07:57:07AM -0300, Alberto Ferrari wrote: >> > Hi all: >> > >> > Monet version 11.35.19 (Nov2019-SP3) >> > >> > We realized that json columns in any table produces bigger stored >> > disk, no matter how many rows has big data. >> > It seems like depends on rows with different values in same json >> > column: if many rows has different values, then the space on disk >> > increases exponentially. So we can't use jaon columns in big tables, >> > since we run out of space disk in short time. >> > (also tested with 11.43.5 -Jan2022, same issue) >> > >> > Is there any solution to this problem? >> > Tanks! >> > Alberto >> > _______________________________________________ >> > users-list mailing list -- users-list(a)monetdb.org >> > To unsubscribe send an email to users-list-leave(a)monetdb.org >> >> _______________________________________________ >> users-list mailing list -- users-list(a)monetdb.org >> To unsubscribe send an email to users-list-leave(a)monetdb.org > _______________________________________________ > users-list mailing list -- users-list(a)monetdb.org > To unsubscribe send an email to users-list-leave(a)monetdb.org

4 4

JSON column disk space
by Alberto Ferrari 28 Sep '22

28 Sep '22

Hi all: Monet version 11.35.19 (Nov2019-SP3) We realized that json columns in any table produces bigger stored disk, no matter how many rows has big data. It seems like depends on rows with different values in same json column: if many rows has different values, then the space on disk increases exponentially. So we can't use jaon columns in big tables, since we run out of space disk in short time. (also tested with 11.43.5 -Jan2022, same issue) Is there any solution to this problem? Tanks! Alberto

2 4

Announcement: New Sep2022 Feature release of MonetDB suite
by Sjoerd Mullender 26 Sep '22

26 Sep '22

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 ->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->Dbc->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

1 0

Announcement: New Sep2022 Feature release of MonetDB suite
by Sjoerd Mullender 26 Sep '22

26 Sep '22

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 ->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->Dbc->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

1 0