2003.10.01 11:19 PM

VFP RI Bug, sans the KB

I was thinking about posting a list of the MS product bugs I've reported over the years that went on to become Knowledge Base articles (kind of a Greatest Hits post), but then discovered how long the list would be and reconsidered. It sure would be easier to do if MS would acknowledge in their KBs the folks who help them find their bugs, similar to the acknowledgements they give in their security bulletins. I've always figured that after finding and reproducing a bug, enduring an endless back and forth with MS support to prove it (lest I be charged for the call), and then most times coming up with my own workaround anyway, a simple acknowledgement would be the very least MS could do. After all, we are improving their products. Alas, it is not to be.

Anyway, while digging through my old emails in search of "email for case SRX..." messages from MS support, I ran into one from about a year ago that involved an acknowledgement by MS of a bug, but never resulted in a KB article, at least as far as I can tell. The bug involved Visual FoxPro version 6.0, but the MS response seemed to indicate that it affected 7.0 and possibly 8.0 as well. Having vowed to never do VFP again unless tortured or made fantastically rich, I haven't loaded anything after version 6.0 to see for myself. Here's the final response I got from MS:

"It sure does look like a bug! I have opened a bug entry against VFP8 and will let you know the moment I hear something. Thanks for reporting this!!"

So, here's the problem. If you have two tables where one table's name exactly matches the other table's name except for one or more additional characters on the beginning of the name (e.g., "earns" and "curearns", or "hours" and "fythours") and both tables appear on the right of an RI-enforced cascading relationship from the same parent table, and VFP builds the RI cascade logic for the shared left-side (parent) table in such a way that the longer named right-side table is processed first, and the second shorter-named table is related on a field for which there is a similarly named and indexed field in the first longer-named table, then the second shorter-named right-side table will not be processed by the cascade logic, no error will be raised, and no rollback will occur, thus leaving the shorter-named table in an inconsistent state. If the shorter-named table is related on a field for which there isn't also a same-named indexed field in the longer-named table, the cascade will not happen, an error will be raised, and all changes will be rolled back. Yuck.

The reason this happens is because at some point some MS programmer attempted to implement a workarea/order caching mechanism based on table names in the VFP-generated RI logic, and got it wrong.

When each right-side table is processed in the parent table's cascade logic, it is opened with an alias and ordered in a new or existing workarea via this function:

PROCEDURE riopen
PARAMETERS tcTable,tcOrder
local lcCurWkArea,lcNewWkArea,lnInUseSpot
lnInUseSpot=atc(tcTable+"*",pcRIcursors)
IF lnInUseSpot=0
  lcCurWkArea=select()
  SELECT 0
  lcNewWkArea=select()
  IF NOT EMPTY(tcOrder)
    USE (tcTable) AGAIN ORDER (tcOrder) ;
      ALIAS ("__ri"+LTRIM(STR(SELECT()))) share
  ELSE
    USE (tcTable) AGAIN ALIAS ("__ri"+LTRIM(STR(SELECT()))) share
  ENDIF
  if pnerror=0
    pcRIcursors=pcRIcursors+upper(tcTable)+"?"+STR(SELECT(),5)
  else
    lcNewWkArea=0
  endif something bad happened while attempting to open the file
ELSE
  lcNewWkArea=val(substr(pcRIcursors,lnInUseSpot+len(tcTable)+1,5))
  pcRIcursors = strtran(pcRIcursors,upper(tcTable)+"*"+str(lcNewWkArea,5),;
    upper(tcTable)+"?"+str(lcNewWkArea,5))
  IF NOT EMPTY(tcOrder)
    SET ORDER TO (tcOrder) IN (lcNewWkArea)
  ENDIF sent an order
  if pnerror<>0
    lcNewWkArea=0
  endif something bad happened while setting order
ENDIF
RETURN (lcNewWkArea)

I won't even attempt to explain the shenanigans going on here, but suffice it to say that the ATC() on pcRIcursors that occurs immediately upon entering the function attempts to determine whether the table of interest is available for a simple reordering in an existing workarea using an "*"-delimited list of table names followed by a 5-character padded workarea number. If the table is not found in the delimited list, it is opened, ordered, and added to the list with a "?" delimiter. If it is found, the table is reordered in place (if necessary) and the "*" delimiter is changed to a "?".

Immediately after processing each right-side table in the main cascade logic, the following function is called:

PROCEDURE rireuse
* rireuse.prg
PARAMETERS tcTableName,tcWkArea
pcRIcursors = strtran(pcRIcursors,upper(tcTableName)+"?"+str(tcWkArea,5),;
  upper(tcTableName)+"*"+str(tcWkArea,5))
RETURN .t.

This switches the "?" delimiter to an "*" delimiter. Don't know why, don't care.

The problem occurs in ripopen because our intrepid MS programmer only delimited the table names on the right, not on the left and right. So, for the imaginary tables "hours" and "fythours", when the main cascade logic does "fythours" first, pcRIcursors is left equal to "fythours*xxxxx". When it then processes the table "hours", the riopen's ATC() on pcRIcursors inappropriately finds a match on "hours*". This causes the routine to branch to the ELSE, where it gets the workarea from pcRIcursors using the ATC() result. As long as the longer-named table, which was processed first and is still the currently selected table, includes an index tag with the same name being sought for the shorter-named table, the order will be set successfully, no error will be raised, and the main cascade logic will process the longer-named table a second time. The shorter-named table will not be processed at all.

Unlikely? Maybe, but I found it. Of course, only after a number of my tables were left with orphaned rows, causing my client and me no end of trouble. Toss in the time and effort required to track this puppy down, convince MS that it's a real problem, resolve all the data errors, and implement a workaround involving changing the longer table's name (modifying the RI code was not even a possibility), and you have one very unhappy camper.

I suppose someone out there considers it a feature that referential integrity in VFP is enforced by readily accessible system-generated code. Personally, I think it's a crime. I don't want to see how RI sausage is made. I don't ask SQL Server or Access to show me how they handle RI enforcement, and I don't want to see how VFP does it either. And I certainly don't want to debug it for MS. At least not without an acknowledgement, or in this case even a KB.


Comments


TrackBack

TrackBack URL:  http://www.typepad.com/services/trackback/6a00d8341c7bd453ef00d83537b69069e2

Listed below are links to weblogs that reference VFP RI Bug, sans the KB: