diff options
Diffstat (limited to 'mysql-test/t')
27 files changed, 403 insertions, 151 deletions
diff --git a/mysql-test/t/change_user-master.opt b/mysql-test/t/change_user-master.opt new file mode 100644 index 00000000000..cef79bc8585 --- /dev/null +++ b/mysql-test/t/change_user-master.opt @@ -0,0 +1 @@ +--force-restart diff --git a/mysql-test/t/change_user.test b/mysql-test/t/change_user.test index e24ade3efd5..540f9d1b53f 100644 --- a/mysql-test/t/change_user.test +++ b/mysql-test/t/change_user.test @@ -50,9 +50,32 @@ SELECT IS_USED_LOCK('bug31418'); # Bug#31222: com_% global status counters behave randomly with # mysql_change_user. # -# Moved to not_embedded_server.test due to Bug#34517: SHOW GLOBAL STATUS does -# not work properly in embedded server. -# -# TODO: move it back when Bug#34517 is fixed (don't forget to add -# --force-restart into change_user-master.opt). -# + +FLUSH STATUS; + +--disable_result_log +--disable_query_log + +let $i = 100; + +while ($i) +{ + dec $i; + + SELECT 1; +} + +--enable_query_log +--enable_result_log + +let $before= query_get_value(SHOW GLOBAL STATUS LIKE 'com_select',Value,1); + +--change_user + +let $after= query_get_value(SHOW GLOBAL STATUS LIKE 'com_select',Value,1); + +if (`select $after != $before`){ + SHOW GLOBAL STATUS LIKE 'com_select'; + die The value of com_select changed during change_user; +} +echo Value of com_select did not change; diff --git a/mysql-test/t/ctype_ldml.test b/mysql-test/t/ctype_ldml.test index fc6ed0f2579..73a23a751e8 100644 --- a/mysql-test/t/ctype_ldml.test +++ b/mysql-test/t/ctype_ldml.test @@ -21,6 +21,22 @@ insert into t1 values ('a'); select * from t1 where c1='b'; drop table t1; + +# +# Bug#41084 full-text index added to custom UCA collation not working +# +CREATE TABLE t1 ( + col1 varchar(100) character set utf8 collate utf8_test_ci +); +INSERT INTO t1 (col1) VALUES ('abcd'),('efgh'),('ijkl'); +ALTER TABLE t1 ADD FULLTEXT INDEX (col1); +SELECT * FROM t1 where match (col1) against ('abcd'); +SELECT * FROM t1 where match (col1) against ('abcd' IN BOOLEAN MODE); +ALTER TABLE t1 ADD (col2 varchar(100) character set latin1); +UPDATE t1 SET col2=col1; +SELECT * FROM t1 WHERE col1=col2 ORDER BY col1; +DROP TABLE t1; + # # Vietnamese experimental collation # diff --git a/mysql-test/t/ctype_utf8.test b/mysql-test/t/ctype_utf8.test index f0c769251cf..bb64319f1e4 100644 --- a/mysql-test/t/ctype_utf8.test +++ b/mysql-test/t/ctype_utf8.test @@ -164,7 +164,7 @@ drop table t1; # UTF8 breaks primary keys for cols > 333 characters # --error 1071 -create table t1 (a text character set utf8, primary key(a(360))); +create table t1 (a text character set utf8, primary key(a(371))); # diff --git a/mysql-test/t/date_formats.test b/mysql-test/t/date_formats.test index e474fac8a2a..e5dc7ffa53e 100644 --- a/mysql-test/t/date_formats.test +++ b/mysql-test/t/date_formats.test @@ -6,10 +6,15 @@ drop table if exists t1; --enable_warnings ---replace_result ROW <format> STATEMENT <format> MIXED <format> -SHOW GLOBAL VARIABLES LIKE "%e_format"; ---replace_result ROW <format> STATEMENT <format> MIXED <format> -SHOW SESSION VARIABLES LIKE "%e_format"; +SELECT variable_name, variable_value +FROM information_schema.global_variables +WHERE variable_name IN ('date_format', 'datetime_format', 'time_format') +ORDER BY variable_name; + +SELECT variable_name, variable_value +FROM information_schema.session_variables +WHERE variable_name IN ('date_format', 'datetime_format', 'time_format') +ORDER BY variable_name; # # Test setting a lot of different formats to see which formats are accepted and @@ -36,8 +41,10 @@ set datetime_format= '%H:%i:%s.%f %m-%d-%Y'; set datetime_format= '%h:%i:%s %p %Y-%m-%d'; set datetime_format= '%h:%i:%s.%f %p %Y-%m-%d'; ---replace_result ROW <format> STATEMENT <format> MIXED <format> -SHOW SESSION VARIABLES LIKE "%e_format"; +SELECT variable_name, variable_value +FROM information_schema.session_variables +WHERE variable_name IN ('date_format', 'datetime_format', 'time_format') +ORDER BY variable_name; --error 1231 SET time_format='%h:%i:%s'; @@ -121,7 +128,7 @@ SET datetime_format=default; # Test of str_to_date # -# PS doesn't support fraction of a seconds +# PS doesn't support fractions of a second --disable_ps_protocol select str_to_date(concat('15-01-2001',' 2:59:58.999'), concat('%d-%m-%Y',' ','%H:%i:%s.%f')); diff --git a/mysql-test/t/disabled.def b/mysql-test/t/disabled.def index 118efb6f7ed..f61090102ff 100644 --- a/mysql-test/t/disabled.def +++ b/mysql-test/t/disabled.def @@ -9,18 +9,6 @@ # Do not use any TAB characters for whitespace. # ############################################################################## -delayed_insert_limit_func : BUG#37962 2008-07-08 sven *_func tests containing sleeps/race conditions -event_scheduler_func : BUG#37962, BUG#40816 2008-07-08 sven *_func tests containing sleeps/race conditions -interactive_timeout_func : BUG#37962 2008-07-08 sven *_func tests containing sleeps/race conditions -query_cache_wlock_invalidate_func : BUG#37962 2008-07-08 sven *_func tests containing sleeps/race conditions -rpl_init_slave_func : BUG#37962 2008-07-08 sven *_func tests containing sleeps/race conditions -rpl_max_binlog_size_func : BUG#37962 2008-07-08 sven *_func tests containing sleeps/race conditions -slow_query_log_func : BUG#37962 2008-07-08 sven *_func tests containing sleeps/race conditions -sql_low_priority_updates_func : BUG#37962 2008-07-08 sven *_func tests containing sleeps/race conditions -timestamp_func : BUG#37962 2008-07-08 sven *_func tests containing sleeps/race conditions -innodb_max_dirty_pages_pct_func : BUG#41018 BUG#39382 2008-12-02 sven test fails often. some failures fill up the disk, causing subsequent failures in many other tests -federated_transactions : Bug#29523 Transactions do not work -wait_timeout_func : Bug #41225 joro wait_timeout_func fails kill : Bug#37780 2008-12-03 HHunger need some changes to be robust enough for pushbuild. query_cache_28249 : Bug#41098 Query Cache returns wrong result with concurrent insert innodb_bug39438 : BUG#42383 2009-01-28 lsoares "This fails in embedded and on windows. Note that this test is not run on windows and on embedded in PB for main trees currently" diff --git a/mysql-test/t/func_compress.test b/mysql-test/t/func_compress.test index 0ad5b3ca7b1..dc17be219e3 100644 --- a/mysql-test/t/func_compress.test +++ b/mysql-test/t/func_compress.test @@ -49,6 +49,9 @@ set @@global.max_allowed_packet=1048576*100; # reconnect to make the new max packet size take effect --connect (newconn, localhost, root,,) eval select compress(repeat('aaaaaaaaaa', IF('$LOW_MEMORY', 10, 10000000))) is null; +disconnect newconn; +connection default; +set @@global.max_allowed_packet=default; # # Bug #18643: problem with null values diff --git a/mysql-test/t/greedy_optimizer.test b/mysql-test/t/greedy_optimizer.test index b73f70c6a3e..5131c97f122 100644 --- a/mysql-test/t/greedy_optimizer.test +++ b/mysql-test/t/greedy_optimizer.test @@ -311,3 +311,76 @@ explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and show status like 'Last_query_cost'; drop table t1,t2,t3,t4,t5,t6,t7; + + +# +# Bug # 38795: Automatic search depth and nested join's results in server +# crash +# + +CREATE TABLE t1 (a int, b int, d int, i int); INSERT INTO t1 VALUES (1,1,1,1); +CREATE TABLE t2 (b int, c int, j int); INSERT INTO t2 VALUES (1,1,1); +CREATE TABLE t2_1 (j int); INSERT INTO t2_1 VALUES (1); +CREATE TABLE t3 (c int, f int); INSERT INTO t3 VALUES (1,1); +CREATE TABLE t3_1 (f int); INSERT INTO t3_1 VALUES (1); +CREATE TABLE t4 (d int, e int, k int); INSERT INTO t4 VALUES (1,1,1); +CREATE TABLE t4_1 (k int); INSERT INTO t4_1 VALUES (1); +CREATE TABLE t5 (g int, d int, h int, l int); INSERT INTO t5 VALUES (1,1,1,1); +CREATE TABLE t5_1 (l int); INSERT INTO t5_1 VALUES (1); + +SET optimizer_search_depth = 3; + +SELECT 1 +FROM t1 +LEFT JOIN ( + t2 JOIN t3 ON t3.c = t2.c +) ON t2.b = t1.b +LEFT JOIN ( + t4 JOIN t5 ON t5.d = t4.d +) ON t4.d = t1.d +; + +SELECT 1 +FROM t1 +LEFT JOIN ( + t2 LEFT JOIN (t3 JOIN t3_1 ON t3.f = t3_1.f) ON t3.c = t2.c +) ON t2.b = t1.b +LEFT JOIN ( + t4 JOIN t5 ON t5.d = t4.d +) ON t4.d = t1.d +; + +SELECT 1 +FROM t1 +LEFT JOIN ( + (t2 JOIN t2_1 ON t2.j = t2_1.j) JOIN t3 ON t3.c = t2.c +) ON t2.b = t1.b +LEFT JOIN ( + t4 JOIN t5 ON t5.d = t4.d +) ON t4.d = t1.d +; + +SELECT 1 +FROM t1 +LEFT JOIN ( + t2 JOIN t3 ON t3.c = t2.c +) ON t2.b = t1.b +LEFT JOIN ( + (t4 JOIN t4_1 ON t4.k = t4_1.k) LEFT JOIN t5 ON t5.d = t4.d +) ON t4.d = t1.d +; + +SELECT 1 +FROM t1 +LEFT JOIN ( + t2 JOIN t3 ON t3.c = t2.c +) ON t2.b = t1.b +LEFT JOIN ( + t4 LEFT JOIN (t5 JOIN t5_1 ON t5.l = t5_1.l) ON t5.d = t4.d +) ON t4.d = t1.d +; + +SET optimizer_search_depth = DEFAULT; +DROP TABLE t1,t2,t2_1,t3,t3_1,t4,t4_1,t5,t5_1; + +--echo End of 5.0 tests diff --git a/mysql-test/t/having.test b/mysql-test/t/having.test index 683abfd3783..af9af4fe1fc 100644 --- a/mysql-test/t/having.test +++ b/mysql-test/t/having.test @@ -432,3 +432,14 @@ select f1 from t1 having max(f1)=f1; select f1 from t1 group by f1 having max(f1)=f1; set session sql_mode=''; drop table t1; + + +# +# Bug #38637: COUNT DISTINCT prevents NULL testing in HAVING clause +# +CREATE TABLE t1 ( a INT, b INT); +INSERT INTO t1 VALUES (1, 1), (2,2), (3, NULL); +SELECT b, COUNT(DISTINCT a) FROM t1 GROUP BY b HAVING b is NULL; +DROP TABLE t1; + +--echo End of 5.0 tests diff --git a/mysql-test/t/init_file.test b/mysql-test/t/init_file.test index a6efbf6722e..ec74a5ff5ef 100644 --- a/mysql-test/t/init_file.test +++ b/mysql-test/t/init_file.test @@ -13,7 +13,8 @@ INSERT INTO init_file.startup VALUES ( NOW() ); SELECT * INTO @X FROM init_file.startup limit 0,1; SELECT * INTO @Y FROM init_file.startup limit 1,1; SELECT YEAR(@X)-YEAR(@Y); -DROP DATABASE init_file; +# Enable this DROP DATABASE only after resolving bug #42507 +# DROP DATABASE init_file; --echo ok --echo end of 4.1 tests @@ -26,4 +27,5 @@ select * from t1; # Expected: # 30, 3, 11, 13 select * from t2; -drop table t1, t2; +# Enable this DROP TABLE only after resolving bug #42507 +#drop table t1, t2; diff --git a/mysql-test/t/innodb-semi-consistent-master.opt b/mysql-test/t/innodb-semi-consistent-master.opt index 2746e4e184e..e76299453d3 100644 --- a/mysql-test/t/innodb-semi-consistent-master.opt +++ b/mysql-test/t/innodb-semi-consistent-master.opt @@ -1 +1 @@ ---innodb_locks_unsafe_for_binlog=true --innodb_lock_wait_timeout=2 +--innodb_lock_wait_timeout=2 diff --git a/mysql-test/t/innodb-semi-consistent.test b/mysql-test/t/innodb-semi-consistent.test index a3496625e95..6d3020bb560 100644 --- a/mysql-test/t/innodb-semi-consistent.test +++ b/mysql-test/t/innodb-semi-consistent.test @@ -11,7 +11,7 @@ connect (a,localhost,root,,); connect (b,localhost,root,,); connection a; set binlog_format=mixed; -set session transaction isolation level read committed; +set session transaction isolation level repeatable read; create table t1(a int not null) engine=innodb DEFAULT CHARSET=latin1; insert into t1 values (1),(2),(3),(4),(5),(6),(7); set autocommit=0; @@ -19,13 +19,15 @@ set autocommit=0; select * from t1 where a=3 lock in share mode; connection b; set binlog_format=mixed; -set session transaction isolation level read committed; +set session transaction isolation level repeatable read; set autocommit=0; -- error ER_LOCK_WAIT_TIMEOUT update t1 set a=10 where a=5; connection a; commit; connection b; +# perform a semi-consisent read (and unlock non-matching rows) +set session transaction isolation level read committed; update t1 set a=10 where a=5; connection a; -- error ER_LOCK_WAIT_TIMEOUT @@ -33,6 +35,7 @@ select * from t1 where a=2 for update; # this should lock the records (1),(2) select * from t1 where a=2 limit 1 for update; connection b; +# semi-consistent read will skip non-matching locked rows a=1, a=2 update t1 set a=11 where a=6; -- error ER_LOCK_WAIT_TIMEOUT update t1 set a=12 where a=2; diff --git a/mysql-test/t/innodb_bug34300.test b/mysql-test/t/innodb_bug34300.test index ff81d3476f8..c4dbce02e05 100644 --- a/mysql-test/t/innodb_bug34300.test +++ b/mysql-test/t/innodb_bug34300.test @@ -21,6 +21,7 @@ CREATE TABLE bug34300 ( INSERT INTO bug34300 VALUES ('xxx', repeat('a', 8459264), 'zzz'); +-- enable_query_log -- enable_result_log SELECT f4, f8 FROM bug34300; @@ -31,9 +32,10 @@ SELECT f4, f8 FROM bug34300; DROP TABLE bug34300; +disconnect newconn; +connection default; --disable_result_log --disable_query_log SET @@global.max_allowed_packet=default; --enable_result_log --enable_query_log - diff --git a/mysql-test/t/myisampack.test b/mysql-test/t/myisampack.test index 2650c0d2cc3..f554e7d0184 100644 --- a/mysql-test/t/myisampack.test +++ b/mysql-test/t/myisampack.test @@ -35,6 +35,8 @@ DROP TABLE t1; # # Bug#40949 Debug version of MySQL server crashes when run OPTIMIZE on compressed table. +# expanded with testcase for +# BUG#41574 - REPAIR TABLE: crashes for compressed tables # --disable_warnings drop table if exists t1; @@ -57,4 +59,5 @@ flush tables; let $MYSQLD_DATADIR= `select @@datadir`; --exec $MYISAMPACK $MYSQLD_DATADIR/test/t1 optimize table t1; +repair table t1; drop table t1; diff --git a/mysql-test/t/not_embedded_server.test b/mysql-test/t/not_embedded_server.test index 562194a62a4..233ac3edfbb 100644 --- a/mysql-test/t/not_embedded_server.test +++ b/mysql-test/t/not_embedded_server.test @@ -4,6 +4,11 @@ -- source include/not_embedded.inc +# +# Produce output +# + +select 1; # The following fails sporadically because 'check-testcase' runs # queries before this test and there is no way to guarantee that any @@ -31,45 +36,4 @@ #execute stmt1; #deallocate prepare stmt1; - - -# -# Bug#31222: com_% global status counters behave randomly with -# mysql_change_user. -# -# Moved from change_user.test due to Bug#34517: SHOW GLOBAL STATUS does not -# work properly in embedded server. -# -# TODO: move it back when Bug#34517 is fixed. -# - -FLUSH STATUS; - ---disable_result_log ---disable_query_log - -let $i = 100; - -while ($i) -{ - dec $i; - - SELECT 1; -} - ---enable_query_log ---enable_result_log - -let $before= query_get_value(SHOW GLOBAL STATUS LIKE 'com_select',Value,1); - ---change_user - -let $after= query_get_value(SHOW GLOBAL STATUS LIKE 'com_select',Value,1); - -if (`select $after != $before`){ - SHOW GLOBAL STATUS LIKE 'com_select'; - die The value of com_select changed during change_user; -} -echo Value of com_select did not change; - # End of 5.1 tests diff --git a/mysql-test/t/openssl_1.test b/mysql-test/t/openssl_1.test index fb9622a4ed9..e31e0a6199a 100644 --- a/mysql-test/t/openssl_1.test +++ b/mysql-test/t/openssl_1.test @@ -11,8 +11,8 @@ insert into t1 values (5); grant select on test.* to ssl_user1@localhost require SSL; grant select on test.* to ssl_user2@localhost require cipher "DHE-RSA-AES256-SHA"; -grant select on test.* to ssl_user3@localhost require cipher "DHE-RSA-AES256-SHA" AND SUBJECT "/C=SE/ST=Uppsala/L=Uppsala/O=MySQL AB/emailAddress=abstract.mysql.developer@mysql.com"; -grant select on test.* to ssl_user4@localhost require cipher "DHE-RSA-AES256-SHA" AND SUBJECT "/C=SE/ST=Uppsala/L=Uppsala/O=MySQL AB/emailAddress=abstract.mysql.developer@mysql.com" ISSUER "/C=SE/ST=Uppsala/L=Uppsala/O=MySQL AB"; +grant select on test.* to ssl_user3@localhost require cipher "DHE-RSA-AES256-SHA" AND SUBJECT "/C=SE/ST=Uppsala/O=MySQL AB/emailAddress=abstract.mysql.developer@mysql.com"; +grant select on test.* to ssl_user4@localhost require cipher "DHE-RSA-AES256-SHA" AND SUBJECT "/C=SE/ST=Uppsala/O=MySQL AB/emailAddress=abstract.mysql.developer@mysql.com" ISSUER "/C=SE/ST=Uppsala/L=Uppsala/O=MySQL AB"; grant select on test.* to ssl_user5@localhost require cipher "DHE-RSA-AES256-SHA" AND SUBJECT "xxx"; flush privileges; diff --git a/mysql-test/t/partition_innodb_semi_consistent.test b/mysql-test/t/partition_innodb_semi_consistent.test index cfa170f575b..6a6a7cf958e 100644 --- a/mysql-test/t/partition_innodb_semi_consistent.test +++ b/mysql-test/t/partition_innodb_semi_consistent.test @@ -14,7 +14,7 @@ connect (a,localhost,root,,); connect (b,localhost,root,,); connection a; set binlog_format=mixed; -set session transaction isolation level read committed; +set session transaction isolation level repeatable read; create table t1(a int not null) engine=innodb DEFAULT CHARSET=latin1 @@ -27,7 +27,7 @@ set autocommit=0; select * from t1 where a=3 lock in share mode; connection b; set binlog_format=mixed; -set session transaction isolation level read committed; +set session transaction isolation level repeatable read; set autocommit=0; -- error ER_LOCK_WAIT_TIMEOUT update t1 set a=10 where a=5; @@ -35,6 +35,8 @@ connection a; #DELETE FROM t1 WHERE a=5; commit; connection b; +# perform a semi-consisent read (and unlock non-matching rows) +set session transaction isolation level read committed; update t1 set a=10 where a=5; connection a; -- error ER_LOCK_WAIT_TIMEOUT @@ -42,6 +44,7 @@ select * from t1 where a=2 for update; # this should lock the records (1),(2) select * from t1 where a=2 limit 1 for update; connection b; +# semi-consistent read will skip non-matching locked rows a=1, a=2 update t1 set a=11 where a=6; -- error ER_LOCK_WAIT_TIMEOUT update t1 set a=12 where a=2; diff --git a/mysql-test/t/query_cache.test b/mysql-test/t/query_cache.test index 886d677a26a..9a1547fa3d4 100644 --- a/mysql-test/t/query_cache.test +++ b/mysql-test/t/query_cache.test @@ -1261,6 +1261,33 @@ DROP TABLE t1; SET GLOBAL concurrent_insert= @save_concurrent_insert; SET GLOBAL query_cache_size= default; +# +# Bug#36326: nested transaction and select +# + +--disable_warnings +DROP TABLE IF EXISTS t1; +--enable_warnings + +FLUSH STATUS; +SET GLOBAL query_cache_size=1048576; +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(2),(3),(4),(5); +SHOW STATUS LIKE 'Qcache_queries_in_cache'; +SELECT * FROM t1; +BEGIN; +SELECT * FROM t1; +COMMIT; +SHOW STATUS LIKE 'Qcache_queries_in_cache'; +SHOW STATUS LIKE "Qcache_hits"; +SELECT * FROM t1; +BEGIN; +SELECT * FROM t1; +COMMIT; +SHOW STATUS LIKE "Qcache_hits"; +DROP TABLE t1; +SET GLOBAL query_cache_size= default; + --echo End of 5.0 tests # diff --git a/mysql-test/t/query_cache_notembedded.test b/mysql-test/t/query_cache_notembedded.test index ecb7bfc100e..c3889ef0267 100644 --- a/mysql-test/t/query_cache_notembedded.test +++ b/mysql-test/t/query_cache_notembedded.test @@ -226,6 +226,9 @@ disconnect con2; connection default; set GLOBAL query_cache_size=0; + +# End of 5.0 tests + SET @@global.log_bin_trust_function_creators = @old_log_bin_trust_function_creators; # diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index 569df9368f6..57cae8af815 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -3396,4 +3396,39 @@ DELETE FROM v3; DROP VIEW v1,v2,v3; DROP TABLE t1,t2; +--echo # +--echo # BUG#37822: Correlated subquery with IN and IS UNKNOWN provides wrong result +--echo # +create table t1(id integer primary key, g integer, v integer, s char(1)); +create table t2(id integer primary key, g integer, v integer, s char(1)); +insert into t1 values + (10, 10, 10, 'l'), + (20, 20, 20, 'l'), + (40, 40, 40, 'l'), + (41, 40, null, 'l'), + (50, 50, 50, 'l'), + (51, 50, null, 'l'), + (60, 60, 60, 'l'), + (61, 60, null, 'l'), + (70, 70, 70, 'l'), + (90, 90, null, 'l'); +insert into t2 values + (10, 10, 10, 'r'), + (30, 30, 30, 'r'), + (50, 50, 50, 'r'), + (60, 60, 60, 'r'), + (61, 60, null, 'r'), + (70, 70, 70, 'r'), + (71, 70, null, 'r'), + (80, 80, 80, 'r'), + (81, 80, null, 'r'), + (100,100,null, 'r'); + +select * +from t1 +where v in(select v + from t2 + where t1.g=t2.g) is unknown; +drop table t1, t2; + --echo End of 5.1 tests. diff --git a/mysql-test/t/subselect3.test b/mysql-test/t/subselect3.test index 7c3d5013b8d..bf461f83a20 100644 --- a/mysql-test/t/subselect3.test +++ b/mysql-test/t/subselect3.test @@ -640,6 +640,35 @@ WHERE NULL NOT IN ( DROP TABLE t1; +# +# Bug #39069: <row constructor> IN <table-subquery> seriously messed up +# + +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1), (2), (11); + +--echo # 2nd and 3rd columns should be same +SELECT a, ROW(11, 12) = (SELECT a, 22), ROW(11, 12) IN (SELECT a, 22) FROM t1 GROUP BY t1.a; +SELECT a, ROW(11, 12) = (SELECT a, 12), ROW(11, 12) IN (SELECT a, 12) FROM t1 GROUP BY t1.a; +SELECT a, ROW(11, 12) = (SELECT a, 22), ROW(11, 12) IN (SELECT a, 22) FROM t1; +SELECT a, ROW(11, 12) = (SELECT a, 12), ROW(11, 12) IN (SELECT a, 12) FROM t1; + +# The x alias is used below to workaround bug #40674. +# Regression tests for sum function on outer column in subselect from dual: +SELECT a AS x, ROW(11, 12) = (SELECT MAX(x), 22), ROW(11, 12) IN (SELECT MAX(x), 22) FROM t1; +--echo # 2nd and 3rd columns should be same for x == 11 only +SELECT a AS x, ROW(11, 12) = (SELECT MAX(x), 12), ROW(11, 12) IN (SELECT MAX(x), 12) FROM t1; + +DROP TABLE t1; + +--echo # both columns should be same +SELECT ROW(1,2) = (SELECT NULL, NULL), ROW(1,2) IN (SELECT NULL, NULL); +SELECT ROW(1,2) = (SELECT 1, NULL), ROW(1,2) IN (SELECT 1, NULL); +SELECT ROW(1,2) = (SELECT NULL, 2), ROW(1,2) IN (SELECT NULL, 2); +SELECT ROW(1,2) = (SELECT NULL, 1), ROW(1,2) IN (SELECT NULL, 1); +SELECT ROW(1,2) = (SELECT 1, 1), ROW(1,2) IN (SELECT 1, 1); +SELECT ROW(1,2) = (SELECT 1, 2), ROW(1,2) IN (SELECT 1, 2); + --echo End of 5.0 tests # diff --git a/mysql-test/t/system_mysql_db_fix30020.test b/mysql-test/t/system_mysql_db_fix30020.test index a3dc9adb254..0a7d9dad7c9 100644 --- a/mysql-test/t/system_mysql_db_fix30020.test +++ b/mysql-test/t/system_mysql_db_fix30020.test @@ -104,4 +104,5 @@ time_zone_transition_type, general_log, slow_log, event, ndb_binlog_index; # check that we dropped all system tables show tables; +exit; # End of 4.1 tests diff --git a/mysql-test/t/type_timestamp.test b/mysql-test/t/type_timestamp.test index 27922a16a37..e8374e0ebfc 100644 --- a/mysql-test/t/type_timestamp.test +++ b/mysql-test/t/type_timestamp.test @@ -324,7 +324,7 @@ insert into t1 (a, c) values (4, '2004-04-04 00:00:00'), select * from t1; drop table t1; -# End of 4.1 tests +--echo End of 4.1 tests # Restore timezone to default set time_zone= @@global.time_zone; @@ -339,3 +339,21 @@ PRIMARY KEY (`id`) show fields from t1; select is_nullable from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='t1' and COLUMN_NAME='posted_on'; drop table t1; + +# +# Bug#41370: TIMESTAMP field does not accepts NULL from FROM_UNIXTIME() +# + +CREATE TABLE t1 ( f1 INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, + f2 TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, + f3 TIMESTAMP); +INSERT INTO t1 (f2,f3) VALUES (NOW(), "0000-00-00 00:00:00"); +INSERT INTO t1 (f2,f3) VALUES (NOW(), NULL); +INSERT INTO t1 (f2,f3) VALUES (NOW(), ASCII(NULL)); +INSERT INTO t1 (f2,f3) VALUES (NOW(), FROM_UNIXTIME('9999999999')); +INSERT INTO t1 (f2,f3) VALUES (NOW(), TIME(NULL)); +UPDATE t1 SET f2=NOW(), f3=FROM_UNIXTIME('9999999999') WHERE f1=1; +SELECT f1,f2-f3 FROM t1; +DROP TABLE t1; + +--echo End of 5.0 tests diff --git a/mysql-test/t/user_var.test b/mysql-test/t/user_var.test index 5d916e410e3..fd4e538ea6c 100644 --- a/mysql-test/t/user_var.test +++ b/mysql-test/t/user_var.test @@ -285,4 +285,15 @@ set @lastid=-1; select @lastid != id, @lastid, @lastid := id from t1; drop table t1; +# +# Bug#42188: crash and/or memory corruption with user variables in trigger +# + +CREATE TABLE t1 (i INT); +CREATE TRIGGER t_after_insert AFTER INSERT ON t1 FOR EACH ROW SET @bug42188 = 10; +INSERT INTO t1 VALUES (1); +--change_user root,,test +INSERT INTO t1 VALUES (1); +DROP TABLE t1; + --echo End of 5.1 tests diff --git a/mysql-test/t/variables.test b/mysql-test/t/variables.test index 296827fdbd8..a2a519e05f3 100644 --- a/mysql-test/t/variables.test +++ b/mysql-test/t/variables.test @@ -6,7 +6,7 @@ drop table if exists t1,t2; --enable_warnings # -# Bug #19263: variables.test doesn't clean up after itself (I/II -- save) +# Bug#19263: variables.test doesn't clean up after itself (I/II -- save) # set @my_binlog_cache_size =@@global.binlog_cache_size; set @my_connect_timeout =@@global.connect_timeout; @@ -35,6 +35,8 @@ set @my_server_id =@@global.server_id; set @my_slow_launch_time =@@global.slow_launch_time; set @my_storage_engine =@@global.storage_engine; set @my_thread_cache_size =@@global.thread_cache_size; +set @my_max_allowed_packet =@@global.max_allowed_packet; +set @my_delay_key_write =@@global.delay_key_write; # case insensitivity tests (new in 5.0) set @`test`=1; select @test, @`test`, @TEST, @`TEST`, @"teSt"; @@ -197,46 +199,46 @@ SELECT @@version_compile_os LIKE 'non-existent'; # The following should give errors ---error 1231 +--error ER_WRONG_VALUE_FOR_VAR set big_tables=OFFF; ---error 1231 +--error ER_WRONG_VALUE_FOR_VAR set big_tables="OFFF"; ---error 1193 +--error ER_UNKNOWN_SYSTEM_VARIABLE set unknown_variable=1; ---error 1232 +--error ER_WRONG_TYPE_FOR_VAR set max_join_size="hello"; ---error 1286 +--error ER_UNKNOWN_STORAGE_ENGINE set storage_engine=UNKNOWN_TABLE_TYPE; ---error 1231 +--error ER_WRONG_VALUE_FOR_VAR set storage_engine=MERGE, big_tables=2; show local variables like 'storage_engine'; ---error 1229 +--error ER_GLOBAL_VARIABLE set SESSION query_cache_size=10000; ---error 1230 +--error ER_NO_DEFAULT set GLOBAL storage_engine=DEFAULT; ---error 1115 +--error ER_UNKNOWN_CHARACTER_SET set character_set_client=UNKNOWN_CHARACTER_SET; ---error 1273 +--error ER_UNKNOWN_COLLATION set collation_connection=UNKNOWN_COLLATION; ---error 1231 +--error ER_WRONG_VALUE_FOR_VAR set character_set_client=NULL; ---error 1231 +--error ER_WRONG_VALUE_FOR_VAR set collation_connection=NULL; ---error 1228 +--error ER_LOCAL_VARIABLE set global autocommit=1; ---error 1238 +--error ER_INCORRECT_GLOBAL_LOCAL_VAR select @@global.timestamp; ---error 1238 +--error ER_INCORRECT_GLOBAL_LOCAL_VAR set @@version=''; ---error 1229 +--error ER_GLOBAL_VARIABLE set @@concurrent_insert=1; ---error 1228 +--error ER_LOCAL_VARIABLE set @@global.sql_auto_is_null=1; ---error 1238 +--error ER_INCORRECT_GLOBAL_LOCAL_VAR select @@global.sql_auto_is_null; ---error 1229 +--error ER_GLOBAL_VARIABLE set myisam_max_sort_file_size=100; ---error 1231 +--error ER_WRONG_VALUE_FOR_VAR set @@SQL_WARNINGS=NULL; # Test setting all variables @@ -367,23 +369,23 @@ drop table t1,t2; # error conditions # ---error 1193 +--error ER_UNKNOWN_SYSTEM_VARIABLE select @@xxxxxxxxxx; select 1; ---error 1238 +--error ER_INCORRECT_GLOBAL_LOCAL_VAR select @@session.key_buffer_size; ---error 1229 +--error ER_GLOBAL_VARIABLE set ft_boolean_syntax = @@init_connect; ---error 1231 +--error ER_WRONG_VALUE_FOR_VAR set global ft_boolean_syntax = @@init_connect; ---error 1229 +--error ER_GLOBAL_VARIABLE set init_connect = NULL; set global init_connect = NULL; ---error 1229 +--error ER_GLOBAL_VARIABLE set ft_boolean_syntax = @@init_connect; ---error 1231 +--error ER_WRONG_VALUE_FOR_VAR set global ft_boolean_syntax = @@init_connect; # Bug#3754 SET GLOBAL myisam_max_sort_file_size doesn't work as @@ -416,15 +418,15 @@ select @a, @b; # # Bug#2586:Disallow global/session/local as structured var. instance names # ---error 1064 +--error ER_PARSE_ERROR set @@global.global.key_buffer_size= 1; ---error 1064 +--error ER_PARSE_ERROR set GLOBAL global.key_buffer_size= 1; ---error 1064 +--error ER_PARSE_ERROR SELECT @@global.global.key_buffer_size; ---error 1064 +--error ER_PARSE_ERROR SELECT @@global.session.key_buffer_size; ---error 1064 +--error ER_PARSE_ERROR SELECT @@global.local.key_buffer_size; # BUG#5135: cannot turn on log_warnings with SET in 4.1 (and 4.0) @@ -515,27 +517,27 @@ select @@lc_time_names; --echo *** LC_TIME_NAMES: testing with string expressions set lc_time_names=concat('de','_','DE'); select @@lc_time_names; ---error 1105 +--error ER_UNKNOWN_ERROR set lc_time_names=concat('de','+','DE'); select @@lc_time_names; --echo LC_TIME_NAMES: testing with numeric expressions set @@lc_time_names=1+2; select @@lc_time_names; ---error 1232 +--error ER_WRONG_TYPE_FOR_VAR set @@lc_time_names=1/0; select @@lc_time_names; set lc_time_names=en_US; --echo LC_TIME_NAMES: testing NULL and a negative number: ---error 1231 +--error ER_WRONG_VALUE_FOR_VAR set lc_time_names=NULL; ---error 1105 +--error ER_UNKNOWN_ERROR set lc_time_names=-1; select @@lc_time_names; --echo LC_TIME_NAMES: testing locale with the last ID: set lc_time_names=108; select @@lc_time_names; --echo LC_TIME_NAMES: testing a number beyond the valid ID range: ---error 1105 +--error ER_UNKNOWN_ERROR set lc_time_names=109; select @@lc_time_names; --echo LC_TIME_NAMES: testing that 0 is en_US: @@ -577,7 +579,7 @@ select @@query_prealloc_size = @test; # Bug#31588 buffer overrun when setting variables # # Buffer-size Off By One. Should throw valgrind-warning without fix #31588. ---error 1231 +--error ER_WRONG_VALUE_FOR_VAR set global sql_mode=repeat('a',80); --echo End of 4.1 tests @@ -595,9 +597,9 @@ drop table t1; # Bug #10339: read only variables. # ---error 1238 +--error ER_INCORRECT_GLOBAL_LOCAL_VAR set @@warning_count=1; ---error 1238 +--error ER_INCORRECT_GLOBAL_LOCAL_VAR set @@global.error_count=1; # @@ -615,9 +617,9 @@ select @@max_heap_table_size > 0; # Bug #11775 Variable character_set_system does not exist (sometimes) # select @@character_set_system; ---error 1238 +--error ER_INCORRECT_GLOBAL_LOCAL_VAR set global character_set_system = latin1; ---error 1238 +--error ER_INCORRECT_GLOBAL_LOCAL_VAR set @@global.version_compile_os='234'; # @@ -728,7 +730,7 @@ select @@@; # Don't actually output, since it depends on the system --replace_column 1 # select @@hostname; ---error 1238 +--error ER_INCORRECT_GLOBAL_LOCAL_VAR set @@hostname= "anothername"; --replace_column 2 # show variables like 'hostname'; @@ -771,6 +773,7 @@ set global slow_launch_time =@my_slow_launch_time; set global storage_engine =@my_storage_engine; set global thread_cache_size =@my_thread_cache_size; set global max_allowed_packet = default; +set global delay_key_write =@my_delay_key_write; # # Bug#28580 Repeatation of status variables # diff --git a/mysql-test/t/wait_timeout-master.opt b/mysql-test/t/wait_timeout-master.opt deleted file mode 100644 index 9e5c2289eb2..00000000000 --- a/mysql-test/t/wait_timeout-master.opt +++ /dev/null @@ -1 +0,0 @@ ---wait-timeout=1 diff --git a/mysql-test/t/wait_timeout.test b/mysql-test/t/wait_timeout.test index 255947fa79f..63809252a01 100644 --- a/mysql-test/t/wait_timeout.test +++ b/mysql-test/t/wait_timeout.test @@ -1,40 +1,51 @@ -# This tests not performed with embedded server +# Last modification: +# 2009-01-19 H.Hunger Fix Bug#39108 main.wait_timeout fails sporadically +# - Increase wait timeout to 2 seconds +# - Eliminated the corresponding opt file, +# set global wait timeout within the test. +# - Replaced sleeps by wait condition +# - Minor improvements +############################################################################### +# These tests cannot run with the embedded server -- source include/not_embedded.inc -- source include/one_thread_per_connection.inc # -# Bug #8731: wait_timeout does not work on Mac OS X +# Bug#8731: wait_timeout does not work on Mac OS X # +let $start_value= `SELECT @@global.wait_timeout`; +SET @@global.wait_timeout= 2; +--echo disconnect default; +disconnect default; # Connect with another connection and reset counters --disable_query_log connect (wait_con,localhost,root,,test,,); +--echo connection wait_con; connection wait_con; -set session wait_timeout=100; +SET SESSION wait_timeout=100; let $retries=300; -set @aborted_clients= 0; +SET @aborted_clients= 0; --enable_query_log # Disable reconnect and do the query +connect (default,localhost,root,,test,,); +--echo connection default; connection default; -# If slow host (Valgrind...), we may have already timed out here. -# So force a reconnect if necessary, using a dummy query. And issue a -# 'flush status' to reset the 'aborted_clients' counter. ---enable_reconnect -select 0; -flush status; +--echo --disable_reconnect; --disable_reconnect -select 1; +SELECT 1; # Switch to wait_con and wait until server has aborted the connection --disable_query_log +--echo connection wait_con; connection wait_con; while (!`select @aborted_clients`) { - sleep 0.1; + real_sleep 0.1; let $aborted_clients = `SHOW STATUS LIKE 'aborted_clients'`; - eval set @aborted_clients= SUBSTRING('$aborted_clients', 16)+0; + eval SET @aborted_clients= SUBSTRING('$aborted_clients', 16)+0; dec $retries; if (!$retries) @@ -45,44 +56,51 @@ while (!`select @aborted_clients`) --enable_query_log # The server has disconnected, add small sleep to make sure # the disconnect has reached client -sleep 1; - +let $wait_condition= SELECT COUNT(*)=1 FROM information_schema.processlist; +--source include/wait_condition.inc +--echo connection default; connection default; # When the connection is closed in this way, the error code should -# be consistent see bug#2845 for an explanation +# be consistent see Bug#2845 for an explanation # depending on platform/client, either errno 2006 or 2013 can occur below --error 2006,2013 -select 2; +SELECT 2; +--echo --enable_reconnect; --enable_reconnect -select 3; +SELECT 3; # Disconnect so that we will not be confused by a future abort from this # connection. +--echo disconnection default; disconnect default; # # Do the same test as above on a TCP connection -# (which we get by specifying a ip adress) +# (which we get by specifying an ip adress) # Connect with another connection and reset counters --disable_query_log +--echo connection wait_con; connection wait_con; -flush status; # Reset counters +FLUSH STATUS; # Reset counters let $retries=300; -set @aborted_clients= 0; +SET @aborted_clients= 0; --enable_query_log +--echo connection con1; connect (con1,127.0.0.1,root,,test,$MASTER_MYPORT,); +--echo --disable_reconnect; --disable_reconnect -select 1; +SELECT 1; # Switch to wait_con and wait until server has aborted the connection --disable_query_log +--echo connection wait_con; connection wait_con; while (!`select @aborted_clients`) { - sleep 0.1; + real_sleep 0.1; let $aborted_clients = `SHOW STATUS LIKE 'aborted_clients'`; - eval set @aborted_clients= SUBSTRING('$aborted_clients', 16)+0; + eval SET @aborted_clients= SUBSTRING('$aborted_clients', 16)+0; dec $retries; if (!$retries) @@ -93,14 +111,23 @@ while (!`select @aborted_clients`) --enable_query_log # The server has disconnected, add small sleep to make sure # the disconnect has reached client -sleep 1; +let $wait_condition= SELECT COUNT(*)=1 FROM information_schema.processlist; +--source include/wait_condition.inc +--echo connection con1; connection con1; # When the connection is closed in this way, the error code should -# be consistent see bug#2845 for an explanation +# be consistent see Bug#2845 for an explanation # depending on platform/client, either errno 2006 or 2013 can occur below --error 2006,2013 -select 2; +SELECT 2; +--echo --enable_reconnect; --enable_reconnect -select 3; +SELECT 3; +--replace_result $start_value <start_value> +eval SET @@global.wait_timeout= $start_value; +# ML: The start value might be changed in future ... +--echo disconnection con1; disconnect con1; + + |