Bug #120047 Incorrect empty update result when weakening WHERE clause in UPDATE IGNORE with CROSS JOIN and IN subquery

• Submitted: 13 Mar 5:08
• Modified: 13 Mar 5:57
• Reporter: cl hl
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 9.5.0
• OS: Any
• CPU Architecture: Any

[13 Mar 5:08] cl hl

Description:
after i adding a forever true condition,the row match change from 2 to 0.The expected direction is over.But it become an empty set

mysql> -- ORIGINAL
mysql> UPDATE IGNORE t3 CROSS JOIN t1 ON t3.c2 = t1.c1
    -> SET t3.c10 = 'pwhgaodsctpvhsxbyvaebgaitectfnzfysgucsukksvdaxbwsjsj',
    ->     t1.c6 = 'gb'
    -> WHERE (t3.c1 = 2 AND t1.c1 = 3 AND t3.c2 = t1.c1)
    ->   AND (
    ->     t3.c3 IN (
    ->       SELECT t2.c1
    ->       FROM t2
    ->       WHERE CASE
    ->               WHEN t2.c13 < t2.c13 THEN t2.c7
    ->               ELSE t2.c7
    ->             END
    ->             < CASE
    ->                 WHEN t2.c13 BETWEEN '2001-12-18 10:44:33' AND '2011-12-18 05:56:12'
    ->                   THEN STR_TO_DATE('2006-06-21', '%Y-%m-%d')
    ->                 ELSE SUBDATE(t2.c13, INTERVAL 10 DAY)
    ->               END
    ->     )
    ->   );
Query OK, 2 rows affected, 1 warning (0.01 sec)
Rows matched: 2  Changed: 2  Warnings: 1

mysql> UPDATE IGNORE t3 CROSS JOIN t1 ON t3.c2 = t1.c1
    -> SET t3.c10 = 'pwhgaodsctpvhsxbyvaebgaitectfnzfysgucsukksvdaxbwsjsj',
    ->     t1.c6 = 'gb'
    -> WHERE (t3.c1 = 2 AND t1.c1 = 3 AND t3.c2 = t1.c1 OR TRUE)
    ->   AND (
    ->     t3.c3 IN (
    ->       SELECT t2.c1
    ->       FROM t2
    ->       WHERE CASE
    ->               WHEN t2.c13 < t2.c13 THEN t2.c7
    ->               ELSE t2.c7
    ->             END
    ->             < CASE
    ->                 WHEN t2.c13 BETWEEN '2001-12-18 10:44:33' AND '2011-12-18 05:56:12'
    ->                   THEN STR_TO_DATE('2006-06-21', '%Y-%m-%d')
    ->                 ELSE SUBDATE(t2.c13, INTERVAL '10' DAY)
    ->               END
    ->     )
    ->   );
Query OK, 0 rows affected, 1 warning (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 1

How to repeat:
-- MIN-REPRO for round-002 / over
DROP DATABASE IF EXISTS databse2_min;
CREATE DATABASE databse2_min;
USE databse2_min;

CREATE TABLE t1 (
  c1 INT PRIMARY KEY,
  c6 VARCHAR(10)
);

CREATE TABLE t2 (
  c1 INT PRIMARY KEY,
  c7 VARCHAR(32),
  c13 DATETIME
);

CREATE TABLE t3 (
  c1 INT PRIMARY KEY,
  c2 INT,
  c3 INT,
  c10 TEXT
);

INSERT INTO t1 VALUES
(3, 'gzn');

INSERT INTO t2 VALUES
(2, 'a', '2021-10-07 06:26:24');

INSERT INTO t3 VALUES
(2, 3, 2, 'before');

START TRANSACTION;
SAVEPOINT s0;

-- ORIGINAL
UPDATE IGNORE t3 CROSS JOIN t1 ON t3.c2 = t1.c1
SET t3.c10 = 'pwhgaodsctpvhsxbyvaebgaitectfnzfysgucsukksvdaxbwsjsj',
    t1.c6 = 'gb'
WHERE (t3.c1 = 2 AND t1.c1 = 3 AND t3.c2 = t1.c1)
  AND (
    t3.c3 IN (
      SELECT t2.c1
      FROM t2
      WHERE CASE
              WHEN t2.c13 < t2.c13 THEN t2.c7
              ELSE t2.c7
            END
            < CASE
                WHEN t2.c13 BETWEEN '2001-12-18 10:44:33' AND '2011-12-18 05:56:12'
                  THEN STR_TO_DATE('2006-06-21', '%Y-%m-%d')
                ELSE SUBDATE(t2.c13, INTERVAL 10 DAY)
              END
    )
  );

SELECT 'original' AS tag, t1.c1, t1.c6, t3.c1, t3.c10
FROM t3 JOIN t1 ON t3.c2 = t1.c1
WHERE t3.c1 = 2 AND t1.c1 = 3;

ROLLBACK TO s0;

-- MUTATED
UPDATE IGNORE t3 CROSS JOIN t1 ON t3.c2 = t1.c1
SET t3.c10 = 'pwhgaodsctpvhsxbyvaebgaitectfnzfysgucsukksvdaxbwsjsj',
    t1.c6 = 'gb'
WHERE (t3.c1 = 2 AND t1.c1 = 3 AND t3.c2 = t1.c1 OR TRUE)
  AND (
    t3.c3 IN (
      SELECT t2.c1
      FROM t2
      WHERE CASE
              WHEN t2.c13 < t2.c13 THEN t2.c7
              ELSE t2.c7
            END
            < CASE
                WHEN t2.c13 BETWEEN '2001-12-18 10:44:33' AND '2011-12-18 05:56:12'
                  THEN STR_TO_DATE('2006-06-21', '%Y-%m-%d')
                ELSE SUBDATE(t2.c13, INTERVAL '10' DAY)
              END
    )
  );

SELECT 'mutated' AS tag, t1.c1, t1.c6, t3.c1, t3.c10
FROM t3 JOIN t1 ON t3.c2 = t1.c1
WHERE t3.c1 = 2 AND t1.c1 = 3;

ROLLBACK;

Suggested fix:
Maybe it is affected by optimizer.The mutation (condition OR TRUE) effectively turns a part of the filter into a tautology. In a complex CROSS JOIN environment, the optimizer attempts to push predicates down to the storage engine layer. If the optimizer incorrectly "short-circuits" the logic and fails to properly evaluate the remaining mandatory predicates (like the IN subquery) or incorrectly prunes the join paths during this process, it may result in zero rows being matched.

[13 Mar 5:57] Chaithra Marsur Gopala Reddy

Hi cl hl,

Thank you for the test case. Verified as described.