hmm, nobody has any suggestions regarding this???!!!

FWIW, I have, since original post, actually extracted the pertinent original data, created the simplified 'staged' version of the DB suggested in original post (below) from that data, and run the SQL statements against the staged version. In the staged version, I get exactly the results I had expected.

Does this suggest that the version of firebird I'm using may indeed have a problem with deletes involving sub-selects, under some circumstances?

Any suggestions as to how to further deal with, or otherwise narrow why I'm not getting similar results with original database?

--- In firebird-support@yahoogroups.com, "learntrade" <learntrade@...> wrote:
>
> firebird 2.5.0.26074 on windows 7 home premium x64
>
> Below is a (manually obscured, simplified table) scenario that (hopefully) matches what I'm doing, with actual return count values.
>
> Is my SQL just woefully incorrect, or am I possibly experiencing a problem with fb 2.5, in apparently seeing a great many more records deleted than I expect, based on the various queries.
>
> I expected the last query to yield (123686 - 7617) = 116069, not the reported 107548.
>
> Why didn't it???
>
> data content notes:
> 1)all form_id values in table1 are unique
> 2)all subform_id values in table2 are unique EXCEPT there are many duplicates of '0' (zero), _and_, some of those _are_ matched to table1 entities that are like '%hello%' (i.e. t1.form_id = t2.form_id and t2.subform_id = 0) - I don't currently know counts as I've been waiting for an ISQL query (because I want to see what I entered) I did botch, to finish (and Ctrl-C didn't interrupt it.)
>
>
> I will attempt to provide additional information, if what I have presented is insufficient for analysis.
>
> TIA for any assistance.
>
> ==============================================
> create table table1
> (form_id as Numeric
> ,form_filename_src varchar(256)
> ) ;
>
> create index idx_table1 on table1(form_id) ;
>
> create table table2
> (subform_id as numeric
> ,form_id as numeric
> ) ;
>
> create index idxA_table2 on table2(form_id) ;
> create index idxB_table2 on table2(subform_id) ;
>
> select count(form_filename_src) from table1 where form_filename_src like
> '%hello%' ;
>
> 7617
>
> select count(subform_id) from (select t2.subform_id from table2 t2, table1
> t1 where t1.form_id = t2.form_id and t1.form_filename_src like '%hello%') ;
>
> 7617
>
> select count(*) from table2 ;
>
> 123686
>
> delete from table2 where subform_id in (select t2.subform_id from table2 t2,
> table1 t1 where t2.form_id = t1.form_id and t1.form_filename_src like
> '%hello%') ;
>
> (no delete count or rows affected returned)
>
> select count(*) from table2 ;
>
> 107548
>
> ===================
>