hi, monetdb does not support SUM() OVER commands.. does anyone have a
smart alternative to implement this? thanks
SELECT
batch_id,
job_count,
SUM(job_count) OVER (ORDER BY duration) as cumjobs,
SUM(job_count) over () as totjobs,
duration
FROM
test_data ;
Hi,
As per MonetDB Solution suggestion, we are asking here about this topic.
We are studying how MonetDB store its database on disk.
So far we were able to understand pretty much all the files (BAT,
journal, BBP.dir, heap, hash, imprints, and so on). We have custom tools
to decode and dump them (experimental at this point).
There is one thing however that is unclear: we thought that "delta BAT",
which are the BAT assigned to an SQL tables to list the OIDs of rows
that were deleted (named "D_<schema>_<table>" internaly), would contains
only *uniques* values (since you can never delete twice the same row).
But on several databases that we are running, we found that some of
theses BATs contain duplicates. Lot of duplicates actually. Especially
on D_sys__columns and D_sys__tables BATs (respectively for the
sys._columns and sys._tables system tables). Some databases do not have
this "issue" (while they all have the same schema, and process the same
kind of data as the other ones).
Can someone explain if duplicates are expected in theses BATs ?
Here is an excerpt of D_sys__tables:
# hexdump -C 02/210.tail
00000000 2e 00 00 00 00 00 00 00 2f 00 00 00 00 00 00 00 |......../.......|
00000010 30 00 00 00 00 00 00 00 31 00 00 00 00 00 00 00 |0.......1.......|
00000020 32 00 00 00 00 00 00 00 33 00 00 00 00 00 00 00 |2.......3.......|
00000030 34 00 00 00 00 00 00 00 35 00 00 00 00 00 00 00 |4.......5.......|
00000040 36 00 00 00 00 00 00 00 37 00 00 00 00 00 00 00 |6.......7.......|
00000050 42 00 00 00 00 00 00 00 43 00 00 00 00 00 00 00 |B.......C.......|
00000060 44 00 00 00 00 00 00 00 45 00 00 00 00 00 00 00 |D.......E.......|
00000070 46 00 00 00 00 00 00 00 47 00 00 00 00 00 00 00 |F.......G.......|
00000080 48 00 00 00 00 00 00 00 49 00 00 00 00 00 00 00 |H.......I.......|
00000090 4a 00 00 00 00 00 00 00 4b 00 00 00 00 00 00 00 |J.......K.......|
000000a0 4c 00 00 00 00 00 00 00 4d 00 00 00 00 00 00 00 |L.......M.......|
000000b0 4e 00 00 00 00 00 00 00 4f 00 00 00 00 00 00 00 |N.......O.......|
000000c0 50 00 00 00 00 00 00 00 50 00 00 00 00 00 00 00 |P.......P.......|
000000d0 51 00 00 00 00 00 00 00 50 00 00 00 00 00 00 00 |Q.......P.......|
000000e0 51 00 00 00 00 00 00 00 52 00 00 00 00 00 00 00 |Q.......R.......|
000000f0 50 00 00 00 00 00 00 00 51 00 00 00 00 00 00 00 |P.......Q.......|
00000100 52 00 00 00 00 00 00 00 53 00 00 00 00 00 00 00 |R.......S.......|
00000110 50 00 00 00 00 00 00 00 51 00 00 00 00 00 00 00 |P.......Q.......|
00000120 52 00 00 00 00 00 00 00 53 00 00 00 00 00 00 00 |R.......S.......|
...
You can clearly see duplicates OID (the first one being 0x50 at 0xc8).
Its entry in BBP.dir (split into sections):
136 32 tmp_210 tmpr_210 02/210 610523782 2 171807 0 0 171807 172032 0 0 0 0
void 0 1 1793 0 0 0 0 0 1000651 0 0 0
oid 8 0 1024 24 25 27 1 46 773603235 1374456 1376256 1
We are using: MonetDB 5 server v11.21.14 (64-bit, 64-bit oids, 128-bit integers).
--
Frédéric Jolliton
Sécuractive
All,
Quick question, how many R interpreters are running within MonetDB if you are using R UDFs? Is there a single shared interpreter or are there multiple copies running?
Thanks,
Dave
Hello,
I have a long and complex embedded python function.
This function depends on the single rows of the input columns, so I would like to parallelize it.
I’ve read about the post on embedded python and python_map. I understand that using python_map should automatically split (in a not specified way) the input columns and fire different (how many?) python processes executing on their input slice. Finally it should pack all the partial results.
Well, I have tried to use python_map, but nothing different happened. Is there something more I have to do apart from setting LANGUAGE PYTHON_MAP?
Regards,
Stefano
Hi,
does MonetDB support positional placeholders like '$1' instead of just
'?' in prepared statements?
I want to refer to a parameter more than once and possibly also out of
order. Is there some alternative available?
Regards,
Frank
Hello,
Can somebody tell me how to reset monetdb administrative password?
I’ve tried to stop mserver5, start it manually,
oldp:=clients.getPasswordHash(“monetdb");
newp:=clients.backendsum("newpassword");
clients.changePassword(oldp, newp);
Stoping it again and start in normal mode
But it didn’t help:
mclient --database=mydb --user=monetdb
password:<usingnewpass>
InvalidCredentialsException:checkCredentials:invalid credentials for user 'monetdb'
Hi Everybody!
Im facing a problem. We register telecomunications calls. As the volume is
so big, we decided to store the data in different tables. The criteria we
used for splitting the info was to create a table per each Switch and Day.
So we have Sw1_20160601, Sw2_20160602...Sw3_20160620..etc etc Each table
has aprox 15MM rows.
In order to create some reports we have created a *MERGE TABLE *and added
to this table the 90 tables (3 switches x 30 days). BUT, now Im trying to
run a very simple query, like SELECT cdr_date FROM xxxx LIMIT 3; To get at
least 3 rows (just to see that this schema works).
It took 21 mins to give me those 3 rows....
Am I doing something wrong?
Rgds
Ariel
The MonetDB team at CWI/MonetDB BV is pleased to announce the
Jun2016 feature release of the MonetDB suite of programs.
More information about MonetDB can be found on our website at
<http://www.monetdb.org/>.
For details on this release, please see the release notes at
<http://www.monetdb.org/Downloads/ReleaseNotes>.
As usual, the download location is <http://dev.monetdb.org/downloads/>.
Jun 2016 feature release
MonetDB5 Server
* Extended the storage() table producing function to also accept
storage([schemaname [, tablename [, columnname]]])
* The :bat[:oid,:any] type descriptor has been turned into its
columnar version :bat[:any]
* Converted the *.mal scripts into *.malC versions to prepare for
removal of the mserver console.
* Removed functions mat.hasMoreElements, mat.info, mat.mergepack,
mat. newIterator, mat.project, mat.pack2, mat.sortReverse,
mat.sort, and mat.slice.
* Removed grouped aggregate functions from the aggr module in which
the groups were indicated by the head column of the bat to be
aggregated. Use the interface with a separate group bat instead.
* The server now stops executing a query when the client disconnects.
* Removed algebra.join. Use algebra.subjoin instead.
* Removed algebra.antijoin. Use algebra.subantijoin or
algebra.subthetajoin instead.
* The MAL function 'leftfetchjoin' is renamed to its relational
algebra version 'projection'
* The generic property handling scheme has been removed. It was used
in just a few places, for widely different purposes and contained
cruft.
* Removed functions str.iconv and str.codeset. Internally, strings
are always in UTF-8 encoding, so we can't allow code set
conversions.
* Removed algebra.like with a BAT argument. Use algebra.likesubselect
instead.
* Removed algebra.leftjoin. Use algebra.subleftjoin or
algebra.leftfetchjoin instead.
* Removed algebra.tdiff and algebra.tinter.
* Removed algebra.sample. Use sample.subuniform instead.
* Removed algebra.select. Use algebra.subselect instead.
* Removed algebra.revert.
* Removed bat.order and bat.orderReverse functions.
* Changed client.getUsers to return two bats, one with the user id
and one with the user name.
* Implemented algebra.subdiff and algebra.subinter.
* Removed algebra.tdifference and algebra.tintersect.
* Removed algebra.tunion.
Build Environment
* A new package MonetDB-lidar (Fedora) or libmonetdb5-server-lidar
(Debian/Ubuntu) has been created to work with LiDAR data.
* Implemented a systemd configuration file for a monetdbd.service on
systems that support it (Fedora, newer Ubuntu).
Client Package
* The Ruby client is now in a separate repository
(http://dev.monetdb.org/hg/monetdb-ruby) and released
independently.
Geom Module
* The geom module has been completely overhauled. Types are now
specified as GEOMETRY(POINT) instead of POINT, old functions have
been removed, new ones introduced. However, even with all the
changes, a database upgrade should still be possible (as always,
make a backup first).
MonetDB Common
* Removed BATconst. Use BATconstant instead.
* Changed BATconstant. It now has a new first argument with the
seqbase for the head column.
* Removed BATmmap. It was no longer used.
* BUNdelete and BATdel don't accept a foce argument and only allow
deleting values that have not yet been committed. BUNdelete
exchanges the deleted value with the last value (if the deleted
value isn't the last value). BATdel compacts the BAT by shifting
values after the deleted values up. The list of to-be-deleted
values in BATdel must be sorted and unique.
* Removed BUNreplace from list of exported functions. It wasn't used,
and there is still BUNinplace and void_inplace that do more-or-less
the same thing.
* Changed BATderiveHeadProps to BATderiveTailProps (and it now works
on the tail column).
* Removed unused functions BATalpha, BATdelta, and BATprev.
* Removed function VIEWcombine. Use BATdense instead.
* Removed "left" parameter from BUNinplace. It wasn't used since the
BAT it works on is dense headed.
* Created function BATdense to easily create a [void,void] BAT with
specified seqbases for head and tail and a count.
* Removed function BATmark. When all head columns are dense, BATmark
basically only created a new [void,void] BAT.
* Renamed BATsubsort to BATsort.
* Removed "sub" from the names of the function BATsubselect,
BATthetasubselect, BATsubcross, BATsubleftjoin, BATsubouterjoin,
BATsubthetajoin, BATsubsemijoin, BATsubdiff, BATsubjoin,
BATsubbandjoin, BATsubrangejoin, and BATsubunique.
* Removed BATsubleftfetchjoin: it was not used.
* Removed BATcross1. Use BATsubcross instead.
* Removed all versions of the SORTloop macro.
* Removed Batkdiff. Use BATsubdiff instead.
* Removed BATselect. Use BATsubselect instead.
* Removed BATsemijoin. Use BATsubsemijoin instead.
* Removed BATjoin. Use BATsubjoin instead.
* Removed BATleftjoin. Use BATsubleftjoin or BATproject instead.
* Removed BATleftfetchjoin. Use BATproject instead.
* Removed BUNins from the list of exported functions.
* Removed legacy functions BATuselect and BATuselect_.
* Removed legacy functions BATantijoin, BATbandjoin, BATouterjoin,
BATrangejoin, and BATthetajoin.
* Removed function BATrevert.
* BATordered now works on the TAIL column.
* Removed obsolete functions BATorder() and BATorder_rev().
* Implemented BATsubdiff which returns a list of OIDs (sorted, i.e.
usable as candidate list) of tuples in the left input whose value
does not occur in the right input.
* Removed function BATkintersect. It wasn't used anymore. It's
functionality can be obtained by using BATsubsemijoin.
* Removed the function BATkunion.
Java Module
* Fixed problem in DatabaseMetaData.getUDTs() when it was called with
types parameter filled. It used to throw SQException with message:
SELECT: identifier 'DATA_TYPE' unknown. Now it returns the UDTs
which match the provided array of data types.
* Implemented MonetDatabaseMetaData.supportsConvert() and
MonetDatabaseMetaData.supportsConvert(int fromType, int toType)
methods. It used to always return false. Now it returns true for
the supported conversions. This fixes Bug 3460.
* ResultSet.setFetchSize(): added a dummy implementation to get rid
of the SQLFeatureNotSupportedException. In MonetDB, it does not
make sense to set the fetch size of a result set. If one really
wants to set the fetch size, one should use
Statement.setFetchSize() instead.
* Fixed resource leak in ResultSetMetaData. It created and cached a
ResultSet object for each column but never closed the ResultSet
objects.
* Corrected DatabaseMetaData methods which accept a catalog filter
argument. Those methods will now filter the results on the
specified catalog name, whereas previously the catalog filter
argument was ignored.
* Corrected output of column KEY_SEQ of DatabaseMetaData methods:
getPrimaryKeys(), getImportedKeys(), getExportedKeys() and
getCrossReference(). It now starts at 1 instead of 0 previously.
* Corrected DatabaseMetaData.getSchemas() by returning 2 instead of 3
columns.
* Improved DatabaseMetaData.getColumns() by returning two additional
columns: IS_AUTOINCREMENT and IS_GENERATEDCOLUMN.
* Improved DatabaseMetaData.getTypeInfo(). It now returns better
information on LITERAL_PREFIX, LITERAL_SUFFIX, CREATE_PARAMS,
CASE_SENSITIVE, FIXED_PREC_SCALE and MAXIMUM_SCALE for some data
types. Also the returned rows are now ordered by DATA_TYPE,
TYPE_NAME, PRECISION as required by the specs. Also corrected
output column names "searchable" into "SEARCHABLE" and "MAXIMUM
SCALE" into "MAXIMUM_SCALE" to match the JDBC specification.
* Corrected DatabaseMetaData.getPseudoColumns(). It used to return 12
empty rows. Now it returns no rows as MonetDB does not have pseudo
columns.
* Implemented method DatabaseMetaData.getClientProperties(). It used
to always return a resultset with 4 completely empty rows. It now
returns a resultset with the possible connection properties.
* Implemented method DatabaseMetaData.getUDTs(). It used to return an
empty resultset. Now it returns the User Defined Types such as
inet, json, url and uuid.
* Corrected the returned table types in
DatabaseMetaData.getTableTypes(). It now returns all 10 table types
(as stored in sys.table_types) instead of the previously 8
hardcoded table types. For old MonetDB servers which do not have
the sys.table_types table, the old behavior is retained.
* Implemented methods DatabaseMetadata.getProcedures() and
DatabaseMetadata.getProcedureColumns(). They used to return an
empty resultset. Now they return the expected Procedures and
ProcedureColumns. Also getProcedureColumns() now returns a
resultset with all 20 columns instead of 13 columns previously.
* Method getFunctionColumns() in DatabaseMetadata used to throw an
SQLException: getFunctionColumns(String, String, String, String) is
not implemented. This method is now implemented and returns a
resultset.
* Method getFunctions() in DatabaseMetadata used to throw an
SQLException: SELECT: no such column 'functions.sql' This has been
corrected. It now returns a resultset as requested.
* The resultsets of DatabaseMetadata methods now no longer return a
value for the *_CAT columns as MonetDB does not support Catalogs.
* Fixed a memory leak in MonetDatabaseMetaData.java for a static
cache which kept references to closed Connection objects.
* Improved JdbcClient program when presenting query data to console.
It used to send an SQL catalog query for each query result column
which slowed down the interactive response considerably. These
additional SQL catalog queries have been eliminated.
* Corrected MonetResultSet.getObject(String columnName). It no longer
throws a NullPointerException in cases where internally a
MonetVirtualResultSet is used.
* Fixed Connection.isValid(): this method should never attempt to
close the connection, even if an error has occurred.
SQL
* Disallow GRANT <some-user> TO <role-or-use>. Only explicitly
created roles can be granted.
* Extended grantable schema privileges: when a user is granted the
"sysadmin" role, the user now hcan not only create schemas, but
also drop schemas.
* Introduced COPY INTO/ COPY FROM global privileges. These privileges
allows other users than 'monetdb' to be granted the privileges to
use the MonetDB bulk data loading/exporting features, i.e., COPY
FROM <file> and COPY INTO <file>.
Bug Fixes
* 2407: Merovingian: allow binds to given ip/interface
* 2815: No SRID support
* 3361: constants as MAL function parameters prevent intermediate
reuse
* 3374: UNIQUE constraint does not set tkey property on the
corresponding BAT
* 3460: incomplete implementation of JDBC driver supportsConvert(),
supportsConvert(int fromType, int toType) methods in
MonetDatabaseMetaData.java
* 3711: JDBC connection using jdbcclient.jar is very slow
* 3877: MonetDBLite should allow close then re-open databases?
without restarting R
* 3911: Invalid connect() call in 'redirect' mode
* 3920: query with DISTINCT and correlated scalar subquery in SELECT
list causes Assertion failure and crash of mserver5
* 3927: COUNT( distinct my_clob ) , QUANTILE( my_double , number )
fails in dev build
* 3956: MonetDBLite unable to execute LIMIT 1 statement
* 3972: Drastic Memory leak of 600GBs while generating plan for Query
with 25 joins
* 3974: Prepared statement rel_bin.c:2378: rel2bin_project: Assertion
`0' failed.
* 3975: Suspicious code in store_manager() on exit path
* 3978: SQL returns TypeException 'aggr.subcorr' undefined for
sys.corr function
* 3980: JOIN with references on both sides crashes mserver
* 3981: Incorrect LEFT JOIN when FROM clause contains nested
subqueries
* 3983: Creation of a Foreign Key which partially maps to a primary
key is accepted without a warning
* 3984: Multiple paths in the .profile
* 3985: ruby-monetdb-sql gem fails for negative timezone offset (USA,
etc.)
* 3987: Segfault on malformed csv import
* 3991: MonetDBLite feature request: default monetdb.sequential to
FALSE on windows
* 3994: MonetDBLite dbDisconnect with shutdown=TRUE freezes the
console on windows
* 3995: NullPointerException when calling getObject()
* 3997: calling scalar functions sys.isaUUID(str) or
sys.isaUUID(uuid) fail
* 3999: length() returns wrong length for strings which have spaces
at the end of the string
* 4010: RELEASE SAVEPOINT after ALTER TABLE crashes mserver5
* 4011: sys.sessions.user column always shows 'monetdb'
* 4013: GDKextendf does not free up memory when it fails due to
insufficient resources
* 4015: Daemon crashes on database release command
The MonetDB team at CWI/MonetDB BV is pleased to announce the
Jun2016 feature release of the MonetDB suite of programs.
More information about MonetDB can be found on our website at
<http://www.monetdb.org/>.
For details on this release, please see the release notes at
<http://www.monetdb.org/Downloads/ReleaseNotes>.
As usual, the download location is <http://dev.monetdb.org/downloads/>.
Jun 2016 feature release
MonetDB5 Server
* Extended the storage() table producing function to also accept
storage([schemaname [, tablename [, columnname]]])
* The :bat[:oid,:any] type descriptor has been turned into its
columnar version :bat[:any]
* Converted the *.mal scripts into *.malC versions to prepare for
removal of the mserver console.
* Removed functions mat.hasMoreElements, mat.info, mat.mergepack,
mat. newIterator, mat.project, mat.pack2, mat.sortReverse,
mat.sort, and mat.slice.
* Removed grouped aggregate functions from the aggr module in which
the groups were indicated by the head column of the bat to be
aggregated. Use the interface with a separate group bat instead.
* The server now stops executing a query when the client disconnects.
* Removed algebra.join. Use algebra.subjoin instead.
* Removed algebra.antijoin. Use algebra.subantijoin or
algebra.subthetajoin instead.
* The MAL function 'leftfetchjoin' is renamed to its relational
algebra version 'projection'
* The generic property handling scheme has been removed. It was used
in just a few places, for widely different purposes and contained
cruft.
* Removed functions str.iconv and str.codeset. Internally, strings
are always in UTF-8 encoding, so we can't allow code set
conversions.
* Removed algebra.like with a BAT argument. Use algebra.likesubselect
instead.
* Removed algebra.leftjoin. Use algebra.subleftjoin or
algebra.leftfetchjoin instead.
* Removed algebra.tdiff and algebra.tinter.
* Removed algebra.sample. Use sample.subuniform instead.
* Removed algebra.select. Use algebra.subselect instead.
* Removed algebra.revert.
* Removed bat.order and bat.orderReverse functions.
* Changed client.getUsers to return two bats, one with the user id
and one with the user name.
* Implemented algebra.subdiff and algebra.subinter.
* Removed algebra.tdifference and algebra.tintersect.
* Removed algebra.tunion.
Build Environment
* A new package MonetDB-lidar (Fedora) or libmonetdb5-server-lidar
(Debian/Ubuntu) has been created to work with LiDAR data.
* Implemented a systemd configuration file for a monetdbd.service on
systems that support it (Fedora, newer Ubuntu).
Client Package
* The Ruby client is now in a separate repository
(http://dev.monetdb.org/hg/monetdb-ruby) and released
independently.
Geom Module
* The geom module has been completely overhauled. Types are now
specified as GEOMETRY(POINT) instead of POINT, old functions have
been removed, new ones introduced. However, even with all the
changes, a database upgrade should still be possible (as always,
make a backup first).
MonetDB Common
* Removed BATconst. Use BATconstant instead.
* Changed BATconstant. It now has a new first argument with the
seqbase for the head column.
* Removed BATmmap. It was no longer used.
* BUNdelete and BATdel don't accept a foce argument and only allow
deleting values that have not yet been committed. BUNdelete
exchanges the deleted value with the last value (if the deleted
value isn't the last value). BATdel compacts the BAT by shifting
values after the deleted values up. The list of to-be-deleted
values in BATdel must be sorted and unique.
* Removed BUNreplace from list of exported functions. It wasn't used,
and there is still BUNinplace and void_inplace that do more-or-less
the same thing.
* Changed BATderiveHeadProps to BATderiveTailProps (and it now works
on the tail column).
* Removed unused functions BATalpha, BATdelta, and BATprev.
* Removed function VIEWcombine. Use BATdense instead.
* Removed "left" parameter from BUNinplace. It wasn't used since the
BAT it works on is dense headed.
* Created function BATdense to easily create a [void,void] BAT with
specified seqbases for head and tail and a count.
* Removed function BATmark. When all head columns are dense, BATmark
basically only created a new [void,void] BAT.
* Renamed BATsubsort to BATsort.
* Removed "sub" from the names of the function BATsubselect,
BATthetasubselect, BATsubcross, BATsubleftjoin, BATsubouterjoin,
BATsubthetajoin, BATsubsemijoin, BATsubdiff, BATsubjoin,
BATsubbandjoin, BATsubrangejoin, and BATsubunique.
* Removed BATsubleftfetchjoin: it was not used.
* Removed BATcross1. Use BATsubcross instead.
* Removed all versions of the SORTloop macro.
* Removed Batkdiff. Use BATsubdiff instead.
* Removed BATselect. Use BATsubselect instead.
* Removed BATsemijoin. Use BATsubsemijoin instead.
* Removed BATjoin. Use BATsubjoin instead.
* Removed BATleftjoin. Use BATsubleftjoin or BATproject instead.
* Removed BATleftfetchjoin. Use BATproject instead.
* Removed BUNins from the list of exported functions.
* Removed legacy functions BATuselect and BATuselect_.
* Removed legacy functions BATantijoin, BATbandjoin, BATouterjoin,
BATrangejoin, and BATthetajoin.
* Removed function BATrevert.
* BATordered now works on the TAIL column.
* Removed obsolete functions BATorder() and BATorder_rev().
* Implemented BATsubdiff which returns a list of OIDs (sorted, i.e.
usable as candidate list) of tuples in the left input whose value
does not occur in the right input.
* Removed function BATkintersect. It wasn't used anymore. It's
functionality can be obtained by using BATsubsemijoin.
* Removed the function BATkunion.
Java Module
* Fixed problem in DatabaseMetaData.getUDTs() when it was called with
types parameter filled. It used to throw SQException with message:
SELECT: identifier 'DATA_TYPE' unknown. Now it returns the UDTs
which match the provided array of data types.
* Implemented MonetDatabaseMetaData.supportsConvert() and
MonetDatabaseMetaData.supportsConvert(int fromType, int toType)
methods. It used to always return false. Now it returns true for
the supported conversions. This fixes Bug 3460.
* ResultSet.setFetchSize(): added a dummy implementation to get rid
of the SQLFeatureNotSupportedException. In MonetDB, it does not
make sense to set the fetch size of a result set. If one really
wants to set the fetch size, one should use
Statement.setFetchSize() instead.
* Fixed resource leak in ResultSetMetaData. It created and cached a
ResultSet object for each column but never closed the ResultSet
objects.
* Corrected DatabaseMetaData methods which accept a catalog filter
argument. Those methods will now filter the results on the
specified catalog name, whereas previously the catalog filter
argument was ignored.
* Corrected output of column KEY_SEQ of DatabaseMetaData methods:
getPrimaryKeys(), getImportedKeys(), getExportedKeys() and
getCrossReference(). It now starts at 1 instead of 0 previously.
* Corrected DatabaseMetaData.getSchemas() by returning 2 instead of 3
columns.
* Improved DatabaseMetaData.getColumns() by returning two additional
columns: IS_AUTOINCREMENT and IS_GENERATEDCOLUMN.
* Improved DatabaseMetaData.getTypeInfo(). It now returns better
information on LITERAL_PREFIX, LITERAL_SUFFIX, CREATE_PARAMS,
CASE_SENSITIVE, FIXED_PREC_SCALE and MAXIMUM_SCALE for some data
types. Also the returned rows are now ordered by DATA_TYPE,
TYPE_NAME, PRECISION as required by the specs. Also corrected
output column names "searchable" into "SEARCHABLE" and "MAXIMUM
SCALE" into "MAXIMUM_SCALE" to match the JDBC specification.
* Corrected DatabaseMetaData.getPseudoColumns(). It used to return 12
empty rows. Now it returns no rows as MonetDB does not have pseudo
columns.
* Implemented method DatabaseMetaData.getClientProperties(). It used
to always return a resultset with 4 completely empty rows. It now
returns a resultset with the possible connection properties.
* Implemented method DatabaseMetaData.getUDTs(). It used to return an
empty resultset. Now it returns the User Defined Types such as
inet, json, url and uuid.
* Corrected the returned table types in
DatabaseMetaData.getTableTypes(). It now returns all 10 table types
(as stored in sys.table_types) instead of the previously 8
hardcoded table types. For old MonetDB servers which do not have
the sys.table_types table, the old behavior is retained.
* Implemented methods DatabaseMetadata.getProcedures() and
DatabaseMetadata.getProcedureColumns(). They used to return an
empty resultset. Now they return the expected Procedures and
ProcedureColumns. Also getProcedureColumns() now returns a
resultset with all 20 columns instead of 13 columns previously.
* Method getFunctionColumns() in DatabaseMetadata used to throw an
SQLException: getFunctionColumns(String, String, String, String) is
not implemented. This method is now implemented and returns a
resultset.
* Method getFunctions() in DatabaseMetadata used to throw an
SQLException: SELECT: no such column 'functions.sql' This has been
corrected. It now returns a resultset as requested.
* The resultsets of DatabaseMetadata methods now no longer return a
value for the *_CAT columns as MonetDB does not support Catalogs.
* Fixed a memory leak in MonetDatabaseMetaData.java for a static
cache which kept references to closed Connection objects.
* Improved JdbcClient program when presenting query data to console.
It used to send an SQL catalog query for each query result column
which slowed down the interactive response considerably. These
additional SQL catalog queries have been eliminated.
* Corrected MonetResultSet.getObject(String columnName). It no longer
throws a NullPointerException in cases where internally a
MonetVirtualResultSet is used.
* Fixed Connection.isValid(): this method should never attempt to
close the connection, even if an error has occurred.
SQL
* Disallow GRANT <some-user> TO <role-or-use>. Only explicitly
created roles can be granted.
* Extended grantable schema privileges: when a user is granted the
"sysadmin" role, the user now hcan not only create schemas, but
also drop schemas.
* Introduced COPY INTO/ COPY FROM global privileges. These privileges
allows other users than 'monetdb' to be granted the privileges to
use the MonetDB bulk data loading/exporting features, i.e., COPY
FROM <file> and COPY INTO <file>.
Bug Fixes
* 2407: Merovingian: allow binds to given ip/interface
* 2815: No SRID support
* 3361: constants as MAL function parameters prevent intermediate
reuse
* 3374: UNIQUE constraint does not set tkey property on the
corresponding BAT
* 3460: incomplete implementation of JDBC driver supportsConvert(),
supportsConvert(int fromType, int toType) methods in
MonetDatabaseMetaData.java
* 3711: JDBC connection using jdbcclient.jar is very slow
* 3877: MonetDBLite should allow close then re-open databases?
without restarting R
* 3911: Invalid connect() call in 'redirect' mode
* 3920: query with DISTINCT and correlated scalar subquery in SELECT
list causes Assertion failure and crash of mserver5
* 3927: COUNT( distinct my_clob ) , QUANTILE( my_double , number )
fails in dev build
* 3956: MonetDBLite unable to execute LIMIT 1 statement
* 3972: Drastic Memory leak of 600GBs while generating plan for Query
with 25 joins
* 3974: Prepared statement rel_bin.c:2378: rel2bin_project: Assertion
`0' failed.
* 3975: Suspicious code in store_manager() on exit path
* 3978: SQL returns TypeException 'aggr.subcorr' undefined for
sys.corr function
* 3980: JOIN with references on both sides crashes mserver
* 3981: Incorrect LEFT JOIN when FROM clause contains nested
subqueries
* 3983: Creation of a Foreign Key which partially maps to a primary
key is accepted without a warning
* 3984: Multiple paths in the .profile
* 3985: ruby-monetdb-sql gem fails for negative timezone offset (USA,
etc.)
* 3987: Segfault on malformed csv import
* 3991: MonetDBLite feature request: default monetdb.sequential to
FALSE on windows
* 3994: MonetDBLite dbDisconnect with shutdown=TRUE freezes the
console on windows
* 3995: NullPointerException when calling getObject()
* 3997: calling scalar functions sys.isaUUID(str) or
sys.isaUUID(uuid) fail
* 3999: length() returns wrong length for strings which have spaces
at the end of the string
* 4010: RELEASE SAVEPOINT after ALTER TABLE crashes mserver5
* 4011: sys.sessions.user column always shows 'monetdb'
* 4013: GDKextendf does not free up memory when it fails due to
insufficient resources
* 4015: Daemon crashes on database release command
Hi all,
MariaDB has released a first alpha version of their new ColumnStore (which is essentially InfiniDB). We have done a first tentative performance test and did a comparison
of MariaDB ColumnStore vs. MonetDB. For most of the test cases, MonetDB wins hands down. If you are interested in the numbers, have a look at
https://reportserver.net/blog/2016/06/20/mariadb-columnstore-vs-innodb-vs-m…
Best Regards,
Arno