| Bug #61604 | Cursor does not return rows in correct order as in the order by of the select | ||
|---|---|---|---|
| Submitted: | 23 Jun 2011 7:14 | Modified: | 22 Dec 2011 22:49 |
| Reporter: | Manoj Kansal | Email Updates: | |
| Status: | No Feedback | Impact on me: | |
| Category: | MySQL Server: DDL | Severity: | S1 (Critical) |
| Version: | 5.1.48 | OS: | Windows (7 Ultimate) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | cursor, order by | ||
[23 Jun 2011 7:25]
Valeriy Kravchuk
Please, provide complete minimal test case, including stored procedure or function that uses cursor. What exact server version, 5.1.x, do you use?
[23 Jun 2011 8:52]
Manoj Kansal
Hi, Please find the stored procedure attached as match_Offers.sql. And the server version I am using is 5.1.48 Thanks, Manoj
[23 Jun 2011 8:53]
Manoj Kansal
Hi, Please find the stored procedure attached as match_Offers.sql. And the server version I am using is 5.1.48 Thanks, Manoj
[4 Jul 2011 15:54]
MySQL Verification Team
Could you please provide all the tables needed to run the SP. Thanks. C:\DBS>c:\dbs\5.5\bin\mysql -uroot --port=3540 --prompt="mysql 5.5 >" Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 Server version: 5.5.15-log Source distribution Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql 5.5 >use apps Database changed mysql 5.5 >call match_Offers(7); +------------------------------+ | Error occurred û terminating | +------------------------------+ | Error occurred û terminating | +------------------------------+ 1 row in set (0.03 sec) Query OK, 0 rows affected, 1 warning (0.03 sec) mysql 5.5 >show warnings; +-------+------+--------------------------------------+ | Level | Code | Message | +-------+------+--------------------------------------+ | Error | 1146 | Table 'apps.trade_all' doesn't exist | +-------+------+--------------------------------------+ 1 row in set (0.00 sec) mysql 5.5 >show tables; +----------------+ | Tables_in_apps | +----------------+ | offers_all | +----------------+ 1 row in set (0.00 sec) mysql 5.5 >
[4 Jul 2011 17:04]
Manoj Kansal
Hi, I have already provided the same details. Please check section: How to repeat: -------------- create a table offers_all as: delimiter $$ CREATE TABLE `offers_all` ( `OFFER_ID` int(11) unsigned zerofill NOT NULL AUTO_INCREMENT, `FROM_CUSTOMER` varchar(45) DEFAULT NULL, `TO_CUSTOMER` varchar(45) DEFAULT NULL, `STATUS` varchar(45) DEFAULT NULL, `OFFER_MANDI` varchar(45) DEFAULT NULL, `OFFER_TYPE` varchar(2) DEFAULT NULL, `OFFER_PRODUCT` varchar(45) DEFAULT NULL, `PRODUCT_GRADE` varchar(45) DEFAULT NULL, `QUANTITY` int(11) DEFAULT '0', `QUANTITY_OPEN` int(11) DEFAULT '0', `QUANTITY_EXECUTED` int(11) DEFAULT '0', `QUANTITY_CANCELED` int(11) DEFAULT '0', `QUANTITY_EXPIRED` int(11) DEFAULT '0', `ADVANCE_RATE` double DEFAULT '0', `NEXTDAY_RATE` double DEFAULT '0', `REGULAR_RATE` double DEFAULT '0', `LOADING` varchar(2) DEFAULT NULL, `EXCISE_DUTY` varchar(2) DEFAULT NULL, `VAT` varchar(2) DEFAULT NULL, `DECLARTAION` varchar(25) DEFAULT NULL, `TRANSPORTATION` varchar(2) DEFAULT NULL, `INSURANCE` varchar(2) DEFAULT NULL, `SERVICE_TAX` varchar(2) DEFAULT NULL, `REVISION_NO` int(11) DEFAULT '0', `CREATE_BY` varchar(45) DEFAULT NULL, `CREATED_TIME` datetime DEFAULT NULL, `UPDATE_BY` varchar(45) DEFAULT NULL, `UPDATED_TIME` datetime DEFAULT NULL, `MATCH_ID` int(11) DEFAULT NULL, PRIMARY KEY (`OFFER_ID`), KEY `OFFER_INDEX` (`OFFER_TYPE`,`OFFER_PRODUCT`,`OFFER_MANDI`), KEY `OFFER_TO_CUST_FK` (`TO_CUSTOMER`), KEY `OFFER_FROM_CUST_FK` (`FROM_CUSTOMER`), CONSTRAINT `OFFER_FROM_CUST_FK` FOREIGN KEY (`FROM_CUSTOMER`) REFERENCES `customers_all` (`SYMBOL`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `OFFER_TO_CUST_FK` FOREIGN KEY (`TO_CUSTOMER`) REFERENCES `customers_all` (`SYMBOL`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=latin1$$ Insert Records as below: INSERT INTO `offers_all` (OFFER_ID,FROM_CUSTOMER,TO_CUSTOMER,STATUS,OFFER_MANDI,OFFER_TYPE,OFFER_PRODUCT,PRODUCT_GRADE,QUANTITY,QUANTITY_OPEN,QUANTITY_EXECUTED,QUANTITY_CANCELED,QUANTITY_EXPIRED,ADVANCE_RATE,NEXTDAY_RATE,REGULAR_RATE,LOADING,EXCISE_DUTY,VAT,DECLARTAION,TRANSPORTATION,INSURANCE,SERVICE_TAX,REVISION_NO,CREATE_BY,CREATED_TIME,UPDATE_BY,UPDATED_TIME,MATCH_ID) VALUES (00000000007,'MAHEN',NULL,'O','RPR','S','BILLET','A',40,40,0,0,0,20200,20500,0,'I','E','E','Declaration','E','I','I',27,'test1','2011-05-30 20:53:00','System','2011-06-23 02:08:32',NULL); INSERT INTO `offers_all` (OFFER_ID,FROM_CUSTOMER,TO_CUSTOMER,STATUS,OFFER_MANDI,OFFER_TYPE,OFFER_PRODUCT,PRODUCT_GRADE,QUANTITY,QUANTITY_OPEN,QUANTITY_EXECUTED,QUANTITY_CANCELED,QUANTITY_EXPIRED,ADVANCE_RATE,NEXTDAY_RATE,REGULAR_RATE,LOADING,EXCISE_DUTY,VAT,DECLARTAION,TRANSPORTATION,INSURANCE,SERVICE_TAX,REVISION_NO,CREATE_BY,CREATED_TIME,UPDATE_BY,UPDATED_TIME,MATCH_ID) VALUES (00000000014,'SATYA',NULL,'O','RPR','B','BILLET','A',30,30,0,0,0,20100,20600,0,'I','E','E','Declaration','E','I','I',16,'test','2011-06-05 23:33:04','System','2011-06-23 02:08:32',NULL); INSERT INTO `offers_all` (OFFER_ID,FROM_CUSTOMER,TO_CUSTOMER,STATUS,OFFER_MANDI,OFFER_TYPE,OFFER_PRODUCT,PRODUCT_GRADE,QUANTITY,QUANTITY_OPEN,QUANTITY_EXECUTED,QUANTITY_CANCELED,QUANTITY_EXPIRED,ADVANCE_RATE,NEXTDAY_RATE,REGULAR_RATE,LOADING,EXCISE_DUTY,VAT,DECLARTAION,TRANSPORTATION,INSURANCE,SERVICE_TAX,REVISION_NO,CREATE_BY,CREATED_TIME,UPDATE_BY,UPDATED_TIME,MATCH_ID) VALUES (00000000015,'SATYA',NULL,'O','RPR','B','BILLET','A',20,20,0,0,0,20500,0,0,'I','E','E','Declaration','E','I','I',12,'test','2011-06-06 16:40:16','System','2011-06-23 02:08:32',NULL); Thanks, Manoj
[10 Jul 2011 11:53]
Valeriy Kravchuk
I've got the same result as Miguel: macbook-pro:5.1 openxs$ bin/mysql -uroot test < ~/Downloads/match_Offers.sql macbook-pro:5.1 openxs$ bin/mysql -uroot test Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 6 Server version: 5.1.59-debug Source distribution Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved. This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL v2 license Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> call match_Offers(7); +------------------------------+ | Error occurred ? terminating | +------------------------------+ | Error occurred ? terminating | +------------------------------+ 1 row in set (0.04 sec) Query OK, 0 rows affected (0.04 sec) So, please, provide a complete test case that proves there is a problem on recent MySQL server version, 5.1.58 at the moment.
[21 Jul 2011 11:30]
Manoj Kansal
I Don't understand why is there so much problem trying to reproduce the issue. Is there a possibility of arranging a web conference or something. So, I can guide to to reproduce the issue. All the steps to reproduce the issue has been provided very clearly and in simple words. Step 1. Create the database table under schema apps. Step 2. Insert records as in my last update Step 3. Create the function Step 4. Execute the function Thanks, Manoj
[22 Nov 2011 22:49]
MySQL Verification Team
Please try version 5.1.60. Thanks.
[23 Dec 2011 7:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
Description: Hi, I have defined a cursor in my stored procedure as below: DECLARE match_cursor CURSOR FOR SELECT FROMCUST.FROM_CUSTOMER FROMCUST, TOCUST.OFFER_ID TOID, TOCUST.FROM_CUSTOMER TOCUST, FROMCUST.QUANTITY_OPEN FROMQUANTITY, TOCUST.QUANTITY_OPEN TOQUANTITY, FROMCUST.OFFER_MANDI FROMMANDI, FROMCUST.OFFER_TYPE FROMTYPE, FROMCUST.OFFER_PRODUCT FROMPRODUCT, FROMCUST.PRODUCT_GRADE FROMGRADE, COALESCE (IF(TOCUST.ADVANCE_RATE >= FROMCUST.ADVANCE_RATE, TOCUST.ADVANCE_RATE, NULL), IF(TOCUST.NEXTDAY_RATE >= FROMCUST.NEXTDAY_RATE, TOCUST.NEXTDAY_RATE, NULL), IF(TOCUST.REGULAR_RATE >= FROMCUST.REGULAR_RATE, TOCUST.REGULAR_RATE, NULL), NULL) AS RATE, FROMCUST.LOADING FROMLOADING, FROMCUST.EXCISE_DUTY FROMEXCISE, FROMCUST.VAT FROMVAT, FROMCUST.DECLARTAION FROMDECLARATION, FROMCUST.TRANSPORTATION FROMTRANSPORT, FROMCUST.INSURANCE FROMINSURANCE, FROMCUST.SERVICE_TAX FROMSERVICE, FROMCUST.REVISION_NO FROMVERSION, TOCUST.REVISION_NO TOVERSION, COALESCE (IF(TOCUST.ADVANCE_RATE >= FROMCUST.ADVANCE_RATE, 1, NULL), IF(TOCUST.NEXTDAY_RATE >= FROMCUST.NEXTDAY_RATE, 2, NULL), IF(TOCUST.REGULAR_RATE >= FROMCUST.REGULAR_RATE, 3, NULL), NULL) AS PAYMENTTERM FROM apps.OFFERS_ALL FROMCUST,apps.OFFERS_ALL TOCUST WHERE FROMCUST.OFFER_ID = ? AND FROMCUST.STATUS IN ( 'O', 'P' ) AND TOCUST.OFFER_TYPE != FROMCUST.OFFER_TYPE AND TOCUST.STATUS IN ( 'O', 'P' ) AND TOCUST.OFFER_MANDI = FROMCUST.OFFER_MANDI AND TOCUST.FROM_CUSTOMER != FROMCUST.FROM_CUSTOMER AND IFNULL(TOCUST.TO_CUSTOMER, FROMCUST.FROM_CUSTOMER) = FROMCUST.FROM_CUSTOMER AND TOCUST.FROM_CUSTOMER = IFNULL(FROMCUST.TO_CUSTOMER, TOCUST.FROM_CUSTOMER) AND ( TOCUST.ADVANCE_RATE >= FROMCUST.ADVANCE_RATE OR TOCUST.NEXTDAY_RATE >= FROMCUST.NEXTDAY_RATE OR TOCUST.REGULAR_RATE >= FROMCUST.REGULAR_RATE ) AND TOCUST.OFFER_PRODUCT = FROMCUST.OFFER_PRODUCT AND TOCUST.PRODUCT_GRADE = FROMCUST.PRODUCT_GRADE AND TOCUST.LOADING = FROMCUST.LOADING AND TOCUST.EXCISE_DUTY = FROMCUST.EXCISE_DUTY AND TOCUST.VAT = FROMCUST.VAT AND TOCUST.DECLARTAION = FROMCUST.DECLARTAION AND TOCUST.TRANSPORTATION = FROMCUST.TRANSPORTATION AND TOCUST.INSURANCE = FROMCUST.INSURANCE AND TOCUST.SERVICE_TAX = TOCUST.SERVICE_TAX ORDER BY PAYMENTTERM, RATE desc, FROMCUST.UPDATED_TIME; But the same does not return the rows in the sequence as expected by the select order by. The same query when executed separately without the cursor the order is correct. Thanks, Manoj How to repeat: create a table offers_all as: delimiter $$ CREATE TABLE `offers_all` ( `OFFER_ID` int(11) unsigned zerofill NOT NULL AUTO_INCREMENT, `FROM_CUSTOMER` varchar(45) DEFAULT NULL, `TO_CUSTOMER` varchar(45) DEFAULT NULL, `STATUS` varchar(45) DEFAULT NULL, `OFFER_MANDI` varchar(45) DEFAULT NULL, `OFFER_TYPE` varchar(2) DEFAULT NULL, `OFFER_PRODUCT` varchar(45) DEFAULT NULL, `PRODUCT_GRADE` varchar(45) DEFAULT NULL, `QUANTITY` int(11) DEFAULT '0', `QUANTITY_OPEN` int(11) DEFAULT '0', `QUANTITY_EXECUTED` int(11) DEFAULT '0', `QUANTITY_CANCELED` int(11) DEFAULT '0', `QUANTITY_EXPIRED` int(11) DEFAULT '0', `ADVANCE_RATE` double DEFAULT '0', `NEXTDAY_RATE` double DEFAULT '0', `REGULAR_RATE` double DEFAULT '0', `LOADING` varchar(2) DEFAULT NULL, `EXCISE_DUTY` varchar(2) DEFAULT NULL, `VAT` varchar(2) DEFAULT NULL, `DECLARTAION` varchar(25) DEFAULT NULL, `TRANSPORTATION` varchar(2) DEFAULT NULL, `INSURANCE` varchar(2) DEFAULT NULL, `SERVICE_TAX` varchar(2) DEFAULT NULL, `REVISION_NO` int(11) DEFAULT '0', `CREATE_BY` varchar(45) DEFAULT NULL, `CREATED_TIME` datetime DEFAULT NULL, `UPDATE_BY` varchar(45) DEFAULT NULL, `UPDATED_TIME` datetime DEFAULT NULL, `MATCH_ID` int(11) DEFAULT NULL, PRIMARY KEY (`OFFER_ID`), KEY `OFFER_INDEX` (`OFFER_TYPE`,`OFFER_PRODUCT`,`OFFER_MANDI`), KEY `OFFER_TO_CUST_FK` (`TO_CUSTOMER`), KEY `OFFER_FROM_CUST_FK` (`FROM_CUSTOMER`), CONSTRAINT `OFFER_FROM_CUST_FK` FOREIGN KEY (`FROM_CUSTOMER`) REFERENCES `customers_all` (`SYMBOL`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `OFFER_TO_CUST_FK` FOREIGN KEY (`TO_CUSTOMER`) REFERENCES `customers_all` (`SYMBOL`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=latin1$$ Insert Records as below: INSERT INTO `offers_all` (OFFER_ID,FROM_CUSTOMER,TO_CUSTOMER,STATUS,OFFER_MANDI,OFFER_TYPE,OFFER_PRODUCT,PRODUCT_GRADE,QUANTITY,QUANTITY_OPEN,QUANTITY_EXECUTED,QUANTITY_CANCELED,QUANTITY_EXPIRED,ADVANCE_RATE,NEXTDAY_RATE,REGULAR_RATE,LOADING,EXCISE_DUTY,VAT,DECLARTAION,TRANSPORTATION,INSURANCE,SERVICE_TAX,REVISION_NO,CREATE_BY,CREATED_TIME,UPDATE_BY,UPDATED_TIME,MATCH_ID) VALUES (00000000007,'MAHEN',NULL,'O','RPR','S','BILLET','A',40,40,0,0,0,20200,20500,0,'I','E','E','Declaration','E','I','I',27,'test1','2011-05-30 20:53:00','System','2011-06-23 02:08:32',NULL); INSERT INTO `offers_all` (OFFER_ID,FROM_CUSTOMER,TO_CUSTOMER,STATUS,OFFER_MANDI,OFFER_TYPE,OFFER_PRODUCT,PRODUCT_GRADE,QUANTITY,QUANTITY_OPEN,QUANTITY_EXECUTED,QUANTITY_CANCELED,QUANTITY_EXPIRED,ADVANCE_RATE,NEXTDAY_RATE,REGULAR_RATE,LOADING,EXCISE_DUTY,VAT,DECLARTAION,TRANSPORTATION,INSURANCE,SERVICE_TAX,REVISION_NO,CREATE_BY,CREATED_TIME,UPDATE_BY,UPDATED_TIME,MATCH_ID) VALUES (00000000014,'SATYA',NULL,'O','RPR','B','BILLET','A',30,30,0,0,0,20100,20600,0,'I','E','E','Declaration','E','I','I',16,'test','2011-06-05 23:33:04','System','2011-06-23 02:08:32',NULL); INSERT INTO `offers_all` (OFFER_ID,FROM_CUSTOMER,TO_CUSTOMER,STATUS,OFFER_MANDI,OFFER_TYPE,OFFER_PRODUCT,PRODUCT_GRADE,QUANTITY,QUANTITY_OPEN,QUANTITY_EXECUTED,QUANTITY_CANCELED,QUANTITY_EXPIRED,ADVANCE_RATE,NEXTDAY_RATE,REGULAR_RATE,LOADING,EXCISE_DUTY,VAT,DECLARTAION,TRANSPORTATION,INSURANCE,SERVICE_TAX,REVISION_NO,CREATE_BY,CREATED_TIME,UPDATE_BY,UPDATED_TIME,MATCH_ID) VALUES (00000000015,'SATYA',NULL,'O','RPR','B','BILLET','A',20,20,0,0,0,20500,0,0,'I','E','E','Declaration','E','I','I',12,'test','2011-06-06 16:40:16','System','2011-06-23 02:08:32',NULL); And Query the records using the above cursor with Offer_id = '7'. The same will return two records as 14 and 15. The default order is 14, 15. But the order by clause as in the query it must return 15, 14. But the same does not happen, the cursor rather chooses to go with the default order. Suggested fix: The order of the rows returns must be as it should be with the order by clause.