Author Topic: Seriously BAD SB+ Issue!  (Read 4851 times)

precisonline

  • President
  • Administrator
  • Rock Star
  • *****
  • Posts: 1612
    • Precision Solutions
Seriously BAD SB+ Issue!
« on: January 19, 2009, 09:55:21 PM »
About a week ago, I created an index for a customer to improve some selecting against their invoice file.  Today I learned that since that index was created, some updates against A DIFFERENT FILE were failing to complete properly.  Odd, you say?  How can an index on one file impact another one negatively?  Believe it or not, it happened, and here's how:

The field that was indexed had an expression like this:

(FIELD(<0>[4,99],'-',1))

Seems innocuous enough: Take the value of <0> (i.e. the key), strip off the first three characters and then return the first dash-delimited part.  So if the key is:

0011234567-1

...this would return:

1234567

The goal of this index was to make it easy to select all of the records with that 1234567 part so that we could do some calculations on the selected records as a group.  Actually, that part worked really well.  We were able to select and group the records out of the file with very little I/O or time.

However, there is another (SB+ enabled BASIC) routine that creates and updates the records in this file.  When that routine wrote a record into this file with the index, immediately after the WRITE the common variables were shifted and KEY no longer held the key to the record and RECORD no longer held the record!  Certainly when looking at BASIC you don't expect the common map to get all jumbled by a WRITE, but that seems to be exactly what happened.

Seems the expression (FIELD(...)) converts to a call to SB.SMART.QUERY when "SB+ Subroutines in Dictionaries" is enabled (via /SB.PARMS).  When that field with the SB.SMART.QUERY call is then indexed, the SMART.QUERY routine - for whatever reason - totally jacks up the common map so that anything to follow is going to fall down very ungracefully.  I mean, doesn't it seem unreasonable that the entire common memory shifts by doing a WRITE in a BASIC subroutine?

So as a general rule, I will take more care to ensure that I never index a field that calls SB.SMART.QUERY.  It may be that it has something to do with the specifics of that expression (like maybe the <0>?) but until we find out what makes this standard SB+ routine fall down, probably best to avoid it rather than risk the type of data corruption that I spent most of today, and likely will spend most of tomorrow, correcting.
-Kevin
Accidents "happen"; success, however, is planned and executed.