db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Chip Hartney (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-4032) Record not found in some SQL
Date Fri, 03 Jul 2009 15:16:51 GMT

    [ 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:
>         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:
= 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.

View raw message