Subject | for select c from t where :p = initial-value-of-p into p .... weird behavior |
---|---|
Author | woodsmailbox |
Post date | 2008-11-04T18:47:52Z |
OK, sorry for posting directly to the tracker (CORE-2166).
I only did because I consider this a bug. I was explained that
this is correct behavior, yet I still get inconsistent results,
even in the context of what was explained to me.
----------------------------------------------------------
test case:
create table t (c integer primary key);
insert into t values (1);
insert into t values (2);
insert into t values (3);
execute block returns (r integer) as
declare variable p integer = 1234;
begin
for select c, c from t where :p = 1234 into p, r do suspend;
end
This gets only the first row (i.e. when p was 1234).
This is supposed to be correct behavior.
Ok, I have a few questions since I'm a little confused:
1) is the exact behavior documented somewhere?
2) the premise that the query is reevaluated does not hold for the
following test case, i.e. I never get the row where c = 3 with the
following block:
execute block returns (r integer) as
declare variable p integer = 1;
begin
for select c from t where c in (1, 2, :p) into r do
begin
p = 3;
suspend;
end
end
... as far as can infer from those few tests, it seems that the where
clause do get reevaluated, but data is not re-queried to fulfill the
new condition.
So my question is: what is the exact rule that does such behavior follow?
I only did because I consider this a bug. I was explained that
this is correct behavior, yet I still get inconsistent results,
even in the context of what was explained to me.
----------------------------------------------------------
test case:
create table t (c integer primary key);
insert into t values (1);
insert into t values (2);
insert into t values (3);
execute block returns (r integer) as
declare variable p integer = 1234;
begin
for select c, c from t where :p = 1234 into p, r do suspend;
end
This gets only the first row (i.e. when p was 1234).
This is supposed to be correct behavior.
Ok, I have a few questions since I'm a little confused:
1) is the exact behavior documented somewhere?
2) the premise that the query is reevaluated does not hold for the
following test case, i.e. I never get the row where c = 3 with the
following block:
execute block returns (r integer) as
declare variable p integer = 1;
begin
for select c from t where c in (1, 2, :p) into r do
begin
p = 3;
suspend;
end
end
... as far as can infer from those few tests, it seems that the where
clause do get reevaluated, but data is not re-queried to fulfill the
new condition.
So my question is: what is the exact rule that does such behavior follow?