есть две таблицы:

create table items (
itemid integer not null,
name varchar(128) not null)

itemid name
---------------
1 item1
2 item2
3 item3
4 item4
5 item5

и

create table itemvalues (
itemid integer not null,
ondate date not null,
ivalue double precision)

itemid ondate ivalue
----------------------
1 1.1.2000 1.000
1 1.2.2000 2.000
2 1.1.2000 1.500
4 1.3.2000 4.000

Следующий прок должен выбрать значения итемов на дату:

CREATE PROCEDURE SP_GET
RETURNS (
ITEMID INTEGER,
NAME VARCHAR(128),
IVALUE DOUBLE PRECISION,
IONDATE DATE)
AS
begin
for select itemid, name from items into :ITEMID, :NAME
do begin
IVALUE = null;
IONDATE = null;
select iv.ivalue, iv.ondate from itemvalues iv
where iv.itemid=:ITEMID and iv.ondate=
(select max(ondate) from itemvalues
where itemid=:ITEMID and ondate<='1.1.2005')
into :IVALUE, :IONDATE;
suspend;
end
end

результат должен быть такой:

itemid name ivalue iondate
------------------------------
1 item1 2.000 1.2.2000
2 item2 1.500 1.1.2000
3 item3 null null
4 item4 4.000 1.3.2000
5 item5 null null

а возвращается:

itemid name ivalue iondate
------------------------------
1 item1 2.000 1.2.2000
2 item2 null null
3 item3 null null
4 item4 null null
5 item5 null null

причём, что интересно, если в подзапросе пишу:
...
(select max(ondate) from itemvalues
where itemid=iv.itemid and ondate<='1.1.2005')
...
то всё работает, как надо. Где здесь могут быть грабли?

запускаю ibexpert-ом

та же фигня при запуске через ibx

и ещё - проверялось на fb1.5 и fb1.03. результаты одинаковые

2Gold:

CREATE TABLE ITEMS (
ITEMID INTEGER NOT NULL,
NAME VARCHAR(128) NOT NULL
);


CREATE TABLE ITEMVALUES (
ITEMID INTEGER NOT NULL,
ONDATE DATE NOT NULL,
IVALUE DOUBLE PRECISION
);

INSERT INTO ITEMS (ITEMID, NAME) VALUES (1, 'Item1');
INSERT INTO ITEMS (ITEMID, NAME) VALUES (2, 'Item2');
INSERT INTO ITEMS (ITEMID, NAME) VALUES (3, 'Item3');
INSERT INTO ITEMS (ITEMID, NAME) VALUES (4, 'Item4');
INSERT INTO ITEMS (ITEMID, NAME) VALUES (5, 'Item5');

INSERT INTO ITEMVALUES (ITEMID, ONDATE, IVALUE) VALUES (1, '2000-01-01 00:00:00', 1);
INSERT INTO ITEMVALUES (ITEMID, ONDATE, IVALUE) VALUES (1, '2000-02-01 00:00:00', 2);
INSERT INTO ITEMVALUES (ITEMID, ONDATE, IVALUE) VALUES (2, '2000-01-01 00:00:00', 1.5);
INSERT INTO ITEMVALUES (ITEMID, ONDATE, IVALUE) VALUES (4, '2000-03-01 00:00:00', 4);

ALTER TABLE ITEMS ADD CONSTRAINT PK_ITEMS PRIMARY KEY (ITEMID);
ALTER TABLE ITEMVALUES ADD CONSTRAINT FK_ITEMVALUES FOREIGN KEY (ITEMID) REFERENCES ITEMS (ITEMID);

SET TERM ^ ;

CREATE PROCEDURE SP_GET
RETURNS (
ITEMID INTEGER,
NAME VARCHAR(128),
IVALUE DOUBLE PRECISION,
IONDATE DATE)
AS
begin
for select itemid, name from items into :ITEMID, :NAME
do begin
IVALUE = null;
IONDATE = null;
select iv.ivalue, iv.ondate from itemvalues iv
where iv.itemid=:ITEMID and iv.ondate=
(select max(ondate) from itemvalues
where itemid=:ITEMID and ondate<='1.1.2005')
into :IVALUE, :IONDATE;
suspend;
end
end
^

SET TERM ; ^

2Johnmen:
авторЭто не грабли.
То есть, ты хочешь, сказать, что так быть и должно?
авторТебе просто надо понять
1. что такое iv и зачем оно нужно
2. таблица во внешнем запросе и во вложенном с т.з. самого запроса - разные таблицы
А что конкретно надо понять? Чё-то я тебя недопонял.

2Мимопроходящий: спасибо, хотя вот Johnmen говорит, что это не грабли. и кому в этом случае верить?
обойти-то я обошёл, правда прок стал работать медленнее

1.5.0.4306

ok - скачаю.
теперь новый прикол:

на таблице itemvalues индекс IV_IDX(ITEMID, ONDATE), в самой таблице ~3500000 записей.

для коррелированного и некоррелированного подзапросов выходит разное время выполнения:

select iv.ivalue, iv.ondate from itemvalues iv
where iv.itemid=1 and iv.ondate=
(select max(ondate) from itemvalues
where itemid=iv.itemid and ondate<='1.1.2005')


Plan
PLAN (ITEMVALUES INDEX (IV_IDX))
PLAN (IV INDEX (IV_IDX))

Adapted Plan
PLAN (ITEMVALUES INDEX (IV_IDX)) PLAN (IV INDEX (IV_IDX))

------ Performance info ------
Prepare time = 0ms
Execute time = 735ms
Avg fetch time = 735,00 ms
Current memory = 1 726 128
Max memory = 2 504 592
Memory buffers = 2 048
Reads from disk to cache = 0
Writes from cache to disk = 6
Fetches from cache = 254 657


select iv.ivalue, iv.ondate from itemvalues iv
where iv.itemid=1 and iv.ondate=
(select max(ondate) from itemvalues
where itemid=1 and ondate<='1.1.2005')

Plan
PLAN (ITEMVALUES INDEX (IV_IDX))
PLAN (IV INDEX (IV_IDX))

Adapted Plan
PLAN (ITEMVALUES INDEX (IV_IDX)) PLAN (IV INDEX (IV_IDX))

------ Performance info ------
Prepare time = 0ms
Execute time = 16ms
Avg fetch time = 16,00 ms
Current memory = 1 652 648
Max memory = 2 504 592
Memory buffers = 2 048
Reads from disk to cache = 0
Writes from cache to disk = 6
Fetches from cache = 838

В первом случае он чуть ли не весь кэш перелопатил - можно подумать, что индекс не использовался, хотя пишет, что таки использовался.
Как такое можно объяснить?

авторОчень просто. В первом случае поздапрос выполняется для каждой записи внешнего. Во втором - один раз.
:)
дык и в первом, и во втором случае одна запись возвращается.

я так думаю, что сервер идёт по индексу и для каждой записи c itemid=1 (а их порядка 300) выполняет один и тот же подзапрос. Получается, что оптимизатор не выполнил свою основную задачу? или как?

2mv:
ты что, как можно? зайцы - это для меня святое :)

2Johnmen:
умеешь ты, однако, загадками говорить... :)

ага - значит таки оптимизатор криво работает. тогда на этом вопрос закрыт