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

Throws exception if a SQL/GIS takes argument null #2814

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

Throws exception if a SQL/GIS takes argument null #2814

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

Comments

@monetdb-team
Copy link

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

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.

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