General information
Category:
Databases,Tables, Views, Indexing and SQL syntax
Title:
Is this a SHOWSTOPPER bug or what?????
Correlated update BUG!!!!
It happens when you alias the table ("y") in the correlated update in FoxPro. If you don't alias the table then FoxPro works correctly.
I wouldn't mind if FoxPro errored out, but instead it incorrectly updates the table.
***************************************************************************FoxPro code
use in select('junk')
use in select('junk2')
create cursor junk (id integer, d1 varchar(2))
create cursor junk2 (id integer, d2 varchar(2))
insert into junk (id) values (1)
insert into junk (id) values (2)
insert into junk (id) values (3)
insert into junk (id) values (4)
insert into junk (id) values (5)
insert into junk2 (id,d2) values (1,'aa')
insert into junk2 (id,d2) values (2,'bb')
insert into junk2 (id,d2) values (3,'cc')
update junk set d1 = d2 from junk y inner join junk2 on y.id = junk2.id
select * from junk
Here's the equivalent code for SQL 2000:
***************************************************************************T-SQL
--drop table #junk
--drop table #junk2
create table #junk (id integer, d1 varchar(2))
create table #junk2 (id integer, d2 varchar(2))
insert into #junk (id) values (1)
insert into #junk (id) values (2)
insert into #junk (id) values (3)
insert into #junk (id) values (4)
insert into #junk (id) values (5)
insert into #junk2 (id,d2) values (1,'aa')
insert into #junk2 (id,d2) values (2,'bb')
insert into #junk2 (id,d2) values (3,'cc')
select * from #junk
select * from #junk2
update #junk set d1 = d2 from #junk y inner join #junk2 on y.id = #junk2.id
select * from #junk
***************************************************************************
FoxPro (incorrect) results are:
ID D1
1 aa
2 aa
3 aa
4 aa
5 aa
SQL 2000 (correct) results are:
ID D1
1 aa
2 bb
3 cc
4 NULL
5 NULL
***************************************************************************
Aha!!!!!!!! But in FoxPro this does work:
use in select('junk')
use in select('junk2')
create cursor junk (id integer, d1 varchar(2))
create cursor junk2 (id integer, d2 varchar(2))
insert into junk (id) values (1)
insert into junk (id) values (2)
insert into junk (id) values (3)
insert into junk (id) values (4)
insert into junk (id) values (5)
insert into junk2 (id,d2) values (1,'aa')
insert into junk2 (id,d2) values (2,'bb')
insert into junk2 (id,d2) values (3,'cc')
update y set y.d1 = d2 from junk y inner join junk2 on y.id = junk2.id
select * from junk
********************************************************
Who's going to fix this incredibly dangerous bug?
********************************************************
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only