Bug with "UPDATE" statement using subselect

CrateDB
1

So, this query:

UPDATE table
SET column = '123'
WHERE id = 2
AND NOT EXISTS (
    SELECT 1
    FROM table
    WHERE column = '123'
);

Should NOT work if there is a row with column value 123 in the table already and YET it works and changes the row.
Huge problem for me so fix it as soon as possible.

2

Hi @Jiren,

Thanks for reporting. Would you mind opening an issue on GitHub (Issues · crate/crate · GitHub), so our development team can interact with you directly?

Thanks.

4

What is there to interact with? I literally gave you the code and described the contradiction in its function. Fix it.

5

Dear @Jiren

First of all, welcome to the CrateDB Community :wave:

May I remind you to take a look at FAQ - CrateDB Community.

While Crate.io is a company, the community is a place for a civilised discussion around CrateDB for everyone. Also CrateDB itself is open source software under Apache License v2.0. Which implies that we are open to feedback and contributions to make it a better software, however it also specifically states, that we provide the software

“AS IS”, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND

Huge problem for me so fix it as soon as possible.

While we typically don’t discriminate on bugs in any way, if you need a speedy bug fix for a specific problem, I’d typically recommend to get in a commercial agreement.

Unfortunately you also left out one very important detail in your example, specifically that you defined id as PRIMARY KEY. Considering that - and while I see this as a bug - I would not consider this “HUGE”, as with WHERE id = 2 you specifically select on one record, and one record only.

6

To elaborate more on the request for raising a GitHub issue: On opening a new issue, you will see a form with several input fields. This structure helps to process issues in a standardized way, such as having complete steps to reproduce. As @proddata already mentioned, your issue depends on the details of your CREATE TABLE statement (primary key definition), which wasn’t shared.

Additionally, you will receive direct updates from the development team when a fix is available, as well as potentially additional information, like applicable workarounds. I find it a fair ask to raise a GitHub issue in return for that.

1 Like
7

Also, FWIW, while waiting for a fix you may want to try the following workaround which seems to work for me on 5.5.2:

INSERT INTO f2 (id,column1)
SELECT id
	,CASE WHEN NOT EXISTS (
				SELECT 1
				FROM f2
				WHERE column1 = '123'
				)
			THEN '123'
		ELSE column1
		END
FROM f2
WHERE id = 2 
ON CONFLICT(id) DO UPDATE SET column1 = excluded.column1;
8

To ensure this is properly tracked I went ahead and raised it on GH → UPDATE statement not honouring WHERE NOT EXISTS filter when used in conjunction with a filter on PK · Issue #15282 · crate/crate · GitHub

1 Like
9

Hi again @Jiren , just a quick message to let you know that a fix for this issue has now been committed
Fix handling of query conditions in delete/update with WHERE doc-keys by mfussenegger · Pull Request #15293 · crate/crate (github.com)

1 Like
10

Sure, but if I get the last update 5.5.2, will this bug be fixed or you are only planning to release the fixed version?

11

Hi,
This will be available on the next release which is coming soon, but in the meanwhile you can see this working already on the latest nightly build (docker image: crate/crate:nightly-5.6.0-2024-01-09-00-02)