MySQL Bugs: #10670: errors on geometry data in resultset

Bug #10670	errors on geometry data in resultset
Submitted:	16 May 2005 14:20	Modified:	23 May 2005 7:46
Reporter:	Mauro Gajetti	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:	3.1.6+	OS:	Windows (windows)
Assigned to:		CPU Architecture:	Any

Description:
I tried to do a select using a spatial query both with a statement and a prepared statement it seems they produce different resultset.
The resultset of the prepared statment is wrong, it has bad data in the geometry column

How to repeat:
I tried to do a select both with a statement and a prepared statement
this is the code:
Statement stm = conn.createStatement();

s = "SELECT cat, name, AsWKB(point) FROM divdbpois WHERE MBRIntersects(Envelope(GeomFromText('LineString("+ minX+ " " + minY +", "+ maxX+ " " + maxY+ ")')), point)";

rs = stm.executeQuery(s);

and it works, 
but when i tried to use a prepared statement the resultset contained bad coordinates:

pStm = conn.prepareStatement("SELECT cat, name, AsWKB(point) FROM divdbpois WHERE MBRIntersects(Envelope(GeomFromText(?)), point)");

s = "LineString("+ minX+ " " + minY +", "+ maxX+ " " + maxY + ")";
pStm.setString(1, s);
rs = pStm.executeQuery();

I'm working on windows xp pro, mysql server 4.1.9-11 (all the same problem)
I'm don't know if the problem is on the server or on the connector

Hi! Could you please put here your table definition (you can get it by 'show create table TABLENAME' command) and values for a couple of rows, that we can try your request..

Thanks for the quick reply
this is the table definition:

CREATE TABLE `divdbpois` (
  `id` int(10) unsigned NOT NULL default '0',
  `cat` int(10) unsigned NOT NULL default '0',
  `name` varchar(45) NOT NULL default '0',
  `point` point NOT NULL default '',
  PRIMARY KEY  (`id`),
  SPATIAL KEY `pointIdx` (`point`(32))
) ENGINE=MyISAM DEFAULT CHARSET=latin1

and this is a sample:
50925490, 4441, 'Pino sulla  Sponda del Lago Maggiore', 'POINT(873665 4610034)'
50927521, 4441, 'Viggiù', 'POINT(890955 4587199)'
50925104, 4443, 'Olgiate Comasco', 'POINT(896756 4578352)'
50922034, 4441, 'Bodio Lomnago', 'POINT(874730 4579069)'
50923472, 4441, 'Cuveglio', 'POINT(873333 4590440)'
50925827, 4441, 'Rancio Valcuvia', 'POINT(877305 4591540)'
50924358, 4441, 'Lonate Ceppino', 'POINT(887557 4570419)'
50922901, 4441, 'Cavargna', 'POINT(911214 4608997)'
50926511, 4441, 'Sant'Abbondio', 'POINT(925789 4605156)'
50923126, 4441, 'Cino', 'POINT(948797 4615804)'
50927198, 4441, 'Trezzone', 'POINT(935356 4617180)'
50927411, 4441, 'Veleso', 'POINT(918021 4590686)'
50925482, 4441, 'Pigra', 'POINT(912399 4595584)'
50923607, 4441, 'Erve', 'POINT(945084 4581938)'
50923838, 4442, 'Galbiate', 'POINT(937617 4581664)'
50924937, 4441, 'Morterone', 'POINT(948230 4587410)'
50924400, 4441, 'Luisago', 'POINT(903689 4576119)'
545790023, 4441, 'Orsenigo', 'POINT(917708 4577824)'
50922724, 4441, 'Cassago Brianza', 'POINT(928922 4573410)'
50924882, 4441, 'Montevecchia', 'POINT(938037 4570462)'

Sorry...but I could't repeat the bug. I created test case and both Statement and prepareStatement return right values:

D:\STORE ODBC FILES\10670>java test
Test 1....
Lonate Ceppino
Montevecchia
Test 2....
Lonate Ceppino
Montevecchia

My test case is attached.

fill out data into table

Attachment: 1.sql (text/plain), 2.02 KiB.

Test case

Attachment: test.java (text/plain), 1.50 KiB.