I have a TIBOQuery where our code called the Locate function on it.  It results in a SQL error.  After researching the issue it turns out the reason is that one of the parts of the ORDER BY clause for the query orders by column index instead of a field name because the column not a standard field but the resultant of a subquery in the select clause.  After stepping into the IBO code, I noticed Locate call results in the use of TIB_LocateCursor.  As far as I can tell, it tries to have the locate look in the query's buffer, but if it doesn't find what you are looking for it then crafts a new sql statement and tries to search for the desired record by executing a new record.  I'm guessing this has some kind of performance benefits vs what the BDE TQuery does in some scenarios.  When it crafts this new query though, it apparently is trying to only include the fields you are searching on in the select clause while keeping the where and order by clause intact.  This is a problem if your order by clause includes a column index and that index is greater than the fields returned or it could alter the intended result if that number is less than or equal to the number of columns in the new query but now points to a new column.

Example:

select

  field1,

  field2,

  field3,

  (select first 1 skip 0 somefield from sometable order by somefield) field4

from

  sometable2

order by

  4,

  field1

Now let's say that after executing that query we did a Locate call on it trying to find the record where field1 equaled some value.  The query could end up having TIB_LocateCursor craft a sql statement like the following:

select

  field1

from

  sometable2

order by

  4,

  field1

This will cause a SQL error because there is only 1 column in this new sql statement but the order by clause points to column 4 which no longer exists in this new query.

I'm not sure why Locate doesn't just loop over the dataset like the original BDE components did.  I suspect this is a performance optimization.  If calling another query is desirable then the query really needs to be correct.  In this scenario, IBO should have decided that it also needed to include "field4" in the TIB_LocateCursor query and it would then need to modify the order by clause to first order by index 2 instead of 4.