01YaSrFk

1MySQL stored procedure giving different results to command line
2call find_child(1006);
3+--------+
4| nodeid |
5+--------+
6|   1006 |
7|   1006 |
8|   1006 |
9|   1006 |
10+--------+
114 rows in set (0.01 sec)
12	
13mysql> create temporary table KID_TABLE (nodeid INT);
14Query OK, 0 rows affected (0.00 sec)
15
16mysql> insert ignore into KID_TABLE (nodeid) select nodeid from CORPORATENODE 
17       where parentid in (1006);
18Query OK, 4 rows affected (0.00 sec)
19Records: 4  Duplicates: 0  Warnings: 0
20
21mysql> select * from KID_TABLE;
22+--------+
23| nodeid |
24+--------+
25|   1007 |
26|   1008 |
27|   1031 |
28|   1038 |
29+--------+
304 rows in set (0.00 sec)
31	
32DELIMITER $$
33
34DROP PROCEDURE IF EXISTS `find_child`$$
35CREATE PROCEDURE  `find_child`( IN NodeID INT)
36    DETERMINISTIC
37BEGIN
38declare nid INT;
39set nid= NodeID;
40create temporary table KID_TABLE (nodeid INT);
41insert ignore into KID_TABLE (nodeid) select nodeid 
42from CORPORATENODE where parentid in (1006);
43select * from KID_TABLE;
44drop table KID_TABLE;
45END $$
46
47DELIMITER ;
48	
49CREATE TABLE  `PARENT` (  
50    `NODEID` int(11) NOT NULL AUTO_INCREMENT,  
51    `PARENTID` int(11) NOT NULL DEFAULT '0' COMMENT '0 value means top node',  
52    `NAME` varchar(50) NOT NULL,  
53    PRIMARY KEY (`NODEID`) USING BTREE  
54    ) ENGINE=InnoDB;
55	
56DELIMITER $$
57
58DROP PROCEDURE IF EXISTS `find_child`$$
59CREATE PROCEDURE  `find_child`( IN in_NodeID INT)
60BEGIN
61create temporary table KID_TABLE (nodeid INT);
62insert ignore into KID_TABLE (nodeid) select nodeid 
63from CORPORATENODE where parentid in (in_NodeID);
64select * from KID_TABLE;
65drop table KID_TABLE;
66END $$
67
68DELIMITER ;
69	
70DELIMITER $$
71
72DROP PROCEDURE IF EXISTS `find_child`$$
73CREATE PROCEDURE  `find_child`( IN in_NodeID INT)
74BEGIN
75select nodeid from CORPORATENODE where parentid in (in_NodeID);
76END $$
77
78DELIMITER ;
79	
80CREATE PROCEDURE `find_child`( IN NodeID int)
81    DETERMINISTIC
82    BEGIN
83     declare nid INT;
84     declare rows_before INT DEFAULT 0;
85     declare rows_after INT DEFAULT 0;
86     set nid= NodeID;
87     delete from CHILD_TABLE;
88     insert into CHILD_TABLE values (nid);
89     set rows_before = (select count(*) from CHILD_TABLE);
90     if rows_before !=rows_after then
91       increment: repeat
92        set rows_before = (select count(*) from CHILD_TABLE);
93        insert ignore into CHILD_TABLE (nodeid) select b.nodeid from CORPORATENODE b 
94        where b.parentid in (select c.nodeid from CHILD_TABLE c);
95        set rows_after= (select count(*) from CHILD_TABLE);
96        until rows_before =rows_after
97       end repeat increment;
98    end if;
99    update NODEVERSION n set STATUS=1 where n.nodeid in 
100    (select c.nodeid from CHILD_TABLE c);
101   END