Wrong result (NULLs instead of real values) with TEMPTABLE view, LEFT JOIN

revno: 3793 [merge]

revision-id: igor@askmonty.org-20140804170551-76ohozgq308a9f3a

parent: sergii@pisem.net-20140801100455-jtqd7ofg3xwau6j1

parent: igor@askmonty.org-20140801051743-7i2297y96icyx9vx

committer: Igor Babaev <igor@askmonty.org>

branch nick: maria-5.3

timestamp: Mon 2014-08-04 10:05:51 -0700

message:

  Merge.

    ------------------------------------------------------------

    revno: 3774.1.1

    revision-id: igor@askmonty.org-20140801051743-7i2297y96icyx9vx

    parent: psergey@askmonty.org-20140318080632-c1pql218bfrx26y2

    committer: Igor Babaev <igor@askmonty.org>

    branch nick: maria-5.3-mdev5721

    timestamp: Thu 2014-07-31 22:17:43 -0700

    message:

      Fixed bug mdev-5721.

      Do not define a look-up key for a temporary table if its length

      exceeds the maximum length of such keys. 

CREATE TABLE t1 (i INT, state VARCHAR(1024)) ENGINE=MyISAM;

INSERT INTO t1 VALUES (2,'Louisiana'),(9,'Maine');

CREATE TABLE t2 (state VARCHAR(1024), j INT) ENGINE=MyISAM;

CREATE OR REPLACE ALGORITHM=TEMPTABLE VIEW v2 AS SELECT * FROM t2;

INSERT INTO t2 VALUES 

('Louisiana',9),('Alaska',5);

SELECT * FROM t1 AS t1_1 LEFT JOIN ( t1 AS t1_2 INNER JOIN v2 ON t1_2.i = j ) ON t1_1.state = v2.state;

DROP VIEW v2;

DROP TABLE t1, t2;

i	state	i	state	state	j

2	Louisiana	9	Maine	Louisiana	9

9	Maine	NULL	NULL	NULL	NULL

i	state	i	state	state	j

2	Louisiana	NULL	NULL	NULL	NULL

9	Maine	NULL	NULL	NULL	NULL