Date: 2016-04-28 13:46:48 +0200
From: Martin van Dinther <<martin.van.dinther>>
To: SQL devs <>
Version: 11.21.19 (Jul2015-SP4)

Last updated: 2016-06-23 10:24:30 +0200

Comment 22103

Date: 2016-04-28 13:46:48 +0200
From: Martin van Dinther <<martin.van.dinther>>

User-Agent: Mozilla/5.0 (X11; Fedora; Linux x86_64; rv:45.0) Gecko/20100101 Firefox/45.0
Build Identifier:

functions sys.isaUUID(str) and sys.isaUUID(uuid) should have been created to return a boolean. Instead they are created returning a uuid:

select * from functions where name ilike 'isaUUID';
-- list two rows
select * from args where func_id in (select id from functions where name ilike 'isaUUID');
-- list four rows, the result return types are marked: uuid !! This should be boolean.

Reproducible: Always

Steps to Reproduce:

1. start mserver5 (Jul2015)
2. start mclient
3. Run SQL commands:
   CREATE TABLE testUUID (s varchar(36), u UUID);
   INSERT INTO testUUID (s, u) VALUES ('ad887b3d-08f7-c308-7285-354a1857cbc8', convert('ad887b3d-08f7-c308-7285-354a1857cbc8', uuid));
   INSERT INTO testUUID (s, u) VALUES ('7393ad7e-4fcf-461a-856e-b70027fe1a9e', convert('7393ad7e-4fcf-461a-856e-b70027fe1a9e', uuid));
   INSERT INTO testUUID (s, u) VALUES ('c005d6fd-20c3-4d01-91a5-bbe676593530', convert('c005d6fd-20c3-4d01-91a5-bbe676593530', uuid));
   SELECT * FROM testUUID ORDER BY s;

SELECT s, u, isaUUID(s) as a_isa_UUID FROM testUUID ORDER BY s;
SELECT s, u, isaUUID(u) as u_isa_UUID FROM testUUID ORDER BY s;
SELECT * FROM testUUID WHERE isaUUID(s) = TRUE ORDER BY s;
SELECT * FROM testUUID WHERE isaUUID(u) = TRUE ORDER BY s;

SELECT MIN(u) AS mn, MAX(u) AS mx, COUNT(u) AS cnt, COUNT(DISTINCT u) AS cnt_d FROM testUUID;
SELECT SUM(u) AS sumu FROM testUUID;
SELECT AVG(u) AS sumu FROM testUUID;

DROP TABLE testUUID;

Actual Results:

bash-4.3$ mclient
Welcome to mclient, the MonetDB/SQL interactive terminal (unreleased)
Database: MonetDB v11.21.20 (unreleased), 'demo'
Type \q to quit, ? for a list of available commands
auto commit mode: on
sql>CREATE TABLE testUUID (s varchar(36), u UUID);
operation successful (24.150ms)
sql>INSERT INTO testUUID (s, u) VALUES ('ad887b3d-08f7-c308-7285-354a1857cbc8', convert('ad887b3d-08f7-c308-7285-354a1857cbc8', uuid));
1 affected row (5.896ms)
sql>INSERT INTO testUUID (s, u) VALUES ('7393ad7e-4fcf-461a-856e-b70027fe1a9e', convert('7393ad7e-4fcf-461a-856e-b70027fe1a9e', uuid));
1 affected row (5.976ms)
sql>INSERT INTO testUUID (s, u) VALUES ('c005d6fd-20c3-4d01-91a5-bbe676593530', convert('c005d6fd-20c3-4d01-91a5-bbe676593530', uuid));
1 affected row (6.244ms)
sql>SELECT * FROM testUUID ORDER BY s;
+--------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------+
| s | u |
+======================================+======================================================================================================================================+
| 7393ad7e-4fcf-461a-856e-b70027fe1a9e | 7393ad7e-4fcf-461a-856e-b70027fe1a9e |
| ad887b3d-08f7-c308-7285-354a1857cbc8 | ad887b3d-08f7-c308-7285-354a1857cbc8 |
| c005d6fd-20c3-4d01-91a5-bbe676593530 | c005d6fd-20c3-4d01-91a5-bbe676593530 |
+--------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------+
3 tuples (1.153ms)
sql>SELECT s, u, isaUUID(s) as a_isa_UUID FROM testUUID ORDER BY s;
TypeException:user.s5_2[41]:'bat.append' undefined in: bat.append(X_80:bat[:oid,:uuid],X_87:bit);
program contains errors
sql>SELECT s, u, isaUUID(u) as u_isa_UUID FROM testUUID ORDER BY s;
TypeException:user.s6_2[42]:'uuid.isaUUID' undefined in: uuid.isaUUID(X_87:uuid);
program contains errors
sql>SELECT * FROM testUUID WHERE isaUUID(s) = TRUE ORDER BY s;
types uuid(0,0) and boolean(1,0) are not equal
sql>SELECT * FROM testUUID WHERE isaUUID(u) = TRUE ORDER BY s;
types uuid(0,0) and boolean(1,0) are not equal
sql>
sql>SELECT MIN(u) AS mn, MAX(u) AS mx, COUNT(u) AS cnt, COUNT(DISTINCT u) AS cnt_d FROM testUUID;
+-------------------------------------------------------------------------------+-------------------------------------------------------------------------------+------+------+
| mn | mx | cnt | cnt_ |
: : : : d :
+===============================================================================+===============================================================================+======+======+
| 7393ad7e-4fcf-461a-856e-b70027fe1a9e | c005d6fd-20c3-4d01-91a5-bbe676593530 | 3 | 3 |
+-------------------------------------------------------------------------------+-------------------------------------------------------------------------------+------+------+
1 tuple (4.751ms)
sql>SELECT SUM(u) AS sumu FROM testUUID;
types uuid(0,0) and tinyint(8,0) are not equal for column 'u'
sql>SELECT AVG(u) AS sumu FROM testUUID;
types uuid(0,0) and double(53,0) are not equal for column 'u'
sql>
sql>DROP TABLE testUUID;
operation successful (3.783ms)
sql>

Expected Results:

Using functions sys.isaUUID(str) and sys.isaUUID(uuid) should not fail but return a boolean.

This needs to be corrected in sql/scripts/45_uuid.sql
Instead of:
create function sys.isaUUID(u uuid)
returns uuid external name uuid."isaUUID";

create function sys.isaUUID(u string)
returns uuid external name uuid."isaUUID";

It should become:
create function sys.isaUUID(u uuid)
returns boolean external name uuid."isaUUID";

create function sys.isaUUID(u string)
returns boolean external name uuid."isaUUID";

Also the upgrade program code needs to be extended.

Comment 22104

Date: 2016-04-28 14:26:14 +0200
From: MonetDB Mercurial Repository <>

Changeset fd9aac3a6906 made by Martin van Dinther martin.van.dinther@monetdbsolutions.com in the MonetDB repo, refers to this bug.

For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=fd9aac3a6906

Changeset description:

Added test for Bug #3997

Comment 22105

Date: 2016-04-28 15:03:33 +0200
From: Martin van Dinther <<martin.van.dinther>>

To correct the problem for an existing database run commands (as admin):
drop function sys.isaUUID(uuid);
create function sys.isaUUID(u uuid) returns boolean external name uuid."isaUUID";

drop function sys.isaUUID(string);
create function sys.isaUUID(u string) returns boolean external name uuid."isaUUID";

-- check the new return types
select * from args where func_id in (select id from functions where name ilike 'isaUUID');
-- the result return types are now boolean

-- also patch the contents of table sys.systemfunctions (as we dropped 2 system functions)
select * from "sys"."systemfunctions" where function_id NOT IN (select id from "sys"."functions");
-- will show two (or more) rows, remove all those invalid FK rows
delete from "sys"."systemfunctions" where function_id NOT IN (select id from "sys"."functions");

-- check if the new function id's also exist in the systemfunctions table
select * from "sys"."functions" where name ilike 'isaUUID';
-- should list two rows
select * from "sys"."functions" where name ilike 'isaUUID' and id NOT IN (select id from "sys"."systemfunctions")
-- if rows are listed add the function id's to table sys.systemfunctions
insert into "sys"."systemfunctions" select id from "sys"."functions" where name ilike 'isaUUID' and id NOT IN (select id from "sys"."systemfunctions");

Comment 22106

Date: 2016-04-28 15:36:11 +0200
From: Martin van Dinther <<martin.van.dinther>>

Correction for last 2 SQL statements of comment2, it should be:
select * from "sys"."functions" where name ilike 'isaUUID' and id NOT IN (select function_id from "sys"."systemfunctions")
-- list two rows, add the function id's to table sys.systemfunctions
insert into "sys"."systemfunctions" (select id from "sys"."functions" where name ilike 'isaUUID' and id NOT IN (select function_id from "sys"."systemfunctions"));
-- two rows inserted

Comment 22115

Date: 2016-04-29 13:33:45 +0200
From: @sjoerdmullender

(In reply to Martin van Dinther from comment 2)

To correct the problem for an existing database run commands (as admin):
drop function sys.isaUUID(uuid);
create function sys.isaUUID(u uuid) returns boolean external name
uuid."isaUUID";

This part doesn't work. A uuid is not a str, so the MAL function isn't found. However, the function doesn't actually make any sense: if the argument is of type UUID, it is by definition a UUID, so the function should always return TRUE:

create function sys.isaUUID(u uuid) returns boolean begin return true; end;

Comment 22119

Date: 2016-05-02 11:44:38 +0200
From: MonetDB Mercurial Repository <>

Changeset 2b223d498fe4 made by Sjoerd Mullender sjoerd@acm.org in the MonetDB repo, refers to this bug.

For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=2b223d498fe4

Changeset description:

Turn sys.isauuid into a Boolean returning function.
This fixes bug #3997.

Comment 22120

Date: 2016-05-03 09:36:39 +0200
From: @sjoerdmullender

Fix will be in next feature release.