[MDEV-31307] Second excution of query "select * from t1 where a is null" where a is auto_increment column doesn't return result Created: 2023-05-19  Updated: 2023-11-28

Status: Open
Project: MariaDB Server
Component/s: None
Affects Version/s: 10.4, 10.5, 10.6, 10.9, 10.10, 11.0, 11.1
Fix Version/s: 10.4, 10.5, 10.6, 11.0, 11.1

Type: Bug Priority: Major
Reporter: Lena Startseva Assignee: Unassigned
Resolution: Unresolved Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-31003 Second execution for ps-protocol Stalled

 Description   

In testcase below expected that LAST_INSERT_ID() and "select * from t1 where a is null" have the same behavior, but "select * from t1 where a is null" doesn't return result on second execution:

create table t1 (a int auto_increment primary key, b int);
 
insert t1 (b) values (10);
 
set sql_auto_is_null=1;
 
select * from t1 where a is null;
 
select * from t1 where a is null;
 
drop table t1;

Actual result:

select LAST_INSERT_ID();
 
LAST_INSERT_ID()
 
1
 
select * from t1 where a is null;
 
a	b
 
1	10
 
select LAST_INSERT_ID();
 
LAST_INSERT_ID()
 
1
 
select * from t1 where a is null;
 
a	b

Expected result:

select LAST_INSERT_ID();
 
LAST_INSERT_ID()
 
1
 
select * from t1 where a is null;
 
a	b
 
1	10
 
select LAST_INSERT_ID();
 
LAST_INSERT_ID()
 
1
 
select * from t1 where a is null;
 
a	b
 
1	10


Generated at Thu Feb 08 10:22:51 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.