diff options
Diffstat (limited to 'mysql-test/t')
79 files changed, 7028 insertions, 199 deletions
diff --git a/mysql-test/t/alter_table.test b/mysql-test/t/alter_table.test index 5534aa0a234..bf599818ad6 100644 --- a/mysql-test/t/alter_table.test +++ b/mysql-test/t/alter_table.test @@ -1062,3 +1062,18 @@ ALTER TABLE t1 CHANGE COLUMN f1 f1_no_real_change TIMESTAMP NULL DEFAULT NULL; DROP TABLE t1; --echo End of 5.1 tests + +# +# Bug #31031 ALTER TABLE regression in 5.0 +# +# The ALTER TABLE operation failed with +# ERROR 1089 (HY000): Incorrect sub part key; ... +# +CREATE TABLE t1(c CHAR(10), + i INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY); +INSERT INTO t1 VALUES('a',2),('b',4),('c',6); +ALTER TABLE t1 + DROP i, + ADD i INT UNSIGNED NOT NULL AUTO_INCREMENT, + AUTO_INCREMENT = 1; +DROP TABLE t1; diff --git a/mysql-test/t/create.test b/mysql-test/t/create.test index 5ffa1b93929..9f3c3a88151 100644 --- a/mysql-test/t/create.test +++ b/mysql-test/t/create.test @@ -1582,3 +1582,56 @@ create table t3 (a int) row_format=page; --echo --echo End of 5.1 tests + + +########################################################################### + +--echo +--echo # -- +--echo # -- Bug #43054 Assertion `!table->auto_increment_field_not_null' +--echo # -- failed when redefining trigger +--echo + +#--disable_abort_on_error + +CREATE TABLE B ( + pk INTEGER AUTO_INCREMENT, + int_key INTEGER NOT NULL, + PRIMARY KEY (pk), + KEY (int_key) +); + +INSERT IGNORE INTO B VALUES ('9', '9'); + +CREATE TABLE IF NOT EXISTS t1 ( + `pk` INTEGER NOT NULL AUTO_INCREMENT , + `int` INTEGER , + PRIMARY KEY ( `pk` ) +) SELECT `pk` , `int_key` FROM B ; + +--delimiter | + +CREATE TRIGGER f BEFORE INSERT ON t1 FOR EACH ROW +BEGIN + INSERT INTO t1 ( `int` ) VALUES (4 ),( 8 ),( 2 ) ; +END ; | + +--delimiter ; +--error ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG +CREATE TABLE IF NOT EXISTS t1 ( + `pk` INTEGER NOT NULL AUTO_INCREMENT , + `int` INTEGER , + PRIMARY KEY ( `pk` ) +) SELECT `pk` , `int_key` FROM B ; + +--delimiter | +--error ER_NOT_SUPPORTED_YET +CREATE TRIGGER f BEFORE INSERT ON t1 FOR EACH ROW +BEGIN + UPDATE A SET `pk`=1 WHERE `pk`=0 ; +END ;| + +--delimiter ; + +DROP TABLE t1; +DROP TABLE B; diff --git a/mysql-test/t/ctype_collate.test b/mysql-test/t/ctype_collate.test index 6b6abbcfbcc..cb2113277b9 100644 --- a/mysql-test/t/ctype_collate.test +++ b/mysql-test/t/ctype_collate.test @@ -172,6 +172,18 @@ DROP TABLE t1; # # Test that optimizer doesn't use indexes with wrong collation # +# +# BUG#48447, Delivering too few records with indexes using collate syntax +# +create table t1 (a varchar(1) character set latin1 collate latin1_general_ci); +insert into t1 values ('A'),('a'),('B'),('b'),('C'),('c'); +select * from t1 where a > 'B' collate latin1_bin; +select * from t1 where a <> 'B' collate latin1_bin; +create index i on t1 (a); +select * from t1 where a > 'B' collate latin1_bin; +select * from t1 where a <> 'B' collate latin1_bin; +drop table t1; + SET NAMES latin1; CREATE TABLE t1 (s1 char(10) COLLATE latin1_german1_ci, diff --git a/mysql-test/t/ctype_cp932_binlog_stm.test b/mysql-test/t/ctype_cp932_binlog_stm.test index 89df33a6df5..af6e6baf92a 100644 --- a/mysql-test/t/ctype_cp932_binlog_stm.test +++ b/mysql-test/t/ctype_cp932_binlog_stm.test @@ -22,18 +22,18 @@ CALL bug18293("Foo's a Bar", _cp932 0xED40ED41ED42, 47.93)| SELECT HEX(s1),HEX(s2),d FROM t4| DROP PROCEDURE bug18293| DROP TABLE t4| -SHOW BINLOG EVENTS FROM 370| +SHOW BINLOG EVENTS FROM 371| delimiter ;| --echo End of 5.0 tests # # #28436: Incorrect position in SHOW BINLOG EVENTS causes server coredump -# Note: 364 is a magic position (found experimentally, depends on +# Note: 365 is a magic position (found experimentally, depends on # the log's contents) that caused the server crash. --error 1220 -SHOW BINLOG EVENTS FROM 365; +SHOW BINLOG EVENTS FROM 366; --echo Bug#44352 UPPER/LOWER function doesn't work correctly on cp932 and sjis environment. CREATE TABLE t1 (a varchar(16)) character set cp932; diff --git a/mysql-test/t/ctype_errors.test b/mysql-test/t/ctype_errors.test new file mode 100644 index 00000000000..3c73fdae264 --- /dev/null +++ b/mysql-test/t/ctype_errors.test @@ -0,0 +1,47 @@ +--echo Start of 5.4 tests + +# +# ls_messages +# +CREATE TABLE t1(f1 INT); +connect (con1,localhost,root,,test); +connection con1; +SET lc_messages=ru_RU; +SHOW VARIABLES LIKE 'lc_messages'; +--error ER_TABLE_EXISTS_ERROR +CREATE TABLE t1(f1 INT); +SET NAMES utf8; +--error ER_TABLE_EXISTS_ERROR +CREATE TABLE t1(f1 INT); +connection default; +SHOW VARIABLES LIKE 'lc_messages'; +--error ER_TABLE_EXISTS_ERROR +CREATE TABLE t1(f1 INT); + +SHOW GLOBAL VARIABLES LIKE 'lc_messages'; +SET GLOBAL lc_messages=ru_RU; +SHOW GLOBAL VARIABLES LIKE 'lc_messages'; +SET GLOBAL lc_messages=en_US; + +disconnect con1; +DROP TABLE t1; + +# +# Bug#1406 Tablename in Errormessage not in default characterset +# +--error ER_BAD_TABLE_ERROR +drop table `ק`; + +# +# Bug#14602 Error messages not returned in character_set_results +# +connect (con1,localhost,root,,test); +connection con1; +SET lc_messages=cs_CZ; +SET NAMES UTF8; +--error ER_BAD_DB_ERROR +USE nonexistant; +disconnect con1; +connection default; + +--echo End of 5.4 tests diff --git a/mysql-test/t/ctype_ldml.test b/mysql-test/t/ctype_ldml.test index 0395de273de..0fcfd603aad 100644 --- a/mysql-test/t/ctype_ldml.test +++ b/mysql-test/t/ctype_ldml.test @@ -57,6 +57,7 @@ DROP TABLE t1; # # Vietnamese experimental collation # +--echo Vietnamese experimental collation show collation like 'ucs2_vn_ci'; create table t1 (c1 char(1) character set ucs2 collate ucs2_vn_ci); @@ -99,3 +100,28 @@ drop table t1; set names latin1; show collation like 'latin1_test'; select "foo" = "foo " collate latin1_test; + +-- echo The following tests check that two-byte collation IDs work +# The file ../std-data/Index.xml has a number of collations with high IDs. + +# Test that the "ID" column in I_S and SHOW queries can handle two bytes +select * from information_schema.collations where id>256 order by id; +show collation like '%test%'; + +# Test that two-byte collation ID is correctly transfered to the client side. +show collation like 'ucs2_vn_ci'; +create table t1 (c1 char(1) character set ucs2 collate ucs2_vn_ci); +insert into t1 values (0x0061); +--enable_metadata +set @@character_set_results=NULL; +select * from t1; +--disable_metadata +drop table t1; + +# +# Check maximum collation ID (2047 as of MySQL-6.0.9) +# +CREATE TABLE t1 (s1 char(10) character set utf8 collate utf8_maxuserid_ci); +INSERT INTO t1 VALUES ('a'),('b'); +SELECT * FROM t1 WHERE s1='a' ORDER BY BINARY s1; +DROP TABLE t1; diff --git a/mysql-test/t/ctype_utf8.test b/mysql-test/t/ctype_utf8.test index f0c769251cf..bb3eaa84391 100644 --- a/mysql-test/t/ctype_utf8.test +++ b/mysql-test/t/ctype_utf8.test @@ -2,6 +2,8 @@ # Tests with the utf8 character set # +--source include/have_innodb.inc + --disable_warnings drop table if exists t1,t2; --enable_warnings @@ -256,9 +258,7 @@ drop table t1; # Bug 4521: unique key prefix interacts poorly with utf8 # InnoDB: keys with prefix compression, case insensitive collation. # ---disable_warnings create table t1 (c varchar(30) character set utf8, unique(c(10))) engine=innodb; ---enable_warnings insert into t1 values ('1'),('2'),('3'),('x'),('y'),('z'); insert into t1 values ('aaaaaaaaaa'); --error ER_DUP_ENTRY @@ -306,9 +306,7 @@ drop table t1; # Bug 4521: unique key prefix interacts poorly with utf8 # InnoDB: fixed length keys, case insensitive collation # ---disable_warnings create table t1 (c char(3) character set utf8, unique (c(2))) engine=innodb; ---enable_warnings insert into t1 values ('1'),('2'),('3'),('4'),('x'),('y'),('z'); insert into t1 values ('a'); insert into t1 values ('aa'); @@ -383,12 +381,10 @@ drop table t1; # Bug 4531: unique key prefix interacts poorly with utf8 # Check BDB, case insensitive collation # ---disable_warnings create table t1 ( c char(10) character set utf8, unique key a (c(1)) ) engine=innodb; ---enable_warnings insert into t1 values ('a'),('b'),('c'),('d'),('e'),('f'); --error ER_DUP_ENTRY insert into t1 values ('aa'); @@ -506,12 +502,10 @@ drop table t1; # Bug 4531: unique key prefix interacts poorly with utf8 # Check BDB, binary collation # ---disable_warnings create table t1 ( c char(10) character set utf8 collate utf8_bin, unique key a (c(1)) ) engine=innodb; ---enable_warnings insert into t1 values ('a'),('b'),('c'),('d'),('e'),('f'); --error ER_DUP_ENTRY insert into t1 values ('aa'); @@ -543,12 +537,10 @@ drop table t1; # Bug#4594: column index make = failed for gbk, but like works # Check InnoDB # ---disable_warnings create table t1 ( str varchar(255) character set utf8 not null, key str (str(2)) ) engine=innodb; ---enable_warnings INSERT INTO t1 VALUES ('str'); INSERT INTO t1 VALUES ('str2'); select * from t1 where str='str'; @@ -581,12 +573,10 @@ drop table t1; # the same for BDB # ---disable_warnings create table t1 ( str varchar(255) character set utf8 not null, key str (str(2)) ) engine=innodb; ---enable_warnings INSERT INTO t1 VALUES ('str'); INSERT INTO t1 VALUES ('str2'); select * from t1 where str='str'; @@ -603,14 +593,11 @@ DROP TABLE t1; # # Bug #5723: length(<varchar utf8 field>) returns varying results # ---disable_warnings SET NAMES utf8; ---disable_warnings CREATE TABLE t1 ( subject varchar(255) character set utf8 collate utf8_unicode_ci, p varchar(15) character set utf8 ) ENGINE=InnoDB DEFAULT CHARSET=latin1; ---enable_warnings INSERT INTO t1 VALUES ('è°·å·ä¿ŠäºŒã¨ç”³ã—ã¾ã™ãŒã€ã‚¤ãƒ³ã‚¿ãƒ¼ãƒãƒƒãƒˆäºˆç´„ã®ä¼šå“¡ç™»éŒ²ã‚’ã—ã¾ã—ãŸã¨ã“ã‚ã€ãƒ¡ãƒ¼ãƒ«ã‚¢ãƒ‰ãƒ¬ã‚¹ã‚’é–“é•ãˆã¦ã—ã¾ã„会員ＩＤãŒå—ã‘å–ã‚‹ã“ã¨ãŒå‡ºæ¥ã¾ã›ã‚“ã§ã—ãŸã€‚é–“é•ãˆã‚¢ãƒ‰ãƒ¬ã‚¹ã¯tani-shun@n.vodafone.ne.jpを書ãè¾¼ã¿ã¾ã—ãŸã€‚ã©ã†ã™ã‚Œã°ã‚ˆã„ã§ã™ã‹ï¼Ÿ ãã®ä»–ã€ä½æ‰€ç‰ã¯é–“é•ãˆã‚ã‚Šã¾ã›ã‚“。連絡ãã ã•ã„。よã‚ã—ããŠé¡˜ã„ã—ã¾ã™ã€‚m(__)m','040312-000057'); INSERT INTO t1 VALUES ('aaa','bbb'); SELECT length(subject) FROM t1; @@ -661,18 +648,14 @@ DROP TABLE t1; # Bug #6019 SELECT tries to use too short prefix index on utf8 data # set names utf8; ---disable_warnings create table t1 ( a int primary key, b varchar(6), index b3(b(3)) ) engine=innodb character set=utf8; ---enable_warnings insert into t1 values(1,'foo'),(2,'foobar'); select * from t1 where b like 'foob%'; ---disable_warnings alter table t1 engine=innodb; ---enable_warnings select * from t1 where b like 'foob%'; drop table t1; @@ -841,14 +824,12 @@ INSERT INTO t1 VALUES (1,'blah','464','aaa','fkc1c9ilc20x0hgae7lx6j09','ERR','ERR Имри.Ðфимим.Ðеимимримдмримрмрирор имримримримр имридм ирбднримрфмририримрфмфмим.Ðд.Д имдимримрад.Ðдимримримрмдиримримримр м.Дадимфшьмримд им.Ðдимимрн имадми','ИМРИ.ÐФИМИМ.ÐЕИМИМРИМДМРИМРМРИРОР',3,'2005-06-01 17:30:43','1234567890'), (2,'blah','464','aaa','haxpl2ilc20x00bj4tt2m5ti','11','11 g','G',3,'2005-06-02 22:43:10','1234567890'); ---disable_warnings CREATE TABLE t2 ( `msisdn` varchar(15) NOT NULL default '', `operator_id` int(11) NOT NULL default '0', `created` datetime NOT NULL default '0000-00-00 00:00:00', UNIQUE KEY `PK_user` (`msisdn`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; ---enable_warnings INSERT INTO t2 VALUES ('1234567890',2,'2005-05-24 13:53:25'); @@ -1013,10 +994,8 @@ drop table t1; # additional tests from duplicate bug#20744 MySQL return no result set names utf8; ---disable_warnings create table t1 (a varchar(30) not null primary key) engine=innodb default character set utf8 collate utf8_general_ci; ---enable_warnings insert into t1 values ('ã‚ã„ã†ãˆãŠã‹ããã‘ã“ã•ã—ã™ã›ã'); insert into t1 values ('ã•ã—ã™ã›ãã‹ããã‘ã“ã‚ã„ã†ãˆãŠ'); select a as gci1 from t1 where a like 'ã•ã—ã™ã›ãã‹ããã‘ã“ã‚ã„ã†ãˆãŠ%'; @@ -1024,10 +1003,8 @@ select a as gci2 from t1 where a like 'ã‚ã„ã†ãˆãŠã‹ããã‘ã“ã•ã—ã™ã drop table t1; set names utf8; ---disable_warnings create table t1 (a varchar(30) not null primary key) engine=innodb default character set utf8 collate utf8_unicode_ci; ---enable_warnings insert into t1 values ('ã‚ã„ã†ãˆãŠã‹ããã‘ã“ã•ã—ã™ã›ã'); insert into t1 values ('ã•ã—ã™ã›ãã‹ããã‘ã“ã‚ã„ã†ãˆãŠ'); select a as uci1 from t1 where a like 'ã•ã—ã™ã›ãã‹ããã‘ã“ã‚ã„ã†ãˆãŠ%'; @@ -1035,10 +1012,8 @@ select a as uci2 from t1 where a like 'ã‚ã„ã†ãˆãŠã‹ããã‘ã“ã•ã—ã™ã drop table t1; set names utf8; ---disable_warnings create table t1 (a varchar(30) not null primary key) engine=innodb default character set utf8 collate utf8_bin; ---enable_warnings insert into t1 values ('ã‚ã„ã†ãˆãŠã‹ããã‘ã“ã•ã—ã™ã›ã'); insert into t1 values ('ã•ã—ã™ã›ãã‹ããã‘ã“ã‚ã„ã†ãˆãŠ'); select a as bin1 from t1 where a like 'ã•ã—ã™ã›ãã‹ããã‘ã“ã‚ã„ã†ãˆãŠ%'; @@ -1363,26 +1338,22 @@ select concat(a, if(b>10, 'x' 'x', 'y' 'y')) from t1; select concat(a, if(b>10, 'x' 'æ', 'y' 'ß')) from t1; drop table t1; - # # Bug#19960: Inconsistent results when joining # InnoDB tables using partial UTF8 indexes # ---disable_warnings + CREATE TABLE t1 ( colA int(11) NOT NULL, colB varchar(255) character set utf8 NOT NULL, PRIMARY KEY (colA) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; ---enable_warnings INSERT INTO t1 (colA, colB) VALUES (1, 'foo'), (2, 'foo bar'); ---disable_warnings CREATE TABLE t2 ( colA int(11) NOT NULL, colB varchar(255) character set utf8 NOT NULL, KEY bad (colA,colB(3)) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; ---enable_warnings INSERT INTO t2 (colA, colB) VALUES (1, 'foo'),(2, 'foo bar'); SELECT * FROM t1 JOIN t2 ON t1.colA=t2.colA AND t1.colB=t2.colB WHERE t1.colA < 3; @@ -1456,3 +1427,22 @@ SELECT CONVERT(a, CHAR), CONVERT(b, CHAR) from t1 GROUP BY b; DROP TABLE t1; --echo End of 5.0 tests + + +--echo Start of 5.4 tests +# +# Bug#26474: Add Sinhala script (Sri Lanka) collation to MySQL +# +--disable_warnings +DROP TABLE IF EXISTS t1; +--enable_warnings +CREATE TABLE t1 ( + predicted_order int NOT NULL, + utf8_encoding VARCHAR(10) NOT NULL +) CHARACTER SET utf8; +INSERT INTO t1 VALUES (19, x'E0B696'), (30, x'E0B69AE0B798'), (61, x'E0B6AF'), (93, x'E0B799'), (52, x'E0B6A6'), (73, x'E0B6BBE0B78AE2808D'), (3, x'E0B686'), (56, x'E0B6AA'), (55, x'E0B6A9'), (70, x'E0B6B9'), (94, x'E0B79A'), (80, x'E0B785'), (25, x'E0B69AE0B791'), (48, x'E0B6A2'), (13, x'E0B690'), (86, x'E0B793'), (91, x'E0B79F'), (81, x'E0B786'), (79, x'E0B784'), (14, x'E0B691'), (99, x'E0B78A'), (8, x'E0B68B'), (68, x'E0B6B7'), (22, x'E0B69A'), (16, x'E0B693'), (33, x'E0B69AE0B7B3'), (38, x'E0B69AE0B79D'), (21, x'E0B683'), (11, x'E0B68E'), (77, x'E0B782'), (40, x'E0B69AE0B78A'), (101, x'E0B78AE2808DE0B6BB'), (35, x'E0B69AE0B79A'), (1, x'E0B7B4'), (9, x'E0B68C'), (96, x'E0B79C'), (6, x'E0B689'), (95, x'E0B79B'), (88, x'E0B796'), (64, x'E0B6B3'), (26, x'E0B69AE0B792'), (82, x'E0B78F'), (28, x'E0B69AE0B794'), (39, x'E0B69AE0B79E'), (97, x'E0B79D'), (2, x'E0B685'), (75, x'E0B780'), (34, x'E0B69AE0B799'), (69, x'E0B6B8'), (83, x'E0B790'), (18, x'E0B695'), (90, x'E0B7B2'), (17, x'E0B694'), (72, x'E0B6BB'), (66, x'E0B6B5'), (59, x'E0B6AD'), (44, x'E0B69E'), (15, x'E0B692'), (23, x'E0B69AE0B78F'), (65, x'E0B6B4'), (42, x'E0B69C'), (63, x'E0B6B1'), (85, x'E0B792'), (47, x'E0B6A1'), (49, x'E0B6A3'), (92, x'E0B7B3'), (78, x'E0B783'), (36, x'E0B69AE0B79B'), (4, x'E0B687'), (24, x'E0B69AE0B790'), (87, x'E0B794'), (37, x'E0B69AE0B79C'), (32, x'E0B69AE0B79F'), (29, x'E0B69AE0B796'), (43, x'E0B69D'), (62, x'E0B6B0'), (100, x'E0B78AE2808DE0B6BA'), (60, x'E0B6AE'), (45, x'E0B69F'), (12, x'E0B68F'), (46, x'E0B6A0'), (50, x'E0B6A5'), (51, x'E0B6A4'), (5, x'E0B688'), (76, x'E0B781'), (89, x'E0B798'), (74, x'E0B6BD'), (10, x'E0B68D'), (57, x'E0B6AB'), (71, x'E0B6BA'), (58, x'E0B6AC'), (27, x'E0B69AE0B793'), (54, x'E0B6A8'), (84, x'E0B791'), (31, x'E0B69AE0B7B2'), (98, x'E0B79E'), (53, x'E0B6A7'), (41, x'E0B69B'), (67, x'E0B6B6'), (7, x'E0B68A'), (20, x'E0B682'); +SELECT predicted_order, hex(utf8_encoding) FROM t1 ORDER BY utf8_encoding COLLATE utf8_sinhala_ci; +DROP TABLE t1; + +--echo End of 5.4 tests + diff --git a/mysql-test/t/delayed.test b/mysql-test/t/delayed.test index 689341391c9..d1e9bc154f5 100644 --- a/mysql-test/t/delayed.test +++ b/mysql-test/t/delayed.test @@ -247,7 +247,7 @@ DROP TABLE t1; # Bug #32676: insert delayed crash with wrong column and function specified # CREATE TABLE t1 (a INT); ---error ER_BAD_FIELD_ERROR +--error 1305 INSERT DELAYED INTO t1 SET b= b(); DROP TABLE t1; @@ -353,3 +353,38 @@ DROP TABLE t1, t2; --echo End of 5.1 tests + + + +--echo # +--echo # Bug #47274 assert in open_table on CREATE TABLE <already existing> +--echo # + +--disable_warnings +DROP TABLE IF EXISTS t1; +DROP TABLE IF EXISTS t2; +--enable_warnings + +CREATE TABLE t1 ( f1 INTEGER AUTO_INCREMENT, PRIMARY KEY (f1)); + +--echo # The following CREATE TABLEs before gave an assert. + +INSERT DELAYED t1 VALUES (4); +--error ER_TABLE_EXISTS_ERROR +CREATE TABLE t1 AS SELECT 1 AS f1; + +REPLACE DELAYED t1 VALUES (5); +--error ER_TABLE_EXISTS_ERROR +CREATE TABLE t1 AS SELECT 1 AS f1; + +INSERT DELAYED t1 VALUES (6); +--error ER_TABLE_EXISTS_ERROR +CREATE TABLE t1 (f1 INTEGER); + +CREATE TABLE t2 (f1 INTEGER); +INSERT DELAYED t1 VALUES (7); +--error ER_TABLE_EXISTS_ERROR +CREATE TABLE t1 LIKE t2; + +DROP TABLE t2; +DROP TABLE t1; diff --git a/mysql-test/t/drop-no_root.test b/mysql-test/t/drop-no_root.test new file mode 100644 index 00000000000..8fb5b3f74a8 --- /dev/null +++ b/mysql-test/t/drop-no_root.test @@ -0,0 +1,85 @@ +# This test uses chmod, can't be run with root permissions +--source include/not_as_root.inc + +########################################################################### + +--echo +--echo # -- +--echo # -- Bug#26704: Failing DROP DATABASE brings mysql-client out of sync. +--echo # -- + +--echo +--disable_warnings +DROP DATABASE IF EXISTS mysql_test; +--enable_warnings + +--echo +CREATE DATABASE mysql_test; +CREATE TABLE mysql_test.t1(c INT); + +use mysql_test; + +let $MYSQLD_DATADIR= `select @@datadir`; + +--echo +--echo chmod 000 mysql_test/t1.frm +--chmod 0000 $MYSQLD_DATADIR/mysql_test/t1.frm + +# NOTE: For the DROP DATABASE below we need: +# - disable result log because ER_DB_DROP_RMDIR contains errno, which can be +# different on different platforms. +# - expect different error codes, because Windows and UNIX behaves +# differently (see below). +# +# NOTE: Windows and UNIX behaves differently in this test case: +# +# - on UNIX when t1.frm is chmoded to 000, it is perfectly deleted +# by the first DROP DATABASE, but some other files (t1.MYI and t1.MYD) left +# in the directory. So, we have to explicitly removes them before the +# second DROP DATABASE. +# +# - on Windows when t1.frm is chmoded to 000, it is not deleted by the first +# DROP DATABASE, but all other files in the database directory are deleted. +# Thus, we have to change the t1.frm permissions again and delete it +# explicitly before the second DROP DATABASE. +# +# All those differences do not really matter for the idea of this test case: +# checking that if DROP DATABASE failed, the client is Ok. + +--echo +--disable_result_log +--error ER_DB_DROP_RMDIR,6 +DROP DATABASE mysql_test; +--enable_result_log + +--echo +SELECT DATABASE(); + +# Remove t1.MYI and t1.MYD. On UNIX it should succeed. On Windows, it fails. +--echo +--echo rm -f mysql_test/t1.MYD mysql_test/t1.MYI +--error 0, 1 +--remove_file $MYSQLD_DATADIR/mysql_test/t1.MYD +--error 0, 1 +--remove_file $MYSQLD_DATADIR/mysql_test/t1.MYI + +# Make t1.frm removable: fail on UNIX, succeed on Windows. +--echo chmod 666 mysql_test/t1.frm +--error 0, 1 +--chmod 0666 $MYSQLD_DATADIR/mysql_test/t1.frm + +# Remove t1.frm: fail on UNIX, succeed on Windows. +--echo rm -f mysql_test/t1.frm +--error 0, 1 +--remove_file $MYSQLD_DATADIR/mysql_test/t1.frm + +--echo +DROP DATABASE mysql_test; + +--echo +use test; + +--echo +--echo # -- End of Bug#26704. + +########################################################################### diff --git a/mysql-test/t/drop.test b/mysql-test/t/drop.test index bb4dd3e11f9..6be4ea25007 100644 --- a/mysql-test/t/drop.test +++ b/mysql-test/t/drop.test @@ -202,3 +202,24 @@ use test; drop database mysqltestbug26703; --echo End of 5.1 tests + +########################################################################### + +--echo +--echo # -- +--echo # -- Bug#37431 (DROP TABLE does not report errors correctly). +--echo # -- + +--disable_warnings +DROP TABLE IF EXISTS t1; +--enable_warnings + +--error ER_BAD_TABLE_ERROR +DROP TABLE t1; + +SHOW WARNINGS; + +--echo +--echo # -- +--echo # -- End of Bug#37431. +--echo # -- diff --git a/mysql-test/t/drop_debug.test b/mysql-test/t/drop_debug.test new file mode 100644 index 00000000000..97ee5847d0a --- /dev/null +++ b/mysql-test/t/drop_debug.test @@ -0,0 +1,35 @@ +# +# DROP-related tests which execution requires debug server. +# +--source include/have_debug.inc + +########################################################################### +--echo +--echo # -- +--echo # -- Bug#43138: DROP DATABASE failure does not clean up message list. +--echo # -- +--echo + +--disable_warnings +DROP DATABASE IF EXISTS mysql_test; +--enable_warnings + +--echo +CREATE DATABASE mysql_test; +CREATE TABLE mysql_test.t1(a INT); + +--echo +SET SESSION DEBUG = "+d,bug43138"; + +--echo +DROP DATABASE mysql_test; + +--echo +SET SESSION DEBUG = "-d,bug43138"; + +--echo +--echo # -- +--echo # -- End of Bug#43138. +--echo # -- + +########################################################################### diff --git a/mysql-test/t/errors.test b/mysql-test/t/errors.test index 89579ec1739..820766c3a78 100644 --- a/mysql-test/t/errors.test +++ b/mysql-test/t/errors.test @@ -67,3 +67,38 @@ SHOW ERRORS; INSERT INTO t1 SELECT b FROM t1; DROP TABLE t1; # End of 5.0 tests + +# +# testing the value encoding in the error messages of set_var +# +SET NAMES utf8; +--error ER_WRONG_VALUE_FOR_VAR +SET sql_quote_show_create= _binary x'5452C39C45'; +--error ER_WRONG_VALUE_FOR_VAR +SET sql_quote_show_create= _utf8 x'5452C39C45'; +--error ER_WRONG_VALUE_FOR_VAR +SET sql_quote_show_create=_latin1 x'5452DC45'; +--error ER_WRONG_VALUE_FOR_VAR +SET sql_quote_show_create='TRÃœE'; +--error ER_WRONG_VALUE_FOR_VAR +SET sql_quote_show_create=TRÃœE; + +SET NAMES latin1; +--error ER_WRONG_VALUE_FOR_VAR +SET sql_quote_show_create= _binary x'5452C39C45'; +--error ER_WRONG_VALUE_FOR_VAR +SET sql_quote_show_create= _utf8 x'5452C39C45'; +--error ER_WRONG_VALUE_FOR_VAR +SET sql_quote_show_create=_latin1 x'5452DC45'; +--error ER_WRONG_VALUE_FOR_VAR +SET sql_quote_show_create='TR.E'; +--error ER_WRONG_VALUE_FOR_VAR +SET sql_quote_show_create=TR.E; + +SET NAMES binary; +--error ER_WRONG_VALUE_FOR_VAR +SET sql_quote_show_create= _binary x'5452C39C45'; +--error ER_WRONG_VALUE_FOR_VAR +SET sql_quote_show_create= _utf8 x'5452C39C45'; +--error ER_WRONG_VALUE_FOR_VAR +SET sql_quote_show_create=_latin1 x'5452DC45'; diff --git a/mysql-test/t/explain.test b/mysql-test/t/explain.test index 77b49a8b1a5..7e2a9bdc811 100644 --- a/mysql-test/t/explain.test +++ b/mysql-test/t/explain.test @@ -187,4 +187,16 @@ SET SESSION sql_mode=@old_sql_mode; DROP TABLE t1; +--echo # +--echo # Bug#30302: Tables that were optimized away are printed in the +--echo # EXPLAIN EXTENDED warning. +--echo # +create table t1(f1 int); +create table t2(f2 int); +insert into t1 values(1); +insert into t2 values(1),(2); +explain extended select * from t1 where f1=1; +explain extended select * from t1 join t2 on f1=f2 where f1=1; +drop table t1,t2; + --echo End of 5.1 tests. diff --git a/mysql-test/t/fulltext.test b/mysql-test/t/fulltext.test index 4537559509d..fb060cc83dd 100644 --- a/mysql-test/t/fulltext.test +++ b/mysql-test/t/fulltext.test @@ -420,6 +420,15 @@ DROP TABLE t1; # End of 4.1 tests # +# bug#34374 - mysql generates incorrect warning +# +create table t1(a text,b date,fulltext index(a))engine=myisam; +insert into t1 set a='water',b='2008-08-04'; +select 1 from t1 where match(a) against ('water' in boolean mode) and b>='2008-08-01'; +drop table t1; +show warnings; + +# # BUG#38842 - Fix for 25951 seems incorrect # CREATE TABLE t1 (a VARCHAR(255), b INT, FULLTEXT(a), KEY(b)); diff --git a/mysql-test/t/func_gconcat.test b/mysql-test/t/func_gconcat.test index e92f3e96303..71d3d5a140b 100644 --- a/mysql-test/t/func_gconcat.test +++ b/mysql-test/t/func_gconcat.test @@ -694,3 +694,35 @@ SELECT 1 FROM t1 WHERE t1.a NOT IN DROP TABLE t1, t2; --echo End of 5.0 tests + +# +# Bug#36785: Wrong error message when group_concat() exceeds max length +# + +--disable_warnings +DROP TABLE IF EXISTS t1, t2; +--enable_warnings + +CREATE TABLE t1 (a VARCHAR(6), b INT); +CREATE TABLE t2 (a VARCHAR(6), b INT); + +INSERT INTO t1 VALUES ('111111', 1); +INSERT INTO t1 VALUES ('222222', 2); +INSERT INTO t1 VALUES ('333333', 3); +INSERT INTO t1 VALUES ('444444', 4); +INSERT INTO t1 VALUES ('555555', 5); + +SET group_concat_max_len = 5; +SET @old_sql_mode = @@sql_mode, @@sql_mode = 'traditional'; + +SELECT GROUP_CONCAT(a), b FROM t1 GROUP BY b LIMIT 3; +--error ER_CUT_VALUE_GROUP_CONCAT +INSERT INTO t2 SELECT GROUP_CONCAT(a), b FROM t1 GROUP BY b; +UPDATE t1 SET a = '11111' WHERE b = 1; +UPDATE t1 SET a = '22222' WHERE b = 2; +--error ER_CUT_VALUE_GROUP_CONCAT +INSERT INTO t2 SELECT GROUP_CONCAT(a), b FROM t1 GROUP BY b; + +SET group_concat_max_len = DEFAULT; +SET @@sql_mode = @old_sql_mode; +DROP TABLE t1, t2; diff --git a/mysql-test/t/func_str.test b/mysql-test/t/func_str.test index 66b9eabd385..032c9ade643 100644 --- a/mysql-test/t/func_str.test +++ b/mysql-test/t/func_str.test @@ -1318,3 +1318,45 @@ insert into t1 values (-1),(null); explain select 1 as a from t1,(select decode(f1,f1) as b from t1) a; explain select 1 as a from t1,(select encode(f1,f1) as b from t1) a; drop table t1; + + + +--echo Start of 5.4 tests +# +# WL#4584 Internationalized number format +# +SELECT format(12345678901234567890.123, 3); +SELECT format(12345678901234567890.123, 3, NULL); +SELECT format(12345678901234567890.123, 3, 'ar_AE'); +SELECT format(12345678901234567890.123, 3, 'ar_SA'); +SELECT format(12345678901234567890.123, 3, 'be_BY'); +SELECT format(12345678901234567890.123, 3, 'de_DE'); +SELECT format(12345678901234567890.123, 3, 'en_IN'); +SELECT format(12345678901234567890.123, 3, 'en_US'); +SELECT format(12345678901234567890.123, 3, 'it_CH'); +SELECT format(12345678901234567890.123, 3, 'ru_RU'); +SELECT format(12345678901234567890.123, 3, 'ta_IN'); + +CREATE TABLE t1 (fmt CHAR(5) NOT NULL); +INSERT INTO t1 VALUES ('ar_AE'); +INSERT INTO t1 VALUES ('ar_SA'); +INSERT INTO t1 VALUES ('be_BY'); +INSERT INTO t1 VALUES ('de_DE'); +INSERT INTO t1 VALUES ('en_IN'); +INSERT INTO t1 VALUES ('en_US'); +INSERT INTO t1 VALUES ('it_CH'); +INSERT INTO t1 VALUES ('ru_RU'); +INSERT INTO t1 VALUES ('ta_IN'); +SELECT fmt, format(12345678901234567890.123, 3, fmt) FROM t1 ORDER BY fmt; +SELECT fmt, format(12345678901234567890.123, 0, fmt) FROM t1 ORDER BY fmt; +SELECT fmt, format(12345678901234567890, 3, fmt) FROM t1 ORDER BY fmt; +SELECT fmt, format(-12345678901234567890, 3, fmt) FROM t1 ORDER BY fmt; +SELECT fmt, format(-02345678901234567890, 3, fmt) FROM t1 ORDER BY fmt; +SELECT fmt, format(-00345678901234567890, 3, fmt) FROM t1 ORDER BY fmt; +SELECT fmt, format(-00045678901234567890, 3, fmt) FROM t1 ORDER BY fmt; +DROP TABLE t1; + +SELECT format(123, 1, 'Non-existent-locale'); + +--echo End of 5.4 tests + diff --git a/mysql-test/t/grant.test b/mysql-test/t/grant.test index bcd393bd6ab..bda48fb9ecf 100644 --- a/mysql-test/t/grant.test +++ b/mysql-test/t/grant.test @@ -1525,5 +1525,357 @@ DROP USER 'user1'@'localhost'; DROP USER 'user2'; DROP DATABASE db1; + +--echo # +--echo # Bug #25863 No database selected error, but documentation +--echo # says * for global allowed +--echo # + +connect(conn1,localhost,root,,*NO-ONE*); + +--error ER_NO_DB_ERROR +GRANT ALL ON * TO mysqltest_1; + +GRANT ALL ON *.* TO mysqltest_1; +SHOW GRANTS FOR mysqltest_1; +DROP USER mysqltest_1; + +USE test; + +GRANT ALL ON * TO mysqltest_1; +SHOW GRANTS FOR mysqltest_1; +DROP USER mysqltest_1; + +GRANT ALL ON *.* TO mysqltest_1; +SHOW GRANTS FOR mysqltest_1; +DROP USER mysqltest_1; + +connection default; +disconnect conn1; + + # Wait till we reached the initial number of concurrent sessions --source include/wait_until_count_sessions.inc + +--echo ######################################################################### +--echo # +--echo # Bug#38347: ALTER ROUTINE privilege allows SHOW CREATE TABLE. +--echo # +--echo ######################################################################### + +--echo +--echo # -- +--echo # -- Prepare the environment. +--echo # -- + +DELETE FROM mysql.user WHERE User LIKE 'mysqltest_%'; +DELETE FROM mysql.db WHERE User LIKE 'mysqltest_%'; +DELETE FROM mysql.tables_priv WHERE User LIKE 'mysqltest_%'; +DELETE FROM mysql.columns_priv WHERE User LIKE 'mysqltest_%'; +FLUSH PRIVILEGES; + +--disable_warnings +DROP DATABASE IF EXISTS mysqltest_db1; +--enable_warnings + +CREATE DATABASE mysqltest_db1; + +CREATE TABLE mysqltest_db1.t1(a INT); + +--echo +--echo # -- +--echo # -- Check that global privileges don't allow SHOW CREATE TABLE. +--echo # -- + +GRANT EVENT ON mysqltest_db1.* TO mysqltest_u1@localhost; +GRANT CREATE TEMPORARY TABLES ON mysqltest_db1.* TO mysqltest_u1@localhost; +GRANT LOCK TABLES ON mysqltest_db1.* TO mysqltest_u1@localhost; +GRANT ALTER ROUTINE ON mysqltest_db1.* TO mysqltest_u1@localhost; +GRANT CREATE ROUTINE ON mysqltest_db1.* TO mysqltest_u1@localhost; +GRANT EXECUTE ON mysqltest_db1.* TO mysqltest_u1@localhost; + +GRANT FILE ON *.* TO mysqltest_u1@localhost; +GRANT CREATE USER ON *.* TO mysqltest_u1@localhost; +GRANT PROCESS ON *.* TO mysqltest_u1@localhost; +GRANT RELOAD ON *.* TO mysqltest_u1@localhost; +GRANT REPLICATION CLIENT ON *.* TO mysqltest_u1@localhost; +GRANT REPLICATION SLAVE ON *.* TO mysqltest_u1@localhost; +GRANT SHOW DATABASES ON *.* TO mysqltest_u1@localhost; +GRANT SHUTDOWN ON *.* TO mysqltest_u1@localhost; +GRANT USAGE ON *.* TO mysqltest_u1@localhost; + +--echo +SHOW GRANTS FOR mysqltest_u1@localhost; + +--echo +--echo # connection: con1 (mysqltest_u1@mysqltest_db1) +--connect (con1,localhost,mysqltest_u1,,mysqltest_db1) +--connection con1 + +--echo +--error ER_TABLEACCESS_DENIED_ERROR +SHOW CREATE TABLE t1; + +--echo +--echo # connection: default +--connection default + +--disconnect con1 + +--echo +REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost; +SHOW GRANTS FOR mysqltest_u1@localhost; + +--echo +--echo # -- +--echo # -- Check that global SELECT allows SHOW CREATE TABLE. +--echo # -- + +--echo +GRANT SELECT ON mysqltest_db1.* TO mysqltest_u1@localhost; + +--source include/bug38347.inc + +--echo +--echo # -- +--echo # -- Check that global INSERT allows SHOW CREATE TABLE. +--echo # -- + +--echo +GRANT INSERT ON mysqltest_db1.* TO mysqltest_u1@localhost; + +--source include/bug38347.inc + +--echo +--echo # -- +--echo # -- Check that global UPDATE allows SHOW CREATE TABLE. +--echo # -- + +--echo +GRANT UPDATE ON mysqltest_db1.* TO mysqltest_u1@localhost; + +--source include/bug38347.inc + +--echo +--echo # -- +--echo # -- Check that global DELETE allows SHOW CREATE TABLE. +--echo # -- + +--echo +GRANT DELETE ON mysqltest_db1.* TO mysqltest_u1@localhost; + +--source include/bug38347.inc + +--echo +--echo # -- +--echo # -- Check that global CREATE allows SHOW CREATE TABLE. +--echo # -- + +--echo +GRANT CREATE ON mysqltest_db1.* TO mysqltest_u1@localhost; + +--source include/bug38347.inc + +--echo +--echo # -- +--echo # -- Check that global DROP allows SHOW CREATE TABLE. +--echo # -- + +--echo +GRANT DROP ON mysqltest_db1.* TO mysqltest_u1@localhost; + +--source include/bug38347.inc + +--echo +--echo # -- +--echo # -- Check that global ALTER allows SHOW CREATE TABLE. +--echo # -- + +--echo +GRANT ALTER ON mysqltest_db1.* TO mysqltest_u1@localhost; + +--source include/bug38347.inc + +--echo +--echo # -- +--echo # -- Check that global INDEX allows SHOW CREATE TABLE. +--echo # -- + +--echo +GRANT INDEX ON mysqltest_db1.* TO mysqltest_u1@localhost; + +--source include/bug38347.inc + +--echo +--echo # -- +--echo # -- Check that global REFERENCES allows SHOW CREATE TABLE. +--echo # -- + +--echo +GRANT REFERENCES ON mysqltest_db1.* TO mysqltest_u1@localhost; + +--source include/bug38347.inc + +--echo +--echo # -- +--echo # -- Check that global GRANT OPTION allows SHOW CREATE TABLE. +--echo # -- + +--echo +GRANT GRANT OPTION ON mysqltest_db1.* TO mysqltest_u1@localhost; + +--source include/bug38347.inc + +--echo +--echo # -- +--echo # -- Check that global CREATE VIEW allows SHOW CREATE TABLE. +--echo # -- + +--echo +GRANT CREATE VIEW ON mysqltest_db1.* TO mysqltest_u1@localhost; + +--source include/bug38347.inc + +--echo +--echo # -- +--echo # -- Check that global SHOW VIEW allows SHOW CREATE TABLE. +--echo # -- + +--echo +GRANT SHOW VIEW ON mysqltest_db1.* TO mysqltest_u1@localhost; + +--source include/bug38347.inc + +--echo +--echo # -- +--echo # -- Check that table-level SELECT allows SHOW CREATE TABLE. +--echo # -- + +--echo +GRANT SELECT ON mysqltest_db1.t1 TO mysqltest_u1@localhost; + +--source include/bug38347.inc + +--echo +--echo # -- +--echo # -- Check that table-level INSERT allows SHOW CREATE TABLE. +--echo # -- + +--echo +GRANT INSERT ON mysqltest_db1.t1 TO mysqltest_u1@localhost; + +--source include/bug38347.inc + +--echo +--echo # -- +--echo # -- Check that table-level UPDATE allows SHOW CREATE TABLE. +--echo # -- + +--echo +GRANT UPDATE ON mysqltest_db1.t1 TO mysqltest_u1@localhost; + +--source include/bug38347.inc + +--echo +--echo # -- +--echo # -- Check that table-level DELETE allows SHOW CREATE TABLE. +--echo # -- + +--echo +GRANT DELETE ON mysqltest_db1.t1 TO mysqltest_u1@localhost; + +--source include/bug38347.inc + +--echo +--echo # -- +--echo # -- Check that table-level CREATE allows SHOW CREATE TABLE. +--echo # -- + +--echo +GRANT CREATE ON mysqltest_db1.t1 TO mysqltest_u1@localhost; + +--source include/bug38347.inc + +--echo +--echo # -- +--echo # -- Check that table-level DROP allows SHOW CREATE TABLE. +--echo # -- + +--echo +GRANT DROP ON mysqltest_db1.t1 TO mysqltest_u1@localhost; + +--source include/bug38347.inc + +--echo +--echo # -- +--echo # -- Check that table-level ALTER allows SHOW CREATE TABLE. +--echo # -- + +--echo +GRANT ALTER ON mysqltest_db1.t1 TO mysqltest_u1@localhost; + +--source include/bug38347.inc + +--echo +--echo # -- +--echo # -- Check that table-level INDEX allows SHOW CREATE TABLE. +--echo # -- + +--echo +GRANT INDEX ON mysqltest_db1.t1 TO mysqltest_u1@localhost; + +--source include/bug38347.inc + +--echo +--echo # -- +--echo # -- Check that table-level REFERENCES allows SHOW CREATE TABLE. +--echo # -- + +--echo +GRANT REFERENCES ON mysqltest_db1.t1 TO mysqltest_u1@localhost; + +--source include/bug38347.inc + +--echo +--echo # -- +--echo # -- Check that table-level GRANT OPTION allows SHOW CREATE TABLE. +--echo # -- + +--echo +GRANT GRANT OPTION ON mysqltest_db1.t1 TO mysqltest_u1@localhost; + +--source include/bug38347.inc + +--echo +--echo # -- +--echo # -- Check that table-level CREATE VIEW allows SHOW CREATE TABLE. +--echo # -- + +--echo +GRANT CREATE VIEW ON mysqltest_db1.t1 TO mysqltest_u1@localhost; + +--source include/bug38347.inc + +--echo +--echo # -- +--echo # -- Check that table-level SHOW VIEW allows SHOW CREATE TABLE. +--echo # -- + +--echo +GRANT SHOW VIEW ON mysqltest_db1.t1 TO mysqltest_u1@localhost; + +--source include/bug38347.inc + +--echo +--echo # -- +--echo # -- Cleanup. +--echo # -- + +--echo +DROP DATABASE mysqltest_db1; + +DROP USER mysqltest_u1@localhost; + +--echo +--echo # End of Bug#38347. +--echo diff --git a/mysql-test/t/grant4.test b/mysql-test/t/grant4.test new file mode 100644 index 00000000000..f3e551cd623 --- /dev/null +++ b/mysql-test/t/grant4.test @@ -0,0 +1,146 @@ +--source include/not_embedded.inc + +# Setup database, tables and user accounts +--disable_warnings +drop database if exists mysqltest_db1; +--enable_warnings +create database mysqltest_db1; +use mysqltest_db1; +create table t_column_priv_only (a int, b int); +create table t_select_priv like t_column_priv_only; +create table t_no_priv like t_column_priv_only; +grant all privileges on test.* to mysqltest_u1@localhost; +grant insert (a) on mysqltest_db1.t_column_priv_only to mysqltest_u1@localhost; +grant select on mysqltest_db1.t_select_priv to mysqltest_u1@localhost; + +--echo ** Connect as restricted user mysqltest_u1. +--echo +connect (con1,localhost,mysqltest_u1,,); +connection con1; + +######################################################################## +--echo ** Test column level privileges only. No SELECT privileges on the table. +--echo ** INSERT INTO ... VALUES ... +--echo ** Attempting to insert values to a table with only column privileges +--echo ** should work. +insert into mysqltest_db1.t_column_priv_only (a) VALUES (1); +--echo + +######################################################################### +--echo ** SHOW COLUMNS +--echo ** Should succeed because we have privileges (any) on at least one of the columns. +select column_name as 'Field',column_type as 'Type',is_nullable as 'Null',column_key as 'Key',column_default as 'Default',extra as 'Extra' from information_schema.columns where table_schema='mysqltest_db1' and table_name='t_column_priv_only'; +show columns from mysqltest_db1.t_column_priv_only; +######################################################################### +--echo ** SHOW COLUMNS +--echo ** Should fail because there are no privileges on any column combination. +--error 1142 +show columns from mysqltest_db1.t_no_priv; +--echo ** However, select from I_S.COLUMNS will succeed but not show anything: +select column_name as 'Field',column_type as 'Type',is_nullable as 'Null',column_key as 'Key',column_default as 'Default',extra as 'Extra' from information_schema.columns where table_schema='mysqltest_db1' and table_name='t_no_priv'; +--echo +######################################################################### +--echo ** CREATE TABLE ... LIKE ... require SELECT privleges and will fail. +--error 1142 +create table test.t_no_priv like mysqltest_db1.column_priv_only; +--echo +######################################################################### +--echo ** Just to be sure... SELECT also fails. +--error 1142 +select * from mysqltest_db1.t_column_priv_only; +--echo +######################################################################### +--echo ** SHOW CREATE TABLE ... require any privileges on all columns (the entire table). +--echo ** First we try and fail on a table with only one column privilege. +--error 1142 +show create table mysqltest_db1.t_column_priv_only; +--echo +######################################################################### +--echo ** Now we do the same on a table with SELECT privileges. +--echo +######################################################################### +--echo ** SHOW COLUMNS +--echo ** Success because we got some privileges on the table (SELECT_ACL) +show columns from mysqltest_db1.t_select_priv; +--echo +######################################################################### +--echo ** CREATE TABLE ... LIKE ... require SELECT privleges and will SUCCEED. +--disable_warnings +drop table if exists test.t_duplicated; +--enable_warnings +create table test.t_duplicated like mysqltest_db1.t_select_priv; +drop table test.t_duplicated; +--echo +######################################################################### +--echo ** SHOW CREATE TABLE will succeed because we have a privilege on all columns in the table (table-level privilege). +show create table mysqltest_db1.t_select_priv; +--echo +######################################################################### +--echo ** SHOW CREATE TABLE will fail if there is no grants at all: +--error 1142 +show create table mysqltest_db1.t_no_priv; +--echo + +connection default; + +# +# SHOW INDEX +# +use mysqltest_db1; +CREATE TABLE t5 (s1 INT); +CREATE INDEX i ON t5 (s1); +CREATE TABLE t6 (s1 INT, s2 INT); +CREATE VIEW v5 AS SELECT * FROM t5; +CREATE VIEW v6 AS SELECT * FROM t6; +CREATE VIEW v2 AS SELECT * FROM t_select_priv; +CREATE VIEW v3 AS SELECT * FROM t_select_priv; +CREATE INDEX i ON t6 (s1); +GRANT UPDATE (s2) ON t6 to mysqltest_u1@localhost; +GRANT UPDATE (s2) ON v6 to mysqltest_u1@localhost; +GRANT SHOW VIEW ON v2 to mysqltest_u1@localhost; +GRANT SHOW VIEW, SELECT ON v3 to mysqltest_u1@localhost; + +connection con1; +use mysqltest_db1; +--echo ** Connect as restricted user mysqltest_u1. +--echo ** SELECT FROM INFORMATION_SCHEMA.STATISTICS will succeed because any privileges will do (authentication is enough). +# +# this result is wrong. reported as bug#34104 +# +SELECT * FROM INFORMATION_SCHEMA.STATISTICS WHERE table_name='t5'; +# +# Bug27145 EXTRA_ACL trouble +# +--echo ** SHOW INDEX FROM t5 will fail because we don't have any privileges on any column combination. +--error 1142 +SHOW INDEX FROM t5; +--echo ** SHOW INDEX FROM t6 will succeed because there exist a privilege on a column combination on t6. +SHOW INDEX FROM t6; + +# CHECK TABLE +--echo ** CHECK TABLE requires any privilege on any column combination and should succeed for t6: +CHECK TABLE t6; +--echo ** With no privileges access is naturally denied: +--error 1142 +CHECK TABLE t5; + +# CHECKSUM +--echo ** CHECKSUM TABLE requires SELECT privileges on the table. The following should fail: +--error 1142 +CHECKSUM TABLE t6; +--echo ** And this should work: +CHECKSUM TABLE t_select_priv; + +# SHOW CREATE VIEW +--error 1142 +SHOW CREATE VIEW v5; +--error 1142 +SHOW CREATE VIEW v6; +--error 1142 +SHOW CREATE VIEW v2; +SHOW CREATE VIEW v3; + +connection default; +disconnect con1; +drop database mysqltest_db1; +drop user mysqltest_u1@localhost; diff --git a/mysql-test/t/group_min_max.test b/mysql-test/t/group_min_max.test index f623df372fa..0e6fef9b855 100644 --- a/mysql-test/t/group_min_max.test +++ b/mysql-test/t/group_min_max.test @@ -570,13 +570,13 @@ explain select count(distinct a1,a2,b) from t1 where (a2 >= 'b') and (b = 'a'); explain select count(distinct a1,a2,b,c) from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121'); explain extended select count(distinct a1,a2,b) from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c'); explain select count(distinct b) from t1 where (a2 >= 'b') and (b = 'a'); -explain extended select ord(a1) + count(distinct a1,a2,b) from t1 where (a1 > 'a') and (a2 > 'a'); +explain extended select 98 + count(distinct a1,a2,b) from t1 where (a1 > 'a') and (a2 > 'a'); select count(distinct a1,a2,b) from t1 where (a2 >= 'b') and (b = 'a'); select count(distinct a1,a2,b,c) from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121'); select count(distinct a1,a2,b) from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c'); select count(distinct b) from t1 where (a2 >= 'b') and (b = 'a'); -select ord(a1) + count(distinct a1,a2,b) from t1 where (a1 > 'a') and (a2 > 'a'); +select 98 + count(distinct a1,a2,b) from t1 where (a1 > 'a') and (a2 > 'a'); # # Queries with expressions in the select clause @@ -1045,3 +1045,124 @@ SELECT a, MAX(b) FROM t WHERE b GROUP BY a; DROP TABLE t; --echo End of 5.1 tests + + +--echo # +--echo # WL#3220 (Loose index scan for COUNT DISTINCT) +--echo # + +CREATE TABLE t1 (a INT, b INT, c INT, KEY (a,b)); +INSERT INTO t1 VALUES (1,1,1), (1,2,1), (1,3,1), (1,4,1); +INSERT INTO t1 SELECT a, b + 4, 1 FROM t1; +INSERT INTO t1 SELECT a + 1, b, 1 FROM t1; +CREATE TABLE t2 (a INT, b INT, c INT, d INT, e INT, f INT, KEY (a,b,c)); +INSERT INTO t2 VALUES (1,1,1,1,1,1), (1,2,1,1,1,1), (1,3,1,1,1,1), + (1,4,1,1,1,1); +INSERT INTO t2 SELECT a, b + 4, c,d,e,f FROM t2; +INSERT INTO t2 SELECT a + 1, b, c,d,e,f FROM t2; + +EXPLAIN SELECT COUNT(DISTINCT a) FROM t1; +SELECT COUNT(DISTINCT a) FROM t1; + +EXPLAIN SELECT COUNT(DISTINCT a,b) FROM t1; +SELECT COUNT(DISTINCT a,b) FROM t1; + +EXPLAIN SELECT COUNT(DISTINCT b,a) FROM t1; +SELECT COUNT(DISTINCT b,a) FROM t1; + +EXPLAIN SELECT COUNT(DISTINCT b) FROM t1; +SELECT COUNT(DISTINCT b) FROM t1; + +EXPLAIN SELECT COUNT(DISTINCT a) FROM t1 GROUP BY a; +SELECT COUNT(DISTINCT a) FROM t1 GROUP BY a; + +EXPLAIN SELECT COUNT(DISTINCT b) FROM t1 GROUP BY a; +SELECT COUNT(DISTINCT b) FROM t1 GROUP BY a; + +EXPLAIN SELECT COUNT(DISTINCT a) FROM t1 GROUP BY b; +SELECT COUNT(DISTINCT a) FROM t1 GROUP BY b; + +EXPLAIN SELECT DISTINCT COUNT(DISTINCT a) FROM t1; +SELECT DISTINCT COUNT(DISTINCT a) FROM t1; + +EXPLAIN SELECT COUNT(DISTINCT a, b + 0) FROM t1; +SELECT COUNT(DISTINCT a, b + 0) FROM t1; + +EXPLAIN SELECT COUNT(DISTINCT a) FROM t1 HAVING COUNT(DISTINCT b) < 10; +SELECT COUNT(DISTINCT a) FROM t1 HAVING COUNT(DISTINCT b) < 10; + +EXPLAIN SELECT COUNT(DISTINCT a) FROM t1 HAVING COUNT(DISTINCT c) < 10; +SELECT COUNT(DISTINCT a) FROM t1 HAVING COUNT(DISTINCT c) < 10; + +EXPLAIN SELECT 1 FROM t1 HAVING COUNT(DISTINCT a) < 10; +SELECT 1 FROM t1 HAVING COUNT(DISTINCT a) < 10; + +EXPLAIN SELECT 1 FROM t1 GROUP BY a HAVING COUNT(DISTINCT b) > 1; +SELECT 1 FROM t1 GROUP BY a HAVING COUNT(DISTINCT b) > 1; + +EXPLAIN SELECT COUNT(DISTINCT t1_1.a) FROM t1 t1_1, t1 t1_2 GROUP BY t1_1.a; +SELECT COUNT(DISTINCT t1_1.a) FROM t1 t1_1, t1 t1_2 GROUP BY t1_1.a; + +EXPLAIN SELECT COUNT(DISTINCT a), 12 FROM t1; +SELECT COUNT(DISTINCT a), 12 FROM t1; + +EXPLAIN SELECT COUNT(DISTINCT a, b, c) FROM t2; +SELECT COUNT(DISTINCT a, b, c) FROM t2; + +EXPLAIN SELECT COUNT(DISTINCT a), SUM(DISTINCT a), AVG(DISTINCT a) FROM t2; +SELECT COUNT(DISTINCT a), SUM(DISTINCT a), AVG(DISTINCT a) FROM t2; + +EXPLAIN SELECT COUNT(DISTINCT a), SUM(DISTINCT a), AVG(DISTINCT f) FROM t2; +SELECT COUNT(DISTINCT a), SUM(DISTINCT a), AVG(DISTINCT f) FROM t2; + +EXPLAIN SELECT COUNT(DISTINCT a, b), COUNT(DISTINCT b, a) FROM t2; +SELECT COUNT(DISTINCT a, b), COUNT(DISTINCT b, a) FROM t2; + +EXPLAIN SELECT COUNT(DISTINCT a, b), COUNT(DISTINCT b, f) FROM t2; +SELECT COUNT(DISTINCT a, b), COUNT(DISTINCT b, f) FROM t2; + +EXPLAIN SELECT COUNT(DISTINCT a, b), COUNT(DISTINCT b, d) FROM t2; +SELECT COUNT(DISTINCT a, b), COUNT(DISTINCT b, d) FROM t2; + +EXPLAIN SELECT a, c, COUNT(DISTINCT c, a, b) FROM t2 GROUP BY a, b, c; +SELECT a, c, COUNT(DISTINCT c, a, b) FROM t2 GROUP BY a, b, c; + +EXPLAIN SELECT COUNT(DISTINCT c, a, b) FROM t2 + WHERE a > 5 AND b BETWEEN 10 AND 20 GROUP BY a, b, c; +SELECT COUNT(DISTINCT c, a, b) FROM t2 + WHERE a > 5 AND b BETWEEN 10 AND 20 GROUP BY a, b, c; + +EXPLAIN SELECT COUNT(DISTINCT b), SUM(DISTINCT b) FROM t2 WHERE a = 5 + GROUP BY b; +SELECT COUNT(DISTINCT b), SUM(DISTINCT b) FROM t2 WHERE a = 5 + GROUP BY b; + +EXPLAIN SELECT a, COUNT(DISTINCT b), SUM(DISTINCT b) FROM t2 GROUP BY a; +SELECT a, COUNT(DISTINCT b), SUM(DISTINCT b) FROM t2 GROUP BY a; + +EXPLAIN SELECT COUNT(DISTINCT b), SUM(DISTINCT b) FROM t2 GROUP BY a; +SELECT COUNT(DISTINCT b), SUM(DISTINCT b) FROM t2 GROUP BY a; + +EXPLAIN SELECT COUNT(DISTINCT a, b) FROM t2 WHERE c = 13 AND d = 42; +SELECT COUNT(DISTINCT a, b) FROM t2 WHERE c = 13 AND d = 42; + +EXPLAIN SELECT a, COUNT(DISTINCT a), SUM(DISTINCT a) FROM t2 + WHERE b = 13 AND c = 42 GROUP BY a; +SELECT a, COUNT(DISTINCT a), SUM(DISTINCT a) FROM t2 + WHERE b = 13 AND c = 42 GROUP BY a; + +EXPLAIN SELECT COUNT(DISTINCT a, b), SUM(DISTINCT a) FROM t2 WHERE b = 42; +SELECT COUNT(DISTINCT a, b), SUM(DISTINCT a) FROM t2 WHERE b = 42; + +EXPLAIN SELECT SUM(DISTINCT a), MAX(b) FROM t2 GROUP BY a; +SELECT SUM(DISTINCT a), MAX(b) FROM t2 GROUP BY a; + +EXPLAIN SELECT 42 * (a + c + COUNT(DISTINCT c, a, b)) FROM t2 GROUP BY a, b, c; +SELECT 42 * (a + c + COUNT(DISTINCT c, a, b)) FROM t2 GROUP BY a, b, c; + +EXPLAIN SELECT (SUM(DISTINCT a) + MAX(b)) FROM t2 GROUP BY a; +SELECT (SUM(DISTINCT a) + MAX(b)) FROM t2 GROUP BY a; + +DROP TABLE t1,t2; + +--echo # end of WL#3220 tests diff --git a/mysql-test/t/information_schema-big.test b/mysql-test/t/information_schema-big.test new file mode 100644 index 00000000000..c9cd65f0851 --- /dev/null +++ b/mysql-test/t/information_schema-big.test @@ -0,0 +1,48 @@ +# This test uses grants, which can't get tested for embedded server +-- source include/big_test.inc +-- source include/not_embedded.inc + +# check that CSV engine was compiled in, as the result of the test depends +# on the presence of the log tables (which are CSV-based). +--source include/have_csv.inc + +--disable_warnings +DROP TABLE IF EXISTS t0,t1,t2,t3,t4,t5; +DROP VIEW IF EXISTS v1; +--enable_warnings + + +--echo # +--echo # Bug#18925: subqueries with MIN/MAX functions on INFORMARTION_SCHEMA +--echo # + +SELECT t.table_name, c1.column_name + FROM information_schema.tables t + INNER JOIN + information_schema.columns c1 + ON t.table_schema = c1.table_schema AND + t.table_name = c1.table_name + WHERE t.table_schema = 'information_schema' AND + c1.ordinal_position = + ( SELECT COALESCE(MIN(c2.ordinal_position),1) + FROM information_schema.columns c2 + WHERE c2.table_schema = t.table_schema AND + c2.table_name = t.table_name AND + c2.column_name LIKE '%SCHEMA%' + ) + AND t.table_name NOT LIKE 'innodb%'; +SELECT t.table_name, c1.column_name + FROM information_schema.tables t + INNER JOIN + information_schema.columns c1 + ON t.table_schema = c1.table_schema AND + t.table_name = c1.table_name + WHERE t.table_schema = 'information_schema' AND + c1.ordinal_position = + ( SELECT COALESCE(MIN(c2.ordinal_position),1) + FROM information_schema.columns c2 + WHERE c2.table_schema = 'information_schema' AND + c2.table_name = t.table_name AND + c2.column_name LIKE '%SCHEMA%' + ) + AND t.table_name NOT LIKE 'innodb%'; diff --git a/mysql-test/t/information_schema.test b/mysql-test/t/information_schema.test index 6060c78f791..9da7cc1042d 100644 --- a/mysql-test/t/information_schema.test +++ b/mysql-test/t/information_schema.test @@ -366,7 +366,7 @@ show tables from information_schema like "T%"; create database information_schema; use information_schema; show full tables like "T%"; ---error ER_UNKNOWN_TABLE +--error ER_DBACCESS_DENIED_ERROR create table t1(a int); use test; show tables; @@ -914,41 +914,6 @@ DROP PROCEDURE p1; DROP USER mysql_bug20230@localhost; # -# Bug#18925 subqueries with MIN/MAX functions on INFORMARTION_SCHEMA -# - -SELECT t.table_name, c1.column_name - FROM information_schema.tables t - INNER JOIN - information_schema.columns c1 - ON t.table_schema = c1.table_schema AND - t.table_name = c1.table_name - WHERE t.table_schema = 'information_schema' AND - c1.ordinal_position = - ( SELECT COALESCE(MIN(c2.ordinal_position),1) - FROM information_schema.columns c2 - WHERE c2.table_schema = t.table_schema AND - c2.table_name = t.table_name AND - c2.column_name LIKE '%SCHEMA%' - ) - AND t.table_name not like 'innodb_%'; -SELECT t.table_name, c1.column_name - FROM information_schema.tables t - INNER JOIN - information_schema.columns c1 - ON t.table_schema = c1.table_schema AND - t.table_name = c1.table_name - WHERE t.table_schema = 'information_schema' AND - c1.ordinal_position = - ( SELECT COALESCE(MIN(c2.ordinal_position),1) - FROM information_schema.columns c2 - WHERE c2.table_schema = 'information_schema' AND - c2.table_name = t.table_name AND - c2.column_name LIKE '%SCHEMA%' - ) - AND t.table_name not like 'innodb_%'; - -# # Bug#2123 query with a simple non-correlated subquery over # INFORMARTION_SCHEMA.TABLES # @@ -1426,6 +1391,65 @@ SET TIMESTAMP=DEFAULT; --echo End of 5.1 tests. +# +# Bug#24062 Incorrect error msg after execute DROP TABLE IF EXISTS on information_schema +# +--error ER_DBACCESS_DENIED_ERROR +create table information_schema.t1 (f1 INT); +--error ER_DBACCESS_DENIED_ERROR +drop table information_schema.t1; +--error ER_DBACCESS_DENIED_ERROR +drop temporary table if exists information_schema.t1; +--error ER_DBACCESS_DENIED_ERROR +create temporary table information_schema.t1 (f1 INT); +--error ER_DBACCESS_DENIED_ERROR +drop view information_schema.v1; +--error ER_DBACCESS_DENIED_ERROR +create view information_schema.v1; +--error ER_DBACCESS_DENIED_ERROR +create trigger mysql.trg1 after insert on information_schema.t1 for each row set @a=1; +--error 1109 +create table t1 select * from information_schema.t1; + +CREATE TABLE t1(f1 char(100)); +--error ER_DBACCESS_DENIED_ERROR +REPAIR TABLE t1, information_schema.tables; +CHECKSUM TABLE t1, information_schema.tables; +--error ER_DBACCESS_DENIED_ERROR +ANALYZE TABLE t1, information_schema.tables; +CHECK TABLE t1, information_schema.tables; +--error ER_DBACCESS_DENIED_ERROR +OPTIMIZE TABLE t1, information_schema.tables; +--error ER_DBACCESS_DENIED_ERROR +RENAME TABLE v1 to v2, information_schema.tables to t2; +--error ER_DBACCESS_DENIED_ERROR +DROP TABLE t1, information_schema.tables; + +--error ER_DBACCESS_DENIED_ERROR +LOCK TABLES t1 READ, information_schema.tables READ; +DROP TABLE t1; + +# +# Bug#39270 I_S optimization algorithm does not work properly in some cases +# +EXPLAIN SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE; +EXPLAIN SELECT * FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME='t1'; +EXPLAIN SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS + WHERE CONSTRAINT_SCHEMA='test'; +EXPLAIN SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS + WHERE TABLE_NAME='t1' and TABLE_SCHEMA='test'; +EXPLAIN SELECT * FROM INFORMATION_SCHEMA.TRIGGERS + WHERE EVENT_OBJECT_SCHEMA='test'; + +# +# Bug #43834 Assertion in Natural_join_column::db_name() on an I_S query +# +SELECT * +FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE +LEFT JOIN INFORMATION_SCHEMA.COLUMNS +USING (TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME) +WHERE COLUMNS.TABLE_SCHEMA = 'test' +AND COLUMNS.TABLE_NAME = 't1'; + # Wait till all disconnects are completed --source include/wait_until_count_sessions.inc - diff --git a/mysql-test/t/information_schema_db.test b/mysql-test/t/information_schema_db.test index ef66f71d2ad..7326f33b113 100644 --- a/mysql-test/t/information_schema_db.test +++ b/mysql-test/t/information_schema_db.test @@ -1,5 +1,5 @@ # this test mostly test privilege control (what doesn't work -# in the embedded server by default). So disabled in embedded-server mode +# in the embedded server by default). So skip the test in embedded-server mode. -- source include/not_embedded.inc -- source include/testdb_only.inc @@ -13,7 +13,7 @@ drop function if exists f2; use INFORMATION_SCHEMA; --replace_result Tables_in_INFORMATION_SCHEMA Tables_in_information_schema -show tables where Tables_in_information_schema not like "Innodb%"; +show tables where Tables_in_INFORMATION_SCHEMA NOT LIKE 'Innodb%'; --replace_result 'Tables_in_INFORMATION_SCHEMA (T%)' 'Tables_in_information_schema (T%)' show tables from INFORMATION_SCHEMA like 'T%'; create database `inf%`; @@ -123,7 +123,7 @@ create view v1 as select f1 from t1; grant insert on v1 to testdb_2@localhost; create view v5 as select f1 from t1; -grant show view on v5 to testdb_2@localhost; +grant select, show view on v5 to testdb_2@localhost; --error ER_SPECIFIC_ACCESS_DENIED_ERROR create definer=`no_such_user`@`no_such_host` view v6 as select f1 from t1; @@ -131,7 +131,7 @@ create definer=`no_such_user`@`no_such_host` view v6 as select f1 from t1; connection default; use testdb_1; create view v6 as select f1 from t1; -grant show view on v6 to testdb_2@localhost; +grant select, show view on v6 to testdb_2@localhost; create table t2 (f1 char(4)); create definer=`no_such_user`@`no_such_host` view v7 as select * from t2; @@ -163,10 +163,10 @@ show fields from testdb_1.v7; show create view testdb_1.v7; revoke insert(f1) on v3 from testdb_2@localhost; -revoke show view on v5 from testdb_2@localhost; +revoke select,show view on v5 from testdb_2@localhost; connection default; use testdb_1; -revoke show view on v6 from testdb_2@localhost; +revoke select,show view on v6 from testdb_2@localhost; connection testdb_2; --error ER_TABLEACCESS_DENIED_ERROR diff --git a/mysql-test/t/innodb_mysql.test b/mysql-test/t/innodb_mysql.test index a2a1113598d..3cd7b40f4ab 100644 --- a/mysql-test/t/innodb_mysql.test +++ b/mysql-test/t/innodb_mysql.test @@ -537,3 +537,21 @@ DROP VIEW v1; DROP TABLE t1,t2; --echo End of 5.1 tests + + +--echo # +--echo # Test for bug #39932 "create table fails if column for FK is in different +--echo # case than in corr index". +--echo # +--disable_warnings +drop tables if exists t1, t2; +--enable_warnings +create table t1 (pk int primary key) engine=InnoDB; +--echo # Even although the below statement uses uppercased field names in +--echo # foreign key definition it still should be able to find explicitly +--echo # created supporting index. So it should succeed and should not +--echo # create any additional supporting indexes. +create table t2 (fk int, key x (fk), + constraint x foreign key (FK) references t1 (PK)) engine=InnoDB; +show create table t2; +drop table t2, t1; diff --git a/mysql-test/t/insert.test b/mysql-test/t/insert.test index 8f9ed6c7d06..40d1464b86a 100644 --- a/mysql-test/t/insert.test +++ b/mysql-test/t/insert.test @@ -235,8 +235,10 @@ insert into t1 values (1,11), (2,22); insert into t2 values (1,12), (2,24); --error 1393 insert into v1 (f1) values (3) on duplicate key update f3= f3 + 10; +--error 1393 insert into v1 (f1) values (3) on duplicate key update f1= f3 + 10; select * from t1; +--error 1393 insert into v1 (f1) values (3) on duplicate key update f1= f3 + 10; select * from t1; drop view v1; @@ -499,3 +501,33 @@ DROP TABLE t1, t2; --echo End of 5.0 tests. +--echo # +--echo # Bug#34898 "mysql_info() reports 0 warnings while +--echo # mysql_warning_count() reports 1" +--echo # Check that the number of warnings reported by +--echo # mysql_info() is correct. +--echo # + +--disable_warnings +drop table if exists t1; +--enable_warnings + +create table t1 (data varchar(4) not null); + +set sql_mode='error_for_division_by_zero'; +--echo # +--echo # Demonstrate that the number of warnings matches +--echo # the information in mysql_info(). +--echo # +--enable_info +insert t1 (data) values ('letter'), (1/0); +update t1 set data='envelope' where 1/0 or 1; +insert t1 (data) values (default), (1/0), ('dead beef'); +--disable_info + +set sql_mode=default; +drop table t1; + +--echo # +--echo # End of 5.4 tests +--echo # diff --git a/mysql-test/t/join_optimizer.test b/mysql-test/t/join_optimizer.test new file mode 100644 index 00000000000..3afe82113b9 --- /dev/null +++ b/mysql-test/t/join_optimizer.test @@ -0,0 +1,45 @@ +--disable_warnings +drop table if exists t0,t1,t2,t3; +--enable_warnings + +--echo # +--echo # BUG#38049 incorrect rows estimations with references from preceding table +--echo # + +create table t0 (a int); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); + +create table t1 (a varchar(32)); +insert into t1 values ('owner'),('requester'),('admincc'),('cc'); + +CREATE TABLE t2 ( + id int(11) NOT NULL, + type varchar(32) default NULL, + PRIMARY KEY (id) +); +insert into t2 values (1,'owner'), (2,'admincc'); + + +CREATE TABLE t3 ( + id int(11) NOT NULL, + domain varchar(32) default NULL, + type varchar(32) default NULL, + PRIMARY KEY (id) +); + +set @domain='system'; +set @pk=0; +INSERT INTO t3 select @pk:=@pk+1, 'system', t1.a from t1; +INSERT INTO t3 select @pk:=@pk+1, 'queue', t1.a from t1, t0 where t0.a<3; +INSERT INTO t3 select @pk:=@pk+1, 'ticket', t1.a from t1, t0 A, t0 B, t0 C; + +CREATE INDEX groups_d ON t3(domain); +CREATE INDEX groups_t ON t3(type); +CREATE INDEX groups_td ON t3(type, domain); +CREATE INDEX groups_dt ON t3(domain, type); +--echo For table g this must use ref(groups_dt) and #rows should be around 15 and not 335: +explain +SELECT STRAIGHT_JOIN g.id FROM t2 a, t3 g USE INDEX(groups_dt) +WHERE g.domain = 'queue' AND g.type = a.type; + +drop table t0,t1,t2,t3; diff --git a/mysql-test/t/loadxml.test b/mysql-test/t/loadxml.test new file mode 100644 index 00000000000..8b9c1bd1b0d --- /dev/null +++ b/mysql-test/t/loadxml.test @@ -0,0 +1,108 @@ +# +# Tests for "LOAD XML" - a contributed patch from Erik Wetterberg. +# + +# Running the $MYSQL_DUMP tool against an embedded server does not work. +--source include/not_embedded.inc + +--disable_warnings +drop table if exists t1, t2; +--enable_warnings + +create table t1 (a int, b varchar(64)); + + +--echo -- Load a static XML file +load xml infile '../../std_data/loadxml.dat' into table t1 +rows identified by '<row>'; +select * from t1 order by a; +delete from t1; + + +--echo -- Load a static XML file with 'IGNORE num ROWS' +load xml infile '../../std_data/loadxml.dat' into table t1 +rows identified by '<row>' ignore 4 rows; +select * from t1 order by a; + + +--echo -- Check 'mysqldump --xml' + 'LOAD XML' round trip +--exec $MYSQL_DUMP --xml test t1 > "$MYSQLTEST_VARDIR/tmp/loadxml-dump.xml" 2>&1 +delete from t1; +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--eval load xml infile '$MYSQLTEST_VARDIR/tmp/loadxml-dump.xml' into table t1 rows identified by '<row>'; +select * from t1 order by a; + +--echo --Check that default row tag is '<row> +delete from t1; +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--eval load xml infile '$MYSQLTEST_VARDIR/tmp/loadxml-dump.xml' into table t1; +select * from t1 order by a; + +--echo -- Check that 'xml' is not a keyword +select 1 as xml; + + +# +# Bug #42520 killing load .. infile Assertion failed: ! is_set(), file .\sql_error.cc, line 8 +# + +--disable_query_log +delete from t1; +insert into t1 values (1, '12345678900987654321'), (2, 'asdfghjkl;asdfghjkl;'); +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +--exec $MYSQL_DUMP --xml test t1 > "$MYSQLTEST_VARDIR/tmp/loadxml-dump.xml" 2>&1 +--enable_query_log + +connect (addconroot, localhost, root,,); +connection addconroot; +create table t2(fl text); +--let $PSEUDO_THREAD_ID=`select @@pseudo_thread_id ` + +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--send_eval LOAD XML LOCAL INFILE "$MYSQLTEST_VARDIR/tmp/loadxml-dump.xml" INTO TABLE t2 ROWS IDENTIFIED BY '<person>'; + +sleep 3; + + +connection default; +sleep 1; + +--disable_query_log +--eval kill $PSEUDO_THREAD_ID +sleep 2; +--enable_query_log + +disconnect addconroot; + +# +# Clean up +# +remove_file $MYSQLTEST_VARDIR/tmp/loadxml-dump.xml; +drop table t1; +drop table t2; + +# +# Bug #36750 LOAD XML doesn't understand new line (feed) characters in multi line text fields +# + +create table t1 ( + id int(11) not null, + text text, + primary key (id) +) engine=MyISAM default charset=latin1; +load xml infile '../../std_data/loadxml2.dat' into table t1; +select * from t1; +drop table t1; + diff --git a/mysql-test/t/locale.test b/mysql-test/t/locale.test index 7ceb49fd1f4..04ac95cea06 100644 --- a/mysql-test/t/locale.test +++ b/mysql-test/t/locale.test @@ -3,6 +3,44 @@ DROP TABLE IF EXISTS t1; --enable_warnings --echo Start of 5.4 tests + +--echo # +--echo # WL#4642 Greek locale for DAYNAME, MONTHNAME, DATE_FORMAT +--echo # + +SET NAMES utf8; + +SET @@lc_time_names=109; +SELECT @@lc_time_names; + +CREATE TABLE t1 (a DATE); +INSERT INTO t1 VALUES +('2006-01-01'),('2006-01-02'),('2006-01-03'), +('2006-01-04'),('2006-01-05'),('2006-01-06'),('2006-01-07'); +SELECT a, date_format(a,'%a') as abday, dayname(a) as day FROM t1 ORDER BY a; +DROP TABLE t1; + +CREATE TABLE t1 (a DATE); +INSERT INTO t1 VALUES +('2006-01-01'),('2006-02-01'),('2006-03-01'), +('2006-04-01'),('2006-05-01'),('2006-06-01'), +('2006-07-01'),('2006-08-01'),('2006-09-01'), +('2006-10-01'),('2006-11-01'),('2006-12-01'); +SELECT a, date_format(a,'%b') as abmon, monthname(a) as mon FROM t1 ORDER BY a; + +SELECT format(123456.789, 3, 'el_GR'); +DROP TABLE t1; + +--echo # +--echo # Bug#46633 Obsolete Serbian locale name +--echo # +SET lc_messages=sr_YU; +SHOW VARIABLES LIKE 'lc_messages'; +SET lc_messages=sr_RS; +SHOW VARIABLES LIKE 'lc_messages'; +SET lc_time_names=sr_RS; +SELECT format(123456.789, 3, 'sr_RS'); + --echo # --echo # Bug#43207 wrong LC_TIME names for romanian locale --echo # diff --git a/mysql-test/t/lock_sync.test b/mysql-test/t/lock_sync.test new file mode 100644 index 00000000000..73289685114 --- /dev/null +++ b/mysql-test/t/lock_sync.test @@ -0,0 +1,118 @@ +# +# Locking related tests which use DEBUG_SYNC facility. +# +--source include/have_debug_sync.inc +# We need InnoDB to be able use TL_WRITE_ALLOW_WRITE type of locks in our tests. +--source include/have_innodb.inc +# Until bug#41971 'Thread state on embedded server is always "Writing to net"' +# is fixed this test can't be run on embedded version of server. +--source include/not_embedded.inc + +# Save the initial number of concurrent sessions. +--source include/count_sessions.inc + + +--echo # +--echo # Test for bug #45143 "All connections hang on concurrent ALTER TABLE". +--echo # +--echo # Concurrent execution of statements which required weak write lock +--echo # (TL_WRITE_ALLOW_WRITE) on several instances of the same table and +--echo # statements which tried to acquire stronger write lock (TL_WRITE, +--echo # TL_WRITE_ALLOW_READ) on this table might have led to deadlock. +--disable_warnings +drop table if exists t1; +--enable_warnings +--echo # Create auxiliary connections used through the test. +connect (con_bug45143_1,localhost,root,,test,,); +connect (con_bug45143_3,localhost,root,,test,,); +connect (con_bug45143_2,localhost,root,,test,,); +connection default; +--echo # Reset DEBUG_SYNC facility before using it. +set debug_sync= 'RESET'; +--echo # Turn off logging so calls to locking subsystem performed +--echo # for general_log table won't interfere with our test. +set @old_general_log = @@global.general_log; +set @@global.general_log= OFF; + +create table t1 (i int) engine=InnoDB; +insert into t1 values (1); +--echo # Prepare user lock which will be used for resuming execution of +--echo # the first statement after it acquires TL_WRITE_ALLOW_WRITE lock. +select get_lock("lock_bug45143_wait", 0); + +--echo # Switch to connection 'con_bug45143_1'. +connection con_bug45143_1; +--echo # Sending: +--send insert into t1 values (get_lock("lock_bug45143_wait", 100)); + +--echo # Switch to connection 'con_bug45143_2'. +connection con_bug45143_2; +--echo # Wait until the above INSERT takes TL_WRITE_ALLOW_WRITE lock on 't1' +--echo # and then gets blocked on user lock 'lock_bug45143_wait'. +let $wait_condition= select count(*)= 1 from information_schema.processlist + where state= 'User lock' and + info='insert into t1 values (get_lock("lock_bug45143_wait", 100))'; +--source include/wait_condition.inc +--echo # Ensure that upcoming SELECT waits after acquiring TL_WRITE_ALLOW_WRITE +--echo # lock for the first instance of 't1'. +set debug_sync='thr_multi_lock_after_thr_lock SIGNAL parked WAIT_FOR go'; +--echo # Sending: +--send select count(*) > 0 from t1 as a, t1 as b for update; + +--echo # Switch to connection 'con_bug45143_3'. +connection con_bug45143_3; +--echo # Wait until the above SELECT ... FOR UPDATE is blocked after +--echo # acquiring lock for the the first instance of 't1'. +set debug_sync= 'now WAIT_FOR parked'; +--echo # Send LOCK TABLE statement which will try to get TL_WRITE lock on 't1': +--send lock table t1 write; + +--echo # Switch to connection 'default'. +connection default; +--echo # Wait until this LOCK TABLES statement starts waiting for table lock. +let $wait_condition= select count(*)= 1 from information_schema.processlist + where state= 'Locked' and + info='lock table t1 write'; +--source include/wait_condition.inc +--echo # Allow SELECT ... FOR UPDATE to resume. +--echo # Since it already has TL_WRITE_ALLOW_WRITE lock on the first instance +--echo # of 't1' it should be able to get lock on the second instance without +--echo # waiting, even although there is another thread which has such lock +--echo # on this table and also there is a thread waiting for a TL_WRITE on it. +set debug_sync= 'now SIGNAL go'; + +--echo # Switch to connection 'con_bug45143_2'. +connection con_bug45143_2; +--echo # Reap SELECT ... FOR UPDATE +--reap + +--echo # Switch to connection 'default'. +connection default; +--echo # Resume execution of the INSERT statement. +select release_lock("lock_bug45143_wait"); + +--echo # Switch to connection 'con_bug45143_1'. +connection con_bug45143_1; +--echo # Reap INSERT statement. +--reap + +--echo # Switch to connection 'con_bug45143_3'. +connection con_bug45143_3; +--echo # Reap LOCK TABLES statement. +--reap +unlock tables; + +--echo # Switch to connection 'default'. +connection default; +--echo # Do clean-up. +disconnect con_bug45143_1; +disconnect con_bug45143_2; +disconnect con_bug45143_3; +set debug_sync= 'RESET'; +set @@global.general_log= @old_general_log; +drop table t1; + + +# Check that all connections opened by test cases in this file are really +# gone so execution of other tests won't be affected by their presence. +--source include/wait_until_count_sessions.inc diff --git a/mysql-test/t/log_state.test b/mysql-test/t/log_state.test index e40dd1e3491..4d8c8fc6e74 100644 --- a/mysql-test/t/log_state.test +++ b/mysql-test/t/log_state.test @@ -1,16 +1,5 @@ ### t/log_state.test ### # -# This test suffers from server -# Bug#38124 "general_log_file" variable silently unset when using expression -# In short: -# SET GLOBAL general_log_file = @<whatever> -# SET GLOBAL slow_query_log = @<whatever> -# cause that the value of these server system variables is set to default -# instead of the assigned values. There comes no error message or warning. -# If this bug is fixed please -# 1. try this test with "let $fixed_bug38124 = 0;" -# 2. remove all workarounds if 1. was successful. -let $fixed_bug38124 = 0; --source include/not_embedded.inc --source include/have_csv.inc @@ -166,16 +155,6 @@ SET @@global.general_log = @old_general_log; SET @@global.general_log_file = @old_general_log_file; SET @@global.slow_query_log = @old_slow_query_log; SET @@global.slow_query_log_file = @old_slow_query_log_file; -if(!$fixed_bug38124) -{ - --disable_query_log - let $my_var = `SELECT @old_general_log_file`; - eval SET @@global.general_log_file = '$my_var'; - let $my_var = `SELECT @old_slow_query_log_file`; - eval SET @@global.slow_query_log_file = '$my_var'; - --enable_query_log -} - ########################################################################### @@ -278,15 +257,6 @@ SET GLOBAL slow_query_log_file= NULL; # Reset to initial values in case a setting above was successful. SET GLOBAL general_log_file= @old_general_log_file; SET GLOBAL slow_query_log_file= @old_slow_query_log_file; -if(!$fixed_bug38124) -{ - --disable_query_log - let $my_var = `SELECT @old_general_log_file`; - eval SET @@global.general_log_file = '$my_var'; - let $my_var = `SELECT @old_slow_query_log_file`; - eval SET @@global.slow_query_log_file = '$my_var'; - --enable_query_log -} ########################################################################### @@ -307,15 +277,6 @@ SHOW VARIABLES LIKE '%log_file'; --echo SET GLOBAL general_log_file = @old_general_log_file; SET GLOBAL slow_query_log_file = @old_slow_query_log_file; -if(!$fixed_bug38124) -{ - --disable_query_log - let $my_var = `SELECT @old_general_log_file`; - eval SET @@global.general_log_file = '$my_var'; - let $my_var = `SELECT @old_slow_query_log_file`; - eval SET @@global.slow_query_log_file = '$my_var'; - --enable_query_log -} --echo --echo # -- End of Bug#32748. @@ -351,19 +312,43 @@ SET @@global.general_log = @old_general_log; SET @@global.general_log_file = @old_general_log_file; SET @@global.slow_query_log = @old_slow_query_log; SET @@global.slow_query_log_file = @old_slow_query_log_file; -if(!$fixed_bug38124) -{ - --disable_query_log - let $my_var = `SELECT @old_general_log_file`; - eval SET @@global.general_log_file = '$my_var'; - let $my_var = `SELECT @old_slow_query_log_file`; - eval SET @@global.slow_query_log_file = '$my_var'; - --enable_query_log -} - --echo End of 5.1 tests + +########################################################################### + +--echo +--echo # -- +--echo # -- Bug#38124: "general_log_file" variable silently unset when +--echo # -- using expression +--echo # -- + +# Store away the special DEFAULT value so we +# can compare it later, then try to set the +# general_log_file using different functions +# and expressions. + +SET GLOBAL general_log_file = DEFAULT; +SELECT @@general_log_file INTO @my_glf; + +SET GLOBAL general_log_file = 'BUG38124.LOG'; +SELECT @@general_log_file; + +SET GLOBAL general_log_file = concat('BUG38124-2.LOG'); +SELECT @@general_log_file; + +SET GLOBAL general_log_file = substr('BUG38124-2.LOG',3,6); +SELECT @@general_log_file; + +SET GLOBAL general_log_file = DEFAULT; +SELECT @@general_log_file = @my_glf; + + +## Reset to initial values +SET GLOBAL general_log_file = @old_general_log_file; + + --enable_ps_protocol # @@ -380,17 +365,7 @@ SET global general_log = @old_general_log; SET global general_log_file = @old_general_log_file; SET global slow_query_log = @old_slow_query_log; SET global slow_query_log_file = @old_slow_query_log_file; -if(!$fixed_bug38124) -{ - --disable_query_log - let $my_var = `SELECT @old_general_log_file`; - eval SET @@global.general_log_file = '$my_var'; - let $my_var = `SELECT @old_slow_query_log_file`; - eval SET @@global.slow_query_log_file = '$my_var'; - --enable_query_log -} - -# Remove the log files that was created in the "default location" + +# Remove the log file that was created in the "default location" # i.e var/run ---remove_file $MYSQLTEST_VARDIR/run/mysqld.log --remove_file $MYSQLTEST_VARDIR/tmp/log.master diff --git a/mysql-test/t/log_state_bug33693-master.opt b/mysql-test/t/log_state_bug33693-master.opt new file mode 100644 index 00000000000..9f4ae1a0c8f --- /dev/null +++ b/mysql-test/t/log_state_bug33693-master.opt @@ -0,0 +1 @@ +--pid-file=$MYSQLTEST_VARDIR/run/mysqld.1.pid --log= diff --git a/mysql-test/t/log_state_bug33693.test b/mysql-test/t/log_state_bug33693.test new file mode 100644 index 00000000000..d67b28fc3e9 --- /dev/null +++ b/mysql-test/t/log_state_bug33693.test @@ -0,0 +1,18 @@ +### t/log_state_bug33693.test +# +# Regression test for bug #33693 +# "general log name and location depend on PID +# file, not on predefined values" +# +# The server is started with a hard-coded +# PID file in the $MYSQLTEST_VARDIR/run +# directory, and an unspecified general log +# file name. +# +# The correct result should show the log file to +# rest in the database directory. Unfixed, the +# log file will be in the same directory as the +# PID. + +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--eval SELECT INSTR(@@general_log_file, '$MYSQLTEST_VARDIR/run'); diff --git a/mysql-test/t/lowercase_table2.test b/mysql-test/t/lowercase_table2.test index 521df01cc9b..92add60616a 100644 --- a/mysql-test/t/lowercase_table2.test +++ b/mysql-test/t/lowercase_table2.test @@ -150,3 +150,75 @@ select TABLE_SCHEMA,TABLE_NAME FROM information_schema.TABLES where TABLE_SCHEMA ='mysqltest_LC2'; use test; drop database mysqltest_LC2; + +--echo # End of 5.1 tests + + +--echo # +--echo # Test for bug #44738 "fill_schema_table_from_frm() opens tables without +--echo # lowercasing table name". Due to not properly normalizing table names +--echo # in lower_case_table_names modes in this function queries to I_S which +--echo # were executed through it left entries with incorrect key in table +--echo # definition cache. As result further queries to I_S that used this +--echo # function produced stale results in cases when table definition was +--echo # changed by a DDL statement. Also combination of this issue and a +--echo # similar problem in CREATE TABLE (it also has peeked into table +--echo # definition cache using non-normalized key) led to spurious +--echo # ER_TABLE_EXISTS_ERROR errors when one tried to create table with the +--echo # same name as a previously existing but dropped table. +--echo # +--disable_warnings +drop database if exists mysqltest_UPPERCASE; +drop table if exists t_bug44738_UPPERCASE; +--enable_warnings +create database mysqltest_UPPERCASE; +use mysqltest_UPPERCASE; +create table t_bug44738_UPPERCASE (i int) comment='Old comment'; +create table t_bug44738_lowercase (i int) comment='Old comment'; +select table_schema, table_name, table_comment from information_schema.tables + where table_schema like 'mysqltest_%' and table_name like 't_bug44738_%' + order by table_name; +alter table t_bug44738_UPPERCASE comment='New comment'; +alter table t_bug44738_lowercase comment='New comment'; +--echo # There should be no stale entries in TDC for our tables after the +--echo # above ALTER TABLE statements so new version of comments should be +--echo # returned by the below query to I_S. +select table_schema, table_name, table_comment from information_schema.tables + where table_schema like 'mysqltest_%' and table_name like 't_bug44738_%' + order by table_name; +drop database mysqltest_UPPERCASE; +use test; + +--echo # Let us check that the original test case which led to discovery +--echo # of this problem also works. +create table t_bug44738_UPPERCASE (i int); +select table_schema, table_name, table_comment from information_schema.tables + where table_schema = 'test' and table_name like 't_bug44738_%'; +drop table t_bug44738_UPPERCASE; +--echo # After the above DROP TABLE there are no entries in TDC which correspond +--echo # to our table and therefore the below statement should succeed. +create table t_bug44738_UPPERCASE (i int); +drop table t_bug44738_UPPERCASE; + +--echo # Finally, let us check that another issue which was exposed by +--echo # the original test case is solved. I.e. that fuse in CREATE TABLE +--echo # which ensures that table is not created if there is an entry for +--echo # it in TDC even though it was removed from disk uses normalized +--echo # version of the table name. +create table t_bug44738_UPPERCASE (i int) engine = myisam; +--echo # Load table definition in TDC. +select table_schema, table_name, table_comment from information_schema.tables + where table_schema = 'test' and table_name like 't_bug44738_%'; +--echo # Simulate manual removal of the table. +let $MYSQLD_DATADIR= `select @@datadir`; +--remove_file $MYSQLD_DATADIR/test/t_bug44738_UPPERCASE.frm +--remove_file $MYSQLD_DATADIR/test/t_bug44738_UPPERCASE.MYD +--remove_file $MYSQLD_DATADIR/test/t_bug44738_UPPERCASE.MYI +--echo # After manual removal of table still there should be an entry for table +--echo # in TDC so attempt to create table with the same name should fail. +--error ER_TABLE_EXISTS_ERROR +create table t_bug44738_UPPERCASE (i int); +--echo # And should succeed after FLUSH TABLES. +flush tables; +create table t_bug44738_UPPERCASE (i int); +drop table t_bug44738_UPPERCASE; diff --git a/mysql-test/t/merge.test b/mysql-test/t/merge.test index 015ae28c155..63ad5a1e97c 100644 --- a/mysql-test/t/merge.test +++ b/mysql-test/t/merge.test @@ -1634,3 +1634,63 @@ DROP TRIGGER tr1; DROP TABLE t1, t2, t3; --echo End of 5.1 tests + +--echo # +--echo # An additional test case for Bug#27430 Crash in subquery code +--echo # when in PS and table DDL changed after PREPARE +--echo # +--echo # Test merge table with too many merge children. +--echo # +--disable_warnings +drop table if exists t_parent; +--enable_warnings +set @save_table_definition_cache=@@global.table_definition_cache; +--echo # +--echo # Set @@global.table_definition_cache to minimum +--echo # +set @@global.table_definition_cache=400; +set @a=null; +let $1 = 400; +--echo # +--echo # Create 400 merge children +--echo # +--disable_query_log +while ($1) +{ +--disable_warnings + eval drop table if exists t$1; +--enable_warnings + eval create table t$1 (a int) engine=myisam; + eval set @a=ifnull(concat(@a, ", ", "t$1"), "t$1"); + dec $1; +} +--enable_query_log +set @a=concat("create table t_parent (a int) union(", @a, + ") insert_method=first engine=mrg_myisam"); +prepare stmt from @a; +execute stmt; +prepare stmt from "select * from t_parent"; +--error ER_NEED_REPREPARE +execute stmt; +--error ER_NEED_REPREPARE +execute stmt; +--error ER_NEED_REPREPARE +execute stmt; +deallocate prepare stmt; +--echo # +--echo # Create merge parent +--echo # + +--echo # +--echo # Cleanup +--echo # +let $1 = 400; +--disable_query_log +while ($1) +{ + eval drop table t$1; + dec $1; +} +--enable_query_log +drop table t_parent; +set @@global.table_definition_cache=@save_table_definition_cache; diff --git a/mysql-test/t/metadata.test b/mysql-test/t/metadata.test index a10767579fb..a42dcfd618d 100644 --- a/mysql-test/t/metadata.test +++ b/mysql-test/t/metadata.test @@ -130,3 +130,60 @@ SELECT COALESCE(d, d), IFNULL(d, d), IF(i, d, d), DROP TABLE t1; --echo End of 5.0 tests + +# Verify that column metadata is correct for all possible data types. +# Originally about BUG#42980 "Client doesn't set NUM_FLAG for DECIMAL" + +create table t1( +# numeric types +bool_col bool, +boolean_col boolean, +bit_col bit(5), +tiny tinyint, +tiny_uns tinyint unsigned, +small smallint, +small_uns smallint unsigned, +medium mediumint, +medium_uns mediumint unsigned, +int_col int, +int_col_uns int unsigned, +big bigint, +big_uns bigint unsigned, +decimal_col decimal(10,5), +# synonyms of DECIMAL +numeric_col numeric(10), +fixed_col fixed(10), +dec_col dec(10), +decimal_col_uns decimal(10,5) unsigned, +fcol float, +fcol_uns float unsigned, +dcol double, +double_precision_col double precision, +dcol_uns double unsigned, +# date/time types +date_col date, +time_col time, +timestamp_col timestamp, +year_col year, +datetime_col datetime, +# string types +char_col char(5), +varchar_col varchar(10), +binary_col binary(10), +varbinary_col varbinary(10), +tinyblob_col tinyblob, +blob_col blob, +mediumblob_col mediumblob, +longblob_col longblob, +text_col text, +mediumtext_col mediumtext, +longtext_col longtext, +enum_col enum("A","B","C"), +set_col set("F","E","D") +); + +--enable_metadata +select * from t1; +--disable_metadata + +drop table t1; diff --git a/mysql-test/t/mysql.test b/mysql-test/t/mysql.test index 7e28358ba8b..8b30d9961d8 100644 --- a/mysql-test/t/mysql.test +++ b/mysql-test/t/mysql.test @@ -383,6 +383,11 @@ remove_file $MYSQLTEST_VARDIR/tmp/bug31060.sql; drop tables t1, t2; # +# mysql client with 'init-command' option +# +--exec $MYSQL --init-command="SET lc_messages=ru_RU" -e "SHOW VARIABLES LIKE 'lc_messages';" + +# # Bug #27884: mysql --html does not quote HTML special characters in output # --write_file $MYSQLTEST_VARDIR/tmp/bug27884.sql diff --git a/mysql-test/t/mysql_locale_posix.test b/mysql-test/t/mysql_locale_posix.test new file mode 100644 index 00000000000..7d306dfc6c4 --- /dev/null +++ b/mysql-test/t/mysql_locale_posix.test @@ -0,0 +1,205 @@ +--source include/not_windows.inc +--source include/have_case_sensitive_file_system.inc + + +# +# Note, please keep this file UTF-8 compatible. +# After editing, make sure that +# "file mysql_locale_posix.test" +# says +# "UTF-8 Unicode text" +# or +# "UTF-8 Unicode English text" +# + + +# +# Check if we're running on a POSIX-locale machine +# + +--disable_query_log +--exec locale -a > $MYSQLTEST_VARDIR/tmp/locale_a.output 2>/dev/null || true +SET @file=REPLACE(LOAD_FILE('../../tmp/locale_a.output'), '-', ''); +# +# Note, file content must be case sensitive. +# Some platforms are case sensitive regarding to locale name. +# For example, on HP-UX +# LANG=cs_CZ.iso88592 - works fine +# LANG=cs_CZ.ISO88592 - does not work +# +--remove_file $MYSQLTEST_VARDIR/tmp/locale_a.output + +if (`SELECT (IFNULL(@file,'') NOT LIKE '%\nde_DE.iso88591\n%')`) +{ + Skip Need POSIX locale de_DE.iso88591; +} + +if (`SELECT (IFNULL(@file,'') NOT LIKE '%\nru_RU.koi8r\n%')`) +{ + Skip Need POSIX locale ru_RU.koi8r; +} + +if (`SELECT (IFNULL(@file,'') NOT LIKE '%\ncs_CZ.iso88592\n%')`) +{ + Skip Need POSIX locale cs_CZ.iso88592; +} + +if (`SELECT (IFNULL(@file,'') NOT LIKE '%\nen_US.utf8\n%')`) +{ + Skip Need POSIX locale en_US.utf8; +} +--enable_query_log + + + +SET NAMES utf8; + +--disable_warnings +DROP DATABASE IF EXISTS `ó`; +DROP DATABASE IF EXISTS `Ä‚Å‚`; +DROP DATABASE IF EXISTS `цÐ`; +DROP DATABASE IF EXISTS `ó`; +--enable_warnings + +# +# Test some of the OS-to-MySQL character set mappings +# + + +# {"ANSI_X3.4-1968", "ascii", my_cs_exact}, +# {"ansi1251", "cp1251", my_cs_exact}, +# {"armscii8", "armscii8", my_cs_exact}, +# {"ASCII", "ascii", my_cs_exact}, +# {"Big5", "big5", my_cs_exact}, +# {"cp1251", "cp1251", my_cs_exact}, +# {"cp1255", "cp1255", my_cs_exact}, +# {"CP866", "cp866", my_cs_exact}, +# {"eucCN", "gb2312", my_cs_exact}, +# {"eucJP", "ujis", my_cs_exact}, +# {"eucKR", "euckr", my_cs_exact}, +# +# {"gb18030", "gb18030", my_cs_exact}, +# +# {"gb2312", "gb2312", my_cs_exact}, +# {"gbk", "gbk", my_cs_exact}, +# {"georgianps", "geostd8", my_cs_exact}, +# {"IBM-1252", "cp1252", my_cs_exact}, +# +# {"iso88591", "latin1", my_cs_approx}, +# {"ISO_8859-1", "latin1", my_cs_approx}, +# {"ISO8859-1", "latin1", my_cs_approx}, +# {"ISO-8859-1", "latin1", my_cs_approx}, + + +# +# _utf8 0xC3B3 = LATIN SMALL LETTER O WITH ACUTE +# _latin1 0xC3 = LATIN CAPITAL LETTER A WITH TILDE +# _latin1 0xB3 = SUPERSCRIPT THREE +# +--echo +--echo iso88591 +--exec LC_ALL=fi_FI.iso88591 $MYSQL --default-character-set=auto test -e "SELECT @@character_set_client" +--exec LC_ALL=fi_FI.iso88591 $MYSQLADMIN -uroot -S $MASTER_MYSOCK -P $MASTER_MYPORT --default-character-set=auto create ó +--exec LC_ALL=fi_FI.iso88591 $MYSQL_SHOW --default-character-set=auto ó +--exec LC_ALL=fi_FI.iso88591 $MYSQL_CHECK --default-character-set=auto "ó" + + +# {"iso885913", "latin7", my_cs_exact}, +# {"ISO_8859-13", "latin7", my_cs_exact}, +# {"ISO8859-13", "latin7", my_cs_exact}, +# {"ISO-8859-13", "latin7", my_cs_exact}, +# +# {"iso885915", "latin9", my_cs_exact}, +# {"ISO_8859-15", "latin9", my_cs_exact}, +# {"ISO8859-15", "latin9", my_cs_exact}, +# {"ISO-8859-15", "latin9", my_cs_exact}, +# +# {"iso88592", "latin2", my_cs_exact}, +# {"ISO_8859-2", "latin2", my_cs_exact}, +# {"ISO8859-2", "latin2", my_cs_exact}, +# {"ISO-8859-2", "latin2", my_cs_exact}, +# + + +# +# _utf8 0xC3B3 = LATIN SMALL LETTER O WITH ACUTE +# _latin2 0xC3 = LATIN CAPITAL LETTER A WITH BREVE +# _latin2 0xB3 = LATIN SMALL LETTER L WITH STROKE +# +--echo +--echo iso88592 +--exec LC_ALL=cs_CZ.iso88592 $MYSQL --character-sets-dir=$CHARSETSDIR --default-character-set=auto test -e "SELECT @@character_set_client" +--exec LC_ALL=cs_CZ.iso88592 $MYSQLADMIN --character-sets-dir=$CHARSETSDIR -uroot -S $MASTER_MYSOCK -P $MASTER_MYPORT --default-character-set=auto create ó +--exec LC_ALL=cs_CZ.iso88592 $MYSQL_SHOW --character-sets-dir=$CHARSETSDIR --default-character-set=auto ó +--exec LC_ALL=cs_CZ.iso88592 $MYSQL_CHECK --character-sets-dir=$CHARSETSDIR--default-character-set=auto "ó" + + +# {"iso88597", "greek", my_cs_exact}, +# {"ISO_8859-7", "greek", my_cs_exact}, +# {"ISO8859-7", "greek", my_cs_exact}, +# {"ISO-8859-7", "greek", my_cs_exact}, +# +# {"iso88598", "hebrew", my_cs_exact}, +# {"ISO_8859-8", "hebrew", my_cs_exact}, +# {"ISO8859-8", "hebrew", my_cs_exact}, +# {"ISO-8859-8", "hebrew", my_cs_exact}, +# +# {"iso88599", "latin5", my_cs_exact}, +# {"ISO_8859-9", "latin5", my_cs_exact}, +# {"ISO8859-9", "latin5", my_cs_exact}, +# {"ISO-8859-9", "latin5", my_cs_exact}, +# +# {"koi8r", "koi8r", my_cs_exact}, +# {"KOI8-R", "koi8r", my_cs_exact}, +# {"koi8u", "koi8u", my_cs_exact}, +# {"KOI8-U", "koi8u", my_cs_exact}, +# + +# +# _utf8 0xC3B3 = LATIN SMALL LETTER O WITH ACUTE +# _koi8r 0xC3 = CYRILLIC SMALL LETTER TSE +# _koi8r 0xB3 = CYRILLIC CAPITAL LETTER IO +# + +--echo +--echo koi8r +--exec LC_ALL=ru_RU.koi8r $MYSQL --character-sets-dir=$CHARSETSDIR --default-character-set=auto test -e "SELECT @@character_set_client" +--exec LC_ALL=ru_RU.koi8r $MYSQLADMIN --character-sets-dir=$CHARSETSDIR -uroot -S $MASTER_MYSOCK -P $MASTER_MYPORT --default-character-set=auto create ó +--exec LC_ALL=ru_RU.koi8r $MYSQL_SHOW --character-sets-dir=$CHARSETSDIR --default-character-set=auto ó +--exec LC_ALL=ru_RU.koi8r $MYSQL_CHECK --character-sets-dir=$CHARSETSDIR --default-character-set=auto "ó" + + +# {"Shift_JIS", "sjis", my_cs_exact}, +# {"SJIS", "sjis", my_cs_exact}, +# +# {"tis620", "tis620", my_cs_exact}, +# +# {"ujis", "ujis", my_cs_exact}, +# +# {"US-ASCII", "ascii", my_cs_exact}, +# +# {"utf8", "utf8", my_cs_exact}, +# {"utf-8", "utf8", my_cs_exact}, + +--echo +--echo utf8 +--exec LC_ALL=en_US.utf8 $MYSQL --default-character-set=auto test -e "SELECT @@character_set_client" +--exec LC_ALL=en_US.utf8 $MYSQLADMIN -uroot -S $MASTER_MYSOCK -P $MASTER_MYPORT --default-character-set=auto create ó +--exec LC_ALL=en_US.utf8 $MYSQL_SHOW --default-character-set=auto ó +--exec LC_ALL=en_US.utf8 $MYSQL_SHOW --default-character-set=auto "ó" +--exec LC_ALL=en_US.utf8 $MYSQL_SHOW --default-character-set=auto "Ä‚Å‚" +--exec LC_ALL=en_US.utf8 $MYSQL_SHOW --default-character-set=auto "цÐ" +--exec LC_ALL=en_US.utf8 $MYSQL_CHECK --default-character-set=auto ó +--exec LC_ALL=en_US.utf8 $MYSQL_CHECK --default-character-set=auto "ó" +--exec LC_ALL=en_US.utf8 $MYSQL_CHECK --default-character-set=auto "Ä‚Å‚" +--exec LC_ALL=en_US.utf8 $MYSQL_CHECK --default-character-set=auto "цÐ" + +SHOW DATABASES LIKE 'ó'; +SHOW DATABASES LIKE 'Ä‚Å‚'; +SHOW DATABASES LIKE 'цÐ'; +SHOW DATABASES LIKE 'ó'; + +DROP DATABASE `ó`; +DROP DATABASE `Ä‚Å‚`; +DROP DATABASE `цÐ`; +DROP DATABASE `ó`; diff --git a/mysql-test/t/mysql_upgrade.test b/mysql-test/t/mysql_upgrade.test index d1f97d7287e..8bcbe064b68 100644 --- a/mysql-test/t/mysql_upgrade.test +++ b/mysql-test/t/mysql_upgrade.test @@ -89,3 +89,10 @@ DROP USER mysqltest1@'%'; set GLOBAL sql_mode='STRICT_ALL_TABLES,ANSI_QUOTES,NO_ZERO_DATE'; --exec $MYSQL_UPGRADE --skip-verbose --force 2>&1 eval set GLOBAL sql_mode=default; + +# +# Test the --upgrade-system-tables option +# +--replace_result $MYSQLTEST_VARDIR var +--exec $MYSQL_UPGRADE --skip-verbose --upgrade-system-tables + diff --git a/mysql-test/t/mysqladmin.test b/mysql-test/t/mysqladmin.test index 839ecf00b60..eb59e60eefe 100644 --- a/mysql-test/t/mysqladmin.test +++ b/mysql-test/t/mysqladmin.test @@ -4,7 +4,7 @@ # Test "mysqladmin ping" # ---exec $MYSQLADMIN --no-defaults -S $MASTER_MYSOCK -P $MASTER_MYPORT -u root --password= ping 2>&1 +--exec $MYSQLADMIN --no-defaults --default-character-set=latin1 -S $MASTER_MYSOCK -P $MASTER_MYPORT -u root --password= ping 2>&1 # @@ -19,7 +19,7 @@ EOF --replace_regex /.*mysqladmin.*: unknown/mysqladmin: unknown/ --error 7 ---exec $MYSQLADMIN --defaults-file=$MYSQLTEST_VARDIR/tmp/bug10608.cnf -S $MASTER_MYSOCK -P $MASTER_MYPORT -u root --password= ping 2>&1 +--exec $MYSQLADMIN --defaults-file=$MYSQLTEST_VARDIR/tmp/bug10608.cnf --default-character-set=latin1 -S $MASTER_MYSOCK -P $MASTER_MYPORT -u root --password= ping 2>&1 remove_file $MYSQLTEST_VARDIR/tmp/bug10608.cnf; # When mysqladmin finds "loose-database" in .cnf file it shall print @@ -30,6 +30,6 @@ loose-database=db2 EOF --replace_regex /Warning: .*mysqladmin.*: unknown/Warning: mysqladmin: unknown/ ---exec $MYSQLADMIN --defaults-file=$MYSQLTEST_VARDIR/tmp/bug10608.cnf -S $MASTER_MYSOCK -P $MASTER_MYPORT -u root --password= ping 2>&1 +--exec $MYSQLADMIN --defaults-file=$MYSQLTEST_VARDIR/tmp/bug10608.cnf --default-character-set=latin1 -S $MASTER_MYSOCK -P $MASTER_MYPORT -u root --password= ping 2>&1 remove_file $MYSQLTEST_VARDIR/tmp/bug10608.cnf; diff --git a/mysql-test/t/mysqlbinlog.test b/mysql-test/t/mysqlbinlog.test index 78661b1bbc4..1e5ce4cb0e1 100644 --- a/mysql-test/t/mysqlbinlog.test +++ b/mysql-test/t/mysqlbinlog.test @@ -71,8 +71,7 @@ select "--- --position --" as ""; --enable_query_log --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR --replace_regex /SQL_LOAD_MB-[0-9]-[0-9]/SQL_LOAD_MB-#-#/ ---exec $MYSQL_BINLOG --short-form --local-load=$MYSQLTEST_VARDIR/tmp/ --position=330 $MYSQLD_DATADIR/master-bin.000002 - +--exec $MYSQL_BINLOG --short-form --local-load=$MYSQLTEST_VARDIR/tmp/ --position=331 $MYSQLD_DATADIR/master-bin.000002 # These are tests for remote binlog. # They should return the same as previous test. @@ -108,7 +107,7 @@ select "--- --position --" as ""; --enable_query_log --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR --replace_regex /SQL_LOAD_MB-[0-9]-[0-9]/SQL_LOAD_MB-#-#/ ---exec $MYSQL_BINLOG --short-form --local-load=$MYSQLTEST_VARDIR/tmp/ --read-from-remote-server --position=330 --user=root --host=127.0.0.1 --port=$MASTER_MYPORT master-bin.000002 +--exec $MYSQL_BINLOG --short-form --local-load=$MYSQLTEST_VARDIR/tmp/ --read-from-remote-server --position=331 --user=root --host=127.0.0.1 --port=$MASTER_MYPORT master-bin.000002 # Bug#7853 mysqlbinlog does not accept input from stdin --disable_query_log @@ -443,3 +442,27 @@ FLUSH LOGS; --echo End of 5.0 tests --echo End of 5.1 tests + +# +# BUG#38468 Memory leak detected when using mysqlbinlog utility; +# +disable_query_log; +RESET MASTER; +CREATE TABLE t1 SELECT 1; +FLUSH LOGS; +DROP TABLE t1; +enable_query_log; + +# Write an empty file for comparison +write_file $MYSQLTEST_VARDIR/tmp/mysqlbinlog.warn.empty; +EOF + +# Before fix of BUG#38468, this would generate some warnings +--exec $MYSQL_BINLOG $MYSQLD_DATADIR/master-bin.000001 >/dev/null 2> $MYSQLTEST_VARDIR/tmp/mysqlbinlog.warn + +# Make sure the command above does not generate any error or warnings +diff_files $MYSQLTEST_VARDIR/tmp/mysqlbinlog.warn $MYSQLTEST_VARDIR/tmp/mysqlbinlog.warn.empty; + +# Cleanup for this part of test +remove_file $MYSQLTEST_VARDIR/tmp/mysqlbinlog.warn.empty; +remove_file $MYSQLTEST_VARDIR/tmp/mysqlbinlog.warn; diff --git a/mysql-test/t/mysqlbinlog2.test b/mysql-test/t/mysqlbinlog2.test index d6be029ea56..6089b83e42d 100644 --- a/mysql-test/t/mysqlbinlog2.test +++ b/mysql-test/t/mysqlbinlog2.test @@ -50,15 +50,15 @@ select "--- offset --" as ""; --disable_query_log select "--- start-position --" as ""; --enable_query_log ---exec $MYSQL_BINLOG --short-form --start-position=608 $MYSQLD_DATADIR/master-bin.000001 +--exec $MYSQL_BINLOG --short-form --start-position=609 $MYSQLD_DATADIR/master-bin.000001 --disable_query_log select "--- stop-position --" as ""; --enable_query_log ---exec $MYSQL_BINLOG --short-form --stop-position=608 $MYSQLD_DATADIR/master-bin.000001 +--exec $MYSQL_BINLOG --short-form --stop-position=609 $MYSQLD_DATADIR/master-bin.000001 --disable_query_log select "--- start and stop positions ---" as ""; --enable_query_log ---exec $MYSQL_BINLOG --short-form --start-position=608 --stop-position 725 $MYSQLD_DATADIR/master-bin.000001 +--exec $MYSQL_BINLOG --short-form --start-position=609 --stop-position 726 $MYSQLD_DATADIR/master-bin.000001 --disable_query_log select "--- start-datetime --" as ""; --enable_query_log @@ -84,11 +84,11 @@ select "--- offset --" as ""; --disable_query_log select "--- start-position --" as ""; --enable_query_log ---exec $MYSQL_BINLOG --short-form --start-position=608 $MYSQLD_DATADIR/master-bin.000001 $MYSQLD_DATADIR/master-bin.000002 +--exec $MYSQL_BINLOG --short-form --start-position=609 $MYSQLD_DATADIR/master-bin.000001 $MYSQLD_DATADIR/master-bin.000002 --disable_query_log select "--- stop-position --" as ""; --enable_query_log ---exec $MYSQL_BINLOG --short-form --stop-position=134 $MYSQLD_DATADIR/master-bin.000001 $MYSQLD_DATADIR/master-bin.000002 +--exec $MYSQL_BINLOG --short-form --stop-position=135 $MYSQLD_DATADIR/master-bin.000001 $MYSQLD_DATADIR/master-bin.000002 --disable_query_log select "--- start-datetime --" as ""; --enable_query_log @@ -111,15 +111,15 @@ select "--- offset --" as ""; --disable_query_log select "--- start-position --" as ""; --enable_query_log ---exec $MYSQL_BINLOG --short-form --start-position=608 --read-from-remote-server --user=root --host=127.0.0.1 --port=$MASTER_MYPORT master-bin.000001 +--exec $MYSQL_BINLOG --short-form --start-position=609 --read-from-remote-server --user=root --host=127.0.0.1 --port=$MASTER_MYPORT master-bin.000001 --disable_query_log select "--- stop-position --" as ""; --enable_query_log ---exec $MYSQL_BINLOG --short-form --stop-position=608 --read-from-remote-server --user=root --host=127.0.0.1 --port=$MASTER_MYPORT master-bin.000001 +--exec $MYSQL_BINLOG --short-form --stop-position=609 --read-from-remote-server --user=root --host=127.0.0.1 --port=$MASTER_MYPORT master-bin.000001 --disable_query_log select "--- start and stop positions ---" as ""; --enable_query_log ---exec $MYSQL_BINLOG --short-form --start-position=608 --stop-position 725 --read-from-remote-server --user=root --host=127.0.0.1 --port=$MASTER_MYPORT master-bin.000001 +--exec $MYSQL_BINLOG --short-form --start-position=609 --stop-position 726 --read-from-remote-server --user=root --host=127.0.0.1 --port=$MASTER_MYPORT master-bin.000001 --disable_query_log select "--- start-datetime --" as ""; --enable_query_log @@ -142,11 +142,11 @@ select "--- offset --" as ""; --disable_query_log select "--- start-position --" as ""; --enable_query_log ---exec $MYSQL_BINLOG --short-form --start-position=608 --read-from-remote-server --user=root --host=127.0.0.1 --port=$MASTER_MYPORT master-bin.000001 master-bin.000002 +--exec $MYSQL_BINLOG --short-form --start-position=609 --read-from-remote-server --user=root --host=127.0.0.1 --port=$MASTER_MYPORT master-bin.000001 master-bin.000002 --disable_query_log select "--- stop-position --" as ""; --enable_query_log ---exec $MYSQL_BINLOG --short-form --stop-position=134 --read-from-remote-server --user=root --host=127.0.0.1 --port=$MASTER_MYPORT master-bin.000001 master-bin.000002 +--exec $MYSQL_BINLOG --short-form --stop-position=135 --read-from-remote-server --user=root --host=127.0.0.1 --port=$MASTER_MYPORT master-bin.000001 master-bin.000002 --disable_query_log select "--- start-datetime --" as ""; --enable_query_log diff --git a/mysql-test/t/mysqlcheck.test b/mysql-test/t/mysqlcheck.test index 986b5aba385..831aba72fb5 100644 --- a/mysql-test/t/mysqlcheck.test +++ b/mysql-test/t/mysqlcheck.test @@ -178,7 +178,7 @@ SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_SCHEMA="#mysql50#a@b" ORDER BY trigger_name; --echo mysqlcheck --fix-db-names --fix-table-names --all-databases ---exec $MYSQL_CHECK --fix-db-names --fix-table-names --all-databases +--exec $MYSQL_CHECK --default-character-set=utf8 --fix-db-names --fix-table-names --all-databases USE `a@b`; SELECT * FROM INFORMATION_SCHEMA.TRIGGERS diff --git a/mysql-test/t/outfile.test b/mysql-test/t/outfile.test index 45b13f141f7..9f2fc22da99 100644 --- a/mysql-test/t/outfile.test +++ b/mysql-test/t/outfile.test @@ -109,7 +109,7 @@ create user user_1@localhost; grant all on mysqltest.* to user_1@localhost; connect (con28181_1,localhost,user_1,,mysqltest); ---error ER_DBACCESS_DENIED_ERROR +--error ER_ACCESS_DENIED_ERROR eval select schema_name into outfile "../../tmp/outfile-test.4" fields terminated by ',' optionally enclosed by '"' diff --git a/mysql-test/t/partition.test b/mysql-test/t/partition.test index 0ff4b118426..f23cb73daf6 100644 --- a/mysql-test/t/partition.test +++ b/mysql-test/t/partition.test @@ -372,12 +372,12 @@ drop table t1; # # BUG 16002: Handle unsigned integer functions properly # ---error ER_PARSE_ERROR +--error ER_INCONSISTENT_TYPE_OF_FUNCTIONS_ERROR create table t1 (a bigint) partition by range (a) (partition p0 values less than (0xFFFFFFFFFFFFFFFF), partition p1 values less than (10)); ---error ER_PARSE_ERROR +--error ER_INCONSISTENT_TYPE_OF_FUNCTIONS_ERROR create table t1 (a bigint) partition by list (a) (partition p0 values in (0xFFFFFFFFFFFFFFFF), @@ -1410,7 +1410,7 @@ PARTITION BY LIST (a) SHOW CREATE TABLE t1; DROP TABLE t1; ---error ER_PARSE_ERROR +--error ER_NULL_IN_VALUES_LESS_THAN CREATE TABLE t1 (a int) PARTITION BY RANGE(a) (PARTITION p0 VALUES LESS THAN (NULL)); diff --git a/mysql-test/t/partition_column.test b/mysql-test/t/partition_column.test new file mode 100644 index 00000000000..9e47b94b036 --- /dev/null +++ b/mysql-test/t/partition_column.test @@ -0,0 +1,426 @@ +# +# Tests for the new column list partitioning introduced in second +# version for partitioning. +# +--source include/have_partition.inc + +--disable_warnings +drop table if exists t1; +--enable_warnings + +# +# BUG#49180, Possible to define empty intervals for column list partitioning +# +--error ER_RANGE_NOT_INCREASING_ERROR +create table t1 (a int, b int) +partition by range columns (a,b) +( partition p0 values less than (maxvalue, 10), + partition p1 values less than (maxvalue, maxvalue)); + +--error ER_RANGE_NOT_INCREASING_ERROR +create table t1 (a int, b int, c int) +partition by range columns (a,b,c) +( partition p0 values less than (1, maxvalue, 10), + partition p1 values less than (1, maxvalue, maxvalue)); + +# +# BUG#48161, Delivering too few records using collate syntax with partitions +# +# Test case from BUG#48447 with some extension +create table t1 (a varchar(1) character set latin1 collate latin1_general_ci) +partition by range columns(a) +( partition p0 values less than ('a'), + partition p1 values less than ('b'), + partition p2 values less than ('c'), + partition p3 values less than ('d')); +insert into t1 values ('A'),('a'),('B'),('b'),('C'),('c'); +select * from t1 where a > 'B' collate latin1_bin; +select * from t1 where a <> 'B' collate latin1_bin; +alter table t1 remove partitioning; +select * from t1 where a > 'B' collate latin1_bin; +select * from t1 where a <> 'B' collate latin1_bin; +drop table t1; + +# Test case from BUG#48161 +create table t1 (a varchar(2) character set latin1, + b varchar(2) character set latin1) +partition by list columns(a,b) +(partition p0 values in (('a','a'))); +insert into t1 values ('A','A'); +select * from t1 where b <> 'a' collate latin1_bin AND + a = 'A' collate latin1_bin; +alter table t1 remove partitioning; +select * from t1 where b <> 'a' collate latin1_bin AND + a = 'A' collate latin1_bin; +drop table t1; + +create table t1 (a varchar(5)) +partition by list columns(a) +( partition p0 values in ('\''), + partition p1 values in ('\\'), + partition p2 values in ('\0')); +show create table t1; +drop table t1; + +# +# BUG#48165, sql_mode gives error +# +set @@sql_mode=allow_invalid_dates; +--error ER_WRONG_TYPE_COLUMN_VALUE_ERROR +create table t1 (a char, b char, c date) +partition by range columns (a,b,c) +( partition p0 values less than (0,0,to_days('3000-11-31'))); + +--error ER_WRONG_TYPE_COLUMN_VALUE_ERROR +create table t1 (a char, b char, c date) +partition by range columns (a,b,c) +( partition p0 values less than (0,0,'3000-11-31')); +set @@sql_mode=''; + +--error ER_WRONG_TYPE_COLUMN_VALUE_ERROR +create table t1 (a int, b char(10), c varchar(25), d datetime) +partition by range columns(a,b,c,d) +subpartition by hash (to_seconds(d)) +subpartitions 4 +( partition p0 values less than (1, 0, MAXVALUE, '1900-01-01'), + partition p1 values less than (1, 'a', MAXVALUE, '1999-01-01'), + partition p2 values less than (1, 'a', MAXVALUE, MAXVALUE), + partition p3 values less than (1, MAXVALUE, MAXVALUE, MAXVALUE)); + +create table t1 (a int, b char(10), c varchar(25), d datetime) +partition by range columns(a,b,c,d) +subpartition by hash (to_seconds(d)) +subpartitions 4 +( partition p0 values less than (1, '0', MAXVALUE, '1900-01-01'), + partition p1 values less than (1, 'a', MAXVALUE, '1999-01-01'), + partition p2 values less than (1, 'b', MAXVALUE, MAXVALUE), + partition p3 values less than (1, MAXVALUE, MAXVALUE, MAXVALUE)); +select partition_method, partition_expression, partition_description + from information_schema.partitions where table_name = "t1"; +show create table t1; +drop table t1; + +--error ER_NULL_IN_VALUES_LESS_THAN +create table t1 (a int, b int) +partition by range columns (a,b) +(partition p0 values less than (NULL, maxvalue)); + +--error ER_MAXVALUE_IN_VALUES_IN, ER_PARSE_ERROR +create table t1 (a int, b int) +partition by list columns(a,b) +( partition p0 values in ((maxvalue, 0))); + +create table t1 (a int, b int) +partition by list columns (a,b) +( partition p0 values in ((0,0))); +--error ER_MAXVALUE_IN_VALUES_IN, ER_PARSE_ERROR +alter table t1 add partition +(partition p1 values in (maxvalue, maxvalue)); +drop table t1; +# +# BUG#47837, Crash when two same fields in column list processing +# +--error ER_SAME_NAME_PARTITION_FIELD +create table t1 (a int, b int) +partition by key (a,a); +--error ER_SAME_NAME_PARTITION_FIELD +create table t1 (a int, b int) +partition by list columns(a,a) +( partition p values in ((1,1))); + +# +# BUG#47838, List partitioning have problems with <= and >= +# +create table t1 (a int signed) +partition by list (a) +( partition p0 values in (1, 3, 5, 7, 9, NULL), + partition p1 values in (2, 4, 6, 8, 0)); +insert into t1 values (NULL),(0),(1),(2),(2),(4),(4),(4),(8),(8); +select * from t1 where NULL <= a; +select * from t1 where a is null; +explain partitions select * from t1 where a is null; +select * from t1 where a <= 1; +drop table t1; + +create table t1 (a int signed) +partition by list columns(a) +( partition p0 values in (1, 3, 5, 7, 9, NULL), + partition p1 values in (2, 4, 6, 8, 0)); +insert into t1 values (NULL),(0),(1),(2),(2),(4),(4),(4),(8),(8); +select * from t1 where a <= NULL; +select * from t1 where a is null; +explain partitions select * from t1 where a is null; +select * from t1 where a <= 1; +drop table t1; + +create table t1 (a int, b int) +partition by list columns(a,b) +( partition p0 values in ((1, NULL), (2, NULL), (NULL, NULL)), + partition p1 values in ((1,1), (2,2)), + partition p2 values in ((3, NULL), (NULL, 1))); +select partition_method, partition_expression, partition_description + from information_schema.partitions where table_name = "t1"; +show create table t1; +# +# BUG#47754 Crash when selecting using NOT BETWEEN for column list partitioning +# +insert into t1 values (3, NULL); +insert into t1 values (NULL, 1); +insert into t1 values (NULL, NULL); +insert into t1 values (1, NULL); +insert into t1 values (2, NULL); +insert into t1 values (1,1); +insert into t1 values (2,2); +select * from t1 where a = 1; +select * from t1 where a = 2; +select * from t1 where a > 8; +select * from t1 where a not between 8 and 8; +show create table t1; +drop table t1; + +--error ER_MULTIPLE_DEF_CONST_IN_LIST_PART_ERROR +create table t1 (a int) +partition by list (a) +( partition p0 values in (1), + partition p1 values in (1)); + +create table t1 (a int) +partition by list (a) +( partition p0 values in (2, 1), + partition p1 values in (4, NULL, 3)); +select partition_method, partition_expression, partition_description + from information_schema.partitions where table_name = "t1"; +show create table t1; +insert into t1 values (1); +insert into t1 values (2); +insert into t1 values (3); +insert into t1 values (4); +insert into t1 values (NULL); +--error ER_NO_PARTITION_FOR_GIVEN_VALUE +insert into t1 values (5); +drop table t1; + +--error ER_PARSE_ERROR +create table t1 (a int) +partition by list columns(a) +( partition p0 values in (2, 1), + partition p1 values in ((4), (NULL), (3))); + +create table t1 (a int) +partition by list columns(a) +( partition p0 values in (2, 1), + partition p1 values in (4, NULL, 3)); +select partition_method, partition_expression, partition_description + from information_schema.partitions where table_name = "t1"; +show create table t1; +insert into t1 values (1); +insert into t1 values (2); +insert into t1 values (3); +insert into t1 values (4); +insert into t1 values (NULL); +--error ER_NO_PARTITION_FOR_GIVEN_VALUE +insert into t1 values (5); +show create table t1; +drop table t1; + +create table t1 (a int, b char(10), c varchar(5), d int) +partition by range columns(a,b,c) +subpartition by key (c,d) +subpartitions 3 +( partition p0 values less than (1,'abc','abc'), + partition p1 values less than (2,'abc','abc'), + partition p2 values less than (3,'abc','abc'), + partition p3 values less than (4,'abc','abc')); +select partition_method, partition_expression, partition_description + from information_schema.partitions where table_name = "t1"; +show create table t1; + +insert into t1 values (1,'a','b',1),(2,'a','b',2),(3,'a','b',3); +insert into t1 values (1,'b','c',1),(2,'b','c',2),(3,'b','c',3); +insert into t1 values (1,'c','d',1),(2,'c','d',2),(3,'c','d',3); +insert into t1 values (1,'d','e',1),(2,'d','e',2),(3,'d','e',3); +select * from t1 where (a = 1 AND b < 'd' AND (c = 'b' OR (c = 'c' AND d = 1)) OR + (a = 1 AND b >= 'a' AND (c = 'c' OR (c = 'd' AND d = 2)))); +drop table t1; + +create table t1 (a int, b varchar(2), c int) +partition by range columns (a, b, c) +(partition p0 values less than (1, 'A', 1), + partition p1 values less than (1, 'B', 1)); +select partition_method, partition_expression, partition_description + from information_schema.partitions where table_name = "t1"; +show create table t1; +insert into t1 values (1, 'A', 1); +explain partitions select * from t1 where a = 1 AND b <= 'A' and c = 1; +select * from t1 where a = 1 AND b <= 'A' and c = 1; +drop table t1; + +create table t1 (a char, b char, c char) +partition by list columns(a) +( partition p0 values in ('a')); +insert into t1 (a) values ('a'); +select * from t1 where a = 'a'; +drop table t1; + +--error ER_WRONG_TYPE_COLUMN_VALUE_ERROR +create table t1 (d time) +partition by range columns(d) +( partition p0 values less than ('2000-01-01'), + partition p1 values less than ('2040-01-01')); + +--error ER_FIELD_TYPE_NOT_ALLOWED_AS_PARTITION_FIELD +create table t1 (d timestamp) +partition by range columns(d) +( partition p0 values less than ('2000-01-01'), + partition p1 values less than ('2040-01-01')); + +--error ER_FIELD_TYPE_NOT_ALLOWED_AS_PARTITION_FIELD +create table t1 (d bit(1)) +partition by range columns(d) +( partition p0 values less than (0), + partition p1 values less than (1)); + +create table t1 (a int, b int) +partition by range columns(a,b) +(partition p0 values less than (maxvalue, 10)); +drop table t1; + +create table t1 (d date) +partition by range columns(d) +( partition p0 values less than ('2000-01-01'), + partition p1 values less than ('2009-01-01')); +drop table t1; + +create table t1 (d date) +partition by range columns(d) +( partition p0 values less than ('1999-01-01'), + partition p1 values less than ('2000-01-01')); +drop table t1; + +create table t1 (d date) +partition by range columns(d) +( partition p0 values less than ('2000-01-01'), + partition p1 values less than ('3000-01-01')); +drop table t1; + +create table t1 (a int, b int) +partition by range columns(a,b) +(partition p2 values less than (99,99), + partition p1 values less than (99,999)); + +insert into t1 values (99,998); +select * from t1 where b = 998; +drop table t1; + +create table t1 as select to_seconds(null) as to_seconds; +select data_type from information_schema.columns +where column_name='to_seconds'; +drop table t1; + +--error ER_PARSE_ERROR +create table t1 (a int, b int) +partition by list columns(a,b) +(partition p0 values in ((maxvalue,maxvalue))); +create table t1 (a int, b int) +partition by range columns(a,b) +(partition p0 values less than (maxvalue,maxvalue)); +drop table t1; + +create table t1 (a int) +partition by list columns(a) +(partition p0 values in (0)); +select partition_method from information_schema.partitions where table_name='t1'; +drop table t1; + +create table t1 (a char(6)) +partition by range columns(a) +(partition p0 values less than ('H23456'), + partition p1 values less than ('M23456')); +insert into t1 values ('F23456'); +select * from t1; +drop table t1; + +-- error 1054 +create table t1 (a char(6)) +partition by range columns(a) +(partition p0 values less than (H23456), + partition p1 values less than (M23456)); + +-- error ER_WRONG_TYPE_COLUMN_VALUE_ERROR +create table t1 (a char(6)) +partition by range columns(a) +(partition p0 values less than (23456), + partition p1 values less than (23456)); + +-- error 1064 +create table t1 (a int, b int) +partition by range columns(a,b) +(partition p0 values less than (10)); + +-- error ER_PARTITION_COLUMN_LIST_ERROR +create table t1 (a int, b int) +partition by range columns(a,b) +(partition p0 values less than (1,1,1); + +create table t1 (a int, b int) +partition by range columns(a,b) +(partition p0 values less than (1, 0), + partition p1 values less than (2, maxvalue), + partition p2 values less than (3, 3), + partition p3 values less than (10, maxvalue)); + +-- error ER_NO_PARTITION_FOR_GIVEN_VALUE +insert into t1 values (11,0); +insert into t1 values (0,1),(1,1),(2,1),(3,1),(3,4),(4,9),(9,1); +select * from t1; + +alter table t1 +partition by range columns(b,a) +(partition p0 values less than (1,2), + partition p1 values less than (3,3), + partition p2 values less than (9,5)); +explain partitions select * from t1 where b < 2; +select * from t1 where b < 2; +explain partitions select * from t1 where b < 4; +select * from t1 where b < 4; + +alter table t1 reorganize partition p1 into +(partition p11 values less than (2,2), + partition p12 values less than (3,3)); + +-- error ER_REORG_OUTSIDE_RANGE +alter table t1 reorganize partition p0 into +(partition p01 values less than (0,3), + partition p02 values less than (1,1)); + +-- error ER_PARTITION_COLUMN_LIST_ERROR +alter table t1 reorganize partition p2 into +(partition p2 values less than(9,6,1)); + +-- error ER_PARTITION_COLUMN_LIST_ERROR +alter table t1 reorganize partition p2 into +(partition p2 values less than (10)); + +alter table t1 reorganize partition p2 into +(partition p21 values less than (4,7), + partition p22 values less than (9,5)); +explain partitions select * from t1 where b < 4; +select * from t1 where b < 4; +drop table t1; + +create table t1 (a int, b int) +partition by list columns(a,b) +subpartition by hash (b) +subpartitions 2 +(partition p0 values in ((0,0), (1,1)), + partition p1 values in ((1000,1000))); +insert into t1 values (1000,1000); +#select * from t1 where a = 0 and b = 0; +drop table t1; + +create table t1 (a char, b char, c char) +partition by range columns(a,b,c) +( partition p0 values less than ('a','b','c')); +alter table t1 add partition +(partition p1 values less than ('b','c','d')); +drop table t1; diff --git a/mysql-test/t/partition_column_prune.test b/mysql-test/t/partition_column_prune.test new file mode 100644 index 00000000000..848825ba71b --- /dev/null +++ b/mysql-test/t/partition_column_prune.test @@ -0,0 +1,71 @@ +# +# Partition pruning tests for new COLUMN LIST feature +# +-- source include/have_partition.inc + +--disable_warnings +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; +--enable_warnings + +create table t1 (a char, b char, c char) +partition by range columns(a,b,c) +( partition p0 values less than ('a','b','c')); +insert into t1 values ('a', NULL, 'd'); +explain partitions select * from t1 where a = 'a' AND c = 'd'; +select * from t1 where a = 'a' AND c = 'd'; +drop table t1; + +## COLUMN_LIST partition pruning tests +create table t1 (a int not null) partition by range columns(a) ( + partition p0 values less than (10), + partition p1 values less than (20), + partition p2 values less than (30), + partition p3 values less than (40), + partition p4 values less than (50), + partition p5 values less than (60), + partition p6 values less than (70) +); +insert into t1 values (5),(15),(25),(35),(45),(55),(65); +insert into t1 values (5),(15),(25),(35),(45),(55),(65); + +create table t2 (a int not null) partition by range(a) ( + partition p0 values less than (10), + partition p1 values less than (20), + partition p2 values less than (30), + partition p3 values less than (40), + partition p4 values less than (50), + partition p5 values less than (60), + partition p6 values less than (70) +); +insert into t2 values (5),(15),(25),(35),(45),(55),(65); +insert into t2 values (5),(15),(25),(35),(45),(55),(65); + +explain partitions select * from t1 where a > 35 and a < 45; +explain partitions select * from t2 where a > 35 and a < 45; + +drop table t1, t2; + +create table t1 (a int not null, b int not null ) +partition by range columns(a,b) ( + partition p01 values less than (2,10), + partition p02 values less than (2,20), + partition p03 values less than (2,30), + + partition p11 values less than (4,10), + partition p12 values less than (4,20), + partition p13 values less than (4,30), + + partition p21 values less than (6,10), + partition p22 values less than (6,20), + partition p23 values less than (6,30) +); + +insert into t1 values (2,5), (2,15), (2,25), + (4,5), (4,15), (4,25), (6,5), (6,15), (6,25); +insert into t1 select * from t1; + +explain partitions select * from t1 where a=2; +explain partitions select * from t1 where a=4; +explain partitions select * from t1 where a=2 and b < 22; + +drop table t1; diff --git a/mysql-test/t/partition_datatype.test b/mysql-test/t/partition_datatype.test index 7440a9bf3a3..0a9fae15354 100644 --- a/mysql-test/t/partition_datatype.test +++ b/mysql-test/t/partition_datatype.test @@ -4,6 +4,7 @@ # as partition by key # Created to verify the fix for Bug#31705 # Partitions: crash if varchar length > 65530 +# BUG#48164 limited size to 3072 bytes # -- source include/have_partition.inc @@ -192,27 +193,29 @@ create table t1 (a set('y','n')) partition by key (a); insert into t1 values ('y'); select * from t1 where a = 'y'; drop table t1; -create table t1 (a varchar(65531)) partition by key (a); +create table t1 (a varchar(3068)) partition by key (a); insert into t1 values ('bbbb'); insert into t1 values ('aaaa'); select * from t1 where a = 'aaaa'; select * from t1 where a like 'aaa%'; select * from t1 where a = 'bbbb'; drop table t1; -create table t1 (a varchar(65532)) partition by key (a); +create table t1 (a varchar(3069)) partition by key (a); insert into t1 values ('bbbb'); insert into t1 values ('aaaa'); select * from t1 where a = 'aaaa'; select * from t1 where a like 'aaa%'; select * from t1 where a = 'bbbb'; drop table t1; -create table t1 (a varchar(65533) not null) partition by key (a); +create table t1 (a varchar(3070) not null) partition by key (a); insert into t1 values ('bbbb'); insert into t1 values ('aaaa'); select * from t1 where a = 'aaaa'; select * from t1 where a like 'aaa%'; select * from t1 where a = 'bbbb'; drop table t1; +-- error ER_PARTITION_FIELDS_TOO_LONG +create table t1 (a varchar(3070)) partition by key (a); -- error ER_TOO_BIG_ROWSIZE create table t1 (a varchar(65533)) partition by key (a); -- error ER_TOO_BIG_ROWSIZE diff --git a/mysql-test/t/partition_error.test b/mysql-test/t/partition_error.test index 49632f95dfb..eb7a4942f5b 100644 --- a/mysql-test/t/partition_error.test +++ b/mysql-test/t/partition_error.test @@ -180,7 +180,7 @@ partitions 3 (partition x1, partition x2); # -# Partition by key specified 3 partitions but only defined 2 => error +# Partition by hash, random function # --error 1064 CREATE TABLE t1 ( @@ -193,7 +193,7 @@ partitions 2 (partition x1, partition x2); # -# Partition by key specified 3 partitions but only defined 2 => error +# Partition by range, random function # --error 1064 CREATE TABLE t1 ( @@ -206,7 +206,7 @@ partitions 2 (partition x1 values less than (0), partition x2 values less than (2)); # -# Partition by key specified 3 partitions but only defined 2 => error +# Partition by list, random function # --error 1064 CREATE TABLE t1 ( @@ -452,7 +452,7 @@ partitions 2 # # Partition by range, inconsistent partition function and constants # ---error 1064 +--error ER_INCONSISTENT_TYPE_OF_FUNCTIONS_ERROR CREATE TABLE t1 ( a int not null, b int not null, @@ -522,7 +522,7 @@ partitions 2 # # Partition by range, missing parenthesis # ---error 1064 +--error ER_PARTITION_WRONG_VALUES_ERROR CREATE TABLE t1 ( a int not null, b int not null, @@ -536,7 +536,7 @@ partitions 2 # # Partition by range, maxvalue in wrong place # ---error 1064 +--error ER_PARTITION_MAXVALUE_ERROR CREATE TABLE t1 ( a int not null, b int not null, @@ -550,7 +550,7 @@ partitions 2 # # Partition by range, maxvalue in several places # ---error 1064 +--error ER_PARTITION_MAXVALUE_ERROR CREATE TABLE t1 ( a int not null, b int not null, @@ -765,7 +765,7 @@ partitions 2 # # Partition by list, wrong constant result type (not INT) # ---error 1064 +--error ER_INCONSISTENT_TYPE_OF_FUNCTIONS_ERROR CREATE TABLE t1 ( a int not null, b int not null, diff --git a/mysql-test/t/partition_innodb.test b/mysql-test/t/partition_innodb.test index 36e3edf05aa..2e08834cfc7 100644 --- a/mysql-test/t/partition_innodb.test +++ b/mysql-test/t/partition_innodb.test @@ -6,6 +6,51 @@ drop table if exists t1; --enable_warnings # +# BUG#47774, Assertion failure in InnoDB using column list partitioning +# +create table t1 (a varchar(5), b int signed, c varchar(10), d datetime) +partition by range columns(b,c) +subpartition by hash(to_seconds(d)) +( partition p0 values less than (2, 'b'), + partition p1 values less than (4, 'd'), + partition p2 values less than (10, 'za')); +insert into t1 values ('a', 3, 'w', '2001-10-27 04:34:00'); +insert into t1 values ('r', 7, 'w', '2001-10-27 05:34:00'); +insert into t1 values ('g', 10, 'w', '2001-10-27 06:34:00'); +update t1 set a = 'c' where a > 'f'; +drop table t1; + +# +# BUG#47776, Failed to update for MEMORY engine, crash for InnoDB and success for MyISAM +# +create table t1 (a varchar(5)) +engine=memory +partition by range columns(a) +( partition p0 values less than ('m'), + partition p1 values less than ('za')); +insert into t1 values ('j'); +update t1 set a = 'z' where (a >= 'j'); +drop table t1; + +create table t1 (a varchar(5)) +engine=myisam +partition by range columns(a) +( partition p0 values less than ('m'), + partition p1 values less than ('za')); +insert into t1 values ('j'); +update t1 set a = 'z' where (a >= 'j'); +drop table t1; + +create table t1 (a varchar(5)) +engine=innodb +partition by range columns(a) +( partition p0 values less than ('m'), + partition p1 values less than ('za')); +insert into t1 values ('j'); +update t1 set a = 'z' where (a >= 'j'); +drop table t1; + +# # Bug#47029: Crash when reorganize partition with subpartition # create table t1 (a int not null, diff --git a/mysql-test/t/partition_list.test b/mysql-test/t/partition_list.test index 1c76de9d55a..8d2ec88e0f4 100644 --- a/mysql-test/t/partition_list.test +++ b/mysql-test/t/partition_list.test @@ -40,6 +40,8 @@ subpartitions 2 partition p1 values in (1), partition pnull values in (null, 2), partition p3 values in (3)); +select partition_method, partition_expression, partition_description + from information_schema.partitions where table_name = "t1"; insert into t1 values (0,0),(0,1),(1,0),(1,1),(null,0),(null,1); insert into t1 values (2,0),(2,1),(3,0),(3,1); diff --git a/mysql-test/t/partition_mgm_err.test b/mysql-test/t/partition_mgm_err.test index 0f8b8d3cd90..f921fa8ebca 100644 --- a/mysql-test/t/partition_mgm_err.test +++ b/mysql-test/t/partition_mgm_err.test @@ -61,7 +61,7 @@ ALTER TABLE t1 REORGANIZE PARTITION x0, x1, x1 INTO ALTER TABLE t1 REORGANIZE PARTITION x0,x1 INTO (PARTITION x01 VALUES LESS THAN (5)); ---error ER_REORG_OUTSIDE_RANGE +--error ER_RANGE_NOT_INCREASING_ERROR ALTER TABLE t1 REORGANIZE PARTITION x0,x1 INTO (PARTITION x01 VALUES LESS THAN (4), PARTITION x11 VALUES LESS THAN (2)); diff --git a/mysql-test/t/partition_pruning.test b/mysql-test/t/partition_pruning.test index 11e65be45fc..fd3f7f2c322 100644 --- a/mysql-test/t/partition_pruning.test +++ b/mysql-test/t/partition_pruning.test @@ -51,6 +51,26 @@ INSERT INTO t1 VALUES ('0000-00-00'), ('0000-01-02'), ('0001-01-01'), ALTER TABLE t1 DROP KEY a; --source include/partition_date_range.inc DROP TABLE t1; +--echo # TO_SECONDS, test of LIST and index +CREATE TABLE t1 (a DATE, KEY(a)) +PARTITION BY LIST (TO_SECONDS(a)) +(PARTITION `p0001-01-01` VALUES IN (TO_SECONDS('0001-01-01')), + PARTITION `p2001-01-01` VALUES IN (TO_SECONDS('2001-01-01')), + PARTITION `pNULL` VALUES IN (NULL), + PARTITION `p0000-01-02` VALUES IN (TO_SECONDS('0000-01-02')), + PARTITION `p1001-01-01` VALUES IN (TO_SECONDS('1001-01-01'))); +if ($verify_without_partitions) +{ +ALTER TABLE t1 REMOVE PARTITIONING; +} +INSERT INTO t1 VALUES ('0000-00-00'), ('0000-01-02'), ('0001-01-01'), + ('1001-00-00'), ('1001-01-01'), ('1002-00-00'), ('2001-01-01'); +--source include/partition_date_range.inc +--echo # test without index +ALTER TABLE t1 DROP KEY a; +--source include/partition_date_range.inc +DROP TABLE t1; + # # Bug#46362: Endpoint should be set to false for TO_DAYS(DATE) @@ -1159,3 +1179,22 @@ INSERT INTO t1 VALUES ('2006-03-01 12:00:00'); -- echo must use p0 only: explain partitions select * from t1 where recdate < '2006-01-01 00:00:00'; drop table t1; + +-- echo # +-- echo # BUG#33730 Full table scan instead selected partitions for query more than 10 partitions +-- echo # +create table t0 (a int); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t1 (a int) + partition by range(a+0) ( + partition p0 values less than (64), + partition p1 values less than (128), + partition p2 values less than (255) +); +insert into t1 select A.a + 10*B.a from t0 A, t0 B; + +# this will use interval_via_walking +explain partitions select * from t1 where a between 10 and 13; +explain partitions select * from t1 where a between 10 and 10+33; + +drop table t0, t1; diff --git a/mysql-test/t/partition_range.test b/mysql-test/t/partition_range.test index c02d9049f2e..07c345faed5 100644 --- a/mysql-test/t/partition_range.test +++ b/mysql-test/t/partition_range.test @@ -9,6 +9,78 @@ drop table if exists t1, t2; --enable_warnings +--error ER_NULL_IN_VALUES_LESS_THAN +create table t1 (a int) +partition by range (a) +( partition p0 values less than (NULL), + partition p1 values less than (MAXVALUE)); +# +# Merge fix of bug#27927 for TO_SECONDS function +# +create table t1 (a datetime not null) +partition by range (TO_SECONDS(a)) +( partition p0 VALUES LESS THAN (TO_SECONDS('2007-03-08 00:00:00')), + partition p1 VALUES LESS THAN (TO_SECONDS('2007-04-01 00:00:00'))); +select partition_method, partition_expression, partition_description + from information_schema.partitions where table_name = "t1"; +INSERT INTO t1 VALUES ('2007-03-01 12:00:00'), ('2007-03-07 12:00:00'); +INSERT INTO t1 VALUES ('2007-03-08 12:00:00'), ('2007-03-15 12:00:00'); +explain partitions select * from t1 where a < '2007-03-08 00:00:00'; +explain partitions select * from t1 where a < '2007-03-08 00:00:01'; +explain partitions select * from t1 where a <= '2007-03-08 00:00:00'; +explain partitions select * from t1 where a <= '2007-03-07 23:59:59'; +explain partitions select * from t1 where a < '2007-03-07 23:59:59'; +drop table t1; +# +# New test cases for new function to_seconds +# +create table t1 (a date) +partition by range(to_seconds(a)) +(partition p0 values less than (to_seconds('2004-01-01')), + partition p1 values less than (to_seconds('2005-01-01'))); +insert into t1 values ('2003-12-30'),('2004-12-31'); +select * from t1; +explain partitions select * from t1 where a <= '2003-12-31'; +select * from t1 where a <= '2003-12-31'; +explain partitions select * from t1 where a <= '2005-01-01'; +select * from t1 where a <= '2005-01-01'; +drop table t1; + +create table t1 (a datetime) +partition by range(to_seconds(a)) +(partition p0 values less than (to_seconds('2004-01-01 12:00:00')), + partition p1 values less than (to_seconds('2005-01-01 12:00:00'))); +insert into t1 values ('2004-01-01 11:59:29'),('2005-01-01 11:59:59'); +select * from t1; +explain partitions select * from t1 where a <= '2004-01-01 11:59.59'; +select * from t1 where a <= '2004-01-01 11:59:59'; +explain partitions select * from t1 where a <= '2005-01-01'; +select * from t1 where a <= '2005-01-01'; +drop table t1; + +# +# Adding new test cases for column list variant for partitioning +# +--error 1064 +create table t1 (a int, b char(20)) +partition by range columns(a,b) +(partition p0 values less than (1)); + +--error ER_TOO_MANY_VALUES_ERROR +create table t1 (a int, b char(20)) +partition by range(a) +(partition p0 values less than (1,"b")); + +--error ER_TOO_MANY_VALUES_ERROR +create table t1 (a int, b char(20)) +partition by range(a) +(partition p0 values less than (1,"b")); + +create table t1 (a int, b char(20)) +partition by range columns(b) +(partition p0 values less than ("b")); +drop table t1; + # # BUG 33429: Succeeds in adding partition when maxvalue on last partition # diff --git a/mysql-test/t/partition_truncate.test b/mysql-test/t/partition_truncate.test new file mode 100644 index 00000000000..93b9cf62d14 --- /dev/null +++ b/mysql-test/t/partition_truncate.test @@ -0,0 +1,26 @@ +# +# Simple tests to verify truncate partition syntax +# +--source include/have_partition.inc +--disable_warnings +drop table if exists t1, t2, t3, t4; +--enable_warnings + +create table t1 (a int) +partition by list (a) +(partition p1 values in (0)); +--error ER_WRONG_PARTITION_NAME +alter table t1 truncate partition p1,p1; +--error ER_WRONG_PARTITION_NAME +alter table t1 truncate partition p0; +drop table t1; + +create table t1 (a int) +partition by list (a) +subpartition by hash (a) +subpartitions 1 +(partition p1 values in (1) + (subpartition sp1)); +--error ER_WRONG_PARTITION_NAME +alter table t1 truncate partition sp1; +drop table t1; diff --git a/mysql-test/t/partition_utf8.test b/mysql-test/t/partition_utf8.test new file mode 100644 index 00000000000..d3ad7ba671e --- /dev/null +++ b/mysql-test/t/partition_utf8.test @@ -0,0 +1,42 @@ +# Tests for Column list which requires utf8 output +--source include/have_partition.inc +set names utf8; +create table t1 (a varchar(2) character set cp1250) +partition by list columns (a) +( partition p0 values in (0x81)); +show create table t1; +drop table t1; +create table t1 (a varchar(2) character set cp1250) +partition by list columns (a) +( partition p0 values in (0x80)); +show create table t1; +drop table t1; + +# +# BUG#48164, too long partition fields causes crash +# +--error ER_PARTITION_FIELDS_TOO_LONG +create table t1 (a varchar(1500), b varchar(1570)) +partition by list columns(a,b) +( partition p0 values in (('a','b'))); + +create table t1 (a varchar(1023) character set utf8 collate utf8_spanish2_ci) +partition by range columns(a) +( partition p0 values less than ('CZ'), + partition p1 values less than ('CH'), + partition p2 values less than ('D')); +insert into t1 values ('czz'),('chi'),('ci'),('cg'); +select * from t1 where a between 'cg' AND 'ci'; +drop table t1; + +# +# BUG#48163, Dagger in UCS2 not working as partition value +# +create table t1 (a varchar(2) character set ucs2) +partition by list columns (a) +(partition p0 values in (0x2020), + partition p1 values in ('')); +show create table t1; +insert into t1 values (''); +insert into t1 values (_ucs2 0x2020); +drop table t1; diff --git a/mysql-test/t/plugin.test b/mysql-test/t/plugin.test index 788a7b336ef..0bf86b47dd7 100644 --- a/mysql-test/t/plugin.test +++ b/mysql-test/t/plugin.test @@ -22,6 +22,12 @@ SELECT * FROM t1; DROP TABLE t1; +# a couple of tests for variables +set global example_ulong_var=500; +set global example_enum_var= e1; +show status like 'example%'; +show variables like 'example%'; + UNINSTALL PLUGIN example; --error 1305 UNINSTALL PLUGIN EXAMPLE; diff --git a/mysql-test/t/profiling.test b/mysql-test/t/profiling.test index 275c2d77c4a..afbce04b966 100644 --- a/mysql-test/t/profiling.test +++ b/mysql-test/t/profiling.test @@ -1,4 +1,4 @@ ---source include/have_community_features.inc +--source include/have_profiling.inc # Verify that the protocol isn't violated if we ask for profiling info # before profiling has recorded anything. diff --git a/mysql-test/t/ps.test b/mysql-test/t/ps.test index db5994d434b..844be582290 100644 --- a/mysql-test/t/ps.test +++ b/mysql-test/t/ps.test @@ -3009,5 +3009,209 @@ execute stmt; drop table t1; deallocate prepare stmt; +########################################################################### +--echo --echo End of 5.1 tests. + +########################################################################### + +--echo +--echo # +--echo # WL#4435: Support OUT-parameters in prepared statements. +--echo # +--echo + +# The idea of this test case is to check that +# - OUT-parameters of four allowed types (string, double, int, decimal) work +# properly; +# - INOUT and OUT parameters work properly; +# - A mix of IN and OUT parameters work properly; + +--disable_warnings +DROP PROCEDURE IF EXISTS p_string; +DROP PROCEDURE IF EXISTS p_double; +DROP PROCEDURE IF EXISTS p_int; +DROP PROCEDURE IF EXISTS p_decimal; +--enable_warnings + +delimiter |; + +--echo +CREATE PROCEDURE p_string( + IN v0 INT, + OUT v1 CHAR(32), + IN v2 CHAR(32), + INOUT v3 CHAR(32)) +BEGIN + SET v0 = -1; + SET v1 = 'test_v1'; + SET v2 = 'n/a'; + SET v3 = 'test_v3'; +END| + +--echo +CREATE PROCEDURE p_double( + IN v0 INT, + OUT v1 DOUBLE(4, 2), + IN v2 DOUBLE(4, 2), + INOUT v3 DOUBLE(4, 2)) +BEGIN + SET v0 = -1; + SET v1 = 12.34; + SET v2 = 98.67; + SET v3 = 56.78; +END| + +--echo +CREATE PROCEDURE p_int( + IN v0 CHAR(10), + OUT v1 INT, + IN v2 INT, + INOUT v3 INT) +BEGIN + SET v0 = 'n/a'; + SET v1 = 1234; + SET v2 = 9876; + SET v3 = 5678; +END| + +--echo +CREATE PROCEDURE p_decimal( + IN v0 INT, + OUT v1 DECIMAL(4, 2), + IN v2 DECIMAL(4, 2), + INOUT v3 DECIMAL(4, 2)) +BEGIN + SET v0 = -1; + SET v1 = 12.34; + SET v2 = 98.67; + SET v3 = 56.78; +END| + +delimiter ;| + +--echo +PREPARE stmt_str FROM 'CALL p_string(?, ?, ?, ?)'; +PREPARE stmt_dbl FROM 'CALL p_double(?, ?, ?, ?)'; +PREPARE stmt_int FROM 'CALL p_int(?, ?, ?, ?)'; +PREPARE stmt_dec FROM 'CALL p_decimal(?, ?, ?, ?)'; + +--echo +SET @x_str_1 = NULL; +SET @x_str_2 = NULL; +SET @x_str_3 = NULL; +SET @x_dbl_1 = NULL; +SET @x_dbl_2 = NULL; +SET @x_dbl_3 = NULL; +SET @x_int_1 = NULL; +SET @x_int_2 = NULL; +SET @x_int_3 = NULL; +SET @x_dec_1 = NULL; +SET @x_dec_2 = NULL; +SET @x_dec_3 = NULL; + +--echo +--echo -- Testing strings... + +--echo +EXECUTE stmt_str USING @x_int_1, @x_str_1, @x_str_2, @x_str_3; +SELECT @x_int_1, @x_str_1, @x_str_2, @x_str_3; + +--echo +EXECUTE stmt_str USING @x_int_1, @x_str_1, @x_str_2, @x_str_3; +SELECT @x_int_1, @x_str_1, @x_str_2, @x_str_3; + +--echo +--echo -- Testing doubles... + +--echo +EXECUTE stmt_dbl USING @x_int_1, @x_dbl_1, @x_dbl_2, @x_dbl_3; +SELECT @x_int_1, @x_dbl_1, @x_dbl_2, @x_dbl_3; + +--echo +EXECUTE stmt_dbl USING @x_int_1, @x_dbl_1, @x_dbl_2, @x_dbl_3; +SELECT @x_int_1, @x_dbl_1, @x_dbl_2, @x_dbl_3; + +--echo +--echo -- Testing ints... + +--echo +EXECUTE stmt_int USING @x_str_1, @x_int_1, @x_int_2, @x_int_3; +SELECT @x_str_1, @x_int_1, @x_int_2, @x_int_3; + +--echo +EXECUTE stmt_int USING @x_str_1, @x_int_1, @x_int_2, @x_int_3; +SELECT @x_str_1, @x_int_1, @x_int_2, @x_int_3; + +--echo +--echo -- Testing decs... + +--echo +EXECUTE stmt_dec USING @x_int_1, @x_dec_1, @x_dec_2, @x_dec_3; +SELECT @x_int_1, @x_dec_1, @x_dec_2, @x_dec_3; + +--echo +EXECUTE stmt_dec USING @x_int_1, @x_dec_1, @x_dec_2, @x_dec_3; +SELECT @x_int_1, @x_dec_1, @x_dec_2, @x_dec_3; + +--echo +DEALLOCATE PREPARE stmt_str; +DEALLOCATE PREPARE stmt_dbl; +DEALLOCATE PREPARE stmt_int; +DEALLOCATE PREPARE stmt_dec; + +--echo +DROP PROCEDURE p_string; +DROP PROCEDURE p_double; +DROP PROCEDURE p_int; +DROP PROCEDURE p_decimal; + +# +# Another test case for WL#4435: check out parameters in Dynamic SQL. +# + +--echo +--disable_warnings +DROP PROCEDURE IF EXISTS p1; +DROP PROCEDURE IF EXISTS p2; +--enable_warnings + +--echo + +CREATE PROCEDURE p1(OUT v1 CHAR(10)) + SET v1 = 'test1'; + +--echo + +delimiter |; +CREATE PROCEDURE p2(OUT v2 CHAR(10)) +BEGIN + SET @query = 'CALL p1(?)'; + PREPARE stmt1 FROM @query; + EXECUTE stmt1 USING @u1; + DEALLOCATE PREPARE stmt1; + + SET v2 = @u1; +END| +delimiter ;| + +--echo + +CALL p2(@a); +SELECT @a; + +--echo + +DROP PROCEDURE p1; +DROP PROCEDURE p2; + +--echo +--echo # End of WL#4435. + +########################################################################### + +--echo +--echo End of 6.0 tests. + +########################################################################### diff --git a/mysql-test/t/query_cache_disabled-master.opt b/mysql-test/t/query_cache_disabled-master.opt new file mode 100644 index 00000000000..d7d47164883 --- /dev/null +++ b/mysql-test/t/query_cache_disabled-master.opt @@ -0,0 +1 @@ +--query_cache_type=0 diff --git a/mysql-test/t/query_cache_disabled.test b/mysql-test/t/query_cache_disabled.test new file mode 100644 index 00000000000..cbc98bd94d6 --- /dev/null +++ b/mysql-test/t/query_cache_disabled.test @@ -0,0 +1,15 @@ +-- source include/have_query_cache.inc +# +# Bug#38551 query cache can still consume [very little] cpu time even when it is off. +# +SHOW GLOBAL VARIABLES LIKE 'query_cache_type'; +--error ER_QUERY_CACHE_DISABLED +SET GLOBAL query_cache_type=ON; +--error ER_QUERY_CACHE_DISABLED +SET GLOBAL query_cache_type=DEMAND; +--error ER_QUERY_CACHE_DISABLED +SET GLOBAL query_cache_type=OFF; +SET GLOBAL query_cache_size=1024*1024; +SHOW GLOBAL VARIABLES LIKE 'query_cache_size'; +SET GLOBAL query_cache_size=0; + diff --git a/mysql-test/t/show_check.test b/mysql-test/t/show_check.test index 0ce807ae73e..0e4af16dd1f 100644 --- a/mysql-test/t/show_check.test +++ b/mysql-test/t/show_check.test @@ -629,7 +629,6 @@ show plugins; show columns in t1; show slave hosts; show keys in t1; -show column types; show table types; show storage engines; show authors; diff --git a/mysql-test/t/signal.test b/mysql-test/t/signal.test new file mode 100644 index 00000000000..bdb6625ba32 --- /dev/null +++ b/mysql-test/t/signal.test @@ -0,0 +1,2685 @@ +# Copyright (C) 2008 Sun Microsystems, Inc +# +# This program is free software; you can redistribute it and/or modify +# it under the terms of the GNU General Public License as published by +# the Free Software Foundation; version 2 of the License. +# +# This program is distributed in the hope that it will be useful, +# but WITHOUT ANY WARRANTY; without even the implied warranty of +# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +# GNU General Public License for more details. +# +# You should have received a copy of the GNU General Public License +# along with this program; if not, write to the Free Software +# Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA + +# Tests for SIGNAL and RESIGNAL + +--echo # +--echo # PART 1: syntax +--echo # + +--echo # +--echo # Test every new reserved and non reserved keywords +--echo # + +--disable_warnings +drop table if exists signal_non_reserved; +--enable_warnings + +create table signal_non_reserved ( + class_origin int, + subclass_origin int, + constraint_catalog int, + constraint_schema int, + constraint_name int, + catalog_name int, + schema_name int, + table_name int, + column_name int, + cursor_name int, + message_text int, + sqlcode int +); + +drop table signal_non_reserved; + +--disable_warnings +drop table if exists diag_non_reserved; +--enable_warnings + +create table diag_non_reserved ( + diagnostics int, + current int, + stacked int, + exception int +); + +drop table diag_non_reserved; + +--disable_warnings +drop table if exists diag_cond_non_reserved; +--enable_warnings + +create table diag_cond_non_reserved ( + condition_identifier int, + condition_number int, + condition_name int, + connection_name int, + message_length int, + message_octet_length int, + parameter_mode int, + parameter_name int, + parameter_ordinal_position int, + returned_sqlstate int, + routine_catalog int, + routine_name int, + routine_schema int, + server_name int, + specific_name int, + trigger_catalog int, + trigger_name int, + trigger_schema int +); + +drop table diag_cond_non_reserved; + +--disable_warnings +drop table if exists diag_stmt_non_reserved; +--enable_warnings + +create table diag_stmt_non_reserved ( + number int, + more int, + command_function int, + command_function_code int, + dynamic_function int, + dynamic_function_code int, + row_count int, + transactions_committed int, + transactions_rolled_back int, + transaction_active int +); + +drop table diag_stmt_non_reserved; + +--disable_warnings +drop table if exists test_reserved; +--enable_warnings + +--error ER_PARSE_ERROR +create table test_reserved (signal int); + +--error ER_PARSE_ERROR +create table test_reserved (resignal int); + +--error ER_PARSE_ERROR +create table test_reserved (condition int); + +--echo # +--echo # Test the SIGNAL syntax +--echo # + +--disable_warnings +drop procedure if exists test_invalid; +drop procedure if exists test_signal_syntax; +drop function if exists test_signal_func; +--enable_warnings + +delimiter $$; + +--error ER_PARSE_ERROR +create procedure test_invalid() +begin + SIGNAL; +end $$ + +--error ER_SP_COND_MISMATCH +create procedure test_invalid() +begin + SIGNAL foo; +end $$ + +--error ER_SIGNAL_BAD_CONDITION_TYPE +create procedure test_invalid() +begin + DECLARE foo CONDITION FOR 1234; + SIGNAL foo; +end $$ + +create procedure test_signal_syntax() +begin + DECLARE foo CONDITION FOR SQLSTATE '12345'; + SIGNAL foo; +end $$ +drop procedure test_signal_syntax $$ + +create procedure test_signal_syntax() +begin + SIGNAL SQLSTATE '23000'; +end $$ +drop procedure test_signal_syntax $$ + +create procedure test_signal_syntax() +begin + SIGNAL SQLSTATE VALUE '23000'; +end $$ +drop procedure test_signal_syntax $$ + +create procedure test_signal_syntax() +begin + DECLARE foo CONDITION FOR SQLSTATE '12345'; + SIGNAL foo SET CLASS_ORIGIN = 'foo'; +end $$ +drop procedure test_signal_syntax $$ + +create procedure test_signal_syntax() +begin + DECLARE foo CONDITION FOR SQLSTATE '12345'; + SIGNAL foo SET SUBCLASS_ORIGIN = 'foo'; +end $$ +drop procedure test_signal_syntax $$ + +create procedure test_signal_syntax() +begin + DECLARE foo CONDITION FOR SQLSTATE '12345'; + SIGNAL foo SET CONSTRAINT_CATALOG = 'foo'; +end $$ +drop procedure test_signal_syntax $$ + +create procedure test_signal_syntax() +begin + DECLARE foo CONDITION FOR SQLSTATE '12345'; + SIGNAL foo SET CONSTRAINT_SCHEMA = 'foo'; +end $$ +drop procedure test_signal_syntax $$ + +create procedure test_signal_syntax() +begin + DECLARE foo CONDITION FOR SQLSTATE '12345'; + SIGNAL foo SET CONSTRAINT_NAME = 'foo'; +end $$ +drop procedure test_signal_syntax $$ + +create procedure test_signal_syntax() +begin + DECLARE foo CONDITION FOR SQLSTATE '12345'; + SIGNAL foo SET CATALOG_NAME = 'foo'; +end $$ +drop procedure test_signal_syntax $$ + +create procedure test_signal_syntax() +begin + DECLARE foo CONDITION FOR SQLSTATE '12345'; + SIGNAL foo SET SCHEMA_NAME = 'foo'; +end $$ +drop procedure test_signal_syntax $$ + +create procedure test_signal_syntax() +begin + DECLARE foo CONDITION FOR SQLSTATE '12345'; + SIGNAL foo SET TABLE_NAME = 'foo'; +end $$ +drop procedure test_signal_syntax $$ + +create procedure test_signal_syntax() +begin + DECLARE foo CONDITION FOR SQLSTATE '12345'; + SIGNAL foo SET COLUMN_NAME = 'foo'; +end $$ +drop procedure test_signal_syntax $$ + +create procedure test_signal_syntax() +begin + DECLARE foo CONDITION FOR SQLSTATE '12345'; + SIGNAL foo SET CURSOR_NAME = 'foo'; +end $$ +drop procedure test_signal_syntax $$ + +create procedure test_signal_syntax() +begin + DECLARE foo CONDITION FOR SQLSTATE '12345'; + SIGNAL foo SET MESSAGE_TEXT = 'foo'; +end $$ +drop procedure test_signal_syntax $$ + +create procedure test_signal_syntax() +begin + DECLARE foo CONDITION FOR SQLSTATE '12345'; + SIGNAL foo SET MYSQL_ERRNO = 'foo'; +end $$ +drop procedure test_signal_syntax $$ + +--error ER_DUP_SIGNAL_SET +create procedure test_invalid() +begin + DECLARE foo CONDITION FOR SQLSTATE '12345'; + SIGNAL foo SET CLASS_ORIGIN = 'foo', CLASS_ORIGIN = 'bar'; +end $$ + +--error ER_DUP_SIGNAL_SET +create procedure test_invalid() +begin + DECLARE foo CONDITION FOR SQLSTATE '12345'; + SIGNAL foo SET MESSAGE_TEXT = 'foo', MESSAGE_TEXT = 'bar'; +end $$ + +--error ER_DUP_SIGNAL_SET +create procedure test_invalid() +begin + DECLARE foo CONDITION FOR SQLSTATE '12345'; + SIGNAL foo SET MYSQL_ERRNO = 'foo', MYSQL_ERRNO = 'bar'; +end $$ + +create procedure test_signal_syntax() +begin + DECLARE foo CONDITION FOR SQLSTATE '12345'; + SIGNAL foo SET + CLASS_ORIGIN = 'foo', + SUBCLASS_ORIGIN = 'foo', + CONSTRAINT_CATALOG = 'foo', + CONSTRAINT_SCHEMA = 'foo', + CONSTRAINT_NAME = 'foo', + CATALOG_NAME = 'foo', + SCHEMA_NAME = 'foo', + TABLE_NAME = 'foo', + COLUMN_NAME = 'foo', + CURSOR_NAME = 'foo', + MESSAGE_TEXT = 'foo', + MYSQL_ERRNO = 'foo'; +end $$ +drop procedure test_signal_syntax $$ + +--error ER_SP_BAD_SQLSTATE +SIGNAL SQLSTATE '00000' $$ + +--error ER_SP_BAD_SQLSTATE +SIGNAL SQLSTATE '00001' $$ + +--error ER_SP_BAD_SQLSTATE +create procedure test_invalid() +begin + SIGNAL SQLSTATE '00000'; +end $$ + +--error ER_SP_BAD_SQLSTATE +create procedure test_invalid() +begin + SIGNAL SQLSTATE '00001'; +end $$ + +--echo # +--echo # Test conditions information that SIGNAL can not set +--echo # + +--error ER_PARSE_ERROR +create procedure test_invalid() +begin + SIGNAL SQLSTATE '12345' SET bla_bla = 'foo'; +end $$ + +--error ER_PARSE_ERROR +create procedure test_invalid() +begin + SIGNAL SQLSTATE '12345' SET CONDITION_IDENTIFIER = 'foo'; +end $$ + +--error ER_PARSE_ERROR +create procedure test_invalid() +begin + SIGNAL SQLSTATE '12345' SET CONDITION_NUMBER = 'foo'; +end $$ + +--error ER_PARSE_ERROR +create procedure test_invalid() +begin + SIGNAL SQLSTATE '12345' SET CONNECTION_NAME = 'foo'; +end $$ + +--error ER_PARSE_ERROR +create procedure test_invalid() +begin + SIGNAL SQLSTATE '12345' SET MESSAGE_LENGTH = 'foo'; +end $$ + +--error ER_PARSE_ERROR +create procedure test_invalid() +begin + SIGNAL SQLSTATE '12345' SET MESSAGE_OCTET_LENGTH = 'foo'; +end $$ + +--error ER_PARSE_ERROR +create procedure test_invalid() +begin + SIGNAL SQLSTATE '12345' SET PARAMETER_MODE = 'foo'; +end $$ + +--error ER_PARSE_ERROR +create procedure test_invalid() +begin + SIGNAL SQLSTATE '12345' SET PARAMETER_NAME = 'foo'; +end $$ + +--error ER_PARSE_ERROR +create procedure test_invalid() +begin + SIGNAL SQLSTATE '12345' SET PARAMETER_ORDINAL_POSITION = 'foo'; +end $$ + +--error ER_PARSE_ERROR +create procedure test_invalid() +begin + SIGNAL SQLSTATE '12345' SET RETURNED_SQLSTATE = 'foo'; +end $$ + +--error ER_PARSE_ERROR +create procedure test_invalid() +begin + SIGNAL SQLSTATE '12345' SET ROUTINE_CATALOG = 'foo'; +end $$ + +--error ER_PARSE_ERROR +create procedure test_invalid() +begin + SIGNAL SQLSTATE '12345' SET ROUTINE_NAME = 'foo'; +end $$ + +--error ER_PARSE_ERROR +create procedure test_invalid() +begin + SIGNAL SQLSTATE '12345' SET ROUTINE_SCHEMA = 'foo'; +end $$ + +--error ER_PARSE_ERROR +create procedure test_invalid() +begin + SIGNAL SQLSTATE '12345' SET SERVER_NAME = 'foo'; +end $$ + +--error ER_PARSE_ERROR +create procedure test_invalid() +begin + SIGNAL SQLSTATE '12345' SET SPECIFIC_NAME = 'foo'; +end $$ + +--error ER_PARSE_ERROR +create procedure test_invalid() +begin + SIGNAL SQLSTATE '12345' SET TRIGGER_CATALOG = 'foo'; +end $$ + +--error ER_PARSE_ERROR +create procedure test_invalid() +begin + SIGNAL SQLSTATE '12345' SET TRIGGER_NAME = 'foo'; +end $$ + +--error ER_PARSE_ERROR +create procedure test_invalid() +begin + SIGNAL SQLSTATE '12345' SET TRIGGER_SCHEMA = 'foo'; +end $$ + +delimiter ;$$ + +--echo # +--echo # Test the RESIGNAL syntax +--echo # + +--disable_warnings +drop procedure if exists test_invalid; +drop procedure if exists test_resignal_syntax; +--enable_warnings + +delimiter $$; + +--error ER_SP_COND_MISMATCH +create procedure test_invalid() +begin + RESIGNAL foo; +end $$ + +create procedure test_resignal_syntax() +begin + RESIGNAL; +end $$ +drop procedure test_resignal_syntax $$ + +--error ER_SIGNAL_BAD_CONDITION_TYPE +create procedure test_invalid() +begin + DECLARE foo CONDITION FOR 1234; + RESIGNAL foo; +end $$ + +create procedure test_resignal_syntax() +begin + DECLARE foo CONDITION FOR SQLSTATE '12345'; + RESIGNAL foo; +end $$ +drop procedure test_resignal_syntax $$ + +create procedure test_resignal_syntax() +begin + RESIGNAL SQLSTATE '23000'; +end $$ +drop procedure test_resignal_syntax $$ + +create procedure test_resignal_syntax() +begin + RESIGNAL SQLSTATE VALUE '23000'; +end $$ +drop procedure test_resignal_syntax $$ + +create procedure test_resignal_syntax() +begin + RESIGNAL SET CLASS_ORIGIN = 'foo'; +end $$ +drop procedure test_resignal_syntax $$ + +create procedure test_resignal_syntax() +begin + DECLARE foo CONDITION FOR SQLSTATE '12345'; + RESIGNAL foo SET CLASS_ORIGIN = 'foo'; +end $$ +drop procedure test_resignal_syntax $$ + +create procedure test_resignal_syntax() +begin + RESIGNAL SET SUBCLASS_ORIGIN = 'foo'; +end $$ +drop procedure test_resignal_syntax $$ + +create procedure test_resignal_syntax() +begin + DECLARE foo CONDITION FOR SQLSTATE '12345'; + RESIGNAL foo SET SUBCLASS_ORIGIN = 'foo'; +end $$ +drop procedure test_resignal_syntax $$ + +create procedure test_resignal_syntax() +begin + RESIGNAL SET CONSTRAINT_CATALOG = 'foo'; +end $$ +drop procedure test_resignal_syntax $$ + +create procedure test_resignal_syntax() +begin + DECLARE foo CONDITION FOR SQLSTATE '12345'; + RESIGNAL foo SET CONSTRAINT_CATALOG = 'foo'; +end $$ +drop procedure test_resignal_syntax $$ + +create procedure test_resignal_syntax() +begin + RESIGNAL SET CONSTRAINT_SCHEMA = 'foo'; +end $$ +drop procedure test_resignal_syntax $$ + +create procedure test_resignal_syntax() +begin + DECLARE foo CONDITION FOR SQLSTATE '12345'; + RESIGNAL foo SET CONSTRAINT_SCHEMA = 'foo'; +end $$ +drop procedure test_resignal_syntax $$ + +create procedure test_resignal_syntax() +begin + RESIGNAL SET CONSTRAINT_NAME = 'foo'; +end $$ +drop procedure test_resignal_syntax $$ + +create procedure test_resignal_syntax() +begin + DECLARE foo CONDITION FOR SQLSTATE '12345'; + RESIGNAL foo SET CONSTRAINT_NAME = 'foo'; +end $$ +drop procedure test_resignal_syntax $$ + +create procedure test_resignal_syntax() +begin + RESIGNAL SET CATALOG_NAME = 'foo'; +end $$ +drop procedure test_resignal_syntax $$ + +create procedure test_resignal_syntax() +begin + DECLARE foo CONDITION FOR SQLSTATE '12345'; + RESIGNAL foo SET CATALOG_NAME = 'foo'; +end $$ +drop procedure test_resignal_syntax $$ + +create procedure test_resignal_syntax() +begin + RESIGNAL SET SCHEMA_NAME = 'foo'; +end $$ +drop procedure test_resignal_syntax $$ + +create procedure test_resignal_syntax() +begin + DECLARE foo CONDITION FOR SQLSTATE '12345'; + RESIGNAL foo SET SCHEMA_NAME = 'foo'; +end $$ +drop procedure test_resignal_syntax $$ + +create procedure test_resignal_syntax() +begin + RESIGNAL SET TABLE_NAME = 'foo'; +end $$ +drop procedure test_resignal_syntax $$ + +create procedure test_resignal_syntax() +begin + DECLARE foo CONDITION FOR SQLSTATE '12345'; + RESIGNAL foo SET TABLE_NAME = 'foo'; +end $$ +drop procedure test_resignal_syntax $$ + +create procedure test_resignal_syntax() +begin + RESIGNAL SET COLUMN_NAME = 'foo'; +end $$ +drop procedure test_resignal_syntax $$ + +create procedure test_resignal_syntax() +begin + DECLARE foo CONDITION FOR SQLSTATE '12345'; + RESIGNAL foo SET COLUMN_NAME = 'foo'; +end $$ +drop procedure test_resignal_syntax $$ + +create procedure test_resignal_syntax() +begin + RESIGNAL SET CURSOR_NAME = 'foo'; +end $$ +drop procedure test_resignal_syntax $$ + +create procedure test_resignal_syntax() +begin + DECLARE foo CONDITION FOR SQLSTATE '12345'; + RESIGNAL foo SET CURSOR_NAME = 'foo'; +end $$ +drop procedure test_resignal_syntax $$ + +create procedure test_resignal_syntax() +begin + RESIGNAL SET MESSAGE_TEXT = 'foo'; +end $$ +drop procedure test_resignal_syntax $$ + +create procedure test_resignal_syntax() +begin + DECLARE foo CONDITION FOR SQLSTATE '12345'; + RESIGNAL foo SET MESSAGE_TEXT = 'foo'; +end $$ +drop procedure test_resignal_syntax $$ + +create procedure test_resignal_syntax() +begin + RESIGNAL SET MYSQL_ERRNO = 'foo'; +end $$ +drop procedure test_resignal_syntax $$ + +create procedure test_resignal_syntax() +begin + DECLARE foo CONDITION FOR SQLSTATE '12345'; + RESIGNAL foo SET MYSQL_ERRNO = 'foo'; +end $$ +drop procedure test_resignal_syntax $$ + +--error ER_DUP_SIGNAL_SET +create procedure test_invalid() +begin + DECLARE foo CONDITION FOR SQLSTATE '12345'; + RESIGNAL foo SET CLASS_ORIGIN = 'foo', CLASS_ORIGIN = 'bar'; +end $$ + +--error ER_DUP_SIGNAL_SET +create procedure test_invalid() +begin + DECLARE foo CONDITION FOR SQLSTATE '12345'; + RESIGNAL foo SET MESSAGE_TEXT = 'foo', MESSAGE_TEXT = 'bar'; +end $$ + +--error ER_DUP_SIGNAL_SET +create procedure test_invalid() +begin + DECLARE foo CONDITION FOR SQLSTATE '12345'; + RESIGNAL foo SET MYSQL_ERRNO = 'foo', MYSQL_ERRNO = 'bar'; +end $$ + +create procedure test_resignal_syntax() +begin + DECLARE foo CONDITION FOR SQLSTATE '12345'; + RESIGNAL foo SET + CLASS_ORIGIN = 'foo', + SUBCLASS_ORIGIN = 'foo', + CONSTRAINT_CATALOG = 'foo', + CONSTRAINT_SCHEMA = 'foo', + CONSTRAINT_NAME = 'foo', + CATALOG_NAME = 'foo', + SCHEMA_NAME = 'foo', + TABLE_NAME = 'foo', + COLUMN_NAME = 'foo', + CURSOR_NAME = 'foo', + MESSAGE_TEXT = 'foo'; +end $$ +drop procedure test_resignal_syntax $$ + +--error ER_SP_BAD_SQLSTATE +create procedure test_invalid() +begin + RESIGNAL SQLSTATE '00000'; +end $$ + +--error ER_SP_BAD_SQLSTATE +create procedure test_invalid() +begin + RESIGNAL SQLSTATE '00001'; +end $$ + +delimiter ;$$ + +--echo # +--echo # PART 2: non preparable statements +--echo # + +--error ER_UNSUPPORTED_PS +prepare stmt from 'SIGNAL SQLSTATE \'23000\''; + +--error ER_UNSUPPORTED_PS +prepare stmt from 'RESIGNAL SQLSTATE \'23000\''; + +--echo # +--echo # PART 3: runtime execution +--echo # + +--disable_warnings +drop procedure if exists test_signal; +drop procedure if exists test_resignal; +drop table if exists t_warn; +drop table if exists t_cursor; +--enable_warnings + +# Helper tables +create table t_warn(a integer(2)); +create table t_cursor(a integer); + +--echo # +--echo # SIGNAL can also appear in a query +--echo # + +--error ER_SP_COND_MISMATCH +SIGNAL foo; + +SIGNAL SQLSTATE '01000'; + +--error ER_SIGNAL_NOT_FOUND +SIGNAL SQLSTATE '02000'; + +--error ER_SIGNAL_EXCEPTION +SIGNAL SQLSTATE '23000'; + +--error ER_SIGNAL_EXCEPTION +SIGNAL SQLSTATE VALUE '23000'; + +--error ER_WRONG_VALUE_FOR_VAR +SIGNAL SQLSTATE 'HY000' SET MYSQL_ERRNO = 65536; + +--error ER_WRONG_VALUE_FOR_VAR +SIGNAL SQLSTATE 'HY000' SET MYSQL_ERRNO = 99999; + +--error ER_WRONG_VALUE_FOR_VAR +SIGNAL SQLSTATE 'HY000' SET MYSQL_ERRNO = 4294967295; + +--error ER_WRONG_VALUE_FOR_VAR +SIGNAL SQLSTATE 'HY000' SET MYSQL_ERRNO = 0; + +--error ER_PARSE_ERROR +SIGNAL SQLSTATE 'HY000' SET MYSQL_ERRNO = -1; + +--error 65535 +SIGNAL SQLSTATE 'HY000' SET MYSQL_ERRNO = 65535; + +--echo # +--echo # RESIGNAL can also appear in a query +--echo # + +--error ER_RESIGNAL_WITHOUT_ACTIVE_HANDLER +RESIGNAL; + +--error ER_SP_COND_MISMATCH +RESIGNAL foo; + +--error ER_RESIGNAL_WITHOUT_ACTIVE_HANDLER +RESIGNAL SQLSTATE '12345'; + +--error ER_RESIGNAL_WITHOUT_ACTIVE_HANDLER +RESIGNAL SQLSTATE VALUE '12345'; + +--echo # +--echo # Different kind of SIGNAL conditions +--echo # + +delimiter $$; + +create procedure test_signal() +begin + # max range + DECLARE foo CONDITION FOR SQLSTATE 'AABBB'; + SIGNAL foo SET MYSQL_ERRNO = 65535; +end $$ + +--error 65535 +call test_signal() $$ +drop procedure test_signal $$ + +create procedure test_signal() +begin + # max range + DECLARE foo CONDITION FOR SQLSTATE 'AABBB'; + SIGNAL foo SET MYSQL_ERRNO = 65536; +end $$ + +--error ER_WRONG_VALUE_FOR_VAR +call test_signal() $$ +drop procedure test_signal $$ + +create procedure test_signal() +begin + # Error + DECLARE foo CONDITION FOR SQLSTATE '99999'; + SIGNAL foo SET MYSQL_ERRNO = 9999; +end $$ + +--error 9999 +call test_signal() $$ +drop procedure test_signal $$ + +create procedure test_signal() +begin + # warning + DECLARE too_few_records CONDITION FOR SQLSTATE '01000'; + SIGNAL too_few_records SET MYSQL_ERRNO = 1261; +end $$ + +call test_signal() $$ +drop procedure test_signal $$ + +create procedure test_signal() +begin + # Not found + DECLARE sp_fetch_no_data CONDITION FOR SQLSTATE '02000'; + SIGNAL sp_fetch_no_data SET MYSQL_ERRNO = 1329; +end $$ + +--error ER_SP_FETCH_NO_DATA +call test_signal() $$ +drop procedure test_signal $$ + +create procedure test_signal() +begin + # Error + DECLARE sp_cursor_already_open CONDITION FOR SQLSTATE '24000'; + SIGNAL sp_cursor_already_open SET MYSQL_ERRNO = 1325; +end $$ + +--error ER_SP_CURSOR_ALREADY_OPEN +call test_signal() $$ +drop procedure test_signal $$ + +create procedure test_signal() +begin + # Severe error + DECLARE lock_deadlock CONDITION FOR SQLSTATE '40001'; + SIGNAL lock_deadlock SET MYSQL_ERRNO = 1213; +end $$ + +--error ER_LOCK_DEADLOCK +call test_signal() $$ +drop procedure test_signal $$ + +create procedure test_signal() +begin + # Unknown -> error + DECLARE foo CONDITION FOR SQLSTATE "99999"; + SIGNAL foo; +end $$ + +--error ER_SIGNAL_EXCEPTION +call test_signal() $$ +drop procedure test_signal $$ + +create procedure test_signal() +begin + # warning, no subclass + DECLARE warn CONDITION FOR SQLSTATE "01000"; + SIGNAL warn; +end $$ + +call test_signal() $$ +drop procedure test_signal $$ + +create procedure test_signal() +begin + # warning, with subclass + DECLARE warn CONDITION FOR SQLSTATE "01123"; + SIGNAL warn; +end $$ + +call test_signal() $$ +drop procedure test_signal $$ + +create procedure test_signal() +begin + # Not found, no subclass + DECLARE not_found CONDITION FOR SQLSTATE "02000"; + SIGNAL not_found; +end $$ + +--error ER_SIGNAL_NOT_FOUND +call test_signal() $$ +drop procedure test_signal $$ + +create procedure test_signal() +begin + # Not found, with subclass + DECLARE not_found CONDITION FOR SQLSTATE "02XXX"; + SIGNAL not_found; +end $$ + +--error ER_SIGNAL_NOT_FOUND +call test_signal() $$ +drop procedure test_signal $$ + +create procedure test_signal() +begin + # Error, no subclass + DECLARE error CONDITION FOR SQLSTATE "12000"; + SIGNAL error; +end $$ + +--error ER_SIGNAL_EXCEPTION +call test_signal() $$ +drop procedure test_signal $$ + +create procedure test_signal() +begin + # Error, with subclass + DECLARE error CONDITION FOR SQLSTATE "12ABC"; + SIGNAL error; +end $$ + +--error ER_SIGNAL_EXCEPTION +call test_signal() $$ +drop procedure test_signal $$ + +create procedure test_signal() +begin + # Severe error, no subclass + DECLARE error CONDITION FOR SQLSTATE "40000"; + SIGNAL error; +end $$ + +--error ER_SIGNAL_EXCEPTION +call test_signal() $$ +drop procedure test_signal $$ + +create procedure test_signal() +begin + # Severe error, with subclass + DECLARE error CONDITION FOR SQLSTATE "40001"; + SIGNAL error; +end $$ + +--error ER_SIGNAL_EXCEPTION +call test_signal() $$ +drop procedure test_signal $$ + +--echo # +--echo # Test the scope of condition +--echo # + +create procedure test_signal() +begin + DECLARE foo CONDITION FOR SQLSTATE '99999'; + begin + DECLARE foo CONDITION FOR 8888; + end; + SIGNAL foo SET MYSQL_ERRNO=9999; /* outer */ +end $$ + +--error 9999 +call test_signal() $$ +drop procedure test_signal $$ + +create procedure test_signal() +begin + DECLARE foo CONDITION FOR 9999; + begin + DECLARE foo CONDITION FOR SQLSTATE '88888'; + SIGNAL foo SET MYSQL_ERRNO=8888; /* inner */ + end; +end $$ + +--error 8888 +call test_signal() $$ +drop procedure test_signal $$ + +--echo # +--echo # Test SET MYSQL_ERRNO +--echo # + +create procedure test_signal() +begin + DECLARE foo CONDITION FOR SQLSTATE '99999'; + SIGNAL foo SET MYSQL_ERRNO = 1111; +end $$ + +--error 1111 +call test_signal() $$ +drop procedure test_signal $$ + +create procedure test_signal() +begin + DECLARE warn CONDITION FOR SQLSTATE "01000"; + SIGNAL warn SET MYSQL_ERRNO = 1111; +end $$ + +call test_signal() $$ +drop procedure test_signal $$ + +create procedure test_signal() +begin + DECLARE not_found CONDITION FOR SQLSTATE "02000"; + SIGNAL not_found SET MYSQL_ERRNO = 1111; +end $$ + +--error 1111 +call test_signal() $$ +drop procedure test_signal $$ + +create procedure test_signal() +begin + DECLARE error CONDITION FOR SQLSTATE "55000"; + SIGNAL error SET MYSQL_ERRNO = 1111; +end $$ + +--error 1111 +call test_signal() $$ +drop procedure test_signal $$ + +--echo # +--echo # Test SET MESSAGE_TEXT +--echo # + +--error ER_SIGNAL_EXCEPTION +SIGNAL SQLSTATE '77777' SET MESSAGE_TEXT='' $$ + +create procedure test_signal() +begin + DECLARE foo CONDITION FOR SQLSTATE '77777'; + SIGNAL foo SET + MESSAGE_TEXT = "", + MYSQL_ERRNO=5678; +end $$ + +--error 5678 +call test_signal() $$ +drop procedure test_signal $$ + +create procedure test_signal() +begin + DECLARE foo CONDITION FOR SQLSTATE '99999'; + SIGNAL foo SET + MESSAGE_TEXT = "Something bad happened", + MYSQL_ERRNO=9999; +end $$ + +--error 9999 +call test_signal() $$ +drop procedure test_signal $$ + +create procedure test_signal() +begin + DECLARE warn CONDITION FOR SQLSTATE "01000"; + SIGNAL warn SET MESSAGE_TEXT = "Something bad happened"; +end $$ + +call test_signal() $$ +drop procedure test_signal $$ + +create procedure test_signal() +begin + DECLARE not_found CONDITION FOR SQLSTATE "02000"; + SIGNAL not_found SET MESSAGE_TEXT = "Something bad happened"; +end $$ + +--error ER_SIGNAL_NOT_FOUND +call test_signal() $$ +drop procedure test_signal $$ + +create procedure test_signal() +begin + DECLARE error CONDITION FOR SQLSTATE "55000"; + SIGNAL error SET MESSAGE_TEXT = "Something bad happened"; +end $$ + +--error ER_SIGNAL_EXCEPTION +call test_signal() $$ +drop procedure test_signal $$ + +create procedure test_signal() +begin + DECLARE something CONDITION FOR SQLSTATE "01000"; + SIGNAL something SET MESSAGE_TEXT = _utf8 "This is a UTF8 text"; +end $$ + +call test_signal() $$ +drop procedure test_signal $$ + +create procedure test_signal() +begin + DECLARE something CONDITION FOR SQLSTATE "01000"; + SIGNAL something SET MESSAGE_TEXT = ""; +end $$ + +call test_signal() $$ +drop procedure test_signal $$ + +create procedure test_signal() +begin + DECLARE warn CONDITION FOR SQLSTATE "01111"; + SIGNAL warn SET MESSAGE_TEXT = "á a"; +end $$ + +call test_signal() $$ +show warnings $$ +drop procedure test_signal $$ + +--echo # +--echo # Test SET complex expressions +--echo # + +create procedure test_signal() +begin + DECLARE error CONDITION FOR SQLSTATE '99999'; + SIGNAL error SET + MYSQL_ERRNO = NULL; +end $$ + +--error ER_WRONG_VALUE_FOR_VAR +call test_signal() $$ +drop procedure test_signal $$ + +create procedure test_signal() +begin + DECLARE error CONDITION FOR SQLSTATE '99999'; + SIGNAL error SET + CLASS_ORIGIN = NULL; +end $$ + +--error ER_WRONG_VALUE_FOR_VAR +call test_signal() $$ +drop procedure test_signal $$ + +create procedure test_signal() +begin + DECLARE error CONDITION FOR SQLSTATE '99999'; + SIGNAL error SET + SUBCLASS_ORIGIN = NULL; +end $$ + +--error ER_WRONG_VALUE_FOR_VAR +call test_signal() $$ +drop procedure test_signal $$ + +create procedure test_signal() +begin + DECLARE error CONDITION FOR SQLSTATE '99999'; + SIGNAL error SET + CONSTRAINT_CATALOG = NULL; +end $$ + +--error ER_WRONG_VALUE_FOR_VAR +call test_signal() $$ +drop procedure test_signal $$ + +create procedure test_signal() +begin + DECLARE error CONDITION FOR SQLSTATE '99999'; + SIGNAL error SET + CONSTRAINT_SCHEMA = NULL; +end $$ + +--error ER_WRONG_VALUE_FOR_VAR +call test_signal() $$ +drop procedure test_signal $$ + +create procedure test_signal() +begin + DECLARE error CONDITION FOR SQLSTATE '99999'; + SIGNAL error SET + CONSTRAINT_NAME = NULL; +end $$ + +--error ER_WRONG_VALUE_FOR_VAR +call test_signal() $$ +drop procedure test_signal $$ + +create procedure test_signal() +begin + DECLARE error CONDITION FOR SQLSTATE '99999'; + SIGNAL error SET + CATALOG_NAME = NULL; +end $$ + +--error ER_WRONG_VALUE_FOR_VAR +call test_signal() $$ +drop procedure test_signal $$ + +create procedure test_signal() +begin + DECLARE error CONDITION FOR SQLSTATE '99999'; + SIGNAL error SET + SCHEMA_NAME = NULL; +end $$ + +--error ER_WRONG_VALUE_FOR_VAR +call test_signal() $$ +drop procedure test_signal $$ + +create procedure test_signal() +begin + DECLARE error CONDITION FOR SQLSTATE '99999'; + SIGNAL error SET + TABLE_NAME = NULL; +end $$ + +--error ER_WRONG_VALUE_FOR_VAR +call test_signal() $$ +drop procedure test_signal $$ + +create procedure test_signal() +begin + DECLARE error CONDITION FOR SQLSTATE '99999'; + SIGNAL error SET + COLUMN_NAME = NULL; +end $$ + +--error ER_WRONG_VALUE_FOR_VAR +call test_signal() $$ +drop procedure test_signal $$ + +create procedure test_signal() +begin + DECLARE error CONDITION FOR SQLSTATE '99999'; + SIGNAL error SET + CURSOR_NAME = NULL; +end $$ + +--error ER_WRONG_VALUE_FOR_VAR +call test_signal() $$ +drop procedure test_signal $$ + +create procedure test_signal() +begin + DECLARE error CONDITION FOR SQLSTATE '99999'; + SIGNAL error SET + MESSAGE_TEXT = NULL; +end $$ + +--error ER_WRONG_VALUE_FOR_VAR +call test_signal() $$ +drop procedure test_signal $$ + +create procedure test_signal() +begin + DECLARE something CONDITION FOR SQLSTATE '99999'; + DECLARE message_text VARCHAR(64) DEFAULT "Local string variable"; + DECLARE sqlcode INTEGER DEFAULT 1234; + + SIGNAL something SET + MESSAGE_TEXT = message_text, + MYSQL_ERRNO = sqlcode; +end $$ + +--error 1234 +call test_signal() $$ +drop procedure test_signal $$ + +create procedure test_signal(message_text VARCHAR(64), sqlcode INTEGER) +begin + DECLARE something CONDITION FOR SQLSTATE "12345"; + + SIGNAL something SET + MESSAGE_TEXT = message_text, + MYSQL_ERRNO = sqlcode; +end $$ + +--error ER_WRONG_VALUE_FOR_VAR +call test_signal("Parameter string", NULL) $$ + +--error ER_WRONG_VALUE_FOR_VAR +call test_signal(NULL, 1234) $$ + +--error 5678 +call test_signal("Parameter string", 5678) $$ +drop procedure test_signal $$ + +create procedure test_signal() +begin + DECLARE something CONDITION FOR SQLSTATE "AABBB"; + + SIGNAL something SET + MESSAGE_TEXT = @message_text, + MYSQL_ERRNO = @sqlcode; +end $$ + +--error ER_WRONG_VALUE_FOR_VAR +call test_signal() $$ + +set @sqlcode= 12 $$ + +--error ER_WRONG_VALUE_FOR_VAR +call test_signal() $$ + +set @message_text= "User variable" $$ + +--error 12 +call test_signal() $$ +drop procedure test_signal $$ + +--error ER_PARSE_ERROR +create procedure test_invalid() +begin + DECLARE something CONDITION FOR SQLSTATE "AABBB"; + + SIGNAL something SET + MESSAGE_TEXT = @message_text := 'illegal', + MYSQL_ERRNO = @sqlcode := 1234; +end $$ + +create procedure test_signal() +begin + DECLARE aaa VARCHAR(64); + DECLARE bbb VARCHAR(64); + DECLARE ccc VARCHAR(64); + DECLARE ddd VARCHAR(64); + DECLARE eee VARCHAR(64); + DECLARE fff VARCHAR(64); + DECLARE ggg VARCHAR(64); + DECLARE hhh VARCHAR(64); + DECLARE iii VARCHAR(64); + DECLARE jjj VARCHAR(64); + DECLARE kkk VARCHAR(64); + + DECLARE warn CONDITION FOR SQLSTATE "01234"; + + set aaa= repeat("A", 64); + set bbb= repeat("B", 64); + set ccc= repeat("C", 64); + set ddd= repeat("D", 64); + set eee= repeat("E", 64); + set fff= repeat("F", 64); + set ggg= repeat("G", 64); + set hhh= repeat("H", 64); + set iii= repeat("I", 64); + set jjj= repeat("J", 64); + set kkk= repeat("K", 64); + + SIGNAL warn SET + CLASS_ORIGIN = aaa, + SUBCLASS_ORIGIN = bbb, + CONSTRAINT_CATALOG = ccc, + CONSTRAINT_SCHEMA = ddd, + CONSTRAINT_NAME = eee, + CATALOG_NAME = fff, + SCHEMA_NAME = ggg, + TABLE_NAME = hhh, + COLUMN_NAME = iii, + CURSOR_NAME = jjj, + MESSAGE_TEXT = kkk, + MYSQL_ERRNO = 65535; +end $$ + +call test_signal() $$ +drop procedure test_signal $$ + +create procedure test_signal() +begin + DECLARE warn CONDITION FOR SQLSTATE "01234"; + + SIGNAL warn SET + MYSQL_ERRNO = 999999999999999999999999999999999999999999999999999; +end $$ + +--error ER_WRONG_VALUE_FOR_VAR +call test_signal() $$ +drop procedure test_signal $$ + +create procedure test_signal() +begin + DECLARE aaax VARCHAR(65); + DECLARE bbbx VARCHAR(65); + DECLARE cccx VARCHAR(65); + DECLARE dddx VARCHAR(65); + DECLARE eeex VARCHAR(65); + DECLARE fffx VARCHAR(65); + DECLARE gggx VARCHAR(65); + DECLARE hhhx VARCHAR(65); + DECLARE iiix VARCHAR(65); + DECLARE jjjx VARCHAR(65); + DECLARE kkkx VARCHAR(65); + DECLARE lllx VARCHAR(129); + + DECLARE warn CONDITION FOR SQLSTATE "01234"; + + set aaax= concat(repeat("A", 64), "X"); + set bbbx= concat(repeat("B", 64), "X"); + set cccx= concat(repeat("C", 64), "X"); + set dddx= concat(repeat("D", 64), "X"); + set eeex= concat(repeat("E", 64), "X"); + set fffx= concat(repeat("F", 64), "X"); + set gggx= concat(repeat("G", 64), "X"); + set hhhx= concat(repeat("H", 64), "X"); + set iiix= concat(repeat("I", 64), "X"); + set jjjx= concat(repeat("J", 64), "X"); + set kkkx= concat(repeat("K", 64), "X"); + set lllx= concat(repeat("1", 100), + repeat("2", 20), + repeat("8", 8), + "X"); + + SIGNAL warn SET + CLASS_ORIGIN = aaax, + SUBCLASS_ORIGIN = bbbx, + CONSTRAINT_CATALOG = cccx, + CONSTRAINT_SCHEMA = dddx, + CONSTRAINT_NAME = eeex, + CATALOG_NAME = fffx, + SCHEMA_NAME = gggx, + TABLE_NAME = hhhx, + COLUMN_NAME = iiix, + CURSOR_NAME = jjjx, + MESSAGE_TEXT = lllx, + MYSQL_ERRNO = 10000; +end $$ + +#NOTE: the warning text for ER_TRUNCATED_WRONG_VALUE contains +# value: '%-.128s', so the warning printed truncates the value too, +# which affects MESSAGE_TEXT (the X is missing) + +call test_signal() $$ +drop procedure test_signal $$ + +# Test that HANDLER can catch conditions raised by SIGNAL + +create procedure test_signal() +begin + DECLARE warn CONDITION FOR SQLSTATE "01234"; + DECLARE CONTINUE HANDLER for SQLSTATE "01234" + begin + select "Caught by SQLSTATE"; + end; + + SIGNAL warn SET + MESSAGE_TEXT = "Raising a warning", + MYSQL_ERRNO = 1012; +end $$ + +call test_signal() $$ +drop procedure test_signal $$ + +create procedure test_signal() +begin + DECLARE warn CONDITION FOR SQLSTATE "01234"; + DECLARE CONTINUE HANDLER for 1012 + begin + select "Caught by number"; + end; + + SIGNAL warn SET + MESSAGE_TEXT = "Raising a warning", + MYSQL_ERRNO = 1012; +end $$ + +call test_signal() $$ +drop procedure test_signal $$ + +create procedure test_signal() +begin + DECLARE warn CONDITION FOR SQLSTATE "01234"; + DECLARE CONTINUE HANDLER for SQLWARNING + begin + select "Caught by SQLWARNING"; + end; + + SIGNAL warn SET + MESSAGE_TEXT = "Raising a warning", + MYSQL_ERRNO = 1012; +end $$ + +call test_signal() $$ +drop procedure test_signal $$ + +create procedure test_signal() +begin + DECLARE not_found CONDITION FOR SQLSTATE "02ABC"; + DECLARE CONTINUE HANDLER for SQLSTATE "02ABC" + begin + select "Caught by SQLSTATE"; + end; + + SIGNAL not_found SET + MESSAGE_TEXT = "Raising a not found", + MYSQL_ERRNO = 1012; +end $$ + +call test_signal() $$ +drop procedure test_signal $$ + +create procedure test_signal() +begin + DECLARE not_found CONDITION FOR SQLSTATE "02ABC"; + DECLARE CONTINUE HANDLER for 1012 + begin + select "Caught by number"; + end; + + SIGNAL not_found SET + MESSAGE_TEXT = "Raising a not found", + MYSQL_ERRNO = 1012; +end $$ + +call test_signal() $$ +drop procedure test_signal $$ + +create procedure test_signal() +begin + DECLARE not_found CONDITION FOR SQLSTATE "02ABC"; + DECLARE CONTINUE HANDLER for NOT FOUND + begin + select "Caught by NOT FOUND"; + end; + + SIGNAL not_found SET + MESSAGE_TEXT = "Raising a not found", + MYSQL_ERRNO = 1012; +end $$ + +call test_signal() $$ +drop procedure test_signal $$ + +create procedure test_signal() +begin + DECLARE error CONDITION FOR SQLSTATE "55555"; + DECLARE CONTINUE HANDLER for SQLSTATE "55555" + begin + select "Caught by SQLSTATE"; + end; + + SIGNAL error SET + MESSAGE_TEXT = "Raising an error", + MYSQL_ERRNO = 1012; +end $$ + +call test_signal() $$ +drop procedure test_signal $$ + +create procedure test_signal() +begin + DECLARE error CONDITION FOR SQLSTATE "55555"; + DECLARE CONTINUE HANDLER for 1012 + begin + select "Caught by number"; + end; + + SIGNAL error SET + MESSAGE_TEXT = "Raising an error", + MYSQL_ERRNO = 1012; +end $$ + +call test_signal() $$ +drop procedure test_signal $$ + +create procedure test_signal() +begin + DECLARE error CONDITION FOR SQLSTATE "55555"; + DECLARE CONTINUE HANDLER for SQLEXCEPTION + begin + select "Caught by SQLEXCEPTION"; + end; + + SIGNAL error SET + MESSAGE_TEXT = "Raising an error", + MYSQL_ERRNO = 1012; +end $$ + +call test_signal() $$ +drop procedure test_signal $$ + +--echo # +--echo # Test where SIGNAL can be used +--echo # + +create function test_signal_func() returns integer +begin + DECLARE warn CONDITION FOR SQLSTATE "01XXX"; + + SIGNAL warn SET + MESSAGE_TEXT = "This function SIGNAL a warning", + MYSQL_ERRNO = 1012; + + return 5; +end $$ + +select test_signal_func() $$ +drop function test_signal_func $$ + +create function test_signal_func() returns integer +begin + DECLARE not_found CONDITION FOR SQLSTATE "02XXX"; + + SIGNAL not_found SET + MESSAGE_TEXT = "This function SIGNAL not found", + MYSQL_ERRNO = 1012; + + return 5; +end $$ + +--error 1012 +select test_signal_func() $$ +drop function test_signal_func $$ + +create function test_signal_func() returns integer +begin + DECLARE error CONDITION FOR SQLSTATE "50000"; + + SIGNAL error SET + MESSAGE_TEXT = "This function SIGNAL an error", + MYSQL_ERRNO = 1012; + + return 5; +end $$ + +--error 1012 +select test_signal_func() $$ +drop function test_signal_func $$ + +--disable_warnings +drop table if exists t1 $$ +--enable_warnings + +create table t1 (a integer) $$ + +create trigger t1_ai after insert on t1 for each row +begin + DECLARE msg VARCHAR(128); + DECLARE warn CONDITION FOR SQLSTATE "01XXX"; + + set msg= concat("This trigger SIGNAL a warning, a=", NEW.a); + SIGNAL warn SET + MESSAGE_TEXT = msg, + MYSQL_ERRNO = 1012; +end $$ + +insert into t1 values (1), (2) $$ + +drop trigger t1_ai $$ + +create trigger t1_ai after insert on t1 for each row +begin + DECLARE msg VARCHAR(128); + DECLARE not_found CONDITION FOR SQLSTATE "02XXX"; + + set msg= concat("This trigger SIGNAL a not found, a=", NEW.a); + SIGNAL not_found SET + MESSAGE_TEXT = msg, + MYSQL_ERRNO = 1012; +end $$ + +--error 1012 +insert into t1 values (3), (4) $$ + +drop trigger t1_ai $$ + +create trigger t1_ai after insert on t1 for each row +begin + DECLARE msg VARCHAR(128); + DECLARE error CONDITION FOR SQLSTATE "03XXX"; + + set msg= concat("This trigger SIGNAL an error, a=", NEW.a); + SIGNAL error SET + MESSAGE_TEXT = msg, + MYSQL_ERRNO = 1012; +end $$ + +--error 1012 +insert into t1 values (5), (6) $$ + +drop table t1 $$ + +create table t1 (errno integer, msg varchar(128)) $$ + +create trigger t1_ai after insert on t1 for each row +begin + DECLARE warn CONDITION FOR SQLSTATE "01XXX"; + + SIGNAL warn SET + MESSAGE_TEXT = NEW.msg, + MYSQL_ERRNO = NEW.errno; +end $$ + +insert into t1 set errno=1012, msg='Warning message 1 in trigger' $$ +insert into t1 set errno=1013, msg='Warning message 2 in trigger' $$ + +drop table t1 $$ + +--disable_warnings +drop table if exists t1 $$ +drop procedure if exists p1 $$ +drop function if exists f1 $$ +--enable_warnings + +create table t1 (s1 int) $$ +insert into t1 values (1) $$ + +create procedure p1() +begin + declare a int; + declare c cursor for select f1() from t1; + declare continue handler for sqlstate '03000' + select "caught 03000"; + declare continue handler for 1326 + select "caught cursor is not open"; + + select "Before open"; + open c; + select "Before fetch"; + fetch c into a; + select "Before close"; + close c; +end $$ + +create function f1() returns int +begin + signal sqlstate '03000'; + return 5; +end $$ + +## FIXME : MEMORY plugin warning, valgrind leak, bug#36518 +## call p1() $$ + +drop table t1 $$ +drop procedure p1 $$ +drop function f1 $$ + +--echo # +--echo # Test the RESIGNAL runtime +--echo # + +# 6 tests: +# {Signaled warning, Signaled Not Found, Signaled Error, +# warning, not found, error} --> RESIGNAL + +create procedure test_resignal() +begin + DECLARE warn CONDITION FOR SQLSTATE "01234"; + DECLARE CONTINUE HANDLER for 1012 + begin + select "before RESIGNAL"; + RESIGNAL; + select "after RESIGNAL"; + end; + + SIGNAL warn SET + MESSAGE_TEXT = "Raising a warning", + MYSQL_ERRNO = 1012; +end $$ + +call test_resignal() $$ +drop procedure test_resignal $$ + +create procedure test_resignal() +begin + DECLARE not_found CONDITION FOR SQLSTATE "02222"; + DECLARE CONTINUE HANDLER for 1012 + begin + select "before RESIGNAL"; + RESIGNAL; + select "after RESIGNAL"; + end; + + SIGNAL not_found SET + MESSAGE_TEXT = "Raising a not found", + MYSQL_ERRNO = 1012; +end $$ + +--error 1012 +call test_resignal() $$ +drop procedure test_resignal $$ + +create procedure test_resignal() +begin + DECLARE error CONDITION FOR SQLSTATE "55555"; + DECLARE CONTINUE HANDLER for 1012 + begin + select "before RESIGNAL"; + RESIGNAL; + select "after RESIGNAL"; + end; + + SIGNAL error SET + MESSAGE_TEXT = "Raising an error", + MYSQL_ERRNO = 1012; +end $$ + +--error 1012 +call test_resignal() $$ +drop procedure test_resignal $$ + +create procedure test_resignal() +begin + DECLARE CONTINUE HANDLER for sqlwarning + begin + select "before RESIGNAL"; + RESIGNAL; + select "after RESIGNAL"; + end; + + insert into t_warn set a= 9999999999999999; +end $$ + +call test_resignal() $$ +drop procedure test_resignal $$ + +create procedure test_resignal() +begin + DECLARE x integer; + DECLARE c cursor for select * from t_cursor; + DECLARE CONTINUE HANDLER for not found + begin + select "before RESIGNAL"; + RESIGNAL; + select "after RESIGNAL"; + end; + + open c; + fetch c into x; + close c; +end $$ + +--error ER_SP_FETCH_NO_DATA +call test_resignal() $$ +drop procedure test_resignal $$ + +create procedure test_resignal() +begin + DECLARE CONTINUE HANDLER for sqlexception + begin + select "before RESIGNAL"; + RESIGNAL; + select "after RESIGNAL"; + end; + + drop table no_such_table; +end $$ + +--error ER_BAD_TABLE_ERROR +call test_resignal() $$ +drop procedure test_resignal $$ + +# 6 tests: +# {Signaled warning, Signaled Not Found, Signaled Error, +# warning, not found, error} --> RESIGNAL SET ... + +create procedure test_resignal() +begin + DECLARE warn CONDITION FOR SQLSTATE "01234"; + DECLARE CONTINUE HANDLER for 1012 + begin + select "before RESIGNAL"; + RESIGNAL SET + MESSAGE_TEXT = "RESIGNAL of a warning", + MYSQL_ERRNO = 5555 ; + select "after RESIGNAL"; + end; + + SIGNAL warn SET + MESSAGE_TEXT = "Raising a warning", + MYSQL_ERRNO = 1012; +end $$ + +call test_resignal() $$ +drop procedure test_resignal $$ + +create procedure test_resignal() +begin + DECLARE not_found CONDITION FOR SQLSTATE "02111"; + DECLARE CONTINUE HANDLER for 1012 + begin + select "before RESIGNAL"; + RESIGNAL SET + MESSAGE_TEXT = "RESIGNAL of a not found", + MYSQL_ERRNO = 5555 ; + select "after RESIGNAL"; + end; + + SIGNAL not_found SET + MESSAGE_TEXT = "Raising a not found", + MYSQL_ERRNO = 1012; +end $$ + +--error 5555 +call test_resignal() $$ +drop procedure test_resignal $$ + +create procedure test_resignal() +begin + DECLARE error CONDITION FOR SQLSTATE "33333"; + DECLARE CONTINUE HANDLER for 1012 + begin + select "before RESIGNAL"; + RESIGNAL SET + MESSAGE_TEXT = "RESIGNAL of an error", + MYSQL_ERRNO = 5555 ; + select "after RESIGNAL"; + end; + + SIGNAL error SET + MESSAGE_TEXT = "Raising an error", + MYSQL_ERRNO = 1012; +end $$ + +--error 5555 +call test_resignal() $$ +drop procedure test_resignal $$ + +create procedure test_resignal() +begin + DECLARE CONTINUE HANDLER for sqlwarning + begin + select "before RESIGNAL"; + RESIGNAL SET + MESSAGE_TEXT = "RESIGNAL of a warning", + MYSQL_ERRNO = 5555 ; + select "after RESIGNAL"; + end; + + insert into t_warn set a= 9999999999999999; +end $$ + +call test_resignal() $$ +drop procedure test_resignal $$ + +create procedure test_resignal() +begin + DECLARE x integer; + DECLARE c cursor for select * from t_cursor; + DECLARE CONTINUE HANDLER for not found + begin + select "before RESIGNAL"; + RESIGNAL SET + MESSAGE_TEXT = "RESIGNAL of not found", + MYSQL_ERRNO = 5555 ; + select "after RESIGNAL"; + end; + + open c; + fetch c into x; + close c; +end $$ + +--error 5555 +call test_resignal() $$ +drop procedure test_resignal $$ + +create procedure test_resignal() +begin + DECLARE CONTINUE HANDLER for sqlexception + begin + select "before RESIGNAL"; + RESIGNAL SET + MESSAGE_TEXT = "RESIGNAL of an error", + MYSQL_ERRNO = 5555 ; + select "after RESIGNAL"; + end; + + drop table no_such_table; +end $$ + +--error 5555 +call test_resignal() $$ +drop procedure test_resignal $$ + +######################################################### + +# 3 tests: +# {Signaled warning} +# --> RESIGNAL SQLSTATE {warning, not found, error} SET ... + +create procedure test_resignal() +begin + DECLARE warn CONDITION FOR SQLSTATE "01111"; + DECLARE CONTINUE HANDLER for 1012 + begin + select "before RESIGNAL"; + RESIGNAL SQLSTATE "01222" SET + MESSAGE_TEXT = "RESIGNAL to warning", + MYSQL_ERRNO = 5555 ; + select "after RESIGNAL"; + end; + + SIGNAL warn SET + MESSAGE_TEXT = "Raising a warning", + MYSQL_ERRNO = 1012; +end $$ + +call test_resignal() $$ +drop procedure test_resignal $$ + +create procedure test_resignal() +begin + DECLARE warn CONDITION FOR SQLSTATE "01111"; + DECLARE CONTINUE HANDLER for 1012 + begin + select "before RESIGNAL"; + RESIGNAL SQLSTATE "02222" SET + MESSAGE_TEXT = "RESIGNAL to not found", + MYSQL_ERRNO = 5555 ; + select "after RESIGNAL"; + end; + + SIGNAL warn SET + MESSAGE_TEXT = "Raising a warning", + MYSQL_ERRNO = 1012; +end $$ + +--error 5555 +call test_resignal() $$ +show warnings $$ +drop procedure test_resignal $$ + +create procedure test_resignal() +begin + DECLARE warn CONDITION FOR SQLSTATE "01111"; + DECLARE CONTINUE HANDLER for 1012 + begin + select "before RESIGNAL"; + RESIGNAL SQLSTATE "33333" SET + MESSAGE_TEXT = "RESIGNAL to error", + MYSQL_ERRNO = 5555 ; + select "after RESIGNAL"; + end; + + SIGNAL warn SET + MESSAGE_TEXT = "Raising a warning", + MYSQL_ERRNO = 1012; +end $$ + +--error 5555 +call test_resignal() $$ +show warnings $$ +drop procedure test_resignal $$ + +# 3 tests: +# {Signaled not found} +# --> RESIGNAL SQLSTATE {warning, not found, error} SET ... + +create procedure test_resignal() +begin + DECLARE not_found CONDITION FOR SQLSTATE "02ABC"; + DECLARE CONTINUE HANDLER for 1012 + begin + select "before RESIGNAL"; + RESIGNAL SQLSTATE "01222" SET + MESSAGE_TEXT = "RESIGNAL to warning", + MYSQL_ERRNO = 5555 ; + select "after RESIGNAL"; + end; + + SIGNAL not_found SET + MESSAGE_TEXT = "Raising a not found", + MYSQL_ERRNO = 1012; +end $$ + +call test_resignal() $$ +drop procedure test_resignal $$ + +create procedure test_resignal() +begin + DECLARE not_found CONDITION FOR SQLSTATE "02ABC"; + DECLARE CONTINUE HANDLER for 1012 + begin + select "before RESIGNAL"; + RESIGNAL SQLSTATE "02222" SET + MESSAGE_TEXT = "RESIGNAL to not found", + MYSQL_ERRNO = 5555 ; + select "after RESIGNAL"; + end; + + SIGNAL not_found SET + MESSAGE_TEXT = "Raising a not found", + MYSQL_ERRNO = 1012; +end $$ + +--error 5555 +call test_resignal() $$ +show warnings $$ +drop procedure test_resignal $$ + +create procedure test_resignal() +begin + DECLARE not_found CONDITION FOR SQLSTATE "02ABC"; + DECLARE CONTINUE HANDLER for 1012 + begin + select "before RESIGNAL"; + RESIGNAL SQLSTATE "33333" SET + MESSAGE_TEXT = "RESIGNAL to error", + MYSQL_ERRNO = 5555 ; + select "after RESIGNAL"; + end; + + SIGNAL not_found SET + MESSAGE_TEXT = "Raising a not found", + MYSQL_ERRNO = 1012; +end $$ + +--error 5555 +call test_resignal() $$ +show warnings $$ +drop procedure test_resignal $$ + +# 3 tests: +# {Signaled error} +# --> RESIGNAL SQLSTATE {warning, not found, error} SET ... + +create procedure test_resignal() +begin + DECLARE error CONDITION FOR SQLSTATE "AAAAA"; + DECLARE CONTINUE HANDLER for 1012 + begin + select "before RESIGNAL"; + RESIGNAL SQLSTATE "01222" SET + MESSAGE_TEXT = "RESIGNAL to warning", + MYSQL_ERRNO = 5555 ; + select "after RESIGNAL"; + end; + + SIGNAL error SET + MESSAGE_TEXT = "Raising an error", + MYSQL_ERRNO = 1012; +end $$ + +call test_resignal() $$ +drop procedure test_resignal $$ + +create procedure test_resignal() +begin + DECLARE error CONDITION FOR SQLSTATE "AAAAA"; + DECLARE CONTINUE HANDLER for 1012 + begin + select "before RESIGNAL"; + RESIGNAL SQLSTATE "02222" SET + MESSAGE_TEXT = "RESIGNAL to not found", + MYSQL_ERRNO = 5555 ; + select "after RESIGNAL"; + end; + + SIGNAL error SET + MESSAGE_TEXT = "Raising an error", + MYSQL_ERRNO = 1012; +end $$ + +--error 5555 +call test_resignal() $$ +show warnings $$ +drop procedure test_resignal $$ + +create procedure test_resignal() +begin + DECLARE error CONDITION FOR SQLSTATE "AAAAA"; + DECLARE CONTINUE HANDLER for 1012 + begin + select "before RESIGNAL"; + RESIGNAL SQLSTATE "33333" SET + MESSAGE_TEXT = "RESIGNAL to error", + MYSQL_ERRNO = 5555 ; + select "after RESIGNAL"; + end; + + SIGNAL error SET + MESSAGE_TEXT = "Raising an error", + MYSQL_ERRNO = 1012; +end $$ + +--error 5555 +call test_resignal() $$ +show warnings $$ +drop procedure test_resignal $$ + +# 3 tests: +# {warning} +# --> RESIGNAL SQLSTATE {warning, not found, error} SET ... + +create procedure test_resignal() +begin + DECLARE CONTINUE HANDLER for sqlwarning + begin + select "before RESIGNAL"; + RESIGNAL SQLSTATE "01111" SET + MESSAGE_TEXT = "RESIGNAL to a warning", + MYSQL_ERRNO = 5555 ; + select "after RESIGNAL"; + end; + + insert into t_warn set a= 9999999999999999; +end $$ + +call test_resignal() $$ +drop procedure test_resignal $$ + +create procedure test_resignal() +begin + DECLARE CONTINUE HANDLER for sqlwarning + begin + select "before RESIGNAL"; + RESIGNAL SQLSTATE "02444" SET + MESSAGE_TEXT = "RESIGNAL to a not found", + MYSQL_ERRNO = 5555 ; + select "after RESIGNAL"; + end; + + insert into t_warn set a= 9999999999999999; +end $$ + +--error 5555 +call test_resignal() $$ +show warnings $$ +drop procedure test_resignal $$ + +create procedure test_resignal() +begin + DECLARE CONTINUE HANDLER for sqlwarning + begin + select "before RESIGNAL"; + RESIGNAL SQLSTATE "44444" SET + MESSAGE_TEXT = "RESIGNAL to an error", + MYSQL_ERRNO = 5555 ; + select "after RESIGNAL"; + end; + + insert into t_warn set a= 9999999999999999; +end $$ + +--error 5555 +call test_resignal() $$ +show warnings $$ +drop procedure test_resignal $$ + +# 3 tests: +# {not found} +# --> RESIGNAL SQLSTATE {warning, not found, error} SET ... + +create procedure test_resignal() +begin + DECLARE x integer; + DECLARE c cursor for select * from t_cursor; + DECLARE CONTINUE HANDLER for not found + begin + select "before RESIGNAL"; + RESIGNAL SQLSTATE "01111" SET + MESSAGE_TEXT = "RESIGNAL to a warning", + MYSQL_ERRNO = 5555 ; + select "after RESIGNAL"; + end; + + open c; + fetch c into x; + close c; +end $$ + +call test_resignal() $$ +drop procedure test_resignal $$ + +create procedure test_resignal() +begin + DECLARE x integer; + DECLARE c cursor for select * from t_cursor; + DECLARE CONTINUE HANDLER for not found + begin + select "before RESIGNAL"; + RESIGNAL SQLSTATE "02444" SET + MESSAGE_TEXT = "RESIGNAL to a not found", + MYSQL_ERRNO = 5555 ; + select "after RESIGNAL"; + end; + + open c; + fetch c into x; + close c; +end $$ + +--error 5555 +call test_resignal() $$ +show warnings $$ +drop procedure test_resignal $$ + +create procedure test_resignal() +begin + DECLARE x integer; + DECLARE c cursor for select * from t_cursor; + DECLARE CONTINUE HANDLER for not found + begin + select "before RESIGNAL"; + RESIGNAL SQLSTATE "44444" SET + MESSAGE_TEXT = "RESIGNAL to an error", + MYSQL_ERRNO = 5555 ; + select "after RESIGNAL"; + end; + + open c; + fetch c into x; + close c; +end $$ + +--error 5555 +call test_resignal() $$ +show warnings $$ +drop procedure test_resignal $$ + +# 3 tests: +# {error} +# --> RESIGNAL SQLSTATE {warning, not found, error} SET ... + +create procedure test_resignal() +begin + DECLARE CONTINUE HANDLER for sqlexception + begin + select "before RESIGNAL"; + RESIGNAL SQLSTATE "01111" SET + MESSAGE_TEXT = "RESIGNAL to a warning", + MYSQL_ERRNO = 5555 ; + select "after RESIGNAL"; + end; + + drop table no_such_table; +end $$ + +call test_resignal() $$ +drop procedure test_resignal $$ + +create procedure test_resignal() +begin + DECLARE CONTINUE HANDLER for sqlexception + begin + select "before RESIGNAL"; + RESIGNAL SQLSTATE "02444" SET + MESSAGE_TEXT = "RESIGNAL to a not found", + MYSQL_ERRNO = 5555 ; + select "after RESIGNAL"; + end; + + drop table no_such_table; +end $$ + +--error 5555 +call test_resignal() $$ +show warnings $$ +drop procedure test_resignal $$ + +create procedure test_resignal() +begin + DECLARE CONTINUE HANDLER for sqlexception + begin + select "before RESIGNAL"; + RESIGNAL SQLSTATE "44444" SET + MESSAGE_TEXT = "RESIGNAL to an error", + MYSQL_ERRNO = 5555 ; + select "after RESIGNAL"; + end; + + drop table no_such_table; +end $$ + +--error 5555 +call test_resignal() $$ +show warnings $$ +drop procedure test_resignal $$ + +--echo # +--echo # More complex cases +--echo # + +--disable_warnings +drop procedure if exists peter_p1 $$ +drop procedure if exists peter_p2 $$ +--enable_warnings + +CREATE PROCEDURE peter_p1 () +BEGIN + DECLARE x CONDITION FOR 1231; + DECLARE EXIT HANDLER FOR x + BEGIN + SELECT '2'; + RESIGNAL SET MYSQL_ERRNO = 9999; + END; + + BEGIN + DECLARE EXIT HANDLER FOR x + BEGIN + SELECT '1'; + RESIGNAL SET SCHEMA_NAME = 'test'; + END; + SET @@sql_mode=NULL; + END; +END +$$ + +CREATE PROCEDURE peter_p2 () +BEGIN + DECLARE x CONDITION for 9999; + DECLARE EXIT HANDLER FOR x + BEGIN + SELECT '3'; + RESIGNAL SET MESSAGE_TEXT = 'Hi, I am a useless error message'; + END; + CALL peter_p1(); +END +$$ + +# +# Here, RESIGNAL only modifies the condition caught, +# so there is only 1 condition at the end +# The final SQLSTATE is 42000 (it comes from the error 1231), +# since the condition attributes are preserved. +# +--error 9999 +CALL peter_p2() $$ +show warnings $$ + +drop procedure peter_p1 $$ +drop procedure peter_p2 $$ + +CREATE PROCEDURE peter_p1 () +BEGIN + DECLARE x CONDITION FOR SQLSTATE '42000'; + DECLARE EXIT HANDLER FOR x + BEGIN + SELECT '2'; + RESIGNAL x SET MYSQL_ERRNO = 9999; + END; + + BEGIN + DECLARE EXIT HANDLER FOR x + BEGIN + SELECT '1'; + RESIGNAL x SET + SCHEMA_NAME = 'test', + MYSQL_ERRNO= 1231; + END; + /* Raises ER_WRONG_VALUE_FOR_VAR : 1231, SQLSTATE 42000 */ + SET @@sql_mode=NULL; + END; +END +$$ + +CREATE PROCEDURE peter_p2 () +BEGIN + DECLARE x CONDITION for SQLSTATE '42000'; + DECLARE EXIT HANDLER FOR x + BEGIN + SELECT '3'; + RESIGNAL x SET + MESSAGE_TEXT = 'Hi, I am a useless error message', + MYSQL_ERRNO = 9999; + END; + CALL peter_p1(); +END +$$ + +# +# Here, "RESIGNAL <condition>" create a new condition in the diagnostics +# area, so that there are 4 conditions at the end. +# +--error 9999 +CALL peter_p2() $$ +show warnings $$ + +drop procedure peter_p1 $$ +drop procedure peter_p2 $$ + +# +# Test the value of MESSAGE_TEXT in RESIGNAL when no SET MESSAGE_TEXT clause +# is provided (the expected result is the text from the SIGNALed condition) +# + +drop procedure if exists peter_p3 $$ + +create procedure peter_p3() +begin + declare continue handler for sqlexception + resignal sqlstate '99002' set mysql_errno = 2; + + signal sqlstate '99001' set mysql_errno = 1, message_text = "Original"; +end $$ + +--error 2 +call peter_p3() $$ + +# Expecting 2 conditions, both with the text "Original" +show warnings $$ + +drop procedure peter_p3 $$ + +delimiter ;$$ + +drop table t_warn; +drop table t_cursor; + +--echo # +--echo # Miscelaneous test cases +--echo # + +delimiter $$; + +create procedure test_signal() +begin + DECLARE foo CONDITION FOR SQLSTATE '12345'; + SIGNAL foo SET MYSQL_ERRNO = 0x12; /* 18 */ +end $$ + +-- error 18 +call test_signal $$ +drop procedure test_signal $$ + +create procedure test_signal() +begin + DECLARE foo CONDITION FOR SQLSTATE '12345'; + SIGNAL foo SET MYSQL_ERRNO = 0b00010010; /* 18 */ +end $$ + +-- error 18 +call test_signal $$ +drop procedure test_signal $$ + +create procedure test_signal() +begin + DECLARE foo CONDITION FOR SQLSTATE '12345'; + SIGNAL foo SET MYSQL_ERRNO = '65'; /* 65 */ +end $$ + +-- error 65 +call test_signal $$ +drop procedure test_signal $$ + +create procedure test_signal() +begin + DECLARE foo CONDITION FOR SQLSTATE '12345'; + SIGNAL foo SET MYSQL_ERRNO = 'A'; /* illegal */ +end $$ + +-- error ER_WRONG_VALUE_FOR_VAR +call test_signal $$ +drop procedure test_signal $$ + +create procedure test_signal() +begin + DECLARE foo CONDITION FOR SQLSTATE '12345'; + SIGNAL foo SET MYSQL_ERRNO = "65"; /* 65 */ +end $$ + +-- error 65 +call test_signal $$ +drop procedure test_signal $$ + +create procedure test_signal() +begin + DECLARE foo CONDITION FOR SQLSTATE '12345'; + SIGNAL foo SET MYSQL_ERRNO = "A"; /* illegal */ +end $$ + +-- error ER_WRONG_VALUE_FOR_VAR +call test_signal $$ +drop procedure test_signal $$ + +create procedure test_signal() +begin + DECLARE foo CONDITION FOR SQLSTATE '12345'; + SIGNAL foo SET MYSQL_ERRNO = `65`; /* illegal */ +end $$ + +-- error ER_BAD_FIELD_ERROR +call test_signal $$ +drop procedure test_signal $$ + +create procedure test_signal() +begin + DECLARE foo CONDITION FOR SQLSTATE '12345'; + SIGNAL foo SET MYSQL_ERRNO = `A`; /* illegal */ +end $$ + +-- error ER_BAD_FIELD_ERROR +call test_signal $$ +drop procedure test_signal $$ + +create procedure test_signal() +begin + DECLARE foo CONDITION FOR SQLSTATE '12345'; + SIGNAL foo SET MYSQL_ERRNO = 3.141592; /* 3 */ +end $$ + +-- error 3 +call test_signal $$ +drop procedure test_signal $$ + +create procedure test_signal() +begin + DECLARE foo CONDITION FOR SQLSTATE '12345'; + SIGNAL foo SET MYSQL_ERRNO = 1000, + MESSAGE_TEXT= 0x41; /* A */ +end $$ + +-- error 1000 +call test_signal $$ +drop procedure test_signal $$ + +create procedure test_signal() +begin + DECLARE foo CONDITION FOR SQLSTATE '12345'; + SIGNAL foo SET MYSQL_ERRNO = 1000, + MESSAGE_TEXT= 0b01000001; /* A */ +end $$ + +-- error 1000 +call test_signal $$ +drop procedure test_signal $$ + +create procedure test_signal() +begin + DECLARE foo CONDITION FOR SQLSTATE '12345'; + SIGNAL foo SET MYSQL_ERRNO = 1000, + MESSAGE_TEXT = "Hello"; +end $$ + +-- error 1000 +call test_signal $$ +drop procedure test_signal $$ + +create procedure test_signal() +begin + DECLARE foo CONDITION FOR SQLSTATE '12345'; + SIGNAL foo SET MYSQL_ERRNO = 1000, + MESSAGE_TEXT = 'Hello'; +end $$ + +-- error 1000 +call test_signal $$ +drop procedure test_signal $$ + +create procedure test_signal() +begin + DECLARE foo CONDITION FOR SQLSTATE '12345'; + SIGNAL foo SET MYSQL_ERRNO = 1000, + MESSAGE_TEXT = `Hello`; +end $$ + +-- error ER_BAD_FIELD_ERROR +call test_signal $$ +drop procedure test_signal $$ + +create procedure test_signal() +begin + DECLARE foo CONDITION FOR SQLSTATE '12345'; + SIGNAL foo SET MYSQL_ERRNO = 1000, + MESSAGE_TEXT = 65.4321; +end $$ + +-- error 1000 +call test_signal $$ +drop procedure test_signal $$ + +-- error ER_PARSE_ERROR +create procedure test_signal() +begin + DECLARE céèçà foo CONDITION FOR SQLSTATE '12345'; + SIGNAL céèçà SET MYSQL_ERRNO = 1000; +end $$ + +-- error ER_PARSE_ERROR +create procedure test_signal() +begin + DECLARE "céèçà " CONDITION FOR SQLSTATE '12345'; + SIGNAL "céèçà " SET MYSQL_ERRNO = 1000; +end $$ + +-- error ER_PARSE_ERROR +create procedure test_signal() +begin + DECLARE 'céèçà ' CONDITION FOR SQLSTATE '12345'; + SIGNAL 'céèçà ' SET MYSQL_ERRNO = 1000; +end $$ + +create procedure test_signal() +begin + DECLARE `céèçà ` CONDITION FOR SQLSTATE '12345'; + SIGNAL `céèçà ` SET MYSQL_ERRNO = 1000; +end $$ + +-- error 1000 +call test_signal $$ +drop procedure test_signal $$ + +create procedure test_signal() +begin + SIGNAL SQLSTATE '77777' SET MYSQL_ERRNO = 1000, MESSAGE_TEXT='ÃÂÃÅÄ'; +end $$ + +# Commented until WL#751 is implemented in this version +# -- error 1000 +# call test_signal $$ +drop procedure test_signal $$ + +delimiter ; $$ + diff --git a/mysql-test/t/signal_code.test b/mysql-test/t/signal_code.test new file mode 100644 index 00000000000..d2f65647c81 --- /dev/null +++ b/mysql-test/t/signal_code.test @@ -0,0 +1,57 @@ +# Copyright (C) 2008 Sun Microsystems, Inc +# +# This program is free software; you can redistribute it and/or modify +# it under the terms of the GNU General Public License as published by +# the Free Software Foundation; version 2 of the License. +# +# This program is distributed in the hope that it will be useful, +# but WITHOUT ANY WARRANTY; without even the implied warranty of +# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +# GNU General Public License for more details. +# +# You should have received a copy of the GNU General Public License +# along with this program; if not, write to the Free Software +# Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA + +# Tests for SIGNAL and RESIGNAL + +-- source include/have_debug.inc + +use test; + +--disable_warnings +drop procedure if exists signal_proc; +drop function if exists signal_func; +--enable_warnings + +delimiter $$; + +create procedure signal_proc() +begin + DECLARE foo CONDITION FOR SQLSTATE '12345'; + + SIGNAL foo; + SIGNAL foo SET MESSAGE_TEXT = "This is an error message"; + RESIGNAL foo; + RESIGNAL foo SET MESSAGE_TEXT = "This is an error message"; +end $$ + +create function signal_func() returns int +begin + DECLARE foo CONDITION FOR SQLSTATE '12345'; + + SIGNAL foo; + SIGNAL foo SET MESSAGE_TEXT = "This is an error message"; + RESIGNAL foo; + RESIGNAL foo SET MESSAGE_TEXT = "This is an error message"; + return 0; +end $$ + +delimiter ;$$ + +show procedure code signal_proc; +drop procedure signal_proc; + +show function code signal_func; +drop function signal_func; + diff --git a/mysql-test/t/signal_demo1.test b/mysql-test/t/signal_demo1.test new file mode 100644 index 00000000000..5de847ba0ba --- /dev/null +++ b/mysql-test/t/signal_demo1.test @@ -0,0 +1,345 @@ +# Copyright (C) 2008 Sun Microsystems, Inc +# +# This program is free software; you can redistribute it and/or modify +# it under the terms of the GNU General Public License as published by +# the Free Software Foundation; version 2 of the License. +# +# This program is distributed in the hope that it will be useful, +# but WITHOUT ANY WARRANTY; without even the implied warranty of +# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +# GNU General Public License for more details. +# +# You should have received a copy of the GNU General Public License +# along with this program; if not, write to the Free Software +# Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA + +# +# Demonstrate how SIGNAL can be used to enforce integrity constraints. +# + +# Naming: +# - PO: Purchase Order +# - AB: Address Book +# - IN: Inventory + +# Simplified schema: +# +# Relation 1: +# PO_ORDER (PK: po_id) 1:1 <---> 0:N (FK: po_id) PO_ORDER_LINE +# +# Relation 2: +# IN_INVENTORY (PK: item_id) 1:1 <---> 0:N (FK: item_id) PO_ORDER_LINE +# +# Relation 3: +# +--> 0:1 (PK: person_id) AB_PHYSICAL_PERSON +# PO_ORDER (FK: cust_id) 1:1 <--| +# +--> 0:1 (PK: company_id) AB_MORAL_PERSON +# This is an 'arc' relationship :) +# + + +--disable_warnings +drop database if exists demo; +--enable_warnings + +create database demo; + +use demo; + +create table ab_physical_person ( + person_id integer, + first_name VARCHAR(50), + middle_initial CHAR, + last_name VARCHAR(50), + primary key (person_id)); + +create table ab_moral_person ( + company_id integer, + name VARCHAR(100), + primary key (company_id)); + +create table in_inventory ( + item_id integer, + descr VARCHAR(50), + stock integer, + primary key (item_id)); + +create table po_order ( + po_id integer auto_increment, + cust_type char, /* arc relationship, see cust_id */ + cust_id integer, /* FK to ab_physical_person *OR* ab_moral_person */ + primary key (po_id)); + +create table po_order_line ( + po_id integer, /* FK to po_order.po_id */ + line_no integer, + item_id integer, /* FK to in_inventory.item_id */ + qty integer); + +delimiter $$; + +--echo # +--echo # Schema integrity enforcement +--echo # + +create procedure check_pk_person(in person_type char, in id integer) +begin + declare x integer; + declare msg varchar(128); + + /* + Test integrity constraints for an 'arc' relationship. + Based on 'person_type', 'id' points to either a + physical person, or a moral person. + */ + case person_type + when 'P' then + begin + select count(person_id) from ab_physical_person + where ab_physical_person.person_id = id + into x; + + if (x != 1) + then + set msg= concat('No such physical person, PK:', id); + SIGNAL SQLSTATE '45000' SET + MESSAGE_TEXT = msg, + MYSQL_ERRNO = 10000; + end if; + end; + + when 'M' then + begin + select count(company_id) from ab_moral_person + where ab_moral_person.company_id = id + into x; + + if (x != 1) + then + set msg= concat('No such moral person, PK:', id); + SIGNAL SQLSTATE '45000' SET + MESSAGE_TEXT = msg, + MYSQL_ERRNO = 10000; + end if; + end; + + else + begin + set msg= concat('No such person type:', person_type); + SIGNAL SQLSTATE '45000' SET + MESSAGE_TEXT = msg, + MYSQL_ERRNO = 20000; + end; + end case; +end +$$ + +create procedure check_pk_inventory(in id integer) +begin + declare x integer; + declare msg varchar(128); + + select count(item_id) from in_inventory + where in_inventory.item_id = id + into x; + + if (x != 1) + then + set msg= concat('Failed integrity constraint, table in_inventory, PK:', + id); + SIGNAL SQLSTATE '45000' SET + MESSAGE_TEXT = msg, + MYSQL_ERRNO = 10000; + end if; +end +$$ + +create procedure check_pk_order(in id integer) +begin + declare x integer; + declare msg varchar(128); + + select count(po_id) from po_order + where po_order.po_id = id + into x; + + if (x != 1) + then + set msg= concat('Failed integrity constraint, table po_order, PK:', id); + SIGNAL SQLSTATE '45000' SET + MESSAGE_TEXT = msg, + MYSQL_ERRNO = 10000; + end if; +end +$$ + +create trigger po_order_bi before insert on po_order +for each row +begin + call check_pk_person(NEW.cust_type, NEW.cust_id); +end +$$ + +create trigger po_order_bu before update on po_order +for each row +begin + call check_pk_person(NEW.cust_type, NEW.cust_id); +end +$$ + +create trigger po_order_line_bi before insert on po_order_line +for each row +begin + call check_pk_order(NEW.po_id); + call check_pk_inventory(NEW.item_id); +end +$$ + +create trigger po_order_line_bu before update on po_order_line +for each row +begin + call check_pk_order(NEW.po_id); + call check_pk_inventory(NEW.item_id); +end +$$ + +--echo # +--echo # Application helpers +--echo # + +create procedure po_create_order( + in p_cust_type char, + in p_cust_id integer, + out id integer) +begin + insert into po_order set cust_type = p_cust_type, cust_id = p_cust_id; + set id = last_insert_id(); +end +$$ + +create procedure po_add_order_line( + in po integer, + in line integer, + in item integer, + in q integer) +begin + insert into po_order_line set + po_id = po, line_no = line, item_id = item, qty = q; +end +$$ + +delimiter ;$$ + +--echo # +--echo # Create sample data +--echo # + +insert into ab_physical_person values + ( 1, "John", "A", "Doe"), + ( 2, "Marry", "B", "Smith") +; + +insert into ab_moral_person values + ( 3, "ACME real estate, INC"), + ( 4, "Local school") +; + +insert into in_inventory values + ( 100, "Table, dinner", 5), + ( 101, "Chair", 20), + ( 200, "Table, coffee", 3), + ( 300, "School table", 25), + ( 301, "School chairs", 50) +; + +select * from ab_physical_person order by person_id; +select * from ab_moral_person order by company_id; +select * from in_inventory order by item_id; + +--echo # +--echo # Entering an order +--echo # + +set @my_po = 0; + +/* John Doe wants 1 table and 4 chairs */ +call po_create_order("P", 1, @my_po); + +call po_add_order_line (@my_po, 1, 100, 1); +call po_add_order_line (@my_po, 2, 101, 4); + +/* Marry Smith wants a coffee table */ +call po_create_order("P", 2, @my_po); + +call po_add_order_line (@my_po, 1, 200, 1); + +--echo # +--echo # Entering bad data in an order +--echo # + +# There is no item 999 in in_inventory +--error 10000 +call po_add_order_line (@my_po, 1, 999, 1); + +--echo # +--echo # Entering bad data in an unknown order +--echo # + +# There is no order 99 in po_order +--error 10000 +call po_add_order_line (99, 1, 100, 1); + +--echo # +--echo # Entering an order for an unknown company +--echo # + +# There is no moral person of id 7 +--error 10000 +call po_create_order("M", 7, @my_po); + +--echo # +--echo # Entering an order for an unknown person type +--echo # + +# There is no person of type X +--error 20000 +call po_create_order("X", 1, @my_po); + +/* The local school wants 10 class tables and 20 chairs */ +call po_create_order("M", 4, @my_po); + +call po_add_order_line (@my_po, 1, 300, 10); +call po_add_order_line (@my_po, 2, 301, 20); + +# Raw data +select * from po_order; +select * from po_order_line; + +# Creative reporting ... + +select po_id as "PO#", + ( case cust_type + when "P" then concat (pp.first_name, + " ", + pp.middle_initial, + " ", + pp.last_name) + when "M" then mp.name + end ) as "Sold to" + from po_order po + left join ab_physical_person pp on po.cust_id = pp.person_id + left join ab_moral_person mp on po.cust_id = company_id +; + +select po_id as "PO#", + ol.line_no as "Line", + ol.item_id as "Item", + inv.descr as "Description", + ol.qty as "Quantity" + from po_order_line ol, in_inventory inv + where inv.item_id = ol.item_id + order by ol.item_id, ol.line_no; + +drop database demo; + + diff --git a/mysql-test/t/signal_demo2.test b/mysql-test/t/signal_demo2.test new file mode 100644 index 00000000000..fc909cb926c --- /dev/null +++ b/mysql-test/t/signal_demo2.test @@ -0,0 +1,207 @@ +# Copyright (C) 2008 Sun Microsystems, Inc +# +# This program is free software; you can redistribute it and/or modify +# it under the terms of the GNU General Public License as published by +# the Free Software Foundation; version 2 of the License. +# +# This program is distributed in the hope that it will be useful, +# but WITHOUT ANY WARRANTY; without even the implied warranty of +# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +# GNU General Public License for more details. +# +# You should have received a copy of the GNU General Public License +# along with this program; if not, write to the Free Software +# Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA + +# +# Demonstrate how RESIGNAL can be used to 'catch' and 're-throw' an error +# + +--disable_warnings +drop database if exists demo; +--enable_warnings + +create database demo; + +use demo; + +delimiter $$; + +create procedure proc_top_a(p1 integer) +begin + ## DECLARE CONTINUE HANDLER for SQLEXCEPTION, NOT FOUND + begin + end; + + select "Starting ..."; + call proc_middle_a(p1); + select "The end"; +end +$$ + +create procedure proc_middle_a(p1 integer) +begin + DECLARE l integer; + # without RESIGNAL: + # Should be: DECLARE EXIT HANDLER for SQLEXCEPTION, NOT FOUND + DECLARE EXIT HANDLER for 1 /* not sure how to handle exceptions */ + begin + select "Oops ... now what ?"; + end; + + select "In prod_middle()"; + + create temporary table t1(a integer, b integer); + select GET_LOCK("user_mutex", 10) into l; + + insert into t1 set a = p1, b = p1; + + call proc_bottom_a(p1); + + select RELEASE_LOCK("user_mutex") into l; + drop temporary table t1; +end +$$ + +create procedure proc_bottom_a(p1 integer) +begin + select "In proc_bottom()"; + + if (p1 = 1) then + begin + select "Doing something that works ..."; + select * from t1; + end; + end if; + + if (p1 = 2) then + begin + select "Doing something that fail (simulate an error) ..."; + drop table no_such_table; + end; + end if; + + if (p1 = 3) then + begin + select "Doing something that *SHOULD* works ..."; + select * from t1; + end; + end if; + +end +$$ + +delimiter ;$$ + +# +# Code without RESIGNAL: +# errors are apparent to the caller, +# but there is no cleanup code, +# so that the environment (get_lock(), temporary table) is polluted ... +# +call proc_top_a(1); + +# Expected +--error ER_BAD_TABLE_ERROR +call proc_top_a(2); + +# Dirty state +--error ER_TABLE_EXISTS_ERROR +call proc_top_a(3); + +# Dirty state +--error ER_TABLE_EXISTS_ERROR +call proc_top_a(1); + +drop temporary table if exists t1; + +delimiter $$; + +create procedure proc_top_b(p1 integer) +begin + select "Starting ..."; + call proc_middle_b(p1); + select "The end"; +end +$$ + +create procedure proc_middle_b(p1 integer) +begin + DECLARE l integer; + DECLARE EXIT HANDLER for SQLEXCEPTION, NOT FOUND + begin + begin + DECLARE CONTINUE HANDLER for SQLEXCEPTION, NOT FOUND + begin + /* Ignore errors from the cleanup code */ + end; + + select "Doing cleanup !"; + select RELEASE_LOCK("user_mutex") into l; + drop temporary table t1; + end; + + RESIGNAL; + end; + + select "In prod_middle()"; + + create temporary table t1(a integer, b integer); + select GET_LOCK("user_mutex", 10) into l; + + insert into t1 set a = p1, b = p1; + + call proc_bottom_b(p1); + + select RELEASE_LOCK("user_mutex") into l; + drop temporary table t1; +end +$$ + +create procedure proc_bottom_b(p1 integer) +begin + select "In proc_bottom()"; + + if (p1 = 1) then + begin + select "Doing something that works ..."; + select * from t1; + end; + end if; + + if (p1 = 2) then + begin + select "Doing something that fail (simulate an error) ..."; + drop table no_such_table; + end; + end if; + + if (p1 = 3) then + begin + select "Doing something that *SHOULD* works ..."; + select * from t1; + end; + end if; + +end +$$ + +delimiter ;$$ + +# +# Code with RESIGNAL: +# errors are apparent to the caller, +# the but cleanup code did get a chance to act ... +# + +call proc_top_b(1); + +--error ER_BAD_TABLE_ERROR +call proc_top_b(2); + +call proc_top_b(3); + +call proc_top_b(1); + +drop database demo; + diff --git a/mysql-test/t/signal_demo3.test b/mysql-test/t/signal_demo3.test new file mode 100644 index 00000000000..347f1b75a79 --- /dev/null +++ b/mysql-test/t/signal_demo3.test @@ -0,0 +1,159 @@ +# Copyright (C) 2008 Sun Microsystems, Inc +# +# This program is free software; you can redistribute it and/or modify +# it under the terms of the GNU General Public License as published by +# the Free Software Foundation; version 2 of the License. +# +# This program is distributed in the hope that it will be useful, +# but WITHOUT ANY WARRANTY; without even the implied warranty of +# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +# GNU General Public License for more details. +# +# You should have received a copy of the GNU General Public License +# along with this program; if not, write to the Free Software +# Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA + +# +# Demonstrate how RESIGNAL can be used to print a stack trace +# + +# Save defaults + +SET @start_global_value = @@global.max_error_count; +SELECT @start_global_value; +SET @start_session_value = @@session.max_error_count; +SELECT @start_session_value; + +--disable_warnings +drop database if exists demo; +--enable_warnings + +create database demo; + +use demo; + +delimiter $$; + +create procedure proc_1() +begin + declare exit handler for sqlexception + resignal sqlstate '45000' set message_text='Oops in proc_1'; + + call proc_2(); +end +$$ + +create procedure proc_2() +begin + declare exit handler for sqlexception + resignal sqlstate '45000' set message_text='Oops in proc_2'; + + call proc_3(); +end +$$ + +create procedure proc_3() +begin + declare exit handler for sqlexception + resignal sqlstate '45000' set message_text='Oops in proc_3'; + + call proc_4(); +end +$$ + +create procedure proc_4() +begin + declare exit handler for sqlexception + resignal sqlstate '45000' set message_text='Oops in proc_4'; + + call proc_5(); +end +$$ + +create procedure proc_5() +begin + declare exit handler for sqlexception + resignal sqlstate '45000' set message_text='Oops in proc_5'; + + call proc_6(); +end +$$ + +create procedure proc_6() +begin + declare exit handler for sqlexception + resignal sqlstate '45000' set message_text='Oops in proc_6'; + + call proc_7(); +end +$$ + +create procedure proc_7() +begin + declare exit handler for sqlexception + resignal sqlstate '45000' set message_text='Oops in proc_7'; + + call proc_8(); +end +$$ + +create procedure proc_8() +begin + declare exit handler for sqlexception + resignal sqlstate '45000' set message_text='Oops in proc_8'; + + call proc_9(); +end +$$ + +create procedure proc_9() +begin + declare exit handler for sqlexception + resignal sqlstate '45000' set message_text='Oops in proc_9'; + + ## Do something that fails, to see how errors are reported + drop table oops_it_is_not_here; +end +$$ + +delimiter ;$$ + +-- error ER_SIGNAL_EXCEPTION +call proc_1(); + +# This is the interesting part: +# the complete call stack from the origin of failure (proc_9) +# to the top level caller (proc_1) is available ... + +show warnings; + +SET @@session.max_error_count = 5; +SELECT @@session.max_error_count; + +-- error ER_SIGNAL_EXCEPTION +call proc_1(); +show warnings; + +SET @@session.max_error_count = 7; +SELECT @@session.max_error_count; + +-- error ER_SIGNAL_EXCEPTION +call proc_1(); +show warnings; + +SET @@session.max_error_count = 9; +SELECT @@session.max_error_count; + +-- error ER_SIGNAL_EXCEPTION +call proc_1(); +show warnings; + +drop database demo; + +# Restore defaults + +SET @@global.max_error_count = @start_global_value; +SELECT @@global.max_error_count; +SET @@session.max_error_count = @start_session_value; +SELECT @@session.max_error_count; + diff --git a/mysql-test/t/signal_sqlmode.test b/mysql-test/t/signal_sqlmode.test new file mode 100644 index 00000000000..860c145a361 --- /dev/null +++ b/mysql-test/t/signal_sqlmode.test @@ -0,0 +1,123 @@ +# Copyright (C) 2008 Sun Microsystems, Inc +# +# This program is free software; you can redistribute it and/or modify +# it under the terms of the GNU General Public License as published by +# the Free Software Foundation; version 2 of the License. +# +# This program is distributed in the hope that it will be useful, +# but WITHOUT ANY WARRANTY; without even the implied warranty of +# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +# GNU General Public License for more details. +# +# You should have received a copy of the GNU General Public License +# along with this program; if not, write to the Free Software +# Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA + +# Tests for SIGNAL, RESIGNAL and GET DIAGNOSTICS + +SET @save_sql_mode=@@sql_mode; + +SET sql_mode=''; + +--disable_warnings +drop procedure if exists p; +drop procedure if exists p2; +drop procedure if exists p3; +--enable_warnings + +delimiter $$; + +create procedure p() +begin + declare utf8_var VARCHAR(128) CHARACTER SET UTF8; + set utf8_var = concat(repeat('A', 128), 'X'); + select length(utf8_var), utf8_var; +end +$$ + +create procedure p2() +begin + declare msg VARCHAR(129) CHARACTER SET UTF8; + set msg = concat(repeat('A', 128), 'X'); + select length(msg), msg; + + signal sqlstate '55555' set message_text = msg; +end +$$ + +create procedure p3() +begin + declare name VARCHAR(65) CHARACTER SET UTF8; + set name = concat(repeat('A', 64), 'X'); + select length(name), name; + + signal sqlstate '55555' set + message_text = 'Message', + table_name = name; +end +$$ +delimiter ;$$ + +call p; + +--error ER_SIGNAL_EXCEPTION +call p2; + +--error ER_SIGNAL_EXCEPTION +call p3; + +drop procedure p; +drop procedure p2; +drop procedure p3; + +SET sql_mode='STRICT_ALL_TABLES'; + +delimiter $$; + +create procedure p() +begin + declare utf8_var VARCHAR(128) CHARACTER SET UTF8; + set utf8_var = concat(repeat('A', 128), 'X'); + select length(utf8_var), utf8_var; +end +$$ + +create procedure p2() +begin + declare msg VARCHAR(129) CHARACTER SET UTF8; + set msg = concat(repeat('A', 128), 'X'); + select length(msg), msg; + + signal sqlstate '55555' set message_text = msg; +end +$$ + +create procedure p3() +begin + declare name VARCHAR(65) CHARACTER SET UTF8; + set name = concat(repeat('A', 64), 'X'); + select length(name), name; + + signal sqlstate '55555' set + message_text = 'Message', + table_name = name; +end +$$ + +delimiter ;$$ + +--error ER_DATA_TOO_LONG +call p; + +--error ER_COND_ITEM_TOO_LONG +call p2; + +--error ER_COND_ITEM_TOO_LONG +call p3; + +drop procedure p; +drop procedure p2; +drop procedure p3; + +SET @@sql_mode=@save_sql_mode; + diff --git a/mysql-test/t/sp-no-code.test b/mysql-test/t/sp-no-code.test new file mode 100644 index 00000000000..ce16c32a270 --- /dev/null +++ b/mysql-test/t/sp-no-code.test @@ -0,0 +1,12 @@ +# +# Test the debugging feature "show procedure/function code <name>" +# + +-- source include/have_nodebug.inc + +--error ER_FEATURE_DISABLED +show procedure code foo; + +--error ER_FEATURE_DISABLED +show function code foo; + diff --git a/mysql-test/t/sp-vars.test b/mysql-test/t/sp-vars.test index fe063889f81..2e7257cbcea 100644 --- a/mysql-test/t/sp-vars.test +++ b/mysql-test/t/sp-vars.test @@ -1448,3 +1448,42 @@ drop function f1; drop table t1; # End of 5.1 tests. + +########################################################################### +# +# Test case for BUG#28299: To-number conversion warnings work +# differenly with CHAR and VARCHAR sp variables +# +########################################################################### + +--echo +--echo --------------------------------------------------------------- +--echo BUG#28299 +--echo --------------------------------------------------------------- +--echo + +DELIMITER |; +CREATE PROCEDURE ctest() +BEGIN + DECLARE i CHAR(16); + DECLARE j INT; + SET i= 'string'; + SET j= 1 + i; +END| +DELIMITER ;| + +CALL ctest(); +DROP PROCEDURE ctest; + +DELIMITER |; +CREATE PROCEDURE vctest() +BEGIN + DECLARE i VARCHAR(16); + DECLARE j INT; + SET i= 'string'; + SET j= 1 + i; +END| +DELIMITER ;| + +CALL vctest(); +DROP PROCEDURE vctest; diff --git a/mysql-test/t/sp.test b/mysql-test/t/sp.test index 73ba62612b8..b35c10450a2 100644 --- a/mysql-test/t/sp.test +++ b/mysql-test/t/sp.test @@ -2882,7 +2882,6 @@ create procedure bug4902() begin show charset like 'foo'; show collation like 'foo'; - show column types; show create table t1; show create database test; show databases like 'foo'; @@ -8334,3 +8333,53 @@ DROP PROCEDURE p5; --echo # ------------------------------------------------------------------ --echo # -- End of 5.1 tests --echo # ------------------------------------------------------------------ + +# +# Bug#39255: Stored procedures: crash if function references nonexistent table +# + +--disable_warnings +DROP FUNCTION IF EXISTS f1; +DROP TABLE IF EXISTS t_non_existing; +DROP TABLE IF EXISTS t1; +--enable_warnings + +delimiter |; +CREATE FUNCTION f1() RETURNS INT +BEGIN + DECLARE v INT; + SELECT a INTO v FROM t_non_existing; + RETURN 1; +END| +delimiter ;| + +CREATE TABLE t1 (a INT) ENGINE = myisam; +INSERT INTO t1 VALUES (1); + +--error ER_NO_SUCH_TABLE +SELECT * FROM t1 WHERE a = f1(); + +DROP FUNCTION f1; +DROP TABLE t1; + +--echo # +--echo # Bug#34197: CREATE PROCEDURE fails when COMMENT truncated in non +--echo # strict SQL mode +--echo # + +--disable_warnings +DROP PROCEDURE IF EXISTS p1; +--enable_warnings + +CREATE PROCEDURE p1 () +COMMENT +'12345678901234567890123456789012345678901234567890123456789012345678901234567890' +BEGIN +END; + +SELECT comment FROM mysql.proc WHERE name = "p1"; + +SELECT routine_comment FROM information_schema.routines WHERE routine_name = "p1"; + +DROP PROCEDURE p1; + diff --git a/mysql-test/t/sp_trans.test b/mysql-test/t/sp_trans.test index 0b04b9d7668..ce18b2b578e 100644 --- a/mysql-test/t/sp_trans.test +++ b/mysql-test/t/sp_trans.test @@ -592,6 +592,44 @@ select distinct f1, bug13575(f1) from t3 order by f1| drop function bug13575| drop table t3| +--echo # +--echo # End of 5.1 tests +--echo # + +--echo # +--echo # Bug #35877 Update .. WHERE with function, constraint violation, crash +--echo # + +--disable_warnings +DROP TABLE IF EXISTS t1_aux| +DROP TABLE IF EXISTS t1_not_null| +DROP FUNCTION IF EXISTS f1_two_inserts| +--enable_warnings + +-- echo # MyISAM test +CREATE TABLE t1_not_null (f1 BIGINT, f2 BIGINT NOT NULL)| +CREATE TABLE t1_aux (f1 BIGINT, f2 BIGINT)| +INSERT INTO t1_aux VALUES (1,1)| + +CREATE FUNCTION f1_two_inserts() returns INTEGER +BEGIN + INSERT INTO t1_not_null SET f1 = 10, f2 = NULL; + RETURN 1; +END| + +-- error ER_BAD_NULL_ERROR +UPDATE t1_aux SET f2 = 2 WHERE f1 = f1_two_inserts()| + +-- echo # InnoDB test +ALTER TABLE t1_not_null ENGINE = InnoDB| +ALTER TABLE t1_aux ENGINE = InnoDB| + +-- error ER_BAD_NULL_ERROR +UPDATE t1_aux SET f2 = 2 WHERE f1 = f1_two_inserts()| + +DROP TABLE t1_aux, t1_not_null| +DROP FUNCTION f1_two_inserts| + # # BUG#NNNN: New bug synopsis diff --git a/mysql-test/t/sp_trans_log.test b/mysql-test/t/sp_trans_log.test index 2f2b84a9bef..68467f71ee1 100644 --- a/mysql-test/t/sp_trans_log.test +++ b/mysql-test/t/sp_trans_log.test @@ -35,7 +35,8 @@ reset master| --error ER_DUP_ENTRY insert into t2 values (bug23333(),1)| --replace_column 2 # 5 # 6 # -show binlog events from 106 /* with fixes for #23333 will show there is the query */| +# the following must show there is (are) events after the query */ +source include/show_binlog_events.inc| select count(*),@a from t1 /* must be 1,1 */| delimiter ;| diff --git a/mysql-test/t/sql_mode.test b/mysql-test/t/sql_mode.test index 4a9f34443cb..27956bc8877 100644 --- a/mysql-test/t/sql_mode.test +++ b/mysql-test/t/sql_mode.test @@ -309,6 +309,21 @@ flush privileges; --connection default drop user mysqltest_32753@localhost; + +# +# Bug#21099 MySQL 5.0.22 silently creates MyISAM tables even though +# InnoDB specified. +# + +SET @org_mode=@@sql_mode; +SET @@sql_mode='traditional'; + +# Agreed change was to add NO_ENGINE_SUBSTITUTION to TRADITIONAL sql mode. +SELECT @@sql_mode LIKE '%NO_ENGINE_SUBSTITUTION%'; + +SET sql_mode=@org_mode; + + # # Bug#45100: Incomplete DROP USER in case of SQL_MODE = 'PAD_CHAR_TO_FULL_LENGTH' # diff --git a/mysql-test/t/trigger.test b/mysql-test/t/trigger.test index 1e55f9d5993..3ab724a835a 100644 --- a/mysql-test/t/trigger.test +++ b/mysql-test/t/trigger.test @@ -2397,3 +2397,31 @@ SELECT * FROM t2; DROP TABLE t1, t2; --echo End of 5.1 tests. + + +--echo # +--echo # Bug#34453 Can't change size of file (Errcode: 1224) +--echo # + +--disable_warnings +DROP TRIGGER IF EXISTS t1_bi; +DROP TRIGGER IF EXISTS t1_bd; +DROP TABLE IF EXISTS t1; +DROP TEMPORARY TABLE IF EXISTS t2; +--enable_warnings + +CREATE TABLE t1 (s1 INT); +CREATE TEMPORARY TABLE t2 (s1 INT); +CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW INSERT INTO t2 VALUES (0); +CREATE TRIGGER t1_bd BEFORE DELETE ON t1 FOR EACH ROW DELETE FROM t2; +INSERT INTO t1 VALUES (0); +INSERT INTO t1 VALUES (0); +SELECT * FROM t1; +SELECT * FROM t2; +-- echo # Reported to give ERROR 14 (HY000): +-- echo # Can't change size of file (Errcode: 1224) +-- echo # on Windows +DELETE FROM t1; + +DROP TABLE t1; +DROP TEMPORARY TABLE t2; diff --git a/mysql-test/t/type_date.test b/mysql-test/t/type_date.test index aec60bc2dee..db273f90425 100644 --- a/mysql-test/t/type_date.test +++ b/mysql-test/t/type_date.test @@ -247,3 +247,21 @@ select * from t1 where a between '0000-00-01' and '0000-00-02'; drop table t1; --echo End of 5.1 tests + +--echo # +--echo # Bug #33629: last_day function can return null, but has 'not null' +--echo # flag set for result +--echo # + +SELECT 1 FROM (SELECT LAST_DAY('0')) a; +SELECT 1 FROM (SELECT MAKEDATE(2011,0)) a; + +CREATE TABLE t1 AS + SELECT LAST_DAY('1970-01-01') AS f1, + MAKEDATE(1970, 1) AS f2; +DESCRIBE t1; +DROP TABLE t1; + +--echo # + +--echo End of 6.0 tests diff --git a/mysql-test/t/type_decimal.test b/mysql-test/t/type_decimal.test index 8a81908296f..dfe36ed0905 100644 --- a/mysql-test/t/type_decimal.test +++ b/mysql-test/t/type_decimal.test @@ -8,13 +8,13 @@ SET SQL_WARNINGS=1; CREATE TABLE t1 ( id int(11) NOT NULL auto_increment, datatype_id int(11) DEFAULT '0' NOT NULL, - minvalue decimal(20,10) DEFAULT '0.0000000000' NOT NULL, - maxvalue decimal(20,10) DEFAULT '0.0000000000' NOT NULL, + min_value decimal(20,10) DEFAULT '0.0000000000' NOT NULL, + max_value decimal(20,10) DEFAULT '0.0000000000' NOT NULL, valuename varchar(20), forecolor int(11), backcolor int(11), PRIMARY KEY (id), - UNIQUE datatype_id (datatype_id, minvalue, maxvalue) + UNIQUE datatype_id (datatype_id, min_value, max_value) ); INSERT INTO t1 VALUES ( '1', '4', '0.0000000000', '0.0000000000', 'Ei saja', '0', '16776960'); INSERT INTO t1 VALUES ( '2', '4', '1.0000000000', '1.0000000000', 'Sajab', '16777215', '255'); @@ -148,8 +148,8 @@ INSERT INTO t1 VALUES ( '139', '21', '326.0000000000', '326.0000000000', 'Lumine INSERT INTO t1 VALUES ( '143', '16', '-4.9000000000', '-0.1000000000', '', NULL, '15774720'); INSERT INTO t1 VALUES ( '145', '15', '0.0000000000', '1.9000000000', '', '0', '16769024'); INSERT INTO t1 VALUES ( '146', '16', '0.0000000000', '1.9000000000', '', '0', '16769024'); -select * from t1 where minvalue<=1 and maxvalue>=-1 and datatype_id=16; -select * from t1 where minvalue<=-1 and maxvalue>=-1 and datatype_id=16; +select * from t1 where min_value<=1 and max_value>=-1 and datatype_id=16; +select * from t1 where min_value<=-1 and max_value>=-1 and datatype_id=16; drop table t1; # diff --git a/mysql-test/t/type_varchar.test b/mysql-test/t/type_varchar.test index 9098881e379..33b84266118 100644 --- a/mysql-test/t/type_varchar.test +++ b/mysql-test/t/type_varchar.test @@ -199,3 +199,21 @@ SELECT a,(a + 0) FROM t1 ORDER BY a; SELECT a,(a DIV 2) FROM t1 ORDER BY a; SELECT a,CAST(a AS SIGNED) FROM t1 ORDER BY a; DROP TABLE t1; + +# +# Bug #28299: To-number conversion warnings work differenly with CHAR +# and VARCHAR sp variables +# +# * Verify that 'Truncated incorrect DOUBLE value' is shown for 's' +# when using both CHAR and VARCHAR. +# + +CREATE TABLE t1 (a VARCHAR(16)); +INSERT INTO t1 VALUES ('5'), ('s'), (''); +SELECT 5 = a FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (a CHAR(16)); +INSERT INTO t1 VALUES ('5'), ('s'), (''); +SELECT 5 = a FROM t1; +DROP TABLE t1; diff --git a/mysql-test/t/variables+c.test b/mysql-test/t/variables+c.test index 0092d34133d..aa4bc6c631c 100644 --- a/mysql-test/t/variables+c.test +++ b/mysql-test/t/variables+c.test @@ -1,4 +1,4 @@ ---source include/have_community_features.inc +--source include/have_profiling.inc # # Bug#24822: Patch: uptime_since_flush_status diff --git a/mysql-test/t/variables.test b/mysql-test/t/variables.test index 1580d7f36d7..bf91dbf570b 100644 --- a/mysql-test/t/variables.test +++ b/mysql-test/t/variables.test @@ -559,7 +559,7 @@ select @@lc_time_names; --echo *** LC_TIME_NAMES: testing with string expressions set lc_time_names=concat('de','_','DE'); select @@lc_time_names; ---error ER_UNKNOWN_ERROR +--error ER_UNKNOWN_LOCALE set lc_time_names=concat('de','+','DE'); select @@lc_time_names; --echo LC_TIME_NAMES: testing with numeric expressions @@ -572,15 +572,15 @@ set lc_time_names=en_US; --echo LC_TIME_NAMES: testing NULL and a negative number: --error ER_WRONG_VALUE_FOR_VAR set lc_time_names=NULL; ---error ER_UNKNOWN_ERROR +--error ER_UNKNOWN_LOCALE set lc_time_names=-1; select @@lc_time_names; --echo LC_TIME_NAMES: testing locale with the last ID: -set lc_time_names=108; +set lc_time_names=109; select @@lc_time_names; --echo LC_TIME_NAMES: testing a number beyond the valid ID range: ---error ER_UNKNOWN_ERROR -set lc_time_names=109; +--error ER_UNKNOWN_LOCALE +set lc_time_names=110; select @@lc_time_names; --echo LC_TIME_NAMES: testing that 0 is en_US: set lc_time_names=0; @@ -948,15 +948,15 @@ SET @@global.init_file= 'x'; # --echo # --replace_column 2 # -SHOW VARIABLES like 'language'; +SHOW VARIABLES like 'lc_messages_dir'; --error ER_INCORRECT_GLOBAL_LOCAL_VAR -SELECT @@session.language; +SELECT @@session.lc_messages_dir; --replace_column 1 # -SELECT @@global.language; +SELECT @@global.lc_messages_dir; --error ER_INCORRECT_GLOBAL_LOCAL_VAR -SET @@session.language= 'x'; +SET @@session.lc_messages_dir= 'x'; --error ER_INCORRECT_GLOBAL_LOCAL_VAR -SET @@global.language= 'x'; +SET @@global.lc_messages_dir= 'x'; # --echo # --replace_column 2 # @@ -1227,3 +1227,165 @@ SET GLOBAL max_binlog_cache_size = 5 * 1024 * 1024 * 1024; SELECT @@GLOBAL.max_binlog_cache_size; SET GLOBAL max_binlog_cache_size = @old_max_binlog_cache_size; --echo End of 5.1 tests + +########################################################################### + +--echo +--echo # +--echo # Bug#34828: OF is taken as OFF and a value of 0 is set for variable SQL_notes. +--echo # +--echo + +--echo # Checking sql_notes... +SET @sql_notes_saved = @@sql_notes; + +--echo +SET @@sql_notes = ON; +SELECT @@sql_notes; + +--echo +--error ER_WRONG_VALUE_FOR_VAR +SET @@sql_notes = OF; +SELECT @@sql_notes; + +--echo +SET @@sql_notes = OFF; +SELECT @@sql_notes; + +--echo +SET @@sql_notes = @sql_notes_saved; + +--echo +--echo # Checking delay_key_write... +SET @delay_key_write_saved = @@delay_key_write; + +--echo +SET GLOBAL delay_key_write = ON; +SELECT @@delay_key_write; + +--echo +--error ER_WRONG_VALUE_FOR_VAR +SET GLOBAL delay_key_write = OF; +SELECT @@delay_key_write; + +--echo +--error ER_WRONG_VALUE_FOR_VAR +SET GLOBAL delay_key_write = AL; +SELECT @@delay_key_write; + +--echo +SET GLOBAL delay_key_write = OFF; +SELECT @@delay_key_write; + +--echo +SET GLOBAL delay_key_write = ALL; +SELECT @@delay_key_write; + +--echo +SET GLOBAL delay_key_write = @delay_key_write_saved; + +--echo +--echo # Checking sql_safe_updates... +SET @sql_safe_updates_saved = @@sql_safe_updates; + +--echo +SET @@sql_safe_updates = ON; +SELECT @@sql_safe_updates; + +--echo +--error ER_WRONG_VALUE_FOR_VAR +SET @@sql_safe_updates = OF; +SELECT @@sql_safe_updates; + +--echo +SET @@sql_safe_updates = OFF; +SELECT @@sql_safe_updates; + +--echo +SET @@sql_safe_updates = @sql_safe_updates_saved; + +--echo +--echo # Checking foreign_key_checks... +SET @foreign_key_checks_saved = @@foreign_key_checks; + +--echo +SET @@foreign_key_checks = ON; +SELECT @@foreign_key_checks; + +--echo +--error ER_WRONG_VALUE_FOR_VAR +SET @@foreign_key_checks = OF; +SELECT @@foreign_key_checks; + +--echo +SET @@foreign_key_checks = OFF; +SELECT @@foreign_key_checks; + +--echo +SET @@foreign_key_checks = @foreign_key_checks_saved; + +--echo +--echo # Checking unique_checks... +SET @unique_checks_saved = @@unique_checks; + +--echo +SET @@unique_checks = ON; +SELECT @@unique_checks; + +--echo +--error ER_WRONG_VALUE_FOR_VAR +SET @@unique_checks = OF; +SELECT @@unique_checks; + +--echo +SET @@unique_checks = OFF; +SELECT @@unique_checks; + +--echo +SET @@unique_checks = @unique_checks_saved; + +--echo +--echo # Checking sql_buffer_result... +SET @sql_buffer_result_saved = @@sql_buffer_result; + +--echo +SET @@sql_buffer_result = ON; +SELECT @@sql_buffer_result; + +--echo +--error ER_WRONG_VALUE_FOR_VAR +SET @@sql_buffer_result = OF; +SELECT @@sql_buffer_result; + +--echo +SET @@sql_buffer_result = OFF; +SELECT @@sql_buffer_result; + +--echo +SET @@sql_buffer_result = @sql_buffer_result_saved; + +--echo +--echo # Checking sql_quote_show_create... +SET @sql_quote_show_create_saved = @@sql_quote_show_create; + +--echo +SET @@sql_quote_show_create = ON; +SELECT @@sql_quote_show_create; + +--echo +--error ER_WRONG_VALUE_FOR_VAR +SET @@sql_quote_show_create = OF; +SELECT @@sql_quote_show_create; + +--echo +SET @@sql_quote_show_create = OFF; +SELECT @@sql_quote_show_create; + +--echo +SET @@sql_quote_show_create = @sql_quote_show_create_saved; + +--echo +--echo # End of Bug#34828. +--echo + +########################################################################### diff --git a/mysql-test/t/view_grant.test b/mysql-test/t/view_grant.test index 175468db702..f01edb1e499 100644 --- a/mysql-test/t/view_grant.test +++ b/mysql-test/t/view_grant.test @@ -1072,12 +1072,12 @@ CREATE USER u29908_1@localhost; CREATE DEFINER = u29908_1@localhost VIEW v1 AS SELECT f1 FROM t1; CREATE DEFINER = u29908_1@localhost SQL SECURITY INVOKER VIEW v2 AS SELECT f1 FROM t1; -GRANT DROP, CREATE VIEW, SHOW VIEW ON mysqltest_29908.v1 TO u29908_1@localhost; -GRANT DROP, CREATE VIEW, SHOW VIEW ON mysqltest_29908.v2 TO u29908_1@localhost; +GRANT SELECT, DROP, CREATE VIEW, SHOW VIEW ON mysqltest_29908.v1 TO u29908_1@localhost; +GRANT SELECT, DROP, CREATE VIEW, SHOW VIEW ON mysqltest_29908.v2 TO u29908_1@localhost; GRANT SELECT ON mysqltest_29908.t1 TO u29908_1@localhost; CREATE USER u29908_2@localhost; -GRANT DROP, CREATE VIEW ON mysqltest_29908.v1 TO u29908_2@localhost; -GRANT DROP, CREATE VIEW, SHOW VIEW ON mysqltest_29908.v2 TO u29908_2@localhost; +GRANT SELECT, DROP, CREATE VIEW ON mysqltest_29908.v1 TO u29908_2@localhost; +GRANT SELECT, DROP, CREATE VIEW, SHOW VIEW ON mysqltest_29908.v2 TO u29908_2@localhost; GRANT SELECT ON mysqltest_29908.t1 TO u29908_2@localhost; connect (u2,localhost,u29908_2,,mysqltest_29908); |