Date: 2020-03-27 00:45:54 +0100
From: Martin van Dinther <<martin.van.dinther>>
To: SQL devs <>
Version: 11.35.19 (Nov2019-SP3)
CC: @PedroTadim

Last updated: 2020-07-27 09:30:16 +0200

Comment 27630

Date: 2020-03-27 00:45:54 +0100
From: Martin van Dinther <<martin.van.dinther>>

User-Agent: Mozilla/5.0 (Windows NT 6.1; rv:74.0) Gecko/20100101 Firefox/74.0
Build Identifier:

The url extraction functions: sys.getanchor(), sys.getbasename(), sys.getcontext(), sys.getdomain(), sys.getextension(), sys.getfile(), sys.gethost(), sys.getport(), sys.getprotocol(), sys.getquery(), sys.getroboturl() and sys.getuser() do not accept null as argument value. Instead they return an error: Error: bad url

It would be better (more consistent) to return null in those cases, similar to string, numeric and temporal functions.

Reproducible: Always

Steps to Reproduce:

create table turl (val url);
insert into turl values ('https://me@www.monetdb.org:458/Doc/Abc.html?lang=nl&sort=ascexample');
insert into turl values (null);
select * from turl;

select val, sys.getanchor(val) from turl;
select val, sys.getbasename(val) from turl;
select val, sys.getcontext(val) from turl;
select val, sys.getdomain(val) from turl;
select val, sys.getextension(val) from turl;
select val, sys.getfile(val) from turl;
select val, sys.gethost(val) from turl;
select val, sys.getport(val) from turl;
select val, sys.getprotocol(val) from turl;
select val, sys.getquery(val) from turl;
select val, sys.getroboturl(val) from turl;
select val, sys.getuser(val) from turl;

drop table turl;

Actual Results:

sql>create table turl (val url);
operation successful
sql>insert into turl values ('https://me@www.monetdb.org:458/Doc/Abc.html?lang=nl&sort=ascexample');
1 affected row
sql>insert into turl values (null);
1 affected row
sql>select * from turl;
+----------------------------------------------------------------------+
| val |
+======================================================================+
| https://me@www.monetdb.org:458/Doc/Abc.html?lang=nl&sort=ascexample |
| null |
+----------------------------------------------------------------------+
2 tuples
sql>
sql>select val, sys.getanchor(val) from turl;
bad url
sql>select val, sys.getbasename(val) from turl;
bad url
sql>select val, sys.getcontext(val) from turl;
bad url
sql>select val, sys.getdomain(val) from turl;
bad url
sql>select val, sys.getextension(val) from turl;
bad url
sql>select val, sys.getfile(val) from turl;
bad url
sql>select val, sys.gethost(val) from turl;
bad url
sql>select val, sys.getport(val) from turl;
bad url
sql>select val, sys.getprotocol(val) from turl;
bad url
sql>select val, sys.getquery(val) from turl;
bad url
sql>select val, sys.getroboturl(val) from turl;
bad url
sql>select val, sys.getuser(val) from turl;
bad url
sql>
sql>drop table turl;
operation successful

Expected Results:

sql>create table turl (val url);
operation successful
sql>insert into turl values ('https://me@www.monetdb.org:458/Doc/Abc.html?lang=nl&sort=ascexample');
1 affected row
sql>insert into turl values (null);
1 affected row
sql>select * from turl;
+----------------------------------------------------------------------+
| val |
+======================================================================+
| https://me@www.monetdb.org:458/Doc/Abc.html?lang=nl&sort=ascexample |
| null |
+----------------------------------------------------------------------+
2 tuples
sql>
sql>select val, sys.getanchor(val) from turl;
+----------------------------------------------------------------------+---------+
| val | L1 |
+======================================================================+=========+
| https://me@www.monetdb.org:458/Doc/Abc.html?lang=nl&sort=ascexample | example |
| null | null |
+----------------------------------------------------------------------+---------+
2 tuples
sql>select val, sys.getbasename(val) from turl;
+----------------------------------------------------------------------+------+
| val | L1 |
+======================================================================+======+
| https://me@www.monetdb.org:458/Doc/Abc.html?lang=nl&sort=ascexample | Abc |
| null | null |
+----------------------------------------------------------------------+------+
2 tuples
sql>select val, sys.getcontext(val) from turl;
+----------------------------------------------------------------------+---------------+
| val | L1 |
+======================================================================+===============+
| https://me@www.monetdb.org:458/Doc/Abc.html?lang=nl&sort=ascexample | /Doc/Abc.html |
| null | null |
+----------------------------------------------------------------------+---------------+
2 tuples
sql>select val, sys.getdomain(val) from turl;
+----------------------------------------------------------------------+------+
| val | L1 |
+======================================================================+======+
| https://me@www.monetdb.org:458/Doc/Abc.html?lang=nl&sort=ascexample | org |
| null | null |
+----------------------------------------------------------------------+------+
2 tuples
sql>select val, sys.getextension(val) from turl;
+----------------------------------------------------------------------+------+
| val | L1 |
+======================================================================+======+
| https://me@www.monetdb.org:458/Doc/Abc.html?lang=nl&sort=ascexample | html |
| null | null |
+----------------------------------------------------------------------+------+
2 tuples
sql>select val, sys.getfile(val) from turl;
+----------------------------------------------------------------------+----------+
| val | L1 |
+======================================================================+==========+
| https://me@www.monetdb.org:458/Doc/Abc.html?lang=nl&sort=ascexample | Abc.html |
| null | null |
+----------------------------------------------------------------------+----------+
2 tuples
sql>select val, sys.gethost(val) from turl;
+----------------------------------------------------------------------+-----------------+
| val | L1 |
+======================================================================+=================+
| https://me@www.monetdb.org:458/Doc/Abc.html?lang=nl&sort=ascexample | www.monetdb.org |
| null | null |
+----------------------------------------------------------------------+-----------------+
2 tuples
sql>select val, sys.getport(val) from turl;
+----------------------------------------------------------------------+------+
| val | L1 |
+======================================================================+======+
| https://me@www.monetdb.org:458/Doc/Abc.html?lang=nl&sort=ascexample | 458 |
| null | null |
+----------------------------------------------------------------------+------+
2 tuples
sql>select val, sys.getprotocol(val) from turl;
+----------------------------------------------------------------------+-------+
| val | L1 |
+======================================================================+=======+
| https://me@www.monetdb.org:458/Doc/Abc.html?lang=nl&sort=ascexample | https |
| null | null |
+----------------------------------------------------------------------+-------+
2 tuples
sql>select val, sys.getquery(val) from turl;
+----------------------------------------------------------------------+------------------+
| val | L1 |
+======================================================================+==================+
| https://me@www.monetdb.org:458/Doc/Abc.html?lang=nl&sort=ascexample | lang=nl&sort=asc |
| null | null |
+----------------------------------------------------------------------+------------------+
2 tuples
sql>select val, sys.getroboturl(val) from turl;
+----------------------------------------------------------------------+-------------------------------------------+
| val | L1 |
+======================================================================+===========================================+
| https://me@www.monetdb.org:458/Doc/Abc.html?lang=nl&sort=ascexample | https://me@www.monetdb.org:458/robots.txt |
| null | null |
+----------------------------------------------------------------------+-------------------------------------------+
2 tuples
sql>select val, sys.getuser(val) from turl;
+----------------------------------------------------------------------+------+
| val | L1 |
+======================================================================+======+
| https://me@www.monetdb.org:458/Doc/Abc.html?lang=nl&sort=ascexample | me |
| null | null |
+----------------------------------------------------------------------+------+
2 tuples
sql>
sql>drop table turl;
operation successful

I left out function sys.getcontent() as it will be removed, see 6843.

Comment 27857

Date: 2020-06-16 10:13:26 +0200
From: MonetDB Mercurial Repository <>

Changeset 4fbe5a6e053c made by Pedro Ferreira pedro.ferreira@monetdbsolutions.com in the MonetDB repo, refers to this bug.

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

Changeset description:

Handle NULL URLs. This fixes bug #6845.