MySQL Bugs: #29765: SQLSetPos w/SQL_DELETE advances dynamic cursor incorrectly

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

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.

Hi Jim,

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

Regards,

Susanne

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.

Test case

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

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.

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.

> 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.

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.

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.

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".

Where does this issue stand at this time? I have not seen any info since August.

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.

Fix committed and will be released in 5.1.1.

A note has been added to the 5.1.1 changelog: 

SQLSetPos with SQL_DELETE advances dynamic cursor incorrectly