[
https://issues.apache.org/jira/browse/DERBY-4032?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12668884#action_12668884
]
Chip Hartney commented on DERBY-4032:
-------------------------------------
This makes sense to me as one of the processes the app undergoes is a reload of
the Product data. That process includes a DELETE of most records in the table
followed by a series of INSERTs to rebuild the list of products. This is all
done within a single UOW. Perhaps, in this process, the HG15497 record is
deleted from the table, but only marked as deleted in the index. Then,
perhaps, the following INSERT may not have cleaned up the record marked as
deleted...perhaps because it is on a page boundary.
I have seen similar page boundary quirks in other DBMSs (that I was more
familiar with) in the past. That would explain why most rows are not so
affected.
I also want to mention that I have seen what I believe is this same behavior
twice before...2-3 months ago...on another table using the same app and the
same version of Derby. Again, during the reload process, many records are
deleted from a table, then a complex query is run that joins the remaining
records to a master list to identify those that need further processing. The
JOIN returned at least one of the records that had just been deleted! It was
repeatable. I did not investigate as deeply because it occurred only in my
development environment. I simply recreated the DB and all was well again. I
now believe this was the same problem.
Given that the problem is that there is an undesired row in the index, we can
fix the DB by rebuilding the index. I just did so, successfully correcting the
problem, as follows...
ij> select "Num" from "APP"."Product" where "Num" = 'HG15497';
Num
----------------
0 rows selected
ij> ALTER TABLE "APP"."Product" DROP CONSTRAINT "Product_AK_Num";
0 rows inserted/updated/deleted
ij> select "Num" from "APP"."Product" where "Num" = 'HG15497';
Num
----------------
HG15497
1 row selected
ij> ALTER TABLE "APP"."Product" ADD CONSTRAINT "Product_AK_Num" UNIQUE ("Num");
0 rows inserted/updated/deleted
ij> select "Num" from "APP"."Product" where "Num" = 'HG15497';
Num
----------------
HG15497
1 row selected
I will use this as the patch if the problem arises again and will look for a
new version of Derby with a resolution to this problem to prevent the problem
from arising to start with.
> Record not found in some SQL
> ----------------------------
>
> Key: DERBY-4032
> URL: https://issues.apache.org/jira/browse/DERBY-4032
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.3.2.1
> Environment: Windows XP
> Reporter: Chip Hartney
> Attachments: OrderEntryDB-base.zip, OrderEntryDB-seg0-part1.zip,
> OrderEntryDB-seg0-part2.zip, OrderEntryDB-seg0-part3.zip,
> OrderEntryDB-seg0-part4.zip, OrderEntryDB-seg0-part5.zip,
> OrderEntryDB-seg0-part6.zip, OrderEntryDB-seg0-part7.zip
>
>
> Per discussion at
> http://www.nabble.com/Record-not-found-in-some-SQL---Bug--td21700110.html...
> I have a "Product" table with a "Num" column that contains a record that is
> only accessible by some SQL and not others. I have tested this by JDBC
> access from my Java app as well was from IJ directly.
> ij> select "Num", length("Num") as "Len" from app."Product" where "Num" like
> 'HG1549%';
> Num |Len
> ----------------------------
> HG15490 |7
> HG15493 |7
> HG15497 |7 <== Found as expected
> HG15499 |7
> 4 rows selected
> ij> select "Num" from app."Product" where "Num" = 'HG15490';
> Num
> ----------------
> HG15490 <== Found as expected
> 1 row selected
> ij> select "Num" from app."Product" where "Num" = 'HG15493';
> Num
> ----------------
> HG15493 <== Found as expected
> 1 row selected
> ij> select "Num" from app."Product" where "Num" = 'HG15499';
> Num
> ----------------
> HG15499 <== Found as expected
> 1 row selected
> ij> select "Num" from app."Product" where "Num" = 'HG15497';
> Num
> ----------------
> 0 rows selected <== Not found!!!
> What could possibly hide the 'HG15497' record from the last SELECT?
> And it's not just a matter of equality versus inequality...as the following
> SQL does return the record:
> SELECT I."STYLE" FROM TEMP."ZJVINV2" AS I INNER JOIN APP."Product" AS P
> ON I."STYLE" = P."Num" WHERE I."STYLE" = 'HG15497';
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
