Just taking a stab here but if the total row size is max = 8088 and your varchar field is taking up 8000 then that only leaves you 88 bytes for the other columns in the table. Are you sure that you only have 88 bytes worth additional columns.
This could be why truncating the text field to a smaller size allows data to be returned as you mentioned below. -----Original Message----- From: Filip Sergeys [mailto:[EMAIL PROTECTED] Sent: Thursday, December 18, 2003 8:32 AM To: PcgScrapAddy Cc: [EMAIL PROTECTED] Subject: RE: strange record length limitation in select query I checked the columns; the longest field is 7999. THe columns itself is a varchar(8000).So that should be OK But you did point me to the fact that I forgot to include some important information in my original post: Maxdb version 7.5.00.05 OS: SuSE Linux Regards, Filip Sergeys On Thu, 2003-12-18 at 15:02, PcgScrapAddy wrote: > If you are using a 7.3.x.x version you may want to check the set field sizes > vs. the actual data length in the field. I had a situation where the amount > of data stored in the field was longer than the defined size. Upon fetch the > query would not return any data. Once I set the offending field's size to be > greater than the data contained in it the query returned data as expected. > > -----Original Message----- > From: Filip Sergeys [mailto:[EMAIL PROTECTED] > Sent: Thursday, December 18, 2003 4:05 AM > To: [EMAIL PROTECTED] > Subject: strange record length limitation in select query > > > 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] > > -- *-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-* * 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] -- MaxDB Discussion Mailing List For list archives: http://lists.mysql.com/maxdb To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
