Date: 2019-11-06 19:34:54 +0100
From: Martin van Dinther <<martin.van.dinther>>
To: SQL devs <>
Version: 11.33.3 (Apr2019)

Last updated: 2020-06-03 16:58:54 +0200

Comment 27398

Date: 2019-11-06 19:34:54 +0100
From: Martin van Dinther <<martin.van.dinther>>

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

In file sql/scripts/12_url.sql function isurl() is declared as:
CREATE function isaURL(theUrl url) RETURNS BOOL
EXTERNAL NAME url."isaURL";
This probably should have been:
CREATE function isaURL(theUrl string) RETURNS BOOL
EXTERNAL NAME url."isaURL";
so a string argument type instead of a url argument type, as it makes no sense to test a url value whether it is a url value (it is always true by definition).
It does make sense to test a string value on whether it is a valid url value.

Reproducible: Always

Steps to Reproduce:

create table t6785 (v varchar(99), u url);
insert into t6785 (v) values (null), ('http'), ('http:'), ('https:'), ('ftp:'), ('ftp:/'),
('www.a.com'), ('monetdb:org'), ('www.monetdb.org'), ('http://www.example.com/index.html'),
('mailto:someone@example.com'), ('jdbc:monetdb;//localhost');
select * from t6785 order by v;
select * from t6785 where sys.isaurl(v) order by v;
update t6785 set u = cast(v as url) where sys.isaurl(v);
select * from t6785 order by v;
select * from t6785 where sys.isaurl(u) order by v;
drop table t6785;

select f.name, f.func, f.mod, f.language, f.type, /* f.side_effect, f.varres, f.vararg, f.schema_id, */
f.system, a.name, a.type, a.type_digits, a.type_scale, a.inout, a.number
from sys.functions f join sys.args a on f.id = a.func_id where f.name = 'isaurl';

Actual Results:

sql>create table t6785 (v varchar(99), u url);
operation successful
sql>insert into t6785 (v) values (null), ('http'), ('http:'), ('https:'), ('ftp:'), ('ftp:/'),
more>('www.a.com'), ('monetdb:org'), ('www.monetdb.org'), ('http://www.example.com/index.html'),
more>('mailto:someone@example.com'), ('jdbc:monetdb;//localhost');
12 affected rows
sql>select * from t6785 order by v;
+-----------------------------------+---------------------------------------------------------------------------------------+
| v | u |
+===================================+=======================================================================================+
| null | null |
| ftp: | null |
| ftp:/ | null |
| http | null |
| http: | null |
| http://www.example.com/index.html | null |
| https: | null |
| jdbc:monetdb;//localhost | null |
| mailto:someone@example.com | null |
| monetdb:org | null |
| www.a.com | null |
| www.monetdb.org | null |
+-----------------------------------+---------------------------------------------------------------------------------------+
12 tuples
sql>select * from t6785 where sys.isaurl(v) order by v;
+-----------------------------------+---------------------------------------------------------------------------------------+
| v | u |
+===================================+=======================================================================================+
| ftp: | null |
| ftp:/ | null |
| http: | null |
| http://www.example.com/index.html | null |
| https: | null |
| jdbc:monetdb;//localhost | null |
| mailto:someone@example.com | null |
| monetdb:org | null |
+-----------------------------------+---------------------------------------------------------------------------------------+
8 tuples
sql>update t6785 set u = cast(v as url) where sys.isaurl(v);
8 affected rows
sql>select * from t6785 order by v;
+-----------------------------------+-----------------------------------+
| v | u |
+===================================+===================================+
| null | null |
| ftp: | ftp: |
| ftp:/ | ftp:/ |
| http | null |
| http: | http: |
| http://www.example.com/index.html | http://www.example.com/index.html |
| https: | https: |
| jdbc:monetdb;//localhost | jdbc:monetdb;//localhost |
| mailto:someone@example.com | mailto:someone@example.com |
| monetdb:org | monetdb:org |
| www.a.com | null |
| www.monetdb.org | null |
+-----------------------------------+-----------------------------------+
12 tuples
sql>select * from t6785 where sys.isaurl(u) order by v;
+-----------------------------------+-----------------------------------+
| v | u |
+===================================+===================================+
| ftp: | ftp: |
| ftp:/ | ftp:/ |
| http: | http: |
| http://www.example.com/index.html | http://www.example.com/index.html |
| https: | https: |
| jdbc:monetdb;//localhost | jdbc:monetdb;//localhost |
| mailto:someone@example.com | mailto:someone@example.com |
| monetdb:org | monetdb:org |
+-----------------------------------+-----------------------------------+
8 tuples
sql>drop table t6785;
operation successful
sql>
sql>select f.name, f.func, f.mod, f.language, f.type, /* f.side_effect, f.varres, f.vararg, f.schema_id, */
more>f.system, a.name, a.type, a.type_digits, a.type_scale, a.inout, a.number
more>from sys.functions f join sys.args a on f.id = a.func_id where f.name = 'isaurl';
+--------+---------------------------------------------------------+------+------+------+-------+--------+---------+------+------+------+------+
| name | func | mod | lang | type | syste | name | type | type | type | inou | numb |
: : : : uage : : m : : : _dig : _sca : t : er :
: : : : : : : : : its : le : : :
+========+=========================================================+======+======+======+=======+========+=========+======+======+======+======+
| isaurl | create function isaurl(theurl url) returns bool | url | 1 | 1 | true | result | boolean | 1 | 0 | 0 | 0 |
: : external name url."isaURL"; : : : : : : : : : : :
| isaurl | create function isaurl(theurl url) returns bool | url | 1 | 1 | true | theurl | url | 0 | 0 | 1 | 1 |
: : external name url."isaURL"; : : : : : : : : : : :
+--------+---------------------------------------------------------+------+------+------+-------+--------+---------+------+------+------+------+
2 tuples
sql>

Expected Results:

sql>select f.name, f.func, f.mod, f.language, f.type, /* f.side_effect, f.varres, f.vararg, f.schema_id, */
more>f.system, a.name, a.type, a.type_digits, a.type_scale, a.inout, a.number
more>from sys.functions f join sys.args a on f.id = a.func_id where f.name = 'isaurl';
+--------+---------------------------------------------------------+------+------+------+-------+--------+---------+------+------+------+------+
| name | func | mod | lang | type | syste | name | type | type | type | inou | numb |
: : : : uage : : m : : : _dig : _sca : t : er :
: : : : : : : : : its : le : : :
+========+=========================================================+======+======+======+=======+========+=========+======+======+======+======+
| isaurl | create function isaurl(theurl string) returns bool | url | 1 | 1 | true | result | boolean | 1 | 0 | 0 | 0 |
: : external name url."isaURL"; : : : : : : : : : : :
| isaurl | create function isaurl(theurl string) returns bool | url | 1 | 1 | true | theurl | clob | 0 | 0 | 1 | 1 |
: : external name url."isaURL"; : : : : : : : : : : :
+--------+---------------------------------------------------------+------+------+------+-------+--------+---------+------+------+------+------+
2 tuples
sql>

Comment 27506

Date: 2020-01-03 14:22:44 +0100
From: MonetDB Mercurial Repository <>

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

For complete details, see https//devmonetdborg/hg/MonetDB?cmd=changeset;node=f90d24a8ac52

Changeset description:

Change function sys.isaurl to accept string instead of url.
This fixes bug #6785.