[MDEV-31277] Wrong result on 2-nd execution of PS to select from view using derived - Jira

XML

Word

Printable

Type: Bug
Status: Closed (View Workflow)
Priority: Critical
Resolution: Fixed
Affects Version/s: 10.5, 10.6, 11.1(EOL), 10.4(EOL), 10.9(EOL), 10.10(EOL), 11.0(EOL)
Fix Version/s: 10.5.25, 10.6.18, 10.11.8, 11.0.6, 11.1.5, 11.2.4, 11.4.2
Component/s: Prepared Statements, Views
Labels:
None

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

blocks

MDEV-30073 Wrong result on 2nd execution of PS for query with NOT EXISTS

is caused by

MDEV-28602 Wrong result with outer join, merged derived table and view

relates to

MDEV-30073 Wrong result on 2nd execution of PS for query with NOT EXISTS

MDEV-31003 Second execution for ps-protocol

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.