I found a strange limitation on record length in a select query which I like to understand. I know there is a row limitation of 8088 bytes, but I thought that was on storage, not on retrieval.
The query is a join of 3 tables, one of these tables, t_messagelng, has a column type varchar(8000). The following query fails: SELECT a.idx as pidx, c.txt as mtxt FROM common.t_parameter as a, common.t_message as b, common.t_messagelng as c WHERE b.seqmsg = c.seqmsg AND a.msglabel = b.nam AND a.groupid = 'VR_PARKING' AND c.seqlng = 2 AND length(a.msglabel) > 2 The error: Auto Commit: On, SQL Mode: Internal, Isolation Level: Repeatable General error;-9008 POS(1) System error: BD Illegal record. After doing all kinds of rearranging where clauses that didn't work, I tested the query with a substr clause on the txt column: SELECT a.idx as pidx, substr(c.txt,1,7981) as mtxt FROM common.t_parameter as a, common.t_message as b, common.t_messagelng as c WHERE b.seqmsg = c.seqmsg AND a.msglabel = b.nam AND a.groupid = 'VR_PARKING' AND c.seqlng = 2 AND length(a.msglabel) > 2 Hurray it worked, and this is the output:(also far less than 8088 bytes) pidx mtxt ------------------------- 0 Afgesloten garage 2 Open garage 3 Openbare weg The length may not be more than 7981 bytes, less works, more not. However; select txt from t_messagelng generates no error and outputs the entire column length Question: What is the mechanism behind this. Is there a way to calculate that value or to foresee errors, because some records are really 8000 bytes long and we need the complete record. Is there a plan to overcome this limitation in future releases. Thanks, Cheers, Filip Sergeys -- *-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-* * System Engineer, Verzekeringen NV * * www.verzekeringen.be * * Oostkaai 23 B-2170 Merksem * * 03/6416673 - 0477/340942 * *-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-* -- MaxDB Discussion Mailing List For list archives: http://lists.mysql.com/maxdb To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
