At 08:10 AM 20/03/2008, Kjell Rilbe wrote:

>Helen Borrie wrote:
>> >I would have expected this to work:
>> >DELETE
>> >FROM MYTABLE T
>> >WHERE
>> >T.DATE=
>> >(SELECT MIN(Q.DATE) FROM
>> >MYTABLE Q WHERE T.NUMPAT = Q.NUMPAT )
>> >Why doesn't it work? What error do you get?
>>
>> I would expect it to work, but too well! There's nothing here to prevent
>> this statement from continuing to cycle through until it has deleted all
>> of the records in the table (except those where NUMPAT or "DATE" were
>> NULL). At the very least I would want to test this theory on a copy of
>> the database!
>
>Oh, is this the gotcha where a FB query sees its own effects, i.e. when
>one record is deleted, and the subquery is reevaluated for the next
>record, the previously deleted record is not seen by the subquery, and
>so might decide that the next record, although newer, should also be
>deleted?

Not quite....the transaction sees that it has deleted records so, to that extent, it is not going to try and revisit records that are already deleted. However, as soon as you delete the record with the MIN date value, the next oldest record becomes a candidate for the test. I think there's nothing in the logic of the outer cursor when comparing it with the the current output of the correlated inner query that can tell it "you've already visited this guy so ignore him." Hence, my warning is to TEST on a copy of the database, not on production data.

>I'm qurious. What does the appropriate SQL standard have to say about
>this behavior, and if it says that this is wrong,

AFAIR, it does say it's wrong. It is an inherent implementation issue with the IB/Fb architecture that, if it were easy to fix, would have been fixed by now.

>are there any plans to change FB to adhere to the standard?

Well, it's certainly not viewed by anyone I know as a "feature" that we want to keep. ;-) That is, you don't have to convince anyone that it's wrong!

"Adhere to the standard" isn't quite the right way to think about things. The standards aren't concerned with the implementation details *at all*. They are concerned about the semantics of the language and the expected outcomes. Nothing is presumed about the implementation whatsoever.

But, possibly more to the point, there is no DBMS that "adheres to the standard". In most cases, the standard wasn't there to "adhere to" when the database engine was implemented, anyway. The standards tend to change and augment over time, and to develop new dimensions. So aspiring to comply with standard X is much more a case of doing what is possible to make the feature as compliant as possible with a certain version of standard X.

What can occur during Firebird's development is that a "gotcha" becomes fixable when something else is reimplemented, or when a characteristic is added to the engine for some purpose, that resolves other things coincidentally. I don't mean "by accident" but rather, by an architectural improvement for one purpose providing an alternative or improved way to achieve better results for other purposes.

[[ It can work the other way as well: Firebird 2 is now, by design, standards-compliant wrt NOT IN(), so that now it returns correct results, as the standard requires, but very, very slowly, because the correct logic can't use indexes. We actually have users screaming to reverse this change: they prefer to get wrong results fast than correct results slowly. ]]

Given the desirability of removing the "gotcha" that affects DML operations using this style of selection, we can hope/guess that, at some point, everything affected by it will have been corrected as the result of some developments along the way. It is quite possible that *this* aspect of it may be gone already in some version and I'm scaring you unnecessarily. :-)

Compliance (or not) with any theoretical "standard" is never going to remove the need to TEST the effects of every statement properly in the lab before it is *ever* launched onto production data.

./heLen