Announcement: New Mar2018 Feature release of MonetDB suite

Sjoerd Mullender sjoerd at
Thu Mar 29 15:25:37 CEST 2018

The MonetDB team at CWI/MonetDB BV is pleased to announce the
Mar2018 feature release of the MonetDB suite of programs.

More information about MonetDB can be found on our website at

For details on this release, please see the release notes at

As usual, the download location is <>.

Some notable changes:

- Code base requires a C99 compiler.

- The internal representation of REAL and DOUBLE NULL values has
  changed.  Before they were represented as the smallest C float/double
  finite value (-FLT_MAX and -DBL_MAX).  Now they are represented by a
  NaN (not-a-number) value.  This requires a database upgrade which is
  done automatically at startup.  Be advised that it is recommended to
  make a backup before upgrading.

- Various extensions to SQL have been implemented.

For more details, see below.

Mar 2018 feature release (11.29.3)

   MonetDB5 Server
     * Implemented function pcre.replace_first which is like pcre.replace,
       except it only replaces the first match.
     * The EXPLAIN command now shows all the MAL type resolutions, because
       in general users may not be aware of their signatures. It also
       simplifies programs to analyze such plans.
     * Implemented versions of group.(sub)group(done) that don't return a
     * Removed MAL functions streams.socketRead, streams.socketReadBytes,
       streams.socketWrite, and streams.socketWriteBytes.
     * Removed MAL functions streams.openRead(s:streams):streams and
     * Lots of changes to streamline the internal error handling. In
       principle, all errors should now include a SQLSTATE error code (see
       the SQL standard).

   Build Environment
     * Added the .pdb files needed for debug symbols to the Windows
       installer for MonetDB/SQL.

     * mclient's execution time profiling options and output format have
       been changed. Instead of implicitly via the "--interactive"/"-i"
       option (or when using an interactive mclient console), execution
       time profiling is now controlled via an explicit
       "--timer=timermode"/"-t timermode" command-line option, or a "\t
       timermode" command in the interactive mclient console. The default
       (also in the interactive mclient console) is now timermode "none",
       i.e., no timing information is given. Timermode "clock" activates
       client-side wall-clock timing ("clk") in "human-friendly" format
       much like the interactive mode did before. Timermode "performance"
       also provides detailed server-side timings: "sql" is the time to
       parse the SQL query, optimize the logical relational plan and
       create the initial physical (MAL) plan; "opt" is the time to
       optimize the physical (MAL) plan; "run" is the time to execute the
       physical (MAL) plan. With timermode "performance" all server-side
       timings and the client-side wall-clock time are given in
       milliseconds (ms). Note that the client-measured wall-clock time
       "clk" is reported per query only when options "--interactive" or
       "--echo" are used, because only then does mclient send individual
       lines (statements) of the SQL script to the server. Otherwise,
       mclient sends the SQL script in large(r) batch(es) to the server,
       and, thus, only the total wall-clock time per batch is measured and
       reported. The server-measured detailed performance timings "sql",
       "opt", "run" are always measured and reported per query. Also, all
       timing information is now given on a separate line and sent to
       stderr rather than stdout.
     * Some types and constants were moved from configure (and hence
       monetdb_config.h) to gdk.h. In particular, the types "lng" and
       "ulng" have been moved and can therefore no longer be used by code
       that doesn't (ultimately) include gdk.h. Just use int64_t instead.
       A bunch of format defines have been removed: SZFMT, SSZFMT, PTRFMT,
       PDFMT. Just use the C standard codes for those (%zu, %zd, %p, %td).
       The define for printing a lng (LLFMT) was also moved. Use PRId64
       for printing int64_t in code not using gdk.h. Removed all
       references to __int64 and long long (use int64_t instead).

     * Add daemon commands for starting/stopping collection of profiler
       (stethoscope) logs.

   Client Package
     * ODBC: The driver function SQLProcedureColumns was implemented.
     * ODBC: Changed table types as used by SQLTables from "LOCAL
       "GLOBAL TEMPORARY TABLE" respectively.
     * ODBC: Initial support for the HUGEINT type in SQL was added. Any
       value with type HUGEINT can be retrieved in a C variable with type
       SQL_C_CHAR or SQL_C_WCHAR. A value of type HUGEINT can be retrieved
       in other C types as long as they fit, the largest C type supported
       being a 64 bit integer (equivalent to BIGINT).
     * The functions in the mapi library that require 64 bit integers now
       use the standard type int64_t instead of the non-standard
       mapi_int64. This requires a compilation environment that has the
       stdint.h include file (standardized in C99). Compilation of the
       library also requires the inttypes.h include file (also
       standardized in C99).
     * Add a new pretty printing option to stethoscope Running stethoscope
       with the flag -j will produce not pretty printed output (one json
       object per line). Running with the -y flag will produce pretty
       printed output. Running with neither will produce the legacy, line
       oriented format

     * The three mapi_explain* functions and mapi_trace don't return any
       useful information, so they now return void.
     * The functions mapi_error_str and mapi_result_error now return const
       char * instead of plain char * to indicate that the returned data
       belongs to the library and should not be changed or freed by the
     * New function const char *mapi_result_errorcode(MapiHdl) which
       returns the SQLSTATE code if available when an error has occurred.

     * The interface of mnstr_fgetpos and mnstr_fsetpos was changed to
       look more like the standard C functions fsetpos and fgetpos: they
       both have a second argument "pointer to fpos_t".
     * Removed function wbstream.
     * Removed functions udp_rastream and udp_wastream.
     * Removed functions socket_rstream and socket_wstream.
     * Removed functions append_wstream and append_wastream.
     * Removed functions mnstr_rstream and mnstr_wstream.

     * The function mcrypt_getHashAlgorithms now returns a static,
       constant string, so the result should not be modified or freed.

   MonetDB Common
     * Changed return type of function void_replace_bat from BUN to
       gdk_return: it now only returns whether the operation succeeded or
     * Changed the return type of BATroles from void to gdk_return: it can
       fail due to malloc failure.
     * Removed functions ALIGNsetH, ALIGNsetT, and CREATEview_ (mind the
       underscore). The first can easily be replace by using BAThseqbase
       (that's all it did), the second was only used once, and the third
       can be replace by VIEWcreate.
     * Removed unused functions BATmemsize and BATvmsize.
     * Removed the tnodense property: it was maintained but never actually
       used, not even stored.
     * The NIL representation of the internal flt and dbl types was
       changed from the smallest representable finite value to NaN
     * Changed the interface of ATOMformat and VALformat: they now return
       a pointer to the allocated string.
     * The length "method" for atoms now returns a size_t, the "len" field
       of a ValRecord is now a size_t, the "size" field of the atomDesc
       structure is now unsigned short.
     * Removed the "align" field from the ATOM descriptor (atomDesc)
     * The atomtostr and atomfromstr "methods" for atoms now return
       ssize_t and require a pointer to size_t for the size of the buffer.
     * The atom tostr and fromstr "methods" now always return -1 on error.
       A return value greater than 0 is normal, a return value of 0 is not
       normal, but technically not an error.

   Testing Environment
     * Added a --data_path option to that defines an external
       data repository. See the commit message of c484932c7fd8 for more

     * Extended support to use CREATE ORDERED INDEX on columns of type:
       char, varchar, clob, blob, url, json, inet and uuid.
     * Added new system view: sys.ids which contains all database objects
       ids which can be used in sys.dependencies table.
     * Added new system view: sys.dependencies_vw which shows all data of
       sys.dependencies including names on objects, object types and
       dependency types.
     * Added 25 new system views for finding out dependencies between
       database objects. These new dependency views improve, extend and
       replace the 17 sys.dependencies_X_on_Y() functions as previously
       defined in 21_dependency_functions.sql. Those
       sys.dependencies_X_on_Y() functions are now marked as deprecated.
     * Added new system view: sys.roles which contains all defined roles.
     * Added new system view: sys.var_values which shows the values for
       system variables.
     * Added support for COMMENT ON statements using SQL syntax: COMMENT
       description text' | NULL | '' } ; For COLUMN the qname can be
       "table_name"."column_name" or fully qualified as in:
       "schema_name"."table_name"."column_name". For FUNCTION, PROCEDURE,
       AGGREGATE, FILTER FUNCTION and LOADER the qname may need to include
       the signature (argument types) to be able to differentiate between
       multiple overloaded functions which have the same name and schema.
       By specifying IS NULL or IS '' you remove the comment for the
       database object. If a database object is dropped, the associated
       comment is also removed. Note: it is not allowed or possible to add
       comments for temporary tables or objects in schema "tmp". The sql
       catalog has been extended with system table: sys.comments. The
       keyword 'COMMENT' has now become a reserved keyword.
     * Removed system function sys.environment(). It was a duplicate of
       system function sys.env().
     * Implemented behavior for DROP SCHEMA my_schema RESTRICT command.
       Previously the RESTRICT keyword was accepted but not obeyed. It
       would always do a CASCADE operation. Also the default behavior of
       DROP SCHEMA my_schema command is now changed into RESTRICT behavior
       (was CASCADE).
     * The internal NULL representation of the REAL (FLOAT) and DOUBLE
       types was changed from the smallest representable finite value to
       NaN (not-a-number).
     * A column default value can be used in a UPDATE statement: UPDATE
       tname SET cname = DEFAULT, and INSERT statements: INSERT INTO tname
       VALUES (..., DEFAULT, ...)
     * Added support for TRUNCATE statements conforming to the SQL:2008
       IDENTITY ] [ RESTRICT | CASCADE ] In a TRUNCATE statement a
       'CONTINUE IDENTITY' or 'RESTART IDENTITY' clause can be passed to
       restart or not, being the former the default one. The 'CASCADE'
       option instructs to truncate referencing table(s) also if the
       referencing table(s) have foreign key references to this table. The
       default behavior is 'RESTRICT'. Note: it is possible to use
       TRUNCATE statements in a transaction and thus to rollback the
       effects of a truncate. The keywords 'TRUNCATE' and 'CONTINUE' have
       now become reserved keywords.
     * Added possibility to GRANT or REVOKE a TRUNCATE privilege to a user
       or role.
     * Added possibility to define a TRIGGER on a TRUNCATE event.
     * Added possibility to specify 'OR REPLACE' in following CREATE
       commands: CREATE [ OR REPLACE ] VIEW qname ... CREATE [ OR REPLACE
       ] TRIGGER qname ...
     * Added possibility to specify 'IF EXIST' in following DROP commands:
       qname ... DROP FILTER FUNCTION [ IF EXISTS ] qname ... DROP LOADER
       [ IF EXISTS ] qname ... DROP PROCEDURE [ IF EXISTS ] qname ... DROP
       TRIGGER [ IF EXISTS ] qname ...
     * Lots of changes to streamline the internal error handling. In
       principle, all errors should now include a SQLSTATE error code (see
       the SQL standard).
     * Added support for extracting the quarter (number between 1 and 4)
       of a date or a timestamp or a timestamp with timezone in SQL:
       EXTRACT ( QUARTER FROM my_date_expr ). Added support for extracting
       the week (number between 1 and 53) of a date or a timestamp or a
       timestamp with timezone in SQL: EXTRACT ( WEEK FROM my_date_expr ).
       Added support for scalar functions: quarter(date_expr),
       quarter(timestamp_expr) and quarter(timestamptz_expr).

   Bug Fixes
     * 3574: Add support for: create OR REPLACE view ...
     * 3824: Created table not visible from ODBC
     * 3831: Extend date part extraction and date formating functions to
       support more formats like "quarter", "iso year/week"
     * 6244: Add support for: TRUNCATE TABLE
     * 6249: DEFAULT in row-values missing (sqlsmith)
     * 6346: BATsort returns GDK_SUCCEED when **sorted bat is null
     * 6438: Implement functionality to enforce the restrict option in:
     * 6507: Column Header coming with trailing spaces in compiled version
       of MonetDB 11.27.9/11
     * 6513: Sqlitelogictest: Wrong MAL plan generation for column product
     * 6526: Crash using aggregate function inside a case statement in
       having clause
     * 6529: Sqlitelogictest crash in select query with IN operator and
     * 6530: Sqlitelogictest: select query with NOT IN giving wrong
     * 6532: copy into ignore null as directive if first column doesn't
       come from file
     * 6534: [Mar2018]: mclient -f tab / --format=tab complains about
       "unsupported formatter"
     * 6535: [Mar2018]: mclient -t / --timer does not work as documented
     * 6536: [Mar2018]: timing output of mclient -t / --timer= should go
       to stderr rather than stdout
     * 6537: [Mar2018]: mclient's default timing mode should (again) be
       "none" rather than "clock"
     * 6541: [Mar2018]: mclient reports incorrect wall-clock time
     * 6542: assertion failure when querying: select count(*) from
     * 6543: Mar2018: truncate on SQL system tables should NOT be allowed
     * 6545: Sqlitelogictest crash in IN query
     * 6546: Sqlitelogictest crash in IN query with division
     * 6547: OS-dependent behaviour for ilike
     * 6548: Select from remote table leaves session open
     * 6549: Add log messages when listen fails
     * 6550: Sqlitelogictest crash on complex CASE statement
     * 6551: Sqlitelogictest wrong NULL value cast
     * 6552: Sqlitelogictest crash on complex case statement
     * 6553: Sqlitelogictest crash on aggregation with having statement
     * 6554: Sqlitelogictest crash on complex case statement
     * 6555: Sqlitelogictest wrong result set generated from complex case
     * 6556: Sqlitelogictest division by zero on COALESCE call
     * 6557: Sqlitelogictest crash on aggregation query with not in
     * 6559: rows in sys.statistics are not removed when a temporary table
       is dropped.
     * 6560: Sqlitelogictest crash on group by query with having in
     * 6561: Sqlitelogictest crash on group by query with having not in

More information about the developers-list mailing list