diff options
| author | Sergei Golubchik <sergii@pisem.net> | 2011-10-19 21:45:18 +0200 |
|---|---|---|
| committer | Sergei Golubchik <sergii@pisem.net> | 2011-10-19 21:45:18 +0200 |
| commit | 76f0b94bb0b2994d639353530c5b251d0f1a204b (patch) | |
| tree | 9ed50628aac34f89a37637bab2fc4915b86b5eb4 /mysql-test/include | |
| parent | 4e46d8e5bff140f2549841167dc4b65a3c0a645d (diff) | |
| parent | 5dc1a2231f55bacc9aaf0e24816f3d9c2ee1f21d (diff) | |
| download | mariadb-git-76f0b94bb0b2994d639353530c5b251d0f1a204b.tar.gz | |
merge with 5.3
sql/sql_insert.cc:
CREATE ... IF NOT EXISTS may do nothing, but
it is still not a failure. don't forget to my_ok it.
******
CREATE ... IF NOT EXISTS may do nothing, but
it is still not a failure. don't forget to my_ok it.
sql/sql_table.cc:
small cleanup
******
small cleanup
Diffstat (limited to 'mysql-test/include')
34 files changed, 669 insertions, 1898 deletions
diff --git a/mysql-test/include/binlog_start_pos.inc b/mysql-test/include/binlog_start_pos.inc new file mode 100644 index 00000000000..add5a42a426 --- /dev/null +++ b/mysql-test/include/binlog_start_pos.inc @@ -0,0 +1,28 @@ +############################################################################## +# +# binlog_start_pos is the postion of the the first event in the binary log +# which follows the Format description event. Intended to reduce test suite +# dependance on the Format description event length changes (e.g. in case +# of adding new events). Evaluated as: +# +# binlog_start_pos = 4 /* binlog header */ + +# (Format_description_log_event length) +# +# Format_description_log_event length = +# 19 /* event common header */ + +# 57 /* misc stuff in the Format description header */ + +# number of events + +# 1 /* Checksum algorithm */ + +# 4 /* CRC32 length */ +# +# With current number of events = 160, +# +# binlog_start_pos = 4 + 19 + 57 + 160 + 1 + 4 = 245. +# +############################################################################## + +let $binlog_start_pos=245; +--disable_query_log +SET @binlog_start_pos=245; +--enable_query_log + diff --git a/mysql-test/include/check_shared_row_lock.inc b/mysql-test/include/check_shared_row_lock.inc index efc7e13b3aa..1c9d9b0c3c6 100644 --- a/mysql-test/include/check_shared_row_lock.inc +++ b/mysql-test/include/check_shared_row_lock.inc @@ -33,7 +33,8 @@ connection default; # least it acquires S-locks on some of rows. let $wait_condition= select count(*) = 1 from information_schema.processlist - where state in ("Sending data","statistics", "preparing") and + where state in ("Sending data","statistics", "preparing", "updating", + "executing", "Searching rows for update") and info = "$wait_statement"; --source include/wait_condition.inc diff --git a/mysql-test/include/ddl_i18n.check_sp.inc b/mysql-test/include/ddl_i18n.check_sp.inc index c182f797847..d88f4335729 100644 --- a/mysql-test/include/ddl_i18n.check_sp.inc +++ b/mysql-test/include/ddl_i18n.check_sp.inc @@ -36,19 +36,19 @@ SHOW PROCEDURE STATUS LIKE 'p4'| --echo --echo ---replace_column 23 CREATED 24 ALTERED +--replace_column 24 CREATED 25 ALTERED SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE routine_name = 'p1'| --echo ---replace_column 23 CREATED 24 ALTERED +--replace_column 24 CREATED 25 ALTERED SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE routine_name = 'p2'| --echo ---replace_column 23 CREATED 24 ALTERED +--replace_column 24 CREATED 25 ALTERED SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE routine_name = 'p3'| --echo ---replace_column 23 CREATED 24 ALTERED +--replace_column 24 CREATED 25 ALTERED SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE routine_name = 'p4'| # - Initialize the used variables (actual values don't matter); diff --git a/mysql-test/include/diff_tables.inc b/mysql-test/include/diff_tables.inc index 5f88d8d3073..b5ee4db0e8f 100644 --- a/mysql-test/include/diff_tables.inc +++ b/mysql-test/include/diff_tables.inc @@ -178,13 +178,16 @@ while ($_dt_tables) } --diff_files $_dt_prev_outfile $_dt_outfile # Remove previous outfile. Keep current file for comparison with next table. + --disable_warnings --remove_file $_dt_prev_outfile + --enable_warnings } --let $_dt_prev_outfile= $_dt_outfile } +--disable_warnings --remove_file $_dt_prev_outfile - +--enable_warnings --let $include_filename= diff_tables.inc [$diff_tables] --source include/end_include_file.inc diff --git a/mysql-test/include/handler.inc b/mysql-test/include/handler.inc deleted file mode 100644 index 57d368960bf..00000000000 --- a/mysql-test/include/handler.inc +++ /dev/null @@ -1,1838 +0,0 @@ -# include/handler.inc -# -# The variables -# $engine_type -- storage engine to be tested -# $other_engine_type -- storage engine <> $engine_type -# $other_handler_engine_type -- storage engine <> $engine_type, if possible -# 1. $other_handler_engine_type must support handler -# 2. $other_handler_engine_type must point to an all -# time available storage engine -# 2006-08 MySQL 5.1 MyISAM and MEMORY only -# have to be set before sourcing this script. --- source include/not_embedded.inc -# -# 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 -# - -eval SET SESSION STORAGE_ENGINE = $engine_type; - ---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; - -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; -eval alter table t1 engine = $engine_type; ---error 1109 -handler t2 read first; - -# -# 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; -eval alter table t1 engine=$other_engine_type; ---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 -eval alter table t1 engine=$other_handler_engine_type; ---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 -eval alter table t5 engine=$other_handler_engine_type; ---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 -eval alter table t3 engine=$other_handler_engine_type; ---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; -eval alter table t1 engine=$engine_type; ---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; - -CREATE TABLE t1 ( no1 smallint(5) NOT NULL default '0', no2 int(10) NOT NULL default '0', PRIMARY KEY (no1,no2)); -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 - -# -# 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; - -# -# Bug#25856 - HANDLER table OPEN in one connection lock DROP TABLE in another one -# ---disable_warnings -drop table if exists t1; ---enable_warnings -eval create table t1 (a int) ENGINE=$other_engine_type; ---echo --> client 2 -connection con2; ---error 1031 -handler t1 open; ---echo --> client 1 -connection default; -drop table t1; -disconnect con2; - -# -# Bug#30632 HANDLER read failure causes hang -# ---disable_warnings -drop table if exists t1; ---enable_warnings -create table t1 (a int); -handler t1 open as t1_alias; ---error 1176 -handler t1_alias read a next; ---error 1054 -handler t1_alias READ a next where inexistent > 0; ---error 1176 -handler t1_alias read a next; ---error 1054 -handler t1_alias READ a next where inexistent > 0; -handler t1_alias close; -drop table t1; - -# -# Bug#21587 FLUSH TABLES causes server crash when used with HANDLER statements -# - ---disable_warnings -drop table if exists t1,t2; ---enable_warnings -create table t1 (c1 int); -create table t2 (c1 int); -insert into t1 values (1); -insert into t2 values (2); ---echo connection: default -handler t1 open; -handler t1 read first; -connect (flush,localhost,root,,); -connection flush; ---echo connection: flush ---send flush tables; -connect (waiter,localhost,root,,); -connection waiter; ---echo connection: waiter -let $wait_condition= - select count(*) = 1 from information_schema.processlist - where state = "Waiting for table flush"; ---source include/wait_condition.inc -connection default; ---echo connection: default -handler t2 open; -handler t2 read first; -handler t1 read next; -handler t1 close; -handler t2 close; -connection flush; -reap; -connection default; -drop table t1,t2; -disconnect flush; - -# -# Bug#31409 RENAME TABLE causes server crash or deadlock when used with HANDLER statements -# - ---disable_warnings -drop table if exists t1, t0; ---enable_warnings -create table t1 (c1 int); ---echo connection: default -handler t1 open; -handler t1 read first; -connect (flush,localhost,root,,); -connection flush; ---echo connection: flush ---send rename table t1 to t0; -connection waiter; ---echo connection: waiter -let $wait_condition= - select count(*) = 1 from information_schema.processlist - where state = "Waiting for table metadata lock" and - info = "rename table t1 to t0"; ---source include/wait_condition.inc -connection default; ---echo connection: default ---echo # ---echo # RENAME placed two pending locks and waits. ---echo # When HANDLER t0 OPEN does open_tables(), it calls ---echo # mysql_ha_flush(), which in turn closes the open HANDLER for t1. ---echo # RENAME TABLE gets unblocked. If it gets scheduled quickly ---echo # and manages to complete before open_tables() ---echo # of HANDLER t0 OPEN, open_tables() and therefore the whole ---echo # HANDLER t0 OPEN succeeds. Otherwise open_tables() ---echo # notices a pending or active exclusive metadata lock on t2 ---echo # and the whole HANDLER t0 OPEN fails with ER_LOCK_DEADLOCK ---echo # error. ---echo # ---error 0, ER_LOCK_DEADLOCK -handler t0 open; ---error 0, ER_UNKNOWN_TABLE -handler t0 close; ---echo connection: flush -connection flush; -reap; ---error ER_UNKNOWN_TABLE -handler t1 read next; ---error ER_UNKNOWN_TABLE -handler t1 close; -connection default; -drop table t0; -connection flush; -disconnect flush; ---source include/wait_until_disconnected.inc -connection waiter; -disconnect waiter; ---source include/wait_until_disconnected.inc -connection default; - -# -# Bug#30882 Dropping a temporary table inside a stored function may cause a server crash -# -# Test HANDLER statements in conjunction with temporary tables. While the temporary table -# is open by a HANDLER, no other statement can access it. -# - ---disable_warnings -drop table if exists t1; ---enable_warnings -create temporary table t1 (a int, b char(1), key a(a), key b(a,b)); -insert into t1 values (0,"a"),(1,"b"),(2,"c"),(3,"d"),(4,"e"), - (5,"f"),(6,"g"),(7,"h"),(8,"i"),(9,"j"); -select a,b from t1; -handler t1 open as a1; -handler a1 read a first; -handler a1 read a next; -handler a1 read a next; ---error ER_CANT_REOPEN_TABLE -select a,b from t1; -handler a1 read a prev; -handler a1 read a prev; -handler a1 read a=(6) where b="g"; -handler a1 close; -select a,b from t1; -handler t1 open as a2; -handler a2 read a first; -handler a2 read a last; -handler a2 read a prev; -handler a2 close; -drop table t1; - -# -# Bug#31397 Inconsistent drop table behavior of handler tables. -# - ---disable_warnings -drop table if exists t1,t2; ---enable_warnings -create table t1 (a int); -handler t1 open as t1_alias; -drop table t1; -create table t1 (a int); -handler t1 open as t1_alias; -flush tables; -drop table t1; -create table t1 (a int); -handler t1 open as t1_alias; -handler t1_alias close; -drop table t1; -create table t1 (a int); -handler t1 open as t1_alias; -handler t1_alias read first; -drop table t1; ---error ER_UNKNOWN_TABLE -handler t1_alias read next; - -# Test that temporary tables associated with handlers are properly dropped. - -create table t1 (a int); -create temporary table t2 (a int, key(a)); -handler t1 open as a1; -handler t2 open as a2; -handler a2 read a first; -drop table t1, t2; ---error ER_UNKNOWN_TABLE -handler a2 read a next; ---error ER_UNKNOWN_TABLE -handler a1 close; - -# Alter table drop handlers - -create table t1 (a int, key(a)); -create table t2 like t1; -handler t1 open as a1; -handler t2 open as a2; -handler a1 read a first; -handler a2 read a first; -alter table t1 add b int; ---error ER_UNKNOWN_TABLE -handler a1 close; -handler a2 close; -drop table t1, t2; - -# Rename table drop handlers - -create table t1 (a int, key(a)); -handler t1 open as a1; -handler a1 read a first; -rename table t1 to t2; ---error ER_UNKNOWN_TABLE -handler a1 read a first; -drop table t2; - -# Optimize table drop handlers - -create table t1 (a int, key(a)); -create table t2 like t1; -handler t1 open as a1; -handler t2 open as a2; -handler a1 read a first; -handler a2 read a first; -optimize table t1; ---error ER_UNKNOWN_TABLE -handler a1 close; -handler a2 close; -drop table t1, t2; - -# Flush tables causes handlers reopen - -create table t1 (a int, b char(1), key a(a), key b(a,b)); -insert into t1 values (0,"a"),(1,"b"),(2,"c"),(3,"d"),(4,"e"), - (5,"f"),(6,"g"),(7,"h"),(8,"i"),(9,"j"); -handler t1 open; -handler t1 read a first; -handler t1 read a next; -flush tables; -handler t1 read a next; -handler t1 read a next; -flush tables with read lock; -handler t1 read a next; -unlock tables; -drop table t1; ---error ER_UNKNOWN_TABLE -handler t1 read a next; - -# -# Bug#41110: crash with handler command when used concurrently with alter table -# Bug#41112: crash in mysql_ha_close_table/get_lock_data with alter table -# - -connect(con1,localhost,root,,); -connect(con2,localhost,root,,); - -connection default; ---disable_warnings -drop table if exists t1; ---enable_warnings ---echo # First test case which is supposed trigger the execution ---echo # path on which problem was discovered. -create table t1 (a int); -insert into t1 values (1); -handler t1 open; -connection con1; -lock table t1 write; -send alter table t1 engine=memory; -connection con2; -let $wait_condition= - select count(*) = 1 from information_schema.processlist - where state = "Waiting for table metadata lock" and - info = "alter table t1 engine=memory"; ---source include/wait_condition.inc -connection default; ---error ER_ILLEGAL_HA -handler t1 read a next; -handler t1 close; -connection con1; ---reap -unlock tables; -drop table t1; ---echo # Now test case which was reported originally but which no longer ---echo # triggers execution path which has caused the problem. -connection default; -create table t1 (a int, key(a)); -insert into t1 values (1); -handler t1 open; -connection con1; -send alter table t1 engine=memory; -connection con2; -let $wait_condition= - select count(*) = 1 from information_schema.processlist - where state = "Waiting for table metadata lock" and - info = "alter table t1 engine=memory"; ---source include/wait_condition.inc -connection default; ---echo # Since S metadata lock was already acquired at HANDLER OPEN time ---echo # and TL_READ lock requested by HANDLER READ is compatible with ---echo # ALTER's TL_WRITE_ALLOW_READ the below statement should succeed ---echo # without waiting. The old version of table should be used in it. -handler t1 read a next; -handler t1 close; -connection con1; ---reap # Since last in this connection was a send -drop table t1; -disconnect con1; ---source include/wait_until_disconnected.inc -connection con2; -disconnect con2; ---source include/wait_until_disconnected.inc -connection default; - -# -# Bug#44151 using handler commands on information_schema tables crashes server -# -USE information_schema; ---error ER_WRONG_USAGE -HANDLER COLUMNS OPEN; -USE test; - ---echo # ---echo # Add test coverage for HANDLER and LOCK TABLES, HANDLER and DDL. ---echo # ---disable_warnings -drop table if exists t1, t2, t3; ---enable_warnings -create table t1 (a int, key a (a)); -insert into t1 (a) values (1), (2), (3), (4), (5); -create table t2 (a int, key a (a)) select * from t1; -create temporary table t3 (a int, key a (a)) select * from t2; -handler t1 open; -handler t2 open; -handler t3 open; ---echo # ---echo # No HANDLER sql is allowed under LOCK TABLES. ---echo # But it does not implicitly closes all handlers. ---echo # -lock table t1 read; ---error ER_LOCK_OR_ACTIVE_TRANSACTION -handler t1 open; ---error ER_LOCK_OR_ACTIVE_TRANSACTION -handler t1 read next; ---error ER_LOCK_OR_ACTIVE_TRANSACTION -handler t2 close; ---error ER_LOCK_OR_ACTIVE_TRANSACTION -handler t3 open; ---echo # After UNLOCK TABLES handlers should be around and ---echo # we should be able to continue reading through them. -unlock tables; -handler t1 read next; -handler t1 close; -handler t2 read next; -handler t2 close; -handler t3 read next; -handler t3 close; -drop temporary table t3; ---echo # ---echo # Other operations that implicitly close handler: ---echo # ---echo # TRUNCATE ---echo # -handler t1 open; -truncate table t1; ---error ER_UNKNOWN_TABLE -handler t1 read next; -handler t1 open; ---echo # ---echo # CREATE TRIGGER ---echo # -create trigger t1_ai after insert on t1 for each row set @a=1; ---error ER_UNKNOWN_TABLE -handler t1 read next; ---echo # ---echo # DROP TRIGGER ---echo # -handler t1 open; -drop trigger t1_ai; ---error ER_UNKNOWN_TABLE -handler t1 read next; ---echo # ---echo # ALTER TABLE ---echo # -handler t1 open; -alter table t1 add column b int; ---error ER_UNKNOWN_TABLE -handler t1 read next; ---echo # ---echo # ANALYZE TABLE ---echo # -handler t1 open; -analyze table t1; ---error ER_UNKNOWN_TABLE -handler t1 read next; ---echo # ---echo # OPTIMIZE TABLE ---echo # -handler t1 open; -optimize table t1; ---error ER_UNKNOWN_TABLE -handler t1 read next; ---echo # ---echo # REPAIR TABLE ---echo # -handler t1 open; -repair table t1; ---error ER_UNKNOWN_TABLE -handler t1 read next; ---echo # ---echo # DROP TABLE, naturally. ---echo # -handler t1 open; -drop table t1; ---error ER_UNKNOWN_TABLE -handler t1 read next; -create table t1 (a int, b int, key a (a)) select a from t2; ---echo # ---echo # RENAME TABLE, naturally ---echo # -handler t1 open; -rename table t1 to t3; ---error ER_UNKNOWN_TABLE -handler t1 read next; ---echo # ---echo # CREATE TABLE (even with IF NOT EXISTS clause, ---echo # and the table exists). ---echo # -handler t2 open; -create table if not exists t2 (a int); ---error ER_UNKNOWN_TABLE -handler t2 read next; -rename table t3 to t1; -drop table t2; ---echo # ---echo # FLUSH TABLE doesn't close the table but loses the position ---echo # -handler t1 open; -handler t1 read a prev; -flush table t1; -handler t1 read a prev; -handler t1 close; ---echo # ---echo # FLUSH TABLES WITH READ LOCK behaves like FLUSH TABLE. ---echo # -handler t1 open; -handler t1 read a prev; -flush tables with read lock; -handler t1 read a prev; -handler t1 close; -unlock tables; ---echo # ---echo # Let us also check that these operations behave in similar ---echo # way under LOCK TABLES. ---echo # ---echo # TRUNCATE under LOCK TABLES. ---echo # -handler t1 open; -lock tables t1 write; -truncate table t1; -unlock tables; ---error ER_UNKNOWN_TABLE -handler t1 read next; -handler t1 open; ---echo # ---echo # CREATE TRIGGER under LOCK TABLES. ---echo # -lock tables t1 write; -create trigger t1_ai after insert on t1 for each row set @a=1; -unlock tables; ---error ER_UNKNOWN_TABLE -handler t1 read next; ---echo # ---echo # DROP TRIGGER under LOCK TABLES. ---echo # -handler t1 open; -lock tables t1 write; -drop trigger t1_ai; -unlock tables; ---error ER_UNKNOWN_TABLE -handler t1 read next; ---echo # ---echo # ALTER TABLE under LOCK TABLES. ---echo # -handler t1 open; -lock tables t1 write; -alter table t1 drop column b; -unlock tables; ---error ER_UNKNOWN_TABLE -handler t1 read next; ---echo # ---echo # ANALYZE TABLE under LOCK TABLES. ---echo # -handler t1 open; -lock tables t1 write; -analyze table t1; -unlock tables; ---error ER_UNKNOWN_TABLE -handler t1 read next; ---echo # ---echo # OPTIMIZE TABLE under LOCK TABLES. ---echo # -handler t1 open; -lock tables t1 write; -optimize table t1; -unlock tables; ---error ER_UNKNOWN_TABLE -handler t1 read next; ---echo # ---echo # REPAIR TABLE under LOCK TABLES. ---echo # -handler t1 open; -lock tables t1 write; -repair table t1; -unlock tables; ---error ER_UNKNOWN_TABLE -handler t1 read next; ---echo # ---echo # DROP TABLE under LOCK TABLES, naturally. ---echo # -handler t1 open; -lock tables t1 write; -drop table t1; -unlock tables; ---error ER_UNKNOWN_TABLE -handler t1 read next; -create table t1 (a int, b int, key a (a)); -insert into t1 (a) values (1), (2), (3), (4), (5); ---echo # ---echo # FLUSH TABLE doesn't close the table but loses the position ---echo # -handler t1 open; -handler t1 read a prev; -lock tables t1 write; -flush table t1; -unlock tables; -handler t1 read a prev; -handler t1 close; ---echo # ---echo # Explore the effect of HANDLER locks on concurrent DDL ---echo # -handler t1 open; ---echo # Establishing auxiliary connections con1, con2, con3 -connect(con1, localhost, root,,); -connect(con2, localhost, root,,); -connect(con3, localhost, root,,); ---echo # --> connection con1; -connection con1; ---echo # Sending: ---send drop table t1 ---echo # We can't use connection 'default' as wait_condition will ---echo # autoclose handlers. ---echo # --> connection con2 -connection con2; ---echo # Waitng for 'drop table t1' to get blocked... -let $wait_condition=select count(*)=1 from information_schema.processlist - where state='Waiting for table metadata lock' and - info='drop table t1'; ---source include/wait_condition.inc ---echo # --> connection default -connection default; -handler t1 read a prev; -handler t1 read a prev; -handler t1 close; ---echo # --> connection con1 -connection con1; ---echo # Reaping 'drop table t1'... ---reap ---echo # --> connection default -connection default; ---echo # ---echo # Explore the effect of HANDLER locks in parallel with SELECT ---echo # -create table t1 (a int, key a (a)); -insert into t1 (a) values (1), (2), (3), (4), (5); -begin; -select * from t1; -handler t1 open; -handler t1 read a prev; -handler t1 read a prev; -handler t1 close; ---echo # --> connection con1; -connection con1; ---echo # Sending: ---send drop table t1 ---echo # --> connection con2 -connection con2; ---echo # Waiting for 'drop table t1' to get blocked... -let $wait_condition=select count(*)=1 from information_schema.processlist - where state='Waiting for table metadata lock' and - info='drop table t1'; ---source include/wait_condition.inc ---echo # --> connection default -connection default; ---echo # We can still use the table, it's part of the transaction -select * from t1; ---echo # Such are the circumstances that t1 is a part of transaction, ---echo # thus we can reopen it in the handler -handler t1 open; ---echo # We can commit the transaction, it doesn't close the handler ---echo # and doesn't let DROP to proceed. -commit; -handler t1 read a prev; -handler t1 read a prev; -handler t1 read a prev; -handler t1 close; ---echo # --> connection con1 -connection con1; ---echo # Now drop can proceed ---echo # Reaping 'drop table t1'... ---reap ---echo # --> connection default -connection default; ---echo # ---echo # Demonstrate that HANDLER locks and transaction locks ---echo # reside in the same context, and we don't back-off ---echo # when have transaction or handler locks. ---echo # -create table t1 (a int, key a (a)); -insert into t1 (a) values (1), (2), (3), (4), (5); -create table t0 (a int, key a (a)); -insert into t0 (a) values (1), (2), (3), (4), (5); -begin; -select * from t1; ---echo # --> connection con2 -connection con2; ---echo # Sending: -send rename table t0 to t3, t1 to t0, t3 to t1; ---echo # --> connection con1 -connection con1; ---echo # Waiting for 'rename table ...' to get blocked... -let $wait_condition=select count(*)=1 from information_schema.processlist - where state='Waiting for table metadata lock' and - info='rename table t0 to t3, t1 to t0, t3 to t1'; ---source include/wait_condition.inc ---echo # --> connection default -connection default; ---error ER_LOCK_DEADLOCK -handler t0 open; ---error ER_LOCK_DEADLOCK -select * from t0; -handler t1 open; -commit; -handler t1 close; ---echo # --> connection con2 -connection con2; ---echo # Reaping 'rename table ...'... ---reap ---echo # --> connection default -connection default; -handler t1 open; -handler t1 read a prev; -handler t1 close; -drop table t0; ---echo # ---echo # Originally there was a deadlock error in this test. ---echo # With implementation of deadlock detector ---echo # we no longer deadlock, but block and wait on a lock. ---echo # The HANDLER is auto-closed as soon as the connection ---echo # sees a pending conflicting lock against it. ---echo # -create table t2 (a int, key a (a)); -handler t1 open; ---echo # --> connection con1 -connection con1; -lock tables t2 read; ---echo # --> connection con2 -connection con2; ---echo # Sending 'drop table t2'... ---send drop table t2 ---echo # --> connection con1 -connection con1; ---echo # Waiting for 'drop table t2' to get blocked... -let $wait_condition=select count(*)=1 from information_schema.processlist - where state='Waiting for table metadata lock' and - info='drop table t2'; ---source include/wait_condition.inc ---echo # --> connection default -connection default; ---echo # Sending 'select * from t2' -send select * from t2; ---echo # --> connection con1 -connection con1; ---echo # Waiting for 'select * from t2' to get blocked... -let $wait_condition=select count(*)=1 from information_schema.processlist - where state='Waiting for table metadata lock' and - info='select * from t2'; -unlock tables; ---echo # --> connection con2 -connection con2; ---echo # Reaping 'drop table t2'... ---reap ---echo # --> connection default -connection default; ---echo # Reaping 'select * from t2' ---error ER_NO_SUCH_TABLE -reap; -handler t1 close; - ---echo # ---echo # ROLLBACK TO SAVEPOINT releases transactional locks, ---echo # but has no effect on open HANDLERs ---echo # -create table t2 like t1; -create table t3 like t1; -begin; ---echo # Have something before the savepoint -select * from t3; -savepoint sv; -handler t1 open; -handler t1 read a first; -handler t1 read a next; -select * from t2; ---echo # --> connection con1 -connection con1; ---echo # Sending: ---send drop table t1 ---echo # --> connection con2 -connection con2; ---echo # Sending: ---send drop table t2 ---echo # --> connection default -connection default; ---echo # Let DROP TABLE statements sync in. We must use ---echo # a separate connection for that, because otherwise SELECT ---echo # will auto-close the HANDLERs, becaues there are pending ---echo # exclusive locks against them. ---echo # --> connection con3 -connection con3; ---echo # Waiting for 'drop table t1' to get blocked... -let $wait_condition=select count(*)=1 from information_schema.processlist - where state='Waiting for table metadata lock' and - info='drop table t1'; ---source include/wait_condition.inc ---echo # Waiting for 'drop table t2' to get blocked... -let $wait_condition=select count(*)=1 from information_schema.processlist - where state='Waiting for table metadata lock' and - info='drop table t2'; ---source include/wait_condition.inc ---echo # Demonstrate that t2 lock was released and t2 was dropped ---echo # after ROLLBACK TO SAVEPOINT ---echo # --> connection default -connection default; -rollback to savepoint sv; ---echo # --> connection con2 -connection con2; ---echo # Reaping 'drop table t2'... ---reap ---echo # Demonstrate that ROLLBACK TO SAVEPOINT didn't release the handler ---echo # lock. ---echo # --> connection default -connection default; -handler t1 read a next; -handler t1 read a next; ---echo # Demonstrate that the drop will go through as soon as we close the ---echo # HANDLER -handler t1 close; ---echo # connection con1 -connection con1; ---echo # Reaping 'drop table t1'... ---reap ---echo # --> connection default -connection default; -commit; -drop table t3; ---echo # ---echo # A few special cases when using SAVEPOINT/ROLLBACK TO ---echo # SAVEPOINT and HANDLER. ---echo # ---echo # Show that rollback to the savepoint taken in the beginning ---echo # of the transaction doesn't release mdl lock on ---echo # the HANDLER that was opened later. ---echo # -create table t1 (a int, key a(a)); -insert into t1 (a) values (1), (2), (3), (4), (5); -create table t2 like t1; -begin; -savepoint sv; -handler t1 open; -handler t1 read a first; -handler t1 read a next; -select * from t2; ---echo # --> connection con1 -connection con1; ---echo # Sending: ---send drop table t1 ---echo # --> connection con2 -connection con2; ---echo # Sending: ---send drop table t2 ---echo # --> connection default -connection default; ---echo # Let DROP TABLE statements sync in. We must use ---echo # a separate connection for that, because otherwise SELECT ---echo # will auto-close the HANDLERs, becaues there are pending ---echo # exclusive locks against them. ---echo # --> connection con3 -connection con3; ---echo # Waiting for 'drop table t1' to get blocked... -let $wait_condition=select count(*)=1 from information_schema.processlist - where state='Waiting for table metadata lock' and - info='drop table t1'; ---source include/wait_condition.inc ---echo # Waiting for 'drop table t2' to get blocked... -let $wait_condition=select count(*)=1 from information_schema.processlist - where state='Waiting for table metadata lock' and - info='drop table t2'; ---source include/wait_condition.inc ---echo # Demonstrate that t2 lock was released and t2 was dropped ---echo # after ROLLBACK TO SAVEPOINT ---echo # --> connection default -connection default; -rollback to savepoint sv; ---echo # --> connection con2 -connection con2; ---echo # Reaping 'drop table t2'... ---reap ---echo # Demonstrate that ROLLBACK TO SAVEPOINT didn't release the handler ---echo # lock. ---echo # --> connection default -connection default; -handler t1 read a next; -handler t1 read a next; ---echo # Demonstrate that the drop will go through as soon as we close the ---echo # HANDLER -handler t1 close; ---echo # connection con1 -connection con1; ---echo # Reaping 'drop table t1'... ---reap ---echo # --> connection default -connection default; -commit; ---echo # ---echo # Show that rollback to the savepoint taken in the beginning ---echo # of the transaction works properly (no valgrind warnins, etc), ---echo # even though it's done after the HANDLER mdl lock that was there ---echo # at the beginning is released and added again. ---echo # -create table t1 (a int, key a(a)); -insert into t1 (a) values (1), (2), (3), (4), (5); -create table t2 like t1; -create table t3 like t1; -insert into t3 (a) select a from t1; -begin; -handler t1 open; -savepoint sv; -handler t1 read a first; -select * from t2; -handler t1 close; -handler t3 open; -handler t3 read a first; -rollback to savepoint sv; ---echo # --> connection con1 -connection con1; -drop table t1, t2; ---echo # Sending: ---send drop table t3 ---echo # Let DROP TABLE statement sync in. ---echo # --> connection con2 -connection con2; ---echo # Waiting for 'drop table t3' to get blocked... -let $wait_condition=select count(*)=1 from information_schema.processlist - where state='Waiting for table metadata lock' and - info='drop table t3'; ---source include/wait_condition.inc ---echo # Demonstrate that ROLLBACK TO SAVEPOINT didn't release the handler ---echo # lock. ---echo # --> connection default -connection default; -handler t3 read a next; ---echo # Demonstrate that the drop will go through as soon as we close the ---echo # HANDLER -handler t3 close; ---echo # connection con1 -connection con1; ---echo # Reaping 'drop table t3'... ---reap ---echo # --> connection default -connection default; -commit; - ---echo # ---echo # If we have to wait on an exclusive locks while having ---echo # an open HANDLER, ER_LOCK_DEADLOCK is reported. ---echo # -create table t1 (a int, key a(a)); -create table t2 like t1; -handler t1 open; ---echo # --> connection con1 -connection con1; -lock table t1 write, t2 write; ---echo # --> connection default -connection default; -send drop table t2; ---echo # --> connection con2 -connection con2; ---echo # Waiting for 'drop table t2' to get blocked... -let $wait_condition=select count(*)=1 from information_schema.processlist - where state='Waiting for table metadata lock' and - info='drop table t2'; ---source include/wait_condition.inc ---echo # --> connection con1 -connection con1; ---error ER_LOCK_DEADLOCK -drop table t1; -unlock tables; ---echo # --> connection default -connection default; -reap; - ---echo # Demonstrate that there is no deadlock with FLUSH TABLE, ---echo # even though it is waiting for the other table to go away -create table t2 like t1; ---echo # Sending: ---send flush table t2 ---echo # --> connection con2 -connection con2; -drop table t1; ---echo # --> connection con1 -connection con1; -unlock tables; ---echo # --> connection default -connection default; ---echo # Reaping 'flush table t2'... ---reap -drop table t2; - ---echo # ---echo # Bug #46224 HANDLER statements within a transaction might ---echo # lead to deadlocks ---echo # -create table t1 (a int, key a(a)); -insert into t1 values (1), (2); - ---echo # --> connection default -connection default; -begin; -select * from t1; -handler t1 open; - ---echo # --> connection con1 -connection con1; ---echo # Sending: ---send lock tables t1 write - ---echo # --> connection con2 -connection con2; ---echo # Check that 'lock tables t1 write' waits until transaction which ---echo # has read from the table commits. -let $wait_condition= - select count(*) = 1 from information_schema.processlist - where state = "Waiting for table metadata lock" and - info = "lock tables t1 write"; ---source include/wait_condition.inc - ---echo # --> connection default -connection default; ---echo # The below 'handler t1 read ...' should not be blocked as ---echo # 'lock tables t1 write' has not succeeded yet. -handler t1 read a next; - ---echo # Unblock 'lock tables t1 write'. -commit; - ---echo # --> connection con1 -connection con1; ---echo # Reap 'lock tables t1 write'. ---reap - ---echo # --> connection default -connection default; ---echo # Sending: ---send handler t1 read a next - ---echo # --> connection con1 -connection con1; ---echo # Waiting for 'handler t1 read a next' to get blocked... -let $wait_condition= - select count(*) = 1 from information_schema.processlist - where state = "Waiting for table level lock" and - info = "handler t1 read a next"; ---source include/wait_condition.inc - ---echo # The below 'drop table t1' should be able to proceed without ---echo # waiting as it will force HANDLER to be closed. -drop table t1; -unlock tables; - ---echo # --> connection default -connection default; ---echo # Reaping 'handler t1 read a next'... ---error ER_NO_SUCH_TABLE ---reap -handler t1 close; - ---echo # --> connection con1 -connection con1; -disconnect con1; ---source include/wait_until_disconnected.inc ---echo # --> connection con2 -connection con2; -disconnect con2; ---source include/wait_until_disconnected.inc ---echo # --> connection con3 -connection con3; -disconnect con3; ---source include/wait_until_disconnected.inc -connection default; - ---echo # ---echo # A temporary table test. ---echo # Check that we don't loose positions of HANDLER opened ---echo # against a temporary table. ---echo # -create table t1 (a int, b int, key a (a)); -insert into t1 (a) values (1), (2), (3), (4), (5); -create temporary table t2 (a int, b int, key a (a)); -insert into t2 (a) select a from t1; -handler t1 open; -handler t1 read a next; -handler t2 open; -handler t2 read a next; -flush table t1; -handler t2 read a next; ---echo # Sic: the position is lost -handler t1 read a next; -select * from t1; ---echo # Sic: the position is not lost -handler t2 read a next; ---error ER_CANT_REOPEN_TABLE -select * from t2; -handler t2 read a next; -drop table t1; -drop temporary table t2; - ---echo # ---echo # A test for lock_table_names()/unlock_table_names() function. ---echo # It should work properly in presence of open HANDLER. ---echo # -create table t1 (a int, b int, key a (a)); -create table t2 like t1; -create table t3 like t1; -create table t4 like t1; -handler t1 open; -handler t2 open; -rename table t4 to t5, t3 to t4, t5 to t3; -handler t1 read first; -handler t2 read first; -drop table t1, t2, t3, t4; - ---echo # ---echo # A test for FLUSH TABLES WITH READ LOCK and HANDLER statements. ---echo # -set autocommit=0; -create table t1 (a int, b int, key a (a)); -insert into t1 (a, b) values (1, 1), (2, 1), (3, 2), (4, 2), (5, 5); -create table t2 like t1; -insert into t2 (a, b) select a, b from t1; -create table t3 like t1; -insert into t3 (a, b) select a, b from t1; -commit; -flush tables with read lock; -handler t1 open; -lock table t1 read; ---error ER_LOCK_OR_ACTIVE_TRANSACTION -handler t1 read next; ---echo # This implicitly leaves LOCK TABLES but doesn't drop the GLR ---error ER_NO_SUCH_TABLE -lock table not_exists_write read; ---echo # We still have the read lock. ---error ER_CANT_UPDATE_WITH_READLOCK -drop table t1; -handler t1 open; -select a from t2; -handler t1 read next; -flush tables with read lock; -handler t2 open; -flush tables with read lock; -handler t1 read next; -select a from t3; -handler t2 read next; -handler t1 close; -rollback; -handler t2 close; ---error ER_CANT_UPDATE_WITH_READLOCK -drop table t1; -commit; -flush tables; ---error ER_CANT_UPDATE_WITH_READLOCK -drop table t1; -unlock tables; -drop table t1; -set autocommit=default; -drop table t2, t3; - ---echo # ---echo # HANDLER statement and operation-type aware metadata locks. ---echo # Check that when we clone a ticket for HANDLER we downrade ---echo # the lock. ---echo # ---echo # Establish an auxiliary connection con1. -connect (con1,localhost,root,,); ---echo # -> connection default -connection default; -create table t1 (a int, b int, key a (a)); -insert into t1 (a, b) values (1, 1), (2, 1), (3, 2), (4, 2), (5, 5); -begin; -insert into t1 (a, b) values (6, 6); -handler t1 open; -handler t1 read a last; -insert into t1 (a, b) values (7, 7); -handler t1 read a last; -commit; ---echo # -> connection con1 -connection con1; ---echo # Demonstrate that the HANDLER doesn't hold MDL_SHARED_WRITE. -lock table t1 write; -unlock tables; ---echo # -> connection default -connection default; -handler t1 read a prev; -handler t1 close; ---echo # Cleanup. -drop table t1; ---echo # -> connection con1 -connection con1; -disconnect con1; ---source include/wait_until_disconnected.inc ---echo # -> connection default -connection default; - ---echo # ---echo # A test for Bug#50555 "handler commands crash server in ---echo # my_hash_first()". ---echo # ---error ER_UNKNOWN_TABLE -handler no_such_table read no_such_index first; ---error ER_UNKNOWN_TABLE -handler no_such_table close; - - ---echo # ---echo # Bug#50907 Assertion `hash_tables->table->next == __null' on ---echo # HANDLER OPEN ---echo # - ---disable_warnings -DROP TABLE IF EXISTS t1, t2; ---enable_warnings - -CREATE TEMPORARY TABLE t1 (i INT); -CREATE TEMPORARY TABLE t2 (i INT); - -# This used to trigger the assert -HANDLER t2 OPEN; - -# This also used to trigger the assert -HANDLER t2 READ FIRST; - -HANDLER t2 CLOSE; -DROP TABLE t1, t2; - - ---echo # ---echo # Bug#50912 Assertion `ticket->m_type >= mdl_request->type' ---echo # failed on HANDLER + I_S ---echo # - ---disable_warnings -DROP TABLE IF EXISTS t1; ---enable_warnings - -CREATE TABLE t1 (id INT); -HANDLER t1 OPEN; - -# This used to trigger the assert. -SELECT table_name, table_comment FROM information_schema.tables - WHERE table_schema= 'test' AND table_name= 't1'; - -HANDLER t1 CLOSE; -DROP TABLE t1; - - ---echo # ---echo # Test for bug #50908 "Assertion `handler_tables_hash.records == 0' ---echo # failed in enter_locked_tables_mode". ---echo # ---disable_warnings -drop tables if exists t1, t2; -drop function if exists f1; ---enable_warnings -create table t1 (i int); -insert into t1 values (1), (2); -create table t2 (j int); -insert into t2 values (1); -create function f1() returns int return (select count(*) from t2); ---echo # Check that open HANDLER survives statement executed in ---echo # prelocked mode. -handler t1 open; -handler t1 read next; ---echo # The below statement were aborted due to an assertion failure. -select f1() from t2; -handler t1 read next; -handler t1 close; ---echo # Check that the same happens under GLOBAL READ LOCK. -flush tables with read lock; -handler t1 open; -handler t1 read next; -select f1() from t2; -handler t1 read next; -unlock tables; -handler t1 close; ---echo # Now, check that the same happens if LOCK TABLES is executed. -handler t1 open; -handler t1 read next; -lock table t2 read; -select * from t2; -unlock tables; -handler t1 read next; -handler t1 close; ---echo # Finally, check scenario with GRL and LOCK TABLES. -flush tables with read lock; -handler t1 open; -handler t1 read next; -lock table t2 read; -select * from t2; ---echo # This unlocks both tables and GRL. -unlock tables; -handler t1 read next; -handler t1 close; ---echo # Clean-up. -drop function f1; -drop tables t1, t2; - - ---echo # ---echo # Test for bug #51136 "Crash in pthread_rwlock_rdlock on TEMPORARY + ---echo # HANDLER + LOCK + SP". ---echo # Also see additional coverage for this bug in flush.test. ---echo # ---disable_warnings -drop tables if exists t1, t2; ---enable_warnings -create table t1 (i int); -create temporary table t2 (j int); -handler t1 open; -lock table t2 read; ---echo # This commit should not release any MDL locks. -commit; -unlock tables; ---echo # The below statement crashed before the bug fix as it ---echo # has attempted to release metadata lock which was ---echo # already released by commit. -handler t1 close; -drop tables t1, t2; - - ---echo # ---echo # Bug#51355 handler stmt cause assertion in ---echo # bool MDL_context::try_acquire_lock(MDL_request*) ---echo # - ---disable_warnings -DROP TABLE IF EXISTS t1; ---enable_warnings - -connect(con51355, localhost, root); - ---echo # Connection default -connection default; -CREATE TABLE t1(id INT, KEY id(id)); -HANDLER t1 OPEN; - ---echo # Connection con51355 -connection con51355; ---echo # Sending: ---send DROP TABLE t1 - ---echo # Connection default -connection default; ---echo # This I_S query will cause the handler table to be closed and ---echo # the metadata lock to be released. This will allow DROP TABLE ---echo # to proceed. Waiting for the table to be removed. -let $wait_condition= - SELECT COUNT(*) = 0 FROM information_schema.tables WHERE table_name = "t1"; ---source include/wait_condition.inc - ---echo # Connection con51355 -connection con51355; ---echo # Reaping: DROP TABLE t1 ---reap - ---echo # Connection default -connection default; ---error ER_NO_SUCH_TABLE -HANDLER t1 READ id NEXT; -# This caused an assertion ---error ER_NO_SUCH_TABLE -HANDLER t1 READ id NEXT; - -HANDLER t1 CLOSE; ---echo # Connection con51355 -connection con51355; -disconnect con51355; ---source include/wait_until_disconnected.inc ---echo # Connection default -connection default; - - ---echo # ---echo # Bug#54401 assert in Diagnostics_area::set_eof_status , HANDLER ---echo # - ---disable_warnings -DROP TABLE IF EXISTS t1, t2; -DROP FUNCTION IF EXISTS f1; ---enable_warnings - -delimiter |; -CREATE FUNCTION f1() RETURNS INTEGER -BEGIN - SELECT 1 FROM t2 INTO @a; - RETURN 1; -END| -delimiter ;| - -# Get f1() parsed and cached ---error ER_NO_SUCH_TABLE -SELECT f1(); - -CREATE TABLE t1(a INT); -INSERT INTO t1 VALUES (1); -HANDLER t1 OPEN; -# This used to cause the assert ---error ER_NOT_SUPPORTED_YET -HANDLER t1 READ FIRST WHERE f1() = 1; -HANDLER t1 CLOSE; - -DROP FUNCTION f1; -DROP TABLE t1; - - ---echo # ---echo # Bug#54920 Stored functions are allowed in HANDLER statements, ---echo # but broken. ---echo # - ---disable_warnings -DROP TABLE IF EXISTS t1; -DROP FUNCTION IF EXISTS f1; ---enable_warnings - -CREATE TABLE t1 (a INT); -INSERT INTO t1 VALUES (1), (2); -CREATE FUNCTION f1() RETURNS INT RETURN 1; -HANDLER t1 OPEN; - ---error ER_NOT_SUPPORTED_YET -HANDLER t1 READ FIRST WHERE f1() = 1; - -HANDLER t1 CLOSE; -DROP FUNCTION f1; -DROP TABLE t1; diff --git a/mysql-test/include/have_binlog_checksum_off.inc b/mysql-test/include/have_binlog_checksum_off.inc new file mode 100644 index 00000000000..c7c444c8785 --- /dev/null +++ b/mysql-test/include/have_binlog_checksum_off.inc @@ -0,0 +1,4 @@ +if (`select variable_value not like 'NONE' from information_schema.GLOBAL_VARIABLES + where variable_name='binlog_checksum'`){ + skip Can not run the test when server activated checksumming; +} diff --git a/mysql-test/include/have_ndb.inc b/mysql-test/include/have_ndb.inc index cfc5b5d0ff8..1266f80c8cd 100644 --- a/mysql-test/include/have_ndb.inc +++ b/mysql-test/include/have_ndb.inc @@ -1,10 +1,2 @@ # Check that server is compiled and started with support for NDB -#disable_query_log; -#--require r/true.require -#select (support = 'YES' or support = 'DEFAULT') as `TRUE` from information_schema.engines where engine = 'ndbcluster'; -#--source include/ndb_not_readonly.inc -#enable_query_log; -# always make sure we have both mysql servers started ok before test starts -# there are some initial startup bugs that are avoided by doing this, avoiding sporadic -# failures in mysql-test-run --source include/have_multi_ndb.inc diff --git a/mysql-test/include/have_not_innodb_plugin.inc b/mysql-test/include/have_not_innodb_plugin.inc index aaefbaf661c..e40fd811021 100644 --- a/mysql-test/include/have_not_innodb_plugin.inc +++ b/mysql-test/include/have_not_innodb_plugin.inc @@ -1,4 +1,4 @@ disable_query_log; --require r/not_true.require -select (PLUGIN_LIBRARY LIKE 'ha_innodb_plugin%') as `TRUE` from information_schema.plugins where PLUGIN_NAME='InnoDB'; +select (PLUGIN_LIBRARY LIKE 'ha_innodb_plugin%' OR PLUGIN_DESCRIPTION LIKE '%xtradb%') as `TRUE` from information_schema.plugins where PLUGIN_NAME='InnoDB'; enable_query_log; diff --git a/mysql-test/include/have_xtradb.opt b/mysql-test/include/have_xtradb.opt new file mode 100644 index 00000000000..4fb96229a7b --- /dev/null +++ b/mysql-test/include/have_xtradb.opt @@ -0,0 +1,2 @@ +--loose-innodb +--plugin-load=$HA_XTRADB_SO diff --git a/mysql-test/include/icp_tests.inc b/mysql-test/include/icp_tests.inc new file mode 100644 index 00000000000..4af8bf9c452 --- /dev/null +++ b/mysql-test/include/icp_tests.inc @@ -0,0 +1,256 @@ +--echo # +--echo # Bug#36981 - "innodb crash when selecting for update" +--echo # + +# +# Test 1: Test based on the reproduction test case for this bug. +# This query resulted in a crash in InnoDB due to +# InnoDB changing from using the index which the push condition +# where for to use the clustered index due to "SELECT ... FOR UPDATE". +# + +CREATE TABLE t1 ( + c1 CHAR(1), + c2 CHAR(10), + KEY (c1) +); + +INSERT INTO t1 VALUES ('3', null); + +SELECT * FROM t1 WHERE c1='3' FOR UPDATE; + +DROP TABLE t1; + +# +# Test 2: Extended test case to test that the correct rows are returned. +# This test is for ensuring that if InnoDB refuses to accept +# the pushed index condition it is still evaluated. +# + +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); + +CREATE TABLE t2 (a INT); +INSERT INTO t2 SELECT A.a + 10*(B.a + 10*C.a) FROM t1 A, t1 B, t1 C; + +CREATE TABLE t3 ( + c1 CHAR(10) NOT NULL, + c2 CHAR(10) NOT NULL, + c3 CHAR(200) NOT NULL, + KEY (c1) +); + +INSERT INTO t3 + SELECT CONCAT('c-',1000+t2.a,'=w'), CONCAT('c-',1000+ t2.a,'=w'), 'filler' + FROM t2; + +INSERT INTO t3 + SELECT CONCAT('c-',1000+t2.a,'=w'), CONCAT('c-',2000+t2.a,'=w'), 'filler-1' + FROM t2; + +INSERT INTO t3 + SELECT CONCAT('c-',1000+t2.a,'=w'), CONCAT('c-',3000+t2.a,'=w'), 'filler-2' + FROM t2; + +--sorted_result +SELECT c1,c3 FROM t3 WHERE c1 >= 'c-1994=w' and c1 != 'c-1996=w' FOR UPDATE; + +DROP TABLE t1,t2,t3; + +--echo # +--echo # Bug#42580 - Innodb's ORDER BY ..LIMIT returns no rows for +--echo # null-safe operator <=> NULL +--echo # + +CREATE TABLE t1( + c1 DATE NOT NULL, + c2 DATE NULL, + c3 DATETIME, + c4 TIMESTAMP, + PRIMARY KEY(c1), + UNIQUE(c2) +); + +--echo +INSERT INTO t1 VALUES('0000-00-00', '0000-00-00', '2008-01-04', '2008-01-05'); +INSERT INTO t1 VALUES('2007-05-25', '2007-05-25', '2007-05-26', '2007-05-26'); +INSERT INTO t1 VALUES('2008-01-01', NULL , '2008-01-02', '2008-01-03'); +INSERT INTO t1 VALUES('2008-01-17', NULL , NULL , '2009-01-29'); +INSERT INTO t1 VALUES('2009-01-29', '2009-01-29', '2009-01-29', '2009-01-29'); + +--echo +SELECT * FROM t1 WHERE c2 <=> NULL ORDER BY c1,c2; +--echo +SELECT * FROM t1 WHERE c2 <=> NULL ORDER BY c1,c2 LIMIT 2; + +--echo +DROP TABLE t1; + +--echo # +--echo # Bug#40992 - InnoDB: Crash when engine_condition_pushdown is on +--echo # + +CREATE TABLE t ( + dummy INT PRIMARY KEY, + a INT UNIQUE, + b INT +); + +INSERT INTO t VALUES (1,1,1),(3,3,3),(5,5,5); + +SELECT * FROM t WHERE a > 2 FOR UPDATE; + +DROP TABLE t; + +--echo # +--echo # Bug#35080 - Innodb crash at mem_block_get_len line 72 +--echo # + +CREATE TABLE t1 ( + t1_autoinc INT(11) NOT NULL AUTO_INCREMENT, + uuid VARCHAR(36) DEFAULT NULL, + PRIMARY KEY (t1_autoinc), + KEY k (uuid) +); + +CREATE TABLE t2 ( + t2_autoinc INT(11) NOT NULL AUTO_INCREMENT, + uuid VARCHAR(36) DEFAULT NULL, + date DATETIME DEFAULT NULL, + PRIMARY KEY (t2_autoinc), + KEY k (uuid) +); + +CREATE VIEW v1 AS + SELECT t1_autoinc, uuid + FROM t1 + WHERE (ISNULL(uuid) OR (uuid like '%-%')); + +CREATE VIEW v2 AS + SELECT t2_autoinc, uuid, date + FROM t2 + WHERE (ISNULL(uuid) OR (LENGTH(uuid) = 36)); + +CREATE PROCEDURE delete_multi (IN uuid CHAR(36)) + DELETE v1, v2 FROM v1 INNER JOIN v2 + ON v1.uuid = v2.uuid + WHERE v1.uuid = @uuid; + +SET @uuid = UUID(); + +INSERT INTO v1 (uuid) VALUES (@uuid); +INSERT INTO v2 (uuid, date) VALUES (@uuid, '2009-09-09'); + +CALL delete_multi(@uuid); + +DROP procedure delete_multi; +DROP table t1,t2; +DROP view v1,v2; + +--echo # +--echo # Bug#41996 - multi-table delete crashes server (InnoDB table) +--echo # + +CREATE TABLE t1 ( + b BIGINT, + i INT, + KEY (b) +); + +INSERT INTO t1 VALUES (2, 2); + +DELETE t1 FROM t1 a, t1 WHERE a.i=t1.b; + +DROP TABLE t1; + +--echo # +--echo # Bug#43448 - Server crashes on multi table delete with Innodb +--echo # + +CREATE TABLE t1 ( + id1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, + t CHAR(12) +); + +CREATE TABLE t2 ( + id2 INT NOT NULL, + t CHAR(12) +); + +CREATE TABLE t3( + id3 INT NOT NULL, + t CHAR(12), + INDEX(id3) +); + +delimiter |; + +CREATE PROCEDURE insert_data () +BEGIN + DECLARE i1 INT DEFAULT 20; + DECLARE i2 INT; + DECLARE i3 INT; + + WHILE (i1 > 0) DO + INSERT INTO t1(t) VALUES (i1); + SET i2 = 2; + WHILE (i2 > 0) DO + INSERT INTO t2(id2, t) VALUES (i1, i2); + SET i3 = 2; + WHILE (i3 > 0) DO + INSERT INTO t3(id3, t) VALUES (i1, i2); + SET i3 = i3 -1; + END WHILE; + SET i2 = i2 -1; + END WHILE; + SET i1 = i1 - 1; + END WHILE; +END | + +delimiter ;| + +CALL insert_data(); + +SELECT COUNT(*) FROM t1 WHERE id1 > 10; +SELECT COUNT(*) FROM t2 WHERE id2 > 10; +SELECT COUNT(*) FROM t3 WHERE id3 > 10; + +DELETE t1, t2, t3 +FROM t1, t2, t3 +WHERE t1.id1 = t2.id2 AND t2.id2 = t3.id3 AND t1.id1 > 3; + +SELECT COUNT(*) FROM t1; +SELECT COUNT(*) FROM t2; +SELECT COUNT(*) FROM t3; + +DROP PROCEDURE insert_data; +DROP TABLE t1, t2, t3; + +--echo # +--echo # BUG#778434 Wrong result with in_to_exists=on in maria-5.3-mwl89 +--echo # +CREATE TABLE t1 ( f11 int) ; +INSERT IGNORE INTO t1 VALUES (0); + +CREATE TABLE t2 ( f10 int) ; +INSERT IGNORE INTO t2 VALUES (0); + +CREATE TABLE t3 ( f1 int NOT NULL , f10 int, PRIMARY KEY (f1)) ; +INSERT IGNORE INTO t3 VALUES (6,0),(10,0); + +CREATE TABLE t4 ( f11 int) ; +INSERT IGNORE INTO t4 VALUES +(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(NULL), +(0),(0),(0),(0),(0),(0),(0),(0),(0),(0); + +set @tmp_778434=@@optimizer_switch; +SET optimizer_switch='materialization=off,in_to_exists=on,subquery_cache=off,semijoin=off'; + +SELECT * FROM t1 INNER JOIN t2 ON t2.f10 = t1.f11 +WHERE (6, 234) IN ( + SELECT t3.f1, t3.f1 + FROM t3 JOIN t4 ON t4.f11 = t3.f10 +); + +DROP TABLE t1,t2,t3,t4; +set optimizer_switch= @tmp_778434; diff --git a/mysql-test/include/index_merge1.inc b/mysql-test/include/index_merge1.inc index ef116c5addc..86298f86906 100644 --- a/mysql-test/include/index_merge1.inc +++ b/mysql-test/include/index_merge1.inc @@ -194,7 +194,7 @@ alter table t2 add index i321(key3, key2, key1); 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; +explain select key3 from t2 where key1 < 500 or key2 < 500; # index_merge vs 'all', index_merge is better. explain select key7 from t2 where key1 <100 or key2 < 100; @@ -334,12 +334,12 @@ update t0 set key2=1, key3=1, key4=1, key5=1,key6=1,key7=1 where key7 < 500; --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 + from t0 as A straight_join 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 + from t0 as A straight_join 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); @@ -539,7 +539,7 @@ INSERT INTO t1 SELECT * FROM t1; INSERT INTO t1 SELECT * FROM t1; INSERT INTO t1 SELECT * FROM t1; INSERT INTO t1 SELECT * FROM t1; -SET SESSION sort_buffer_size=1; +SET SESSION sort_buffer_size=1024*8; EXPLAIN SELECT * FROM t1 FORCE INDEX(a,b) WHERE a LIKE 'a%' OR b LIKE 'b%' ORDER BY a,b; diff --git a/mysql-test/include/index_merge2.inc b/mysql-test/include/index_merge2.inc index 23c8c6466c7..1d6b82e1787 100644 --- a/mysql-test/include/index_merge2.inc +++ b/mysql-test/include/index_merge2.inc @@ -122,20 +122,16 @@ insert into t1 (key1a, key1b, key2a, key2b, key3a, key3b) analyze table t1; select count(*) from t1; -if ($index_merge_random_rows_in_EXPLAIN) -{ - --replace_column 9 # -} +--replace_column 9 REF +--replace_result i2,i1 i1,i2 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; -if ($index_merge_random_rows_in_EXPLAIN) -{ - --replace_column 9 # -} +--replace_column 9 REF +--replace_result i3,i1 i1,i3 explain select count(*) from t1 where key1a = 2 and key1b is null and key3a = 2 and key3b is null; diff --git a/mysql-test/include/long_test.inc b/mysql-test/include/long_test.inc new file mode 100644 index 00000000000..d9a3b338229 --- /dev/null +++ b/mysql-test/include/long_test.inc @@ -0,0 +1,4 @@ +# We use this --source include to mark a test as taking long to run. +# We can use this to schedule such test early (to not be left with +# only one or two long tests running, and rests of works idle), or to +# run a quick test skipping long-running test cases. diff --git a/mysql-test/include/maria_make_snapshot_for_comparison.inc b/mysql-test/include/maria_make_snapshot_for_comparison.inc index cb756f60527..0c71bd10408 100644 --- a/mysql-test/include/maria_make_snapshot_for_comparison.inc +++ b/mysql-test/include/maria_make_snapshot_for_comparison.inc @@ -8,6 +8,7 @@ # cover tables mysqltest.$mms_tname1,...$mms_tnameN connection admin; +--source include/wait_until_connected_again.inc let $mms_table_to_use=$mms_tables; let $mms_purpose=comparison; diff --git a/mysql-test/include/maria_make_snapshot_for_feeding_recovery.inc b/mysql-test/include/maria_make_snapshot_for_feeding_recovery.inc index dc706174b7c..fded4cb83b4 100644 --- a/mysql-test/include/maria_make_snapshot_for_feeding_recovery.inc +++ b/mysql-test/include/maria_make_snapshot_for_feeding_recovery.inc @@ -11,6 +11,7 @@ connection admin; +--source include/wait_until_connected_again.inc let $mms_table_to_use=$mms_tables; let $mms_purpose=feeding_recovery; diff --git a/mysql-test/include/mix1.inc b/mysql-test/include/mix1.inc index 4614535c188..588de7b9f6a 100644 --- a/mysql-test/include/mix1.inc +++ b/mysql-test/include/mix1.inc @@ -1377,7 +1377,17 @@ INSERT INTO t1 VALUES (1,'init'); DELIMITER |; CREATE PROCEDURE p1() BEGIN - UPDATE t1 SET b = CONCAT(b, '+con2') WHERE a = 1; + # retry the UPDATE in case it times out the lock before con1 has time + # to COMMIT. + DECLARE do_retry INT DEFAULT 0; + DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET do_retry = 1; + retry_loop:LOOP + UPDATE t1 SET b = CONCAT(b, '+con2') WHERE a = 1; + IF do_retry = 0 THEN + LEAVE retry_loop; + END IF; + SET do_retry = 0; + END LOOP; INSERT INTO t2 VALUES (); END| DELIMITER ;| diff --git a/mysql-test/include/mrr_tests.inc b/mysql-test/include/mrr_tests.inc index 21c419aa1a0..ad7dff61477 100644 --- a/mysql-test/include/mrr_tests.inc +++ b/mysql-test/include/mrr_tests.inc @@ -71,6 +71,9 @@ select b,filler from t3 where (b>='c-1011=w' and b<= 'c-1018=w') or # # Now try different keypart types and special values # +--disable_warnings +drop table if exists t4; +--enable_warnings create table t4 (a varchar(10), b int, c char(10), filler char(200), key idx1 (a, b, c)); diff --git a/mysql-test/include/mtr_check.sql b/mysql-test/include/mtr_check.sql index a5654d3596c..fcd5eebc269 100644 --- a/mysql-test/include/mtr_check.sql +++ b/mysql-test/include/mtr_check.sql @@ -13,7 +13,9 @@ BEGIN -- that are supposed to change SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE variable_name != 'timestamp' - AND variable_name != 'INNODB_IBUF_MAX_SIZE' + AND variable_name not like "Last_IO_Err*" + AND variable_name != 'INNODB_IBUF_MAX_SIZE' AND + variable_name != 'INNODB_FILE_FORMAT_CHECK' ORDER BY variable_name; -- Dump all databases, there should be none diff --git a/mysql-test/include/mtr_warnings.sql b/mysql-test/include/mtr_warnings.sql index 7446504424b..57cd04d81a1 100644 --- a/mysql-test/include/mtr_warnings.sql +++ b/mysql-test/include/mtr_warnings.sql @@ -125,7 +125,6 @@ INSERT INTO global_suppressions VALUES ("Slave: The incident LOST_EVENTS occured on the master"), ("Slave: Unknown error.* 1105"), ("Slave: Can't drop database.* database doesn't exist"), - ("Sort aborted"), ("Time-out in NDB"), ("Warning:\s+One can only use the --user.*root"), ("Warning:\s+Table:.* on (delete|rename)"), @@ -200,6 +199,17 @@ INSERT INTO global_suppressions VALUES */ ("Found lock of type 6 that is write and read locked"), + /* + Transient network failures that cause warnings on reconnect. + BUG#47743 and BUG#47983. + */ + ("Slave I/O: Get master SERVER_ID failed with error:.*"), + ("Slave I/O: Get master clock failed with error:.*"), + ("Slave I/O: Get master COLLATION_SERVER failed with error:.*"), + ("Slave I/O: Get master TIME_ZONE failed with error:.*"), + ("Slave I/O: The slave I/O thread stops because a fatal error is encountered when it tried to SET @master_binlog_checksum on master.*"), + ("Slave I/O: Get master BINLOG_CHECKSUM failed with error.*"), + ("Slave I/O: Notifying master by SET @master_binlog_checksum= @@global.binlog_checksum failed with error.*"), ("THE_LAST_SUPPRESSION")|| diff --git a/mysql-test/include/mysqld--help.inc b/mysql-test/include/mysqld--help.inc index 91fe9bf00e3..3b8fdd482fc 100644 --- a/mysql-test/include/mysqld--help.inc +++ b/mysql-test/include/mysqld--help.inc @@ -17,7 +17,7 @@ perl; # Variables which we don't want to display in the result file since # their paths may vary: @skipvars=qw/basedir open-files-limit general-log-file log plugin-dir - log-slow-queries pid-file slow-query-log-file + log-slow-queries pid-file slow-query-log-file log-basename datadir slave-load-tmpdir tmpdir socket/; # Plugins which may or may not be there: diff --git a/mysql-test/include/not_debug.inc b/mysql-test/include/not_debug.inc new file mode 100644 index 00000000000..5ea01fe2935 --- /dev/null +++ b/mysql-test/include/not_debug.inc @@ -0,0 +1,6 @@ +let $is_debug = `select version() like '%debug%'`; +if ($is_debug) +{ + skip Does not run in with debug binaries; +} + diff --git a/mysql-test/include/ps_conv.inc b/mysql-test/include/ps_conv.inc index 9ac943d5bdd..2e42542d19a 100644 --- a/mysql-test/include/ps_conv.inc +++ b/mysql-test/include/ps_conv.inc @@ -106,7 +106,7 @@ drop table t5 ; # c1 tinyint, c2 smallint, c3 mediumint, c4 int, # c5 integer, c6 bigint, c7 float, c8 double, # c9 double precision, c10 real, c11 decimal(7, 4), c12 numeric(8, 4), -# c13 date, c14 datetime, c15 timestamp(14), c16 time, +# c13 date, c14 datetime, c15 timestamp, c16 time, # c17 year, c18 tinyint, c19 bool, c20 char, # c21 char(10), c22 varchar(30), c23 tinyblob, c24 tinytext, # c25 blob, c26 text, c27 mediumblob, c28 mediumtext, @@ -672,7 +672,6 @@ select '-- insert into string columns --' as test_sequence ; --enable_query_log ######## INSERT into .. string columns values(CHAR(n),LONGTEXT) ######## ---disable_query_log insert into t9 ( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 ) values @@ -860,8 +859,6 @@ values execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00 ; ---enable_query_log - ######## SELECT of all inserted records ######## select c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 from t9 where c1 >= 20 @@ -980,14 +977,13 @@ delete from t9 ; ######################### test of date/time columns ######################## # # -# c13 date, c14 datetime, c15 timestamp(14), c16 time, c17 year # +# c13 date, c14 datetime, c15 timestamp, c16 time, c17 year # # # ############################################################################ --disable_query_log select '-- insert into date/time columns --' as test_sequence ; --enable_query_log ######## INSERT into .. date/time columns values(VARCHAR(19),LONGTEXT) ######## ---disable_query_log set @arg00= '1991-01-01 01:01:01' ; insert into t9 ( c1, c13, c14, c15, c16, c17 ) @@ -1143,12 +1139,9 @@ values ( 83, ?, ?, '1991-01-01 01:01:01', ?, ? )" ; execute stmt2 using @arg00, @arg00, @arg00, @arg00 ; ---enable_query_log - ######## SELECT of all inserted records ######## select c1, c13, c14, c15, c16, c17 from t9 order by c1 ; - --disable_query_log select '-- select .. where date/time column = .. --' as test_sequence ; --enable_query_log @@ -1156,19 +1149,19 @@ select '-- select .. where date/time column = .. --' as test_sequence ; set @arg00= '1991-01-01 01:01:01' ; select 'true' as found from t9 where c1= 20 and c13= CAST('1991-01-01 01:01:01' AS DATE) and c14= '1991-01-01 01:01:01' and - c15= '1991-01-01 01:01:01' and c16= '1991-01-01 01:01:01' and + c15= '1991-01-01 01:01:01' and c17= '1991-01-01 01:01:01' ; select 'true' as found from t9 -where c1= 20 and c13= CAST(@arg00 AS DATE) and c14= @arg00 and c15= @arg00 and c16= @arg00 +where c1= 20 and c13= CAST(@arg00 AS DATE) and c14= @arg00 and c15= @arg00 and c17= @arg00 ; prepare stmt1 from "select 'true' as found from t9 where c1= 20 and c13= CAST('1991-01-01 01:01:01' AS DATE) and c14= '1991-01-01 01:01:01' and - c15= '1991-01-01 01:01:01' and c16= '1991-01-01 01:01:01' and + c15= '1991-01-01 01:01:01' and c17= '1991-01-01 01:01:01'" ; execute stmt1 ; prepare stmt1 from "select 'true' as found from t9 -where c1= 20 and c13= CAST(? AS DATE) and c14= ? and c15= ? and c16= ? and c17= ?" ; -execute stmt1 using @arg00, @arg00, @arg00, @arg00, @arg00 ; +where c1= 20 and c13= CAST(? AS DATE) and c14= ? and c15= ? and c17= ?" ; +execute stmt1 using @arg00, @arg00, @arg00, @arg00 ; ######## SELECT .. WHERE column(date/time/..)=value(DATETIME/LONGBLOB) ######## @@ -1177,21 +1170,39 @@ select 'true' as found from t9 where c1= 20 and c13= CAST('1991-01-01 00:00:00' as datetime) and c14= CAST('1991-01-01 01:01:01' as datetime) and c15= CAST('1991-01-01 01:01:01' as datetime) and - c16= CAST('1991-01-01 01:01:01' as datetime) and c17= CAST('1991-01-01 01:01:01' as datetime) ; select 'true' as found from t9 -where c1= 20 and c13= CAST(@arg00 AS DATE) and c14= @arg00 and c15= @arg00 and c16= @arg00 +where c1= 20 and c13= CAST(@arg00 AS DATE) and c14= @arg00 and c15= @arg00 and c17= @arg00 ; prepare stmt1 from "select 'true' as found from t9 where c1= 20 and c13= CAST('1991-01-01 00:00:00' as datetime) and c14= CAST('1991-01-01 01:01:01' as datetime) and c15= CAST('1991-01-01 01:01:01' as datetime) and - c16= CAST('1991-01-01 01:01:01' as datetime) and c17= CAST('1991-01-01 01:01:01' as datetime)" ; execute stmt1 ; prepare stmt1 from "select 'true' as found from t9 -where c1= 20 and c13= CAST(? AS DATE) and c14= ? and c15= ? and c16= ? and c17= ?" ; -execute stmt1 using @arg00, @arg00, @arg00, @arg00, @arg00 ; +where c1= 20 and c13= CAST(? AS DATE) and c14= ? and c15= ? and c17= ?" ; +execute stmt1 using @arg00, @arg00, @arg00, @arg00 ; + + +######## SELECT .. WHERE column(date/time/..)=value(CHAR(n)/LONGTEXT) ######## +set @arg00= '01:01:01' ; +select 'true' as found from t9 where c1= 20 and c16= '01:01:01' ; +select 'true' as found from t9 where c1= 20 and c16= @arg00 ; +prepare stmt1 from "select 'true' as found from t9 where c1= 20 and c16= '01:01:01'" ; +execute stmt1 ; +prepare stmt1 from "select 'true' as found from t9 where c1= 20 and c16= ?" ; +execute stmt1 using @arg00 ; + + +######## SELECT .. WHERE column(date/time/..)=value(DATETIME/LONGBLOB) ######## +set @arg00= CAST('01:01:01' as time) ; +select 'true' as found from t9 where c1= 20 and c16= CAST('01:01:01' as time) ; +select 'true' as found from t9 where c1= 20 and c16= @arg00 ; +prepare stmt1 from "select 'true' as found from t9 where c1= 20 and c16= CAST('01:01:01' as time)" ; +execute stmt1 ; +prepare stmt1 from "select 'true' as found from t9 where c1= 20 and c16= ?" ; +execute stmt1 using @arg00 ; ######## SELECT .. WHERE column(year)=value(INT(10)/BIGINT) ######## diff --git a/mysql-test/include/ps_query.inc b/mysql-test/include/ps_query.inc index ae6027a0e07..8148935cbe1 100644 --- a/mysql-test/include/ps_query.inc +++ b/mysql-test/include/ps_query.inc @@ -597,7 +597,6 @@ execute stmt1 using @arg00, @arg01, @arg02, @arg03, @arg04, @arg05, @arg06, --enable_result_log drop table t2 ; - ##### test case derived from client_test.c: test_bug4079() --error 1242 select 1 < (select a from t1) ; diff --git a/mysql-test/include/restart_slave_sql.inc b/mysql-test/include/restart_slave_sql.inc index ee6c6d7ced6..55e87f4b57f 100644 --- a/mysql-test/include/restart_slave_sql.inc +++ b/mysql-test/include/restart_slave_sql.inc @@ -40,4 +40,4 @@ source include/wait_for_slave_sql_to_start.inc; --let $include_filename= restart_slave.inc ---source include/end_include_file.inc
\ No newline at end of file +--source include/end_include_file.inc diff --git a/mysql-test/include/rpl_connection_master.inc b/mysql-test/include/rpl_connection_master.inc index e54e34071c8..fa09cc8a610 100644 --- a/mysql-test/include/rpl_connection_master.inc +++ b/mysql-test/include/rpl_connection_master.inc @@ -1,2 +1,2 @@ let $rpl_connection_name= master; -source include/rpl_connection.inc;
\ No newline at end of file +source include/rpl_connection.inc; diff --git a/mysql-test/include/rpl_connection_slave.inc b/mysql-test/include/rpl_connection_slave.inc index ef3876394d6..8dcfb3b611b 100644 --- a/mysql-test/include/rpl_connection_slave.inc +++ b/mysql-test/include/rpl_connection_slave.inc @@ -1,2 +1,2 @@ let $rpl_connection_name= slave; -source include/rpl_connection.inc;
\ No newline at end of file +source include/rpl_connection.inc; diff --git a/mysql-test/include/rpl_connection_slave1.inc b/mysql-test/include/rpl_connection_slave1.inc index 8aee6defbdd..a408d14596b 100644 --- a/mysql-test/include/rpl_connection_slave1.inc +++ b/mysql-test/include/rpl_connection_slave1.inc @@ -1,2 +1,2 @@ let $rpl_connection_name= slave1; -source include/rpl_connection.inc;
\ No newline at end of file +source include/rpl_connection.inc; diff --git a/mysql-test/include/show_binlog_events.inc b/mysql-test/include/show_binlog_events.inc index 05f99f4a64d..e5670c054fa 100644 --- a/mysql-test/include/show_binlog_events.inc +++ b/mysql-test/include/show_binlog_events.inc @@ -3,7 +3,7 @@ # # Useage: # let $binlog_file= master-bin.000002; -# let $binlog_start= 106; +# let $binlog_start= 240; # let $binlog_limit= 1, 3; # source include/show_binlog_events.inc; # diff --git a/mysql-test/include/show_binlog_events2.inc b/mysql-test/include/show_binlog_events2.inc index 0e1a889bacc..c32d12537fd 100644 --- a/mysql-test/include/show_binlog_events2.inc +++ b/mysql-test/include/show_binlog_events2.inc @@ -1,4 +1,4 @@ ---let $binlog_start=107 +--let $binlog_start=245 --replace_result $binlog_start <binlog_start> --replace_column 2 # 5 # --replace_regex /\/\* xid=.* \*\//\/* XID *\// /table_id: [0-9]+/table_id: #/ diff --git a/mysql-test/include/subselect_mat_cost.inc b/mysql-test/include/subselect_mat_cost.inc new file mode 100644 index 00000000000..04b116e9527 --- /dev/null +++ b/mysql-test/include/subselect_mat_cost.inc @@ -0,0 +1,152 @@ +-- echo +-- echo /* A. Subqueries in the SELECT clause. */ +explain +select a1, a1 in (select b1 from t2 where b1 > '0') from t1; +select a1, a1 in (select b1 from t2 where b1 > '0') from t1; +-- echo +explain +select a1, a2, (a1, a2) in (select b1, b2 from t2 where b1 > '0') from t1; +select a1, a2, (a1, a2) in (select b1, b2 from t2 where b1 > '0') from t1; +-- echo +explain +select a1, a2, (a1, a2) in (select b1, b2 from t2 where b1 > '0' and b1 < '9') from t1; +select a1, a2, (a1, a2) in (select b1, b2 from t2 where b1 > '0' and b1 < '9') from t1; + +-- echo +-- echo /* +-- echo B. "Natural" examples of subqueries without grouping that +-- echo cannot be flattened into semijoin. +-- echo */ + +explain +select a1 from t1 where a1 in (select b2 from t2) or a2 < '9'; +select a1 from t1 where a1 in (select b2 from t2) or a2 < '9'; +-- echo +explain +select a1, a2 from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0') or a2 < '9'; +select a1, a2 from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0') or a2 < '9'; +-- echo UNION subqueries are currently limited to only use IN-TO-EXISTS. +explain +select a2 from t1 where a2 in (select b2 from t2 UNION select b3 from t2 as t3); +select a2 from t1 where a2 in (select b2 from t2 UNION select b3 from t2 as t3); +-- echo +explain +select a1 from t1 where a1 = '1 - 02' and a1 in (select max(b1) from t2 where b2 = '2 - 02'); +select a1 from t1 where a1 = '1 - 02' and a1 in (select max(b1) from t2 where b2 = '2 - 02'); +-- echo +explain +select a1, a2 from t1 where (a1, a2) in (select b1, b2 from t2 order by b3); +select a1, a2 from t1 where (a1, a2) in (select b1, b2 from t2 order by b3); + +-- echo +-- echo /* C. Subqueries in the WHERE clause with GROUP BY. */ +explain +select * from t1 where a1 in (select b1 from t2 where b1 > '0' group by b1); +select * from t1 where a1 in (select b1 from t2 where b1 > '0' group by b1); +-- echo +explain +select * from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0' group by b1, b2); +select * from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0' group by b1, b2); +-- echo +explain +select * from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0' group by b1, b2 having b2 < '2 - 04'); +select * from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0' group by b1, b2 having b2 < '2 - 04'); +-- echo +explain +select * from t1 where (a1, a2, a3) in (select b1, b2, b3 from t2 group by b1, b2, b3); +select * from t1 where (a1, a2, a3) in (select b1, b2, b3 from t2 group by b1, b2, b3); +-- echo +explain +select * from t1 where (a1, a2, a3) in (select b1, b2, b3 from t2 where b3 = '3 - 02' group by b1, b2); +select * from t1 where (a1, a2, a3) in (select b1, b2, b3 from t2 where b3 = '3 - 02' group by b1, b2); +-- echo +explain +select * from t1 where (a1,a2,a3) in (select b1,b2,b3 from t2 where b1 = '1 - 01' group by b1,b2,b3); +select * from t1 where (a1,a2,a3) in (select b1,b2,b3 from t2 where b1 = '1 - 01' group by b1,b2,b3); + +-- echo +-- echo /* +-- echo D. Subqueries for which materialization is not possible, and the +-- echo optimizer reverts to in-to-exists. +-- echo */ +# The first two cases are rejected during the prepare phase by the procedure +# subquery_types_allow_materialization(). +explain +select left(a1,7), left(a2,7) from t1_1024 where a1 in (select b1 from t2_1024 where b1 > '0') or a2 < '9'; +select left(a1,7), left(a2,7) from t1_1024 where a1 in (select b1 from t2_1024 where b1 > '0') or a2 < '9'; +explain +select left(a1,7), left(a2,7) from t1_1024 where (a1,a2) in (select b1, b2 from t2_1024 where b1 > '0') or a2 < '9'; +select left(a1,7), left(a2,7) from t1_1024 where (a1,a2) in (select b1, b2 from t2_1024 where b1 > '0') or a2 < '9'; +-- echo +# The following two subqueries return the result of a string function with a +# blob argument, where the return type may be != blob. These are rejected during +# cost-based optimization when attempting to create a temporary table. +explain +select left(a1,7), left(a2,7) from t1_1024 where a1 in (select substring(b1,1,1024) from t2_1024 where b1 > '0') or a2 < '9'; +select left(a1,7), left(a2,7) from t1_1024 where a1 in (select substring(b1,1,1024) from t2_1024 where b1 > '0') or a2 < '9'; +explain +select left(a1,7), left(a2,7) from t1_1024 where (a1,a2) in (select substring(b1,1,1024), substring(b2,1,1024) from t2_1024 where b1 > '0') or a2 < '9'; +select left(a1,7), left(a2,7) from t1_1024 where (a1,a2) in (select substring(b1,1,1024), substring(b2,1,1024) from t2_1024 where b1 > '0') or a2 < '9'; +-- echo + + +-- echo +-- echo /* E. Edge cases. */ +-- echo + +-- echo /* E.1 Both materialization and in_to_exists cannot be off. */ +set @save_optimizer_switch=@@optimizer_switch; +set @@optimizer_switch = 'materialization=off,in_to_exists=off'; +--error ER_ILLEGAL_SUBQUERY_OPTIMIZER_SWITCHES +select * from t1 where a1 in (select b1 from t2 where b1 > '0' group by b1); +set @@optimizer_switch = @save_optimizer_switch; + +-- echo /* E.2 Outer query without tables, always uses IN-TO-EXISTS. */ +explain +select '1 - 03' in (select b1 from t2 where b1 > '0'); +select '1 - 03' in (select b1 from t2 where b1 > '0'); + +-- echo /* E.3 Subqueries without tables. */ +explain +select a1 from t1 where a1 in (select '1 - 03') or a2 < '9'; +select a1 from t1 where a1 in (select '1 - 03') or a2 < '9'; +-- echo UNION subqueries are currently limited to only use IN-TO-EXISTS. +explain +select a1 from t1 where a1 in (select '1 - 03' UNION select '1 - 02'); +select a1 from t1 where a1 in (select '1 - 03' UNION select '1 - 02'); + +-- echo /* E.4 optimize_cond detects FALSE where/having clause. */ +explain +select a1 from t1 where a1 in (select b1 from t2 where b1 = b2 and b2 = '1 - 03' and b1 = '1 - 02' ) or a2 < '9'; +select a1 from t1 where a1 in (select b1 from t2 where b1 = b2 and b2 = '1 - 03' and b1 = '1 - 02' ) or a2 < '9'; + +-- echo /* E.5 opt_sum_query detects no matching min/max row or substitutes MIN/MAX with a const. */ +-- echo TODO this test produces wrong result due to missing logic to handle the case +-- echo when JOIN::optimize detects an empty subquery result. +explain +select a1 from t1 where a1 in (select max(b1) from t2); +select a1 from t1 where a1 in (select max(b1) from t2); +-- echo +explain +select a1 from t1 where a1 in (select max(b1) from t2 where b1 = '7 - 02'); +select a1 from t1 where a1 in (select max(b1) from t2 where b1 = '7 - 02'); + +-- echo /* E.6 make_join_select detects impossible WHERE. * + +-- echo TODO + +-- echo /* E.7 constant optimization detects "no matching row in const table". */ + +-- echo TODO + +-- echo /* E.8 Impossible WHERE noticed after reading const tables. */ +explain +select '1 - 03' in (select b1 from t2 where b1 > '0' and b1 < '0'); +select '1 - 03' in (select b1 from t2 where b1 > '0' and b1 < '0'); + +-- echo +-- echo /* F. UPDATE/DELETE with subqueries. */ +-- echo + +-- echo TODO +-- echo diff --git a/mysql-test/include/type_hrtime.inc b/mysql-test/include/type_hrtime.inc new file mode 100644 index 00000000000..cd631f25632 --- /dev/null +++ b/mysql-test/include/type_hrtime.inc @@ -0,0 +1,128 @@ + +--source include/have_innodb.inc + +--disable_warnings +drop table if exists t1, t2, t3; +--enable_warnings + +--error ER_TOO_BIG_PRECISION +eval create table t1 (a $type(7)); + +eval create table t1 (a $type(3), key(a)); +insert t1 values ('2010-12-11 00:20:03.1234'); +insert t1 values ('2010-12-11 15:47:11.1234'); +insert t1 values (20101211010203.45678); +insert t1 values (20101211030405.789e0); +insert t1 values (99991231235959e1); +select * from t1; +--replace_regex /121000/121094/ /457000/457031/ /789000/789062/ +select truncate(a, 6) from t1; # Field::val_real() +select a DIV 1 from t1; # Field::val_int() +select group_concat(distinct a) from t1; # Field::cmp() +alter table t1 engine=innodb; +select * from t1 order by a; +select * from t1 order by a+0; +drop table t1; +eval create table t1 (a $type(4)) engine=innodb; +insert t1 values ('2010-12-11 01:02:03.456789'); +select * from t1; +select extract(microsecond from a + interval 100 microsecond) from t1 where a>'2010-11-12 01:02:03.456'; +select a from t1 where a>'2010-11-12 01:02:03.456' group by a; + +# +# metadata +# +show create table t1; +show columns from t1; +--query_vertical select table_name, column_name, column_default, is_nullable, data_type, character_maximum_length, character_octet_length, numeric_precision, numeric_scale, datetime_precision, character_set_name, collation_name, column_type, column_key, extra from information_schema.columns where table_name='t1' + +# +# update/delete +# +select a, a+interval 9876543 microsecond from t1; +update t1 set a=a+interval 9876543 microsecond; +select * from t1; +select a, a + interval 2 year from t1; +insert t1 select a + interval 2 year from t1; +select * from t1; +delete from t1 where a < 20110101; +select * from t1; + +# +# create ... select +# +create table t2 select * from t1; +create table t3 like t1; + +show create table t2; +show create table t3; +drop table t2, t3; + +# math, aggregation +insert t1 values ('2010-12-13 14:15:16.222222'); +select a, a+0, a-1, a*1, a/2 from t1; +select max(a), min(a), sum(a), avg(a) from t1; +create table t2 select a, a+0, a-1, a*1, a/2 from t1; +create table t3 select max(a), min(a), sum(a), avg(a) from t1; +show create table t2; +show create table t3; + +drop table t1, t2, t3; + +# insert, alter with conversion +--vertical_results +eval create table t1 (f0_$type $type(0), f1_$type $type(1), f2_$type $type(2), f3_$type $type(3), f4_$type $type(4), f5_$type $type(5), f6_$type $type(6)); +insert t1 values ( '2010-11-12 11:14:17.765432', '2010-11-12 11:14:17.765432', '2010-11-12 11:14:17.765432', '2010-11-12 11:14:17.765432', '2010-11-12 11:14:17.765432', '2010-11-12 11:14:17.765432', '2010-11-12 11:14:17.765432'); +select * from t1; +eval select cast(f0_$type as time(4)) time4_f0_$type, cast(f1_$type as datetime(3)) datetime3_f1_$type, cast(f2_$type as date) date_f2_$type, cast(f4_$type as double) double_f3_$type, cast(f4_$type as decimal(40,5)) decimal5_f4_$type, cast(f5_$type as signed) bigint_f5_$type, cast(f6_$type as char(255)) varchar_f6_$type from t1; +eval create table t2 (time4_f0_$type time(4), datetime3_f1_$type datetime(3), date_f2_$type date, double_f3_$type double, decimal5_f4_$type decimal(40,5), bigint_f5_$type bigint, varchar_f6_$type varchar(255)); +insert t2 select * from t1; +select * from t2; +eval alter table t1 change f0_$type time4_f0_$type time(4), change f1_$type datetime3_f1_$type datetime(3), change f2_$type date_f2_$type date, change f3_$type double_f3_$type double, change f4_$type decimal5_f4_$type decimal(40,5), change f5_$type bigint_f5_$type bigint, change f6_$type varchar_f6_$type varchar(255); +select * from t1; +eval alter table t1 modify time4_f0_$type $type(0), modify datetime3_f1_$type $type(1), modify date_f2_$type $type(2), modify double_f3_$type $type(3), modify decimal5_f4_$type $type(4), modify bigint_f5_$type $type(5), modify varchar_f6_$type $type(6); +select * from t1; +delete from t1; +insert t1 select * from t2; +select * from t1; +drop table t1, t2; +--horizontal_results + +# +# SP +# +eval create table t1 (a $type(6), b $type(6)); +eval create procedure foo(x $type, y $type(4)) insert into t1 values (x, y); +call foo('2010-02-03 4:5:6.789123', '2010-02-03 4:5:6.789123'); +select * from t1; +delimiter |; +eval create procedure bar(a int, c $type(5)) +begin + declare b $type(4); + set b = c + interval a microsecond; + insert t1 values (b, c + interval a microsecond); +end| +delimiter ;| +call bar(1111111, '2011-01-02 3:4:5.123456'); +select * from t1; +drop procedure foo; +drop procedure bar; +eval create function xyz(s char(20)) returns $type(4) + return addtime('2010-10-10 10:10:10.101010', s); +select xyz('1:1:1.010101'); +drop function xyz; + +# +# Views +# + +create view v1 as select * from t1 group by a,b; +select * from v1; +show columns from v1; +create table t2 select * from v1; +show create table t2; +select * from t2; + +drop view v1; +drop table t1, t2; + diff --git a/mysql-test/include/varchar.inc b/mysql-test/include/varchar.inc index 4501659158d..50741130895 100644 --- a/mysql-test/include/varchar.inc +++ b/mysql-test/include/varchar.inc @@ -92,7 +92,7 @@ explain select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' alter table t1 add unique(v); alter table t1 add key(v); select concat('*',v,'*',c,'*',t,'*') as qq from t1 where v='a'; ---replace_column 6 # 9 # +--replace_column 6 # 9 # 10 # explain select * from t1 where v='a'; # GROUP BY @@ -129,7 +129,7 @@ explain select count(*) from t1 where v like 'a%'; explain select count(*) from t1 where v between 'a' and 'a '; --replace_column 9 # explain select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n'; ---replace_column 9 # +--replace_column 9 # 10 # explain select * from t1 where v='a'; # GROUP BY @@ -158,7 +158,7 @@ explain select count(*) from t1 where v like 'a%'; explain select count(*) from t1 where v between 'a' and 'a '; --replace_column 9 # explain select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n'; ---replace_column 9 # +--replace_column 9 # 10 # explain select * from t1 where v='a'; # GROUP BY diff --git a/mysql-test/include/world.inc b/mysql-test/include/world.inc index eae6556c422..eae6556c422 100644..100755 --- a/mysql-test/include/world.inc +++ b/mysql-test/include/world.inc diff --git a/mysql-test/include/world_schema.inc b/mysql-test/include/world_schema.inc index c683faf0114..c683faf0114 100644..100755 --- a/mysql-test/include/world_schema.inc +++ b/mysql-test/include/world_schema.inc |