Bug #109852 MySQL8.0.31 EXCEPT for ALL the problems: not being able to move same values

• Submitted: 31 Jan 2023 2:43
• Modified: 31 Jan 2023 5:35
• Reporter: HULONG CUI
• Status: Not a Bug
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 8.0.31
• OS: Any
• CPU Architecture: Any

[31 Jan 2023 2:43] HULONG CUI

Description:
EXCEPT for ALL the problems: not being able to move same values

EXCEPT：A={1,2,3,3}，B={3,4,5,6}	A-B={1,2}

but the result in the actual sql statement：not exclude value 3
EXCEPT：A={1,2,3,3}，B={3,4,5,6}	A-B={1,2,3}

How to repeat:
CREATE TABLE `DataSetD` (
  `id` bigint NOT NULL ,
  `name` varchar(10) DEFAULT NULL
);
INSERT INTO DataSetD(id,name) VALUES(1,'A'),(1,'A'),(3,'C');

mysql> SELECT * FROM DataSetD;
+----+------+
| id | name |
+----+------+
|  1 | A    |
|  1 | A    |
|  3 | C    |
+----+------+
3 rows in set (0.00 sec)

CREATE TABLE `DataSetE` (
  `id` bigint NOT NULL ,
  `name` varchar(10) DEFAULT NULL
);
INSERT INTO DataSetE(id,name) VALUES(1,'A');

mysql> SELECT * FROM DataSetE;
+----+------+
| id | name |
+----+------+
|  1 | A    |
+----+------+
1 row in set (0.00 sec)

mysql > table  DataSetD EXCEPT ALL  table DataSetE;
+----+------+
| id | name |
+----+------+
|  1 | A    |
|  3 | C    |
+----+------+
2 rows in set (0.00 sec)

can't move DataSetD  id,name(1,A) values

[31 Jan 2023 5:35] MySQL Verification Team

Hello HULONG CUI,

Thank you for the report and test case.
IMHO "EXCEPT ALL" is working as expected i.e. "EXCEPT ALL" don't remove duplicates. You can try with just "EXCEPT".

-
table  DataSetD EXCEPT  ALL table DataSetE;
+====+======+
| id | name |
+====+======+
| 1  | A    |
| 3  | C    |
+----+------+

Without ALL:
table  DataSetD EXCEPT table DataSetE;
+====+======+
| id | name |
+====+======+
| 3  | C    |
+----+------+

Please see 
https://dev.mysql.com/doc/refman/8.0/en/intersect.html
https://dev.mysql.com/doc/refman/8.0/en/except.html

regards,
Umesh