Bug #31158 Spatial, Union, LONGBLOB vs BLOB bug (crops data)
Submitted: 23 Sep 2007 14:59 Modified: 15 Jan 2008 16:29
Reporter: Artur Bać Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: MyISAM storage engine Severity:S2 (Serious)
Version:5.0.45 OS:Microsoft Windows (WindowsXP64, x64 ver of MySQL )
Assigned to: Alexey Botchkov
Tags: BLOB, spatial, UNION
Triage: D2 (Serious)

[23 Sep 2007 14:59] Artur Bać
Description:
There is bug with UNION and size of result datatype with AsBinary(geometry) and union of 2 tables if the first query returns LONGBLOB and second BLOB the first result BLOB data is croped to 65535 bytes.
As We cannot control the type (LONGBLOB, BLOB) resulted by AsBinary(some GEOMETRY)
there is serius problem with union

How to repeat:
In oceans there is one big polygon with ~30000 vertices
In waterpoly there are small polygons with <1000 vertices

CREATE TABLE "oceans_spat" (
  "geomdata" polygon NOT NULL,
  SPATIAL KEY "index_geom" ("geomdata")
) ENGINE=MyISAM DEFAULT CHARSET=latin2 DELAY_KEY_WRITE=1 ROW_FORMAT=FIXED;

CREATE TABLE "waterpoly_spat" (
  "geomdata" polygon NOT NULL,
  SPATIAL KEY "index_geom" ("geomdata")
) ENGINE=MyISAM DEFAULT CHARSET=latin2 DELAY_KEY_WRITE=1 ROW_FORMAT=FIXED;

Test case view 1 - WRONG RESULT

  select 
    aswkb("ws"."geomdata") AS "geomdatawkb" 
  from 
    "oceans_spat" "ws" 
union 
  select 
    aswkb("ws"."geomdata") AS "geomdatawkb" 
  from 
    "waterpoly_spat" "ws";

RESULT
describe voceans;
+-------------+------+------+-----+---------+-------+
| Field       | Type | Null | Key | Default | Extra |
+-------------+------+------+-----+---------+-------+
| geomdatawkb | blob | YES  |     | NULL    |       |
+-------------+------+------+-----+---------+-------+
1 row in set

Test case 2 - RESULT OK

CREATE ALGORITHM=UNDEFINED DEFINER="root"@"localhost" SQL SECURITY DEFINER VIEW "voceans_ok" AS 
  select 
    aswkb("ws"."geomdata") AS "geomdatawkb" 
  from 
    "oceans_spat" "ws";

describe voceans_ok;
+-------------+----------+------+-----+---------+-------+
| Field       | Type     | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+-------+
| geomdatawkb | longblob | YES  |     | NULL    |       |
+-------------+----------+------+-----+---------+-------+
1 row in set

Suggested fix:

BLOB type should be chosed of the longest size in UNION.
[23 Sep 2007 15:15] Artur Bać
There is some workaround joining real empty table with real field of type LONGBLOB

Then result type is LONGBLOB and data is not croped to 65535 bytes.

CREATE TABLE "v_fix_lb" (
  "geomdatawkb" longblob
) ENGINE=MyISAM DEFAULT CHARSET=latin2 ROW_FORMAT=FIXED;

select 
    "v_fix_lb"."geomdatawkb" AS "geomdatawkb" 
  from 
    "v_fix_lb" union 
  select 
    aswkb("ws"."geomdata") AS "geomdatawkb" 
  from 
    "waterpoly_spat" "ws" union 
  select 
    aswkb("ws"."geomdata") AS "geomdatawkb" 
  from 
    "oceans_spat" "ws";
[23 Sep 2007 15:54] Valerii Kravchuk
Thank you for a bug report. Verified just as descibed.
[7 Nov 2007 10:56] 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/37245

ChangeSet@1.2688, 2007-11-07 13:50:42+04:00, holyfoot@mysql.com +3 -0
  Bug #31158  Spatial, Union, LONGBLOB vs BLOB bug (crops data)
  
  max_length parameter for BLOB-returning functions must be big enough
  for any possible content. Otherwise the field created for a table
  will be too small.
[13 Nov 2007 6:56] 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/37633

ChangeSet@1.2688, 2007-11-13 10:51:47+04:00, holyfoot@mysql.com +5 -0
  Bug #31158  Spatial, Union, LONGBLOB vs BLOB bug (crops data)
  
  max_length parameter for BLOB-returning functions must be big enough
  for any possible content. Otherwise the field created for a table
  will be too small.
[14 Dec 2007 8:13] Bugs System
Pushed into 4.1.24
[14 Dec 2007 8:14] Bugs System
Pushed into 5.0.54
[14 Dec 2007 8:17] Bugs System
Pushed into 5.1.23-rc
[14 Dec 2007 8:20] Bugs System
Pushed into 6.0.5-alpha
[15 Jan 2008 16:29] MC Brown
A note has been added to the 4.1.24, 5.0.54, 5.1.23 and 6.0.5 changelogs: 

Data in BLOB or GEOMETRY columns could be cropped when performing a UNION query.