MySQL Bugs: #21888: Query on GEOMETRY field using PointFromWKB() results in lost connection

Bug #21888	Query on GEOMETRY field using PointFromWKB() results in lost connection
Submitted:	28 Aug 2006 23:43	Modified:	23 Oct 2006 14:26
Reporter:	Peter Brodersen (Candidate Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S2 (Serious)
Version:	5.1.12-beta-log, 5.0.24-max-log\4.1BK	OS:	Linux (linux\windows)
Assigned to:	Alexey Botchkov	CPU Architecture:	Any
Tags:	crash, Geometry

Description:
A simple query where a field of type geometry is queried makes the server drop the connection (and possibly crashes the current server child process).

The crash only seem to occur when zero-valued coordinates are present in the table.

How to repeat:
CREATE TABLE geom (foo GEOMETRY NOT NULL, SPATIAL INDEX(foo) );
INSERT INTO geom (foo) VALUES (PointFromWKB(POINT(1,1)));
INSERT INTO geom (foo) VALUES (PointFromWKB(POINT(1,0)));
INSERT INTO geom (foo) VALUES (PointFromWKB(POINT(0,1)));
INSERT INTO geom (foo) VALUES (PointFromWKB(POINT(0,0)));
SELECT 1 FROM geom WHERE foo != PointFromWKB(POINT(0,0));

ERROR 2013 (HY000): Lost connection to MySQL server during query

Call Stack 5.1.12-beta-nt on Windows 2003 32-bits

Attachment: bt-windows-21888.txt (text/plain), 2.46 KiB.

Call stack attached:

D:\mysql\bin>mysqladmin -uroot create db1

D:\mysql\bin>mysql -uroot db1
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4 to server version: 5.1.12-beta-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE geom (foo GEOMETRY NOT NULL, SPATIAL INDEX(foo) );
Query OK, 0 rows affected (0.09 sec)

mysql> INSERT INTO geom (foo) VALUES (PointFromWKB(POINT(1,1)));
Query OK, 1 row affected (0.03 sec)

mysql> INSERT INTO geom (foo) VALUES (PointFromWKB(POINT(1,0)));
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO geom (foo) VALUES (PointFromWKB(POINT(0,1)));
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO geom (foo) VALUES (PointFromWKB(POINT(0,0)));
Query OK, 1 row affected (0.00 sec)

mysql> SELECT 1 FROM geom WHERE foo != PointFromWKB(POINT(0,0));
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql>

Back Trace 5.0.25BK on Suse 10 32-bits

Attachment: bt-linux-21888.txt (text/plain), 11.74 KiB.

Thank you for the bug report. I was able to repeat.
Back trace attached for 5.0.25BK Linux.

miguel@hegel:~/dbs/5.0> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.25-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE geom (foo GEOMETRY NOT NULL, SPATIAL INDEX(foo) );
NSERT INTO geom (foo) VALUES (PointFromWKB(POINT(1,1)));
INSERT INQuery OK, 0 rows affected (0.02 sec)

TO gemysql> INSERT INTO geom (foo) VALUES (PointFromWKB(POINT(1,1)));
ALUES (PointFromWKB(POINT(1,0)));
INSERT INTO geom (foo) VALUES (PointFromWKB(POINT(0,1)));Query OK, 1 row affected (0.04 sec)

INmysql> INSERT INTO geom (foo) VALUES (PointFromWKB(POINT(1,0)));
O geom (foo) VALUES (PointQuery OK, 1 row affected (0.01 sec)

Frommysql> INSERT INTO geom (foo) VALUES (PointFromWKB(POINT(0,1)));
0,0)));
SELECT 1 FROM geom Query OK, 1 row affected (0.01 sec)

WHEmysql> INSERT INTO geom (foo) VALUES (PointFromWKB(POINT(0,0)));
intFromWKB(POINT(0,0)Query OK, 1 row affected (0.01 sec)

);mysql> SELECT 1 FROM geom WHERE foo != PointFromWKB(POINT(0,0));
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql>

-----------------------------------------------------------------
miguel@hegel:~/dbs/4.1> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.22-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE geom (foo GEOMETRY NOT NULL, SPATIAL INDEX(foo) );
INSERT INTO geom (foo) VALUES (PointFromWKB(POINT(1,1)));
IQuery OK, 0 rows affected (0.02 sec)

NSEmysql> INSERT INTO geom (foo) VALUES (PointFromWKB(POINT(1,1)));
T INTO geom (foo) VALUES (PointFromWQuery OK, 1 row affected (0.02 sec)

KBmysql> INSERT INTO geom (foo) VALUES (PointFromWKB(POINT(1,0)));
Query OK, 1 row affected (0.00 sec)

Imysql> INSERT INTO geom (foo) VALUES (PointFromWKB(POINT(0,1)));
IQuery OK, 1 row affected (0.00 sec)

mysql> INSERT INTO geom (foo) VALUES (PointFromWKB(POINT(0,0)));
SEQuery OK, 1 row affected (0.00 sec)

mysql> SELECT 1 FROM geom WHERE foo != PointFromWKB(POINT(0,0));
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql>

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/12040

ChangeSet@1.2528, 2006-09-15 20:28:21+05:00, holyfoot@mysql.com +3 -0
  bug #21888 (Query on GEOMETRY crashes server)

Looks ok to push.

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/12837

ChangeSet@1.2573, 2006-09-29 17:56:02+05:00, holyfoot@mysql.com +3 -0
  bug #21888 (Query on GEOMETRY field crashes the server)
  
  RTree keys are really different from BTree and need specific
  paramters to be set by optimizer to work.
  Sometimes optimizer doesn't set those properly.
  Here we decided just to add code to check that the parameters
  are correct. Hope to fix optimizer sometimes.

Pushed in 4.1.22/5.0.27/5.1.13-beta

Noted in 4.1.22, 5.0.27, 5.1.13 changelogs.

The optimizer sometimes mishandled R-tree indexes for GEOMETRY data types,
resulting in a server crash.