Since SQLite 3.47.0, I have a problem with the IN operator for joined tables on columns with the UNIQUE constraint.

CREATE TABLE t1 (a int UNIQUE);
CREATE TABLE t2 (b int UNIQUE);
INSERT INTO t1 VALUES (1);
INSERT INTO t2 VALUES (1), (2);
SELECT t1.a, t2.b FROM t1, t2 WHERE (t1.a, t2.b) = (1, 1);
┌───┬───┐
│ a │ b │
├───┼───┤
│ 1 │ 1 │
└───┴───┘
SELECT t1.a, t2.b FROM t1, t2 WHERE (t1.a, t2.b) IN ((1, 1));
┌───┬───┐
│ a │ b │
├───┼───┤
│ 1 │ 1 │
└───┴───┘
SELECT t1.a, t2.b FROM t1, t2 WHERE (t1.a, t2.b) = (1, 2);
┌───┬───┐
│ a │ b │
├───┼───┤
│ 1 │ 2 │
└───┴───┘
SELECT t1.a, t2.b FROM t1, t2 WHERE (t1.a, t2.b) IN ((1, 2));

As you can see, the last request doesn’t return anything. The exact same SQL works correctly with 3.46.1, the last request gives (1, 2).