| 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: | |
| Category: | MySQL Server: MyISAM storage engine | Severity: | S2 (Serious) |
| Version: | 5.0.45 | OS: | Windows (WindowsXP64, x64 ver of MySQL ) |
| Assigned to: | Alexey Botchkov | CPU Architecture: | Any |
| Tags: | BLOB, spatial, 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]
Valeriy 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.
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.