Diverging results in maria-5.3-mwl128-dsmrr-cpk with join buffer (incremental, BKA join)
Bug #670417 reported by
Philip Stoev
This bug affects 1 person
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
MariaDB |
Fix Released
|
Undecided
|
Sergey Petrunia |
Bug Description
The following query
SELECT table2.
Returns 999 rows when executed with BKA in maria-5.
Related branches
Changed in maria: | |
status: | New → Invalid |
Changed in maria: | |
status: | Invalid → Fix Committed |
Changed in maria: | |
assignee: | nobody → Sergey Petrunia (sergefp) |
Changed in maria: | |
status: | Fix Committed → Fix Released |
To post a comment you must log in.
Test case:
--disable_ abort_on_ error merge=on, index_merge_ union=on, index_merge_ sort_union= on,index_ merge_intersect ion=on, index_condition _pushdown= on,firstmatch= on,loosescan= on,materializat ion=on, semijoin= on,partial_ match_rowid_ merge=on, partial_ match_table_ scan=on, subquery_ cache=on, mrr_sort_ keys=on, outer_join_ with_cache= off,semijoin_ with_cache= off,join_ cache_increment al=on,join_ cache_hashed= on,join_ cache_bka= on,table_ elimination= on'; abort_on_ error
SET SESSION optimizer_switch = 'index_
SET SESSION optimizer_use_mrr = 'force';
SET SESSION join_cache_level = 6;
SET SESSION join_buffer_size = 136;
SET SESSION debug = '';
--enable_
--disable_warnings
DROP TABLE /*! IF EXISTS */ t1;
DROP TABLE /*! IF EXISTS */ t2;
--enable_warnings
CREATE TABLE t1 ( key,col_ int_key) v','v') ,(11,8, 'f','f' ),(12,5, 'v','v' ),(13,8, 's','s' ),(14,8, 'a','a' ),(15,6, 'p','p' ),(16,7, 'z','z' ),(17,2, 'a','a' ),(18,5, 'h','h' ),(19,7, 'h','h' ),(20,2, 'v','v' ),(21,9, 'v','v' ),(22,142, 'b','b' ),(23,3, 'y','y' ),(24,0, 'v','v' ),(25,3, 'm','m' ),(26,5, 'z','z' ),(27,9, 'n','n' ),(28,1, 'd','d' ),(29,107, 'a','a' ); key,col_ int_key) ,'x'),( 2,5,'g' ,'g'),( 3,1,'o' ,'o'),( 4,0,'g' ,'g'),( 5,1,'v' ,'v'),( 6,190,' m','m') ,(7,6,' x','x') ,(8,3,' c','c') ,(9,4,' z','z') ,(10,3, 'i','i' ),(11,186, 'x','x' ),(12,1, 'g','g' ),(13,8, 'q','q' ),(14,226, 'm','m' ),(15,133, 'p','p' ),(16,6, 'e','e' ),(17,3, 't','t' ),(18,8, 'j','j' ),(19,5, 'h','h' ),(20,7, 'w','w' );
pk int(11) NOT NULL AUTO_INCREMENT,
col_int_key int(11) NOT NULL,
col_varchar_key varchar(1) NOT NULL,
col_varchar_nokey varchar(1) NOT NULL,
PRIMARY KEY (pk),
KEY col_int_key (col_int_key),
KEY col_varchar_key (col_varchar_
) ENGINE=MyISAM AUTO_INCREMENT=30 DEFAULT CHARSET=latin1;
INSERT INTO t1 VALUES (10,8,'
CREATE TABLE t2 (
pk int(11) NOT NULL AUTO_INCREMENT,
col_int_key int(11) NOT NULL,
col_varchar_key varchar(1) NOT NULL,
col_varchar_nokey varchar(1) NOT NULL,
PRIMARY KEY (pk),
KEY col_int_key (col_int_key),
KEY col_varchar_key (col_varchar_
) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=latin1;
INSERT INTO t2 VALUES (1,9,'x'
SELECT COUNT(table2. col_varchar_ key) FROM t2 STRAIGHT_JOIN ( t1 AS table2 JOIN t1 AS table3 ON table3.pk ) ON table3. col_varchar_ key = table2. col_varchar_ key AND table3. col_varchar_ key = table2. col_varchar_ nokey ; col_varchar_ key) FROM t2 STRAIGHT_JOIN ( t1 AS table2 JOIN t1 AS table3 ON table3.pk ) ON table3. col_varchar_ key = table2. col_varchar_ key AND table3. col_varchar_ key = table2. col_varchar_ nokey ;
EXPLAIN SELECT COUNT(table2.