SQLite 3.47.0 Bug: IN Operator Fails with Joined Tables on UNIQUE Columns

---

Issue Overview: IN Operator Behavior with Joined Tables on UNIQUE Columns

The core issue revolves around the behavior of the IN operator in SQLite when used with joined tables on columns that have the UNIQUE constraint. Specifically, the problem manifests when comparing tuples of columns from two tables using the IN operator. The issue was introduced in SQLite version 3.47.0 and affects queries that involve joined tables with UNIQUE constraints on the columns being compared.

To illustrate the problem, consider the following schema and queries:

CREATE TABLE t1 (a int UNIQUE);
CREATE TABLE t2 (b int UNIQUE);
INSERT INTO t1 VALUES (1);
INSERT INTO t2 VALUES (1), (2);

The first query uses the equality operator = to compare tuples:

SELECT t1.a, t2.b FROM t1, t2 WHERE (t1.a, t2.b) = (1, 1);

This query correctly returns the expected result:

┌───┬───┐
│ a │ b │
├───┼───┤
│ 1 │ 1 │
└───┴───┘

Similarly, the second query uses the IN operator to compare tuples:

SELECT t1.a, t2.b FROM t1, t2 WHERE (t1.a, t2.b) IN ((1, 1));

This query also returns the expected result:

┌───┬───┐
│ a │ b │
├───┼───┤
│ 1 │ 1 │
└───┴───┘

However, the third query, which uses the equality operator = to compare tuples with different values, also works as expected:

SELECT t1.a, t2.b FROM t1, t2 WHERE (t1.a, t2.b) = (1, 2);

This query returns:

┌───┬───┐
│ a │ b │
├───┼───┤
│ 1 │ 2 │
└───┴───┘

The issue arises with the fourth query, which uses the IN operator to compare tuples with different values:

SELECT t1.a, t2.b FROM t1, t2 WHERE (t1.a, t2.b) IN ((1, 2));

In SQLite 3.47.0, this query returns no results, whereas in SQLite 3.46.1, it correctly returns the tuple (1, 2). This discrepancy indicates a regression in the behavior of the IN operator when used with joined tables on UNIQUE columns.

---

Possible Causes: Changes in Tuple Comparison Logic and UNIQUE Constraint Handling

The root cause of this issue lies in changes made to the tuple comparison logic in SQLite 3.47.0, particularly when the IN operator is used with joined tables on columns that have the UNIQUE constraint. The following factors contribute to the problem:

1. Tuple Comparison Logic Changes: The IN operator in SQLite is designed to compare tuples of values. In SQLite 3.47.0, a change was introduced that altered how tuples are compared when the UNIQUE constraint is present on the columns being compared. This change inadvertently broke the expected behavior of the IN operator in certain cases.

2. Interaction with UNIQUE Constraints: The UNIQUE constraint on columns a and b in tables t1 and t2 respectively plays a role in how the IN operator processes the comparison. The constraint ensures that each value in the column is unique, which affects the internal logic used by SQLite to evaluate the IN operator. The change in SQLite 3.47.0 introduced a bug where the UNIQUE constraint interfered with the correct evaluation of the IN operator for joined tables.

3. Regression from Previous Versions: The issue is a regression from SQLite 3.46.1, where the IN operator worked correctly with joined tables on UNIQUE columns. The regression was introduced in SQLite 3.47.0 due to changes in the internal logic for handling tuple comparisons.

4. Comparison with PostgreSQL: The behavior of the IN operator in SQLite 3.47.0 was inconsistent with that of PostgreSQL, which correctly handles tuple comparisons with the IN operator. This inconsistency further confirms that the issue is a bug in SQLite 3.47.0.

---

Troubleshooting Steps, Solutions & Fixes: Resolving the IN Operator Bug in SQLite 3.47.0

To address the issue with the IN operator in SQLite 3.47.0, the following steps can be taken:

1. Verify the Issue: Before attempting any fixes, it is important to verify that the issue is indeed present in your SQLite installation. You can do this by running the test queries provided in the issue overview. If the last query returns no results, then the issue is present.

2. Upgrade to the Latest Version: The issue has been fixed in the latest versions of SQLite. Specifically, the fix was implemented in the following check-ins:

   • Trunk: check-in 19d1bede5654bcfa
   • Branch-3.47: check-in 16d46e116086948a

   To resolve the issue, you should upgrade to a version of SQLite that includes these fixes. You can either:

   • Download the latest trunk build from the SQLite website.
   • Wait for the next official release of SQLite that includes the fix.

3. Apply the Fix Manually: If upgrading is not an option, you can manually apply the fix by modifying the SQLite source code. The fix involves reverting the changes that introduced the bug in the tuple comparison logic. You can find the specific changes in the check-ins mentioned above.

4. Workaround Using Alternative Queries: If upgrading or applying the fix manually is not feasible, you can use alternative queries to achieve the same result. For example, instead of using the IN operator, you can use multiple OR conditions to compare the tuples:

   SELECT t1.a, t2.b FROM t1, t2 WHERE (t1.a = 1 AND t2.b = 2);
   

   This query will return the same result as the original query that used the IN operator.

5. Test the Fix: After applying the fix or upgrading to a version that includes the fix, you should test the queries again to ensure that the issue has been resolved. The last query should now return the expected result:

   SELECT t1.a, t2.b FROM t1, t2 WHERE (t1.a, t2.b) IN ((1, 2));
   

   This query should now return:

   ┌───┬───┐
   │ a │ b │
   ├───┼───┤
   │ 1 │ 2 │
   └───┴───┘
   

6. Monitor for Future Updates: While the issue has been fixed, it is important to monitor future updates to SQLite to ensure that no new regressions are introduced. You can do this by following the SQLite mailing list or checking the SQLite website for release notes.

7. Report Additional Issues: If you encounter any additional issues or unexpected behavior after applying the fix, you should report them to the SQLite development team. Providing detailed test cases and steps to reproduce the issue will help the team address any remaining problems.

---

By following these steps, you can resolve the issue with the IN operator in SQLite 3.47.0 and ensure that your queries involving joined tables on UNIQUE columns work as expected. The fix has been thoroughly tested and confirmed to resolve the issue, restoring the correct behavior of the IN operator in SQLite.