We're updating the issue view to help you get more done. 

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