informix-community

Open full view…

Error -528 Maximum output rowsize (32767) exceeded

s-kip
Thu, 07 Feb 2019 11:28:15 GMT

Hi there, i'm developing on a Informix database (v. 12FC9DE) and getting the error -528 (Maximum output rowsize exceeded) when executing following SELECT: SELECT genbson(row("typ", "name", "sortName", "longName", "shortName", "status", "dates", "comment", "customer"), 1, 1)::JSON FROM ( (SELECT genbson(row("name", "shortName"), 1, 1) as "typ", '{"de":"Max"}'::JSON::BSON as "name", '{"de":"Mustermann Max"}'::JSON::BSON as "sortName", '{"de":"Max Mustermann"}'::JSON::BSON as "longName", '{"de":"M. Mustermann"}'::JSON::BSON as "shortName", 'A' as "status", genbson(row("street", "number", "zipCode", "city"), 1, 1) as "address", genbson(row("phone","fax", "mailaddress", "website"), 1, 1) as "contact", genbson(row("birthDate", "retireDate"), 1, 1) as "dates", 'red' as "comment", genbson(row("id"), 1, 1) as "customer" FROM ( (SELECT '{"de":"Person"}'::JSON::BSON as "name", '{"de":"Person"}'::JSON::BSON as "shortName", 'Hauptstraße' as "street", '1' as "number", '12345' as "zipCode", 'Musterhausen' as "city", '012-3456789' as "phone", '012-34567810' as "fax", 'info@mustermann.de' as "mailaddress", 'www.mustermann.de' as "website", '1980-02-27'::date as "birthDate", null::date as "retireDate", 'abdgt-756474-eerrr' as "id" FROM systables where tabid = 1) as a) ) as b); With this statement I try to combine several columns to one JSON/BSON-object. The strings in this example that are converted via ::JSON::BSON are in a stored procedure already combined values. By changing two of these values to normal strings the error does not show up and the SELECT is successful: SELECT genbson(row("typ", "name", "sortName", "longName", "shortName", "status", "dates", "comment", "customer"), 1, 1)::JSON FROM ( (SELECT genbson(row("name", "shortName"), 1, 1) as "typ", '{"de":"Max"}'::JSON::BSON as "name", '{"de":"Mustermann Max"}'::JSON::BSON as "sortName", '{"de":"Max Mustermann"}' as "longName", '{"de":"M. Mustermann"}' as "shortName", 'A' as "status", genbson(row("street", "number", "zipCode", "city"), 1, 1) as "address", genbson(row("phone","fax", "mailaddress", "website"), 1, 1) as "contact", genbson(row("birthDate", "retireDate"), 1, 1) as "dates", 'red' as "comment", genbson(row("id"), 1, 1) as "customer" FROM ( (SELECT '{"de":"Person"}'::JSON::BSON as "name", '{"de":"Person"}'::JSON::BSON as "shortName", 'Hauptstraße' as "street", '1' as "number", '12345' as "zipCode", 'Musterhausen' as "city", '012-3456789' as "phone", '012-34567810' as "fax", 'info@mustermann.de' as "mailaddress", 'www.mustermann.de' as "website", '1980-02-27'::date as "birthDate", null::date as "retireDate", 'abdgt-756474-eerrr' as "id" FROM systables where tabid = 1) as a) ) as b); I use DBeaver or DataStudio to execute the SELECT. In both the error appears. My question is: why? ;) Any suggestions on this? Is it possibly a known bug or am I doing something wrong? Best regards S. Kip

Jonathan Leffler
Mon, 25 Feb 2019 22:38:07 GMT

This question was also asked on Stack Overflow as [Informix error -528: maximum rowsize 32767 exceeded](https://stackoverflow.com/questions/54572473/informix-error-528-maximum-output-rowsize-32767-exceeded). There's a useful, valid comment there from Luis Marques, posted on 2019-02-07: >[It seems] Informix has a hard limit of 32KiB for row size. JSON and BSON rows are considered to have a length of 4100 Bytes. So with 9 BSON/JSON columns you are at 36 KiB, above the 32 KiB limit (tables can have rows larger than 32 KiB, but they have to use types that are stored "off row", like TEXT, BYTE, BLOB, CBLOB). The actual size for the BSON/JSON object is 4,096 bytes and the maximum row size is 32,767 bytes, but it still limits you to no more than 7 columns. You can legitimately consider that this is not obvious; it also might not be properly documented, or it may only be documented in obscure places. It is not strictly a bug, but a "Request for Enhancment" might be appropriate. I have not fully investigated the scope of the issue.

andreasl
Fri, 22 Mar 2019 15:39:52 GMT

To get the above query running you'd cast all those genbson(...) calls to lvarchar(some_sensible_length), e.g. genbson(...)::json::lvarchar(200).