The MonetDB team at MonetDB BV is pleased to announce the
Oct2020 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/Downloads/ReleaseNotes>.
As usual, the download location is <https://www.monetdb.org/downloads/>.
Oct 2020 feature release (11.39.5)
MonetDB5 Server
* The settings for specifying how mserver5 should listen to "The
Internet" have been overhauled. See the manual for details. In
brief, mapi_autosense, mapi_ipv6 and mapi_open are gone. If
mapi_listenaddr equals "localhost" or "all", we listen to both IPv4
and IPv6 (if available), if "127.0.0.1" or "0.0.0.0", we listen to
IPv4 only, if "::1" or "::" we listen to IPv6 only. The first of
each pair is loopback interface only, the second is all interfaces.
If mapi_listenaddr is "none", then no IP port is opened, you need
to use a UNIX domain socket. If mapi_port is 0, we let the
operating system choose a free port (like mapi_autosense). Default
behavior has not changed.
Whole System
* Finished a first version of the new monitoring function
user_statistics(), which is only intended for the DBAs. For each
database user who has logged in during the current mserver5
session, it returns "username": login name of the database user,
"querycount": the number of queries this user has executed since
his/her first login, "totalticks": the total execution time (in
microsecond) of the queries ran by this user, "maxquery": the query
with the longest execution time (if two queries have the same
execution time, the newer overwrites the older), "maxticks": the
execution time of the 'maxquery' (in microsecond), "started": the
start timestamp of the 'maxquery', "finished": the finish timestamp
of the 'maxquery'.
* Removed support for LiDAR data, that is the SQL procedures
sys.lidarattach, sys.lidarload, and sys.lidarexport.
* The sys.queue() has been turned into a circular buffer to allow for
inspection of both active, paused and recently executed queries.
Client Package
* mclient and msqldump now also look in $XDG_CONFIG_HOME for the
monetdb configuration file.
SQL Frontend
* Extended the system monitor with a table-returning function
user_statistics() which keeps some statistics for each SQL user,
e.g. the user's query count, total time spent, and maximal query
seen.
* Removed '@' syntax used to refer into a variable in a query. It was
a non-standard method, which was replaced by a schema addition to
variables. Variables in the global scope now have schema. All
default global variables are set under schema "sys". However
variables inside PSM don't have a schema, because there are no
transaction semantics inside PSM at the moment.
* Removed declared variables and tables from the global scope. They
were transaction agnostic and incompatible with the SQL standard,
i.e. they are valid exclusively under PSM (e.g. functions,
procedures and triggers).
* Scoping semantics were added for both variables and tables.
Variables with the same name at a query are now resolved under the
following precedence rules: 1. Tables, Views and CTEs at the FROM
clause. 2. Variable declared in the body of function/procedure,
i.e. local variable. 3. Function/procedure parameter. 4. Variable
from the global scope. Tables with the same name now have the
following precedence rules at a SQL query: 1. Table declared in the
body of function/procedure, ie local table. 2. Temporary table. 3.
Table from the current session schema. This means the query: SELECT
* FROM "keys"; will list keys from temporary tables instead of
persisted ones, because "keys" table is available for both "sys"
and "tmp" schemas.
* The table returning function "var" was extended with more details
about globally declared variables, namely their schema, type and
current value.
* Removed obsolete sys.getContent(url) function.
* Removed obsolete json.output(json) function.
* Removed compatibility between interval types and other numeric
types in favor for a more strict SQL standard compliance. This
means operations between temporal types and other numeric types
such as INT and DECIMAL are no longer possible, instead use
interval types. e.g. SELECT date '2020-01-01' + 1; now gives the
error. Instead do: SELECT date '2020-01-01' + interval '1' day; if
1 was meant to be a day interval. Setting an interval variable such
as the session's current timezone with a number e.g. SET
current_timezone = 1; is no longer possible. Instead do SET
current_timezone = interval '1' hour; Casting between interval and
other numeric types is no longer possible as well, because they are
not compatible.
* Because of incompatibilities this change may create, if a user
intends to convert a numeric value to an interval, the
multiplication function can be used in the form: <numeric value> *
interval '1' <interval length> e.g. 10 * interval '1' second =
interval '10' second. As for the other way around, the 'EPOCH'
option was added to the extract syntax. This option returns the
number of milliseconds since the UNIX epoch 1970-01-01 00:00:00 UTC
for date, timestamp and time values (it can be negative).
Meanwhile, for day and second intervals, it returns the total
number of milliseconds in the interval. As a side note, the 'EPOCH'
option is not available for month intervals, because this
conversion is not transparent for this type.
* Made general logarithm function log(x,base) compliant with the SQL
standard, by swapping the input parameters. Instead of log(x,base),
now is log(base,x).
Bug Fixes
* 3553: All schema access to ubiquitous functions
* 3815: Incorrect results when expression contains implicit
float/integer conversions
* 6415: Date arithmetic types are inconsistent
* 6814: provide native implementations for scalar functions
sys.degrees(rad) and sys.radians(deg)
* 6843: function sys.getcontent(url) always returns "Feature not
supported"
* 6857: remove not implemented aggregate function json.output(js
json)
* 6870: Missing bulk operators
* 6878: SQL Connection Error when running SELECT queries containing
AND command
* 6910: SQLancer query: 'bat.append' undefined
* 6930: SQLancer crash on join with coalesce
* 6931: Allow EDITOR to be used for the current command in mclient
* 6935: Wrong result when dividing interval by literal float
* 6937: Lost the microsecond precisions
* 6938: Segmentation fault in MalOptimizer
* 6939: Error in optimizer multiplex when selecting
profiler.getlimit() or wlc.clock() or wlc.tick() or wlr.clock() or
wlr.tick()
* 6941: SELECT queries on remote table fail when using LIKE in WHERE
conditions
* 6943: JSON parser is too permissive
* 6948: msqldump with Empty BLOBs cannot be imported
* 6949: Loosing timing precision
* 6950: redundant/replicated code line in gdk/gdk_hash.c
* 6951: Use a different naming scheme for MAL blocks
* 6954: FILTER functions no longer find their implementation
* 6955: ROUND(DECIMAL, PRECISION) gives incorrect result with
non-scalar precision parameter
* 6960: implementation of log(arg1,arg2) function is not compliant
with the SQL standard, arguments are switched
* 6962: "SELECT * FROM ids LIMIT 1" produces: exp_bin: !ERROR: Could
not find %173.id
* 6964: Table returning function: Cannot access column descriptor
* 6965: Crash when using distinct on the result of a table returning
function
* 6974: Oct2020-branch cannot attach and load FITS files
* 6976: Oct2020: default dbfarm cannot be started
* 6978: Oct2020: \d shows empty result in schema created by include
sql script
* 6979: timestamp add integer
* 6980: Oct2020: wrong mel definition for str.epilogue
