| This problem also exists in V6.1, and is fixed under bug #428374.
The prolem can be reproduced by the following script:
CREATE TABLE T1 (F1 CHAR(5),
F2 INTEGER,
QTY INTEGER,
PRICE INTEGER);
CREATE INDEX T1_NDX ON T1 (F1, F2);
insert into t1 value ('K1000', 1, 100, 20);
insert into t1 value ('K1001', 1, 100, 20);
insert into t1 value ('K1002', 1, 100, 20);
insert into t1 value ('K1003', 1, 100, 20);
update rdb$relations
set rdb$cardinality = 5000
where rdb$relation_name = 'T1';
COMMIT;
CREATE TABLE T2 (F1 CHAR(5),
F2 INTEGER,
QTY INTEGER,
PRICE INTEGER);
insert into t2 value ('K1002', 1, 100, 20);
insert into t2 value ('K1001', 1, 100, 20);
insert into t2 value ('K1003', 1, 100, 20);
! This will select NULL for sum (t2.qty * t2.price), because
! the 2nd leg is not sorted, and the match key returns false
select (qty * price),
(select sum (t2.qty * t2.price)
from t2 t2
where t2.f1 = t1.f1 and t2.f2 = t1.f2)
from t1 t1
where t1.f1 = 'K1001' AND t1.f2 = 1;
!Match
! Outer loop
! Conjunct Get Retrieval by index of relation T1
! Index name T1_NDX [2:2]
! Inner loop
! Aggregate Get Retrieval sequentially of relation T2
! This will work
select (qty * price),
(select sum (t2.qty * t2.price)
from t2 t2
where t2.f1 = t1.f1 and t2.f2 = t1.f2)
from t1 t1
where t1.f1 = 'K1003' AND t1.f2 = 1;
! this will work also
select (qty * price),
(select sum (t2.qty * t2.price)
from t2 t2
where t2.f1 = t1.f1 and t2.f2 = t1.f2)
from t1 t1
where t1.f1 = 'K1001';
|