Wrong result (0 instead of NULL) on 2nd execution of PS with LEFT JOIN, TEMPTABLE view

Description

The problem appeared on the 5.5 tree with the following revision:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 revno: 3922 [merge] revision-id: sanja@askmonty.org-20130925141613-wyi3pdd0viulaxtd parent: wlad@montyprogram.com-20130924175251-2wvjo1i166cacwam parent: sanja@askmonty.org-20130925123013-qbytshoda82jzqkn committer: sanja@askmonty.org branch nick: work-maria-5.5-merge-5.3 timestamp: Wed 2013-09-25 17:16:13 +0300 message: merge 5.3 -> 5.5 ------------------------------------------------------------ revno: 2502.567.143 revision-id: sanja@askmonty.org-20130925123013-qbytshoda82jzqkn parent: bar@mnogosearch.org-20130916120355-atoza9gj16selqtp committer: sanja@askmonty.org branch nick: work-maria-5.3-MDEV-5039 timestamp: Wed 2013-09-25 15:30:13 +0300 message: MDEV-5039: incorrect Item_func_regex::update_used_tables() Other fix of maybe_null problem and revert of revno: 3608 "MDEV-3873 & MDEV-3876 & MDEV-3912 : Wrong result (extra rows) with ALL subquery from a MERGE view."

However it doesn't seem to be reproducible on 5.3 tree after the merged revision.

Test case:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 CREATE TABLE t1 (a INT) ENGINE=MyISAM; INSERT INTO t1 VALUES (0),(8); CREATE TABLE t2 (pk INT PRIMARY KEY) ENGINE=MyISAM; CREATE ALGORITHM=TEMPTABLE VIEW v2 AS SELECT * FROM t2; SELECT SUM(pk) FROM t1 LEFT JOIN v2 ON a = pk; PREPARE stmt FROM "SELECT SUM(pk) FROM t1 LEFT JOIN v2 ON a = pk"; EXECUTE stmt; EXECUTE stmt; DEALLOCATE PREPARE stmt; DROP VIEW v2; DROP TABLE t1, t2;

Actual result:

1 2 3 4 5 6 7 8 9 10 SELECT SUM(pk) FROM t1 LEFT JOIN v2 ON a = pk; SUM(pk) NULL PREPARE stmt FROM "SELECT SUM(pk) FROM t1 LEFT JOIN v2 ON a = pk"; EXECUTE stmt; SUM(pk) NULL EXECUTE stmt; SUM(pk) 0

Expected result:

1 2 3 4 5 6 7 8 9 10 SELECT SUM(pk) FROM t1 LEFT JOIN v2 ON a = pk; SUM(pk) NULL PREPARE stmt FROM "SELECT SUM(pk) FROM t1 LEFT JOIN v2 ON a = pk"; EXECUTE stmt; SUM(pk) NULL EXECUTE stmt; SUM(pk) NULL

Environment

None

Status

Assignee

Oleksandr Byelkin

Reporter

Elena Stepanova

Labels

None

External issue ID

None

External issue ID

None

Fix versions

Affects versions

10.0.6
5.5.34

Priority

Major
Configure