MySQL Bugs: #106164: prepare stmt query return error record

Bug #106164	prepare stmt query return error record
Submitted:	14 Jan 2022 6:38	Modified:	30 Mar 2022 18:35
Reporter:	yu zhao	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Prepared statements	Severity:	S2 (Serious)
Version:	mysql Ver 8.0.27 for Linux on x86_64 (M, 8.0.27	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
only happens in prepare statements query

How to repeat:
create table t1  (c_int int, c_str varchar(40) character set latin1 collate latin1_bin, c_datetime datetime, c_timestamp timestamp, c_double double, c_decimal decimal(12, 6), c_enum enum('blue','green','red','yellow','white','orange','purple'), primary key (c_int, c_str)   , key(c_decimal)  , key(c_timestamp));
create table t2  like t1 ;
insert into t1 values(74,"happy hawking","2020-02-09 09:05:59",NULL,52.087467,NULL,'red');
insert into t2 values(9,"relaxed pike","2020-02-21 04:01:17","2020-06-16 07:29:04",67.851665,2.140000,"purple");
insert into t2 values(13,"magical feynman"  ,"2020-02-22 20:54:25","2020-01-17 12:44:09",91.760801,6.078000,"blue"  );
insert into t2 values(37,"sleepy poincare"  ,"2020-02-08 12:27:38","2020-05-24 17:20:53",92.522031,3.527000,"purple");
insert into t2 values(16,"bold lamport"     ,"2020-06-23 06:30:24","2020-03-30 14:18:39",23.914033,6.928000,"white" );
insert into t2 values(20,"dazzling joliot"  ,"2020-01-04 14:45:20","2020-04-23 15:40:17",29.495902,3.745000,"red"  );
insert into t2 values(24,"reverent mclaren" ,"2020-02-20 22:46:35","2020-02-05 10:34:07",73.501801,3.898000,"white" );
insert into t2 values(32,"practical leavitt","2020-06-30 16:44:03","2020-03-04 19:48:55",69.007064,6.101000,"red"   );
insert into t2 values( 6,"inspiring gould"  ,"2020-01-31 09:51:10","2020-05-08 12:51:29",49.691598,2.323000,"orange");
insert into t2 values(18,"18:musing cohen"  ,"2020-02-06 06:26:37","2020-03-07 22:16:40",61.623608,6.576000,"purple");
insert into t2 values(22,"nervous hodgkin"  ,"2020-06-23 07:23:00","2020-04-09 16:04:43", 46.96624,5.528000,"white" );
insert into t2 values(26,"hungry lamport"   ,"2020-05-24 13:13:09","2020-01-20 09:32:34",77.832095,6.648000,"orange");
insert into t2 values( 7,"kind mclean"      ,"2020-05-28 14:39:30","2020-04-04 13:56:23",    6.339,2.109000,"yellow");
insert into t2 values(15,"awesome feynman"  ,"2020-02-10 21:55:36","2020-03-13 12:42:44",92.327104,9.830000,"green" );
insert into t2 values(23,"reverent solomon" ,"2020-04-24 03:54:51","2020-04-09 11:34:48",41.917707,7.140000,"white" );
insert into t2 values(39,"pensive chaplygin","2020-01-26 12:56:59","2020-03-03 05:12:12",49.550909,9.598000,"white" );

prepare stmt1348 from "select * from t1 where c_timestamp = all (select c_timestamp from t2 where c_int = ? )  ";
/* 257:1710 */ set @v0 = 19;
/* 257:1710 */ execute stmt1348 using @v0;
/* 257:1711 */ set @v0 = 14;
/* 257:1711 */ execute stmt1348 using @v0;
/* 257:1712 */ update t1 set c_datetime = c_timestamp, c_timestamp = '2020-03-01 18:18:13' where c_str in ('ecstatic raman', null, 'sleepy bassi');
/* 257:1713 */ set @v0 = 20;
/* 257:1713 */ execute stmt1348 using @v0;

expect:
mysql> select * from t1 where c_timestamp = all (select c_timestamp from t2 where c_int = 20 );
Empty set (0.00 sec)

actual:
mysql> select * from t1 where c_timestamp = all (select c_timestamp from t2 where c_int = 14 );
+-------+---------------+---------------------+-------------+-----------+-----------+--------+
| c_int | c_str         | c_datetime          | c_timestamp | c_double  | c_decimal | c_enum |
+-------+---------------+---------------------+-------------+-----------+-----------+--------+
|    74 | happy hawking | 2020-02-09 09:05:59 | NULL        | 52.087467 |      NULL | red    |
+-------+---------------+---------------------+-------------+-----------+-----------+--------+
1 row in set (0.00 sec)

Hello yu zhao,

Thank you for the report and test case.
Observed that 8.0.27 build is affected.

regards,
Umesh

Documented fix as follows in the MySQL 8.0.30 changelog:

    A problem with evaluation of general quantified comparison
    predicates occurred when the left-hand side of the predicate was
    NULL. In such cases, the value of the subquery evaluation from
    the last current row is saved, so that it does not need
    re-evaluation, but the cached value (result_for_null_param) was
    not cleared between executions, so that the next execution could
    re-use the result from the previous execution. One consequence
    of this was that, when a subquery execution first caused zero
    rows to match from the subquery--which for an ALL predicate
    should return TRUE--a subsequent execution causing at least
    one row to match also returned TRUE, even though FALSE was
    expected.

    To solve this issue, we now make sure to clear the cached value
    cleaning up the subquery predicate following execution.

Closed.