Bug #28016 Select Query has missing rows when PK? not included in selection
Submitted: 22 Apr 2007 11:41 Modified: 27 Jun 2007 19:15
Reporter: Louis Breda van Email Updates:
Status: Can't repeat Impact on me:
None 
Category:Connector / ODBC Severity:S2 (Serious)
Version:MyODBC 3.51.14 OS:Windows (VISTA64)
Assigned to: CPU Architecture:Any
Tags: ODBC

[22 Apr 2007 11:41] Louis Breda van
Description:
Hello,

I discovered that rows where "missing" when doing a select on a large table, containing a selection of fields.

The RS is sorted based on part of the selected fields.
A couple of rows can have equal values within the selected fields. 
One or more of the selected fields kan be NULL. 

The behavoir I noticed is that either
1) rows are returned which are readable, but not updatable or
2) rows only seems to be returned, and are ofcourse not updatable
One thing is for sure, the "missing" rows do exist in the DB

I can not disciminate between situation 1) and 2) since the selected fields in the rows can be equal, and from reading that field I can not tell in it is row instance 1/2/3 etc. 
However since I do not get an error when reading a RS-field, 2) is more likely

Trying to identify the uniek row related to the problem, I did also select the not needed PK. That however took the problem away :>

Note that I had to modify a couple of selection querys to workaround this problem.

Sincerely,

Louis

     

not that those querys used to work when using another DB-type (ms-access) 

How to repeat:
Create a large table (x 100K rows) mytable containing fieldsinstances which are partly filled and parltly null  

SQL_Select = "SELECT mytable.field1, mytable.field2, mytable.field3, "mytable.field4  " + _
"FROM mytable " + _
"WHERE mytable.field2 = 'ape' " + _
"ORDER BY mytable.field3;"

RS.Open SQL_Select, cn, adOpenDynamic, adLockOptimistic

simplified:

while RS.EOF = false

   RS.movenext
   
   testvar = RS!fieldx  'that is probably OK
   check conditions
       
   RS!field = xyz
 
   RS.update <== sometimes!! failes! there is no record to update
             look that way at least
     
wend

RS.Close
[16 May 2007 8:43] Tonci Grgin
Louis, this should be connected to your other report about SQSetPos and Jim's report on malformed WHERE... Can you please attach relevant part of general query log from server so that I can see what actually got to it. Btw, I doubt it's Vista and/or x64 related.
[16 Jun 2007 23: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".
[17 Jun 2007 22:02] Tonci Grgin
Louis, what's with my feedback?
[18 Jun 2007 4:52] Louis Breda van
Tonci,

I have been very busy. I simply did not have time for trying to port the DB towards mysql.

I have the intention however, to do some tests with 5119 and the most recent ODBC-build as soon as I can find some time. Hopefully in the comming week.

Sincerely,

Louis
[18 Jun 2007 7:01] Tonci Grgin
Louis, no problem. It was just unlike you to leave report to expire. Can you please test with 3.51.16 as we made numerous changes to this release:
http://dev.mysql.com/get/Downloads/MyODBC3/mysql-connector-odbc-3.51.16-win32.zip/from/htt...
and come back with results?
[20 Jun 2007 11:01] Tonci Grgin
Louis, I used 10 field subset from 18 field table for this test. Table contains ~200000 rows and is about 70MB on disk (20+MB indexes). All works fine in my test case. What you seeing is related to UPDATE not updating field as it has same value (AR = 0). Try catching the error in rs.Update and move forward or something... The best I found on web is that you should check for whether UPDATE will do actual update or not.

		     13 Query       USE test
		     13 Query       SELECT Rbr, Godina, Tip1, Datum1, Datum3, Konto, Dug, Pot, Status, Opis, Marker FROM bug28016 WHERE Godina = 2006 AND Tip1 = 'IRA' ORDER BY Rbr
070620 11:03:00	     13 Query       UPDATE `test`.`bug28016` SET `Marker`='Z  ' WHERE `Rbr`=172423
070620 11:03:19	     13 Query       UPDATE `test`.`bug28016` SET `Marker`='Z  ' WHERE `Rbr`=172424
070620 11:03:21	     13 Query       UPDATE `test`.`bug28016` SET `Marker`='Z  ' WHERE `Rbr`=172425
070620 11:03:31	     13 Query       UPDATE `test`.`bug28016` SET `Marker`='Z  ' WHERE `Rbr`=172426
070620 11:03:33	     13 Query       UPDATE `test`.`bug28016` SET `Marker`='Z  ' WHERE `Rbr`=172427
		     13 Query       UPDATE `test`.`bug28016` SET `Marker`='Z  ' WHERE `Rbr`=172428
070620 11:03:34	     13 Query       UPDATE `test`.`bug28016` SET `Marker`='Z  ' WHERE `Rbr`=172429
070620 11:03:35	     13 Query       UPDATE `test`.`bug28016` SET `Marker`='Z  ' WHERE `Rbr`=172430
070620 11:03:55	     13 Query       UPDATE `test`.`bug28016` SET `Marker`='Z  ' WHERE `Rbr`=172431
		     13 Query       UPDATE `test`.`bug28016` SET `Marker`='Z  ' WHERE `Rbr`=172432
070620 11:03:56	     13 Query       UPDATE `test`.`bug28016` SET `Marker`='Z  ' WHERE `Rbr`=172433
		     13 Query       UPDATE `test`.`bug28016` SET `Marker`='Z  ' WHERE `Rbr`=172434
070620 11:03:57	     13 Query       UPDATE `test`.`bug28016` SET `Marker`='Z  ' WHERE `Rbr`=172435
		     13 Query       UPDATE `test`.`bug28016` SET `Marker`='Z  ' WHERE `Rbr`=172436
070620 11:03:59	     13 Query       UPDATE `test`.`bug28016` SET `Marker`='Z  ' WHERE `Rbr`=172437
070620 11:04:00	     13 Query       UPDATE `test`.`bug28016` SET `Marker`='Z  ' WHERE `Rbr`=172438
		     13 Query       UPDATE `test`.`bug28016` SET `Marker`='Z  ' WHERE `Rbr`=172439
		     13 Query       UPDATE `test`.`bug28016` SET `Marker`='Z  ' WHERE `Rbr`=172440
070620 11:04:01	     13 Query       UPDATE `test`.`bug28016` SET `Marker`='Z  ' WHERE `Rbr`=172441
070620 11:04:07	     13 Query       UPDATE `test`.`bug28016` SET `Marker`='Z  ' WHERE `Rbr`=172442
070620 11:04:11	     13 Query       UPDATE `test`.`bug28016` SET `Marker`='Z  ' WHERE `Rbr`=172443
070620 11:04:16	      2 Query       show processlist
070620 11:06:27	      2 Query       select * from bug28016 WHERE `Rbr`=172432
070620 11:07:40	      2 Query       SELECT dnevnik.Rbr
FROM solusd.dnevnik
WHERE dnevnik.Godina = 2006
AND dnevnik.Tip1 = "IRA"
AND (Datum3 IS NULL OR Datum3 = '0000-00-00')
ORDER BY dnevnik.Rbr
070620 11:08:02	     14 Connect     root@localhost on test
		     14 Query       SET SQL_AUTO_IS_NULL=0
		     14 Query       select database()
070620 11:08:03	     14 Query       SELECT @@tx_isolation
		     14 Query       USE test
		     14 Query       SELECT Rbr, Godina, Tip1, Datum1, Datum3, Konto, Dug, Pot, Status, Opis, Marker FROM bug28016 WHERE Godina = 2006 AND Tip1 = 'IRA' ORDER BY Rbr
070620 11:08:06	     14 Query       UPDATE `test`.`bug28016` SET `Marker`='Z  ' WHERE `Rbr`=172423 << Already updated! Error
070620 11:08:23	     14 Quit
[20 Jun 2007 11:02] Tonci Grgin
VBS test case using MyODBC 3.51.16GA

Attachment: 28016.vbs (application/octet-stream, text), 1.85 KiB.