[Bug # 229518] Adaptive Server issues an error message if you perform a select from a table that:
Includes a column, or columns, that are very close to the maximum row size for the server’s logical page size (this problem does not occur if the sum of all the row sizes approach the maximum row size), and:
Includes an order by clause, and:
Uses the following format:
select substring(column_1, position, string_size) from table_name order by column_name
For example, if you create the following table:
create table documents(doc_id int, doc_text char(16215) doc_author varchar(50)) insert documents values (1, "Is it OK?", "Jim O'Neal") insert documents values (2, "No", "Judy Dermott")
And perform this select on it:
select substring(doc_text, 50, 20) from documents order by doc_text
Adaptive Server issues this error message:
Attempt to update or insert row failed because resultant row of size 16301 bytes is larger than the maximum size (16298 bytes) allowed for this table
For this query, order by uses the column doc_text and not the substring() column. By default, substring() is defined to be nullable, which means that the result of the substring() is a nullable column. The order by requires a sort, and creation of a worktable, which looks like this:
select doc_text, substring(doc_text, 50, 20) as col2
The error occurs because the worktable is created as an all-pages-locked (APL) table. For APL row formats, Adaptive Server uses a row-length of 16295 bytes for a fixed-length column (in this case, column doc_text), followed by the varchar column doc_author. For the offset and adjust tables, the total row length for column c1 is 16366 bytes, which is larger than an APL row allows.
Workaround: Issue a select statement organized like the following, which orders the data by the substring() expression, and not column doc_text:
select substring(doc_text, 50, 20) from documents order by 1
