Function with VARCHAR result exceeds length limit [CR 406638]

In Sybase IQ 12.6, the maximum size of CHAR data changed from 255 bytes to 32K, as described in New Features in Sybase IQ 12.6. Unless a constant expression is the supplied LENGTH argument to a function that produces a VARCHAR result (such as SPACE or REPEAT), the default length is the maximum allowed.

Sybase IQ 12.6 queries containing one or more of such functions may return one of the following errors:

• ASA Error -1009080: Key doesn't fit on a single
  database page: 65560(4, 1)
  

• ASA Error -1009119: Record size too large for
  database page size.
  

For example:

SELECT COUNT(*) FROM test1 a WHERE 
(a.col1 + SPACE(4-LENGTH(a.col1)) + 
a.col2 + space(2- LENGTH(a.col2))) 
IN (SELECT (b.col3) FROM test1 b);

To avoid such errors, cast the function result with an appropriate maximum length, for example:

SELECT COUNT(*) FROM test1 a WHERE 
(a.col1 + CAST(SPACE(4-LENGTH(a.col1)) 
AS VARCHAR(4)) + a.col2 + 
CAST(SPACE(2-LENGTH (a.col2))
AS VARCHAR(4))) IN (SELECT (b.col3) FROM test1 b);

The errors are more likely with an IQ page size of 64K or a multibyte collation.