| Bug #31158 | Spatial, Union, LONGBLOB vs BLOB bug (crops data) | ||
|---|---|---|---|
| Submitted: | 23 Sep 2007 16:59 | Modified: | 15 Jan 2008 17:29 |
| Reporter: | Artur Bać | ||
| Status: | Closed | ||
| Category: | Server: MyISAM | Severity: | S2 (Serious) |
| Version: | 5.0.45 | OS: | Microsoft Windows (WindowsXP64, x64 ver of MySQL ) |
| Assigned to: | Alexey Botchkov | Target Version: | |
| Tags: | UNION, BLOB, spatial | ||
| Triage: | D2 (Serious) | ||
[23 Sep 2007 17: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 17:54]
Valeriy Kravchuk
Thank you for a bug report. Verified just as descibed.
[7 Nov 2007 11: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 7: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 9:13]
Bugs System
Pushed into 4.1.24
[14 Dec 2007 9:14]
Bugs System
Pushed into 5.0.54
[14 Dec 2007 9:17]
Bugs System
Pushed into 5.1.23-rc
[14 Dec 2007 9:20]
Bugs System
Pushed into 6.0.5-alpha
[15 Jan 2008 17: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.