select statements that include order by clauses

[Bug # 229518] Adaptive Server issues an error message if you perform a select from a table that:

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