Subselect: Not all rows are processed

[Thomas Sonnleitner]

Hello!

If I use a subselect in the cursor definition, not all rows of the select are processed. After removing the subselect, the cursor works.

To simulate my problem, I wrote a small test database:

-----------------------------------------
-- test database
-----------------------------------------

CREATE TABLE A(
id INTEGER PRIMARY KEY
);

CREATE TABLE B(
id INTEGER PRIMARY KEY
);

CREATE TABLE AB(
a_id INTEGER,
b_id INTEGER
);

ALTER TABLE AB
ADD PRIMARY KEY (a_id, b_id);

CREATE TABLE test(
id INTEGER PRIMARY KEY AUTO_INCREMENT,
info VARCHAR(200)
);



CREATE PROCEDURE insertABEntrysForEveryABPosibility(IN p_b_id INT)
BEGIN
DECLARE a_id BIGINT;
DECLARE hasMoreRows BOOL DEFAULT TRUE;
DECLARE curA CURSOR FOR SELECT id FROM A WHERE id NOT IN (SELECT a_id FROM AB JOIN B ON AB.b_id=B.id WHERE B.id=p_b_id);
DECLARE
CONTINUE HANDLER FOR
SQLSTATE '02000'
SET hasMoreRows = FALSE;

OPEN curA;
INSERT INTO test(info) VALUES(CONCAT('A: ',a_id));

FETCH curA INTO a_id;
WHILE hasMoreRows DO
INSERT INTO test(info) VALUES(CONCAT(CONCAT(CONCAT('A: ',a_id),', hasMoreRows : '), hasMoreRows ));
INSERT INTO AB(a_id, b_id) VALUES(a_id, p_b_id);
FETCH curA INTO a_id;
END WHILE;

CLOSE curA;
END


-----------------------------------------
-- test data
-----------------------------------------
DELETE FROM A;
DELETE FROM B;
DELETE FROM AB;
DELETE FROM test;

INSERT INTO A VALUES(1);
INSERT INTO A VALUES(2);
INSERT INTO A VALUES(3);

INSERT INTO B VALUES(1);
INSERT INTO B VALUES(2);
INSERT INTO B VALUES(3);
INSERT INTO B VALUES(4);

INSERT INTO AB VALUES(1,1);
INSERT INTO AB VALUES(1,2);
INSERT INTO AB VALUES(1,3);

INSERT INTO AB VALUES(2,1);
INSERT INTO AB VALUES(2,2);
INSERT INTO AB VALUES(2,3);

INSERT INTO AB VALUES(3,2);
INSERT INTO AB VALUES(3,3);


-----------------------------------------
-- step by step
-----------------------------------------

SELECT id FROM A WHERE id NOT IN (SELECT a_id FROM AB JOIN B ON AB.b_id=B.id WHERE B.id=4); -- 3 Rows are returned
CALL insertABEntrysForEveryABPosibility(4); -- 2 Rows are processed
SELECT * FROM test; --You may have a look an the test-logging-table


-----------------------------------------
-----------------------------------------

Why does the cursor in the procedure does not process all rows of the select-statement? Goal if the procedure is to add one AB-row for every A and the passed p_b_id.
By the way: The cursor does not work as well, if you have no insert into the AB-table but only the insert into the test-logging-table.

My you have an idea. Thank you for your help!

Kind regards, Thomas



Edited 3 time(s). Last edit at 07/09/2009 09:47AM by Thomas Sonnleitner.