Test case

--source include/have_innodb.inc

SET SESSION join_cache_level = 8;
CREATE TABLE `F` (
  `col_varchar_10_utf8_key` varchar(10) CHARACTER SET utf8 DEFAULT NULL,
  `col_varchar_10_latin1` varchar(10) DEFAULT NULL,
  `col_int` int(11) DEFAULT NULL,
  `col_int_key` int(11) DEFAULT NULL,
  KEY `col_varchar_10_utf8_key` (`col_varchar_10_utf8_key`),
  KEY `col_int_key` (`col_int_key`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `F` VALUES ('BCOIA','going',9,76939264);
INSERT INTO `F` VALUES ('get','v',NULL,NULL);
INSERT INTO `F` VALUES ('itrnayglbw','q',NULL,3);
INSERT INTO `F` VALUES ('ouitrnaygl','QDKGF',9,NULL);
INSERT INTO `F` VALUES ('KBWOG','look',NULL,NULL);
INSERT INTO `F` VALUES ('ehyouitrna','YITGO',4,8);
INSERT INTO `F` VALUES ('c','fvwpehyoui',8,9);
INSERT INTO `F` VALUES ('glswfvwpeh','u',5,5);
INSERT INTO `F` VALUES ('w','qaysglswfv',NULL,6);
INSERT INTO `F` VALUES ('VNVAA','r',NULL,349831168);
CREATE TABLE `N` (
  `col_int_key` int(11) DEFAULT NULL,
  `col_int` int(11) DEFAULT NULL,
  `col_varchar_10_utf8_key` varchar(10) CHARACTER SET utf8 DEFAULT NULL,
  `col_varchar_10_latin1` varchar(10) DEFAULT NULL,
  KEY `col_int_key` (`col_int_key`),
  KEY `col_varchar_10_utf8_key` (`col_varchar_10_utf8_key`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `N` VALUES (4,-1707802624,'wwhanhcjzz','fwwhanhcjz');
INSERT INTO `N` VALUES (-1268908032,NULL,'d','think');
INSERT INTO `N` VALUES (0,3,'j','was');
INSERT INTO `N` VALUES (8,2,'ngyrowgyfw','LZUOE');
INSERT INTO `N` VALUES (0,-1214578688,'s','don\'t');
INSERT INTO `N` VALUES (1,8,'ohfngyrowg','oohfngyrow');
INSERT INTO `N` VALUES (3,NULL,'and','gvoohfngyr');
INSERT INTO `N` VALUES (4,NULL,'x','yeah');
INSERT INTO `N` VALUES (NULL,NULL,'his','x');
INSERT INTO `N` VALUES (1012334592,-1012072448,'rayypigvoo','s');
INSERT INTO `N` VALUES (NULL,1132986368,'up','PRYNJ');
INSERT INTO `N` VALUES (1552613376,374341632,'a','a');
INSERT INTO `N` VALUES (NULL,653000704,'joafdrayyp','YVHIF');
INSERT INTO `N` VALUES (-392691712,NULL,'g','zxjoafdray');
INSERT INTO `N` VALUES (4,3,'cszxjoafdr','you');
INSERT INTO `N` VALUES (NULL,NULL,'q','or');
INSERT INTO `N` VALUES (-1692925952,4,'SNFCA','DSVSL');
INSERT INTO `N` VALUES (NULL,0,'qooagbcszx','HLPLE');
INSERT INTO `N` VALUES (2,6,'q','p');
INSERT INTO `N` VALUES (1318977536,8,'j','j');
INSERT INTO `N` VALUES (-190382080,8,'your','did');
INSERT INTO `N` VALUES (NULL,3,'p','say');
INSERT INTO `N` VALUES (1045889024,NULL,'gxmugamnqo','ztgxmugamn');
INSERT INTO `N` VALUES (NULL,-2013134848,'v','eztgxmugam');
INSERT INTO `N` VALUES (NULL,NULL,'j','would');
INSERT INTO `N` VALUES (NULL,-345636864,'nzcheztgxm','TBDRG');
INSERT INTO `N` VALUES (NULL,-975044608,'the','don\'t');
INSERT INTO `N` VALUES (-445382656,NULL,'no','ZXTXS');
INSERT INTO `N` VALUES (1,NULL,'back','NGNHE');
INSERT INTO `N` VALUES (-1165623296,0,'JNXCI','d');
INSERT INTO `N` VALUES (1406402560,-473628672,'u','SJCZQ');
INSERT INTO `N` VALUES (4,NULL,'a','ddhapqmoun');
INSERT INTO `N` VALUES (9,NULL,'GJNDI','q');
INSERT INTO `N` VALUES (NULL,NULL,'OVWKC','s');
INSERT INTO `N` VALUES (317849600,157155328,'l','they');
INSERT INTO `N` VALUES (567214080,-895811584,'MJLVP','QGUQV');
INSERT INTO `N` VALUES (5,NULL,'kdsziddhap','BFMUG');
INSERT INTO `N` VALUES (-2038169600,981467136,'d','jkdsziddha');
INSERT INTO `N` VALUES (-1323630592,980025344,'gjkdsziddh','AYTIF');
INSERT INTO `N` VALUES (5,-223019008,'ZMSFF','GZQUN');
INSERT INTO `N` VALUES (249167872,NULL,'ok','be');
INSERT INTO `N` VALUES (2,NULL,'ANDFC','s');
INSERT INTO `N` VALUES (NULL,1,'to','NXQPP');
INSERT INTO `N` VALUES (NULL,7,'yeah','was');
INSERT INTO `N` VALUES (2,-1532755968,'ujxdqbdyol','QAOWK');
INSERT INTO `N` VALUES (1,1704656896,'unfrujxdqb','nunfrujxdq');
INSERT INTO `N` VALUES (120258560,NULL,'ZNLOR','for');
INSERT INTO `N` VALUES (-349569024,NULL,'JCQJV','are');
INSERT INTO `N` VALUES (NULL,2067726336,'LNTXE','I');
INSERT INTO `N` VALUES (1035796480,-176553984,'see','HIYLX');
INSERT INTO `N` VALUES (-685506560,8,'can\'t','about');
INSERT INTO `N` VALUES (NULL,5,'BXUZW','my');
INSERT INTO `N` VALUES (-73990144,-103350272,'i','v');
INSERT INTO `N` VALUES (-928972800,1593835520,'r','kpuazineob');
INSERT INTO `N` VALUES (1178861568,NULL,'you','t');
INSERT INTO `N` VALUES (5,1,'UGBQC','insmqkpuaz');
INSERT INTO `N` VALUES (NULL,990117888,'on','ZXBLK');
INSERT INTO `N` VALUES (NULL,NULL,'they','going');
INSERT INTO `N` VALUES (NULL,9,'out','epfkminsmq');
INSERT INTO `N` VALUES (NULL,5,'up','I\'m');
INSERT INTO `N` VALUES (1,-336723968,'z','will');
INSERT INTO `N` VALUES (-1871380480,6,'could','xvmsypepfk');
INSERT INTO `N` VALUES (-2098462720,9,'kjsxvmsype','pkjsxvmsyp');
INSERT INTO `N` VALUES (NULL,2,'tvpkjsxvms','w');
INSERT INTO `N` VALUES (NULL,NULL,'a','YIBLV');
INSERT INTO `N` VALUES (NULL,3,'MAOHE','trkztvpkjs');
INSERT INTO `N` VALUES (1773076480,8,'was','xzbtrkztvp');
INSERT INTO `N` VALUES (5,NULL,'FVREI','s');
INSERT INTO `N` VALUES (0,5,'yhosxzbtrk','s');
INSERT INTO `N` VALUES (NULL,0,'s','SKHZQ');
INSERT INTO `N` VALUES (9,2,'l','how');
INSERT INTO `N` VALUES (-758054912,7,'jdxryhosxz','nwjdxryhos');
INSERT INTO `N` VALUES (3,1,'onwjdxryho','JNPZQ');
INSERT INTO `N` VALUES (-1594359808,NULL,'qonwjdxryh','dqonwjdxry');
INSERT INTO `N` VALUES (-1544421376,NULL,'GHIEZ','fyedqonwjd');
CREATE TABLE `P` (
  `col_varchar_10_latin1` varchar(10) DEFAULT NULL,
  `col_int_key` int(11) DEFAULT NULL,
  `col_int` int(11) DEFAULT NULL,
  `col_varchar_10_utf8_key` varchar(10) CHARACTER SET utf8 DEFAULT NULL,
  KEY `col_int_key` (`col_int_key`),
  KEY `col_varchar_10_utf8_key` (`col_varchar_10_utf8_key`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `P` VALUES ('when',5,8,'CPFNH');
INSERT INTO `P` VALUES ('o',9,4,'BICZS');
INSERT INTO `P` VALUES ('jtwujigcwi',2,5,'q');
INSERT INTO `P` VALUES ('for',6,1831141376,'IBFIG');
INSERT INTO `P` VALUES ('j',8,1,'back');
INSERT INTO `P` VALUES ('tell',2129461248,8,'xfbwwowjtw');
INSERT INTO `P` VALUES ('DPROS',NULL,2,'esfxfbwwow');
INSERT INTO `P` VALUES ('t',8,1884094464,'SPWAL');
INSERT INTO `P` VALUES ('BJAWJ',-201064448,624492544,'as');
INSERT INTO `P` VALUES ('MPHUR',2,NULL,'WYDPM');
INSERT INTO `P` VALUES ('CIHPU',NULL,8,'y');
INSERT INTO `P` VALUES ('something',7,8,'of');
INSERT INTO `P` VALUES ('DBAET',-2049048576,1,'ZHEZG');
INSERT INTO `P` VALUES ('sicljqxqes',1012531200,6,'as');
INSERT INTO `P` VALUES ('JHCOF',NULL,-952958976,'gsicljqxqe');
INSERT INTO `P` VALUES ('TLSGC',NULL,-1302069248,'jgsicljqxq');
INSERT INTO `P` VALUES ('ZTFHC',6,4,'bdjgsicljq');
INSERT INTO `P` VALUES ('I\'m',NULL,-1233584128,'s');
INSERT INTO `P` VALUES ('n',-1000079360,-1595801600,'y');
INSERT INTO `P` VALUES ('a',7,NULL,'tecmznkvog');
INSERT INTO `P` VALUES ('so',2,NULL,'back');
INSERT INTO `P` VALUES ('right',NULL,NULL,'hwbrjtecmz');
INSERT INTO `P` VALUES ('he\'s',3,NULL,'v');
INSERT INTO `P` VALUES ('p',NULL,8,'hfjhwbrjte');
INSERT INTO `P` VALUES ('q',6,NULL,'ehfjhwbrjt');
INSERT INTO `P` VALUES ('nzmehfjhwb',-188612608,NULL,'u');
INSERT INTO `P` VALUES ('blsnzmehfj',NULL,9,'just');
INSERT INTO `P` VALUES ('LIUBS',1039466496,NULL,'WJVXH');
INSERT INTO `P` VALUES ('AIPMB',135921664,4,'h');
INSERT INTO `P` VALUES ('really',5,4,'TWWKQ');
INSERT INTO `P` VALUES ('u',NULL,NULL,'RYROX');
INSERT INTO `P` VALUES ('well',1309409280,NULL,'WJKRH');
INSERT INTO `P` VALUES ('fnggphjsta',NULL,-499122176,'AOCAS');
INSERT INTO `P` VALUES ('QBYWW',NULL,NULL,'KFQAW');
INSERT INTO `P` VALUES ('they',NULL,NULL,'OIFGE');
INSERT INTO `P` VALUES ('n',-73465856,7,'ukgifnggph');
INSERT INTO `P` VALUES ('HLXDL',NULL,3,'urukgifngg');
INSERT INTO `P` VALUES ('CMBTY',2048720896,6,'g');
INSERT INTO `P` VALUES ('qvvrurukgi',NULL,-97189888,'how');
INSERT INTO `P` VALUES ('FKXYZ',-1112145920,0,'v');
INSERT INTO `P` VALUES ('I',1329397760,NULL,'DUTZE');
INSERT INTO `P` VALUES ('a',NULL,0,'v');
INSERT INTO `P` VALUES ('g',-362676224,2,'VMWBZ');
INSERT INTO `P` VALUES ('e',-1972240384,7,'j');
INSERT INTO `P` VALUES ('TJYPG',4,1,'you\'re');
INSERT INTO `P` VALUES ('y',NULL,NULL,'FAZUP');
INSERT INTO `P` VALUES ('DZLHR',3,708050944,'you\'re');
INSERT INTO `P` VALUES ('itdrehrtcg',NULL,1212088320,'oh');
INSERT INTO `P` VALUES ('xitdrehrtc',NULL,3,'r');
INSERT INTO `P` VALUES ('giyxitdreh',-1916010496,5,'i');
INSERT INTO `P` VALUES ('it',23003136,NULL,'c');
INSERT INTO `P` VALUES ('yeah',-269156352,2,'why');
INSERT INTO `P` VALUES ('wndshsgiyx',NULL,NULL,'j');
INSERT INTO `P` VALUES ('could',NULL,2,'MDOLU');
INSERT INTO `P` VALUES ('n',NULL,2,'me');
INSERT INTO `P` VALUES ('fruhwndshs',4,NULL,'pxfruhwnds');
INSERT INTO `P` VALUES ('j',8,7,'ttpxfruhwn');
INSERT INTO `P` VALUES ('k',-825360384,NULL,'right');
INSERT INTO `P` VALUES ('he',NULL,5,'icqtttpxfr');
INSERT INTO `P` VALUES ('dmiicqtttp',NULL,-196608,'oh');
INSERT INTO `P` VALUES ('h',1245184,NULL,'out');
INSERT INTO `P` VALUES ('z',1501822976,NULL,'in');
INSERT INTO `P` VALUES ('him',8,NULL,'g');
INSERT INTO `P` VALUES ('s',-294125568,NULL,'eoddmsdmii');
INSERT INTO `P` VALUES ('want',NULL,NULL,'meoddmsdmi');
INSERT INTO `P` VALUES ('v',761593856,1307770880,'ACMSI');
INSERT INTO `P` VALUES ('can',NULL,-343736320,'YLPWB');
INSERT INTO `P` VALUES ('are',7,6,'it\'s');
INSERT INTO `P` VALUES ('out',3,NULL,'h');
INSERT INTO `P` VALUES ('CFQCK',NULL,1535180800,'to');
INSERT INTO `P` VALUES ('OWJSG',-1661534208,6,'r');
INSERT INTO `P` VALUES ('p',NULL,-2106654720,'p');
INSERT INTO `P` VALUES ('o',NULL,-1563820032,'atbnvmtoiv');
INSERT INTO `P` VALUES ('HIPDT',4,-1836253184,'it');
INSERT INTO `P` VALUES ('f',NULL,1,'b');
INSERT INTO `P` VALUES ('DRVGW',NULL,9,'or');
INSERT INTO `P` VALUES ('huqlpqwatb',NULL,9,'QCUCD');
INSERT INTO `P` VALUES ('j',NULL,NULL,'v');
INSERT INTO `P` VALUES ('about',NULL,0,'o');
INSERT INTO `P` VALUES ('hbbdymhuql',-143785984,-687603712,'on');
INSERT INTO `P` VALUES ('a',0,5,'mean');
INSERT INTO `P` VALUES ('b',0,6,'and');
INSERT INTO `P` VALUES ('zponshbbdy',NULL,850526208,'all');
INSERT INTO `P` VALUES ('AQOUD',1,-1117782016,'SLUBL');
INSERT INTO `P` VALUES ('from',NULL,5,'maxunfazpo');
INSERT INTO `P` VALUES ('what',-1046413312,8,'from');
INSERT INTO `P` VALUES ('damaxunfaz',1036713984,4,'q');
INSERT INTO `P` VALUES ('OSNCR',1970143232,NULL,'know');
INSERT INTO `P` VALUES ('oxlndamaxu',NULL,NULL,'EPPAH');
INSERT INTO `P` VALUES ('ok',582156288,7,'q');
INSERT INTO `P` VALUES ('ytbqyjoxln',NULL,1,'ecytbqyjox');
INSERT INTO `P` VALUES ('tgiecytbqy',5,2,'hytgiecytb');
INSERT INTO `P` VALUES ('me',-1197277184,NULL,'x');
INSERT INTO `P` VALUES ('that\'s',NULL,1524432896,'EGBVE');
INSERT INTO `P` VALUES ('his',4,4,'svrhytgiec');
INSERT INTO `P` VALUES ('c',NULL,1,'d');
INSERT INTO `P` VALUES ('b',NULL,NULL,'come');
INSERT INTO `P` VALUES ('you\'re',2013528064,NULL,'PTCOC');
INSERT INTO `P` VALUES ('LDPDC',NULL,8,'could');
INSERT INTO `P` VALUES ('n',NULL,1025572864,'back');
CREATE TABLE `I` (
  `col_int_key` int(11) DEFAULT NULL,
  `col_varchar_10_latin1` varchar(10) DEFAULT NULL,
  `col_varchar_10_utf8_key` varchar(10) CHARACTER SET utf8 DEFAULT NULL,
  `col_int` int(11) DEFAULT NULL,
  KEY `col_int_key` (`col_int_key`),
  KEY `col_varchar_10_utf8_key` (`col_varchar_10_utf8_key`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `I` VALUES (NULL,'tgtqlobtqa','me',-827392000);
INSERT INTO `I` VALUES (-1445330944,'well','VPHFZ',-1853554688);
INSERT INTO `I` VALUES (-540803072,'vlaztgtqlo','rvlaztgtql',NULL);
INSERT INTO `I` VALUES (NULL,'your','x',NULL);
INSERT INTO `I` VALUES (NULL,'l','JAJVR',-1296039936);
INSERT INTO `I` VALUES (NULL,'sfoyrvlazt','OCZCX',1);
INSERT INTO `I` VALUES (NULL,'j','toksfoyrvl',NULL);
INSERT INTO `I` VALUES (-1375666176,'tjtoksfoyr','atjtoksfoy',1774125056);
INSERT INTO `I` VALUES (-1001062400,'u','v',NULL);
INSERT INTO `I` VALUES (NULL,'hnqxatjtok','u',5);
INSERT INTO `I` VALUES (7,'to','slhnqxatjt',NULL);
INSERT INTO `I` VALUES (617283584,'good','be',1549336576);
INSERT INTO `I` VALUES (8,'u','x',-327286784);
INSERT INTO `I` VALUES (986185728,'going','u',4);
INSERT INTO `I` VALUES (4,'hojnswsmyh','XCIHC',-2105933824);
INSERT INTO `I` VALUES (800325632,'for','VLOVA',5);
INSERT INTO `I` VALUES (NULL,'i','LGTNG',-1116733440);
INSERT INTO `I` VALUES (12189696,'i','she',7);
INSERT INTO `I` VALUES (-875823104,'JTRTH','at',NULL);
INSERT INTO `I` VALUES (NULL,'ajddcmshoj','out',1);
INSERT INTO `I` VALUES (-1514733568,'RBKRT','VLUMG',2);
INSERT INTO `I` VALUES (3,'FIWYO','really',3);
INSERT INTO `I` VALUES (4,'RKYIL','zmuuoajddc',-737345536);
INSERT INTO `I` VALUES (9,'IUPXW','QMOQT',-1832976384);
INSERT INTO `I` VALUES (NULL,'i','would',NULL);
CREATE TABLE `L` (
  `col_varchar_10_latin1` varchar(10) DEFAULT NULL,
  `col_int_key` int(11) DEFAULT NULL,
  `col_varchar_10_utf8_key` varchar(10) CHARACTER SET utf8 DEFAULT NULL,
  `col_int` int(11) DEFAULT NULL,
  KEY `col_int_key` (`col_int_key`),
  KEY `col_varchar_10_utf8_key` (`col_varchar_10_utf8_key`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `L` VALUES ('vawvtjsjmw',NULL,'FNUVW',9);
INSERT INTO `L` VALUES ('it',2063073280,'m',3);
INSERT INTO `L` VALUES ('do',1198981120,'KVZOJ',NULL);
INSERT INTO `L` VALUES ('n',334430208,'z',1386414080);
INSERT INTO `L` VALUES ('i',6,'with',-864681984);
INSERT INTO `L` VALUES ('think',5,'okay',-383713280);
INSERT INTO `L` VALUES ('krkpymvawv',-1671954432,'his',0);
INSERT INTO `L` VALUES ('WATCS',3,'JIIZH',0);
INSERT INTO `L` VALUES ('when',NULL,'bfmrhpkrkp',9);
INSERT INTO `L` VALUES ('a',0,'SYPYE',1255538688);
INSERT INTO `L` VALUES ('e',1262354432,'MIPDN',NULL);
INSERT INTO `L` VALUES ('IAFEM',2,'TPBBT',662437888);
INSERT INTO `L` VALUES ('b',NULL,'something',212664320);
INSERT INTO `L` VALUES ('s',2127167488,'I',NULL);
INSERT INTO `L` VALUES ('cfladpbfmr',5,'kncfladpbf',3);
INSERT INTO `L` VALUES ('from',NULL,'FKKYU',-1975123968);
INSERT INTO `L` VALUES ('oh',1457717248,'y',9);
INSERT INTO `L` VALUES ('qhcjlkncfl',NULL,'IZBYM',4);
INSERT INTO `L` VALUES ('x',-2074148864,'c',5);
INSERT INTO `L` VALUES ('e',-786300928,'q',1665794048);
INSERT INTO `L` VALUES ('this',NULL,'abceftjqhc',3);
INSERT INTO `L` VALUES ('wpabceftjq',-440074240,'c',289931264);
INSERT INTO `L` VALUES ('FGMQT',5,'xhkwpabcef',5);
INSERT INTO `L` VALUES ('qxhkwpabce',NULL,'well',1868103680);
INSERT INTO `L` VALUES ('t',-638320640,'xgqxhkwpab',185729024);
INSERT INTO `L` VALUES ('a',1325989888,'hfqxgqxhkw',NULL);
INSERT INTO `L` VALUES ('i',0,'BZETU',NULL);
INSERT INTO `L` VALUES ('AYGKR',5,'GVAZP',NULL);
INSERT INTO `L` VALUES ('now',3,'NHXTB',8);
INSERT INTO `L` VALUES ('qrpjhfqxgq',5,'well',NULL);
INSERT INTO `L` VALUES ('don\'t',NULL,'BYDXQ',NULL);
INSERT INTO `L` VALUES ('mean',2,'pzbfqrpjhf',1037893632);
INSERT INTO `L` VALUES ('dpzbfqrpjh',NULL,'g',NULL);
INSERT INTO `L` VALUES ('h',-1414463488,'POQLV',NULL);
INSERT INTO `L` VALUES ('j',NULL,'d',NULL);
INSERT INTO `L` VALUES ('g',NULL,'me',NULL);
INSERT INTO `L` VALUES ('iprkxvydpz',NULL,'j',8);
INSERT INTO `L` VALUES ('x',NULL,'n',-304873472);
INSERT INTO `L` VALUES ('v',-427884544,'have',NULL);
INSERT INTO `L` VALUES ('r',1338834944,'uvhszeyipr',NULL);
INSERT INTO `L` VALUES ('thuvhszeyi',-1928921088,'be',NULL);
INSERT INTO `L` VALUES ('zhthuvhsze',NULL,'then',-521535488);
INSERT INTO `L` VALUES ('s',NULL,'MMJUE',6);
INSERT INTO `L` VALUES ('vbwzhthuvh',NULL,'tvbwzhthuv',NULL);
INSERT INTO `L` VALUES ('you',-1019805696,'DOAVX',NULL);
INSERT INTO `L` VALUES ('yes',3,'atoxtvbwzh',-505806848);
INSERT INTO `L` VALUES ('oh',2115436544,'can\'t',-1777139712);
INSERT INTO `L` VALUES ('eatoxtvbwz',6,'BBFEJ',NULL);
INSERT INTO `L` VALUES ('x',8,'WELWD',737738752);
INSERT INTO `L` VALUES ('zrzoeatoxt',NULL,'ZEQJV',NULL);


SELECT table2 .`col_int`
FROM I  LEFT  JOIN N table2  ON table2 .`col_varchar_10_latin1`  LEFT  JOIN F table3  LEFT  JOIN L table4  JOIN P table6  ON table6 .`col_int`  ON table3 .`col_int_key`  = table4 .`col_int_key`  ON table2 .`col_varchar_10_latin1`  = table6 .`col_varchar_10_utf8_key`;