Bug #101847 Potentially wrong results
Submitted: 3 Dec 2020 7:17 Modified: 26 Oct 2021 0:10
Reporter: xiaoyang chen Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:8.0 OS:Any
Assigned to: CPU Architecture:Any

[3 Dec 2020 7:17] xiaoyang chen 
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);
[3 Dec 2020 13:26] MySQL Verification Team 
Hi Mr. chen,

Thank you for your bug report.

However, this is not a bug.

Your first query is taking a full Cartesian product, while your second query is taking a semi-Cartesian product.

Those two set operations are not meant to produce the same result.

Not a bug.
[1 Jun 2021 6:45] Erik Frøseth 
Hello,

This is indeed a bug. Here is a simpler test case:

CREATE TABLE t1 (i SMALLINT, INDEX a (i));
CREATE TABLE t2 (i SMALLINT UNSIGNED);

INSERT INTO t1 VALUES (-32768), (32767);
INSERT INTO t2 VALUES (65535);
SELECT * FROM t1 JOIN t2 ON t2.i > t1.i;
SELECT * FROM t1 IGNORE INDEX (a) JOIN t2 ON t2.i > t1.i;

You get different results with and without index.

Tested on 8.0.25
[1 Jun 2021 13:01] MySQL Verification Team 
Hi,

Thank you for your test case.

This is, indeed a bug, even in the last release of 8.0.

Here are results of your test case ....... A dash lines divide results from the first and second query :

i	i
-32768	65535

-----------------------------

i	i
-32768	65535
32767	65535

Verified as reported.
[14 Jun 2021 13:09] Erlend Dahl 
Posted by developer:
 
With Erik's example, this is a regression in 5.7 already.
[14 Jun 2021 13:34] MySQL Verification Team 
Thank you, Erlend .....
[21 Oct 2021 11:57] Qilu Wei 
It seems that this bug is a duplicate of the following one:

https://bugs.mysql.com/bug.php?id=102025
[21 Oct 2021 13:27] MySQL Verification Team 
Hi Mr. Wei,

It does not seem that the report, that you are citing, is a duplicate of this bug report.

However, we shall still check it out .......
[21 Oct 2021 14:12] Steinar Gunderson 
It's a duplicate. I'll send a patch.
[21 Oct 2021 14:15] MySQL Verification Team 
Thanks in advance.
[26 Oct 2021 0:10] Jon Stephens 
Duplicate of BUG#102025.

Closed.
[26 Oct 2021 12:18] MySQL Verification Team 
Thank you, Jon.