Op.in: [] emits IN (NULL) (SQL UNKNOWN, not FALSE), so Op.not around it returns zero rows instead of all rows #18306

Issue Creation Checklist

• I understand that my issue will be automatically closed if I don't fill in the requested information
• I have read the contribution guidelines

Bug Description

WhereSqlBuilder[Op.in] special-cases an empty array by emitting <left> IN (NULL). The comment above that branch states the intended contract (where-sql-builder.ts#L369-L371):

// NOT IN () does not exist in SQL, so we need to return a condition that is:
// - always false if the operator is IN
// - always true if the operator is NOT IN

IN (NULL) does not satisfy "always false". In SQL three-valued logic x IN (NULL) is equivalent to x = NULL, which is UNKNOWN, not FALSE. UNKNOWN is indistinguishable from FALSE at the top level of a WHERE clause, so the bare case looks correct — but NOT (UNKNOWN) is still UNKNOWN, so negating an empty Op.in produces a predicate that can never be satisfied.

num IN [] is unsatisfiable, so its negation is a tautology and must match every row. Instead it matches none, and no error is raised.

This is the mirror image of #18248, fixed by #18250 (merged 2026-07-13), which replaced the empty Op.notIn fragment with an explicit 1 = 1. That change deliberately left the Op.in branch alone, because #18248's description stated that Op.in: [] already emitted "IN (NULL), the correct always-false identity for OR" — which is the assumption this report corrects.

It is reachable without ever typing Op.in, since an array value is inferred as Op.in: { [Op.not]: { num: someEmptyArray } } is the natural shape of a dynamically-built exclusion filter that happens to be empty. The same predicate inside Model.update() / Model.destroy() silently becomes a no-op instead of affecting every row.

Reproducible Example

Here is the link to the SSCCE for this issue: not applicable — the SSCCE is pasted inline below, per the template's note that pasted source is accepted. It is fully self-contained, uses SQLite only, contacts no external service, and needs no TypeScript or decorator configuration.

Install and run:

npm i @sequelize/core @sequelize/sqlite3
node sscce-empty-in.mjs

import { DataTypes, Op, Sequelize } from '@sequelize/core';
import { SqliteDialect } from '@sequelize/sqlite3';

const sequelize = new Sequelize({
  dialect: SqliteDialect,
  storage: ':memory:',
  pool: { idle: Number.POSITIVE_INFINITY, max: 1 },
  logging: false,
});

const M = sequelize.define('M', {
  name: DataTypes.STRING,
  num: DataTypes.INTEGER, // nullable on purpose, to cover three-valued logic
});

await sequelize.sync({ force: true });
await M.bulkCreate([{ name: 'a', num: 1 }, { name: 'b', num: 2 }, { name: 'c', num: null }]);

const emptyList = []; // e.g. a dynamically-built filter that happens to be empty
const qg = sequelize.queryGenerator;
const names = async where => (await M.findAll({ where })).map(r => r.name).join(',') || '(none)';

const cases = [
  ['in []         ', { num: { [Op.in]: emptyList } }, '(none)'],
  ['NOT(in [])    ', { [Op.not]: { num: { [Op.in]: emptyList } } }, 'a,b,c'],
  ['notIn []      ', { num: { [Op.notIn]: emptyList } }, 'a,b,c'],
  ['NOT(notIn []) ', { [Op.not]: { num: { [Op.notIn]: emptyList } } }, '(none)'],
  // reachable without ever typing Op.in — an array value is inferred as Op.in
  ['NOT({num: []})', { [Op.not]: { num: emptyList } }, 'a,b,c'],
];

for (const [label, where, expected] of cases) {
  const got = await names(where);
  const sql = qg.whereQuery(where);
  console.log(
    `${label} | ${sql.padEnd(34)} | got ${got.padEnd(7)} | expected ${expected.padEnd(7)} | ${got === expected ? 'ok' : '<-- WRONG'}`,
  );
}

await sequelize.close();

What do you expect to happen?

num IN [] is unsatisfiable, so its negation is a tautology: all rows should be returned. Symmetrically with the already-fixed empty Op.notIn (which emits 1 = 1, so NOT (1 = 1) correctly returns nothing), the empty Op.in fragment should be an unambiguously false expression such as 0 = 1, so that:

• { num: { [Op.in]: [] } } keeps matching no rows, and
• { [Op.not]: { num: { [Op.in]: [] } } } matches all rows.

Note that the already-merged Op.notIn: [] → 1 = 1 behaviour returns the num IS NULL row too, so returning all rows here (including NULLs) is consistent with the semantics already accepted in #18250 rather than a new position.

What is actually happening?

Two of the five cases are wrong. Verbatim output of the SSCCE above, run against main at e8b702733 (@sequelize/core@7.0.0-alpha.48):

in []          | WHERE `num` IN (NULL)              | got (none)  | expected (none)  | ok
NOT(in [])     | WHERE NOT (`num` IN (NULL))        | got (none)  | expected a,b,c   | <-- WRONG
notIn []       | WHERE 1 = 1                        | got a,b,c   | expected a,b,c   | ok
NOT(notIn [])  | WHERE NOT (1 = 1)                  | got (none)  | expected (none)  | ok
NOT({num: []}) | WHERE NOT (`num` IN (NULL))        | got (none)  | expected a,b,c   | <-- WRONG

No exception or stack trace is produced — the query succeeds and silently returns the wrong rows. The attribute-level form { num: { [Op.not]: { [Op.in]: [] } } } is wrong in the same way.

For completeness, Op.or / Op.and composition of a bare empty Op.in is not affected — num IN (NULL) OR name = 'a' returns the correct row — because UNKNOWN and FALSE are indistinguishable at the top level of a WHERE. Only negation breaks.

Root cause. packages/core/src/abstract-dialect/where-sql-builder.ts#L368-L376, inside WhereSqlBuilder[Op.in] (which [Op.notIn] delegates to). The right.length === 0 branch returns '1 = 1' early only when operator === Op.notIn, and otherwise falls through to rightSql = '(NULL)':

if (right.length === 0) {
  // NOT IN () does not exist in SQL, so we need to return a condition that is:
  // - always false if the operator is IN
  // - always true if the operator is NOT IN
  if (operator === Op.notIn) {
    return '1 = 1';
  }

  rightSql = '(NULL)';   // <-- UNKNOWN, not FALSE
}

wrapWithNot then wraps UNKNOWN in NOT (...), which stays UNKNOWN, so the predicate is unsatisfiable.

Suggested fix. Return an explicit always-false fragment for the empty Op.in case, mirroring the 1 = 1 that #18250 introduced for empty Op.notIn:

-        rightSql = '(NULL)';
+        return '0 = 1';

I applied exactly this one-line change locally, rebuilt @sequelize/core, and re-ran the SSCCE: all five cases become correct (in [] → WHERE 0 = 1 → no rows; NOT(in []) → WHERE NOT (0 = 1) → a,b,c). 0 = 1 is as portable across dialects as the 1 = 1 already emitted for Op.notIn (which #18250 asserts with a single default: expectation, i.e. identical SQL on every dialect), and is more optimiser-friendly than IN (NULL). It is also the fragment v6 used for this class of always-false condition.

I want to flag one open design question rather than assume the answer. #18286 proposes throwing on empty Op.or / Op.not instead of emitting a truth value, on the reasoning that a silent truth value is surprising in either direction. If that philosophy should extend here, the alternative is to throw on Op.in: [] as well. My reason for suggesting the literal instead, in this specific case:

• an empty Op.in has an unambiguous meaning (nothing is a member of the empty set), unlike an empty logical group, which is arguably a programming mistake with no clear intent;
• Op.notIn: [] was just settled the other way in fix(core): make empty Op.notIn compose correctly under Op.or and Op.not #18250 (merged, maintainer co-authored) — throwing on Op.in: [] would be asymmetric with its sibling;
• { [Op.in]: [] } matching no rows is legitimate, widely-relied-upon behaviour today, so throwing would be a breaking change, whereas 0 = 1 preserves it and only changes the emitted SQL text.

Happy to implement whichever direction a maintainer prefers.

On the test side, exactly one existing assertion pins the current SQL and would need updating — where.test.ts#L1630-L1637:

describeInSuite(Op.in, 'IN', () => {
  testSql({ intAttr1: { [Op.in]: [] } }, { default: '[intAttr1] IN (NULL)' });
});

Likely why this survived #18250: the Op.notIn suite immediately below has four cases (bare, Op.or, Op.not, Op.and), while the Op.in suite above has only the bare one. I would add the three missing mirror cases for Op.in alongside the fix.

Environment

• Sequelize version: @sequelize/core@7.0.0-alpha.48 (current npm latest; also reproduced on main at e8b702733)
• Node.js version: v22.22.2
• If TypeScript related: TypeScript version: 5.8.3 (not TypeScript-specific — the SSCCE above is plain JS)
• Database & Version: SQLite 3.52.0. Dialect-independent: the fragment is built in the abstract dialect, so all dialects are affected
• Connector library & Version: @sequelize/sqlite3@7.0.0-alpha.48 with sqlite3@6.0.1

Would you be willing to resolve this issue by submitting a Pull Request?

• Yes, I have the time and I know how to start.
• Yes, I have the time but I will need guidance.
• No, I don't have the time, but my company or I are supporting Sequelize through donations on OpenCollective.
• No, I don't have the time, and I understand that I will need to wait until someone from the community or maintainers is interested in resolving my issue.

Indicate your interest in the resolution of this issue by adding the 👍 reaction. Comments such as "+1" will be removed.