Description:
I have encountered an unexpected inconsistency when executing equivalent SQL queries involving NATURAL JOIN, NATURAL LEFT JOIN, and NATURAL RIGHT JOIN. Specifically, the result of a direct NATURAL JOIN returns an empty set, but a logically equivalent INTERSECT ALL query combining NATURAL LEFT JOIN and NATURAL RIGHT JOIN returns a non-empty result.
mysql> SELECT DISTINCTROW t0.c0 AS ref0 FROM t1 NATURAL JOIN t0;
Empty set (0.00 sec)
mysql>
mysql> (SELECT DISTINCTROW t0.c0 AS ref0 FROM t1 NATURAL LEFT JOIN t0) INTERSECT ALL (SELECT DISTINCTROW t0.c0 AS ref0 FROM t1 NATURAL RIGHT JOIN t0);
+------+
| ref0 |
+------+
| 0 |
+------+
1 row in set (0.00 sec)
How to repeat:
mysql> status;
--------------
mysql Ver 9.2.0 for Linux on x86_64 (MySQL Community Server - GPL)
Connection id: 1191
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 9.2.0 MySQL Community Server - GPL
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8mb4
Db characterset: utf8mb4
Client characterset: latin1
Conn. characterset: latin1
UNIX socket: /var/run/mysqld/mysqld.sock
Binary data as: Hexadecimal
Uptime: 6 hours 53 min 21 sec
--------------------------------- SQLcode ---------------------------------
DROP DATABASE IF EXISTS database27;
CREATE DATABASE database27;
USE database27;
#CREATE TABLE IF NOT EXISTS t0(c0 DOUBLE ZEROFILL COLUMN_FORMAT DEFAULT COMMENT 'asdf' NOT NULL) MAX_ROWS = 8331281052391680878, KEY_BLOCK_SIZE = 49815;
CREATE TABLE t0(c0 BIGINT(135) ZEROFILL COLUMN_FORMAT DEFAULT NOT NULL, c1 DECIMAL ZEROFILL UNIQUE KEY NOT NULL) ;
CREATE TABLE t1(c0 FLOAT ZEROFILL STORAGE MEMORY NOT NULL) STATS_PERSISTENT = 0, STATS_SAMPLE_PAGES = 20270;
#REPLACE LOW_PRIORITY INTO t0(c0, c1) VALUES('_5r).u', NULL);
#REPLACE INTO t0(c1) VALUES(0.5418212216602876);
INSERT IGNORE INTO t0(c1) VALUES(-1.329793033E9);
INSERT IGNORE INTO t1(c0) VALUES(-498942792);
select TABLE_NAME, ENGINE from information_schema.TABLES where table_schema = 'database27';
#DROP INDEX c1 ON t0 ALGORITHM=INPLACE LOCK=NONE;
# DROP INDEX c1 ON t0 LOCK=EXCLUSIVE;
TRUNCATE TABLE t1;
UPDATE t1 SET c0='s!M';
# SET SESSION old_alter_table = ON;
# SET SESSION range_alloc_block_size = 2871241090510512967;
INSERT HIGH_PRIORITY INTO t1(c0) VALUES(44637433);
# ANALYZE TABLE t0 UPDATE HISTOGRAM ON c0, c1;
# CHECK TABLE t0, t1 ;
select TABLE_NAME, ENGINE from information_schema.TABLES where table_schema = 'database27';
REPLACE DELAYED INTO t1(c0) VALUES(0.30545701361463895);
# SET SESSION range_optimizer_max_mem_size = 7232431709144999151;
# SET SESSION myisam_stats_method = nulls_unequal;
SELECT DISTINCTROW t0.c0 AS ref0 FROM t1 NATURAL JOIN t0;
(SELECT DISTINCTROW t0.c0 AS ref0 FROM t1 NATURAL LEFT JOIN t0) INTERSECT ALL (SELECT DISTINCTROW t0.c0 AS ref0 FROM t1 NATURAL RIGHT JOIN t0);
Description: I have encountered an unexpected inconsistency when executing equivalent SQL queries involving NATURAL JOIN, NATURAL LEFT JOIN, and NATURAL RIGHT JOIN. Specifically, the result of a direct NATURAL JOIN returns an empty set, but a logically equivalent INTERSECT ALL query combining NATURAL LEFT JOIN and NATURAL RIGHT JOIN returns a non-empty result. mysql> SELECT DISTINCTROW t0.c0 AS ref0 FROM t1 NATURAL JOIN t0; Empty set (0.00 sec) mysql> mysql> (SELECT DISTINCTROW t0.c0 AS ref0 FROM t1 NATURAL LEFT JOIN t0) INTERSECT ALL (SELECT DISTINCTROW t0.c0 AS ref0 FROM t1 NATURAL RIGHT JOIN t0); +------+ | ref0 | +------+ | 0 | +------+ 1 row in set (0.00 sec) How to repeat: mysql> status; -------------- mysql Ver 9.2.0 for Linux on x86_64 (MySQL Community Server - GPL) Connection id: 1191 Current database: Current user: root@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 9.2.0 MySQL Community Server - GPL Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: utf8mb4 Db characterset: utf8mb4 Client characterset: latin1 Conn. characterset: latin1 UNIX socket: /var/run/mysqld/mysqld.sock Binary data as: Hexadecimal Uptime: 6 hours 53 min 21 sec --------------------------------- SQLcode --------------------------------- DROP DATABASE IF EXISTS database27; CREATE DATABASE database27; USE database27; #CREATE TABLE IF NOT EXISTS t0(c0 DOUBLE ZEROFILL COLUMN_FORMAT DEFAULT COMMENT 'asdf' NOT NULL) MAX_ROWS = 8331281052391680878, KEY_BLOCK_SIZE = 49815; CREATE TABLE t0(c0 BIGINT(135) ZEROFILL COLUMN_FORMAT DEFAULT NOT NULL, c1 DECIMAL ZEROFILL UNIQUE KEY NOT NULL) ; CREATE TABLE t1(c0 FLOAT ZEROFILL STORAGE MEMORY NOT NULL) STATS_PERSISTENT = 0, STATS_SAMPLE_PAGES = 20270; #REPLACE LOW_PRIORITY INTO t0(c0, c1) VALUES('_5r).u', NULL); #REPLACE INTO t0(c1) VALUES(0.5418212216602876); INSERT IGNORE INTO t0(c1) VALUES(-1.329793033E9); INSERT IGNORE INTO t1(c0) VALUES(-498942792); select TABLE_NAME, ENGINE from information_schema.TABLES where table_schema = 'database27'; #DROP INDEX c1 ON t0 ALGORITHM=INPLACE LOCK=NONE; # DROP INDEX c1 ON t0 LOCK=EXCLUSIVE; TRUNCATE TABLE t1; UPDATE t1 SET c0='s!M'; # SET SESSION old_alter_table = ON; # SET SESSION range_alloc_block_size = 2871241090510512967; INSERT HIGH_PRIORITY INTO t1(c0) VALUES(44637433); # ANALYZE TABLE t0 UPDATE HISTOGRAM ON c0, c1; # CHECK TABLE t0, t1 ; select TABLE_NAME, ENGINE from information_schema.TABLES where table_schema = 'database27'; REPLACE DELAYED INTO t1(c0) VALUES(0.30545701361463895); # SET SESSION range_optimizer_max_mem_size = 7232431709144999151; # SET SESSION myisam_stats_method = nulls_unequal; SELECT DISTINCTROW t0.c0 AS ref0 FROM t1 NATURAL JOIN t0; (SELECT DISTINCTROW t0.c0 AS ref0 FROM t1 NATURAL LEFT JOIN t0) INTERSECT ALL (SELECT DISTINCTROW t0.c0 AS ref0 FROM t1 NATURAL RIGHT JOIN t0);