Issue Details (XML | Word | Printable)

Key: CORE-2822
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Major Major
Assignee: Dmitry Yemanov
Reporter: Karol Bieniaszewski
Votes: 0
Watchers: 0
Operations

If you were logged in you would be able to see more operations.
Firebird Core

Error "no current row for fetch operation" when subquery includes a non-trivial derived table

Created: 22/Jan/10 12:44 PM   Updated: 28/Jul/11 10:32 AM
Component/s: Engine
Affects Version/s: 2.0.0, 2.0.1, 2.0.2, 2.0.3, 2.1.0, 2.0.4, 2.1.1, 2.0.5, 2.1.2, 2.1.3, 3.0 Initial, 2.5 RC1
Fix Version/s: 2.5 RC2, 2.0.6, 2.1.4, 3.0 Alpha 1

Time Tracking:
Not Specified

Issue Links:
Relate

Planning Status: Unspecified


 Description  « Hide
CREATE TABLE A
(
  ID Integer NOT NULL,
  O Varchar(20),
  CONSTRAINT PK_A__ID PRIMARY KEY (ID)
);
CREATE TABLE G
(
  ID Integer NOT NULL,
  C Timestamp,
  K Varchar(20),
  S Varchar(5),
  CONSTRAINT PK_G__ID PRIMARY KEY (ID),
  CONSTRAINT UK_G__K UNIQUE (K)
);
CREATE TABLE OT
(
  ID Integer NOT NULL,
  ID_A Integer,
  S Varchar(5),
  FLAG Integer,
  K Varchar(20),
  CONSTRAINT PK_OT__ID PRIMARY KEY (ID),
  CONSTRAINT UK_OT__K__S UNIQUE (K,S)
);
CREATE TABLE OTE
(
  ID Integer NOT NULL,
  ID_OT Integer NOT NULL,
  ID_A Integer NOT NULL,
  CZAS Timestamp,
  CONSTRAINT PK_OTE__ID PRIMARY KEY (ID),
  CONSTRAINT UK_OTE__ID_OT UNIQUE (ID_OT)
);



CREATE INDEX IXA_A__O ON A (O);
ALTER TABLE OT ADD CONSTRAINT FK_OT__ID_A
  FOREIGN KEY (ID_A) REFERENCES A (ID) ON UPDATE CASCADE ON DELETE NO ACTION;
CREATE INDEX IXA__OTE__ID_A ON OTE (ID_A);
CREATE DESCENDING INDEX IXD__OTE__CZAS ON OTE (CZAS);

/* ################################################################## */


INSERT INTO A (ID, O) VALUES ('0', '5555');
INSERT INTO A (ID, O) VALUES ('1', '6666');

INSERT INTO G (ID, C, K, S) VALUES ('0', '22.01.1989, 13:49:18.576', '6666', 'A');
INSERT INTO G (ID, C, K, S) VALUES ('1', '22.01.2001, 13:49:18.576', '5555', 'B');

INSERT INTO OT (ID, ID_A, S, FLAG, K) VALUES ('0', '1', 'B', NULL, '01');
INSERT INTO OT (ID, ID_A, S, FLAG, K) VALUES ('1', '0', 'A', '1993531', '02');

INSERT INTO OTE (ID, ID_OT, ID_A, CZAS) VALUES ('0', '0', '0', '13.08.1980, 09:01:13.000');
INSERT INTO OTE (ID, ID_OT, ID_A, CZAS) VALUES ('1', '1', '1', '05.03.1987, 06:32:57.000');

################################################


SELECT OT.*
FROM
OT OT
INNER JOIN A A ON OT.ID_A=A.ID
INNER JOIN OTE OTE ON OT.ID=OTE.ID_OT
WHERE
( (OT.S<>'KO') AND (OT.FLAG IS NULL)
AND
COALESCE((SELECT MAX(AAA.CZAS) FROM (SELECT COALESCE((SELECT FIRST 1 G.C FROM G G INNER JOIN OT OST ON G.K=OST.K AND G.S=OST.S WHERE G.K=OT_SUB.K ORDER BY G.C DESC), OTE.CZAS) AS CZAS_HG FROM OT OT_SUB INNER JOIN OTE OTE ON OT_SUB.ID=OTE.ID_OT WHERE OT_SUB.ID=OT.ID UNION SELECT OTE.CZAS AS HIST_CZAS FROM OT OT_SUB INNER JOIN OTE OTE ON OT_SUB.ID=OTE.ID_OT WHERE OT_SUB.ID=OT.ID UNION SELECT (SELECT FIRST 1 G.C FROM G G INNER JOIN OT OST ON G.K=OST.K AND G.S=OST.S WHERE G.K=OT_SUB.K ORDER BY G.C DESC) AS G_CZAS FROM OT OT_SUB INNER JOIN OTE OTE ON OT_SUB.ID=OTE.ID_OT WHERE OT_SUB.ID=OT.ID ) AS AAA(CZAS)), CAST('1900-01-01 12:00' AS TIMESTAMP)) >=CAST('2010-01-21 12:30:37' AS TIMESTAMP) ) ORDER BY A.O

#########################################

plan is ..
PLAN SORT (JOIN (AAA G INDEX (UK_G__K), AAA OST INDEX (UK_OT__K__S)))
PLAN SORT (JOIN (AAA G INDEX (UK_G__K), AAA OST INDEX (UK_OT__K__S)))
PLAN SORT (JOIN (AAA G INDEX (UK_G__K), AAA OST INDEX (UK_OT__K__S)))
PLAN JOIN (AAA OT_SUB INDEX (PK_OT__ID), AAA OTE INDEX (UK_OTE__ID_OT))
PLAN JOIN (AAA OT_SUB INDEX (PK_OT__ID), AAA OTE INDEX (UK_OTE__ID_OT))
PLAN JOIN (AAA OT_SUB INDEX (PK_OT__ID), AAA OTE INDEX (UK_OTE__ID_OT))
PLAN SORT (JOIN (AAA G INDEX (UK_G__K), AAA OST INDEX (UK_OT__K__S)))
PLAN SORT (JOIN (AAA G INDEX (UK_G__K), AAA OST INDEX (UK_OT__K__S)))
PLAN SORT (JOIN (AAA G INDEX (UK_G__K), AAA OST INDEX (UK_OT__K__S)))
PLAN JOIN (AAA OT_SUB INDEX (PK_OT__ID), AAA OTE INDEX (UK_OTE__ID_OT))
PLAN JOIN (AAA OT_SUB INDEX (PK_OT__ID), AAA OTE INDEX (UK_OTE__ID_OT))
PLAN JOIN (AAA OT_SUB INDEX (PK_OT__ID), AAA OTE INDEX (UK_OTE__ID_OT))
PLAN JOIN (A ORDER IXA_A__O, OT INDEX (FK_OT__ID_A), OTE INDEX (UK_OTE__ID_OT))

#############################################

i got error
SQL message: -508
the cursor identified in the update or delete statement is not positionet on a row.
engine code: 335544348
no curent row for fetch operation

#############################################

but when i change to use index on A (A.O='6666')

SELECT OT.*
FROM
OT OT
INNER JOIN A A ON OT.ID_A=A.ID
INNER JOIN OTE OTE ON OT.ID=OTE.ID_OT
WHERE
A.O='6666' AND
( (OT.S<>'KO') AND (OT.FLAG IS NULL)
AND
COALESCE((SELECT MAX(AAA.CZAS) FROM (SELECT COALESCE((SELECT FIRST 1 G.C FROM G G INNER JOIN OT OST ON G.K=OST.K AND G.S=OST.S WHERE G.K=OT_SUB.K ORDER BY G.C DESC), OTE.CZAS) AS CZAS_HG FROM OT OT_SUB INNER JOIN OTE OTE ON OT_SUB.ID=OTE.ID_OT WHERE OT_SUB.ID=OT.ID UNION SELECT OTE.CZAS AS HIST_CZAS FROM OT OT_SUB INNER JOIN OTE OTE ON OT_SUB.ID=OTE.ID_OT WHERE OT_SUB.ID=OT.ID UNION SELECT (SELECT FIRST 1 G.C FROM G G INNER JOIN OT OST ON G.K=OST.K AND G.S=OST.S WHERE G.K=OT_SUB.K ORDER BY G.C DESC) AS G_CZAS FROM OT OT_SUB INNER JOIN OTE OTE ON OT_SUB.ID=OTE.ID_OT WHERE OT_SUB.ID=OT.ID ) AS AAA(CZAS)), CAST('1900-01-01 12:00' AS TIMESTAMP)) >=CAST('1977-01-21 12:30:37' AS TIMESTAMP) ) ORDER BY A.O

PLAN JOIN (A ORDER IXA_A__O INDEX (IXA_A__O), OT INDEX (FK_OT__ID_A), OTE INDEX (UK_OTE__ID_OT))

then this work


 All   Comments   Work Log   Change History   Version Control   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Karol Bieniaszewski added a comment - 22/Jan/10 12:57 PM
i add better sample data

Dmitry Yemanov added a comment - 22/Jan/10 02:00 PM
A much simpler test query against the same data:

SELECT OT.*
FROM
  OT OT
  INNER JOIN A A ON OT.ID_A=A.ID
WHERE
    ( SELECT current_timestamp
      FROM (
        SELECT 1 from rdb$database
        UNION
        SELECT 1
        FROM OT OT_SUB
        WHERE OT_SUB.ID=OT.ID ) AS AAA(CZAS)) = current_timestamp

A similar test case against the system tables:

select *
from rdb$relations r natural join rdb$relation_fields rf
where 1 = (
  select 1
  from (
    select 1 from rdb$database
    union
    select 1
    from rdb$fields f
    where f.rdb$field_name = rf.rdb$field_source
  ) as f (id) )

Pavel Cisar added a comment - 28/Jul/11 10:32 AM
QA test added.