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
Comments
Commented by: @livius2 i add better sample data |
Modified by: @livius2description: CREATE TABLE A CREATE INDEX IXA_A__O ON A (O); GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE INSERT INTO A (ID, O) VALUES ('0', 'kQEysUQMjSKApaeshiZx'); INSERT INTO G (ID, C, K, S) VALUES ('0', NULL, 'aaaaaaaaaaaaaaaaaaaa', 'FFiIx'); INSERT INTO OT (ID, ID_A, S, FLAG, K) VALUES ('0', '1', NULL, NULL, NULL); INSERT INTO OTE (ID, ID_OT, ID_A, CZAS) VALUES ('0', '0', '594543', '13.08.1980, 09:01:13.000'); SELECT OT.* plan is .. i got error => CREATE TABLE A CREATE INDEX IXA_A__O ON A (O); GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE INSERT INTO A (ID, O) VALUES ('0', '5555'); INSERT INTO G (ID, C, K, S) VALUES ('0', '22.01.1989, 13:49:18.576', '6666', 'A'); INSERT INTO OT (ID, ID_A, S, FLAG, K) VALUES ('0', '1', 'B', NULL, '01'); INSERT INTO OTE (ID, ID_OT, ID_A, CZAS) VALUES ('0', '0', '0', '13.08.1980, 09:01:13.000'); SELECT OT.* plan is .. i got error but when i change to use index on A (A.O='6666') SELECT OT.* 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 |
Modified by: @livius2description: CREATE TABLE A CREATE INDEX IXA_A__O ON A (O); GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE INSERT INTO A (ID, O) VALUES ('0', '5555'); INSERT INTO G (ID, C, K, S) VALUES ('0', '22.01.1989, 13:49:18.576', '6666', 'A'); INSERT INTO OT (ID, ID_A, S, FLAG, K) VALUES ('0', '1', 'B', NULL, '01'); INSERT INTO OTE (ID, ID_OT, ID_A, CZAS) VALUES ('0', '0', '0', '13.08.1980, 09:01:13.000'); SELECT OT.* plan is .. i got error but when i change to use index on A (A.O='6666') SELECT OT.* 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 CREATE INDEX IXA_A__O ON A (O); /* ################################################################## */ INSERT INTO A (ID, O) VALUES ('0', '5555'); INSERT INTO G (ID, C, K, S) VALUES ('0', '22.01.1989, 13:49:18.576', '6666', 'A'); INSERT INTO OT (ID, ID_A, S, FLAG, K) VALUES ('0', '1', 'B', NULL, '01'); INSERT INTO OTE (ID, ID_OT, ID_A, CZAS) VALUES ('0', '0', '0', '13.08.1980, 09:01:13.000'); ################################################ SELECT OT.* ######################################### plan is .. ############################################# i got error ############################################# but when i change to use index on A (A.O='6666') SELECT OT.* 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 |
Modified by: @dyemanovassignee: Dmitry Yemanov [ dimitr ] |
Commented by: @dyemanov A much simpler test query against the same data: SELECT OT.* A similar test case against the system tables: select * |
Modified by: @dyemanovVersion: 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 ] |
Modified by: @dyemanovsummary: the cursor identified in the update or delete statement is not positioned on a row - engine code: 335544348 => Error |
Modified by: @dyemanovsummary: Error => Error "no curent row for fetch operation" when subquery includes a non-trivial derived table |
Modified by: @dyemanovsummary: 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 |
Commented by: @pcisar QA test added. |
Modified by: @pcisarstatus: Resolved [ 5 ] => Closed [ 6 ] |
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
The text was updated successfully, but these errors were encountered: