Subject Re: [IBO] TIBODataset.Locate causes error when using ORDER BY with column index
Author Svein Erling Tysvær
Post date 2018-10-26T07:08:02Z
Sorry for interfering. One thing I do not quite understand, Jason, is why

select field1, field2, field3,
       (select first 1 skip 0 somefield from sometable order by somefield) field4
from sometable2
order by 4, field1

uses

select field1
from sometable2
order by 4, field1

as an optimization query. To me, the more logical (and simple) optimization query would be:

select field1 from
(select field1, field2, field3,
       (select first 1 skip 0 somefield from sometable order by somefield) field4
from sometable2
order by 4, field1)

Or will this be less effective (I haven't tested anything and there are normally other reasons than optimization when I use CTEs)?
Set

Den ons. 24. okt. 2018 kl. 22:39 skrev gcobb.ibobjects@... [IBObjects] <IBObjects@yahoogroups.com>:


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.