[
https://issues.apache.org/jira/browse/DERBY-4032?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12727004#action_12727004
]
Chip Hartney commented on DERBY-4032:
-------------------------------------
I understand the difficulty in reproduction, but per my earlier entries, this
is reproducible...having occurred a few times for a few of our customers. Not
often, but it has happened more than once. Given the infrequency, I suggest
Knut's idea of a problem handling page boundaries should be checked...if not
done already...maybe by a code review.
Besides, I believe Knut has already pointed out a problem that needs resolution
per his entry on 1/30: "When you scan a range in the index, both of the rows
are found, and the one that's not deleted is returned."
So it appears that the scan logic DOES allow for the existence of duplicate
rows in the index, but the B-tree lookup logic does not. That is an
inconsistency that should be addressed. It seems to me that you have gone part
way...allowing for duplicates in one case, but not the other, and should
therefore change the B-tree logic to accommodate the duplicates also.
If, on the other hand, you decide the scan logic is wrong (and that dups should
never be handled), perhaps it should be changed. But I wouldn't be surprised
if such logic was added to address an inconsistency discovered in the
past...one much like mine. In fact, given that SYSCS_UTIL.SYSCS_CHECK_TABLE
reports such problems, it's clear you guys have found in the past that such
dups do occur. I'm uncomfortable acknowledging that they do occur, and cause
problems when they do, but declaring the cause unresolvable.
Having said all that, be aware that I am no longer working for the client whom
I wrote this app for. Therefore I am unable to provide more information about
the app (code samples, etc). So I realize I will be of little help now and
can't complain if you leave this as Not Reproducible.
> 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: check-table.txt, 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.
