MySQL Bugs: #102025: Wrong result when use "select all"

Bug #102025	Wrong result when use "select all"
Submitted:	18 Dec 2020 7:33	Modified:	25 Oct 2021 23:38
Reporter:	shangshang yu	Email Updates:
Status:	Closed	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

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

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) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+

Hello Shangshang yu,

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

regards,
Umesh

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.

Documented fix as follows in the MySQL 8.0.29 changelog:

    An invalid conversion between signed and unsigned variables led
    to incorrect result from a query of the form SELECT ALL
    t1.c1,t2.c1 FROM t1, t2 WHERE t1.c1 > t2.c1.

Closed.