Wrong results with " Range checked for each record" in maria-5.3
Bug #669423 reported by
Philip Stoev
This bug affects 1 person
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
MariaDB |
Fix Released
|
Undecided
|
Igor Babaev |
Bug Description
In maria 5.3, the following query:
SELECT table2 .col_int_key field3 FROM t1 table1 JOIN t1 table2 ON table2 .col_int_key >= table1 .col_int_key AND table2 .pk < table1 .col_int_nokey ;
returns no rows when the query plan contains "Range checked for each record". In maria-5.2 and for plans containing "join buffer", the query returns rows.
So, this seems to be a regression outside of the join cache code.
Changed in maria: | |
status: | Fix Committed → Fix Released |
To post a comment you must log in.
--disable_warnings
DROP TABLE /*! IF EXISTS */ t1;
--enable_warnings
CREATE TABLE t1 ( key,col_ int_key) 'v'),(11, 3,8,'f' ),(12,3, 5,'v'), (13,2,8, 's'),(14, 1,8,'a' ),(15,0, 6,'p'), (16,8,7, 'z'),(17, 5,2,'a' ),(18,9, 5,'h'), (19,5,7, 'h'),(20, 4,2,'v' ),(21,2, 9,'v'), (22,33, 142,'b' ),(23,5, 3,'y'), (24,1,0, 'v'),(25, 9,3,'m' ),(26,1, 5,'z'), (27,3,9, 'n'),(28, 8,1,'d' ),(29,231, 107,'a' );
pk int(11) NOT NULL AUTO_INCREMENT,
col_int_nokey int(11) NOT NULL,
col_int_key int(11) NOT NULL,
col_varchar_key varchar(1) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
PRIMARY KEY (pk),
KEY col_int_key (col_int_key),
KEY col_varchar_key (col_varchar_
) ENGINE=Aria AUTO_INCREMENT=30 DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1;
INSERT INTO t1 VALUES (10,3,8,
SET SESSION join_cache_level = 7;
SET SESSION join_buffer_size = 102400;
SELECT table2 .col_int_key field3 FROM t1 table1 JOIN t1 table2 ON table2 .col_int_key >= table1 .col_int_key AND table2 .pk < table1 .col_int_nokey ;
EXPLAIN SELECT table2 .col_int_key field3 FROM t1 table1 JOIN t1 table2 ON table2 .col_int_key >= table1 .col_int_key AND table2 .pk < table1 .col_int_nokey ;
SELECT table2 .col_int_key field3 FROM t1 table1 JOIN t1 table2 ON table2 .col_int_key >= table1 .col_int_key AND table2 .pk < table1 .col_int_nokey GROUP BY field3 ;
EXPLAIN SELECT table2 .col_int_key field3 FROM t1 table1 JOIN t1 table2 ON table2 .col_int_key >= table1 .col_int_key AND table2 .pk < table1 .col_int_nokey GROUP BY field3 ;
SELECT table2 .col_int_key field3 FROM t1 table1 JOIN t1 table2 ON table2 .col_int_key >= table1 .col_int_key AND table2 .pk < table1 .col_int_nokey GROUP BY field3 LIMIT 1 ;
EXPLAIN SELECT table2 .col_int_key field3 FROM t1 table1 JOIN t1 table2 ON table2 .col_int_key >= table1 .col_int_key AND table2 .pk < table1 .col_int_nokey GROUP BY field3 LIMIT 1 ;