TiDB is sensitive to the order of AND-connected `IN`/`ANY` subquery predicates, causing a 4x performance difference

[chen8908917]

Bug Report

Please answer these questions before submitting your issue. Thanks!
The following two queries are logically equivalent. The only difference is the order of two AND-connected predicates:

A AND B
B AND A

where A is an = ANY(subquery) predicate and B is an IN(subquery) predicate.

However, TiDB generates different plans and shows a stable performance difference. In my local reproduction, both queries return the same result, but the A AND B version is about 4x slower than the B AND A version.

Observed timing over 30 alternating executions:

A AND B median: 14.200 ms
B AND A median:  3.214 ms
ratio: 4.42x

order ratios:
  original_first: 4.1354
  mutated_first:  4.4888

Both queries return:

0 rows

1. Minimal reproduce step (Required)

DROP DATABASE IF EXISTS tidb_and_order_perf_min;
CREATE DATABASE tidb_and_order_perf_min;
USE tidb_and_order_perf_min;

CREATE TABLE t1 (
  c1 INT PRIMARY KEY,
  c2 INT,
  c4 INT,
  c5 DATE NOT NULL,
  c6 VARCHAR(32) NOT NULL,
  KEY idx_t1_c5(c5),
  KEY idx_t1_c6_c4(c6, c4)
);

CREATE TABLE t2 (
  c1 INT PRIMARY KEY,
  c3 INT NOT NULL,
  c5 DATETIME,
  c13 DATETIME,
  c15 INT
);

CREATE TABLE t3 (
  c1 INT PRIMARY KEY AUTO_INCREMENT,
  c2 INT NOT NULL,
  c5 DATE,
  KEY idx_t3_c5(c5),
  KEY idx_t3_c2(c2)
);

INSERT INTO t1(c1, c2, c4, c5, c6)
WITH RECURSIVE seq(n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM seq WHERE n < 36
)
SELECT
  n,
  MOD(n, 7),
  MOD(n * 7, 101),
  DATE_ADD('2026-01-01', INTERVAL MOD(n, 28) DAY),
  CONCAT('sample_', n)
FROM seq;

INSERT INTO t2(c1, c3, c5, c13, c15) VALUES
  (1, 10, '2026-01-01', '2026-01-02', 10),
  (2, 20, '2026-02-01', '2026-02-02', 90),
  (3, 30, '2026-03-01', '2026-03-02', 40),
  (4, 40, '2026-04-01', '2026-04-02', 100);

-- t3 is intentionally empty.

-- Query 1: A AND B, slower.
SELECT DISTINCT
  t1.c4 * t1.c4 AS q1,
  t1.c6 <> 'sample_30' AS q2,
  t1.c2 AS q3
FROM t1
WHERE t1.c6 = ANY (
  SELECT s.c3 + d.v
  FROM t2 AS s
  CROSS JOIN (
    SELECT DISTINCT
      x.c5 AS d1,
      STDDEV_SAMP(x.c1) AS d2,
      SHA1(x.c6) AS d3,
      x.c4 / NULLIF(x.c1, 0) AS v
    FROM t1 AS x
    WHERE x.c6 IS NOT NULL
    GROUP BY
      x.c5,
      SHA1(x.c6),
      x.c6,
      x.c4 / NULLIF(x.c1, 0),
      x.c4,
      x.c1
    ORDER BY d3 DESC, v DESC
    LIMIT 23
  ) AS d
    ON d.d2 IS NOT NULL
    OR d.d1 NOT BETWEEN '2023-01-01' AND '2023-12-31'
  WHERE
    s.c13 <> '2023-01-01'
    OR s.c5 BETWEEN '2023-01-01' AND '2023-12-31'
    OR s.c15 >= 83
)
AND t1.c5 IN (
  SELECT e.c5
  FROM t3 AS e
  WHERE e.c2 BETWEEN 10 AND 34
);

-- Query 2: B AND A, faster.
SELECT DISTINCT
  t1.c4 * t1.c4 AS q1,
  t1.c6 <> 'sample_30' AS q2,
  t1.c2 AS q3
FROM t1
WHERE t1.c5 IN (
  SELECT e.c5
  FROM t3 AS e
  WHERE e.c2 BETWEEN 10 AND 34
)
AND t1.c6 = ANY (
  SELECT s.c3 + d.v
  FROM t2 AS s
  CROSS JOIN (
    SELECT DISTINCT
      x.c5 AS d1,
      STDDEV_SAMP(x.c1) AS d2,
      SHA1(x.c6) AS d3,
      x.c4 / NULLIF(x.c1, 0) AS v
    FROM t1 AS x
    WHERE x.c6 IS NOT NULL
    GROUP BY
      x.c5,
      SHA1(x.c6),
      x.c6,
      x.c4 / NULLIF(x.c1, 0),
      x.c4,
      x.c1
    ORDER BY d3 DESC, v DESC
    LIMIT 23
  ) AS d
    ON d.d2 IS NOT NULL
    OR d.d1 NOT BETWEEN '2023-01-01' AND '2023-12-31'
  WHERE
    s.c13 <> '2023-01-01'
    OR s.c5 BETWEEN '2023-01-01' AND '2023-12-31'
    OR s.c15 >= 83
);

2. What did you expect to see? (Required)

The two queries should have similar execution time and preferably similar optimized plans, because A AND B and B AND A are logically equivalent.

3. What did you see instead (Required)

The first query is consistently slower.

A AND B median: 14.200 ms
B AND A median:  3.214 ms
ratio: 4.42x

4. What is your TiDB version? (Required)

Release Version: v8.5.7
Edition: Community
Git Commit Hash: 202b7f47286a1109b5c957401d34c9358d130ae0
Git Branch: HEAD
UTC Build Time: 2026-07-15 02:06:00
GoVersion: go1.25.10
Race Enabled: false
Check Table Before Drop: false
Store: unistore

[kennedy8312]

/sig planner
/severity moderate

[kennedy8312]

tidb-triage-bot AI rationale

• severity/moderate: Equivalent subquery predicate order changes cause a 4x performance regression without correctness or availability impact.
• sig/planner: The issue concerns optimizer plan selection for IN/ANY subquery predicates and predicate ordering.