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

Jump to bottom

left outer join with sp is bad [CORE396] #740

Closed
firebird-automations opened this issue Nov 28, 2001 · 6 comments
Closed

left outer join with sp is bad [CORE396] #740

firebird-automations opened this issue Nov 28, 2001 · 6 comments
Labels
component: engine fix-version: 1.5.0 priority: major type: bug

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: bazilio (bazilio)

Assigned to: @ArnoBrinkman

SFID: 486370#⁠
Submitted By: bazilio

When I try to execute query like that

select t1.x, ... , tn.x
from sp, t1, .., tn
left outer join tx on
http://tn.id = http://tx.id and
tx.PrimaryKey = 1111
where .....

I see bad results. Sometimes dependeds to query
complexity I get error message
"The cursor identified in the update or delete
statement is not positioned on a row.
no current record for fetch operation.", or get
incomplete result set, some of expected records
disappears.
I try to use explainment for bug 219525, but with no
results.
@firebird-automations
Copy link
Collaborator Author

Commented by: Alice F. Bird (firebirds)

Date: 2001-12-06 18:15
Sender: seanleyne
Logged In: YES
user_id=71163

Vasily,

I haven't had time to run a test using your script (thanks,
by the way).

Could you please try the following SQL-92 formatted query:

select pin.PROD_ID, pin.RAW_ID, rd.RAW_NAME,pin.RAW_MIN,
pin.RAW_MAX
from "sp_Test" sp
JOIN TestDct rd on sp.RAW_GROUP_ID = rd.RAW_GROUP_ID
LEFT OUTER JOIN TestTable pin ON rd.RAW_ID = pin.RAW_ID
where pin.PROD_ID = 11111
order by sp.SEQUENCE_ID, rd.SEQUENCE_ID

@firebird-automations
Copy link
Collaborator Author

Commented by: Alice F. Bird (firebirds)

Date: 2001-12-06 09:57
Sender: bazilio
Logged In: YES
user_id=379820

Here is a test script distilled from the real data
from my database

create database 'test.gdb' user 'sysdba'
password 'masterkey' page_size=4096;

create table TestGroups
(raw_group_id integer not null,
parent integer,
sequence_id integer not null,
raw_group_name varchar(64),
constraint TestGroups_PK primary key (raw_group_id)
);

CREATE TABLE TestDct (
RAW_ID integer not null,
RAW_NAME varchar(48) not null,
RAW_GROUP_ID integer,
SEQUENCE_ID integer not null,
CONSTRAINT TestDct_PK PRIMARY KEY (RAW_ID),
CONSTRAINT TestDct_FK_RAW_GROUP_ID FOREIGN KEY
(RAW_GROUP_ID) REFERENCES TestGroups (RAW_GROUP_ID) ON
UPDATE CASCADE
);

CREATE TABLE TestTable (
PROD_ID integer NOT NULL,
RAW_ID integer NOT NULL,
RAW_MIN double precision,
RAW_MAX double precision,
CONSTRAINT TastTable_PK PRIMARY KEY (PROD_ID, RAW_ID),
CONSTRAINT TestTable_FK_RAW_ID FOREIGN KEY (RAW_ID)
REFERENCES TestDct(RAW_ID) ON DELETE CASCADE
);

INSERT INTO TESTGROUPS
(RAW_GROUP_ID,PARENT,SEQUENCE_ID,RAW_GROUP_NAME) VALUES
(26,0,1,'11');
INSERT INTO TESTGROUPS
(RAW_GROUP_ID,PARENT,SEQUENCE_ID,RAW_GROUP_NAME) VALUES
(2,26,2,'22');

INSERT INTO TESTDCT
(RAW_ID,RAW_NAME,RAW_GROUP_ID,SEQUENCE_ID) VALUES
(111110,'111110',2,261);
INSERT INTO TESTDCT
(RAW_ID,RAW_NAME,RAW_GROUP_ID,SEQUENCE_ID) VALUES
(111111,'111111',2,264);
INSERT INTO TESTDCT
(RAW_ID,RAW_NAME,RAW_GROUP_ID,SEQUENCE_ID) VALUES
(111113,'111113',2,262);
INSERT INTO TESTDCT
(RAW_ID,RAW_NAME,RAW_GROUP_ID,SEQUENCE_ID) VALUES
(111140,'111140',2,255);
INSERT INTO TESTDCT
(RAW_ID,RAW_NAME,RAW_GROUP_ID,SEQUENCE_ID) VALUES
(111150,'111150',2,277);
INSERT INTO TESTDCT
(RAW_ID,RAW_NAME,RAW_GROUP_ID,SEQUENCE_ID) VALUES
(111152,'111152',2,280);
INSERT INTO TESTDCT
(RAW_ID,RAW_NAME,RAW_GROUP_ID,SEQUENCE_ID) VALUES
(111160,'111160',2,278);
INSERT INTO TESTDCT
(RAW_ID,RAW_NAME,RAW_GROUP_ID,SEQUENCE_ID) VALUES
(111250,'111250',2,289);
INSERT INTO TESTDCT
(RAW_ID,RAW_NAME,RAW_GROUP_ID,SEQUENCE_ID) VALUES
(111410,'111410',2,284);
INSERT INTO TESTDCT
(RAW_ID,RAW_NAME,RAW_GROUP_ID,SEQUENCE_ID) VALUES
(131111,'131111',2,333);
INSERT INTO TESTDCT
(RAW_ID,RAW_NAME,RAW_GROUP_ID,SEQUENCE_ID) VALUES
(131112,'131112',2,334);
INSERT INTO TESTDCT
(RAW_ID,RAW_NAME,RAW_GROUP_ID,SEQUENCE_ID) VALUES
(131113,'131113',2,335);
INSERT INTO TESTDCT
(RAW_ID,RAW_NAME,RAW_GROUP_ID,SEQUENCE_ID) VALUES
(131510,'131510',2,273);
INSERT INTO TESTDCT
(RAW_ID,RAW_NAME,RAW_GROUP_ID,SEQUENCE_ID) VALUES
(132110,'132110',2,340);
INSERT INTO TESTDCT
(RAW_ID,RAW_NAME,RAW_GROUP_ID,SEQUENCE_ID) VALUES
(132421,'132421',2,352);
INSERT INTO TESTDCT
(RAW_ID,RAW_NAME,RAW_GROUP_ID,SEQUENCE_ID) VALUES
(132423,'132423',2,355);
INSERT INTO TESTDCT
(RAW_ID,RAW_NAME,RAW_GROUP_ID,SEQUENCE_ID) VALUES
(132424,'132424',2,354);
INSERT INTO TESTDCT
(RAW_ID,RAW_NAME,RAW_GROUP_ID,SEQUENCE_ID) VALUES
(132425,'132425',2,353);
INSERT INTO TESTDCT
(RAW_ID,RAW_NAME,RAW_GROUP_ID,SEQUENCE_ID) VALUES
(132463,'132463',2,360);
INSERT INTO TESTDCT
(RAW_ID,RAW_NAME,RAW_GROUP_ID,SEQUENCE_ID) VALUES
(132464,'132464',2,359);
INSERT INTO TESTDCT
(RAW_ID,RAW_NAME,RAW_GROUP_ID,SEQUENCE_ID) VALUES
(132465,'132465',2,358);
INSERT INTO TESTDCT
(RAW_ID,RAW_NAME,RAW_GROUP_ID,SEQUENCE_ID) VALUES
(132467,'132467',2,361);
INSERT INTO TESTDCT
(RAW_ID,RAW_NAME,RAW_GROUP_ID,SEQUENCE_ID) VALUES
(133110,'133110',2,408);
INSERT INTO TESTDCT
(RAW_ID,RAW_NAME,RAW_GROUP_ID,SEQUENCE_ID) VALUES
(211111,'211111',2,383);
INSERT INTO TESTDCT
(RAW_ID,RAW_NAME,RAW_GROUP_ID,SEQUENCE_ID) VALUES
(211112,'211112',2,384);
INSERT INTO TESTDCT
(RAW_ID,RAW_NAME,RAW_GROUP_ID,SEQUENCE_ID) VALUES
(211113,'211113',2,385);
INSERT INTO TESTDCT
(RAW_ID,RAW_NAME,RAW_GROUP_ID,SEQUENCE_ID) VALUES
(211213,'211213',2,376);
INSERT INTO TESTDCT
(RAW_ID,RAW_NAME,RAW_GROUP_ID,SEQUENCE_ID) VALUES
(211214,'211214',2,377);
INSERT INTO TESTDCT
(RAW_ID,RAW_NAME,RAW_GROUP_ID,SEQUENCE_ID) VALUES
(212111,'212111',2,388);
INSERT INTO TESTDCT
(RAW_ID,RAW_NAME,RAW_GROUP_ID,SEQUENCE_ID) VALUES
(212112,'212112',2,389);
INSERT INTO TESTDCT
(RAW_ID,RAW_NAME,RAW_GROUP_ID,SEQUENCE_ID) VALUES
(212113,'212113',2,390);
INSERT INTO TESTDCT
(RAW_ID,RAW_NAME,RAW_GROUP_ID,SEQUENCE_ID) VALUES
(212114,'212114',2,391);
INSERT INTO TESTDCT
(RAW_ID,RAW_NAME,RAW_GROUP_ID,SEQUENCE_ID) VALUES
(212116,'212116',2,392);
INSERT INTO TESTDCT
(RAW_ID,RAW_NAME,RAW_GROUP_ID,SEQUENCE_ID) VALUES
(212160,'212160',2,380);
INSERT INTO TESTDCT
(RAW_ID,RAW_NAME,RAW_GROUP_ID,SEQUENCE_ID) VALUES
(213110,'213110',2,367);
INSERT INTO TESTDCT
(RAW_ID,RAW_NAME,RAW_GROUP_ID,SEQUENCE_ID) VALUES
(213120,'213120',2,396);
INSERT INTO TESTDCT
(RAW_ID,RAW_NAME,RAW_GROUP_ID,SEQUENCE_ID) VALUES
(213130,'213130',2,397);
INSERT INTO TESTDCT
(RAW_ID,RAW_NAME,RAW_GROUP_ID,SEQUENCE_ID) VALUES
(213150,'213150',2,368);
INSERT INTO TESTDCT
(RAW_ID,RAW_NAME,RAW_GROUP_ID,SEQUENCE_ID) VALUES
(214110,'214110',2,366);
INSERT INTO TESTDCT
(RAW_ID,RAW_NAME,RAW_GROUP_ID,SEQUENCE_ID) VALUES
(214111,'214111',2,407);
INSERT INTO TESTDCT
(RAW_ID,RAW_NAME,RAW_GROUP_ID,SEQUENCE_ID) VALUES
(241112,'241112',2,414);
INSERT INTO TESTDCT
(RAW_ID,RAW_NAME,RAW_GROUP_ID,SEQUENCE_ID) VALUES
(241113,'241113',2,415);
INSERT INTO TESTDCT
(RAW_ID,RAW_NAME,RAW_GROUP_ID,SEQUENCE_ID) VALUES
(241114,'241114',2,416);
INSERT INTO TESTDCT
(RAW_ID,RAW_NAME,RAW_GROUP_ID,SEQUENCE_ID) VALUES
(241410,'241410',2,417);
INSERT INTO TESTDCT
(RAW_ID,RAW_NAME,RAW_GROUP_ID,SEQUENCE_ID) VALUES
(241510,'241510',2,418);
INSERT INTO TESTDCT
(RAW_ID,RAW_NAME,RAW_GROUP_ID,SEQUENCE_ID) VALUES
(242310,'242310',2,428);
INSERT INTO TESTDCT
(RAW_ID,RAW_NAME,RAW_GROUP_ID,SEQUENCE_ID) VALUES
(242500,'242500',2,432);
INSERT INTO TESTDCT
(RAW_ID,RAW_NAME,RAW_GROUP_ID,SEQUENCE_ID) VALUES
(250110,'250110',2,455);
INSERT INTO TESTDCT
(RAW_ID,RAW_NAME,RAW_GROUP_ID,SEQUENCE_ID) VALUES
(251110,'251110',2,458);
INSERT INTO TESTDCT
(RAW_ID,RAW_NAME,RAW_GROUP_ID,SEQUENCE_ID) VALUES
(251130,'251130',2,450);
INSERT INTO TESTDCT
(RAW_ID,RAW_NAME,RAW_GROUP_ID,SEQUENCE_ID) VALUES
(251140,'251140',2,446);
INSERT INTO TESTDCT
(RAW_ID,RAW_NAME,RAW_GROUP_ID,SEQUENCE_ID) VALUES
(251150,'251150',2,456);
INSERT INTO TESTDCT
(RAW_ID,RAW_NAME,RAW_GROUP_ID,SEQUENCE_ID) VALUES
(252110,'252110',2,449);
INSERT INTO TESTDCT
(RAW_ID,RAW_NAME,RAW_GROUP_ID,SEQUENCE_ID) VALUES
(419035,'419035',2,259);
INSERT INTO TESTDCT
(RAW_ID,RAW_NAME,RAW_GROUP_ID,SEQUENCE_ID) VALUES
(419125,'419125',2,411);

INSERT INTO TESTTABLE (PROD_ID,RAW_ID,RAW_MIN,RAW_MAX)
VALUES (11111,111111,0,60);
INSERT INTO TESTTABLE (PROD_ID,RAW_ID,RAW_MIN,RAW_MAX)
VALUES (11111,111113,0,60);
INSERT INTO TESTTABLE (PROD_ID,RAW_ID,RAW_MIN,RAW_MAX)
VALUES (11111,111140,0,40);
INSERT INTO TESTTABLE (PROD_ID,RAW_ID,RAW_MIN,RAW_MAX)
VALUES (11111,111150,0,15);
INSERT INTO TESTTABLE (PROD_ID,RAW_ID,RAW_MIN,RAW_MAX)
VALUES (11111,111152,0,15);
INSERT INTO TESTTABLE (PROD_ID,RAW_ID,RAW_MIN,RAW_MAX)
VALUES (11111,111160,0,40);
INSERT INTO TESTTABLE (PROD_ID,RAW_ID,RAW_MIN,RAW_MAX)
VALUES (11111,111250,0,10);
INSERT INTO TESTTABLE (PROD_ID,RAW_ID,RAW_MIN,RAW_MAX)
VALUES (11111,111410,0,10);
INSERT INTO TESTTABLE (PROD_ID,RAW_ID,RAW_MIN,RAW_MAX)
VALUES (11111,131111,0,3);
INSERT INTO TESTTABLE (PROD_ID,RAW_ID,RAW_MIN,RAW_MAX)
VALUES (11111,131112,0,3);
INSERT INTO TESTTABLE (PROD_ID,RAW_ID,RAW_MIN,RAW_MAX)
VALUES (11111,131113,0,3);
INSERT INTO TESTTABLE (PROD_ID,RAW_ID,RAW_MIN,RAW_MAX)
VALUES (11111,131510,0,5);
INSERT INTO TESTTABLE (PROD_ID,RAW_ID,RAW_MIN,RAW_MAX)
VALUES (11111,132110,0,15);
INSERT INTO TESTTABLE (PROD_ID,RAW_ID,RAW_MIN,RAW_MAX)
VALUES (11111,132421,0,15);
INSERT INTO TESTTABLE (PROD_ID,RAW_ID,RAW_MIN,RAW_MAX)
VALUES (11111,132423,0,15);
INSERT INTO TESTTABLE (PROD_ID,RAW_ID,RAW_MIN,RAW_MAX)
VALUES (11111,132424,0,15);
INSERT INTO TESTTABLE (PROD_ID,RAW_ID,RAW_MIN,RAW_MAX)
VALUES (11111,132425,0,15);
INSERT INTO TESTTABLE (PROD_ID,RAW_ID,RAW_MIN,RAW_MAX)
VALUES (11111,132463,0,25);
INSERT INTO TESTTABLE (PROD_ID,RAW_ID,RAW_MIN,RAW_MAX)
VALUES (11111,132464,0,25);
INSERT INTO TESTTABLE (PROD_ID,RAW_ID,RAW_MIN,RAW_MAX)
VALUES (11111,132465,0,25);
INSERT INTO TESTTABLE (PROD_ID,RAW_ID,RAW_MIN,RAW_MAX)
VALUES (11111,132467,0,25);
INSERT INTO TESTTABLE (PROD_ID,RAW_ID,RAW_MIN,RAW_MAX)
VALUES (11111,133110,0,3);
INSERT INTO TESTTABLE (PROD_ID,RAW_ID,RAW_MIN,RAW_MAX)
VALUES (11111,211111,0,4);
INSERT INTO TESTTABLE (PROD_ID,RAW_ID,RAW_MIN,RAW_MAX)
VALUES (11111,211112,0,4);
INSERT INTO TESTTABLE (PROD_ID,RAW_ID,RAW_MIN,RAW_MAX)
VALUES (11111,211113,0,4);
INSERT INTO TESTTABLE (PROD_ID,RAW_ID,RAW_MIN,RAW_MAX)
VALUES (11111,211213,0,1);
INSERT INTO TESTTABLE (PROD_ID,RAW_ID,RAW_MIN,RAW_MAX)
VALUES (11111,211214,0,1);
INSERT INTO TESTTABLE (PROD_ID,RAW_ID,RAW_MIN,RAW_MAX)
VALUES (11111,212111,0,10);
INSERT INTO TESTTABLE (PROD_ID,RAW_ID,RAW_MIN,RAW_MAX)
VALUES (11111,212112,0,10);
INSERT INTO TESTTABLE (PROD_ID,RAW_ID,RAW_MIN,RAW_MAX)
VALUES (11111,212113,0,10);
INSERT INTO TESTTABLE (PROD_ID,RAW_ID,RAW_MIN,RAW_MAX)
VALUES (11111,212114,0,10);
INSERT INTO TESTTABLE (PROD_ID,RAW_ID,RAW_MIN,RAW_MAX)
VALUES (11111,212116,0,10);
INSERT INTO TESTTABLE (PROD_ID,RAW_ID,RAW_MIN,RAW_MAX)
VALUES (11111,212160,0,5);
INSERT INTO TESTTABLE (PROD_ID,RAW_ID,RAW_MIN,RAW_MAX)
VALUES (11111,213110,0,6);
INSERT INTO TESTTABLE (PROD_ID,RAW_ID,RAW_MIN,RAW_MAX)
VALUES (11111,213120,0,6);
INSERT INTO TESTTABLE (PROD_ID,RAW_ID,RAW_MIN,RAW_MAX)
VALUES (11111,213130,0,6);
INSERT INTO TESTTABLE (PROD_ID,RAW_ID,RAW_MIN,RAW_MAX)
VALUES (11111,213150,0,3);
INSERT INTO TESTTABLE (PROD_ID,RAW_ID,RAW_MIN,RAW_MAX)
VALUES (11111,214110,0,5);
INSERT INTO TESTTABLE (PROD_ID,RAW_ID,RAW_MIN,RAW_MAX)
VALUES (11111,214111,0,5);
INSERT INTO TESTTABLE (PROD_ID,RAW_ID,RAW_MIN,RAW_MAX)
VALUES (11111,241112,0,5);
INSERT INTO TESTTABLE (PROD_ID,RAW_ID,RAW_MIN,RAW_MAX)
VALUES (11111,241113,0,5);
INSERT INTO TESTTABLE (PROD_ID,RAW_ID,RAW_MIN,RAW_MAX)
VALUES (11111,241114,0,5);
INSERT INTO TESTTABLE (PROD_ID,RAW_ID,RAW_MIN,RAW_MAX)
VALUES (11111,241410,0,5);
INSERT INTO TESTTABLE (PROD_ID,RAW_ID,RAW_MIN,RAW_MAX)
VALUES (11111,241510,0,5);
INSERT INTO TESTTABLE (PROD_ID,RAW_ID,RAW_MIN,RAW_MAX)
VALUES (11111,242310,0,2);
INSERT INTO TESTTABLE (PROD_ID,RAW_ID,RAW_MIN,RAW_MAX)
VALUES (11111,242500,0,0.2);
INSERT INTO TESTTABLE (PROD_ID,RAW_ID,RAW_MIN,RAW_MAX)
VALUES (11111,250110,0,0.3);
INSERT INTO TESTTABLE (PROD_ID,RAW_ID,RAW_MIN,RAW_MAX)
VALUES (11111,251110,0,3);
INSERT INTO TESTTABLE (PROD_ID,RAW_ID,RAW_MIN,RAW_MAX)
VALUES (11111,251130,0,2);
INSERT INTO TESTTABLE (PROD_ID,RAW_ID,RAW_MIN,RAW_MAX)
VALUES (11111,251140,0,2);
INSERT INTO TESTTABLE (PROD_ID,RAW_ID,RAW_MIN,RAW_MAX)
VALUES (11111,251150,0,2);
INSERT INTO TESTTABLE (PROD_ID,RAW_ID,RAW_MIN,RAW_MAX)
VALUES (11111,252110,0,2);
INSERT INTO TESTTABLE (PROD_ID,RAW_ID,RAW_MIN,RAW_MAX)
VALUES (11111,419035,0,20);
INSERT INTO TESTTABLE (PROD_ID,RAW_ID,RAW_MIN,RAW_MAX)
VALUES (11111,419125,3,5);
INSERT INTO TESTTABLE (PROD_ID,RAW_ID,RAW_MIN,RAW_MAX)
VALUES (11111,111110,0,60);

commit;

set term ^;

CREATE PROCEDURE "sp_Test"
RETURNS (
RAW_GROUP_ID INTEGER,
parent INTEGER,
SEQUENCE_ID INTEGER,
raw_group_name VARCHAR(32) CHARACTER SET NONE)
AS
begin

for select raw_group_id, parent, sequence_id,
raw_group_name
from TestGroups

into :raw_group_id, :parent, :sequence_id, :raw_group_name
do
suspend;
end^

set term ;^

commit;

I try to execute following query

select pin.PROD_ID, pin.RAW_ID, rd.RAW_NAME,
pin.RAW_MIN, pin.RAW_MAX
from "sp_Test" sp, TestDct rd LEFT OUTER JOIN TestTable
pin ON
rd.RAW_ID = pin.RAW_ID
where sp.RAW_GROUP_ID = rd.RAW_GROUP_ID AND
pin.PROD_ID = 11111
order by sp.SEQUENCE_ID, rd.SEQUENCE_ID

and get the message
"The cursor identified in the update or delete
statement is not positioned on a row.
no current record for fetch operation.". When I delete
TestDct_FK_RAW_GROUP_ID query works well.

@firebird-automations
Copy link
Collaborator Author

Commented by: Alice F. Bird (firebirds)

Date: 2001-12-06 06:02
Sender: seanleyne
Logged In: YES
user_id=71163

You are mixing SQL-89 (...from Table1, Table2...) and SQL-
92 (... from Table1 INNER/LEFT JOIN TABLE2 ON...) join
syntax. Perhaps you can try using a consistent syntax for
the query.

@firebird-automations
Copy link
Collaborator Author

Commented by: Alice F. Bird (firebirds)

Date: 2001-11-28 07:51
Sender: helebor
Logged In: YES
user_id=60469

what happens when you use correct JOIN syntax?

select t1.x, ... , tn.x
from sp, t1, .., tn
left outer join tx on
http://tn.id = http://tx.id /* and */
WHERE
tx.PrimaryKey = 1111
/* where */ AND .....

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

Workflow: jira [ 10420 ] => Firebird [ 14693 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

QA Status: No test

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
component: engine fix-version: 1.5.0 priority: major type: bug
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
1 participant
@firebird-automations