[
https://issues.apache.org/jira/browse/DERBY-4032?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12668856#action_12668856
]
Knut Anders Hatlen commented on DERBY-4032:
-------------------------------------------
It turns out that the optimizer overrides I suggested didn't do what I expected
them to do. In particular, constraint=null didn't stop the query from using the
constraint. index=null did, and then the row was returned.
ij> select "Num" from "Product" --DERBY-PROPERTIES index=null
> where "Num" = 'HG15497';
Num
----------------
HG15497
1 row selected
ij> select "Num" from "Product" --DERBY-PROPERTIES constraint="Product_AK_Num"
> where "Num" = 'HG15497';
Num
----------------
0 rows selected
It looks like that there are two rows with that value in the index. One of them
is marked as deleted. When you scan a range in the index, both of the rows are
found, and the one that's not deleted is returned. If you fetch the row by
exact match, the B-tree scan happens to see the deleted one first. Since it is
a unique index, and the row it found first was deleted, it assumes that there
is no match and stops searching, and it never sees the row that isn't deleted.
As I understand the B-tree code, there should never be duplicates in a unique
index, even if some of the rows are marked as deleted. So the question is how
did the duplicates end up in the unique index.
It seems like one of the matching rows (the one that's not deleted) is the last
row on one index page, and the deleted one is the first one on another page. So
my guess is that there is a bug in the code that inserts rows in the index when
there's a matching, deleted row on a page boundary.
> 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.
