Re: Wrong results with " Range checked for each record" in maria-5.3
--disable_warnings
DROP TABLE /*! IF EXISTS */ t1;
--enable_warnings

CREATE TABLE t1 (
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_key,col_int_key)
) ENGINE=Aria AUTO_INCREMENT=30 DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1;
INSERT INTO t1 VALUES (10,3,8,'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');

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 ;

Re: [Bug 669423] [NEW] Wrong results with " Range checked for each record" in maria-5.3
On 11/01/2010 05:26 AM, Philip Stoev wrote:
> Public bug reported:
>
> 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.
>
> ** Affects: maria
> Importance: Undecided
> Assignee: Igor Babaev (igorb-seattle)
> Status: New
>
>
> ** Tags: regression rqg
>
>
Philip,
It does not make sense to report each bug: 669423 obviously is duplicate
of 669420.

Regards,
Igor.

Re: Wrong results with " Range checked for each record" in maria-5.3
This bug was fixed by the patch for bug #669420

Launchpad bug id: 669423