Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

calling scalar functions sys.isaUUID(str) or sys.isaUUID(uuid) fail #3997

Closed
monetdb-team opened this issue Nov 30, 2020 · 0 comments
Closed

calling scalar functions sys.isaUUID(str) or sys.isaUUID(uuid) fail #3997

monetdb-team opened this issue Nov 30, 2020 · 0 comments

Comments

@monetdb-team
Copy link

@monetdb-team monetdb-team commented Nov 30, 2020

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
1 participant