Date: 2011-05-19 15:28:28 +0200
From: George Garbis <>
To: SQL devs <>
Version: 11.3.7 (Apr2011-SP2) [obsolete]
CC: charnik

Last updated: 2011-09-16 15:04:35 +0200

Comment 15823

Date: 2011-05-19 15:28:28 +0200
From: George Garbis <>

User-Agent: Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/534.24 (KHTML, like Gecko) Ubuntu/10.10 Chromium/11.0.696.65 Chrome/11.0.696.65 Safari/534.24
Build Identifier:

If i run a SQL/GIS function (e.g. mbr) and this function take null as argument the SQL front end throws exception instead of returning a null value.
Thus, user doesn't get any results only because of one null value in geometry column.
An execution scenario demonstrating this problem is:

sql>create table geom(id int, g Geometry);
sql>insert into geom values(1, GeomFromText('POINT(4 5)', 0));
sql>insert into geom values(2, GeomFromText('POLYGON((0 0, 10 0, 0 10, 0 0))', 0));
sql>insert into geom values(3, null);

sql>select id, mbr(g) from geom where id=1 or id=2;
+------+-----------------------------------------------+
| id | mbr_g |
+======+===============================================+
| 1 | BOX (4.000000 5.000000, 4.000000 5.000000) |
| 2 | BOX (0.000000 0.000000, 10.000000 10.000000) |
+------+-----------------------------------------------+
2 tuples
sql>select id, mbr(g) from geom where;
MALException:geom.mbr:Failed to create mbr

Reproducible: Always

The same happens with almost every GIS function.

I think that it is more useful if a null value is returned and a ternary logic(true, false, unknown) is used in boolean functions (as defined by 'OpenGIS® Implementation Specification for Geographic information - Simple feature access - Part 2: SQL option')

Comment 15826

Date: 2011-05-20 11:02:55 +0200
From: @grobian

this requires some patching of the geom module to recognise null values and return unknown early in the process

Comment 15893

Date: 2011-07-05 14:34:15 +0200
From: @sjoerdmullender

Changeset ca87abd89c7d 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=ca87abd89c7d

Changeset description:

Added test for bug #2814.

Comment 15894

Date: 2011-07-05 14:34:16 +0200
From: @sjoerdmullender

Changeset 802e11e14bd5 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=802e11e14bd5

Changeset description:

Added nil test to geom.mbr() call.
This fixes bug #2814.

Comment 15895

Date: 2011-07-05 14:34:53 +0200
From: @sjoerdmullender

A null test has been added.

Comment 15901

Date: 2011-07-06 11:03:16 +0200
From: George Garbis <>

Created attachment 63
Sql script that checks every function if given null argument

Thanks for the fix, but this problem appears in almost every GIS function.
I uploaded a script that demonstrates the problem in every function.

Attached file: FunctionsThatThrowNullException.sql (text/plain, 4558 bytes)
Description: Sql script that checks every function if given null argument

Comment 15931

Date: 2011-07-11 15:09:31 +0200
From: @sjoerdmullender

Changeset 40197cae30c1 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=40197cae30c1

Changeset description:

Added test from the attachment to bug #2814.

Comment 15932

Date: 2011-07-11 15:09:33 +0200
From: @sjoerdmullender

Changeset 0f3d56f8d7da 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=0f3d56f8d7da

Changeset description:

Test for nil and null.
This makes that the attachment from bug #2814 runs without crashing the
server.

Comment 15933

Date: 2011-07-11 17:11:20 +0200
From: @sjoerdmullender

I am closing this again since I added NULL and NIL checks to the code and the test you attached (thanks!) runs without crashing. Feel free to reopen if there is still a problem.
I don't really like the fact that you get errors for certain operations involving nil, but that cannot be changed in the Apr2011 branch (interface change). It could possibly be changed for a feature release. This would be a different bug/enhancement, though.

Comment 15986

Date: 2011-07-26 14:26:20 +0200
From: George Garbis <>

Sorry for opening this again but I am a bit confused.
I compiled the code of changeset http//devmonetdborg/hg/MonetDB?cmd=changeset;node=0f3d56f8d7da and while running the test script I, still, get the same exceptions.
Am I doing something wrong?
Saying 'runs without crashing' do you mean that you got rid of the exceptions?

Comment 16036

Date: 2011-07-29 11:00:04 +0200
From: @sjoerdmullender

Apr2011-SP2 has been released.

Comment 16050

Date: 2011-07-29 12:55:25 +0200
From: @sjoerdmullender

Changeset 62013099714d 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=62013099714d

Changeset description:

Implemented NULL checks in the geom module.
Now when given NULL as input, the module functions return NULL instead
of an exception.
This fixes bug #2814.

Comment 16051

Date: 2011-07-29 12:58:46 +0200
From: @sjoerdmullender

The test now runs without generating exceptions.
Note that the fix is in the Aug2011 branch.

Comment 16241

Date: 2011-09-16 15:04:35 +0200
From: @sjoerdmullender

The Aug2011 version has been released.