Description:
The following two queries produce inconsistent results.
```
The first query: select t2.c0, t1.c0, t3.c0, (t1.c0) >= (t3.c0) from t2, t3, t1;
+---------------------------------------------------------------------------------------------+-------+--------+--------------------+
| c0 | c0 | c0 | (t1.c0) >= (t3.c0) |
+---------------------------------------------------------------------------------------------+-------+--------+--------------------+
| NULL | NULL | 32767 | NULL |
| NULL | 65535 | 32767 | 1 |
| NULL | NULL | -32768 | NULL |
| NULL | 65535 | -32768 | 1 |
| NULL | NULL | NULL | NULL |
| NULL | 65535 | NULL | NULL |
| NULL | NULL | 32767 | NULL |
| NULL | 65535 | 32767 | 1 |
| NULL | NULL | -32768 | NULL |
| NULL | 65535 | -32768 | 1 |
| NULL | NULL | NULL | NULL |
| NULL | 65535 | NULL | NULL |
| NULL | NULL | 32767 | NULL |
| NULL | 65535 | 32767 | 1 |
| NULL | NULL | -32768 | NULL |
| NULL | 65535 | -32768 | 1 |
| NULL | NULL | NULL | NULL |
| NULL | 65535 | NULL | NULL |
| 0000000000000000000000000000000000000000000000000000000000000000000000000000000001838534557 | NULL | 32767 | NULL |
| 0000000000000000000000000000000000000000000000000000000000000000000000000000000001838534557 | 65535 | 32767 | 1 |
| 0000000000000000000000000000000000000000000000000000000000000000000000000000000001838534557 | NULL | -32768 | NULL |
| 0000000000000000000000000000000000000000000000000000000000000000000000000000000001838534557 | 65535 | -32768 | 1 |
| 0000000000000000000000000000000000000000000000000000000000000000000000000000000001838534557 | NULL | NULL | NULL |
| 0000000000000000000000000000000000000000000000000000000000000000000000000000000001838534557 | 65535 | NULL | NULL |
+---------------------------------------------------------------------------------------------+-------+--------+--------------------+
```
The second query: select t2.c0, t1.c0, t3.c0 from t2, t3, t1 where (t1.c0) >= (t3.c0);
+---------------------------------------------------------------------------------------------+-------+--------+
| c0 | c0 | c0 |
+---------------------------------------------------------------------------------------------+-------+--------+
| NULL | 65535 | -32768 |
| NULL | 65535 | -32768 |
| NULL | 65535 | -32768 |
| 0000000000000000000000000000000000000000000000000000000000000000000000000000000001838534557 | 65535 | -32768 |
+---------------------------------------------------------------------------------------------+-------+--------+
```
According to the first query, the second query should produce the
following result:
+---------------------------------------------------------------------------------------------+-------+--------+
| c0 | c0 | c0 |
+---------------------------------------------------------------------------------------------+-------+--------+
| NULL | 65535 | 32767 |
| NULL | 65535 | -32768 |
| NULL | 65535 | 32767 |
| NULL | 65535 | -32768 |
| NULL | 65535 | 32767 |
| NULL | 65535 | -32768 |
| 0000000000000000000000000000000000000000000000000000000000000000000000000000000001838534557 | 65535 | 32767 |
| 0000000000000000000000000000000000000000000000000000000000000000000000000000000001838534557 | 65535 | -32768 |
+---------------------------------------------------------------------------------------------+-------+--------+
It seems that the results of rows 1, 3, 5, and 7 are missing.
How to repeat:
The following SQL will reproduce the above scenario.
DROP DATABASE IF EXISTS database128;
CREATE DATABASE database128;
USE database128;
CREATE TABLE t0(c0 FLOAT COLUMN_FORMAT DYNAMIC UNIQUE PRIMARY KEY NOT NULL COMMENT 'asdf' STORAGE MEMORY, c1 MEDIUMINT(155) COLUMN_FORMAT DYNAMIC COMMENT 'asdf' STORAGE MEMORY NULL UNIQUE ) MIN_ROWS = 1314428157793096847, INSERT_METHOD = NO, CHECKSUM = 1, STATS_SAMPLE_PAGES = 7329, COMPRESSION = 'LZ4';
CREATE TABLE t1(c0 SMALLINT ZEROFILL COLUMN_FORMAT DEFAULT STORAGE DISK UNIQUE KEY COMMENT 'asdf' ) ;
CREATE TABLE IF NOT EXISTS t2(c0 BIGINT(91) ZEROFILL COLUMN_FORMAT DYNAMIC UNIQUE KEY, c1 TINYINT UNIQUE KEY NULL STORAGE DISK) ;
CREATE TABLE t3(c0 SMALLINT COMMENT 'asdf' ) INSERT_METHOD = FIRST, ENGINE = MyISAM, PACK_KEYS = DEFAULT, AVG_ROW_LENGTH = 6674247429804612968;
INSERT LOW_PRIORITY INTO t2(c0, c1) VALUES(NULL, -70517450), (NULL, 'dS');
SET SESSION query_alloc_block_size = 1544646938;
select TABLE_NAME, ENGINE from information_schema.TABLES where table_schema = 'database128';
DELETE LOW_PRIORITY QUICK IGNORE FROM t3;
TRUNCATE TABLE t0;
ALTER TABLE t1 PACK_KEYS 1, ENABLE KEYS, COMPRESSION 'NONE', FORCE, ROW_FORMAT DEFAULT, RENAME AS t1;
INSERT IGNORE INTO t1(c0) VALUES(832525493), ("167490354");
INSERT DELAYED INTO t2(c0, c1) VALUES(NULL, NULL);
INSERT DELAYED INTO t2(c0, c1) VALUES(',', NULL);
REPLACE INTO t1(c0) VALUES(-507282259);
REPLACE INTO t2(c0, c1) VALUES(NULL, NULL);
CREATE UNIQUE INDEX i0 USING BTREE ON t3(c0 ASC);
DELETE QUICK FROM t3;
REPLACE LOW_PRIORITY INTO t1(c0) VALUES(974221741);
REPLACE INTO t1(c0) VALUES(NULL);
REPLACE INTO t1(c0) VALUES(-1548987571);
INSERT IGNORE INTO t1(c0) VALUES(2046439917);
ALTER TABLE t2 FORCE;
DELETE LOW_PRIORITY IGNORE FROM t0 WHERE EXISTS (SELECT 1 wHERE FALSE);
CREATE INDEX i1 USING BTREE ON t3((t3.c0), ((+ ( EXISTS (SELECT 1 wHERE FALSE))))) VISIBLE;
REPLACE DELAYED INTO t0(c1, c0) VALUES(NULL, NULL);
INSERT LOW_PRIORITY IGNORE INTO t2(c0, c1) VALUES(1838534557, "-1028999567"), (NULL, NULL);
CHECKSUM TABLE t2, t3;
DROP INDEX c0 ON t0 LOCK=NONE;
select TABLE_NAME, ENGINE from information_schema.TABLES where table_schema = 'database128';
ALTER TABLE t2 PACK_KEYS 1, DROP c1, DELAY_KEY_WRITE 1, ALGORITHM DEFAULT, RENAME AS t2, STATS_AUTO_RECALC DEFAULT, COMPRESSION 'ZLIB', CHECKSUM 0, ROW_FORMAT FIXED, DISABLE KEYS, FORCE, STATS_PERSISTENT DEFAULT, INSERT_METHOD FIRST;
select TABLE_NAME, ENGINE from information_schema.TABLES where table_schema = 'database128';
select TABLE_NAME, ENGINE from information_schema.TABLES where table_schema = 'database128';
select TABLE_NAME, ENGINE from information_schema.TABLES where table_schema = 'database128';
INSERT DELAYED IGNORE INTO t3(c0) VALUES(-120429812), ('1119490977'), (NULL);
ALTER TABLE t2 ROW_FORMAT REDUNDANT, DISABLE KEYS, STATS_AUTO_RECALC DEFAULT, CHECKSUM 1, RENAME TO t0;
ALTER TABLE t0 FORCE, STATS_PERSISTENT 1, COMPRESSION 'NONE', PACK_KEYS 1, ALGORITHM INPLACE, RENAME TO t0;
REPLACE DELAYED INTO t0(c1) VALUES(NULL);
INSERT INTO t0(c1, c0) VALUES("g!q", 1613683055), (NULL, 1974293663);
REPLACE DELAYED INTO t0(c1, c0) VALUES(NULL, -647938094);
select t2.c0, t1.c0, t3.c0, (t1.c0) >= (t3.c0) from t2, t3, t1;
select t2.c0, t1.c0, t3.c0 from t2, t3, t1 where (t1.c0) >= (t3.c0);
Description: The following two queries produce inconsistent results. ``` The first query: select t2.c0, t1.c0, t3.c0, (t1.c0) >= (t3.c0) from t2, t3, t1; +---------------------------------------------------------------------------------------------+-------+--------+--------------------+ | c0 | c0 | c0 | (t1.c0) >= (t3.c0) | +---------------------------------------------------------------------------------------------+-------+--------+--------------------+ | NULL | NULL | 32767 | NULL | | NULL | 65535 | 32767 | 1 | | NULL | NULL | -32768 | NULL | | NULL | 65535 | -32768 | 1 | | NULL | NULL | NULL | NULL | | NULL | 65535 | NULL | NULL | | NULL | NULL | 32767 | NULL | | NULL | 65535 | 32767 | 1 | | NULL | NULL | -32768 | NULL | | NULL | 65535 | -32768 | 1 | | NULL | NULL | NULL | NULL | | NULL | 65535 | NULL | NULL | | NULL | NULL | 32767 | NULL | | NULL | 65535 | 32767 | 1 | | NULL | NULL | -32768 | NULL | | NULL | 65535 | -32768 | 1 | | NULL | NULL | NULL | NULL | | NULL | 65535 | NULL | NULL | | 0000000000000000000000000000000000000000000000000000000000000000000000000000000001838534557 | NULL | 32767 | NULL | | 0000000000000000000000000000000000000000000000000000000000000000000000000000000001838534557 | 65535 | 32767 | 1 | | 0000000000000000000000000000000000000000000000000000000000000000000000000000000001838534557 | NULL | -32768 | NULL | | 0000000000000000000000000000000000000000000000000000000000000000000000000000000001838534557 | 65535 | -32768 | 1 | | 0000000000000000000000000000000000000000000000000000000000000000000000000000000001838534557 | NULL | NULL | NULL | | 0000000000000000000000000000000000000000000000000000000000000000000000000000000001838534557 | 65535 | NULL | NULL | +---------------------------------------------------------------------------------------------+-------+--------+--------------------+ ``` The second query: select t2.c0, t1.c0, t3.c0 from t2, t3, t1 where (t1.c0) >= (t3.c0); +---------------------------------------------------------------------------------------------+-------+--------+ | c0 | c0 | c0 | +---------------------------------------------------------------------------------------------+-------+--------+ | NULL | 65535 | -32768 | | NULL | 65535 | -32768 | | NULL | 65535 | -32768 | | 0000000000000000000000000000000000000000000000000000000000000000000000000000000001838534557 | 65535 | -32768 | +---------------------------------------------------------------------------------------------+-------+--------+ ``` According to the first query, the second query should produce the following result: +---------------------------------------------------------------------------------------------+-------+--------+ | c0 | c0 | c0 | +---------------------------------------------------------------------------------------------+-------+--------+ | NULL | 65535 | 32767 | | NULL | 65535 | -32768 | | NULL | 65535 | 32767 | | NULL | 65535 | -32768 | | NULL | 65535 | 32767 | | NULL | 65535 | -32768 | | 0000000000000000000000000000000000000000000000000000000000000000000000000000000001838534557 | 65535 | 32767 | | 0000000000000000000000000000000000000000000000000000000000000000000000000000000001838534557 | 65535 | -32768 | +---------------------------------------------------------------------------------------------+-------+--------+ It seems that the results of rows 1, 3, 5, and 7 are missing. How to repeat: The following SQL will reproduce the above scenario. DROP DATABASE IF EXISTS database128; CREATE DATABASE database128; USE database128; CREATE TABLE t0(c0 FLOAT COLUMN_FORMAT DYNAMIC UNIQUE PRIMARY KEY NOT NULL COMMENT 'asdf' STORAGE MEMORY, c1 MEDIUMINT(155) COLUMN_FORMAT DYNAMIC COMMENT 'asdf' STORAGE MEMORY NULL UNIQUE ) MIN_ROWS = 1314428157793096847, INSERT_METHOD = NO, CHECKSUM = 1, STATS_SAMPLE_PAGES = 7329, COMPRESSION = 'LZ4'; CREATE TABLE t1(c0 SMALLINT ZEROFILL COLUMN_FORMAT DEFAULT STORAGE DISK UNIQUE KEY COMMENT 'asdf' ) ; CREATE TABLE IF NOT EXISTS t2(c0 BIGINT(91) ZEROFILL COLUMN_FORMAT DYNAMIC UNIQUE KEY, c1 TINYINT UNIQUE KEY NULL STORAGE DISK) ; CREATE TABLE t3(c0 SMALLINT COMMENT 'asdf' ) INSERT_METHOD = FIRST, ENGINE = MyISAM, PACK_KEYS = DEFAULT, AVG_ROW_LENGTH = 6674247429804612968; INSERT LOW_PRIORITY INTO t2(c0, c1) VALUES(NULL, -70517450), (NULL, 'dS'); SET SESSION query_alloc_block_size = 1544646938; select TABLE_NAME, ENGINE from information_schema.TABLES where table_schema = 'database128'; DELETE LOW_PRIORITY QUICK IGNORE FROM t3; TRUNCATE TABLE t0; ALTER TABLE t1 PACK_KEYS 1, ENABLE KEYS, COMPRESSION 'NONE', FORCE, ROW_FORMAT DEFAULT, RENAME AS t1; INSERT IGNORE INTO t1(c0) VALUES(832525493), ("167490354"); INSERT DELAYED INTO t2(c0, c1) VALUES(NULL, NULL); INSERT DELAYED INTO t2(c0, c1) VALUES(',', NULL); REPLACE INTO t1(c0) VALUES(-507282259); REPLACE INTO t2(c0, c1) VALUES(NULL, NULL); CREATE UNIQUE INDEX i0 USING BTREE ON t3(c0 ASC); DELETE QUICK FROM t3; REPLACE LOW_PRIORITY INTO t1(c0) VALUES(974221741); REPLACE INTO t1(c0) VALUES(NULL); REPLACE INTO t1(c0) VALUES(-1548987571); INSERT IGNORE INTO t1(c0) VALUES(2046439917); ALTER TABLE t2 FORCE; DELETE LOW_PRIORITY IGNORE FROM t0 WHERE EXISTS (SELECT 1 wHERE FALSE); CREATE INDEX i1 USING BTREE ON t3((t3.c0), ((+ ( EXISTS (SELECT 1 wHERE FALSE))))) VISIBLE; REPLACE DELAYED INTO t0(c1, c0) VALUES(NULL, NULL); INSERT LOW_PRIORITY IGNORE INTO t2(c0, c1) VALUES(1838534557, "-1028999567"), (NULL, NULL); CHECKSUM TABLE t2, t3; DROP INDEX c0 ON t0 LOCK=NONE; select TABLE_NAME, ENGINE from information_schema.TABLES where table_schema = 'database128'; ALTER TABLE t2 PACK_KEYS 1, DROP c1, DELAY_KEY_WRITE 1, ALGORITHM DEFAULT, RENAME AS t2, STATS_AUTO_RECALC DEFAULT, COMPRESSION 'ZLIB', CHECKSUM 0, ROW_FORMAT FIXED, DISABLE KEYS, FORCE, STATS_PERSISTENT DEFAULT, INSERT_METHOD FIRST; select TABLE_NAME, ENGINE from information_schema.TABLES where table_schema = 'database128'; select TABLE_NAME, ENGINE from information_schema.TABLES where table_schema = 'database128'; select TABLE_NAME, ENGINE from information_schema.TABLES where table_schema = 'database128'; INSERT DELAYED IGNORE INTO t3(c0) VALUES(-120429812), ('1119490977'), (NULL); ALTER TABLE t2 ROW_FORMAT REDUNDANT, DISABLE KEYS, STATS_AUTO_RECALC DEFAULT, CHECKSUM 1, RENAME TO t0; ALTER TABLE t0 FORCE, STATS_PERSISTENT 1, COMPRESSION 'NONE', PACK_KEYS 1, ALGORITHM INPLACE, RENAME TO t0; REPLACE DELAYED INTO t0(c1) VALUES(NULL); INSERT INTO t0(c1, c0) VALUES("g!q", 1613683055), (NULL, 1974293663); REPLACE DELAYED INTO t0(c1, c0) VALUES(NULL, -647938094); select t2.c0, t1.c0, t3.c0, (t1.c0) >= (t3.c0) from t2, t3, t1; select t2.c0, t1.c0, t3.c0 from t2, t3, t1 where (t1.c0) >= (t3.c0);