Bug #102025 Wrong result when use "select all"
Submitted: 18 Dec 2020 7:33 Modified: 18 Dec 2020 8:18
Reporter: shangshang yu Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:8.0.22, 5.7.32 OS:Any
Assigned to: CPU Architecture:Any
Tags: regression

[18 Dec 2020 7:33] shangshang yu
Description:
CREATE TABLE `t0` (
  `c0` tinyint unsigned zerofill DEFAULT NULL
) ;
insert into t0 values (255);

CREATE TABLE `t1` (
  `c0` tinyint,
  UNIQUE KEY `c0` (`c0`)
) ;
insert into t1 values (-128),(0),(127);

select all t0.c0,t1.c0 FROM t0, t1 WHERE (t0.c0) > (t1.c0);
+------+------+
| c0   | c0   |
+------+------+
|  255 | -128 |
+------+------+

The result should be:
select all t0.c0,t1.c0 FROM t0, t1 WHERE (t0.c0) > (t1.c0);
+------+------+
| c0   | c0   |
+------+------+
|  255 | -128 |
|  255 |    0 |
|  255 |  127 |
+------+------+

Note:
remove the UNIQUE KEY from t1 can get the right result.

How to repeat:
see description
[18 Dec 2020 8:03] shangshang yu
The explain result for wrong result:
MySQL [test]> explain select all t0.c0,t1.c0 FROM t0, t1 WHERE (t0.c0) > (t1.c0);
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------------------------+
|  1 | SIMPLE      | t0    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL                                           |
|  1 | SIMPLE      | t1    | NULL       | ALL  | c0            | NULL | NULL    | NULL |    3 |    33.33 | Range checked for each record (index map: 0x1) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------------------------+

The explain result for righe result:
MySQL [test]> explain select all t0.c0,t1.c0 FROM t0, t1 WHERE (t0.c0) > (t1.c0);
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                              |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | t0    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL                                               |
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
[18 Dec 2020 8:18] MySQL Verification Team
Hello Shangshang yu,

Thank you for the report.
Verified as described with 8.0.22/5.7.32 builds.

regards,
Umesh
[21 Jan 14:49] Guilhem Bichot
I think that function field_conv() should not do memmove() when signedness of source and target differ ; it should fall back to the Field::store() calls which are in the "else" branches; those calls will handle overflow properly.