Thanks for the replies, and apologies for my late posting :o)

This is the behaviour I would expect from such queries, but it seems
implementations could be at times a far way from the standards.

I heard a lot of hype about IB's infamous stored procedure bug. Could
anybody shed more light on this please. Alternatively, a URL that has info
would be fine.

TIA

----- Original Message -----
Date: Wed, 04 Jul 2001 13:43:42 -0000
From: dianeb77@...
Subject: Re: Not in query

[I replied to this yesterday, I thought, but haven't seen reply appear
on the list ... The thought of orphaned fragments of SQL rules,
drifting through the ether, is somewhat frightening ... but I digress,
or hallucinate, or whatever.]

"Plausible", perhaps, in that it describes (as far as I recall),
InterBase's behaviour in this situation.

However, that behaviour does not conform to SQL standard.

According to the SQL standard, the effect of an "insert into ...
select from ..." statement should be as if you created a separate
result set containing all the rows that satisfy the "select from ..."
part of the query, and then took each of the rows from the
intermediate result set and inserted it into the target table.

[Pause while I find the SQL92 mumbo-jumbo again ...]

Here's the relevant bit from SQL92, subclause 13.8 <insert statement>,
General Rules, where "B" is the target table for the INSERT:

"3) The <query expression> is effectively evaluated before inserting
any rows into B.

4) Let Q be the result of that <query expression>.
Case:
a) If Q is empty, then no row is inserted and a completion condition
is raised: no data. [So sad, too bad]
b) Otherwise, for each row R of Q:
i) A candidate row of B is effectively created <... blah blah blah,
assign default values, blah blah blah assign values from select, blah
blah blah consider casting rules, blah blah blah ...>
iv) The candidate row is inserted into B."

Hope that helps,
db

Date: Tue, 03 Jul 2001 12:47:29 +0200
From: Svein Erling Tysvær <svein.erling.tysvaer@...>
Subject: Re: Re: Not in query

>The problem is in that the secondary query is performed on each
>iteration, and thus if 2 records have the same field value, the first
record
>will be added, but the 2nd will not because when the secondary query is
>executed again, it will find that value in table2.

Finally I get you. Luckily, I've never been in a situation like you
describe. If I had to, I guess I would have solved it by two stage process.
Add one integer field (NewField) to table2 and use something similar to
Helen's suggestion for step 1:

Step 1:
insert into table2 select *, 1 from table1 where (not (exists(select
commonkey from table2 where commonkey = table1.commonkey and NewField <>
1)))

Step 2:
update table2
set NewField = 0

This ought to get you two rows rather than one,
Set


Date: Tue, 03 Jul 2001 22:46:00 +1000
From: Helen Borrie <helebor@...>
Subject: Re: Re: Not in query

Huh? seems to sum up my response to this bit of reasoning (unless I'm
confused and you're not talking about this:
insert into table2 select * from table1 where (not (exists(select commonkey
from table2 where commonkey = table1.commonkey)))

This cannot recurse, even if you had duplicates for commonkey in table1
(which you wouldn't because, in your original requirement, you wanted to
duplicate from one table to a matching other by testing the primary key!)

The "secondary query" that you refer to (it's a correlated sub-query) is
very cheap, because it uses the index on the primary key and doesn't return
a dataset, just True or False based on ripping straight through the tree.

>And just for info, a simple SELECT shows indeed both records, and i am not
>committing the transactions as i am running the queries interactively from
>IBConsole. I just commit at the end of the test. In fact, the only
>disadvantage might be a nanosecond slowdown that non committed transaction
>overhead imposes.

Hmmmm....interrrrresting....

Cheers,
Helen

Date: Tue, 03 Jul 2001 15:41:14 +0200
From: Svein Erling Tysvær <svein.erling.tysvaer@...>
Subject: Re: Re: Not in query

Helen,
lobolos reasoning is basically that if he/she uses
insert into table2
select * from table1 where (not (exists(select NotAUniqueKey
from table2 where NotAUniqueKey = table1.NotAUniqueKey)))
it will never insert more than one record for any NotAUniqueKey. The
plausible explanation is that since the subselect is executed once for
every row, the content of table2 will have changed before the second
instance of NotAUniqueKey executes. I.e.

if table1 contains

ID NotAUniqueKey
1 2
2 3
3 3

and table2 is empty, then

insert into table2
select * from table1 where (not (exists(select NotAUniqueKey
from table2 where NotAUniqueKey = table1.NotAUniqueKey)))

will insert into table2

ID NotAUniqueKey
1 2
2 3

or

ID NotAUniqueKey
1 2
3 3

and not both ID 2 and 3. I have not tested this myself, but it is an
interesting situation I've never thought about and would like it to be
explained from someone like you.

Set

Date: Tue, 03 Jul 2001 23:55:01 +1000
From: Helen Borrie <helebor@...>
Subject: Re: Re: Not in query

Oh, I see... :))

The person to answer navel-gazers like this is Ann (closely watched by DB -
she's the one with the tiara...)

My solution to such a problem is far too mundane - I wouldn't do it! As
soon as things start to look even slightly arcane, I resort to a stored
proc. :))

H.




_________________________________________________________
Do You Yahoo!?
Get your free @... address at http://mail.yahoo.com