Less rows returned with (flat, BNLH join) in maria-5.3-mwl128

[Philip Stoev]

The following query:

SELECT table2.col_int FROM t1 AS table1 JOIN t2 AS table2 ON table1.col_varchar_1024_latin1_key = table2.col_varchar_1024_latin1_key ;

Returns one NULL row when executed with BNLH and 2 rows when executed with no indexes and full table scans.

[Philip Stoev (pstoev-askmonty)]

Test case. Substituting with shorter strings did not reproduce the error.

SET SESSION optimizer_use_mrr = 'force';
SET SESSION join_cache_level = 4;
SET SESSION join_buffer_size = 136;
SET SESSION debug = '';

CREATE TABLE t1 (
  col_varchar_1024_latin1_key varchar(1024) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  col_int int(11) DEFAULT NULL,
  KEY col_varchar_1024_latin1_key (col_varchar_1024_latin1_key(1000))
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO t1 VALUES ('g',-1908473856),('well',9),('lepjojqjimlkhzeovotuhuhzvsgjnsvdtiiwlpaggansqwqtbxtrpgkdpijbzurzgrlxvqbscsaimikytvjicemytfyewkryzhrkkjhsokfyddhrmywsxnmlhgivrtyenhxxtvvntghzdeungnbutdinrrouazkoxzmwerytbeulzoficenbzpmbqpudjultxifchvapbvcfzewzqwqsavgwcxlseeptrfbpgkknwkoyoqfleqqkcufztfbitxcpnkpxqzouvjmycxskadadukleyuszjahybahjvqqzoziqzaqbpkgqnjlkvfcjohurkugnbijejanippjcljmwirnkaklnonpxbnemrqzaoehmetdhqriwagigriyhwtmenggyhnsollaeffqovjygruxqksilutlnaputcactqxgkecbxegtzqhvmmrsbumbxxrhylxjiccfujvwhdihcnrnphyoversbmjcexzabqahgyqfgeiufpvdafhhngktujlmqphjblpzuqirupbphcncnlvdkpimgvsdzcpwbxskjnuqysnefooxbpdqbhmkzgfzcqsvzjdhnhuinutvyjdwjzlzgarwewpsxtrsuhvqlzhhgmgajtsioxgugvqdnfaseqhubzraycetcdfbhfkuevtfqippyrffbthlbslvsdkfbzljkfcjgetsjzbujldobktajagkujozwnrewtptteztiyfyqdmiifqvbhrfzbsjmxvaksteofjphnslkaeqtinmusqkhvuuqsvgaapaiqsqflovvigczaihpxgpluquhiqiikunfbffzumhuqmzayeokagcgqbplza',84738048),('dutlepjojqjimlkhzeovotuhuhzvsgjnsvdtiiwlpaggansqwqtbxtrpgkdpijbzurzgrlxvqbscsaimikytvjicemytfyewkryzhrkkjhsokfyddhrmywsxnmlhgivrtyenhxxtvvntghzdeungnbutdinrrouazkoxzmwerytbeulzoficenbzpmbqpudjultxifchvapbvcfzewzqwqsavgwcxlseeptrfbpgkknwkoyoqfleqqkcufztfbitxcpnkpxqzouvjmycxskadadukleyuszjahybahjvqqzoziqzaqbpkgqnjlkvfcjohurkugnbijejanippjcljmwirnkaklnonpxbnemrqzaoehmetdhqriwagigriyhwtmenggyhnsollaeffqovjygruxqksilutlnaputcactqxgkecbxegtzqhvmmrsbumbxxrhylxjiccfujvwhdihcnrnphyoversbmjcexzabqahgyqfgeiufpvdafhhngktujlmqphjblpzuqirupbphcncnlvdkpimgvsdzcpwbxskjnuqysnefooxbpdqbhmkzgfzcqsvzjdhnhuinutvyjdwjzlzgarwewpsxtrsuhvqlzhhgmgajtsioxgugvqdnfaseqhubzraycetcdfbhfkuevtfqippyrffbthlbslvsdkfbzljkfcjgetsjzbujldobktajagkujozwnrewtptteztiyfyqdmiifqvbhrfzbsjmxvaksteofjphnslkaeqtinmusqkhvuuqsvgaapaiqsqflovvigczaihpxgpluquhiqiikunfbffzumhuqmzayeokagcg',-1675952128),('p',NULL),('y',NULL),('n',NULL);
CREATE TABLE t2 (
  col_varchar_1024_latin1_key varchar(1024) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  col_int int(11) DEFAULT NULL,
  KEY col_varchar_1024_latin1_key (col_varchar_1024_latin1_key)
) ENGINE=Aria DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1;
INSERT INTO t2 VALUES ('when',NULL),('vjunrmtxglywtdtmwusmdxbriojriuccnafrsvlqlbyytqclzerbozxudgwmpokbwtipmkbfaxdkoyvoyggkmunoaqlghgvqvgtpmhrrpoqrooawysynbanetgeaomrrclghbblncocgnwrsvbrgnguuprasottgbhvyoekahjevtmkjxnbbkdsmyzrznuhzrstbyvmtfishmjgykujvmaierowakbicjivvbexvrrrmqvjlekuocqzoxjyvwkumgagpwxgdhizgzyrmenuyyiwnaskweuhqtpyetueqerpqoocaivmodcpkgyghkflynckocwoefvoktepnncwwnggahibbrbruaahyesswxfltorwihqchdqzrilxqppzqtrmzdantttpvwoixlxnjfgsrgoceajvaknetptkchfbbfshllxkanxjmikmojncahkfikzdgiqmplnh',NULL),('ilvjunrmtxglywtdtmwusmdxbriojriuccnafrsvlqlbyytqclzerbozxudgwmpokbwtipmkbfaxdkoyvoyggkmunoaqlghgvqvgtpmhrrpoqrooawysynbanetgeaomrrclghbblncoc...

[Philip Stoev (pstoev-askmonty)]

Better test case:

SET SESSION join_cache_level = 4;
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (
col_varchar_1024_latin1_key varchar(1024) COLLATE latin1_bin,
col_int int(11),
KEY (col_varchar_1024_latin1_key)) ENGINE=MyISAM;
INSERT INTO t1 VALUES ('dutl','-');
INSERT INTO t1 VALUES ('y',NULL);

DROP TABLE IF EXISTS t2;
CREATE TABLE t2 (
col_varchar_1024_latin1_key varchar(1024) COLLATE latin1_bin,
col_int int(11),
KEY (col_varchar_1024_latin1_key)) ENGINE=Aria;
INSERT INTO t2 VALUES ('y',NULL);
INSERT INTO t2 VALUES ('b','4');

SELECT table2.col_int FROM t1 AS table1 JOIN t2 AS table2 ON table1.col_varchar_1024_latin1_key = table2.col_varchar_1024_latin1_key;