You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Ondrej Bouda (Jira)" <ji...@apache.org> on 2021/02/18 16:21:00 UTC
[jira] [Created] (DERBY-7099) DELETE skips a row matching the WHERE
clause (single delete by PK)
Ondrej Bouda created DERBY-7099:
-----------------------------------
Summary: DELETE skips a row matching the WHERE clause (single delete by PK)
Key: DERBY-7099
URL: https://issues.apache.org/jira/browse/DERBY-7099
Project: Derby
Issue Type: Bug
Components: SQL
Affects Versions: 10.15.2.0, 10.14.2.0
Environment: Looks like environment-independent bug - reproduced in multiple environments (prod/test, win/linux, different Java and Derby versions).
Reporter: Ondrej Bouda
Attachments: repro.zip
We came across a bug in Derby when a DELETE by primary key actually does not delete the row (although it exists).
* We found the issue in a delete-reinsert use case when the reinsert was failing with a "duplicate key" error.
* Note: The attached app to reproduce the issue follows this reinsert pattern.
To reproduce the issue, a SELECT (by PK) is required before the DELETE (by PK).
* SELECT => SELECT => DELETE => RE-INSERT => duplicate key error.
* The DELETE works OK if no or just 1 SELECT is executed before.
* The "failed" DELETE correctly reports "0 rows affected".
* Retrying the DELETE makes the delete really happen.
* The behavior is the same both for autocommit and manual commit mode.
* The behavior is 100% reproducible in the attached demo app.
However, it is not reproducible from DBeaver (using the same queries).
Notes regarding the attached database + demo app to reproduce the issue:
* The app takes 3 arguments: path to the database (string), how many times to execute the initial SELECT (int) and an optional "retry" string to retry the DELETE statement.
* Use "test_app.bat" => there are several execution scenarios, uncomment the one you intend to test.
* With 0-1 SELECTs, the demo app works without any issues.
* With 2+ SELECTs, the demo app fails on "duplicate key" when trying to reinsert the deleted row. The app reports that the DELETE affected 0 rows.
* In the "retry" mode, you can see that 2nd DELETE is successful (even for 3+ SELECTs).
Further notes
* The issue persists even after a successful delete-reinsert.
* Check for corruption (SYSCS_CHECK_TABLE) indicates that the table is healthy.
* Full rebuild of the affected table (SYSCS_COMPRESS_TABLE) resolves the issue but - there is no way to tell whether a tables needs rebuild to avoid this kind of issue...
--
This message was sent by Atlassian Jira
(v8.3.4#803005)