Bug #29765 SQLSetPos w/SQL_DELETE advances dynamic cursor incorrectly
Submitted: 12 Jul 2007 16:41 Modified: 14 Dec 2007 15:59
Reporter: Jim Dickenson Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / ODBC Severity:S2 (Serious)
Version:3.51.16 OS:Linux
Assigned to: Jess Balint
Tags: ODBC, SQLSetPos

[12 Jul 2007 16:41] Jim Dickenson
Description:
I have an application available here:

http://www.dickensons.us/odbctest.c

that shows a problem where if one either deletes or updates a row so it does not meet the where clause when one tries to get the "next" row you actually get the row after the one you would expect to get.

How to repeat:
Compile and run the application

Suggested fix:
Fix the driver so that it returns the "row" expected.
[25 Jul 2007 17:56] Susanne Ebrecht
Hi Jim,

thanks for sending a bug report.
Which compiler do you use?

Regards,

Susanne
[25 Jul 2007 22:40] Jim Dickenson
gcc --version
powerpc-apple-darwin8-gcc-4.0.1 (GCC) 4.0.1 (Apple Computer, Inc. build 5367)
Copyright (C) 2005 Free Software Foundation, Inc.
This is free software; see the source for copying conditions.  There is NO
warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
[28 Jul 2007 23:15] Jim Winstead
Test case

Attachment: odbctest.c (, text), 28.02 KiB.

[6 Aug 2007 15:08] Bogdan Degtyariov
Jim,

Your test case worked flawlessly with MyODBC 3.51.16 and 3.51.17 on my machine. It deleted the record "0003" and updated "0007" to "0050". Isn't it the expected behavior? Taking into account that only two statements (delete and update) were executed I'm failing to see the problem in the program logic and mysql log file. Could you provide the details what results are incorrect?
Thanks.
[6 Aug 2007 19:20] Jim Dickenson
There are records 0001 through 0010 in the database. When I delete a record I would expect the next read to get the next record. In my case when I delete 0003 and do another read I get 0005 and not 0004. Likewsie when I update a case I would expect the next read to read the record after the one updated. I update 0007 and when I do a read I get 0009 and not 0008.

Here is the output of my run of my test application:

Item field1=4 bytes='0001'
Item field2=19 bytes='aaaaaaaaaaaaaaaaaaa'
!!!Unchanged!!!

Item field1=4 bytes='0002'
Item field2=22 bytes='bbbbbbbbbbbbbbbbbbbbbb'
!!!Unchanged!!!

Item field1=4 bytes='0003'
Item field2=21 bytes='ccccccccccccccccccccc'
!!!Deleted!!!

Item field1=4 bytes='0005'
Item field2=22 bytes='eeeeeeeeeeeeeeeeeeeeee'
!!!Unchanged!!!

Item field1=4 bytes='0006'
Item field2=23 bytes='fffffffffffffffffffffff'
!!!Unchanged!!!

Item field1=4 bytes='0007'
Item field2=21 bytes='ggggggggggggggggggggg'
!!!Updated!!!

Item field1=4 bytes='0009'
Item field2=21 bytes='iiiiiiiiiiiiiiiiiiiii'
!!!Unchanged!!!

Item field1=4 bytes='0010'
Item field2=24 bytes='jjjjjjjjjjjjjjjjjjjjjjjj'
!!!Unchanged!!!

mysql --version
mysql  Ver 14.12 Distrib 5.0.22, for apple-darwin8.6.0 (powerpc) using readline 5.0

mysql-connector-odbc-3.51.12

I tested this on another system with a newer mysql connector version and had the same problem.

This is what I have installed on my Mac that I just tested this on.
[19 Aug 2007 9:32] Tonci Grgin
> There are records 0001 through 0010 in the database. When I delete a record I would expect the next read to get the next record. In my case when I delete 0003 and do another read I get 0005 and not 0004.

Jim, this is not as easy question as it looks... Let me ask you one question, what is the *current* record, ie. one that will accept your next command, after you delete record 003? What if I say DELETE + DELETE? What if I say DELETE + EDIT? In my opinion there can not be an "empty" row as a place holder just to satisfy your expectation. Pulling next row in ORDER BY up after deletion is no bug in my opinion. Can you point me to some ODBC specs stating differently so I can recheck?

> Likewsie when I update a case I would expect the next read to read the record after the one updated. I update 0007 and when I do a read I get 0009 and not 0008.

Same as above. If updated record had to change his place in matrix some pulling up *is* necessary. What if updated record goes out of scope of query? What would be *current* row then? Would you expect updated row to remain editable / reachable inside original result set? I don't.

The workaround for described behavior existed once in Borland/Corel Paradox. It was called "OnFlyAway" event but I haven't seen it implemented anywhere else.
[20 Aug 2007 14:21] Jim Dickenson
Per Microsoft ODBC 3.0 Programmer's Reference, Volume 1, ISBN 1-57231-516-4 page 225

Talking about SQLSetPos delete operation:
"After SQLSetPos returns, the deleted row is the current row, and its status is SQL_ROW_DELETED."

Same reference page 223

Talking about SQLSetPos update operation:
"After SQLSetPos returns, the current row is set to the updated row."

In addition, for whatever it is worth, PostgreSQL works as described above.
[20 Aug 2007 14:26] Jim Dickenson
To be clear PostgreSQL works as I expect the software to work. Running my application I see all rows. None are skipped due to SQLSetPos changing what the "current" record is.
[20 Aug 2007 16:53] Tonci Grgin
Jim, thanks for reference provided. I will raise this question where appropriate as soon as possible.

Verified as per "Microsoft ODBC 3.0 Programmer's Reference, Volume 1, ISBN 1-57231-516-4 page 225".
[13 Oct 2007 15:08] Jim Dickenson
Where does this issue stand at this time? I have not seen any info since August.
[17 Oct 2007 12:03] Tonci Grgin
Jim, the report has been discussed, priority raised but I can't give you any ETA as there are other, more pressing things for us to take care of.
[12 Dec 2007 19:54] Jess Balint
Fix committed and will be released in 5.1.1.
[14 Dec 2007 15:59] MC Brown
A note has been added to the 5.1.1 changelog: 

SQLSetPos with SQL_DELETE advances dynamic cursor incorrectly