Bug #120056 UPDATE IGNORE returns empty result after weakening WHERE clause with OR in a JOIN context

• Submitted: 14 Mar 4:22
• Modified: 14 Mar 14:24
• Reporter: cl hl
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 8.0.45
• OS: Any
• CPU Architecture: Any

[14 Mar 4:22] cl hl

Description:
This bug demonstrates a violation of the monotonicity property in the query optimizer. By changing (A AND B AND C) to (A AND B OR C), the WHERE clause becomes logically more permissive (a superset). However, the DBMS fails to update the row that was correctly identified by the original, more restrictive statement. This typically indicates a flaw in the optimizer's join-selection or predicate-pushdown logic when transitioning from a high-selectivity point lookup to a more complex execution plan involving OR operators and IN subqueries

mysql> -- ORIGINAL
mysql> UPDATE IGNORE t3
    -> JOIN t1 ON t3.c2 = t1.c1
    -> SET t3.c2 = 76, t1.c4 = 159
    -> WHERE (t3.c1 = 3 AND t1.c1 = 5 AND t3.c2 = t1.c1)
    ->   AND (
    ->     t3.c3 IN (
    ->       SELECT t2.c1
    ->       FROM t2
    ->       WHERE t2.c5 > CASE
    ->         WHEN t2.c13 IS NULL THEN t2.c6
    ->         ELSE t2.c11
    ->       END
    ->     )
    ->   );
Query OK, 1 row affected, 2 warnings (0.00 sec)
Rows matched: 2  Changed: 1  Warnings: 2

mysql> -- MUTATED
mysql> UPDATE IGNORE t3
    -> JOIN t1 ON t3.c2 = t1.c1
    -> SET t3.c2 = 76, t1.c4 = 159
    -> WHERE (t3.c1 = 3 AND t1.c1 = 5 OR t3.c2 = t1.c1)
    ->   AND (
    ->     t3.c3 IN (
    ->       SELECT t2.c1
    ->       FROM t2
    ->       WHERE t2.c5 > CASE
    ->         WHEN t2.c13 IS NULL THEN t2.c6
    ->         ELSE t2.c11
    ->       END
    ->     )
    ->   );
Query OK, 0 rows affected, 1 warning (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 1

How to repeat:
DROP DATABASE IF EXISTS databse13_min;
CREATE DATABASE databse13_min;
USE databse13_min;

CREATE TABLE t1 (
  c1 INT PRIMARY KEY,
  c4 INT NULL
);

CREATE TABLE t2 (
  c1 INT PRIMARY KEY,
  c5 DATE NOT NULL,
  c6 MEDIUMTEXT NULL,
  c11 SET('a','b','c','d') NULL,
  c13 DATETIME NULL
);

CREATE TABLE t3 (
  c1 INT PRIMARY KEY,
  c2 INT NOT NULL,
  c3 INT NOT NULL,
  FOREIGN KEY (c2) REFERENCES t1(c1),
  FOREIGN KEY (c3) REFERENCES t2(c1)
);

INSERT INTO t1 (c1, c4) VALUES
(5, 264);

INSERT INTO t2 (c1, c5, c6, c11, c13) VALUES
(2, '2022-09-25', 'psqturqffxa', 'b,a', '2017-08-07 07:36:30');

INSERT INTO t3 (c1, c2, c3) VALUES
(3, 5, 2);

SELECT VERSION();

START TRANSACTION;
SAVEPOINT s0;

-- ORIGINAL
UPDATE IGNORE t3
JOIN t1 ON t3.c2 = t1.c1
SET t3.c2 = 76, t1.c4 = 159
WHERE (t3.c1 = 3 AND t1.c1 = 5 AND t3.c2 = t1.c1)
  AND (
    t3.c3 IN (
      SELECT t2.c1
      FROM t2
      WHERE t2.c5 > CASE
        WHEN t2.c13 IS NULL THEN t2.c6
        ELSE t2.c11
      END
    )
  );

SHOW WARNINGS;
SELECT 'original' AS tag, t1.c1, t1.c4, t3.c1, t3.c2
FROM t3 JOIN t1 ON t1.c1 = 5
WHERE t3.c1 = 3;

ROLLBACK TO s0;

-- MUTATED
UPDATE IGNORE t3
JOIN t1 ON t3.c2 = t1.c1
SET t3.c2 = 76, t1.c4 = 159
WHERE (t3.c1 = 3 AND t1.c1 = 5 OR t3.c2 = t1.c1)
  AND (
    t3.c3 IN (
      SELECT t2.c1
      FROM t2
      WHERE t2.c5 > CASE
        WHEN t2.c13 IS NULL THEN t2.c6
        ELSE t2.c11
      END
    )
  );

SHOW WARNINGS;
SELECT 'mutated' AS tag, t1.c1, t1.c4, t3.c1, t3.c2
FROM t3 JOIN t1 ON t1.c1 = 5
WHERE t3.c1 = 3;

ROLLBACK;

[14 Mar 14:24] Roy Lyseng

Thank you for the bug report.
Verified as described.