diff options
| author | unknown <ibabaev@bk-internal.mysql.com> | 2007-05-12 23:42:36 +0200 |
|---|---|---|
| committer | unknown <ibabaev@bk-internal.mysql.com> | 2007-05-12 23:42:36 +0200 |
| commit | de34385a565a07536f314813e02f63aec1d0f369 (patch) | |
| tree | 59d9e31c78caca8be26a8746fa5e83ac54aabb1a /mysql-test/t/innodb_mysql.test | |
| parent | 0f6e518a3ba975d84ef192ee295bf330368855b3 (diff) | |
| parent | 3d501aece0f68183d0a74aa37d0938ccd91733ac (diff) | |
| download | mariadb-git-clone-5.0.42-build.tar.gz | |
Merge bk-internal.mysql.com:/data0/bk/mysql-5.0clone-5.0.42-build
into bk-internal.mysql.com:/data0/bk/mysql-5.0-opt
Diffstat (limited to 'mysql-test/t/innodb_mysql.test')
| -rw-r--r-- | mysql-test/t/innodb_mysql.test | 54 |
1 files changed, 54 insertions, 0 deletions
diff --git a/mysql-test/t/innodb_mysql.test b/mysql-test/t/innodb_mysql.test index c9e1de8c3ab..90f9047291c 100644 --- a/mysql-test/t/innodb_mysql.test +++ b/mysql-test/t/innodb_mysql.test @@ -518,4 +518,58 @@ select * from t1; drop table t1; +# +# Bug #28189: optimizer erroniously prefers ref access to range access +# for an InnoDB table +# + +CREATE TABLE t1( + id int AUTO_INCREMENT PRIMARY KEY, + stat_id int NOT NULL, + acct_id int DEFAULT NULL, + INDEX idx1 (stat_id, acct_id), + INDEX idx2 (acct_id) +) ENGINE=MyISAM; + +CREATE TABLE t2( + id int AUTO_INCREMENT PRIMARY KEY, + stat_id int NOT NULL, + acct_id int DEFAULT NULL, + INDEX idx1 (stat_id, acct_id), + INDEX idx2 (acct_id) +) ENGINE=InnoDB; + +INSERT INTO t1(stat_id,acct_id) VALUES + (1,759), (2,831), (3,785), (4,854), (1,921), + (1,553), (2,589), (3,743), (2,827), (2,545), + (4,779), (4,783), (1,597), (1,785), (4,832), + (1,741), (1,833), (3,788), (2,973), (1,907); + +INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1; +INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1; +INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1; +INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1; +INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1; +INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1; +INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1; +INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1; +INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1; +INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1; +INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1; +UPDATE t1 SET acct_id=785 + WHERE MOD(stat_id,2)=0 AND MOD(id,stat_id)=MOD(acct_id,stat_id); +OPTIMIZE TABLE t1; + +SELECT COUNT(*) FROM t1; +SELECT COUNT(*) FROM t1 WHERE acct_id=785; + +EXPLAIN SELECT COUNT(*) FROM t1 WHERE stat_id IN (1,3) AND acct_id=785; + +INSERT INTO t2 SELECT * FROM t1; +OPTIMIZE TABLE t2; + +EXPLAIN SELECT COUNT(*) FROM t2 WHERE stat_id IN (1,3) AND acct_id=785; + +DROP TABLE t1,t2; + --echo End of 5.0 tests |