Oracle mode INTERSECT followed by INTERSECT ALL returns a row absent from the middle operand

CREATE DATABASE IF NOT EXISTS set_test;

USE set_test;

CREATE TABLE a (x INT);

CREATE TABLE b (x INT);

CREATE TABLE c (x INT);

INSERT INTO a VALUES (1),(2),(2);

INSERT INTO b VALUES (1);

INSERT INTO c VALUES (1),(2),(2);

SET sql_mode='ORACLE';

SELECT 'inline_oracle' AS phase;

SELECT x FROM a

INTERSECT

SELECT x FROM b

INTERSECT ALL

SELECT x FROM c

ORDER BY 1;

SELECT 'nested_oracle' AS phase;

(SELECT x FROM a INTERSECT SELECT x FROM b)

INTERSECT ALL

SELECT x FROM c

ORDER BY 1;

PREPARE p FROM 'SELECT x FROM a INTERSECT SELECT x FROM b INTERSECT ALL SELECT x FROM c ORDER BY 1';

SELECT 'prepared_oracle_1' AS phase;

EXECUTE p;

SELECT 'prepared_oracle_2' AS phase;

EXECUTE p;

DEALLOCATE PREPARE p;

SET sql_mode='';

SELECT 'inline_default' AS phase;

SELECT x FROM a

INTERSECT

SELECT x FROM b

INTERSECT ALL

SELECT x FROM c

ORDER BY 1;

PREPARE p FROM 'SELECT x FROM a INTERSECT SELECT x FROM b INTERSECT ALL SELECT x FROM c ORDER BY 1';

SELECT 'prepared_default_1' AS phase;

EXECUTE p;

SELECT 'prepared_default_2' AS phase;

EXECUTE p;

DEALLOCATE PREPARE p;

DROP DATABASE set_test;

inline_oracle

1

2

nested_oracle

1

prepared_oracle_1

1

2

prepared_oracle_2

1

2

inline_default

1

prepared_default_1

1

prepared_default_2

1