Comment 3 for bug 665669

A much simpler test case:

--source include/have_innodb.inc

SET SESSION join_cache_level=0;
SET SESSION optimizer_use_mrr='disable';

CREATE TABLE `B` ( `col_varchar_10_latin1` varchar(10) CHARACTER SET latin1, `col_varchar_10_latin1_key` varchar(10) CHARACTER SET latin1, pk integer auto_increment, `col_varchar_1024_utf8_key` varchar(10) CHARACTER SET utf8, `col_varchar_10_utf8_key` varchar(10) CHARACTER SET utf8, `col_int` int, `col_varchar_10_utf8` varchar(10) CHARACTER SET utf8, `col_varchar_1024_latin1` varchar(10) CHARACTER SET latin1, `col_int_key` int, `col_varchar_1024_latin1_key` varchar(10) CHARACTER SET latin1, `col_varchar_1024_utf8` varchar(10) CHARACTER SET utf8, /*Indices*/ key (`col_varchar_10_latin1_key` ), primary key (pk), key (`col_varchar_1024_utf8_key` ), key (`col_varchar_10_utf8_key` ), key (`col_int_key` ), key (`col_varchar_1024_latin1_key` )) ENGINE=innodb;
CREATE TABLE `R` ( `col_varchar_1024_latin1_key` varchar(10) CHARACTER SET latin1, pk integer auto_increment, `col_varchar_1024_latin1` varchar(10) CHARACTER SET latin1, `col_int` int, `col_varchar_1024_utf8` varchar(10) CHARACTER SET utf8, `col_int_key` int, `col_varchar_10_utf8` varchar(10) CHARACTER SET utf8, `col_varchar_1024_utf8_key` varchar(10) CHARACTER SET utf8, `col_varchar_10_latin1` varchar(10) CHARACTER SET latin1, `col_varchar_10_latin1_key` varchar(10) CHARACTER SET latin1, `col_varchar_10_utf8_key` varchar(10) CHARACTER SET utf8, /*Indices*/ key (`col_varchar_1024_latin1_key` ), primary key (pk), key (`col_int_key` ), key (`col_varchar_1024_utf8_key` ), key (`col_varchar_10_latin1_key` ), key (`col_varchar_10_utf8_key` )) ENGINE=innodb;
INSERT /*! IGNORE */ INTO R VALUES ('d', NULL, 'abcdefghij', 0, 'of', 1, 'k', 'g', 'w', 'h', 'they') ,
 ('l', NULL, 'abcdefghij', -1023016960, 'time', 4, 'abcdefghij', 'w', 'abcdefghijabcdefghij', 'been', 'abcdefghijonchz'),
  ('with', NULL, 's', 2139095040, 'lwvynbkqxkcpq', 6, 'so', 'abcdefghij', 'on', 'abcdefghij', 'abcdefghij') ,
  ('now', NULL, 'good', 2, 'y', -1275002880, 'b', 'well', 'then', 'abcdefghij', 'abcdefghij');

CREATE TABLE `V` ( `col_varchar_1024_latin1` varchar(10) CHARACTER SET latin1, `col_int` int, `col_int_key` int, `col_varchar_1024_utf8_key` varchar(10) CHARACTER SET utf8, `col_varchar_10_utf8` varchar(10) CHARACTER SET utf8, `col_varchar_1024_utf8` varchar(10) CHARACTER SET utf8, `col_varchar_1024_latin1_key` varchar(10) CHARACTER SET latin1, pk integer auto_increment, `col_varchar_10_utf8_key` varchar(10) CHARACTER SET utf8, `col_varchar_10_latin1` varchar(10) CHARACTER SET latin1, `col_varchar_10_latin1_key` varchar(10) CHARACTER SET latin1, /*Indices*/ key (`col_int_key` ), key (`col_varchar_1024_utf8_key` ), key (`col_varchar_1024_latin1_key` ), primary key (pk), key (`col_varchar_10_utf8_key` ), key (`col_varchar_10_latin1_key` )) ENGINE=innodb;

INSERT /*! IGNORE */ INTO V VALUES ('abcdefghij', -1185611776, 6, 'abcdefghij', 'i', 'o', 'do', NULL, 'abcdefghij', 'c', 'o')
, ('q', 1823145984, -1234632704, 'ok', 'your', 'all', 'r', NULL, 'a', 't', 'abcdefghij')
, ('e', 749993984, -799604736, 'abcdefghij', 'abcdefghij', 'b', 'for', NULL, 't', 'n', 'abcdefghij') ;

CREATE TABLE `Z` ( `col_int_key` int, `col_varchar_1024_utf8_key` varchar(10) CHARACTER SET utf8, `col_int` int, `col_varchar_10_latin1_key` varchar(10) CHARACTER SET latin1, `col_varchar_1024_latin1` varchar(10) CHARACTER SET latin1, `col_varchar_10_latin1` varchar(10) CHARACTER SET latin1, `col_varchar_10_utf8` varchar(10) CHARACTER SET utf8, `col_varchar_1024_latin1_key` varchar(10) CHARACTER SET latin1, `col_varchar_10_utf8_key` varchar(10) CHARACTER SET utf8, `col_varchar_1024_utf8` varchar(10) CHARACTER SET utf8, pk integer auto_increment, /*Indices*/ key (`col_int_key` ), key (`col_varchar_1024_utf8_key` ), key (`col_varchar_10_latin1_key` ), key (`col_varchar_1024_latin1_key` ), key (`col_varchar_10_utf8_key` ), primary key (pk)) ENGINE=innodb;

INSERT /*! IGNORE */ INTO Z VALUES (-810876928, 'o', -783155200, 's', 'because', 'is', 'c', 'well', 'c', 'n', NULL)
, (947060736, 'like', -1097269248, 'ok', 'abcdefghij', 'abcdefghij', 'abcdefghij', 'q', 'be', 'up', NULL)
, (803209216, 'w', 0, 'he', 'abcdefghij', 'r', 'abcdefghij', 'had', 't', 'now', NULL) ;

CREATE TABLE `EE` ( `col_varchar_10_latin1_key` varchar(10) CHARACTER SET latin1, `col_int` int, `col_varchar_1024_utf8_key` varchar(10) CHARACTER SET utf8, `col_varchar_1024_latin1` varchar(10) CHARACTER SET latin1, `col_varchar_1024_utf8` varchar(10) CHARACTER SET utf8, `col_int_key` int, `col_varchar_10_latin1` varchar(10) CHARACTER SET latin1, `col_varchar_10_utf8` varchar(10) CHARACTER SET utf8, `col_varchar_10_utf8_key` varchar(10) CHARACTER SET utf8, pk integer auto_increment, `col_varchar_1024_latin1_key` varchar(10) CHARACTER SET latin1, /*Indices*/ key (`col_varchar_10_latin1_key` ), key (`col_varchar_1024_utf8_key` ), key (`col_int_key` ), key (`col_varchar_10_utf8_key` ), primary key (pk), key (`col_varchar_1024_latin1_key` )) ENGINE=myisam;
INSERT /*! IGNORE */ INTO EE VALUES ('as', 6, 'abcdefghij', 'abcdefghij', 's', -1884815360, 'r', 'abcdefghij', 'abcdefghij', NULL, 'abcdefghij')
, ('been', 2, 'abcdefghij', 'y', 'abcdefghij', 673906688, 'it', 'n', 'abcdefghij', NULL, 'abcdefghij')
;

--error 0
 SELECT *
  FROM B AS table1
 LEFT JOIN R AS table2 ON table1 .`col_varchar_10_utf8_key` = table2 .`col_varchar_1024_latin1_key`
  WHERE table2 .`col_int_key` > 2;

--error 0
CREATE TABLE p1 AS
 SELECT table1 .`col_int` field1 , table2 .`col_int` field2 , table1 .`col_int` field3
  FROM V table1
  RIGHT JOIN EE table2
 JOIN Z table3 ON table2 .`col_int_key`
 ON table1 .`pk` = table3 .`pk`
 LEFT JOIN R AS table4
 ON table3 .`pk` = table4 .`col_int`
 WHERE table4 .`pk` <> 3 ;

--error 0

CREATE TABLE p2 AS
 SELECT table1 .`col_int` field1 , table2 .`col_int` field2 , table1 .`col_int` field3
  FROM V table1
  RIGHT JOIN EE table2
 JOIN Z table3 ON table2 .`col_int_key`
 ON table1 .`pk` = table3 .`pk`
 LEFT JOIN R AS table4
 ON table3 .`pk` = table4 .`col_int`
 WHERE table4 .`pk` <> 3 ;

--let $diff_table_1 = test.p1
--let $diff_table_2 = test.p2
--source include/diff_tables.inc