From f8a338a34b73bf7e233076f5959b545f122db331 Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Fri, 29 Oct 2010 18:59:39 -0700 Subject: Fixed bug #665049. The bug could cause wrong results for queries over Maria tables when index condition pushdown was used. --- mysql-test/t/maria_mrr.test | 57 +++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 57 insertions(+) create mode 100644 mysql-test/t/maria_mrr.test (limited to 'mysql-test/t/maria_mrr.test') diff --git a/mysql-test/t/maria_mrr.test b/mysql-test/t/maria_mrr.test new file mode 100644 index 00000000000..a4bb8eb9f9e --- /dev/null +++ b/mysql-test/t/maria_mrr.test @@ -0,0 +1,57 @@ +-- source include/have_maria.inc + +--disable_warnings +drop table if exists t1,t2,t3,t4; +--enable_warnings + +set @save_storage_engine= @@storage_engine; +set storage_engine=aria; + +--source include/mrr_tests.inc + +set storage_engine= @save_storage_engine; + +# +# Bug #665049: index condition pushdown with Maria +# + +CREATE TABLE t1( + pk int NOT NULL, i int NOT NULL, v varchar(1) NOT NULL, + PRIMARY KEY (pk), INDEX idx (v, i) +) ENGINE=ARIA; +INSERT INTO t1 VALUES + (1,9,'x'), (2,5,'g'), (3,1,'o'), (4,0,'g'), (5,1,'v'), + (6,190,'m'), (7,6,'x'), (8,3,'c'), (9,4,'z'), (10,3,'i'), + (11,186,'x'), (12,1,'g'), (13,8,'q'), (14,226,'m'), (15,133,'p'); + +CREATE TABLE t2( + pk int NOT NULL, i int NOT NULL, v varchar(1) NOT NULL, + PRIMARY KEY (pk), INDEX idx (v, i) +) ENGINE=ARIA; +INSERT INTO t2 SELECT * FROM t1; +INSERT INTO t2 VALUES (77, 333, 'z'); + +CREATE TABLE t3( + pk int NOT NULL, i int NOT NULL, v varchar(1) NOT NULL, + PRIMARY KEY (pk), INDEX idx (v, i) +) ENGINE=ARIA; +INSERT INTO t3 SELECT * FROM t1; +INSERT INTO t3 VALUES (88, 442, 'y'), (99, 445, 'w') ; + +SELECT COUNT(t1.v) FROM t1, t2 IGNORE INDEX (idx), t3 IGNORE INDEX (idx) + WHERE t3.v = t2.v AND t3.i < t2.i AND t3.pk > 0 AND t2.pk > 0; +EXPLAIN +SELECT COUNT(t1.v) FROM t1, t2 IGNORE INDEX (idx), t3 IGNORE INDEX (idx) + WHERE t3.v = t2.v AND t3.i < t2.i AND t3.pk > 0 AND t2.pk > 0; + +SELECT COUNT(t1.v) FROM t1, t2, t3 + WHERE t3.v = t2.v AND t3.i < t2.i AND t3.pk > 0 AND t2.pk > 0; +EXPLAIN + SELECT COUNT(t1.v) FROM t1, t2, t3 + WHERE t3.v = t2.v AND t3.i < t2.i AND t3.pk > 0 AND t2.pk > 0; + +DROP TABLE t1,t2,t3; + + + + -- cgit v1.2.1