[jira] [Updated] (DERBY-6978) Select for ID does not deliver existing Row

["Christian Lutz (JIRA)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/DERBY-6978?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Christian Lutz updated DERBY-6978:
----------------------------------
    Description: 
Hello everybody,

we are currently facing a rather strange problem, using the Derby-DB.

In the attached Database there is one table left, called 'TreeEntry' and for several kinds of querries performed on it Derby claims something different than is actually true.

For example the statement

select count\(*\) from treeentry;

tells, that there would be 1426 lines in the table, but if I select all rows and copy them into an editor I see, that there are actually 1492 lines.

Furthermore, the following statement actually returns data, which, in my understanding, should never be the case:

select * from treeentry t where id not in (select id from treeentry where id = t.id);

But on the other hand 

select count\(*\) from treeentry t where id not in (select id from treeentry where id = t.id);

tells, that there would be zero such rows.

In the result of the beforementioned select I see, that a line exists, that has the ID 111664.
If I then explicitly select for this row (which I've just seen existing in the previous select) via

select * from treeentry where id = 111664;

nothing is returned.

I tried updating the statistics for this table via

CALL SYSCS_UTIL.SYSCS_UPDATE_STATISTICS('APP', 'TREEENTRY', null)

but this call does not terminate.
But here I have to remark, that there were also several other tables in the database with the same phenomenon existing, where this call actually terminated, even though it didn't change anything about the problem.

The derby version, with which this problem appeared was 10.12.1.1
But unfortunately, I have absolutely no idea of how to reproduce it.
The only thing I probably can say, that has been done before this issue appeared, was, that the database was copied from one system to another. I don't expect any kinds of incompatibilities there, as, after we discovered this issue, we did the same with a backup of the database in the same way and the issue did not reappear.

I would be very happy, if someone could give me some kind of hint about whether this is a known and already fixed problem, what we could maybe do to avoid this in the future or similar.

If you need any further information from me I'm glad to deliver this still.
Thank you already for your help.

  was:
Hello everybody,

we are currently facing a rather strange problem, using the Derby-DB.

In the attached Database there is one table left, called 'TreeEntry' and for several kinds of querries performed on it Derby claims something different than is actually true.

For example the statement

select count(*) from treeentry;

tells, that there would be 1426 lines in the table, but if I select all rows and copy them into an editor I see, that there are actually 1492 lines.

Furthermore, the following statement actually returns data, which, in my understanding, should never be the case:

select * from treeentry t where id not in (select id from treeentry where id = t.id);

But on the other hand 

select count(*) from treeentry t where id not in (select id from treeentry where id = t.id);

tells, that there would be zero such rows.

In the result of the beforementioned select I see, that a line exists, that has the ID 111664.
If I then explicitly select for this row (which I've just seen existing in the previous select) via

select * from treeentry where id = 111664;

nothing is returned.

I tried updating the statistics for this table via

CALL SYSCS_UTIL.SYSCS_UPDATE_STATISTICS('APP', 'TREEENTRY', null)

but this call does not terminate.
But here I have to remark, that there were also several other tables in the database with the same phenomenon existing, where this call actually terminated, even though it didn't change anything about the problem.

The derby version, with which this problem appeared was 10.12.1.1
But unfortunately, I have absolutely no idea of how to reproduce it.
The only thing I probably can say, that has been done before this issue appeared, was, that the database was copied from one system to another. I don't expect any kinds of incompatibilities there, as, after we discovered this issue, we did the same with a backup of the database in the same way and the issue did not reappear.

I would be very happy, if someone could give me some kind of hint about whether this is a known and already fixed problem, what we could maybe do to avoid this in the future or similar.

If you need any further information from me I'm glad to deliver this still.
Thank you already for your help.


> Select for ID does not deliver existing Row
> -------------------------------------------
>
>                 Key: DERBY-6978
>                 URL: https://issues.apache.org/jira/browse/DERBY-6978
>             Project: Derby
>          Issue Type: Bug
>            Reporter: Christian Lutz
>         Attachments: 20172711_derbydb_bugreport.7z
>
>
> Hello everybody,
> we are currently facing a rather strange problem, using the Derby-DB.
> In the attached Database there is one table left, called 'TreeEntry' and for several kinds of querries performed on it Derby claims something different than is actually true.
> For example the statement
> select count\(*\) from treeentry;
> tells, that there would be 1426 lines in the table, but if I select all rows and copy them into an editor I see, that there are actually 1492 lines.
> Furthermore, the following statement actually returns data, which, in my understanding, should never be the case:
> select * from treeentry t where id not in (select id from treeentry where id = t.id);
> But on the other hand 
> select count\(*\) from treeentry t where id not in (select id from treeentry where id = t.id);
> tells, that there would be zero such rows.
> In the result of the beforementioned select I see, that a line exists, that has the ID 111664.
> If I then explicitly select for this row (which I've just seen existing in the previous select) via
> select * from treeentry where id = 111664;
> nothing is returned.
> I tried updating the statistics for this table via
> CALL SYSCS_UTIL.SYSCS_UPDATE_STATISTICS('APP', 'TREEENTRY', null)
> but this call does not terminate.
> But here I have to remark, that there were also several other tables in the database with the same phenomenon existing, where this call actually terminated, even though it didn't change anything about the problem.
> The derby version, with which this problem appeared was 10.12.1.1
> But unfortunately, I have absolutely no idea of how to reproduce it.
> The only thing I probably can say, that has been done before this issue appeared, was, that the database was copied from one system to another. I don't expect any kinds of incompatibilities there, as, after we discovered this issue, we did the same with a backup of the database in the same way and the issue did not reappear.
> I would be very happy, if someone could give me some kind of hint about whether this is a known and already fixed problem, what we could maybe do to avoid this in the future or similar.
> If you need any further information from me I'm glad to deliver this still.
> Thank you already for your help.



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)