MySQL Bugs: #6553: STRICT mode: Bad spatial values don't cause errors

Bug #6553	STRICT mode: Bad spatial values don't cause errors
Submitted:	10 Nov 2004 17:02	Modified:	30 Apr 2005 13:31
Reporter:	Paul DuBois	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.x	OS:
Assigned to:	Alexey Botchkov	CPU Architecture:	Any

Description:
In strict mode, we get rejection of illegal values. For example:

mysql> drop table if exists t;
Query OK, 0 rows affected, 1 warning (0.09 sec)

mysql> create table t (i tinyint) engine=myisam;
Query OK, 0 rows affected (0.04 sec)

mysql> set sql_mode='strict_all_tables';
Query OK, 0 rows affected (0.06 sec)

mysql> insert into t set i = 127;
Query OK, 1 row affected (0.04 sec)

mysql> insert into t set i = 128;
ERROR 1264 (22003): Out of range value adjusted for column 'i' at row 1

However, this doesn't occur for spatial values.  The following
sequence of statements inserts a legal point value, which succeeds.
Then it inserts an illegal point without and with traditional mode.
The results are the same in both cases.
mysql> set sql_mode='';
Query OK, 0 rows affected (0.00 sec)

mysql> drop table if exists geom;
Query OK, 0 rows affected (0.01 sec)

mysql> create table geom(g point) engine=myisam;
Query OK, 0 rows affected (0.02 sec)

mysql> insert into geom values(pointfromtext('point(1 1)'));
Query OK, 1 row affected (0.01 sec)

mysql> insert into geom values(pointfromtext('point(1,1)'));
Query OK, 1 row affected (0.01 sec)

mysql> set sql_mode='strict_all_tables';
Query OK, 0 rows affected (0.14 sec)

mysql> insert into geom values(pointfromtext('point(1,1)'));
Query OK, 1 row affected (0.14 sec)

mysql> select astext(g) from geom;
+------------+
| astext(g)  |
+------------+
| POINT(1 1) |
| NULL       |
| NULL       |
+------------+
3 rows in set (0.00 sec)

How to repeat:
Here is the script without the prompts and output:

drop table if exists t;
create table t (i tinyint) engine=myisam;
set sql_mode='strict_all_tables';
insert into t set i = 127;
insert into t set i = 128;

set sql_mode='';
drop table if exists geom;
create table geom(g point) engine=myisam;
insert into geom values(pointfromtext('point(1 1)'));
insert into geom values(pointfromtext('point(1,1)'));
set sql_mode='strict_all_tables';
insert into geom values(pointfromtext('point(1,1)'));
select astext(g) from geom;

Suggested fix:
Generate an error for bad spatial values in strict mode.

Verified with 5.0.2-alpha-debug-log

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/internals/22994

Still some more work to be done on the patch:
- Better error message

If possible (if not now, then at least later):
- For wrong values in not strict mode, insert null or "" into the geometry field.
(This is to avoid cases where only a part of the rows in a set are inserted)

Still some more work to be done on the patch:
- Better error message

If possible (if not now, then at least later):
- For wrong values in not strict mode, insert null or "" into the geometry field.
(This is to avoid cases where only a part of the rows in a set are inserted)

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html