Submitted by: Claudio Valderrama C. (robocop)

Assigned to: @ArnoBrinkman

SFID: 219525#⁠
Submitted By: robocop

I've copied the complete message and I will add my comment at the bottom:

=========
"Marco Rocci" wrote in message mailto:news:stetuscrcqkdfc39iuqv7jvss0f2dm1s90@4ax.com...
>
> ---begin metadata---
>
> CREATE DATABASE "JoinTest.gdb" USER "SYSDBA" PASSWORD "masterkey"
> PAGE_SIZE 2048;
>
> CREATE DOMAIN d_currency AS FLOAT;
> CREATE DOMAIN d_date AS TIMESTAMP;
> CREATE DOMAIN d_des AS VARCHAR(30);
> CREATE DOMAIN d_percent AS FLOAT;
> CREATE DOMAIN d_arecod AS SMALLINT;
> CREATE DOMAIN d_itmcod AS SMALLINT;
> CREATE DOMAIN d_colcod AS SMALLINT;
> CREATE DOMAIN d_detcod AS SMALLINT;
> CREATE DOMAIN d_dsccod AS SMALLINT;
> CREATE DOMAIN d_invcod AS INTEGER;
>
> /* Lookup tables */
>
> CREATE TABLE tabare (
> arecod d_arecod NOT NULL,
> aredes d_des NOT NULL
> );
>
> CREATE TABLE tabcol (
> colcod d_colcod NOT NULL,
> coldes d_des NOT NULL
> );
>
> CREATE TABLE tabdsc (
> dsccod d_dsccod NOT NULL,
> dscdes d_des NOT NULL,
> dscmlt d_percent
> );
>
> CREATE TABLE tabitm (
> itmcod d_itmcod NOT NULL,
> itmdes d_des NOT NULL
> );
>
> /* Main tables */
>
> CREATE TABLE tabdet (
> invcod d_invcod NOT NULL,
> detcod d_detcod NOT NULL,
> itmcod d_itmcod NOT NULL,
> colcod d_colcod ,
> detuni SMALLINT NOT NULL,
> detppu d_currency NOT NULL,
> dsccod d_dsccod NOT NULL
> );
>
> CREATE TABLE tabinv (
> invcod d_invcod NOT NULL,
> invnum VARCHAR(10) NOT NULL,
> invdt d_date NOT NULL,
> arecod d_arecod
> );
>
> /* Primary keys */
>
> ALTER TABLE tabare ADD CONSTRAINT tabare_PK PRIMARY KEY (arecod);
> ALTER TABLE tabcol ADD CONSTRAINT tabcol_PK PRIMARY KEY (colcod);
> ALTER TABLE tabdsc ADD CONSTRAINT tabdsc_PK PRIMARY KEY (dsccod);
> ALTER TABLE tabitm ADD CONSTRAINT tabitm_PK PRIMARY KEY (itmcod);
> ALTER TABLE tabdet ADD CONSTRAINT tabdet_PK PRIMARY KEY
> (invcod,detcod);
> ALTER TABLE tabinv ADD CONSTRAINT tabinv_PK PRIMARY KEY (invcod);
>
> /* Unique keys */
>
> ALTER TABLE tabare ADD CONSTRAINT aredes_UK UNIQUE (aredes);
> ALTER TABLE tabcol ADD CONSTRAINT coldes_UK UNIQUE (coldes);
> ALTER TABLE tabdsc ADD CONSTRAINT dscdes_UK UNIQUE (dscdes);
> ALTER TABLE tabitm ADD CONSTRAINT itmdes_UK UNIQUE (itmdes);
> ALTER TABLE tabdet ADD CONSTRAINT invcoditmcod_UK UNIQUE
> (invcod,itmcod);
>
> /* Foreign keys */
>
> ALTER TABLE tabdet ADD CONSTRAINT tabdet_tabinv_FK FOREIGN KEY
> (invcod) REFERENCES tabinv (invcod);
> ALTER TABLE tabdet ADD CONSTRAINT tabdet_tabitm_FK FOREIGN KEY
> (itmcod) REFERENCES tabitm (itmcod);
> ALTER TABLE tabdet ADD CONSTRAINT tabdet_tabcol_FK FOREIGN KEY
> (colcod) REFERENCES tabcol (colcod);
> ALTER TABLE tabdet ADD CONSTRAINT tabdet_tabdsc_FK FOREIGN KEY
> (dsccod) REFERENCES tabdsc (dsccod);
> ALTER TABLE tabinv ADD CONSTRAINT tabinv_tabare_FK FOREIGN KEY
> (arecod) REFERENCES tabare (arecod);
>
> /* Views */
>
> CREATE VIEW view_det (
> invcod ,
> detcod ,
> itmcod ,
> colcod ,
> detuni ,
> detppu ,
> dsccod ,
> itmdes ,
> dscdes ,
> coldes )
> AS SELECT
> a.invcod ,
> a.detcod ,
> a.itmcod ,
> a.colcod ,
> a.detuni ,
> a.detppu ,
> a.dsccod ,
> b.itmdes ,
> c.dscdes ,
> d.coldes
> FROM tabdet a
> LEFT OUTER JOIN tabcol d ON a.colcod=d.colcod
> INNER JOIN tabitm b ON a.itmcod=b.itmcod
> INNER JOIN tabdsc c ON a.dsccod=c.dsccod
> ;
>
> CREATE VIEW view_inv (
> invcod ,
> invnum ,
> invdt ,
> arecod ,
> aredes )
> AS SELECT
> a.invcod ,
> a.invnum ,
> a.invdt ,
> a.arecod ,
> b.aredes
> FROM tabinv a
> LEFT OUTER JOIN tabare b ON a.arecod=b.arecod
> ;
>
> COMMIT;
>
> /* Example Data */
>
> INSERT INTO tabare (arecod,aredes) VALUES (1,'Area A');
> INSERT INTO tabare (arecod,aredes) VALUES (2,'Area B');
> INSERT INTO tabare (arecod,aredes) VALUES (3,'Area C');
>
> INSERT INTO tabcol (colcod,coldes) VALUES (1,'Red' );
> INSERT INTO tabcol (colcod,coldes) VALUES (2,'Green' );
> INSERT INTO tabcol (colcod,coldes) VALUES (3,'Blue' );
> INSERT INTO tabcol (colcod,coldes) VALUES (4,'Yellow');
>
> INSERT INTO tabdsc (dsccod,dscdes,dscmlt) VALUES (1,'Discount
> A',90.00);
> INSERT INTO tabdsc (dsccod,dscdes,dscmlt) VALUES (2,'Discount
> B',80.00);
> INSERT INTO tabdsc (dsccod,dscdes,dscmlt) VALUES (3,'Discount
> C',75.00);
>
> INSERT INTO tabitm (itmcod,itmdes) VALUES (1,'Pens' );
> INSERT INTO tabitm (itmcod,itmdes) VALUES (2,'Pencils');
> INSERT INTO tabitm (itmcod,itmdes) VALUES (3,'Markers');
> INSERT INTO tabitm (itmcod,itmdes) VALUES (4,'Erasers');
> INSERT INTO tabitm (itmcod,itmdes) VALUES (5,'Cards' );
>
> INSERT INTO tabinv (invcod,invnum,invdt,arecod) VALUES (1,'1/A'
> ,'10-SEP-2000',2);
> INSERT INTO tabinv (invcod,invnum,invdt,arecod) VALUES
> (2,'20/A','12-SEP-2000',1);
> INSERT INTO tabinv (invcod,invnum,invdt,arecod) VALUES (3,'8/G'
> ,'14-SEP-2000',2);
> INSERT INTO tabinv (invcod,invnum,invdt,arecod) VALUES
> (4,'22/Z','14-SEP-2000',1);
> INSERT INTO tabinv (invcod,invnum,invdt,arecod) VALUES
> (5,'15/H','16-SEP-2000',3);
>
> INSERT INTO tabdet (invcod,detcod,itmcod,colcod,detuni,detppu,dsccod)
> VALUES (1,1,2,1, 12, 4.95,1);
> INSERT INTO tabdet (invcod,detcod,itmcod,colcod,detuni,detppu,dsccod)
> VALUES (1,2,3,3, 5, 2.90,1);
> INSERT INTO tabdet (invcod,detcod,itmcod, detuni,detppu,dsccod)
> VALUES (1,3,4, 2,10.10,1);
> INSERT INTO tabdet (invcod,detcod,itmcod,colcod,detuni,detppu,dsccod)
> VALUES (1,4,5,2, 2, 7.50,1);
> INSERT INTO tabdet (invcod,detcod,itmcod,colcod,detuni,detppu,dsccod)
> VALUES (2,1,1,1,100, 5.00,1);
> INSERT INTO tabdet (invcod,detcod,itmcod, detuni,detppu,dsccod)
> VALUES (2,2,4, 50, 2.90,1);
> INSERT INTO tabdet (invcod,detcod,itmcod, detuni,detppu,dsccod)
> VALUES (3,1,1, 6, 4.80,1);
> INSERT INTO tabdet (invcod,detcod,itmcod,colcod,detuni,detppu,dsccod)
> VALUES (3,2,2,3, 11, 4.95,2);
> INSERT INTO tabdet (invcod,detcod,itmcod,colcod,detuni,detppu,dsccod)
> VALUES (3,3,3,2, 10,10.00,1);
> INSERT INTO tabdet (invcod,detcod,itmcod,colcod,detuni,detppu,dsccod)
> VALUES (3,4,4,1, 3, 7.40,1);
> INSERT INTO tabdet (invcod,detcod,itmcod, detuni,detppu,dsccod)
> VALUES (3,5,5, 5, 3.15,1);
> INSERT INTO tabdet (invcod,detcod,itmcod,colcod,detuni,detppu,dsccod)
> VALUES (4,1,1,3, 1, 4.95,1);
> INSERT INTO tabdet (invcod,detcod,itmcod,colcod,detuni,detppu,dsccod)
> VALUES (4,2,2,3, 1, 2.95,1);
> INSERT INTO tabdet (invcod,detcod,itmcod,colcod,detuni,detppu,dsccod)
> VALUES (4,3,3,3, 1, 9.95,2);
> INSERT INTO tabdet (invcod,detcod,itmcod, detuni,detppu,dsccod)
> VALUES (4,4,4, 1, 7.55,1);
> INSERT INTO tabdet (invcod,detcod,itmcod,colcod,detuni,detppu,dsccod)
> VALUES (4,5,5,3, 1, 3.20,1);
> INSERT INTO tabdet (invcod,detcod,itmcod,colcod,detuni,detppu,dsccod)
> VALUES (5,1,2,2, 10, 4.85,1);
> INSERT INTO tabdet (invcod,detcod,itmcod, detuni,detppu,dsccod)
> VALUES (5,2,3, 10, 2.95,3);
> INSERT INTO tabdet (invcod,detcod,itmcod,colcod,detuni,detppu,dsccod)
> VALUES (5,3,5,1, 10, 9.90,1);
>
> COMMIT;
>
> ---end metadata---
>
> SELECT * FROM view_det INNER JOIN view_inv ON
> view_det.invcod=view_inv.invcod
>
> DOESN'T WORK -> "No current record for fetch operation."
>
> ... and neither will:
>
> SELECT * FROM view_det, view_inv WHERE view_det.invcod=view_inv.invcod
>
> Any opinions or workarounds?
>
> TIA and regards,
>
> --
> Marco Rocci

If you watch the query plan generated by the failing statement, you'll notice it's really big... no surprise since it involves a join of views that in turn are based on other joins.
The workaround is to convert
SELECT * FROM view_det JOIN view_inv ON
view_det.invcod=view_inv.invcod
to become
SELECT * FROM view_det JOIN view_inv ON
view_det.invcod-view_inv.invcod=0
or
SELECT * FROM view_det JOIN view_inv ON
view_det.invcod+0=view_inv.invcod
or other variations that fool the optimizer by forcing it to a full scan to do the join.
But this doesn't deny the fact that the original statement should work, so this is a bug and the same kind of bug that IB5.X suffered from.

C.