ExecuteSQL's infinite loop (and DB corruption) after self-referencing query
Operating system version
OS X 10.6.8
Description of the issue
Let's get this out of the way first: I used ExecuteSQL in a way that's obviously flawed in retrospect. (Don't do this on a database you'd prefer to keep un-corrupt!)
If you define a calculation field to contain a self-referencing ExecuteSQL statement, FMP will hang while applying your schema changes. It's possible I didn't wait long enough before force-quitting, but it stands to reason FMP would be in an infinite loop at this point. The result was a damaged file that was successfully recoverable as far as I can tell, but who wants to inflict a needless recover on their files?
Steps to reproduce the problem
Again, DON'T DO THIS IN A DATABASE YOU WANT TO KEEP.
In a table called Sample, define a calculation field using this formula:
ExecuteSQL ( "SELECT * FROM Sample"; ""; "" )
In other words, the calculation field will be looking for all fields in the Sample table, which includes itself. That's where the infinite loop comes in. Presumably you could also enter a loop if the calculation field were named Calc and you asked for "SELECT Calc FROM Sample".
FileMaker to realize I'm an idiot.
FileMaker did what I asked and hung itself.
If a calculation field references the same table that it's in, never ever select all columns.