[MDEV-31277] Wrong result on second execution of prepare statement from view create with left join (merged derived table) Created: 2023-05-15  Updated: 2023-11-28

Status: Stalled
Project: MariaDB Server
Component/s: Prepared Statements, Views
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: Oleksandr Byelkin
Resolution: Unresolved Votes: 0
Labels: None

Issue Links:
Problem/Incident
is caused by MDEV-28602 Wrong result with outer join, merged ... Closed
Relates
relates to MDEV-30073 Wrong result on 2nd execution of PS f... Stalled
relates to MDEV-31003 Second execution for ps-protocol Stalled

 Description   

Problem is only on merged derived table. Left join with normal table gives correct result.

Testcase:

create table t1 (
 
  Election int(10) unsigned NOT NULL
 
);
 
 
 
insert into t1 (Election) values (1);
 
 
create table t2 (
 
  VoteID int(10),
 
  ElectionID int(10),
 
  UserID int(10)
 
);
 
 
 
insert into t2 (ElectionID, UserID) values (2,  30), (3, 30);
 
create view v1 as select * from t1
 
  left join ( select 'Y' AS Voted, ElectionID from t2 ) AS T 
    on T.ElectionID = t1.Election
 
limit 9;
 
 
 
prepare stmt1 from "select * from v1";
 
 
 
execute stmt1;
 
execute stmt1;
 
 
 
deallocate prepare stmt1;
 
 
 
drop view v1;
 
drop table t1, t2;

Actual result:

execute stmt1;
 
Election	Voted	ElectionID
 
1	NULL	NULL
 
execute stmt1;
 
Election	Voted	ElectionID
 
1		NULL

Expected result:

execute stmt1;
 
Election	Voted	ElectionID
 
1	NULL	NULL
 
execute stmt1;
 
Election	Voted	ElectionID
 
1	NULL	NULL



 Comments   
Comment by Rex Johnston [ 2023-05-15 ]

10.4 appears to fail in a slightly different way

+----------+-------+------------+
 
| Election | Voted | ElectionID |
 
+----------+-------+------------+
 
|        1 | Y     |       NULL |
 
+----------+-------+------------+
 
+----------+-------+------------+
 
| Election | Voted | ElectionID |
 
+----------+-------+------------+
 
|        1 | Y     |       NULL |
 
+----------+-------+------------+

vs

+----------+-------+------------+
 
| Election | Voted | ElectionID |
 
+----------+-------+------------+
 
|        1 | NULL  |       NULL |
 
+----------+-------+------------+
 
+----------+-------+------------+
 
| Election | Voted | ElectionID |
 
+----------+-------+------------+
 
|        1 |       |       NULL |
 
+----------+-------+------------+

Comment by Rex Johnston [ 2023-05-16 ]

intercepting call in Item_direct_view_ref::save_in_field()

first execution, field=="(Field *)

{v1.Voted=NULL}

"
second execution field=="(Field *)

{v1.Voted=}

"

traced to creation of temporary table, and the fields used.
First execution has field->flags==0
Second execution based on string literal 'Y' which cannot be null, so field->flags==NOT_NULL_FLAG

Also first execution, setup_fields/.../Field_iterator_view::create_item() has table_list->field_translation not null,
second execution it is null.

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