No rows returned with (flat, BNLH join) in maria-5.3-mwl128
Bug #671901 reported by
Philip Stoev
This bug affects 1 person
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
MariaDB |
Fix Released
|
High
|
Igor Babaev |
Bug Description
The following query
SELECT table1.col_int_key FROM t1 AS table1 JOIN t2 AS table2 ON table1.
returns no rows when executed with (flat, BNLH join) , but returns rows when executed with full table scan and classical nested loop join.
Related branches
Changed in maria: | |
status: | New → Confirmed |
importance: | Undecided → High |
status: | Confirmed → In Progress |
Changed in maria: | |
status: | In Progress → Fix Committed |
Changed in maria: | |
status: | Fix Committed → Fix Released |
To post a comment you must log in.
Test case:
--disable_warnings
DROP TABLE /*! IF EXISTS */ t1;
DROP TABLE /*! IF EXISTS */ t2;
--enable_warnings
CREATE TABLE t1 ( 10_utf8_ key varchar(10) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, 1024_utf8_ key varchar(1024) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, 10_utf8_ key (col_varchar_ 10_utf8_ key), 1024_utf8_ key (col_varchar_ 1024_utf8_ key(255) ) 5,'abcdefjhjkl' ),('was' ,-1631322112, 'no'),( 'are',3, 'abcdefjhjkl' ),('abcdefjhjkl ',3,'w' ),('abcdefjhjkl ',4,'x' ),('tell' ,-824573952, 'abcdefjhjkl' ),('t', 0,'r'), ('v',-171101388 8,'the' ),('abcdefjhjkl ',1015414784, 'x'),(' or',4,' o'),('now' ,0,'have' ),('abcdefjhjkl ',-32702464, 'h'),(' abcdefjhjkl' ,4,'abcdefjhjkl '),('time' ,1078394880, 'abcdefjhjkl' ),('f', 4,'j'), ('m',-184555929 6,'abcdefjhjkl' ),('abcdefjhjkl ',-1074397184, 'something' );
col_varchar_
col_int_key int(11) DEFAULT NULL,
col_varchar_
KEY col_varchar_
KEY col_int_key (col_int_key),
KEY col_varchar_
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO t1 VALUES ('did',
CREATE TABLE t2 ( 10_utf8_ key varchar(10) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, 1024_utf8_ key varchar(1024) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, 10_utf8_ key (col_varchar_ 10_utf8_ key), 1024_utf8_ key (col_varchar_ 1024_utf8_ key(333) ) ,8,'k') ,('abcdefjhjkl' ,-575340544, 'abcdefjhjkl' ),('some' ,2,'because' ),('of' ,-517472256, 'f'),(' h',5,'abcdefjhj kl'),(' mean',7, 'z');
col_varchar_
col_int_key int(11) DEFAULT NULL,
col_varchar_
KEY col_varchar_
KEY col_int_key (col_int_key),
KEY col_varcohar_
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO t2 VALUES ('abcdefjhjkl'
SET SESSION join_cache_level = 6; switch= 'join_cache_ bka=off' ;
SET SESSION join_buffer_size = 1024;
SET SESSION optimizer_
SELECT table1.col_int_key FROM t1 AS table1 JOIN t2 AS table2 ON table1. col_varchar_ 10_utf8_ key = table2. col_varchar_ 1024_utf8_ key ; col_varchar_ 10_utf8_ key = table2. col_varchar_ 1024_utf8_ key ;
EXPLAIN SELECT table1.col_int_key FROM t1 AS table1 JOIN t2 AS table2 ON table1.
SET SESSION join_cache_level=0; col_varchar_ 10_utf8_ key = table2. col_varchar_ 1024_utf8_ key; col_varchar_ 10_utf8_ key = table2. col_varchar_ 1024_utf8_ key;
ALTER TABLE t1 DISABLE KEYS;
ALTER TABLE t2 DISABLE KEYS;
SELECT table1.col_int_key FROM t1 AS table1 JOIN t2 AS table2 ON table1.
EXPLAIN SELECT table1.col_int_key FROM t1 AS table1 JOIN t2 AS table2 ON table1.
DROP TABLE t1;
DROP TABLE t2;