diff options
Diffstat (limited to 'mysql-test/t')
27 files changed, 194 insertions, 2883 deletions
diff --git a/mysql-test/t/innodb_cache-master.opt b/mysql-test/t/cache_innodb-master.opt index 5f0ebff98f6..5f0ebff98f6 100644 --- a/mysql-test/t/innodb_cache-master.opt +++ b/mysql-test/t/cache_innodb-master.opt diff --git a/mysql-test/t/cache_innodb.test b/mysql-test/t/cache_innodb.test new file mode 100644 index 00000000000..33a328d1d6c --- /dev/null +++ b/mysql-test/t/cache_innodb.test @@ -0,0 +1,16 @@ +# t/cache_innodb.test +# +# Last update: +# 2006-07-26 ML test refactored (MySQL 5.1) +# main code t/innodb_cache.test --> include/query_cache.inc +# new wrapper t/cache_innodb.test +# + +--source include/have_query_cache.inc + +--source include/have_innodb.inc +let $engine_type= InnoDB; +# InnoDB supports FOREIGN KEYs +let $test_foreign_keys= 1; + +--source include/query_cache.inc diff --git a/mysql-test/t/innodb_concurrent-master.opt b/mysql-test/t/concurrent_innodb-master.opt index f76bada5208..f76bada5208 100644 --- a/mysql-test/t/innodb_concurrent-master.opt +++ b/mysql-test/t/concurrent_innodb-master.opt diff --git a/mysql-test/t/concurrent_innodb.test b/mysql-test/t/concurrent_innodb.test new file mode 100644 index 00000000000..5e9258af8f1 --- /dev/null +++ b/mysql-test/t/concurrent_innodb.test @@ -0,0 +1,20 @@ +# t/concurrent_innodb.test +# +# Concurrent InnoDB tests, mainly in UPDATE's +# Bug#3300 +# Designed and tested by Sinisa Milivojevic, sinisa@mysql.com +# +# two non-interfering UPDATE's not changing result set +# +# Last update: +# 2006-07-26 ML test refactored (MySQL 5.1) +# main code t/innodb_concurrent.test -> include/concurrent.inc +# new wrapper t/concurrent_innodb.test + +# test takes circa 5 minutes to run, so it's big +--source include/big_test.inc + +--source include/have_innodb.inc +let $engine_type= InnoDB; + +--source include/concurrent.inc diff --git a/mysql-test/t/deadlock_innodb.test b/mysql-test/t/deadlock_innodb.test new file mode 100644 index 00000000000..08e3e256790 --- /dev/null +++ b/mysql-test/t/deadlock_innodb.test @@ -0,0 +1,16 @@ +# t/deadlock_innodb.test +# +# +# Last update: +# 2006-07-26 ML test refactored (MySQL 5.1) +# main code --> include/deadlock.inc +# new wrapper t/deadlock_innodb.test +# + +# Can't test this with embedded server +-- source include/not_embedded.inc + +--source include/have_innodb.inc +let $engine_type= InnoDB; + +--source include/deadlock.inc diff --git a/mysql-test/t/disabled.def b/mysql-test/t/disabled.def index cf387f7acee..4a905fb8840 100644 --- a/mysql-test/t/disabled.def +++ b/mysql-test/t/disabled.def @@ -16,6 +16,7 @@ #im_instance_conf : Bug#20294 2006-06-06 monty Instance manager test im_instance_conf fails randomly im_options : Bug#20294 2006-07-24 stewart Instance manager test im_instance_conf fails randomly #im_life_cycle : Bug#20368 2006-06-10 alik im_life_cycle test fails +concurrent_innodb : BUG#21579 2006-08-11 mleich innodb_concurrent random failures with varying differences ndb_autodiscover : BUG#18952 2006-02-16 jmiller Needs to be fixed w.r.t binlog ndb_autodiscover2 : BUG#18952 2006-02-16 jmiller Needs to be fixed w.r.t binlog ndb_binlog_ignore_db : BUG#21279 2006-07-25 ingo Randomly throws a warning diff --git a/mysql-test/t/handler.test b/mysql-test/t/handler.test deleted file mode 100644 index a7f1eeaa2cc..00000000000 --- a/mysql-test/t/handler.test +++ /dev/null @@ -1,428 +0,0 @@ -# -# test of HANDLER ... -# - -# should work in embedded server after mysqltest is fixed --- source include/not_embedded.inc - ---disable_warnings -drop table if exists t1,t3,t4,t5; ---enable_warnings - -create table t1 (a int, b char(10), key a(a), key b(a,b)); -insert into t1 values -(17,"ddd"),(18,"eee"),(19,"fff"),(19,"yyy"), -(14,"aaa"),(15,"bbb"),(16,"ccc"),(16,"xxx"), -(20,"ggg"),(21,"hhh"),(22,"iii"); -handler t1 open as t2; --- error 1064 -handler t2 read a=(SELECT 1); -handler t2 read a first; -handler t2 read a next; -handler t2 read a next; -handler t2 read a prev; -handler t2 read a last; -handler t2 read a prev; -handler t2 read a prev; - -handler t2 read a first; -handler t2 read a prev; - -handler t2 read a last; -handler t2 read a prev; -handler t2 read a next; -handler t2 read a next; - -handler t2 read a=(15); -handler t2 read a=(16); - ---error 1070 -handler t2 read a=(19,"fff"); - -handler t2 read b=(19,"fff"); -handler t2 read b=(19,"yyy"); -handler t2 read b=(19); - ---error 1109 -handler t1 read a last; - -handler t2 read a=(11); -handler t2 read a>=(11); - -handler t2 read a=(18); -handler t2 read a>=(18); -handler t2 read a>(18); -handler t2 read a<=(18); -handler t2 read a<(18); - -handler t2 read a first limit 5; -handler t2 read a next limit 3; -handler t2 read a prev limit 10; - -handler t2 read a>=(16) limit 4; -handler t2 read a>=(16) limit 2,2; -handler t2 read a last limit 3; - -handler t2 read a=(19); -handler t2 read a=(19) where b="yyy"; - -handler t2 read first; -handler t2 read next; -handler t2 read next; ---error 1064 -handler t2 read last; -handler t2 close; - -# -# DROP TABLE / ALTER TABLE -# -handler t1 open as t2; -drop table t1; -create table t1 (a int); -insert into t1 values (17); ---error 1109 -handler t2 read first; -handler t1 open as t2; -alter table t1 engine=MyISAM; ---error 1109 -handler t2 read first; -drop table t1; - -# -# Test case for the bug #787 -# -create table t1 (a int); -insert into t1 values (1),(2),(3),(4),(5),(6); -delete from t1 limit 2; -handler t1 open; -handler t1 read first; -handler t1 read first limit 1,1; -handler t1 read first limit 2,2; -delete from t1 limit 3; -handler t1 read first; -drop table t1; - -# -# Test for #751 -# -create table t1(a int, index(a)); -insert into t1 values (1), (2), (3); -handler t1 open; ---error 1054 -handler t1 read a=(W); ---error 1210 -handler t1 read a=(a); -drop table t1; -# -# BUG#2304 -# -create table t1 (a char(5)); -insert into t1 values ("Ok"); -handler t1 open as t; -handler t read first; -use mysql; -handler t read first; -handler t close; -handler test.t1 open as t; -handler t read first; -handler t close; -use test; -drop table t1; - -# -# BUG#3649 -# -create table t1 ( a int, b int, INDEX a (a) ); -insert into t1 values (1,2), (2,1); -handler t1 open; -handler t1 read a=(1) where b=2; -handler t1 read a=(1) where b=3; -handler t1 read a=(1) where b=1; -handler t1 close; -drop table t1; - -# -# Check if two database names beginning the same are seen as different. -# -# This database begins like the usual 'test' database. -# ---disable_warnings -drop database if exists test_test; ---enable_warnings -create database test_test; -use test_test; -create table t1(table_id char(20) primary key); -insert into t1 values ('test_test.t1'); -insert into t1 values (''); -handler t1 open; -handler t1 read first limit 9; -create table t2(table_id char(20) primary key); -insert into t2 values ('test_test.t2'); -insert into t2 values (''); -handler t2 open; -handler t2 read first limit 9; -# -# This is the usual 'test' database. -# -use test; ---disable_warnings -drop table if exists t1; ---enable_warnings -create table t1(table_id char(20) primary key); -insert into t1 values ('test.t1'); -insert into t1 values (''); ---error 1066 -handler t1 open; -# -# Check accesibility of all the tables. -# -use test; ---error 1064 -handler test.t1 read first limit 9; ---error 1064 -handler test_test.t1 read first limit 9; -handler t1 read first limit 9; ---error 1064 -handler test_test.t2 read first limit 9; -handler t2 read first limit 9; - -# -# Cleanup. -# - ---error 1064 -handler test_test.t1 close; -handler t1 close; -drop table test_test.t1; ---error 1064 -handler test_test.t2 close; -handler t2 close; -drop table test_test.t2; -drop database test_test; - -# -use test; ---error 1064 -handler test.t1 close; ---error 1109 -handler t1 close; -drop table test.t1; - -# -# BUG#4335 -# ---disable_warnings -drop database if exists test_test; -drop table if exists t1; -drop table if exists t2; -drop table if exists t3; ---enable_warnings -create database test_test; -use test_test; -create table t1 (c1 char(20)); -insert into t1 values ('test_test.t1'); -create table t3 (c1 char(20)); -insert into t3 values ('test_test.t3'); -handler t1 open; -handler t1 read first limit 9; -handler t1 open h1; -handler h1 read first limit 9; -use test; -create table t1 (c1 char(20)); -create table t2 (c1 char(20)); -create table t3 (c1 char(20)); -insert into t1 values ('t1'); -insert into t2 values ('t2'); -insert into t3 values ('t3'); ---error 1066 -handler t1 open; ---error 1066 -handler t2 open t1; ---error 1066 -handler t3 open t1; -handler t1 read first limit 9; ---error 1064 -handler test.t1 close; ---error 1066 -handler test.t1 open h1; ---error 1066 -handler test_test.t1 open h1; -handler test_test.t3 open h3; -handler test.t1 open h2; -handler t1 read first limit 9; -handler h1 read first limit 9; -handler h2 read first limit 9; -handler h3 read first limit 9; -handler h2 read first limit 9; ---error 1064 -handler test.h1 close; -handler t1 close; -handler h1 close; -handler h2 close; ---error 1109 -handler t1 read first limit 9; ---error 1109 -handler h1 read first limit 9; ---error 1109 -handler h2 read first limit 9; -handler h3 read first limit 9; -handler h3 read first limit 9; -use test_test; -handler h3 read first limit 9; ---error 1064 -handler test.h3 read first limit 9; -handler h3 close; -use test; -drop table t3; -drop table t2; -drop table t1; -drop database test_test; - -# -# Test if fix for BUG#4286 correctly closes handler tables. -# -create table t1 (c1 char(20)); -insert into t1 values ("t1"); -handler t1 open as h1; -handler h1 read first limit 9; -create table t2 (c1 char(20)); -insert into t2 values ("t2"); -handler t2 open as h2; -handler h2 read first limit 9; -create table t3 (c1 char(20)); -insert into t3 values ("t3"); -handler t3 open as h3; -handler h3 read first limit 9; -create table t4 (c1 char(20)); -insert into t4 values ("t4"); -handler t4 open as h4; -handler h4 read first limit 9; -create table t5 (c1 char(20)); -insert into t5 values ("t5"); -handler t5 open as h5; -handler h5 read first limit 9; -# close first -alter table t1 engine=MyISAM; ---error 1109 -handler h1 read first limit 9; -handler h2 read first limit 9; -handler h3 read first limit 9; -handler h4 read first limit 9; -handler h5 read first limit 9; -# close last -alter table t5 engine=MyISAM; ---error 1109 -handler h1 read first limit 9; -handler h2 read first limit 9; -handler h3 read first limit 9; -handler h4 read first limit 9; ---error 1109 -handler h5 read first limit 9; -# close middle -alter table t3 engine=MyISAM; ---error 1109 -handler h1 read first limit 9; -handler h2 read first limit 9; ---error 1109 -handler h3 read first limit 9; -handler h4 read first limit 9; ---error 1109 -handler h5 read first limit 9; -handler h2 close; -handler h4 close; -# close all depending handler tables -handler t1 open as h1_1; -handler t1 open as h1_2; -handler t1 open as h1_3; -handler h1_1 read first limit 9; -handler h1_2 read first limit 9; -handler h1_3 read first limit 9; -alter table t1 engine=MyISAM; ---error 1109 -handler h1_1 read first limit 9; ---error 1109 -handler h1_2 read first limit 9; ---error 1109 -handler h1_3 read first limit 9; -drop table t1; -drop table t2; -drop table t3; -drop table t4; -drop table t5; - -# -# Bug#14397 - OPTIMIZE TABLE with an open HANDLER causes a crash -# -create table t1 (c1 int); -insert into t1 values (1); -# client 1 -handler t1 open; -handler t1 read first; -# client 2 -connect (con2,localhost,root,,); -connection con2; ---exec echo send the below to another connection, do not wait for the result -send optimize table t1; ---sleep 1 -# client 1 ---exec echo proceed with the normal connection -connection default; -handler t1 read next; -handler t1 close; -# client 2 ---exec echo read the result from the other connection -connection con2; -reap; -# client 1 ---exec echo proceed with the normal connection -connection default; -drop table t1; - -# End of 4.1 tests - -# -# Addendum to Bug#14397 - OPTIMIZE TABLE with an open HANDLER causes a crash -# Show that DROP TABLE can no longer deadlock against -# FLUSH TABLES WITH READ LOCK. This is a 5.0 issue. -# -create table t1 (c1 int); -insert into t1 values (14397); -flush tables with read lock; -# The thread with the global read lock cannot drop the table itself: ---error 1223 -drop table t1; -# -# client 2 -# We need a second connection to try the drop. -# The drop waits for the global read lock to go away. -# Without the addendum fix it locked LOCK_open before entering the wait loop. -connection con2; ---exec echo send the below to another connection, do not wait for the result -send drop table t1; ---sleep 1 -# -# client 1 -# Now we need something that wants LOCK_open. A simple table access which -# opens the table does the trick. ---exec echo proceed with the normal connection -connection default; -# This would hang on LOCK_open without the 5.0 addendum fix. -select * from t1; -# Release the read lock. This should make the DROP go through. -unlock tables; -# -# client 2 -# Read the result of the drop command. -connection con2; ---exec echo read the result from the other connection -reap; -# -# client 1 -# Now back to normal operation. The table should not exist any more. ---exec echo proceed with the normal connection -connection default; ---error 1146 -select * from t1; -# Just to be sure and not confuse the next test case writer. -drop table if exists t1; - diff --git a/mysql-test/t/handler_innodb.test b/mysql-test/t/handler_innodb.test new file mode 100644 index 00000000000..7f9fdb8de56 --- /dev/null +++ b/mysql-test/t/handler_innodb.test @@ -0,0 +1,18 @@ +# t/handler_innodb.test +# +# test of HANDLER ... +# +# Last update: +# 2006-07-31 ML test refactored (MySQL 5.1) +# code of t/handler.test and t/innodb_handler.test united +# main testing code put into include/handler.inc +# rename t/innodb_handler.test to t/handler_innodb.test +# + +# should work in embedded server after mysqltest is fixed +--source include/not_embedded.inc + +--source include/have_innodb.inc +let $engine_type= InnoDB; + +--source include/handler.inc diff --git a/mysql-test/t/handler_myisam.test b/mysql-test/t/handler_myisam.test new file mode 100644 index 00000000000..90acfae1311 --- /dev/null +++ b/mysql-test/t/handler_myisam.test @@ -0,0 +1,17 @@ +# t/handler_myisam.test +# +# test of HANDLER ... +# +# Last update: +# 2006-07-31 ML test refactored (MySQL 5.1) +# code of t/handler.test and t/innodb_handler.test united +# main testing code put into include/handler.inc +# rename t/handler.test to t/handler_myisam.test +# + +# should work in embedded server after mysqltest is fixed +--source include/not_embedded.inc + +let $engine_type= MyISAM; + +--source include/handler.inc diff --git a/mysql-test/t/index_merge.test b/mysql-test/t/index_merge.test deleted file mode 100644 index 3da5711bf7a..00000000000 --- a/mysql-test/t/index_merge.test +++ /dev/null @@ -1,385 +0,0 @@ -# -# Index merge tests -# ---disable_warnings -drop table if exists t0, t1, t2, t3, t4; ---enable_warnings - -# Create and fill a table with simple keys -create table t0 -( - key1 int not null, - INDEX i1(key1) -); - ---disable_query_log -insert into t0 values (1),(2),(3),(4),(5),(6),(7),(8); - -let $1=7; -set @d=8; -while ($1) -{ - eval insert into t0 select key1+@d from t0; - eval set @d=@d*2; - dec $1; -} ---enable_query_log - -alter table t0 add key2 int not null, add index i2(key2); -alter table t0 add key3 int not null, add index i3(key3); -alter table t0 add key4 int not null, add index i4(key4); -alter table t0 add key5 int not null, add index i5(key5); -alter table t0 add key6 int not null, add index i6(key6); -alter table t0 add key7 int not null, add index i7(key7); -alter table t0 add key8 int not null, add index i8(key8); - -update t0 set key2=key1,key3=key1,key4=key1,key5=key1,key6=key1,key7=key1,key8=1024-key1; -analyze table t0; - -# 1. One index -explain select * from t0 where key1 < 3 or key1 > 1020; - -# 2. Simple cases -explain -select * from t0 where key1 < 3 or key2 > 1020; -select * from t0 where key1 < 3 or key2 > 1020; - -explain select * from t0 where key1 < 3 or key2 <4; - -explain -select * from t0 where (key1 > 30 and key1<35) or (key2 >32 and key2 < 40); -select * from t0 where (key1 > 30 and key1<35) or (key2 >32 and key2 < 40); - -# 3. Check that index_merge doesn't break "ignore/force/use index" -explain select * from t0 ignore index (i2) where key1 < 3 or key2 <4; -explain select * from t0 where (key1 < 3 or key2 <4) and key3 = 50; -explain select * from t0 use index (i1,i2) where (key1 < 3 or key2 <4) and key3 = 50; - -explain select * from t0 where (key1 > 1 or key2 > 2); -explain select * from t0 force index (i1,i2) where (key1 > 1 or key2 > 2); - - -# 4. Check if conjuncts are grouped by keyuse -explain - select * from t0 where key1<3 or key2<3 or (key1>5 and key1<8) or - (key1>10 and key1<12) or (key2>100 and key2<110); - -# 5. Check index_merge with conjuncts that are always true/false -# verify fallback to "range" if there is only one non-confluent condition -explain select * from t0 where key2 = 45 or key1 <=> null; - -explain select * from t0 where key2 = 45 or key1 is not null; -explain select * from t0 where key2 = 45 or key1 is null; - -# the last conj. is always false and will be discarded -explain select * from t0 where key2=10 or key3=3 or key4 <=> null; - -# the last conj. is always true and will cause 'all' scan -explain select * from t0 where key2=10 or key3=3 or key4 is null; - -# some more complicated cases -explain select key1 from t0 where (key1 <=> null) or (key2 < 5) or - (key3=10) or (key4 <=> null); -explain select key1 from t0 where (key1 <=> null) or (key1 < 5) or - (key3=10) or (key4 <=> null); - -# 6.Several ways to do index_merge, (ignored) index_merge vs. range -explain select * from t0 where - (key1 < 3 or key2 < 3) and (key3 < 4 or key4 < 4) and (key5 < 5 or key6 < 5); - -explain -select * from t0 where (key1 < 3 or key2 < 6) and (key1 < 7 or key3 < 4); - -select * from t0 where (key1 < 3 or key2 < 6) and (key1 < 7 or key3 < 4); - - -explain select * from t0 where - (key1 < 3 or key2 < 3) and (key3 < 4 or key4 < 4) and (key5 < 2 or key6 < 2); - -# now index_merge is not used at all when "range" is possible -explain select * from t0 where - (key1 < 3 or key2 < 3) and (key3 < 100); - -# this even can cause "all" scan: -explain select * from t0 where - (key1 < 3 or key2 < 3) and (key3 < 1000); - - -# 7. Complex cases -# tree_or(List<SEL_IMERGE>, range SEL_TREE). -explain select * from t0 where - ((key1 < 4 or key2 < 4) and (key2 <5 or key3 < 4)) - or - key2 > 5; - -explain select * from t0 where - ((key1 < 4 or key2 < 4) and (key2 <5 or key3 < 4)) - or - key1 < 7; - -select * from t0 where - ((key1 < 4 or key2 < 4) and (key2 <5 or key3 < 4)) - or - key1 < 7; - -# tree_or(List<SEL_IMERGE>, List<SEL_IMERGE>). -explain select * from t0 where - ((key1 < 4 or key2 < 4) and (key3 <5 or key5 < 4)) - or - ((key5 < 5 or key6 < 6) and (key7 <7 or key8 < 4)); - -explain select * from t0 where - ((key3 <5 or key5 < 4) and (key1 < 4 or key2 < 4)) - or - ((key7 <7 or key8 < 4) and (key5 < 5 or key6 < 6)); - -explain select * from t0 where - ((key3 <5 or key5 < 4) and (key1 < 4 or key2 < 4)) - or - ((key3 <7 or key5 < 2) and (key5 < 5 or key6 < 6)); - -explain select * from t0 where - ((key3 <5 or key5 < 4) and (key1 < 4 or key2 < 4)) - or - (((key3 <7 and key7 < 6) or key5 < 2) and (key5 < 5 or key6 < 6)); - -explain select * from t0 where - ((key3 <5 or key5 < 4) and (key1 < 4 or key2 < 4)) - or - ((key3 >=5 or key5 < 2) and (key5 < 5 or key6 < 6)); - -explain select * from t0 force index(i1, i2, i3, i4, i5, i6 ) where - ((key3 <5 or key5 < 4) and (key1 < 4 or key2 < 4)) - or - ((key3 >=5 or key5 < 2) and (key5 < 5 or key6 < 6)); - -# 8. Verify that "order by" after index merge uses filesort -select * from t0 where key1 < 5 or key8 < 4 order by key1; - -explain -select * from t0 where key1 < 5 or key8 < 4 order by key1; - -# 9. Check that index_merge cost is compared to 'index' where possible -create table t2 like t0; -insert into t2 select * from t0; - -alter table t2 add index i1_3(key1, key3); -alter table t2 add index i2_3(key2, key3); -alter table t2 drop index i1; -alter table t2 drop index i2; -alter table t2 add index i321(key3, key2, key1); - -# index_merge vs 'index', index_merge is better. -explain select key3 from t2 where key1 = 100 or key2 = 100; - -# index_merge vs 'index', 'index' is better. -explain select key3 from t2 where key1 <100 or key2 < 100; - -# index_merge vs 'all', index_merge is better. -explain select key7 from t2 where key1 <100 or key2 < 100; - -# 10. Multipart keys. -create table t4 ( - key1a int not null, - key1b int not null, - key2 int not null, - key2_1 int not null, - key2_2 int not null, - key3 int not null, - - index i1a (key1a, key1b), - index i1b (key1b, key1a), - - index i2_1(key2, key2_1), - index i2_2(key2, key2_1) -); - -insert into t4 select key1,key1,key1 div 10, key1 % 10, key1 % 10, key1 from t0; - -# the following will be handled by index_merge: -select * from t4 where key1a = 3 or key1b = 4; -explain select * from t4 where key1a = 3 or key1b = 4; - -# and the following will not -explain select * from t4 where key2 = 1 and (key2_1 = 1 or key3 = 5); - -explain select * from t4 where key2 = 1 and (key2_1 = 1 or key2_2 = 5); - -explain select * from t4 where key2_1 = 1 or key2_2 = 5; - - -# 11. Multitable selects -create table t1 like t0; -insert into t1 select * from t0; - -# index_merge on first table in join -explain select * from t0 left join t1 on (t0.key1=t1.key1) - where t0.key1=3 or t0.key2=4; - -select * from t0 left join t1 on (t0.key1=t1.key1) - where t0.key1=3 or t0.key2=4; - -explain -select * from t0,t1 where (t0.key1=t1.key1) and ( t0.key1=3 or t0.key2=4); - -# index_merge vs. ref -explain -select * from t0,t1 where (t0.key1=t1.key1) and - (t0.key1=3 or t0.key2=4) and t1.key1<200; - -# index_merge vs. ref -explain -select * from t0,t1 where (t0.key1=t1.key1) and - (t0.key1=3 or t0.key2<4) and t1.key1=2; - -# index_merge on second table in join -explain select * from t0,t1 where t0.key1 = 5 and - (t1.key1 = t0.key1 or t1.key8 = t0.key1); - -# Fix for bug#1974 -explain select * from t0,t1 where t0.key1 < 3 and - (t1.key1 = t0.key1 or t1.key8 = t0.key1); - -# index_merge inside union -explain select * from t1 where key1=3 or key2=4 - union select * from t1 where key1<4 or key3=5; - -# index merge in subselect -explain select * from (select * from t1 where key1 = 3 or key2 =3) as Z where key8 >5; - -# 12. check for long index_merges. -create table t3 like t0; -insert into t3 select * from t0; -alter table t3 add key9 int not null, add index i9(key9); -alter table t3 add keyA int not null, add index iA(keyA); -alter table t3 add keyB int not null, add index iB(keyB); -alter table t3 add keyC int not null, add index iC(keyC); -update t3 set key9=key1,keyA=key1,keyB=key1,keyC=key1; - -explain select * from t3 where - key1=1 or key2=2 or key3=3 or key4=4 or - key5=5 or key6=6 or key7=7 or key8=8 or - key9=9 or keyA=10 or keyB=11 or keyC=12; - -select * from t3 where - key1=1 or key2=2 or key3=3 or key4=4 or - key5=5 or key6=6 or key7=7 or key8=8 or - key9=9 or keyA=10 or keyB=11 or keyC=12; - -# Test for Bug#3183 -explain select * from t0 where key1 < 3 or key2 < 4; -select * from t0 where key1 < 3 or key2 < 4; - -update t0 set key8=123 where key1 < 3 or key2 < 4; -select * from t0 where key1 < 3 or key2 < 4; - -delete from t0 where key1 < 3 or key2 < 4; -select * from t0 where key1 < 3 or key2 < 4; -select count(*) from t0; - -# Test for BUG#4177 -drop table t4; -create table t4 (a int); -insert into t4 values (1),(4),(3); -set @save_join_buffer_size=@@join_buffer_size; -set join_buffer_size= 4000; -explain select max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5) - from t0 as A force index(i1,i2), t0 as B force index (i1,i2) - where (A.key1 < 500000 or A.key2 < 3) - and (B.key1 < 500000 or B.key2 < 3); - -select max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5) - from t0 as A force index(i1,i2), t0 as B force index (i1,i2) - where (A.key1 < 500000 or A.key2 < 3) - and (B.key1 < 500000 or B.key2 < 3); - -update t0 set key1=1; -explain select max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5) - from t0 as A force index(i1,i2), t0 as B force index (i1,i2) - where (A.key1 = 1 or A.key2 = 1) - and (B.key1 = 1 or B.key2 = 1); - -select max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5) - from t0 as A force index(i1,i2), t0 as B force index (i1,i2) - where (A.key1 = 1 or A.key2 = 1) - and (B.key1 = 1 or B.key2 = 1); - -alter table t0 add filler1 char(200), add filler2 char(200), add filler3 char(200); -update t0 set key2=1, key3=1, key4=1, key5=1,key6=1,key7=1 where key7 < 500; - -# The next query will not use index i7 in intersection if the OS doesn't -# support file sizes > 2GB. (ha_myisam::ref_length depends on this and index -# scan cost estimates depend on ha_myisam::ref_length) ---replace_column 9 # ---replace_result "4,4,4,4,4,4,4" X "4,4,4,4,4,4" X "i6,i7" "i6,i7?" "i6" "i6,i7?" -explain select max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5) - from t0 as A, t0 as B - where (A.key1 = 1 and A.key2 = 1 and A.key3 = 1 and A.key4=1 and A.key5=1 and A.key6=1 and A.key7 = 1 or A.key8=1) - and (B.key1 = 1 and B.key2 = 1 and B.key3 = 1 and B.key4=1 and B.key5=1 and B.key6=1 and B.key7 = 1 or B.key8=1); - -select max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5) - from t0 as A, t0 as B - where (A.key1 = 1 and A.key2 = 1 and A.key3 = 1 and A.key4=1 and A.key5=1 and A.key6=1 and A.key7 = 1 or A.key8=1) - and (B.key1 = 1 and B.key2 = 1 and B.key3 = 1 and B.key4=1 and B.key5=1 and B.key6=1 and B.key7 = 1 or B.key8=1); - -set join_buffer_size= @save_join_buffer_size; -# Test for BUG#4177 ends - -drop table t0, t1, t2, t3, t4; - -# BUG#16166 -CREATE TABLE t1 ( - cola char(3) not null, colb char(3) not null, filler char(200), - key(cola), key(colb) -); -INSERT INTO t1 VALUES ('foo','bar', 'ZZ'),('fuz','baz', 'ZZ'); - ---disable_query_log -let $1=9; -while ($1) -{ - eval INSERT INTO t1 SELECT * from t1 WHERE cola = 'foo'; - dec $1; -} - -let $1=13; -while ($1) -{ - eval INSERT INTO t1 SELECT * from t1 WHERE cola <> 'foo'; - dec $1; -} - ---enable_query_log - -OPTIMIZE TABLE t1; -select count(*) from t1; -explain select * from t1 WHERE cola = 'foo' AND colb = 'bar'; -explain select * from t1 force index(cola,colb) WHERE cola = 'foo' AND colb = 'bar'; -drop table t1; - -# -# BUG#17314: Index_merge/intersection not choosen by the optimizer for MERGE tables -# -create table t0 (a int); -insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); -create table t1 ( - a int, b int, - filler1 char(200), filler2 char(200), - key(a),key(b) -); -insert into t1 select @v:= A.a, @v, 't1', 'filler2' from t0 A, t0 B, t0 C; -create table t2 like t1; - -create table t3 ( - a int, b int, - filler1 char(200), filler2 char(200), - key(a),key(b) -) engine=merge union=(t1,t2); - ---replace_column 9 # -explain select * from t1 where a=1 and b=1; ---replace_column 9 # -explain select * from t3 where a=1 and b=1; - -drop table t3; -drop table t0, t1, t2; diff --git a/mysql-test/t/index_merge_innodb.test b/mysql-test/t/index_merge_innodb.test index 25f4e0b4e65..509ad733578 100644 --- a/mysql-test/t/index_merge_innodb.test +++ b/mysql-test/t/index_merge_innodb.test @@ -1,302 +1,24 @@ +# t/index_merge_innodb.test # # Index merge tests # --- source include/have_innodb.inc - ---disable_warnings -drop table if exists t1,t2; ---enable_warnings - -create table t1 -( - key1 int not null, - key2 int not null, - - INDEX i1(key1), - INDEX i2(key2) -) engine=innodb; - ---disable_query_log -let $1=200; -while ($1) -{ - eval insert into t1 values (200-$1, $1); - dec $1; -} ---enable_query_log - -# No primary key -explain select * from t1 where key1 < 5 or key2 > 197; - -select * from t1 where key1 < 5 or key2 > 197; - -explain select * from t1 where key1 < 3 or key2 > 195; -select * from t1 where key1 < 3 or key2 > 195; - -# Primary key as case-sensitive string with \0s. -# also make primary key be longer then max. index length of MyISAM. -alter table t1 add str1 char (255) not null, - add zeroval int not null default 0, - add str2 char (255) not null, - add str3 char (255) not null; - -update t1 set str1='aaa', str2='bbb', str3=concat(key2, '-', key1 div 2, '_' ,if(key1 mod 2 = 0, 'a', 'A')); - -alter table t1 add primary key (str1, zeroval, str2, str3); - -explain select * from t1 where key1 < 5 or key2 > 197; - -select * from t1 where key1 < 5 or key2 > 197; - -explain select * from t1 where key1 < 3 or key2 > 195; -select * from t1 where key1 < 3 or key2 > 195; - -# Test for BUG#5401 -drop table t1; -create table t1 ( - pk integer not null auto_increment primary key, - key1 integer, - key2 integer not null, - filler char (200), - index (key1), - index (key2) -) engine=innodb; -show warnings; ---disable_query_log -let $1=30; -while ($1) -{ - eval insert into t1 (key1, key2, filler) values ($1/4, $1/8, 'filler-data'); - dec $1; -} ---enable_query_log -explain select pk from t1 where key1 = 1 and key2 = 1; -select pk from t1 where key2 = 1 and key1 = 1; -select pk from t1 ignore index(key1,key2) where key2 = 1 and key1 = 1; - -# More tests for BUG#5401. -drop table t1; -create table t1 ( - pk int primary key auto_increment, - key1a int, - key2a int, - key1b int, - key2b int, - dummy1 int, - dummy2 int, - dummy3 int, - dummy4 int, - key3a int, - key3b int, - filler1 char (200), - index i1(key1a, key1b), - index i2(key2a, key2b), - index i3(key3a, key3b) -) engine=innodb; - -create table t2 (a int); -insert into t2 values (0),(1),(2),(3),(4),(NULL); - -insert into t1 (key1a, key1b, key2a, key2b, key3a, key3b) - select A.a, B.a, C.a, D.a, C.a, D.a from t2 A,t2 B,t2 C, t2 D; -insert into t1 (key1a, key1b, key2a, key2b, key3a, key3b) - select key1a, key1b, key2a, key2b, key3a, key3b from t1; -insert into t1 (key1a, key1b, key2a, key2b, key3a, key3b) - select key1a, key1b, key2a, key2b, key3a, key3b from t1; -analyze table t1; -select count(*) from t1; - -explain select count(*) from t1 where - key1a = 2 and key1b is null and key2a = 2 and key2b is null; - -select count(*) from t1 where - key1a = 2 and key1b is null and key2a = 2 and key2b is null; - -explain select count(*) from t1 where - key1a = 2 and key1b is null and key3a = 2 and key3b is null; - -select count(*) from t1 where - key1a = 2 and key1b is null and key3a = 2 and key3b is null; - -drop table t1,t2; - -# Test for BUG#8441 -create table t1 ( - id1 int, - id2 date , - index idx2 (id1,id2), - index idx1 (id2) -) engine = innodb; -insert into t1 values(1,'20040101'), (2,'20040102'); -select * from t1 where id1 = 1 and id2= '20040101'; -drop table t1; - -# Test for BUG#12720 ---disable_warnings -drop view if exists v1; ---enable_warnings -CREATE TABLE t1 ( - `oid` int(11) unsigned NOT NULL auto_increment, - `fk_bbk_niederlassung` int(11) unsigned NOT NULL, - `fk_wochentag` int(11) unsigned NOT NULL, - `uhrzeit_von` time NOT NULL COMMENT 'HH:MM', - `uhrzeit_bis` time NOT NULL COMMENT 'HH:MM', - `geloescht` tinyint(4) NOT NULL, - `version` int(5) NOT NULL, - PRIMARY KEY (`oid`), - KEY `fk_bbk_niederlassung` (`fk_bbk_niederlassung`), - KEY `fk_wochentag` (`fk_wochentag`), - KEY `ix_version` (`version`) -) ENGINE=InnoDB DEFAULT CHARSET=latin1; - -insert into t1 values -(1, 38, 1, '08:00:00', '13:00:00', 0, 1), -(2, 38, 2, '08:00:00', '13:00:00', 0, 1), -(3, 38, 3, '08:00:00', '13:00:00', 0, 1), -(4, 38, 4, '08:00:00', '13:00:00', 0, 1), -(5, 38, 5, '08:00:00', '13:00:00', 0, 1), -(6, 38, 5, '08:00:00', '13:00:00', 1, 2), -(7, 38, 3, '08:00:00', '13:00:00', 1, 2), -(8, 38, 1, '08:00:00', '13:00:00', 1, 2), -(9, 38, 2, '08:00:00', '13:00:00', 1, 2), -(10, 38, 4, '08:00:00', '13:00:00', 1, 2), -(11, 38, 1, '08:00:00', '13:00:00', 0, 3), -(12, 38, 2, '08:00:00', '13:00:00', 0, 3), -(13, 38, 3, '08:00:00', '13:00:00', 0, 3), -(14, 38, 4, '08:00:00', '13:00:00', 0, 3), -(15, 38, 5, '08:00:00', '13:00:00', 0, 3), -(16, 38, 4, '08:00:00', '13:00:00', 0, 4), -(17, 38, 5, '08:00:00', '13:00:00', 0, 4), -(18, 38, 1, '08:00:00', '13:00:00', 0, 4), -(19, 38, 2, '08:00:00', '13:00:00', 0, 4), -(20, 38, 3, '08:00:00', '13:00:00', 0, 4), -(21, 7, 1, '08:00:00', '13:00:00', 0, 1), -(22, 7, 2, '08:00:00', '13:00:00', 0, 1), -(23, 7, 3, '08:00:00', '13:00:00', 0, 1), -(24, 7, 4, '08:00:00', '13:00:00', 0, 1), -(25, 7, 5, '08:00:00', '13:00:00', 0, 1); - -create view v1 as -select - zeit1.oid AS oid, - zeit1.fk_bbk_niederlassung AS fk_bbk_niederlassung, - zeit1.fk_wochentag AS fk_wochentag, - zeit1.uhrzeit_von AS uhrzeit_von, - zeit1.uhrzeit_bis AS uhrzeit_bis, - zeit1.geloescht AS geloescht, - zeit1.version AS version -from - t1 zeit1 -where -(zeit1.version = - (select max(zeit2.version) AS `max(version)` - from t1 zeit2 - where - ((zeit1.fk_bbk_niederlassung = zeit2.fk_bbk_niederlassung) and - (zeit1.fk_wochentag = zeit2.fk_wochentag) and - (zeit1.uhrzeit_von = zeit2.uhrzeit_von) and - (zeit1.uhrzeit_bis = zeit2.uhrzeit_bis) - ) - ) -) -and (zeit1.geloescht = 0); - -select * from v1 where oid = 21; -drop view v1; -drop table t1; -## -CREATE TABLE t1( - t_cpac varchar(2) NOT NULL, - t_vers varchar(4) NOT NULL, - t_rele varchar(2) NOT NULL, - t_cust varchar(4) NOT NULL, - filler1 char(250) default NULL, - filler2 char(250) default NULL, - PRIMARY KEY (t_cpac,t_vers,t_rele,t_cust), - UNIQUE KEY IX_4 (t_cust,t_cpac,t_vers,t_rele), - KEY IX_5 (t_vers,t_rele,t_cust) -) ENGINE=InnoDB; - -insert into t1 values -('tm','2.5 ','a ',' ','',''), ('tm','2.5U','a ','stnd','',''), -('da','3.3 ','b ',' ','',''), ('da','3.3U','b ','stnd','',''), -('tl','7.6 ','a ',' ','',''), ('tt','7.6 ','a ',' ','',''), -('bc','B61 ','a ',' ','',''), ('bp','B61 ','a ',' ','',''), -('ca','B61 ','a ',' ','',''), ('ci','B61 ','a ',' ','',''), -('cp','B61 ','a ',' ','',''), ('dm','B61 ','a ',' ','',''), -('ec','B61 ','a ',' ','',''), ('ed','B61 ','a ',' ','',''), -('fm','B61 ','a ',' ','',''), ('nt','B61 ','a ',' ','',''), -('qm','B61 ','a ',' ','',''), ('tc','B61 ','a ',' ','',''), -('td','B61 ','a ',' ','',''), ('tf','B61 ','a ',' ','',''), -('tg','B61 ','a ',' ','',''), ('ti','B61 ','a ',' ','',''), -('tp','B61 ','a ',' ','',''), ('ts','B61 ','a ',' ','',''), -('wh','B61 ','a ',' ','',''), ('bc','B61U','a ','stnd','',''), -('bp','B61U','a ','stnd','',''), ('ca','B61U','a ','stnd','',''), -('ci','B61U','a ','stnd','',''), ('cp','B61U','a ','stnd','',''), -('dm','B61U','a ','stnd','',''), ('ec','B61U','a ','stnd','',''), -('fm','B61U','a ','stnd','',''), ('nt','B61U','a ','stnd','',''), -('qm','B61U','a ','stnd','',''), ('tc','B61U','a ','stnd','',''), -('td','B61U','a ','stnd','',''), ('tf','B61U','a ','stnd','',''), -('tg','B61U','a ','stnd','',''), ('ti','B61U','a ','stnd','',''), -('tp','B61U','a ','stnd','',''), ('ts','B61U','a ','stnd','',''), -('wh','B61U','a ','stnd','',''); -show create table t1; - -select t_vers,t_rele,t_cust,filler1 from t1 where t_vers = '7.6'; -select t_vers,t_rele,t_cust,filler1 from t1 where t_vers = '7.6' - and t_rele='a' and t_cust = ' '; - -drop table t1; - -# BUG#19021: Crash in index_merge/ROR-intersection optimizer under -# specific circumstances. -create table t1 ( - pk int(11) not null auto_increment, - a int(11) not null default '0', - b int(11) not null default '0', - c int(11) not null default '0', - - filler1 datetime, filler2 varchar(15), - filler3 longtext, - - kp1 varchar(4), kp2 varchar(7), - kp3 varchar(2), kp4 varchar(4), - kp5 varchar(7), - filler4 char(1), - - primary key (pk), - key idx1(a,b,c), - key idx2(c), - key idx3(kp1,kp2,kp3,kp4,kp5) -) engine=innodb default charset=latin1; ---disable_query_log -set @fill= uncompress(unhex(concat( -'F91D0000789CDD993D6FDB301086F7FE0A6D4E0105B8E3F1335D5BA028DA0EEDE28E1D320408', -'52A0713BF4D7571FB62C51A475924839080307B603E77DEE787C8FA41F9E9EEF7F1F8A87A7C3', -'AFE280C5DF9F8F7FEE9F8B1B2CB114D6902E918455245DB91300FA16E42D5201FA4EE29DA05D', -'B9FB3718A33718A3FA8C30AEFAFDE1F317D016AA67BA7A60FDE45BF5F8BA7B5BDE8812AA9F1A', -'069DB03C9804346644F3A3A6A1338DB572756A3C4D1BCC804CABF912C654AE9BB855A2B85962', -'3A479259CAE6A86C0411D01AE5483581EDCBD9A39C45252D532E533979EB9F82E971D979BDB4', -'8531105670740AFBFD1E34AAB0029E4AD0A1D46A6D0946A21A16038A5CD965CD2D524673F712', -'20C304477315CE18405EAF9BD0AFFEAC74FDA14F1FBF5BD34C769D73FBBEDF4750ADD4E5A99C', -'5C8DC04934AFA275D483D536D174C11B12AF27F8F888B41B6FC9DBA569E1FD7BD72D698130B7', -'91B23A98803512B3D31881E8DCDA2AC1754E3644C4BB3A8466750B911681274A39E35E8624B7', -'444A42AC1213F354758E3CF1A4CDD5A688C767CF1B11ABC5867CB15D8A18E0B91E9EC275BB94', -'58F33C2936F64690D55BC29E4A293D95A798D84217736CEAAA538CE1354269EE2162053FBC66', -'496D90CB53323CB279D3A6AF651B4B22B9E430743D83BE48E995A09D4FC9871C22D8D189B945', -'706911BCB8C3C774B9C08D2FC6ED853ADACA37A14A4CB2E027630E5B80ECACD939431B1CDF62', -'7D71487536EA2C678F59685E91F4B6C144BCCB94C1EBA9FA6F5552DDCA4E4539BE326A2720CB', -'45ED028EB3616AC93C46E775FEA9FA6DA7CFCEC6DEBA5FCD1F915EED4D983BDDB881528AD9AB', -'43C1576F29AAB35BDFBC21D422F52B307D350589D45225A887AC46C8EDD72D99EC3ED2E1BCEF', -'7AF26FC4C74097B6768A5EDAFA660CC64278F7E63F99AC954B'))); -prepare x from @fill; -execute x; -deallocate prepare x; ---enable_query_log -set @fill=NULL; -SELECT COUNT(*) FROM t1 WHERE b = 0 AND a = 0 AND c = 13286427 AND - kp1='279' AND kp2='ELM0678' AND kp3='6' AND kp4='10' AND kp5 = 'R '; +# Last update: +# 2006-08-07 ML test refactored (MySQL 5.1) +# Main code of several index_merge tests +# -> include/index_merge*.inc +# wrapper t/index_merge_innodb.test sources now several +# include/index_merge*.inc files +# -drop table t1; +--source include/have_innodb.inc +let $engine_type= InnoDB; +# InnoDB does not support Merge tables (affects include/index_merge1.inc) +let $merge_table_support= 0; +# Tests disabled because of open Bug#21277 and non deterministic explain output. +# --source include/index_merge1.inc +# --source include/index_merge_ror.inc +--source include/index_merge2.inc +--source include/index_merge_2sweeps.inc +--source include/index_merge_ror_cpk.inc diff --git a/mysql-test/t/index_merge_innodb2.test b/mysql-test/t/index_merge_innodb2.test deleted file mode 100644 index ec4ea672bc1..00000000000 --- a/mysql-test/t/index_merge_innodb2.test +++ /dev/null @@ -1,52 +0,0 @@ -# -# 2-sweeps read Index_merge test -# --- source include/have_innodb.inc - ---disable_warnings -drop table if exists t1; ---enable_warnings - -create table t1 ( - pk int primary key, - key1 int, - key2 int, - filler char(200), - filler2 char(200), - index(key1), - index(key2) -) engine=innodb; - - ---disable_query_log -let $1=1000; -while ($1) -{ - eval insert into t1 values($1, $1, $1, 'filler-data','filler-data-2'); - dec $1; -} ---enable_query_log - -select * from t1 where (key1 >= 2 and key1 <= 10) or (pk >= 4 and pk <=8 ); - -set @maxv=1000; - -select * from t1 where - (pk < 5) or (pk > 10 and pk < 15) or (pk >= 50 and pk < 55 ) or (pk > @maxv-10) - or key1=18 or key1=60; - -select * from t1 where - (pk < 5) or (pk > 10 and pk < 15) or (pk >= 50 and pk < 55 ) or (pk > @maxv-10) - or key1 < 3 or key1 > @maxv-11; - -select * from t1 where - (pk < 5) or (pk > 10 and pk < 15) or (pk >= 50 and pk < 55 ) or (pk > @maxv-10) - or - (key1 < 5) or (key1 > 10 and key1 < 15) or (key1 >= 50 and key1 < 55 ) or (key1 > @maxv-10); - -select * from t1 where - (pk > 10 and pk < 15) or (pk >= 50 and pk < 55 ) - or - (key1 < 5) or (key1 > @maxv-10); - -drop table t1; diff --git a/mysql-test/t/index_merge_myisam.test b/mysql-test/t/index_merge_myisam.test new file mode 100644 index 00000000000..8fdda2b772b --- /dev/null +++ b/mysql-test/t/index_merge_myisam.test @@ -0,0 +1,21 @@ +# t/index_merge_myisam.test +# +# Index merge tests +# +# Last update: +# 2006-08-07 ML test refactored (MySQL 5.1) +# Main code of several index_merge tests +# -> include/index_merge*.inc +# wrapper t/index_merge_innodb.test sources now several +# include/index_merge*.inc files +# + +let $engine_type= MyISAM; +# MyISAM supports Merge tables +let $merge_table_support= 1; + +--source include/index_merge1.inc +--source include/index_merge_ror.inc +--source include/index_merge2.inc +--source include/index_merge_2sweeps.inc +--source include/index_merge_ror_cpk.inc diff --git a/mysql-test/t/index_merge_ror.test b/mysql-test/t/index_merge_ror.test deleted file mode 100644 index 474d8e3dbcd..00000000000 --- a/mysql-test/t/index_merge_ror.test +++ /dev/null @@ -1,251 +0,0 @@ -# -# ROR-index_merge tests. -# ---disable_warnings -drop table if exists t0,t1,t2; ---enable_warnings ---disable_query_log -create table t1 -( - /* Field names reflect value(rowid) distribution, st=STairs, swt= SaWTooth */ - st_a int not null default 0, - swt1a int not null default 0, - swt2a int not null default 0, - - st_b int not null default 0, - swt1b int not null default 0, - swt2b int not null default 0, - - /* fields/keys for row retrieval tests */ - key1 int, - key2 int, - key3 int, - key4 int, - - /* make rows much bigger then keys */ - filler1 char (200), - filler2 char (200), - filler3 char (200), - filler4 char (200), - filler5 char (200), - filler6 char (200), - - /* order of keys is important */ - key sta_swt12a(st_a,swt1a,swt2a), - key sta_swt1a(st_a,swt1a), - key sta_swt2a(st_a,swt2a), - key sta_swt21a(st_a,swt2a,swt1a), - - key st_a(st_a), - key stb_swt1a_2b(st_b,swt1b,swt2a), - key stb_swt1b(st_b,swt1b), - key st_b(st_b), - - key(key1), - key(key2), - key(key3), - key(key4) -) ; - -# Fill table -create table t0 as select * from t1; -let $cnt=1000; -while ($cnt) -{ - eval insert into t0 values (1, 2, 3, 1, 2, 3, 0, 0, 0, 0, 'data1', 'data2', 'data3', 'data4', 'data5', 'data6'); - dec $cnt; -} - -alter table t1 disable keys; -let $1=4; -while ($1) -{ - let $2=4; - while ($2) - { - let $3=4; - while ($3) - { - eval insert into t1 select $1, $2, $3, $1 ,$2, $3, key1, key2, key3, key4, filler1, filler2, filler3, filler4, filler5, filler6 from t0; - dec $3; - } - dec $2; - } - dec $1; -} - -# Row retrieval tests -# -1 is used for values 'out of any range we are using' -# insert enough rows for index intersection to be used for (key1,key2) -insert into t1 (key1, key2, key3, key4, filler1) values (100, 100, 100, 100,'key1-key2-key3-key4'); -let $cnt=400; -while ($cnt) -{ - eval insert into t1 (key1, key2, key3, key4, filler1) values (100, -1, 100, -1,'key1-key3'); - dec $cnt; -} -let $cnt=400; -while ($cnt) -{ - eval insert into t1 (key1, key2, key3, key4, filler1) values (-1, 100, -1, 100,'key2-key4'); - dec $cnt; -} -alter table t1 enable keys; ---enable_query_log -select count(*) from t1; - -# One row results tests for cases where a single row matches all conditions -explain select key1,key2 from t1 where key1=100 and key2=100; -select key1,key2 from t1 where key1=100 and key2=100; -explain select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100; -select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100; - -# Several-rows results -insert into t1 (key1, key2, key3, key4, filler1) values (100, 100, -1, -1, 'key1-key2'); -insert into t1 (key1, key2, key3, key4, filler1) values (-1, -1, 100, 100, 'key4-key3'); - -# ROR-intersection, not covering -explain select key1,key2,filler1 from t1 where key1=100 and key2=100; -select key1,key2,filler1 from t1 where key1=100 and key2=100; - -# ROR-intersection, covering -explain select key1,key2 from t1 where key1=100 and key2=100; -select key1,key2 from t1 where key1=100 and key2=100; - -# ROR-union of ROR-intersections -explain select key1,key2,key3,key4 from t1 where key1=100 and key2=100 or key3=100 and key4=100; -select key1,key2,key3,key4 from t1 where key1=100 and key2=100 or key3=100 and key4=100; -explain select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100; -select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100; - -# 3-way ROR-intersection -explain select key1,key2,key3 from t1 where key1=100 and key2=100 and key3=100; -select key1,key2,key3 from t1 where key1=100 and key2=100 and key3=100; - -# ROR-union(ROR-intersection, ROR-range) -insert into t1 (key1,key2,key3,key4,filler1) values (101,101,101,101, 'key1234-101'); -explain select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=101; -select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=101; - -# Run some ROR updates/deletes -select key1,key2, filler1 from t1 where key1=100 and key2=100; -update t1 set filler1='to be deleted' where key1=100 and key2=100; -update t1 set key1=200,key2=200 where key1=100 and key2=100; -delete from t1 where key1=200 and key2=200; -select key1,key2,filler1 from t1 where key2=100 and key2=200; - -# ROR-union(ROR-intersection) with one of ROR-intersection giving empty -# results -explain select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100; -select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100; - -delete from t1 where key3=100 and key4=100; - -# ROR-union with all ROR-intersections giving empty results -explain select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100; -select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100; - -# ROR-intersection with empty result -explain select key1,key2 from t1 where key1=100 and key2=100; -select key1,key2 from t1 where key1=100 and key2=100; - -# ROR-union tests with various cases. -# All scans returning duplicate rows: -insert into t1 (key1, key2, key3, key4, filler1) values (100, 100, 200, 200,'key1-key2-key3-key4-1'); -insert into t1 (key1, key2, key3, key4, filler1) values (100, 100, 200, 200,'key1-key2-key3-key4-2'); -insert into t1 (key1, key2, key3, key4, filler1) values (100, 100, 200, 200,'key1-key2-key3-key4-3'); - -explain select key1,key2,key3,key4,filler1 from t1 where key3=200 or (key1=100 and key2=100) or key4=200; -select key1,key2,key3,key4,filler1 from t1 where key3=200 or (key1=100 and key2=100) or key4=200; - -insert into t1 (key1, key2, key3, key4, filler1) values (-1, -1, -1, 200,'key4'); - -explain select key1,key2,key3,key4,filler1 from t1 where key3=200 or (key1=100 and key2=100) or key4=200; -select key1,key2,key3,key4,filler1 from t1 where key3=200 or (key1=100 and key2=100) or key4=200; - -insert into t1 (key1, key2, key3, key4, filler1) values (-1, -1, 200, -1,'key3'); - -explain select key1,key2,key3,key4,filler1 from t1 where key3=200 or (key1=100 and key2=100) or key4=200; -select key1,key2,key3,key4,filler1 from t1 where key3=200 or (key1=100 and key2=100) or key4=200; - -## -## Optimizer tests -## - -# Check that the shortest key is used for ROR-intersection, covering and non-covering. -explain select * from t1 where st_a=1 and st_b=1; -explain select st_a,st_b from t1 where st_a=1 and st_b=1; - -# Check if "ingore index" syntax works -explain select st_a from t1 ignore index (st_a) where st_a=1 and st_b=1; - -# Do many tests -# Check that keys that don't improve selectivity are skipped. -# - -# Different value on 32 and 64 bit ---replace_result sta_swt12a sta_swt21a sta_swt12a, sta_swt12a, -explain select * from t1 where st_a=1 and swt1a=1 and swt2a=1; - -explain select * from t1 where st_b=1 and swt1b=1 and swt2b=1; - -explain select * from t1 where st_a=1 and swt1a=1 and swt2a=1 and st_b=1 and swt1b=1 and swt2b=1; - -explain select * from t1 ignore index (sta_swt21a, stb_swt1a_2b) - where st_a=1 and swt1a=1 and swt2a=1 and st_b=1 and swt1b=1 and swt2b=1; - -explain select * from t1 ignore index (sta_swt21a, sta_swt12a, stb_swt1a_2b) - where st_a=1 and swt1a=1 and swt2a=1 and st_b=1 and swt1b=1 and swt2b=1; - -explain select * from t1 ignore index (sta_swt21a, sta_swt12a, stb_swt1a_2b, stb_swt1b) - where st_a=1 and swt1a=1 and swt2a=1 and st_b=1 and swt1b=1 and swt2b=1; - -explain select * from t1 - where st_a=1 and swt1a=1 and swt2a=1 and st_b=1 and swt1b=1; - -explain select * from t1 - where st_a=1 and swt1a=1 and st_b=1 and swt1b=1 and swt1b=1; - -explain select st_a from t1 - where st_a=1 and swt1a=1 and st_b=1 and swt1b=1 and swt1b=1; - -explain select st_a from t1 - where st_a=1 and swt1a=1 and st_b=1 and swt1b=1 and swt1b=1; - -drop table t0,t1; - -# 'Partially' covered fields test - -create table t2 ( - a char(10), - b char(10), - filler1 char(255), - filler2 char(255), - key(a(5)), - key(b(5)) -); - ---disable_query_log -let $1=8; -while ($1) -{ - eval insert into t2 values (repeat(char($1+64), 8),repeat(char($1+64), 8),'filler1', 'filler2'); - dec $1; -} -insert into t2 select * from t2; -insert into t2 select * from t2; ---enable_query_log - -# The table row buffer is reused. Fill it with rows that don't match. -select count(a) from t2 where a='BBBBBBBB'; -select count(a) from t2 where b='BBBBBBBB'; - -# BUG#1: ---replace_result a a_or_b b a_or_b -explain select count(a) from t2 where a='AAAAAAAA' and b='AAAAAAAA'; -select count(a) from t2 where a='AAAAAAAA' and b='AAAAAAAA'; -select count(a) from t2 ignore index(a,b) where a='AAAAAAAA' and b='AAAAAAAA'; - -insert into t2 values ('ab', 'ab', 'uh', 'oh'); -explain select a from t2 where a='ab'; -drop table t2; diff --git a/mysql-test/t/index_merge_ror_cpk.test b/mysql-test/t/index_merge_ror_cpk.test deleted file mode 100644 index d58d0b46dd8..00000000000 --- a/mysql-test/t/index_merge_ror_cpk.test +++ /dev/null @@ -1,111 +0,0 @@ -# -# Clustered PK ROR-index_merge tests -# --- source include/have_innodb.inc - ---disable_warnings -drop table if exists t1; ---enable_warnings - -create table t1 -( - pk1 int not null, - pk2 int not null, - - key1 int not null, - key2 int not null, - - pktail1ok int not null, - pktail2ok int not null, - pktail3bad int not null, - pktail4bad int not null, - pktail5bad int not null, - - pk2copy int not null, - badkey int not null, - - filler1 char (200), - filler2 char (200), - key (key1), - key (key2), - - /* keys with tails from CPK members */ - key (pktail1ok, pk1), - key (pktail2ok, pk1, pk2), - key (pktail3bad, pk2, pk1), - key (pktail4bad, pk1, pk2copy), - key (pktail5bad, pk1, pk2, pk2copy), - - primary key (pk1, pk2) -) engine=innodb; - ---disable_query_log -set autocommit=0; -let $1=10000; -while ($1) -{ - eval insert into t1 values ($1 div 10,$1 mod 100, $1/100,$1/100, $1/100,$1/100,$1/100,$1/100,$1/100, $1 mod 100, $1/1000,'filler-data-$1','filler2'); - dec $1; -} -set autocommit=1; ---enable_query_log - -# Verify that range scan on CPK is ROR -# (use index_intersection because it is impossible to check that for index union) -explain select * from t1 where pk1 = 1 and pk2 < 80 and key1=0; -# CPK scan + 1 ROR range scan is a special case -select * from t1 where pk1 = 1 and pk2 < 80 and key1=0; - -# Verify that CPK fields are considered to be covered by index scans -explain select pk1,pk2 from t1 where key1 = 10 and key2=10 and 2*pk1+1 < 2*96+1; -select pk1,pk2 from t1 where key1 = 10 and key2=10 and 2*pk1+1 < 2*96+1; - -# Verify that CPK is always used for index intersection scans -# (this is because it is used as a filter, not for retrieval) -explain select * from t1 where badkey=1 and key1=10; ---replace_column 9 ROWS -explain select * from t1 where pk1 < 7500 and key1 = 10; - -# Verify that keys with 'tails' of PK members are ok. -explain select * from t1 where pktail1ok=1 and key1=10; -explain select * from t1 where pktail2ok=1 and key1=10; - -select ' The following is actually a deficiency, it uses sort_union currently:' as 'note:'; -explain select * from t1 where (pktail2ok=1 and pk1< 50000) or key1=10; - -# The expected rows differs a bit from platform to platform ---replace_result 98 ROWS 99 ROWS -explain select * from t1 where pktail3bad=1 and key1=10; -explain select * from t1 where pktail4bad=1 and key1=10; -explain select * from t1 where pktail5bad=1 and key1=10; - -# Test for problem with innodb key values prefetch buffer: -explain select pk1,pk2,key1,key2 from t1 where key1 = 10 and key2=10 limit 10; -select pk1,pk2,key1,key2 from t1 where key1 = 10 and key2=10 limit 10; - -drop table t1; -# Testcase for BUG#4984 -create table t1 -( - RUNID varchar(22), - SUBMITNR varchar(5), - ORDERNR char(1) , - PROGRAMM varchar(8), - TESTID varchar(4), - UCCHECK char(1), - ETEXT varchar(80), - ETEXT_TYPE char(1), - INFO char(1), - SEVERITY tinyint(3), - TADIRFLAG char(1), - PRIMARY KEY (RUNID,SUBMITNR,ORDERNR,PROGRAMM,TESTID,UCCHECK), - KEY `TVERM~KEY` (PROGRAMM,TESTID,UCCHECK) -) ENGINE=InnoDB DEFAULT CHARSET=latin1; - -update t1 set `ETEXT` = '', `ETEXT_TYPE`='', `INFO`='', `SEVERITY`='', `TADIRFLAG`='' -WHERE - `RUNID`= '' AND `SUBMITNR`= '' AND `ORDERNR`='' AND `PROGRAMM`='' AND - `TESTID`='' AND `UCCHECK`=''; - -drop table t1; - diff --git a/mysql-test/t/innodb-big.test b/mysql-test/t/innodb-big.test deleted file mode 100644 index ade69ffdb45..00000000000 --- a/mysql-test/t/innodb-big.test +++ /dev/null @@ -1,46 +0,0 @@ -# -# Test some things that takes a long time - --- source include/big_test.inc --- source include/have_innodb.inc - ---disable_warnings -DROP TABLE IF EXISTS t1, t2, t3, t4; ---enable_warnings - -# -# Test test how filesort and buffered-record-reads works with innodb -# - -CREATE TABLE t1 (id INTEGER) ENGINE=MYISAM; -CREATE TABLE t2 (id INTEGER primary key) ENGINE=INNODB; -CREATE TABLE t3 (a char(32) primary key,id INTEGER) ENGINE=INNODB; -CREATE TABLE t4 (a char(32) primary key,id INTEGER) ENGINE=MYISAM; - -INSERT INTO t1 (id) VALUES (1); -INSERT INTO t1 SELECT id+1 FROM t1; -INSERT INTO t1 SELECT id+2 FROM t1; -INSERT INTO t1 SELECT id+4 FROM t1; -INSERT INTO t1 SELECT id+8 FROM t1; -INSERT INTO t1 SELECT id+16 FROM t1; -INSERT INTO t1 SELECT id+32 FROM t1; -INSERT INTO t1 SELECT id+64 FROM t1; -INSERT INTO t1 SELECT id+128 FROM t1; -INSERT INTO t1 SELECT id+256 FROM t1; -INSERT INTO t1 SELECT id+512 FROM t1; -INSERT INTO t1 SELECT id+1024 FROM t1; -INSERT INTO t1 SELECT id+2048 FROM t1; -INSERT INTO t1 SELECT id+4096 FROM t1; -INSERT INTO t1 SELECT id+8192 FROM t1; -INSERT INTO t1 SELECT id+16384 FROM t1; -INSERT INTO t1 SELECT id+32768 FROM t1; -INSERT INTO t1 SELECT id+65536 FROM t1; -INSERT INTO t1 SELECT id+131072 FROM t1; -INSERT INTO t1 SELECT id+262144 FROM t1; -INSERT INTO t1 SELECT id+524288 FROM t1; -INSERT INTO t1 SELECT id+1048576 FROM t1; -INSERT INTO t2 SELECT * FROM t1; -INSERT INTO t3 SELECT concat(id),id from t2 ORDER BY -id; -INSERT INTO t4 SELECT * from t3 ORDER BY concat(a); -select sum(id) from t3; -drop table t1,t2,t3,t4; diff --git a/mysql-test/t/innodb-deadlock.test b/mysql-test/t/innodb-deadlock.test deleted file mode 100644 index 41741942963..00000000000 --- a/mysql-test/t/innodb-deadlock.test +++ /dev/null @@ -1,117 +0,0 @@ --- source include/have_innodb.inc -# Can't test this with embedded server --- source include/not_embedded.inc - -connect (con1,localhost,root,,); -connect (con2,localhost,root,,); - ---disable_warnings -drop table if exists t1,t2; ---enable_warnings - -# -# Testing of FOR UPDATE -# - -connection con1; -create table t1 (id integer, x integer) engine=INNODB; -insert into t1 values(0, 0); -set autocommit=0; -SELECT * from t1 where id = 0 FOR UPDATE; - -connection con2; -set autocommit=0; - -# The following query should hang because con1 is locking the page ---send -update t1 set x=2 where id = 0; ---sleep 2 - -connection con1; -update t1 set x=1 where id = 0; -select * from t1; -commit; - -connection con2; -reap; -commit; - -connection con1; -select * from t1; -commit; - -drop table t1; -# -# Testing of FOR UPDATE -# - -connection con1; -create table t1 (id integer, x integer) engine=INNODB; -create table t2 (b integer, a integer) engine=INNODB; -insert into t1 values(0, 0), (300, 300); -insert into t2 values(0, 10), (1, 20), (2, 30); -commit; -set autocommit=0; -select * from t2; -update t2 set a=100 where b=(SELECT x from t1 where id = b FOR UPDATE); -select * from t2; -select * from t1; - -connection con2; -set autocommit=0; - -# The following query should hang because con1 is locking the page ---send -update t1 set x=2 where id = 0; ---sleep 2 - -connection con1; -update t1 set x=1 where id = 0; -select * from t1; -commit; - -connection con2; -reap; -commit; - -connection con1; -select * from t1; -commit; - -drop table t1, t2; -create table t1 (id integer, x integer) engine=INNODB; -create table t2 (b integer, a integer) engine=INNODB; -insert into t1 values(0, 0), (300, 300); -insert into t2 values(0, 0), (1, 20), (2, 30); -commit; - -connection con1; -select a,b from t2 UNION SELECT id, x from t1 FOR UPDATE; -select * from t2; -select * from t1; - -connection con2; - -# The following query should hang because con1 is locking the page -update t2 set a=2 where b = 0; -select * from t2; ---send -update t1 set x=2 where id = 0; ---sleep 2 - -connection con1; -update t1 set x=1 where id = 0; -select * from t1; -commit; - -connection con2; -reap; -commit; - -connection con1; -select * from t1; -commit; - -drop table t1, t2; - -# End of 4.1 tests diff --git a/mysql-test/t/innodb_cache.test b/mysql-test/t/innodb_cache.test deleted file mode 100644 index 8ed2853e4f7..00000000000 --- a/mysql-test/t/innodb_cache.test +++ /dev/null @@ -1,87 +0,0 @@ --- source include/have_innodb.inc --- source include/have_query_cache.inc - -# Initialise ---disable_warnings -drop table if exists t1,t2,t3; ---enable_warnings - -# -# Without auto_commit. -# -flush status; -set autocommit=0; -create table t1 (a int not null) engine=innodb; -insert into t1 values (1),(2),(3); -select * from t1; -show status like "Qcache_queries_in_cache"; -drop table t1; -commit; -set autocommit=1; -begin; -create table t1 (a int not null) engine=innodb; -insert into t1 values (1),(2),(3); -select * from t1; -show status like "Qcache_queries_in_cache"; -drop table t1; -commit; -create table t1 (a int not null) engine=innodb; -create table t2 (a int not null) engine=innodb; -create table t3 (a int not null) engine=innodb; -insert into t1 values (1),(2); -insert into t2 values (1),(2); -insert into t3 values (1),(2); -select * from t1; -select * from t2; -select * from t3; -show status like "Qcache_queries_in_cache"; -show status like "Qcache_hits"; -begin; -select * from t1; -select * from t2; -select * from t3; -show status like "Qcache_queries_in_cache"; -show status like "Qcache_hits"; -insert into t1 values (3); -insert into t2 values (3); -insert into t1 values (4); -select * from t1; -select * from t2; -select * from t3; -show status like "Qcache_queries_in_cache"; -show status like "Qcache_hits"; -commit; -show status like "Qcache_queries_in_cache"; -drop table t3,t2,t1; - -CREATE TABLE t1 (id int(11) NOT NULL auto_increment, PRIMARY KEY (id)) ENGINE=InnoDB; -select count(*) from t1; -insert into t1 (id) values (0); -select count(*) from t1; -drop table t1; - -# -# one statement roll back inside transation -# -let $save_query_cache_size=`select @@global.query_cache_size`; -set GLOBAL query_cache_size=1355776; -CREATE TABLE t1 ( id int(10) NOT NULL auto_increment, a varchar(25) default NULL, PRIMARY KEY (id), UNIQUE KEY a (a)) ENGINE=innodb; -CREATE TABLE t2 ( id int(10) NOT NULL auto_increment, b varchar(25) default NULL, PRIMARY KEY (id), UNIQUE KEY b (b)) ENGINE=innodb; -CREATE TABLE t3 ( id int(10) NOT NULL auto_increment, t1_id int(10) NOT NULL default '0', t2_id int(10) NOT NULL default '0', state int(11) default NULL, PRIMARY KEY (id), UNIQUE KEY t1_id (t1_id,t2_id), KEY t2_id (t2_id,t1_id), CONSTRAINT `t3_ibfk_1` FOREIGN KEY (`t1_id`) REFERENCES `t1` (`id`), CONSTRAINT `t3_ibfk_2` FOREIGN KEY (`t2_id`) REFERENCES `t2` (`id`)) ENGINE=innodb; -INSERT INTO t1 VALUES (1,'me'); -INSERT INTO t2 VALUES (1,'you'); -INSERT INTO t3 VALUES (2,1,1,2); -delete from t3 where t1_id = 1 and t2_id = 1; -select t1.* from t1, t2, t3 where t3.state & 1 = 0 and t3.t1_id = t1.id and t3.t2_id = t2.id and t1.id = 1 order by t1.a asc; -begin; -insert into t3 VALUES ( NULL, 1, 1, 2 ); --- error 1062 -insert into t3 VALUES ( NULL, 1, 1, 2 ); -commit; -select t1.* from t1, t2, t3 where t3.state & 1 = 0 and t3.t1_id = t1.id and t3.t2_id = t2.id and t1.id = 1 order by t1.a asc; -drop table t3,t2,t1; ---disable_query_log -eval set GLOBAL query_cache_size=$save_query_cache_size; ---enable_query_log - -# End of 4.1 tests diff --git a/mysql-test/t/innodb_concurrent.test b/mysql-test/t/innodb_concurrent.test deleted file mode 100644 index 957276a44c7..00000000000 --- a/mysql-test/t/innodb_concurrent.test +++ /dev/null @@ -1,346 +0,0 @@ -# -# Concurrent InnoDB tests, mainly in UPDATE's -# Bug#3300 -# Designed and tested by Sinisa Milivojevic, sinisa@mysql.com -# -# two non-interfering UPDATE's not changing result set -# - -# test takes circa 5 minutes to run, so it's big --- source include/big_test.inc - -connection default; -drop table if exists t1; -create table t1(eta int(11) not null, tipo int(11), c varchar(255)) engine=innodb; -connect (thread1, localhost, mysqltest,,); -connection thread1; -insert into t1 values (7,7, "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"); -insert into t1 values (8,8, "bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb"); -insert into t1 values (10,1,"ccccccccccccccccccccccccccccccccccccccccccc"); -insert into t1 values (20,2,"ddddddddddddddddddddddddddddddddddddddddddd"); -insert into t1 values (30,1,"eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee"); -insert into t1 values (40,2,"fffffffffffffffffffffffffffffffffffffffffff"); -insert into t1 values (50,1,"ggggggggggggggggggggggggggggggggggggggggggg"); -insert into t1 values (60,2,"hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh"); -insert into t1 values (70,1,"iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii"); -insert into t1 values (80,22,"jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj"); -insert into t1 values (90,11,"kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk"); -select get_lock("hello",1); -connect (thread2, localhost, mysqltest,,); -connection thread2; -begin; -send update t1 set eta=1+get_lock("hello",1)*0 where tipo=11; -sleep 1; -connection thread1; -begin; -update t1 set eta=2 where tipo=22; -select release_lock("hello"); -select * from t1; -connection thread2; -reap; -select * from t1; -send commit; -connection thread1; -select * from t1; -commit; -select * from t1; -connection thread2; -reap; -select * from t1; -connection thread1; -select * from t1; -connection default; -drop table t1; - -# -# two UPDATE's running and one changing result set -# -#connect (thread1, localhost, mysqltest,,); -connection thread1; -create table t1(eta int(11) not null, tipo int(11), c varchar(255)) engine=innodb; -insert into t1 values (7,7, "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"); -insert into t1 values (8,8, "bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb"); -insert into t1 values (10,1,"ccccccccccccccccccccccccccccccccccccccccccc"); -insert into t1 values (20,2,"ddddddddddddddddddddddddddddddddddddddddddd"); -insert into t1 values (30,1,"eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee"); -insert into t1 values (40,2,"fffffffffffffffffffffffffffffffffffffffffff"); -insert into t1 values (50,1,"ggggggggggggggggggggggggggggggggggggggggggg"); -insert into t1 values (60,2,"hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh"); -insert into t1 values (70,1,"iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii"); -insert into t1 values (80,22,"jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj"); -insert into t1 values (90,11,"kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk"); -select get_lock("hello",10); -#connect (thread2, localhost, mysqltest,,); -connection thread2; -begin; -send update t1 set eta=1+get_lock("hello",10)*0 where tipo=1; -sleep 1; -connection thread1; -begin; -update t1 set tipo=1 where tipo=2; -select release_lock("hello"); -select * from t1; -connection thread2; -reap; -select * from t1; -send commit; -connection thread1; -select * from t1; -commit; -select * from t1; -connection thread2; -reap; -select * from t1; -connection thread1; -select * from t1; -connection default; -drop table t1; - - -# -# One UPDATE and one INSERT .... Monty's test -# - -#connect (thread1, localhost, mysqltest,,); -connection thread1; -create table t1 (a int not null, b int not null) engine=innodb; -insert into t1 values (1,1),(2,1),(3,1),(4,1); -select get_lock("hello2",1000); -#connect (thread2, localhost, mysqltest,,); -connection thread2; -begin; -send update t1 set b=10+get_lock(concat("hello",a),1000)*0 where -a=2; -sleep 1; -connection thread1; -insert into t1 values (1,1); -select release_lock("hello2"); -select * from t1; -connection thread2; -reap; -select * from t1; -send commit; -connection thread1; -sleep 1; -connection thread2; -reap; -connection default; -drop table t1; - -# -# one UPDATE changing result set and SELECT ... FOR UPDATE -# -#connect (thread1, localhost, mysqltest,,); -connection thread1; -create table t1(eta int(11) not null, tipo int(11), c varchar(255)) engine=innodb; -insert into t1 values (7,7, "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"); -insert into t1 values (8,8, "bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb"); -insert into t1 values (10,1,"ccccccccccccccccccccccccccccccccccccccccccc"); -insert into t1 values (20,2,"ddddddddddddddddddddddddddddddddddddddddddd"); -insert into t1 values (30,1,"eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee"); -insert into t1 values (40,2,"fffffffffffffffffffffffffffffffffffffffffff"); -insert into t1 values (50,1,"ggggggggggggggggggggggggggggggggggggggggggg"); -insert into t1 values (60,2,"hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh"); -insert into t1 values (70,1,"iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii"); -insert into t1 values (80,22,"jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj"); -insert into t1 values (90,11,"kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk"); -select get_lock("hello",10); -#connect (thread2, localhost, mysqltest,,); -connection thread2; -begin; -send select * from t1 where tipo=2 FOR UPDATE; -sleep 1; -connection thread1; -begin; -select release_lock("hello"); ---error 1205 -update t1 set tipo=1+get_lock("hello",10)*0 where tipo=2; -select * from t1; -connection thread2; -reap; -select * from t1; -send commit; -connection thread1; -commit; -connection thread2; -reap; -select * from t1; -connection thread1; -select * from t1; -connection default; -drop table t1; - -# -# one UPDATE not changing result set and SELECT ... FOR UPDATE -# -#connect (thread1, localhost, mysqltest,,); -connection thread1; -create table t1(eta int(11) not null, tipo int(11), c varchar(255)) engine=innodb; -insert into t1 values (7,7, "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"); -insert into t1 values (8,8, "bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb"); -insert into t1 values (10,1,"ccccccccccccccccccccccccccccccccccccccccccc"); -insert into t1 values (20,2,"ddddddddddddddddddddddddddddddddddddddddddd"); -insert into t1 values (30,1,"eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee"); -insert into t1 values (40,2,"fffffffffffffffffffffffffffffffffffffffffff"); -insert into t1 values (50,1,"ggggggggggggggggggggggggggggggggggggggggggg"); -insert into t1 values (60,2,"hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh"); -insert into t1 values (70,1,"iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii"); -insert into t1 values (80,22,"jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj"); -insert into t1 values (90,11,"kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk"); -select get_lock("hello",10); -#connect (thread2, localhost, mysqltest,,); -connection thread2; -begin; -send select * from t1 where tipo=2 FOR UPDATE; -sleep 1; -connection thread1; -begin; -select release_lock("hello"); ---error 1205 -update t1 set tipo=11+get_lock("hello",10)*0 where tipo=22; -select * from t1; -connection thread2; -reap; -select * from t1; -send commit; -connection thread1; -commit; -connection thread2; -reap; -select * from t1; -connection thread1; -select * from t1; -connection default; -drop table t1; - -# -# two SELECT ... FOR UPDATE -# -#connect (thread1, localhost, mysqltest,,); -connection thread1; -create table t1(eta int(11) not null, tipo int(11), c varchar(255)) engine=innodb; -insert into t1 values (7,7, "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"); -insert into t1 values (8,8, "bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb"); -insert into t1 values (10,1,"ccccccccccccccccccccccccccccccccccccccccccc"); -insert into t1 values (20,2,"ddddddddddddddddddddddddddddddddddddddddddd"); -insert into t1 values (30,1,"eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee"); -insert into t1 values (40,2,"fffffffffffffffffffffffffffffffffffffffffff"); -insert into t1 values (50,1,"ggggggggggggggggggggggggggggggggggggggggggg"); -insert into t1 values (60,2,"hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh"); -insert into t1 values (70,1,"iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii"); -insert into t1 values (80,22,"jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj"); -insert into t1 values (90,11,"kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk"); -select get_lock("hello",10); -#connect (thread2, localhost, mysqltest,,); -connection thread2; -begin; -send select * from t1 where tipo=2 FOR UPDATE; -sleep 1; -connection thread1; -begin; -select release_lock("hello"); ---error 1205 -select * from t1 where tipo=1 FOR UPDATE; -connection thread2; -reap; -select * from t1; -send commit; -connection thread1; -commit; -connection thread2; -reap; -select * from t1; -connection thread1; -select * from t1; -connection default; -drop table t1; - - -# -# one UPDATE changing result set and DELETE -# -#connect (thread1, localhost, mysqltest,,); -connection thread1; -create table t1(eta int(11) not null, tipo int(11), c varchar(255)) engine=innodb; -insert into t1 values (7,7, "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"); -insert into t1 values (8,8, "bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb"); -insert into t1 values (10,1,"ccccccccccccccccccccccccccccccccccccccccccc"); -insert into t1 values (20,2,"ddddddddddddddddddddddddddddddddddddddddddd"); -insert into t1 values (30,1,"eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee"); -insert into t1 values (40,2,"fffffffffffffffffffffffffffffffffffffffffff"); -insert into t1 values (50,1,"ggggggggggggggggggggggggggggggggggggggggggg"); -insert into t1 values (60,2,"hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh"); -insert into t1 values (70,1,"iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii"); -insert into t1 values (80,22,"jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj"); -insert into t1 values (90,11,"kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk"); -select get_lock("hello",10); -#connect (thread2, localhost, mysqltest,,); -connection thread2; -begin; -send delete from t1 where tipo=2; -sleep 1; -connection thread1; -begin; -select release_lock("hello"); ---error 1205 -update t1 set tipo=1+get_lock("hello",10)*0 where tipo=2; -select * from t1; -connection thread2; -reap; -select * from t1; -send commit; -connection thread1; -commit; -connection thread2; -reap; -select * from t1; -connection thread1; -select * from t1; -connection default; -drop table t1; - - -# -# one UPDATE not changing result set and DELETE -# -#connect (thread1, localhost, mysqltest,,); -connection thread1; -create table t1(eta int(11) not null, tipo int(11), c varchar(255)) engine=innodb; -insert into t1 values (7,7, "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"); -insert into t1 values (8,8, "bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb"); -insert into t1 values (10,1,"ccccccccccccccccccccccccccccccccccccccccccc"); -insert into t1 values (20,2,"ddddddddddddddddddddddddddddddddddddddddddd"); -insert into t1 values (30,1,"eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee"); -insert into t1 values (40,2,"fffffffffffffffffffffffffffffffffffffffffff"); -insert into t1 values (50,1,"ggggggggggggggggggggggggggggggggggggggggggg"); -insert into t1 values (60,2,"hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh"); -insert into t1 values (70,1,"iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii"); -insert into t1 values (80,22,"jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj"); -insert into t1 values (90,11,"kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk"); -select get_lock("hello",10); -#connect (thread2, localhost, mysqltest,,); -connection thread2; -begin; -send delete from t1 where tipo=2; -sleep 1; -connection thread1; -begin; -select release_lock("hello"); -update t1 set tipo=1+get_lock("hello",10)*0 where tipo=22; -select * from t1; -connection thread2; -reap; -select * from t1; -send commit; -connection thread1; -commit; -connection thread2; -reap; -select * from t1; -connection thread1; -select * from t1; -connection default; -sleep 1; -drop table t1; -disconnect thread1; -disconnect thread2; diff --git a/mysql-test/t/innodb_handler.test b/mysql-test/t/innodb_handler.test deleted file mode 100644 index 18cec97af0d..00000000000 --- a/mysql-test/t/innodb_handler.test +++ /dev/null @@ -1,96 +0,0 @@ --- source include/have_innodb.inc - -# -# test of HANDLER ... -# - ---disable_warnings -drop table if exists t1,t2; ---enable_warnings - -create table t1 (a int, b char(10), key a(a), key b(a,b)) engine=innodb; -insert into t1 values -(17,"ddd"),(18,"eee"),(19,"fff"),(19,"yyy"), -(14,"aaa"),(15,"bbb"),(16,"ccc"),(16,"xxx"), -(20,"ggg"),(21,"hhh"),(22,"iii"); -handler t1 open as t2; -handler t2 read a first; -handler t2 read a next; -handler t2 read a next; -handler t2 read a prev; -handler t2 read a last; -handler t2 read a prev; -handler t2 read a prev; - -handler t2 read a first; -handler t2 read a prev; - -handler t2 read a last; -handler t2 read a prev; -handler t2 read a next; -handler t2 read a next; - -handler t2 read a=(15); -handler t2 read a=(16); - ---error 1070 -handler t2 read a=(19,"fff"); - -handler t2 read b=(19,"fff"); -handler t2 read b=(19,"yyy"); -handler t2 read b=(19); - ---error 1109 -handler t1 read a last; - -handler t2 read a=(11); -handler t2 read a>=(11); - -handler t2 read a=(18); -handler t2 read a>=(18); -handler t2 read a>(18); -handler t2 read a<=(18); -handler t2 read a<(18); - -handler t2 read a first limit 5; -handler t2 read a next limit 3; -handler t2 read a prev limit 10; - -handler t2 read a>=(16) limit 4; -handler t2 read a>=(16) limit 2,2; -handler t2 read a last limit 3; - -handler t2 read a=(19); -handler t2 read a=(19) where b="yyy"; - -handler t2 read first; -handler t2 read next; ---error 1064 -handler t2 read last; -handler t2 close; - -handler t1 open; -handler t1 read a next; # this used to crash as a bug#5373 -handler t1 read a next; -handler t1 close; - -handler t1 open; -handler t1 read a prev; # this used to crash as a bug#5373 -handler t1 read a prev; -handler t1 close; - -handler t1 open as t2; -handler t2 read first; -alter table t1 engine=innodb; ---error 1109 -handler t2 read first; - -drop table t1; -CREATE TABLE t1 ( no1 smallint(5) NOT NULL default '0', no2 int(10) NOT NULL default '0', PRIMARY KEY (no1,no2)) ENGINE=InnoDB; -INSERT INTO t1 VALUES (1,274),(1,275),(2,6),(2,8),(4,1),(4,2); -HANDLER t1 OPEN; -HANDLER t1 READ `primary` = (1, 1000); -HANDLER t1 READ `primary` PREV; -DROP TABLE t1; - -# End of 4.1 tests diff --git a/mysql-test/t/innodb_mysql.test b/mysql-test/t/innodb_mysql.test index 2be53b58a39..d7f1a40eaf0 100644 --- a/mysql-test/t/innodb_mysql.test +++ b/mysql-test/t/innodb_mysql.test @@ -1,322 +1,12 @@ --- source include/have_innodb.inc - ---disable_warnings -drop table if exists t1,t2,t1m,t1i,t2m,t2i,t4; ---enable_warnings - +# t/innodb_mysql.test # -# Bug#17530: Incorrect key truncation on table creation caused server crash. +# Last update: +# 2006-07-26 ML test refactored (MySQL 5.1) +# main testing code t/innodb_mysql.test -> include/mix1.inc # -create table t1(f1 varchar(800) binary not null, key(f1)) engine = innodb - character set utf8 collate utf8_general_ci; -insert into t1 values('aaa'); -drop table t1; - -# BUG#16798: Uninitialized row buffer reads in ref-or-null optimizer -# (repeatable only w/innodb). -create table t1 ( - c_id int(11) not null default '0', - org_id int(11) default null, - unique key contacts$c_id (c_id), - key contacts$org_id (org_id) -) engine=innodb; -insert into t1 values - (2,null),(120,null),(141,null),(218,7), (128,1), - (151,2),(234,2),(236,2),(243,2),(255,2),(259,2),(232,3),(235,3),(238,3), - (246,3),(253,3),(269,3),(285,3),(291,3),(293,3),(131,4),(230,4),(231,4); - -create table t2 ( - slai_id int(11) not null default '0', - owner_tbl int(11) default null, - owner_id int(11) default null, - sla_id int(11) default null, - inc_web int(11) default null, - inc_email int(11) default null, - inc_chat int(11) default null, - inc_csr int(11) default null, - inc_total int(11) default null, - time_billed int(11) default null, - activedate timestamp null default null, - expiredate timestamp null default null, - state int(11) default null, - sla_set int(11) default null, - unique key t2$slai_id (slai_id), - key t2$owner_id (owner_id), - key t2$sla_id (sla_id) -) engine=innodb; -insert into t2(slai_id, owner_tbl, owner_id, sla_id) values - (1,3,1,1), (3,3,10,2), (4,3,3,6), (5,3,2,5), (6,3,8,3), (7,3,9,7), - (8,3,6,8), (9,3,4,9), (10,3,5,10), (11,3,11,11), (12,3,7,12); - -flush tables; -select si.slai_id -from t1 c join t2 si on - ((si.owner_tbl = 3 and si.owner_id = c.org_id) or - ( si.owner_tbl = 2 and si.owner_id = c.c_id)) -where - c.c_id = 218 and expiredate is null; - -select * from t1 where org_id is null; -select si.slai_id -from t1 c join t2 si on - ((si.owner_tbl = 3 and si.owner_id = c.org_id) or - ( si.owner_tbl = 2 and si.owner_id = c.c_id)) -where - c.c_id = 218 and expiredate is null; - -drop table t1, t2; - -# -# Bug#17212: results not sorted correctly by ORDER BY when using index -# (repeatable only w/innodb because of index props) -# -CREATE TABLE t1 (a int, b int, KEY b (b)) Engine=InnoDB; -CREATE TABLE t2 (a int, b int, PRIMARY KEY (a,b)) Engine=InnoDB; -CREATE TABLE t3 (a int, b int, c int, PRIMARY KEY (a), - UNIQUE KEY b (b,c), KEY a (a,b,c)) Engine=InnoDB; - -INSERT INTO t1 VALUES (1, 1); -INSERT INTO t1 SELECT a + 1, b + 1 FROM t1; -INSERT INTO t1 SELECT a + 2, b + 2 FROM t1; - -INSERT INTO t2 VALUES (1,1),(1,2),(1,3),(1,4),(1,5),(1,6),(1,7),(1,8); -INSERT INTO t2 SELECT a + 1, b FROM t2; -DELETE FROM t2 WHERE a = 1 AND b < 2; - -INSERT INTO t3 VALUES (1,1,1),(2,1,2); -INSERT INTO t3 SELECT a + 2, a + 2, 3 FROM t3; -INSERT INTO t3 SELECT a + 4, a + 4, 3 FROM t3; - -# demonstrate a problem when a must-use-sort table flag -# (sort_by_table=1) is being neglected. -SELECT STRAIGHT_JOIN SQL_NO_CACHE t1.b, t1.a FROM t1, t3, t2 WHERE - t3.a = t2.a AND t2.b = t1.a AND t3.b = 1 AND t3.c IN (1, 2) - ORDER BY t1.b LIMIT 2; - -# demonstrate the problem described in the bug report -SELECT STRAIGHT_JOIN SQL_NO_CACHE t1.b, t1.a FROM t1, t3, t2 WHERE - t3.a = t2.a AND t2.b = t1.a AND t3.b = 1 AND t3.c IN (1, 2) - ORDER BY t1.b LIMIT 5; -DROP TABLE t1, t2, t3; -# -# Bug #12882 min/max inconsistent on empty table -# - ---disable_warnings -create table t1m (a int) engine=myisam; -create table t1i (a int) engine=innodb; -create table t2m (a int) engine=myisam; -create table t2i (a int) engine=innodb; ---enable_warnings -insert into t2m values (5); -insert into t2i values (5); - -# test with MyISAM -select min(a) from t1m; -select min(7) from t1m; -select min(7) from DUAL; -explain select min(7) from t2m join t1m; -select min(7) from t2m join t1m; - -select max(a) from t1m; -select max(7) from t1m; -select max(7) from DUAL; -explain select max(7) from t2m join t1m; -select max(7) from t2m join t1m; - -select 1, min(a) from t1m where a=99; -select 1, min(a) from t1m where 1=99; -select 1, min(1) from t1m where a=99; -select 1, min(1) from t1m where 1=99; - -select 1, max(a) from t1m where a=99; -select 1, max(a) from t1m where 1=99; -select 1, max(1) from t1m where a=99; -select 1, max(1) from t1m where 1=99; - -# test with InnoDB -select min(a) from t1i; -select min(7) from t1i; -select min(7) from DUAL; -explain select min(7) from t2i join t1i; -select min(7) from t2i join t1i; - -select max(a) from t1i; -select max(7) from t1i; -select max(7) from DUAL; -explain select max(7) from t2i join t1i; -select max(7) from t2i join t1i; - -select 1, min(a) from t1i where a=99; -select 1, min(a) from t1i where 1=99; -select 1, min(1) from t1i where a=99; -select 1, min(1) from t1i where 1=99; - -select 1, max(a) from t1i where a=99; -select 1, max(a) from t1i where 1=99; -select 1, max(1) from t1i where a=99; -select 1, max(1) from t1i where 1=99; - -# mixed MyISAM/InnoDB test -explain select count(*), min(7), max(7) from t1m, t1i; -select count(*), min(7), max(7) from t1m, t1i; - -explain select count(*), min(7), max(7) from t1m, t2i; -select count(*), min(7), max(7) from t1m, t2i; - -explain select count(*), min(7), max(7) from t2m, t1i; -select count(*), min(7), max(7) from t2m, t1i; -drop table t1m, t1i, t2m, t2i; - -# -# Bug #12672: primary key implcitly included in every innodb index -# (was part of group_min_max.test) -# - -create table t1 ( - a1 char(64), a2 char(64), b char(16), c char(16) not null, d char(16), dummy char(64) default ' ' -); - -insert into t1 (a1, a2, b, c, d) values -('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'), -('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'), -('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'), -('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'), -('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'), -('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'), -('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'), -('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'), -('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'), -('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'), -('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'), -('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4'), -('d','a','a','a411','xy1'),('d','a','a','b411','xy2'),('d','a','a','c411','xy3'),('d','a','a','d411','xy4'), -('d','a','b','e412','xy1'),('d','a','b','f412','xy2'),('d','a','b','g412','xy3'),('d','a','b','h412','xy4'), -('d','b','a','i421','xy1'),('d','b','a','j421','xy2'),('d','b','a','k421','xy3'),('d','b','a','l421','xy4'), -('d','b','b','m422','xy1'),('d','b','b','n422','xy2'),('d','b','b','o422','xy3'),('d','b','b','p422','xy4'), -('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'), -('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'), -('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'), -('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'), -('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'), -('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'), -('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'), -('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'), -('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'), -('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'), -('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'), -('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4'), -('d','a','a','a411','xy1'),('d','a','a','b411','xy2'),('d','a','a','c411','xy3'),('d','a','a','d411','xy4'), -('d','a','b','e412','xy1'),('d','a','b','f412','xy2'),('d','a','b','g412','xy3'),('d','a','b','h412','xy4'), -('d','b','a','i421','xy1'),('d','b','a','j421','xy2'),('d','b','a','k421','xy3'),('d','b','a','l421','xy4'), -('d','b','b','m422','xy1'),('d','b','b','n422','xy2'),('d','b','b','o422','xy3'),('d','b','b','p422','xy4'); ---disable_warnings -create table t4 ( - pk_col int auto_increment primary key, a1 char(64), a2 char(64), b char(16), c char(16) not null, d char(16), dummy char(64) default ' ' -) engine=innodb; ---enable_warnings -insert into t4 (a1, a2, b, c, d, dummy) select * from t1; - -create index idx12672_0 on t4 (a1); -create index idx12672_1 on t4 (a1,a2,b,c); -create index idx12672_2 on t4 (a1,a2,b); -analyze table t1; - -select distinct a1 from t4 where pk_col not in (1,2,3,4); - -drop table t1,t4; - -# -# Bug #6142: a problem with the empty innodb table -# (was part of group_min_max.test) -# - ---disable_warnings -create table t1 ( - a varchar(30), b varchar(30), primary key(a), key(b) -) engine=innodb; ---enable_warnings -select distinct a from t1; -drop table t1; - -# -# Bug #9798: group by with rollup -# (was part of group_min_max.test) -# - ---disable_warnings -create table t1(a int, key(a)) engine=innodb; ---enable_warnings -insert into t1 values(1); -select a, count(a) from t1 group by a with rollup; -drop table t1; - -# -# Bug #13293 Wrongly used index results in endless loop. -# (was part of group_min_max.test) -# -create table t1 (f1 int, f2 char(1), primary key(f1,f2)) engine=innodb; -insert into t1 values ( 1,"e"),(2,"a"),( 3,"c"),(4,"d"); -alter table t1 drop primary key, add primary key (f2, f1); -explain select distinct f1 a, f1 b from t1; -explain select distinct f1, f2 from t1; -drop table t1; - - -# -# Test of behaviour with CREATE ... SELECT -# - -set storage_engine=innodb; -CREATE TABLE t1 (a int, b int); -insert into t1 values (1,1),(1,2); ---error 1062 -CREATE TABLE t2 (primary key (a)) select * from t1; -# This should give warning -drop table if exists t2; ---error 1062 -CREATE TEMPORARY TABLE t2 (primary key (a)) select * from t1; -# This should give warning -drop table if exists t2; -CREATE TABLE t2 (a int, b int, primary key (a)); -BEGIN; -INSERT INTO t2 values(100,100); ---error 1062 -CREATE TABLE IF NOT EXISTS t2 (primary key (a)) select * from t1; -SELECT * from t2; -ROLLBACK; -SELECT * from t2; -TRUNCATE table t2; ---error 1062 -INSERT INTO t2 select * from t1; -SELECT * from t2; -drop table t2; - -CREATE TEMPORARY TABLE t2 (a int, b int, primary key (a)); -BEGIN; -INSERT INTO t2 values(100,100); ---error 1062 -CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) select * from t1; -SELECT * from t2; -COMMIT; -BEGIN; -INSERT INTO t2 values(101,101); ---error 1062 -CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) select * from t1; -SELECT * from t2; -ROLLBACK; -SELECT * from t2; -TRUNCATE table t2; ---error 1062 -INSERT INTO t2 select * from t1; -SELECT * from t2; -drop table t1,t2; +-- source include/have_innodb.inc +let $engine_type= InnoDB; +let $other_engine_type= MEMORY; -# -# Bug#17530: Incorrect key truncation on table creation caused server crash. -# -create table t1(f1 varchar(800) binary not null, key(f1)) engine = innodb - character set utf8 collate utf8_general_ci; -insert into t1 values('aaa'); -drop table t1; +--source include/mix1.inc diff --git a/mysql-test/t/innodb_unsafe_binlog.test b/mysql-test/t/innodb_unsafe_binlog.test deleted file mode 100644 index af1091e4421..00000000000 --- a/mysql-test/t/innodb_unsafe_binlog.test +++ /dev/null @@ -1,248 +0,0 @@ --- source include/have_innodb.inc -# -# Note that these tests uses options -# innodb_locks_unsafe_for_binlog = true -# innodb_lock_timeout = 5 - -# -# Test cases for a bug #15650 -# - ---disable_warnings -drop table if exists t1,t2; ---enable_warnings -create table t1 (id int not null, f_id int not null, f int not null, -primary key(f_id, id)) engine=innodb; -create table t2 (id int not null,s_id int not null,s varchar(200), -primary key(id)) engine=innodb; -INSERT INTO t1 VALUES (8, 1, 3); -INSERT INTO t1 VALUES (1, 2, 1); -INSERT INTO t2 VALUES (1, 0, ''); -INSERT INTO t2 VALUES (8, 1, ''); -commit; -DELETE ml.* FROM t1 AS ml LEFT JOIN t2 AS mm ON (mm.id=ml.id) -WHERE mm.id IS NULL; -select ml.* from t1 as ml left join t2 as mm on (mm.id=ml.id) -where mm.id is null lock in share mode; -drop table t1,t2; - -# -# Test case for unlock row bug where unlock releases all locks granted for -# a row. Only the latest lock should be released. -# - -connect (a,localhost,root,,); -connect (b,localhost,root,,); -connection a; -create table t1(a int not null, b int, primary key(a)) engine=innodb; -insert into t1 values(1,1),(2,2),(3,1),(4,2),(5,1),(6,2),(7,3); -commit; -set autocommit = 0; -select * from t1 lock in share mode; -update t1 set b = 5 where b = 1; -connection b; -set autocommit = 0; -# -# S-lock to records (2,2),(4,2), and (6,2) should not be released in a update -# ---error 1205 -select * from t1 where a = 2 and b = 2 for update; -connection a; -commit; -connection b; -commit; -drop table t1; -connection default; -disconnect a; -disconnect b; - -# -# unlock row test -# - -connect (a,localhost,root,,); -connect (b,localhost,root,,); -connection a; -create table t1(a int not null, b int, primary key(a)) engine=innodb; -insert into t1 values(1,1),(2,2),(3,1),(4,2),(5,1),(6,2),(7,3); -commit; -set autocommit = 0; -update t1 set b = 5 where b = 1; -connection b; -set autocommit = 0; -# -# X-lock to record (7,3) should be released in a update -# -select * from t1 where a = 7 and b = 3 for update; -commit; -connection a; -commit; -drop table t1; -connection default; -disconnect a; -disconnect b; - - -# -# Consistent read should be used in following selects -# -# 1) INSERT INTO ... SELECT -# 2) UPDATE ... = ( SELECT ...) -# 3) CREATE ... SELECT - -connect (a,localhost,root,,); -connect (b,localhost,root,,); -connection a; -create table t1(a int not null, b int, primary key(a)) engine=innodb; -insert into t1 values (1,2),(5,3),(4,2); -create table t2(d int not null, e int, primary key(d)) engine=innodb; -insert into t2 values (8,6),(12,1),(3,1); -commit; -set autocommit = 0; -select * from t2 for update; -connection b; -set autocommit = 0; -insert into t1 select * from t2; -update t1 set b = (select e from t2 where a = d); -create table t3(d int not null, e int, primary key(d)) engine=innodb -select * from t2; -commit; -connection a; -commit; -connection default; -disconnect a; -disconnect b; -drop table t1, t2, t3; - -# -# Consistent read should not be used if -# -# (a) isolation level is serializable OR -# (b) select ... lock in share mode OR -# (c) select ... for update -# -# in following queries: -# -# 1) INSERT INTO ... SELECT -# 2) UPDATE ... = ( SELECT ...) -# 3) CREATE ... SELECT - -connect (a,localhost,root,,); -connect (b,localhost,root,,); -connect (c,localhost,root,,); -connect (d,localhost,root,,); -connect (e,localhost,root,,); -connect (f,localhost,root,,); -connect (g,localhost,root,,); -connect (h,localhost,root,,); -connect (i,localhost,root,,); -connect (j,localhost,root,,); -connection a; -create table t1(a int not null, b int, primary key(a)) engine=innodb; -insert into t1 values (1,2),(5,3),(4,2); -create table t2(a int not null, b int, primary key(a)) engine=innodb; -insert into t2 values (8,6),(12,1),(3,1); -create table t3(d int not null, b int, primary key(d)) engine=innodb; -insert into t3 values (8,6),(12,1),(3,1); -create table t5(a int not null, b int, primary key(a)) engine=innodb; -insert into t5 values (1,2),(5,3),(4,2); -create table t6(d int not null, e int, primary key(d)) engine=innodb; -insert into t6 values (8,6),(12,1),(3,1); -create table t8(a int not null, b int, primary key(a)) engine=innodb; -insert into t8 values (1,2),(5,3),(4,2); -create table t9(d int not null, e int, primary key(d)) engine=innodb; -insert into t9 values (8,6),(12,1),(3,1); -commit; -set autocommit = 0; -select * from t2 for update; -connection b; -set autocommit = 0; -SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; ---send -insert into t1 select * from t2; -connection c; -set autocommit = 0; -SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; ---send -update t3 set b = (select b from t2 where a = d); -connection d; -set autocommit = 0; -SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; ---send -create table t4(a int not null, b int, primary key(a)) engine=innodb select * from t2; -connection e; -set autocommit = 0; ---send -insert into t5 (select * from t2 lock in share mode); -connection f; -set autocommit = 0; ---send -update t6 set e = (select b from t2 where a = d lock in share mode); -connection g; -set autocommit = 0; ---send -create table t7(a int not null, b int, primary key(a)) engine=innodb select * from t2 lock in share mode; -connection h; -set autocommit = 0; ---send -insert into t8 (select * from t2 for update); -connection i; -set autocommit = 0; ---send -update t9 set e = (select b from t2 where a = d for update); -connection j; -set autocommit = 0; ---send -create table t10(a int not null, b int, primary key(a)) engine=innodb select * from t2 for update; - -connection b; ---error 1205 -reap; - -connection c; ---error 1205 -reap; - -connection d; ---error 1205 -reap; - -connection e; ---error 1205 -reap; - -connection f; ---error 1205 -reap; - -connection g; ---error 1205 -reap; - -connection h; ---error 1205 -reap; - -connection i; ---error 1205 -reap; - -connection j; ---error 1205 -reap; - -connection a; -commit; - -connection default; -disconnect a; -disconnect b; -disconnect c; -disconnect d; -disconnect e; -disconnect f; -disconnect g; -disconnect h; -disconnect i; -disconnect j; -drop table t1, t2, t3, t5, t6, t8, t9; diff --git a/mysql-test/t/mix2_myisam.test b/mysql-test/t/mix2_myisam.test new file mode 100644 index 00000000000..65e22f5d540 --- /dev/null +++ b/mysql-test/t/mix2_myisam.test @@ -0,0 +1,19 @@ +# t/mix2_myisam.test +# +# Last update: 2006-07-26 ML create this test as derivate from innodb.test +# + +let $engine_type= MyISAM; +let $other_engine_type= MEMORY; +# MyISAM does not support transactions +let $test_transactions= 0; +# MyISAM does not support FOREIGN KEYFOREIGN KEYs +let $test_foreign_keys= 0; +# MyISAM supports fulltext queries +let $fulltext_query_unsupported= 0; +# MyISAM internal autoincrement values are updated during updates +let $no_autoinc_update= 0; +# MyISAM supports keys on spatial data types +let $no_spatial_key= 0; + +-- source include/mix2.inc diff --git a/mysql-test/t/read_many_rows_innodb.test b/mysql-test/t/read_many_rows_innodb.test new file mode 100644 index 00000000000..0f24dcf92bf --- /dev/null +++ b/mysql-test/t/read_many_rows_innodb.test @@ -0,0 +1,16 @@ +# t/read_many_rows_innodb.test +# +# Check how filesort and buffered-record-reads works with InnoDB. +# This test takes a long time. +# +# Last update: +# 2006-08-03 ML test refactored (MySQL 5.1) +# main code --> include/read_many_rows_innodb.inc +# + +--source include/big_test.inc + +--source include/have_innodb.inc +let $engine_type= InnoDB; + +--source include/read_many_rows.inc diff --git a/mysql-test/t/rowid_order_innodb.test b/mysql-test/t/rowid_order_innodb.test index fb4959d78e6..152eb28d388 100644 --- a/mysql-test/t/rowid_order_innodb.test +++ b/mysql-test/t/rowid_order_innodb.test @@ -1,108 +1,14 @@ +# t/rowid_order_innodb.test # # Test for rowid ordering (and comparison) functions. # do index_merge select for tables with PK of various types. # ---disable_warnings -drop table if exists t1, t2, t3,t4; ---enable_warnings - --- source include/have_innodb.inc - -# Signed number as rowid -create table t1 ( - pk1 int not NULL, - key1 int(11), - key2 int(11), - PRIMARY KEY (pk1), - KEY key1 (key1), - KEY key2 (key2) -) engine=innodb; -insert into t1 values (-5, 1, 1), - (-100, 1, 1), - (3, 1, 1), - (0, 1, 1), - (10, 1, 1); -explain select * from t1 force index(key1, key2) where key1 < 3 or key2 < 3; -select * from t1 force index(key1, key2) where key1 < 3 or key2 < 3; -drop table t1; - -# Unsigned numbers as rowids -create table t1 ( - pk1 int unsigned not NULL, - key1 int(11), - key2 int(11), - PRIMARY KEY (pk1), - KEY key1 (key1), - KEY key2 (key2) -) engine=innodb; -insert into t1 values (0, 1, 1), - (0xFFFFFFFF, 1, 1), - (0xFFFFFFFE, 1, 1), - (1, 1, 1), - (2, 1, 1); -select * from t1 force index(key1, key2) where key1 < 3 or key2 < 3; -drop table t1; - -# Case-insensitive char(N) -create table t1 ( - pk1 char(4) not NULL, - key1 int(11), - key2 int(11), - PRIMARY KEY (pk1), - KEY key1 (key1), - KEY key2 (key2) -) engine=innodb collate latin2_general_ci; -insert into t1 values ('a1', 1, 1), - ('b2', 1, 1), - ('A3', 1, 1), - ('B4', 1, 1); -select * from t1 force index(key1, key2) where key1 < 3 or key2 < 3; -drop table t1; - -# Multi-part PK -create table t1 ( - pk1 int not NULL, - pk2 char(4) not NULL collate latin1_german1_ci, - pk3 char(4) not NULL collate latin1_bin, - key1 int(11), - key2 int(11), - PRIMARY KEY (pk1,pk2,pk3), - KEY key1 (key1), - KEY key2 (key2) -) engine=innodb; -insert into t1 values - (1, 'u', 'u', 1, 1), - (1, 'u', char(0xEC), 1, 1), - (1, 'u', 'x', 1, 1); -insert ignore into t1 select pk1, char(0xEC), pk3, key1, key2 from t1; -insert ignore into t1 select pk1, 'x', pk3, key1, key2 from t1 where pk2='u'; -insert ignore into t1 select 2, pk2, pk3, key1, key2 from t1; -select * from t1; -select * from t1 force index(key1, key2) where key1 < 3 or key2 < 3; - -# Hidden PK -alter table t1 drop primary key; -select * from t1; -select * from t1 force index(key1, key2) where key1 < 3 or key2 < 3; -drop table t1; - -# Variable-length PK -# this is also test for Bug#2688 -create table t1 ( - pk1 varchar(8) NOT NULL default '', - pk2 varchar(4) NOT NULL default '', - key1 int(11), - key2 int(11), - primary key(pk1, pk2), - KEY key1 (key1), - KEY key2 (key2) -) engine=innodb; -insert into t1 values ('','empt',2,2), - ('a','a--a',2,2), - ('bb','b--b',2,2), - ('ccc','c--c',2,2), - ('dddd','d--d',2,2); -select * from t1 force index(key1, key2) where key1 < 3 or key2 < 3; +# Last update: +# 2006-07-28 ML test refactored (MySQL 5.1) +# main code t/rowid_order_innodb.test -> include/rowid_order.inc +# -drop table t1; +--source include/have_innodb.inc +let $engine_type= InnoDB; +--source include/rowid_order.inc diff --git a/mysql-test/t/innodb_unsafe_binlog-master.opt b/mysql-test/t/unsafe_binlog_innodb-master.opt index 9581c225d6d..9581c225d6d 100644 --- a/mysql-test/t/innodb_unsafe_binlog-master.opt +++ b/mysql-test/t/unsafe_binlog_innodb-master.opt diff --git a/mysql-test/t/unsafe_binlog_innodb.test b/mysql-test/t/unsafe_binlog_innodb.test new file mode 100644 index 00000000000..a0516749451 --- /dev/null +++ b/mysql-test/t/unsafe_binlog_innodb.test @@ -0,0 +1,16 @@ +# t/unsafe_binlog_innodb.test +# +# Note that this test uses at least in case of InnoDB options +# innodb_locks_unsafe_for_binlog = true +# innodb_lock_timeout = 5 +# +# Last update: +# 2006-08-02 ML test refactored +# old name was innodb_unsafe_binlog.test +# main code went into include/unsafe_binlog.inc +# + +--source include/have_innodb.inc +let $engine_type= InnoDB; + +--source include/unsafe_binlog.inc |