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

XML

Word

Printable

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.