Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Error "no current row for fetch operation" when subquery includes a non-trivial derived table [CORE2822] #3209

Closed
firebird-automations opened this issue Jan 22, 2010 · 15 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: @livius2

Relate to CORE3103
Is related to QA252

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=http://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=http://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=http://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=http://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=http://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=http://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=http://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=http://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

Commits: 5f1d41a 3c11e2d a908fbe 72430c3 bc60f97 ccac0ea

@firebird-automations
Copy link
Collaborator Author

Commented by: @livius2

i add better sample data

@firebird-automations
Copy link
Collaborator Author

Modified by: @livius2

description: 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);
GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
ON A TO SYSDBA WITH GRANT OPTION;

GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
ON G TO SYSDBA WITH GRANT OPTION;

GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
ON OT TO SYSDBA WITH GRANT OPTION;

GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
ON OTE TO SYSDBA WITH GRANT OPTION;

INSERT INTO A (ID, O) VALUES ('0', 'kQEysUQMjSKApaeshiZx');
INSERT INTO A (ID, O) VALUES ('1', NULL);

INSERT INTO G (ID, C, K, S) VALUES ('0', NULL, 'aaaaaaaaaaaaaaaaaaaa', 'FFiIx');
INSERT INTO G (ID, C, K, S) VALUES ('1', NULL, 'aaaaaaaaaaaaaaaaaaab', 'PBpTL');

INSERT INTO OT (ID, ID_A, S, FLAG, K) VALUES ('0', '1', NULL, NULL, NULL);
INSERT INTO OT (ID, ID_A, S, FLAG, K) VALUES ('1', NULL, 'aaaab', '1993531', 'aaaaaaaaaaaaaaaaaaab');

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

SELECT OT.*
FROM
OT OT
INNER JOIN A A ON OT.ID_A=http://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=http://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=http://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=http://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 positioned on a row.
engine code: 335544348
no curent row for fetch operation

=>

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);
GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
ON A TO SYSDBA WITH GRANT OPTION;

GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
ON G TO SYSDBA WITH GRANT OPTION;

GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
ON OT TO SYSDBA WITH GRANT OPTION;

GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
ON OTE TO SYSDBA WITH GRANT OPTION;

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=http://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=http://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=http://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=http://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=http://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=http://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=http://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=http://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

@firebird-automations
Copy link
Collaborator Author

Modified by: @livius2

description: 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);
GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
ON A TO SYSDBA WITH GRANT OPTION;

GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
ON G TO SYSDBA WITH GRANT OPTION;

GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
ON OT TO SYSDBA WITH GRANT OPTION;

GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
ON OTE TO SYSDBA WITH GRANT OPTION;

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=http://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=http://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=http://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=http://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=http://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=http://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=http://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=http://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

=>

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=http://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=http://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=http://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=http://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=http://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=http://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=http://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=http://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

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

A much simpler test query against the same data:

SELECT OT.*
FROM
OT OT
INNER JOIN A A ON OT.ID_A=http://A.ID
WHERE
( SELECT current_timestamp
FROM (
SELECT 1 from rdb$database
UNION
SELECT 1
FROM OT OT_SUB
WHERE OT_SUB.ID=http://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) )

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Version: 2.5 RC1 [ 10362 ]

Version: 3.0 Initial [ 10301 ]

Version: 2.1.2 [ 10270 ]

Version: 2.0.5 [ 10222 ]

Version: 2.1.1 [ 10223 ]

Version: 2.0.4 [ 10211 ]

Version: 2.1.0 [ 10041 ]

Version: 2.0.3 [ 10200 ]

Version: 2.0.2 [ 10130 ]

Version: 2.0.1 [ 10090 ]

Version: 2.0.0 [ 10091 ]

Fix Version: 2.0.6 [ 10303 ]

Fix Version: 2.5 RC2 [ 10372 ]

Fix Version: 2.1.4 [ 10361 ]

Fix Version: 3.0 Alpha 1 [ 10331 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

summary: the cursor identified in the update or delete statement is not positioned on a row - engine code: 335544348 => Error

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

summary: Error => Error "no curent row for fetch operation" when subquery includes a non-trivial derived table

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

summary: Error "no curent row for fetch operation" when subquery includes a non-trivial derived table => Error "no current row for fetch operation" when subquery includes a non-trivial derived table

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Link: This issue relate to CORE3103 [ CORE3103 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

Link: This issue is related to QA252 [ QA252 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @pcisar

QA test added.

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

status: Resolved [ 5 ] => Closed [ 6 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Closed [ 6 ] => Closed [ 6 ]

QA Status: Done successfully

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment