Bug #120072 aftter changing UNION ALL to INTERSECT,the rows matched unexpectedly become more

• Submitted: 16 Mar 8:39
• Modified: 16 Mar 14:44
• Reporter: cl hl
• Status: Duplicate
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 9.5.0
• OS: Any
• CPU Architecture: Any

[16 Mar 8:39] cl hl

Description:
aftter changing UNION ALL to INTERSECT,the rows matched unexpectedly become more

mysql> UPDATE IGNORE t2_o
    -> SET c4 = (
    ->   SELECT c6
    ->   FROM t1_o
    ->   WHERE c1 = t2_o.c2
    ->   ORDER BY c6 DESC
    ->   LIMIT 1
    -> )
    -> WHERE EXISTS (
    ->   SELECT s.c4
    ->   FROM (SELECT c4 FROM t2_o) AS s
    ->   INTERSECT
    ->   SELECT k
    ->   FROM (
    ->     SELECT c3 AS k FROM t1_o
    ->     UNION ALL
    ->     SELECT c10 AS k FROM t3
    ->   ) AS d
    -> )
    -> ORDER BY c1 DESC;
Query OK, 0 rows affected (0.01 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql>
mysql> UPDATE IGNORE t2_m
    -> SET c4 = (
    ->   SELECT c6
    ->   FROM t1_m
    ->   WHERE c1 = t2_m.c2
    ->   ORDER BY c6 DESC
    ->   LIMIT 1
    -> )
    -> WHERE EXISTS (
    ->   SELECT s.c4
    ->   FROM (SELECT c4 FROM t2_m) AS s
    ->   INTERSECT
    ->   SELECT k
    ->   FROM (
    ->     SELECT c3 AS k FROM t1_m
    ->     INTERSECT
    ->     SELECT c10 AS k FROM t3
    ->   ) AS d
    -> )
    -> ORDER BY c1 DESC;
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4  Changed: 4  Warnings: 0

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

CREATE TABLE t1 (
  c1 INT PRIMARY KEY,
  c3 TEXT NULL,
  c6 VARCHAR(10) NOT NULL
);

CREATE TABLE t2 (
  c1 INT PRIMARY KEY,
  c2 INT NOT NULL,
  c4 TEXT NOT NULL
);

CREATE TABLE t3 (
  c10 TEXT NULL
);

INSERT INTO t1 VALUES
  (1, 'pfuhuwnjlyaqsnmgqofm', 'zjfyiosvd'),
  (3, 'aqfqvtwlozksyhcmtolrpnbisayuqmfuofgsrkdnxcoqunkfynlvrrncxkr', 'bdcwyawch'),
  (4, NULL, 'mldgcxms');

INSERT INTO t2 VALUES
  (1, 3, 'gohhptxxxpndawqvfkqigwmudisnrpsastbuflu'),
  (2, 3, 'jafqtstrrwtobctgwykgbgeoltdq'),
  (3, 4, 'iqyewxlrmr'),
  (4, 1, 'alutpxmhburphnirnstrsryyugroopz');

INSERT INTO t3 VALUES (NULL);

CREATE TABLE t1_o AS SELECT * FROM t1;
CREATE TABLE t1_m AS SELECT * FROM t1;
CREATE TABLE t2_o AS SELECT * FROM t2;
CREATE TABLE t2_m AS SELECT * FROM t2;

ALTER TABLE t1_o ADD PRIMARY KEY (c1);
ALTER TABLE t1_m ADD PRIMARY KEY (c1);
ALTER TABLE t2_o ADD PRIMARY KEY (c1);
ALTER TABLE t2_m ADD PRIMARY KEY (c1);

UPDATE IGNORE t2_o
SET c4 = (
  SELECT c6
  FROM t1_o
  WHERE c1 = t2_o.c2
  ORDER BY c6 DESC
  LIMIT 1
)
WHERE EXISTS (
  SELECT s.c4
  FROM (SELECT c4 FROM t2_o) AS s
  INTERSECT
  SELECT k
  FROM (
    SELECT c3 AS k FROM t1_o
    UNION ALL
    SELECT c10 AS k FROM t3
  ) AS d
)
ORDER BY c1 DESC;

UPDATE IGNORE t2_m
SET c4 = (
  SELECT c6
  FROM t1_m
  WHERE c1 = t2_m.c2
  ORDER BY c6 DESC
  LIMIT 1
)
WHERE EXISTS (
  SELECT s.c4
  FROM (SELECT c4 FROM t2_m) AS s
  INTERSECT
  SELECT k
  FROM (
    SELECT c3 AS k FROM t1_m
    INTERSECT
    SELECT c10 AS k FROM t3
  ) AS d
)
ORDER BY c1 DESC;

SELECT
  b.c1,
  b.c4 AS before_c4,
  o.c4 AS orig_c4,
  m.c4 AS mut_c4
FROM t2 AS b
JOIN t2_o AS o USING (c1)
JOIN t2_m AS m USING (c1)
ORDER BY b.c1;

[16 Mar 14:44] Roy Lyseng

Thank you for the bug report.
Verified as a duplicate of bug#117911.
A fix will be delivered in release 9.7.