Bug #26038 X() value of empty NOT NULL POINT is neither NULL nor NOT NULL
Submitted: 2 Feb 2007 11:27 Modified: 15 Mar 2007 15:27
Reporter: Peter Brodersen (Candidate Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.32-Dotdeb_1.dotdeb.1-log, 4.1 BK, 5.1 BK OS:Linux (Linux)
Assigned to: Ramil Kalimullin CPU Architecture:Any

[2 Feb 2007 11:27] Peter Brodersen
Description:
If a field of type POINT with NOT NULL constraint contains empty data, X(field) would be of type NULL, however both X(field) IS NULL and X(field) IS NOT NULL would return 0.

For what it's worth, Y() behaves the same way as X().

How to repeat:
CREATE TABLE geom (id int, location POINT NOT NULL);
INSERT INTO geom (id) VALUES (1),(2),(3);
SELECT id, location, location IS NULL, location IS NOT NULL, X(location), X(location) IS NULL, X(location) IS NOT NULL FROM geom\G

*************************** 1. row ***************************
                     id: 1
               location:
       location IS NULL: 0
   location IS NOT NULL: 1
            X(location): NULL
    X(location) IS NULL: 0
X(location) IS NOT NULL: 0
*************************** 2. row ***************************
                     id: 2
               location:
       location IS NULL: 0
   location IS NOT NULL: 1
            X(location): NULL
    X(location) IS NULL: 0
X(location) IS NOT NULL: 0
*************************** 3. row ***************************
                     id: 3
               location:
       location IS NULL: 0
   location IS NOT NULL: 1
            X(location): NULL
    X(location) IS NULL: 0
X(location) IS NOT NULL: 0

The value of X(location) is NULL, but the value of X(location) IS NULL should be 1.
[2 Feb 2007 12:30] Sveta Smirnova
Thank you for the report.

Verified as described.
[21 Feb 2007 10:45] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/20254

ChangeSet@1.2609, 2007-02-21 14:45:19+04:00, ramil@mysql.com +6 -0
  Fix for bug #26038: X() value of empty NOT NULL POINT is neither NULL nor NOT NULL
  
  Having maybe_null flag unset for geometry/spatial functions leads to
  wrong Item_func_isnull::val_int()'s results.
  Fix: set maybe_null flag and add is_null() methods.
[21 Feb 2007 11:05] Alexander Barkov
I think the patch is ok to push.
[8 Mar 2007 20:14] Timothy Smith
pushed to 4.1.23, 5.0.38, 5.1.17
[15 Mar 2007 15:27] Paul Dubois
Noted in 4.1.23, 5.0.38, 5.1.17 changelogs.

X() IS NULL and Y() IS NULL comparisons failed when X() and Y()
returned NULL.