[Firebird-devel] [ firebird-Bugs-922602 ] update with subselect in subselect fails

[SourceForge.net <no...@so...>]

Bugs item #922602, was opened at 2004-03-24 12:34
Message generated for change (Settings changed) made by seanleyne
You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=109028&aid=922602&group_id=9028

Category: Core Engine
Group: Fixed v1.5.x
>Status: Closed
Resolution: Fixed
Priority: 5
Submitted By: John Sparrow (johnsparrowuk)
Assigned to: Nobody/Anonymous (nobody)
Summary: update with subselect in subselect fails

Initial Comment:
/********/
create table demo (id integer not null, something varchar
(30), prev varchar(30));
insert into demo values (1,'a',null);
insert into demo values (2,'b',null);
insert into demo values (3,'c',null);
insert into demo values (4,'d',null);

set term !! ;
create procedure doesntwork as
	declare variable id integer;
	declare variable something varchar(30);
begin
	for select id,something from demo 
into :id, :something do
	begin
		update demo outerdemo set 
outerdemo.prev = (select
			d1.something from demo d1 
where d1.id = (select max(d2.id) from demo
			d2 where d2.id < :id))
		where outerdemo.id = :id;
	end
end!!
set term ; !!
/********/

This doesn't perform any changes. Removing the nested 
subquery into a seperate query is a workaround.

But surely this behaviour is a bug?

----------------------------------------------------------------------

Comment By: Nickolay Samofatov (skidder)
Date: 2004-03-24 13:32

Message:
Logged In: YES 
user_id=495356

AFAIK, this is the problem with invariant tracking which was 
present since the beginning of times, fixed in HEAD codebase 
and is now considered for backporting to 1.5.1 release.

Generally all invariant subqueries having single value do not 
work reliably. (select max(d2.id) from demo
d2 where d2.id < :id)) is the example.

So this issue is really a third of forth duplicate report for the 
same problem

----------------------------------------------------------------------

You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=109028&aid=922602&group_id=9028