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

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."

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;

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

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