Bug report: UPDATE/DELETE statement omits records when a row-value, the IN operator, and a subquery are used

(1) By Ján Hric (janhricbs) on 2025-04-15 17:40:18 [source]

Brief description:

Under certain circumstances the UPDATE or DELETE statement does not process all the matching records when the WHERE clause includes the IN operator with a row value on the left-hand side and a subquery on the right-hand side.


Requiered conditions:

The table has:
- an INTEGER PRIMARY KEY column (rowid alias),
- an index or a UNIQUE contraint where the rowid alias is the second column,
- a TEXT/BLOB column,
- at least one other column.
The IN operator in the WHERE clause of the UPDATE/DELETE statement:
- The left-hand side is a row value consisting of columns indexed by the additional index.
- The right-hand side is a subquery.
- The additional index is used for matching.


Example code:

CREATE TABLE items (
  Id INTEGER PRIMARY KEY  /* rowid alias */,
  Item INTEGER  /* any type */,
  Test TEXT  /* TEXT or BLOB */,
  Filler  /* any type */,
  UNIQUE (Item, Id)  /* rowid alias is second */
);

INSERT INTO items (Id, Item)
  VALUES (1, 2), (2, 3), (3, 3), (4, 4);

UPDATE items SET Test = 'ok'
  WHERE (Id, Item) IN (SELECT Id, Item FROM items);

SELECT Id, Item, Test FROM items;
  /* Only rows where Id and Item are equal have been updated. */

(2) By jchd (jchd18) on 2025-04-15 18:21:24 in reply to 1 [link] [source]

Confirmed here.

It affects select as well:

select Id, Item from items WHERE (Id, Item) IN (SELECT Id, Item FROM items);

(3) By Richard Hipp (drh) on 2025-04-15 18:32:26 in reply to 2 [link] [source]

The bug has been in the code for 7 years, 4 months, and 28 days, and since version 3.22.0. We are working on it now.

I the meantime, a work-around is to put a "+" before the "Item" term on the left-hand side of the IN operator:

  ... WHERE (Id, +Item) IN (SELECT Id, Item FROM items);
         --------^--  Added "+"

(4) By Richard Hipp (drh) on 2025-04-15 19:12:42 in reply to 1 [link] [source]

I'm still working on the problem. I agree that it is something that needs fixing. But a better solution for you, right now, is to not use a PRIMARY KEY column as a term in a UNIQUE constraint. Doing so is redundant, since the PRIMARY KEY is already UNIQUE.

A better schema design would be something like this:

CREATE TABLE items (
  Id INTEGER  /* rowid alias */,
  Item INTEGER  /* any type */,
  Test TEXT  /* TEXT or BLOB */,
  Filler,  /* any type */
  PRIMARY KEY(Item, Id),
  UNIQUE(Id)
) WITHOUT ROWID;

If you design the table that way, everything works. And, in fact, most people would design the table like I suggest, or at least in some way similar, not by adding a UNIQUE constraint where one of the terms is the INTEGER PRIMARY KEY. And the fact that must people would do it this way is probably why this bug has gone undetected for more than seven years.

(5) By Richard Hipp (drh) on 2025-04-15 22:01:35 in reply to 1 [link] [source]

The problem should be fixed now, after check-in 2025-04-15T21:59Z. Please post a follow-up if you discover otherwise.