diff options
Diffstat (limited to 'mysql-test/t')
44 files changed, 4301 insertions, 31 deletions
diff --git a/mysql-test/t/almost_full.test b/mysql-test/t/almost_full.test index 5c67ab3c088..24801f9cbc3 100644 --- a/mysql-test/t/almost_full.test +++ b/mysql-test/t/almost_full.test @@ -2,6 +2,8 @@ # Some special cases with empty tables # +call mtr.add_suppression("The table 't1' is full"); + --disable_warnings drop table if exists t1; --enable_warnings diff --git a/mysql-test/t/analyse.test b/mysql-test/t/analyse.test index efcf5f6421c..d8466df14bf 100644 --- a/mysql-test/t/analyse.test +++ b/mysql-test/t/analyse.test @@ -14,6 +14,7 @@ create table t2 select * from t1 procedure analyse(); select * from t2; drop table t1,t2; +--error ER_WRONG_USAGE EXPLAIN SELECT 1 FROM (SELECT 1) a PROCEDURE ANALYSE(); # @@ -102,4 +103,13 @@ select f2 from t1 procedure analyse(1, 1); select f3 from t1 procedure analyse(1, 1); drop table t1; +# +# Bug#46184 Crash, SELECT ... FROM derived table procedure analyze +# +CREATE TABLE t1(a INT,b INT,c INT,d INT,e INT,f INT,g INT,h INT,i INT,j INT,k INT); +INSERT INTO t1 VALUES (); +--error ER_WRONG_USAGE +SELECT * FROM (SELECT * FROM t1) d PROCEDURE ANALYSE(); +DROP TABLE t1; + --echo End of 4.1 tests diff --git a/mysql-test/t/archive.test b/mysql-test/t/archive.test index 7139d95ab49..aad3d19455d 100644 --- a/mysql-test/t/archive.test +++ b/mysql-test/t/archive.test @@ -1599,3 +1599,27 @@ INSERT INTO t1 VALUES (NULL, NULL),(NULL, NULL); FLUSH TABLE t1; SELECT * FROM t1 ORDER BY a; DROP TABLE t1; + +# +# BUG#29203 - archive tables have weird values in show table status +# +CREATE TABLE t1(a INT, b BLOB) ENGINE=archive; +SELECT DATA_LENGTH, AVG_ROW_LENGTH FROM + INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='t1' AND TABLE_SCHEMA='test'; +INSERT INTO t1 VALUES(1, 'sampleblob1'),(2, 'sampleblob2'); +SELECT DATA_LENGTH, AVG_ROW_LENGTH FROM + INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='t1' AND TABLE_SCHEMA='test'; +DROP TABLE t1; + +# +# BUG#46961 - archive engine loses rows during self joining select! +# +SET @save_join_buffer_size= @@join_buffer_size; +SET @@join_buffer_size= 8228; +CREATE TABLE t1(a CHAR(255)) ENGINE=archive; +INSERT INTO t1 VALUES('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'), + ('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'), + ('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'); +SELECT COUNT(t1.a) FROM t1, t1 a, t1 b, t1 c, t1 d, t1 e; +DROP TABLE t1; +SET @@join_buffer_size= @save_join_buffer_size; diff --git a/mysql-test/t/bug46080.test b/mysql-test/t/bug46080.test index 7e56e3ce421..8b4cee4d8b0 100644 --- a/mysql-test/t/bug46080.test +++ b/mysql-test/t/bug46080.test @@ -3,6 +3,9 @@ --echo # sort_buffer_size cannot allocate --echo # +call mtr.add_suppression("Out of memory at line .*, 'my_alloc.c'"); +call mtr.add_suppression("needed .* byte .*k., memory in use: .* bytes .*k"); + CREATE TABLE t1(a CHAR(255)); INSERT INTO t1 VALUES ('a'); diff --git a/mysql-test/t/create.test b/mysql-test/t/create.test index 75ad6c056c1..5ffa1b93929 100644 --- a/mysql-test/t/create.test +++ b/mysql-test/t/create.test @@ -1198,6 +1198,23 @@ CREATE TABLE IF NOT EXISTS t2 (a INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY) DROP TABLE t1, t2; +--echo # +--echo # BUG#46384 - mysqld segfault when trying to create table with same +--echo # name as existing view +--echo # + +CREATE TABLE t1 (a INT); +CREATE TABLE t2 (a INT); + +INSERT INTO t1 VALUES (1),(2),(3); +INSERT INTO t2 VALUES (1),(2),(3); + +CREATE VIEW v1 AS SELECT t1.a FROM t1, t2; +--error ER_TABLE_EXISTS_ERROR +CREATE TABLE v1 AS SELECT * FROM t1; + +DROP VIEW v1; +DROP TABLE t1,t2; --echo End of 5.0 tests diff --git a/mysql-test/t/ctype_gbk_binlog.test b/mysql-test/t/ctype_gbk_binlog.test index a8f653d1b1e..e4c1bee19af 100644 --- a/mysql-test/t/ctype_gbk_binlog.test +++ b/mysql-test/t/ctype_gbk_binlog.test @@ -1,6 +1,7 @@ -- source include/have_binlog_format_mixed_or_statement.inc -- source include/have_gbk.inc +RESET MASTER; SET NAMES gbk; --character_set gbk diff --git a/mysql-test/t/disabled.def b/mysql-test/t/disabled.def index 5436b7166f4..6d8f0af0c28 100644 --- a/mysql-test/t/disabled.def +++ b/mysql-test/t/disabled.def @@ -12,5 +12,3 @@ kill : Bug#37780 2008-12-03 HHunger need some changes to be robust enough for pushbuild. innodb_bug39438 : Bug#42383 2009-01-28 lsoares "This fails in embedded and on windows. Note that this test is not run on windows and on embedded in PB for main trees currently" query_cache_28249 : Bug#43861 2009-03-25 main.query_cache_28249 fails sporadically -init_connect : Bug#44920 2009-07-06 pcrews MTR not processing master.opt input properly on Windows. *Must be done this way due to the nature of the bug* - diff --git a/mysql-test/t/distinct.test b/mysql-test/t/distinct.test index a77d1136840..bf4c23562cf 100644 --- a/mysql-test/t/distinct.test +++ b/mysql-test/t/distinct.test @@ -573,4 +573,44 @@ SELECT DISTINCT a, b, d, c FROM t1; DROP TABLE t1; +--echo # +--echo # Bug #46159: simple query that never returns +--echo # + +# Set max_heap_table_size to the minimum value so that GROUP BY table in the +# SELECT query below gets converted to MyISAM +SET @old_max_heap_table_size = @@max_heap_table_size; +SET @@max_heap_table_size = 16384; + +# Set sort_buffer_size to the mininum value so that remove_duplicates() calls +# remove_dup_with_compare() +SET @old_sort_buffer_size = @@sort_buffer_size; +SET @@sort_buffer_size = 32804; + +CREATE TABLE t1(c1 int, c2 VARCHAR(20)); +INSERT INTO t1 VALUES (1, '1'), (1, '1'), (2, '2'), (3, '1'), (3, '1'), (4, '4'); +# Now we just need to pad the table with random data so we have enough unique +# values to force conversion of the GROUP BY table to MyISAM +INSERT INTO t1 SELECT 5 + 10000 * RAND(), '5' FROM t1; +INSERT INTO t1 SELECT 5 + 10000 * RAND(), '5' FROM t1; +INSERT INTO t1 SELECT 5 + 10000 * RAND(), '5' FROM t1; +INSERT INTO t1 SELECT 5 + 10000 * RAND(), '5' FROM t1; +INSERT INTO t1 SELECT 5 + 10000 * RAND(), '5' FROM t1; +INSERT INTO t1 SELECT 5 + 10000 * RAND(), '5' FROM t1; +INSERT INTO t1 SELECT 5 + 10000 * RAND(), '5' FROM t1; +INSERT INTO t1 SELECT 5 + 10000 * RAND(), '5' FROM t1; + +# First rows of the GROUP BY table that will be processed by +# remove_dup_with_compare() +SELECT c1, c2, COUNT(*) FROM t1 GROUP BY c1 LIMIT 4; + +# The actual test case +SELECT DISTINCT c2 FROM t1 GROUP BY c1 HAVING COUNT(*) > 1; + +# Cleanup + +DROP TABLE t1; +SET @@sort_buffer_size = @old_sort_buffer_size; +SET @@max_heap_table_size = @old_max_heap_table_size; + --echo End of 5.1 tests diff --git a/mysql-test/t/events_logs_tests-master.opt b/mysql-test/t/events_logs_tests-master.opt index 35ff7911705..3ece9710a45 100644 --- a/mysql-test/t/events_logs_tests-master.opt +++ b/mysql-test/t/events_logs_tests-master.opt @@ -1 +1 @@ ---log-slow-queries +--log-output=table,file --log-slow-queries diff --git a/mysql-test/t/explain.test b/mysql-test/t/explain.test index 755e126baf2..18f1145a25d 100644 --- a/mysql-test/t/explain.test +++ b/mysql-test/t/explain.test @@ -135,6 +135,17 @@ EXPLAIN EXTENDED SELECT COUNT(a) FROM t1 USE KEY(a); DROP TABLE t1; +# +# Bug#45989 memory leak after explain encounters an error in the query +# +CREATE TABLE t1(a LONGTEXT); +INSERT INTO t1 VALUES (repeat('a',@@global.max_allowed_packet)); +INSERT INTO t1 VALUES (repeat('b',@@global.max_allowed_packet)); +--error ER_BAD_FIELD_ERROR +EXPLAIN SELECT DISTINCT 1 FROM t1, + (SELECT DISTINCTROW a AS away FROM t1 GROUP BY a WITH ROLLUP) as d1 + WHERE t1.a = d1.a; +DROP TABLE t1; # End of 5.0 tests. 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 7cb7f7f72d2..66b9eabd385 100644 --- a/mysql-test/t/func_str.test +++ b/mysql-test/t/func_str.test @@ -1291,6 +1291,19 @@ INSERT INTO t1 VALUES ('aaaaaaaa'); SELECT LOAD_FILE(a) FROM t1; DROP TABLE t1; +# +# Bug#46815 CONCAT_WS returning wrong data +# +CREATE TABLE t1 (f2 VARCHAR(20)); +CREATE TABLE t2 (f2 VARCHAR(20)); + +INSERT INTO t1 VALUES ('MIN'),('MAX'); +INSERT INTO t2 VALUES ('LOAD'); + +SELECT CONCAT_WS('_', (SELECT t2.f2 FROM t2), t1.f2) AS concat_name FROM t1; + +DROP TABLE t1, t2; + --echo End of 5.0 tests diff --git a/mysql-test/t/group_min_max.test b/mysql-test/t/group_min_max.test index 981be3efece..c09a4fbf490 100644 --- a/mysql-test/t/group_min_max.test +++ b/mysql-test/t/group_min_max.test @@ -1018,3 +1018,18 @@ DROP TABLE t; --echo End of 5.0 tests + +--echo # +--echo # Bug #46607: Assertion failed: (cond_type == Item::FUNC_ITEM) results in +--echo # server crash +--echo # + +CREATE TABLE t (a INT, b INT, INDEX (a,b)); +INSERT INTO t VALUES (2,0), (2,0), (2,1), (2,1); +INSERT INTO t SELECT * FROM t; + +SELECT a, MAX(b) FROM t WHERE b GROUP BY a; + +DROP TABLE t; + +--echo End of 5.1 tests diff --git a/mysql-test/t/insert_select.test b/mysql-test/t/insert_select.test index 9889f48237b..7318e45889a 100644 --- a/mysql-test/t/insert_select.test +++ b/mysql-test/t/insert_select.test @@ -407,6 +407,7 @@ SET GLOBAL myisam_data_pointer_size = 2; INSERT INTO t1 VALUES (1), (2), (3), (4), (5); +call mtr.add_suppression("mysqld: The table '.*#sql.*' is full"); --error ER_RECORD_FILE_FULL,ER_RECORD_FILE_FULL INSERT IGNORE INTO t1 SELECT t1.a FROM t1,t1 t2,t1 t3,t1 t4,t1 t5,t1 t6,t1 t7; diff --git a/mysql-test/t/lock_multi_bug38499.test b/mysql-test/t/lock_multi_bug38499.test index 8178987e802..3d3f084ba5f 100644 --- a/mysql-test/t/lock_multi_bug38499.test +++ b/mysql-test/t/lock_multi_bug38499.test @@ -5,6 +5,9 @@ # Save the initial number of concurrent sessions --source include/count_sessions.inc +SET @odl_sync_frm = @@global.sync_frm; +SET @@global.sync_frm = OFF; + connect (locker,localhost,root,,); connect (writer,localhost,root,,); @@ -214,6 +217,8 @@ DROP TABLE t1; --disconnect locker --disconnect writer +SET @@global.sync_frm = @odl_sync_frm; + # End of 5.0 tests # Wait till all disconnects are completed diff --git a/mysql-test/t/lock_multi_bug38691.test b/mysql-test/t/lock_multi_bug38691.test index 0458f31579e..881a0d8e502 100644 --- a/mysql-test/t/lock_multi_bug38691.test +++ b/mysql-test/t/lock_multi_bug38691.test @@ -8,6 +8,9 @@ # Save the initial number of concurrent sessions --source include/count_sessions.inc +SET @odl_sync_frm = @@global.sync_frm; +SET @@global.sync_frm = OFF; + # Test to see if select will get the lock ahead of low priority update connect (locker,localhost,root,,); @@ -136,6 +139,8 @@ DROP TABLE t1, t2, t3; --disconnect locker --disconnect writer +SET @@global.sync_frm = @odl_sync_frm; + # Wait till all disconnects are completed --source include/wait_until_count_sessions.inc diff --git a/mysql-test/t/log_tables-master.opt b/mysql-test/t/log_tables-master.opt index 35ff7911705..3ece9710a45 100644 --- a/mysql-test/t/log_tables-master.opt +++ b/mysql-test/t/log_tables-master.opt @@ -1 +1 @@ ---log-slow-queries +--log-output=table,file --log-slow-queries diff --git a/mysql-test/t/lowercase_mixed_tmpdir_innodb-master.opt b/mysql-test/t/lowercase_mixed_tmpdir_innodb-master.opt new file mode 100644 index 00000000000..272f91d629c --- /dev/null +++ b/mysql-test/t/lowercase_mixed_tmpdir_innodb-master.opt @@ -0,0 +1,2 @@ +--lower-case-table-names=2 +--tmpdir=$MYSQLTEST_VARDIR/tmp/MixedCase diff --git a/mysql-test/t/lowercase_mixed_tmpdir_innodb-master.sh b/mysql-test/t/lowercase_mixed_tmpdir_innodb-master.sh new file mode 100644 index 00000000000..95c26e3aa02 --- /dev/null +++ b/mysql-test/t/lowercase_mixed_tmpdir_innodb-master.sh @@ -0,0 +1,6 @@ +# This test requires a non-lowercase tmpdir directory on a case-sensitive +# filesystem. + +d="$MYSQLTEST_VARDIR/tmp/MixedCase" +test -d "$d" || mkdir "$d" +rm -f "$d"/* diff --git a/mysql-test/t/lowercase_mixed_tmpdir_innodb.test b/mysql-test/t/lowercase_mixed_tmpdir_innodb.test new file mode 100644 index 00000000000..e3b9b7b2a32 --- /dev/null +++ b/mysql-test/t/lowercase_mixed_tmpdir_innodb.test @@ -0,0 +1,12 @@ +--source include/have_lowercase2.inc +--source include/have_innodb.inc + +--disable_warnings +drop table if exists t1; +--enable_warnings + +create table t1 (id int) engine=InnoDB; +insert into t1 values (1); +create temporary table t2 engine=InnoDB select * from t1; +drop temporary table t2; +drop table t1; diff --git a/mysql-test/t/lowercase_table3.test b/mysql-test/t/lowercase_table3.test index 4748953fe95..f7ca8211288 100644 --- a/mysql-test/t/lowercase_table3.test +++ b/mysql-test/t/lowercase_table3.test @@ -9,7 +9,7 @@ --source include/have_case_insensitive_file_system.inc --source include/not_windows.inc -call mtr.add_suppression("Cannot find or open table test/BUG29839 from .*"); +call mtr.add_suppression("Cannot find or open table test/BUG29839 from"); --disable_warnings DROP TABLE IF EXISTS t1,T1; diff --git a/mysql-test/t/myisam-system.test b/mysql-test/t/myisam-system.test index dc5bb58b6a2..d908e639a4e 100644 --- a/mysql-test/t/myisam-system.test +++ b/mysql-test/t/myisam-system.test @@ -12,11 +12,11 @@ let $MYSQLD_DATADIR= `select @@datadir`; drop table if exists t1; create table t1 (a int) engine=myisam; --remove_file $MYSQLD_DATADIR/test/t1.MYI ---error 1051,6 +--error ER_BAD_TABLE_ERROR,6 drop table t1; create table t1 (a int) engine=myisam; --remove_file $MYSQLD_DATADIR/test/t1.MYD ---error 1105,6,29 +--error ER_BAD_TABLE_ERROR,6,29 drop table t1; ---error 1051 +--error ER_BAD_TABLE_ERROR drop table t1; diff --git a/mysql-test/t/mysqlbinlog-cp932.test b/mysql-test/t/mysqlbinlog-cp932.test index a7055bfc8ca..2a210bea0e0 100644 --- a/mysql-test/t/mysqlbinlog-cp932.test +++ b/mysql-test/t/mysqlbinlog-cp932.test @@ -5,8 +5,9 @@ -- source include/have_cp932.inc -- source include/have_log_bin.inc +RESET MASTER; + # Bug#16217 (mysql client did not know how not switch its internal charset) -flush logs; create table t3 (f text character set utf8); create table t4 (f text character set cp932); --exec $MYSQL --default-character-set=utf8 test -e "insert into t3 values(_utf8'ソ')" @@ -14,7 +15,7 @@ create table t4 (f text character set cp932); flush logs; rename table t3 to t03, t4 to t04; let $MYSQLD_DATADIR= `select @@datadir`; ---exec $MYSQL_BINLOG --short-form $MYSQLD_DATADIR/master-bin.000002 | $MYSQL --default-character-set=utf8 +--exec $MYSQL_BINLOG --short-form $MYSQLD_DATADIR/master-bin.000001 | $MYSQL --default-character-set=utf8 # original and recovered data must be equal select HEX(f) from t03; select HEX(f) from t3; diff --git a/mysql-test/t/mysqltest.test b/mysql-test/t/mysqltest.test index 578b2bf5c6c..9859e73cfae 100644 --- a/mysql-test/t/mysqltest.test +++ b/mysql-test/t/mysqltest.test @@ -853,16 +853,18 @@ while ($outer) eval SELECT '$outer = outer loop variable after dec' AS ""; } +# Test source in an if in a while which is false on 1st iteration let $outer= 2; # Number of outer loops +let $ifval= 0; # false 1st time while ($outer) { - eval SELECT '$outer = outer loop variable after while' AS ""; - - echo here is the sourced script; + echo outer=$outer ifval=$ifval; - eval SELECT '$outer = outer loop variable before dec' AS ""; + if ($ifval) { + --source $MYSQLTEST_VARDIR/tmp/sourced.inc + } dec $outer; - eval SELECT '$outer = outer loop variable after dec' AS ""; + inc $ifval; } @@ -1663,6 +1665,20 @@ EOF remove_file $MYSQLTEST_VARDIR/tmp/test_file1.tmp; +# Test append_file within while +let $outer= 2; # Number of outer loops +while ($outer) +{ + append_file $MYSQLTEST_VARDIR/tmp/app_while.tmp; +These lines should be repeated, +if things work as expected +EOF + dec $outer; +} + +cat_file $MYSQLTEST_VARDIR/tmp/app_while.tmp; +remove_file $MYSQLTEST_VARDIR/tmp/app_while.tmp; + # ---------------------------------------------------------------------------- # test for cat_file # ---------------------------------------------------------------------------- @@ -1710,10 +1726,6 @@ EOF --diff_files $MYSQLTEST_VARDIR/tmp/diff1.tmp $MYSQLTEST_VARDIR/tmp/diff2.tmp --diff_files $MYSQLTEST_VARDIR/tmp/diff2.tmp $MYSQLTEST_VARDIR/tmp/diff1.tmp -# Write the below commands to a intermediary file and execute them with -# mysqltest in --exec, since the output will vary depending on what "diff" -# is available it is sent to /dev/null ---write_file $MYSQLTEST_VARDIR/tmp/diff.test # Compare files that differ in size --error 2 --diff_files $MYSQLTEST_VARDIR/tmp/diff1.tmp $MYSQLTEST_VARDIR/tmp/diff3.tmp @@ -1725,13 +1737,6 @@ EOF --diff_files $MYSQLTEST_VARDIR/tmp/diff1.tmp $MYSQLTEST_VARDIR/tmp/diff4.tmp --error 1 --diff_files $MYSQLTEST_VARDIR/tmp/diff4.tmp $MYSQLTEST_VARDIR/tmp/diff1.tmp -exit; -EOF - -# Execute the above diffs, and send their output to /dev/null - only -# interesting to see that it returns correct error codes ---exec $MYSQL_TEST < $MYSQLTEST_VARDIR/tmp/diff.test > /dev/null 2>&1 - # Compare equal files, again... --diff_files $MYSQLTEST_VARDIR/tmp/diff1.tmp $MYSQLTEST_VARDIR/tmp/diff2.tmp @@ -1740,7 +1745,6 @@ EOF --remove_file $MYSQLTEST_VARDIR/tmp/diff2.tmp --remove_file $MYSQLTEST_VARDIR/tmp/diff3.tmp --remove_file $MYSQLTEST_VARDIR/tmp/diff4.tmp ---remove_file $MYSQLTEST_VARDIR/tmp/diff.test # ---------------------------------------------------------------------------- diff --git a/mysql-test/t/not_partition.test b/mysql-test/t/not_partition.test index 78e6c7495cf..78ca44acf18 100644 --- a/mysql-test/t/not_partition.test +++ b/mysql-test/t/not_partition.test @@ -15,7 +15,7 @@ let $MYSQLD_DATADIR= `SELECT @@datadir`; # Bug#39893: Crash if select on a partitioned table, # when partitioning is disabled FLUSH TABLES; ---copy_file $MYSQLTEST_VARDIR/std_data_ln/parts/t1.frm $MYSQLD_DATADIR/test/t1.frm +--copy_file $MYSQLTEST_VARDIR/std_data/parts/t1.frm $MYSQLD_DATADIR/test/t1.frm SELECT * FROM t1; TRUNCATE TABLE t1; ANALYZE TABLE t1; diff --git a/mysql-test/t/partition_csv.test b/mysql-test/t/partition_csv.test index dd2ef7c1d1f..44013dd4b0a 100644 --- a/mysql-test/t/partition_csv.test +++ b/mysql-test/t/partition_csv.test @@ -10,6 +10,8 @@ --source include/have_partition.inc --source include/have_csv.inc +call mtr.add_suppression("Failed to write to mysql.general_log"); + # # Bug#19307: Partitions: csv delete failure # = CSV engine crashes diff --git a/mysql-test/t/partition_innodb.test b/mysql-test/t/partition_innodb.test index c6bf0af0b6f..36e3edf05aa 100644 --- a/mysql-test/t/partition_innodb.test +++ b/mysql-test/t/partition_innodb.test @@ -6,6 +6,23 @@ drop table if exists t1; --enable_warnings # +# Bug#47029: Crash when reorganize partition with subpartition +# +create table t1 (a int not null, + b datetime not null, + primary key (a,b)) +engine=innodb +partition by range (to_days(b)) +subpartition by hash (a) +subpartitions 2 +( partition p0 values less than (to_days('2009-01-01')), + partition p1 values less than (to_days('2009-02-01')), + partition p2 values less than (to_days('2009-03-01')), + partition p3 values less than maxvalue); +alter table t1 reorganize partition p1,p2 into +( partition p2 values less than (to_days('2009-03-01'))); +drop table t1; +# # Bug#40595: Non-matching rows not released with READ-COMMITTED on tables # with partitions CREATE TABLE t1 (id INT PRIMARY KEY, data INT) ENGINE = InnoDB @@ -274,3 +291,15 @@ PARTITION BY RANGE (int_column) (PARTITION p1 VALUES LESS THAN (5)); show create table t1; drop table t1; + +# +# BUG#46483 - drop table of partitioned table may leave extraneous file +# Note: was only repeatable with InnoDB plugin +# +CREATE TABLE t1 (a INT) ENGINE=InnoDB + PARTITION BY list(a) (PARTITION p1 VALUES IN (1)); +CREATE INDEX i1 ON t1 (a); +DROP TABLE t1; +let $MYSQLD_DATADIR= `SELECT @@datadir`; +# Before the fix it should show extra file like #sql-2405_2.par +--list_files $MYSQLD_DATADIR/test/ * diff --git a/mysql-test/t/partition_pruning.test b/mysql-test/t/partition_pruning.test index ad102062ef8..11e65be45fc 100644 --- a/mysql-test/t/partition_pruning.test +++ b/mysql-test/t/partition_pruning.test @@ -9,6 +9,357 @@ drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; --enable_warnings # +# Bug#20577: Partitions: use of to_days() function leads to selection failures +# +--let $explain_partitions= 1; +--let $verify_without_partitions= 0; +--echo # test of RANGE and index +CREATE TABLE t1 (a DATE, KEY(a)) +PARTITION BY RANGE (TO_DAYS(a)) +(PARTITION `pNULL` VALUES LESS THAN (0), + PARTITION `p0001-01-01` VALUES LESS THAN (366 + 1), + PARTITION `p1001-01-01` VALUES LESS THAN (TO_DAYS('1001-01-01') + 1), + PARTITION `p2001-01-01` VALUES LESS THAN (TO_DAYS('2001-01-01') + 1)); +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; + +--echo # test of LIST and index +CREATE TABLE t1 (a DATE, KEY(a)) +PARTITION BY LIST (TO_DAYS(a)) +(PARTITION `p0001-01-01` VALUES IN (TO_DAYS('0001-01-01')), + PARTITION `p2001-01-01` VALUES IN (TO_DAYS('2001-01-01')), + PARTITION `pNULL` VALUES IN (NULL), + PARTITION `p0000-01-02` VALUES IN (TO_DAYS('0000-01-02')), + PARTITION `p1001-01-01` VALUES IN (TO_DAYS('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) +# There is a problem when comparing DATE with DATETIME. +# In pruning it is converted into the field type +# and in row evaluation it is converted to longlong +# (like a DATETIME). +--echo # Test with DATETIME column NOT NULL +CREATE TABLE t1 ( + a int(10) unsigned NOT NULL, + b DATETIME NOT NULL, + PRIMARY KEY (a, b) +) PARTITION BY RANGE (TO_DAYS(b)) +(PARTITION p20090401 VALUES LESS THAN (TO_DAYS('2009-04-02')), + PARTITION p20090402 VALUES LESS THAN (TO_DAYS('2009-04-03')), + PARTITION p20090403 VALUES LESS THAN (TO_DAYS('2009-04-04')), + PARTITION p20090404 VALUES LESS THAN (TO_DAYS('2009-04-05')), + PARTITION p20090405 VALUES LESS THAN MAXVALUE); +INSERT INTO t1 VALUES (1, '2009-01-01'), (1, '2009-04-01'), (2, '2009-04-01'), + (1, '2009-04-02'), (2, '2009-04-02'), (1, '2009-04-02 23:59:59'), + (1, '2009-04-03'), (2, '2009-04-03'), (1, '2009-04-04'), (2, '2009-04-04'), + (1, '2009-04-05'), (1, '2009-04-06'), (1, '2009-04-07'); +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < CAST('2009-04-03' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= CAST('2009-04-03' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = CAST('2009-04-03' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= CAST('2009-04-03' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > CAST('2009-04-03' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 + WHERE b < CAST('2009-04-02 23:59:59' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 + WHERE b <= CAST('2009-04-02 23:59:59' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 + WHERE b = CAST('2009-04-02 23:59:59' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 + WHERE b >= CAST('2009-04-02 23:59:59' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 + WHERE b > CAST('2009-04-02 23:59:59' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < CAST('2009-04-03' AS DATE); +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= CAST('2009-04-03' AS DATE); +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = CAST('2009-04-03' AS DATE); +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= CAST('2009-04-03' AS DATE); +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > CAST('2009-04-03' AS DATE); +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < '2009-04-03 00:00:00'; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= '2009-04-03 00:00:00'; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = '2009-04-03 00:00:00'; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= '2009-04-03 00:00:00'; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > '2009-04-03 00:00:00'; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < '2009-04-02 23:59:59'; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= '2009-04-02 23:59:59'; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = '2009-04-02 23:59:59'; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= '2009-04-02 23:59:59'; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > '2009-04-02 23:59:59'; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < '2009-04-03'; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= '2009-04-03'; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = '2009-04-03'; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= '2009-04-03'; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > '2009-04-03'; +EXPLAIN PARTITIONS SELECT * FROM t1 + WHERE b < CAST('2009-04-03 00:00:01' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 + WHERE b <= CAST('2009-04-03 00:00:01' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 + WHERE b = CAST('2009-04-03 00:00:01' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 + WHERE b >= CAST('2009-04-03 00:00:01' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 + WHERE b > CAST('2009-04-03 00:00:01' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 + WHERE b < CAST('2009-04-02 23:59:58' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 + WHERE b <= CAST('2009-04-02 23:59:58' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 + WHERE b = CAST('2009-04-02 23:59:58' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 + WHERE b >= CAST('2009-04-02 23:59:58' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 + WHERE b > CAST('2009-04-02 23:59:58' AS DATETIME); +DROP TABLE t1; + +--echo # Test with DATE column NOT NULL +CREATE TABLE t1 ( + a int(10) unsigned NOT NULL, + b DATE NOT NULL, + PRIMARY KEY (a, b) +) PARTITION BY RANGE (TO_DAYS(b)) +(PARTITION p20090401 VALUES LESS THAN (TO_DAYS('2009-04-02')), + PARTITION p20090402 VALUES LESS THAN (TO_DAYS('2009-04-03')), + PARTITION p20090403 VALUES LESS THAN (TO_DAYS('2009-04-04')), + PARTITION p20090404 VALUES LESS THAN (TO_DAYS('2009-04-05')), + PARTITION p20090405 VALUES LESS THAN MAXVALUE); +INSERT INTO t1 VALUES (1, '2009-01-01'), (1, '2009-04-01'), (2, '2009-04-01'), + (1, '2009-04-02'), (2, '2009-04-02'), (1, '2009-04-03'), (2, '2009-04-03'), + (1, '2009-04-04'), (2, '2009-04-04'), (1, '2009-04-05'), (1, '2009-04-06'), + (1, '2009-04-07'); +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < CAST('2009-04-03' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= CAST('2009-04-03' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = CAST('2009-04-03' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= CAST('2009-04-03' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > CAST('2009-04-03' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 + WHERE b < CAST('2009-04-02 23:59:59' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 + WHERE b <= CAST('2009-04-02 23:59:59' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 + WHERE b = CAST('2009-04-02 23:59:59' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 + WHERE b >= CAST('2009-04-02 23:59:59' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 + WHERE b > CAST('2009-04-02 23:59:59' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < CAST('2009-04-03' AS DATE); +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= CAST('2009-04-03' AS DATE); +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = CAST('2009-04-03' AS DATE); +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= CAST('2009-04-03' AS DATE); +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > CAST('2009-04-03' AS DATE); +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < '2009-04-03 00:00:00'; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= '2009-04-03 00:00:00'; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = '2009-04-03 00:00:00'; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= '2009-04-03 00:00:00'; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > '2009-04-03 00:00:00'; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < '2009-04-02 23:59:59'; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= '2009-04-02 23:59:59'; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = '2009-04-02 23:59:59'; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= '2009-04-02 23:59:59'; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > '2009-04-02 23:59:59'; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < '2009-04-03'; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= '2009-04-03'; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = '2009-04-03'; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= '2009-04-03'; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > '2009-04-03'; +EXPLAIN PARTITIONS SELECT * FROM t1 + WHERE b < CAST('2009-04-03 00:00:01' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 + WHERE b <= CAST('2009-04-03 00:00:01' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 + WHERE b = CAST('2009-04-03 00:00:01' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 + WHERE b >= CAST('2009-04-03 00:00:01' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 + WHERE b > CAST('2009-04-03 00:00:01' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 + WHERE b < CAST('2009-04-02 23:59:58' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 + WHERE b <= CAST('2009-04-02 23:59:58' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 + WHERE b = CAST('2009-04-02 23:59:58' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 + WHERE b >= CAST('2009-04-02 23:59:58' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 + WHERE b > CAST('2009-04-02 23:59:58' AS DATETIME); +DROP TABLE t1; + +--echo # Test with DATETIME column NULL +CREATE TABLE t1 ( + a int(10) unsigned NOT NULL, + b DATETIME NULL +) PARTITION BY RANGE (TO_DAYS(b)) +(PARTITION p20090401 VALUES LESS THAN (TO_DAYS('2009-04-02')), + PARTITION p20090402 VALUES LESS THAN (TO_DAYS('2009-04-03')), + PARTITION p20090403 VALUES LESS THAN (TO_DAYS('2009-04-04')), + PARTITION p20090404 VALUES LESS THAN (TO_DAYS('2009-04-05')), + PARTITION p20090405 VALUES LESS THAN MAXVALUE); +INSERT INTO t1 VALUES (1, '2009-01-01'), (1, '2009-04-01'), (2, '2009-04-01'), + (1, '2009-04-02'), (2, '2009-04-02'), (1, '2009-04-02 23:59:59'), + (1, '2009-04-03'), (2, '2009-04-03'), (1, '2009-04-04'), (2, '2009-04-04'), + (1, '2009-04-05'), (1, '2009-04-06'), (1, '2009-04-07'); +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < CAST('2009-04-03' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= CAST('2009-04-03' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = CAST('2009-04-03' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= CAST('2009-04-03' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > CAST('2009-04-03' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 + WHERE b < CAST('2009-04-02 23:59:59' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 + WHERE b <= CAST('2009-04-02 23:59:59' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 + WHERE b = CAST('2009-04-02 23:59:59' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 + WHERE b >= CAST('2009-04-02 23:59:59' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 + WHERE b > CAST('2009-04-02 23:59:59' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < CAST('2009-04-03' AS DATE); +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= CAST('2009-04-03' AS DATE); +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = CAST('2009-04-03' AS DATE); +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= CAST('2009-04-03' AS DATE); +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > CAST('2009-04-03' AS DATE); +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < '2009-04-03 00:00:00'; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= '2009-04-03 00:00:00'; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = '2009-04-03 00:00:00'; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= '2009-04-03 00:00:00'; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > '2009-04-03 00:00:00'; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < '2009-04-02 23:59:59'; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= '2009-04-02 23:59:59'; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = '2009-04-02 23:59:59'; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= '2009-04-02 23:59:59'; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > '2009-04-02 23:59:59'; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < '2009-04-03'; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= '2009-04-03'; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = '2009-04-03'; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= '2009-04-03'; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > '2009-04-03'; +EXPLAIN PARTITIONS SELECT * FROM t1 + WHERE b < CAST('2009-04-03 00:00:01' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 + WHERE b <= CAST('2009-04-03 00:00:01' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 + WHERE b = CAST('2009-04-03 00:00:01' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 + WHERE b >= CAST('2009-04-03 00:00:01' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 + WHERE b > CAST('2009-04-03 00:00:01' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 + WHERE b < CAST('2009-04-02 23:59:58' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 + WHERE b <= CAST('2009-04-02 23:59:58' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 + WHERE b = CAST('2009-04-02 23:59:58' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 + WHERE b >= CAST('2009-04-02 23:59:58' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 + WHERE b > CAST('2009-04-02 23:59:58' AS DATETIME); +DROP TABLE t1; + +--echo # Test with DATE column NULL +CREATE TABLE t1 ( + a int(10) unsigned NOT NULL, + b DATE NULL +) PARTITION BY RANGE (TO_DAYS(b)) +(PARTITION p20090401 VALUES LESS THAN (TO_DAYS('2009-04-02')), + PARTITION p20090402 VALUES LESS THAN (TO_DAYS('2009-04-03')), + PARTITION p20090403 VALUES LESS THAN (TO_DAYS('2009-04-04')), + PARTITION p20090404 VALUES LESS THAN (TO_DAYS('2009-04-05')), + PARTITION p20090405 VALUES LESS THAN MAXVALUE); +INSERT INTO t1 VALUES (1, '2009-01-01'), (1, '2009-04-01'), (2, '2009-04-01'), + (1, '2009-04-02'), (2, '2009-04-02'), (1, '2009-04-03'), (2, '2009-04-03'), + (1, '2009-04-04'), (2, '2009-04-04'), (1, '2009-04-05'), (1, '2009-04-06'), + (1, '2009-04-07'); +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < CAST('2009-04-03' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= CAST('2009-04-03' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = CAST('2009-04-03' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= CAST('2009-04-03' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > CAST('2009-04-03' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 + WHERE b < CAST('2009-04-02 23:59:59' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 + WHERE b <= CAST('2009-04-02 23:59:59' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 + WHERE b = CAST('2009-04-02 23:59:59' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 + WHERE b >= CAST('2009-04-02 23:59:59' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 + WHERE b > CAST('2009-04-02 23:59:59' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < CAST('2009-04-03' AS DATE); +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= CAST('2009-04-03' AS DATE); +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = CAST('2009-04-03' AS DATE); +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= CAST('2009-04-03' AS DATE); +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > CAST('2009-04-03' AS DATE); +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < '2009-04-03 00:00:00'; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= '2009-04-03 00:00:00'; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = '2009-04-03 00:00:00'; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= '2009-04-03 00:00:00'; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > '2009-04-03 00:00:00'; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < '2009-04-02 23:59:59'; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= '2009-04-02 23:59:59'; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = '2009-04-02 23:59:59'; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= '2009-04-02 23:59:59'; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > '2009-04-02 23:59:59'; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < '2009-04-03'; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= '2009-04-03'; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = '2009-04-03'; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= '2009-04-03'; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > '2009-04-03'; +EXPLAIN PARTITIONS SELECT * FROM t1 + WHERE b < CAST('2009-04-03 00:00:01' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 + WHERE b <= CAST('2009-04-03 00:00:01' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 + WHERE b = CAST('2009-04-03 00:00:01' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 + WHERE b >= CAST('2009-04-03 00:00:01' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 + WHERE b > CAST('2009-04-03 00:00:01' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 + WHERE b < CAST('2009-04-02 23:59:58' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 + WHERE b <= CAST('2009-04-02 23:59:58' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 + WHERE b = CAST('2009-04-02 23:59:58' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 + WHERE b >= CAST('2009-04-02 23:59:58' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 + WHERE b > CAST('2009-04-02 23:59:58' AS DATETIME); +DROP TABLE t1; + +--echo # For better code coverage of the patch +CREATE TABLE t1 ( + a int(10) unsigned NOT NULL, + b DATE +) PARTITION BY RANGE ( TO_DAYS(b) ) +(PARTITION p20090401 VALUES LESS THAN (TO_DAYS('2009-04-02')), + PARTITION p20090402 VALUES LESS THAN (TO_DAYS('2009-04-03')), + PARTITION p20090403 VALUES LESS THAN (TO_DAYS('2009-04-04')), + PARTITION p20090404 VALUES LESS THAN (TO_DAYS('2009-04-05')), + PARTITION p20090405 VALUES LESS THAN MAXVALUE); +INSERT INTO t1 VALUES (1, '2009-01-01'), (2, NULL); +--echo # test with an invalid date, which lead to item->null_value is set. +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < CAST('2009-04-99' AS DATETIME); +DROP TABLE t1; + +# # Bug#40972: some sql execution lead the whole database crashing # # Setup so the start is at partition pX and end is at p1 diff --git a/mysql-test/t/ps-master.opt b/mysql-test/t/ps-master.opt index 3eb98fc3d6b..31c287d2bb5 100644 --- a/mysql-test/t/ps-master.opt +++ b/mysql-test/t/ps-master.opt @@ -1 +1 @@ ---log-slow-queries --log-long-format --log-queries-not-using-indexes +--log-output=table,file --log-slow-queries --log-long-format --log-queries-not-using-indexes diff --git a/mysql-test/t/ps_not_windows.test b/mysql-test/t/ps_not_windows.test index 6d85f737b32..0ab08b59f1e 100644 --- a/mysql-test/t/ps_not_windows.test +++ b/mysql-test/t/ps_not_windows.test @@ -2,6 +2,8 @@ --source include/not_embedded.inc # Non-windows specific ps tests. --source include/not_windows.inc +# requires dynamic loading +--source include/have_dynamic_loading.inc # # Bug #20665: All commands supported in Stored Procedures should work in diff --git a/mysql-test/t/show_check-master.opt b/mysql-test/t/show_check-master.opt index 7a438da06cc..aab832e2848 100644 --- a/mysql-test/t/show_check-master.opt +++ b/mysql-test/t/show_check-master.opt @@ -1 +1 @@ ---log-slow-queries --log-long-format --log-queries-not-using-indexes --myisam-recover="" +--log-output=table,file --log-slow-queries --log-long-format --log-queries-not-using-indexes --myisam-recover="" 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.test b/mysql-test/t/sp.test index 5eeac457958..44c4556340e 100644 --- a/mysql-test/t/sp.test +++ b/mysql-test/t/sp.test @@ -8242,6 +8242,28 @@ while ($tab_count) DROP PROCEDURE p1; DROP TABLE t1; + +--echo # +--echo # Bug #46629: Item_in_subselect::val_int(): Assertion `0' +--echo # on subquery inside a SP +--echo # +CREATE TABLE t1(a INT); +CREATE TABLE t2(a INT, b INT PRIMARY KEY); + +DELIMITER |; +CREATE PROCEDURE p1 () +BEGIN + SELECT a FROM t1 A WHERE A.b IN (SELECT b FROM t2 AS B); +END| +DELIMITER ;| +--error ER_BAD_FIELD_ERROR +CALL p1; +--error ER_BAD_FIELD_ERROR +CALL p1; +DROP PROCEDURE p1; +DROP TABLE t1, t2; + + --echo # ------------------------------------------------------------------ --echo # -- End of 5.1 tests --echo # ------------------------------------------------------------------ diff --git a/mysql-test/t/status-master.opt b/mysql-test/t/status-master.opt new file mode 100644 index 00000000000..eb3bb4fe50d --- /dev/null +++ b/mysql-test/t/status-master.opt @@ -0,0 +1 @@ +--log-output=table,file diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index 3504e9e3733..544017ebb97 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -30,7 +30,7 @@ SELECT 1 IN (SELECT 1); SELECT 1 FROM (SELECT 1 as a) b WHERE 1 IN (SELECT (SELECT a)); -- error ER_WRONG_USAGE select (SELECT 1 FROM (SELECT 1) a PROCEDURE ANALYSE(1)); --- error ER_WRONG_PARAMETERS_TO_PROCEDURE +-- error ER_WRONG_USAGE SELECT 1 FROM (SELECT 1) a PROCEDURE ANALYSE((SELECT 1)); -- error ER_BAD_FIELD_ERROR SELECT (SELECT 1) as a FROM (SELECT 1) b WHERE (SELECT a) IS NULL; @@ -3336,6 +3336,38 @@ EXPLAIN EXTENDED SELECT * FROM C WHERE `int_key` IN (SELECT `int_nokey`); DROP TABLE C; --echo # End of test for bug#45061. + +--echo # +--echo # Bug #46749: Segfault in add_key_fields() with outer subquery level +--echo # field references +--echo # + +CREATE TABLE t1 ( + a int, + b int, + UNIQUE (a), KEY (b) +); +INSERT INTO t1 VALUES (1,1), (2,1); + +CREATE TABLE st1 like t1; +INSERT INTO st1 VALUES (1,1), (2,1); + +CREATE TABLE st2 like t1; +INSERT INTO st2 VALUES (1,1), (2,1); + +# should have "impossible where" +EXPLAIN +SELECT MAX(b), (SELECT COUNT(*) FROM st1,st2 WHERE st2.b <= t1.b) +FROM t1 +WHERE a = 230; + +# should not crash +SELECT MAX(b), (SELECT COUNT(*) FROM st1,st2 WHERE st2.b <= t1.b) +FROM t1 +WHERE a = 230; + +DROP TABLE t1, st1, st2; + --echo End of 5.0 tests. # diff --git a/mysql-test/t/subselect4.test b/mysql-test/t/subselect4.test new file mode 100644 index 00000000000..ff4cdf3c439 --- /dev/null +++ b/mysql-test/t/subselect4.test @@ -0,0 +1,32 @@ +# General purpose bug fix tests go here : subselect.test too large + + +--echo # +--echo # Bug #46791: Assertion failed:(table->key_read==0),function unknown +--echo # function,file sql_base.cc +--echo # + +CREATE TABLE t1 (a INT, b INT, KEY(a)); +INSERT INTO t1 VALUES (1,1),(2,2); +CREATE TABLE t2 LIKE t1; +INSERT INTO t2 VALUES (1,1),(2,2); +CREATE TABLE t3 LIKE t1; + +--echo # should have 1 impossible where and 2 dependent subqueries +EXPLAIN +SELECT 1 FROM t1 +WHERE NOT EXISTS (SELECT 1 FROM t2 WHERE 1 = (SELECT MIN(t2.b) FROM t3)) +ORDER BY count(*); + +--echo # should not crash the next statement +SELECT 1 FROM t1 +WHERE NOT EXISTS (SELECT 1 FROM t2 WHERE 1 = (SELECT MIN(t2.b) FROM t3)) +ORDER BY count(*); + +--echo # should not crash: the crash is caused by the previous statement +SELECT 1; + +DROP TABLE t1,t2,t3; + + +--echo End of 5.0 tests. diff --git a/mysql-test/t/udf.test b/mysql-test/t/udf.test index e9ae1a31079..7bf252040e5 100644 --- a/mysql-test/t/udf.test +++ b/mysql-test/t/udf.test @@ -436,4 +436,16 @@ SELECT * FROM t2 WHERE a = sequence(); DROP FUNCTION sequence; DROP TABLE t1,t2; +--echo # +--echo # Bug#46259: 5.0.83 -> 5.1.36, query doesn't work +--echo # +CREATE TABLE t1 ( a INT ); + +INSERT INTO t1 VALUES (1), (2), (3); + +SELECT IF( a = 1, a, a ) AS `b` FROM t1 ORDER BY field( `b` + 1, 1 ); +SELECT IF( a = 1, a, a ) AS `b` FROM t1 ORDER BY field( `b`, 1 ); + +DROP TABLE t1; + --echo End of 5.0 tests. diff --git a/mysql-test/t/upgrade.test b/mysql-test/t/upgrade.test index d571a2efc7c..e390e8a1253 100644 --- a/mysql-test/t/upgrade.test +++ b/mysql-test/t/upgrade.test @@ -124,6 +124,8 @@ with_check_option=0 timestamp=2009-04-10 11:53:37 create-version=1 source=select f1 from `a-b-c`.t1 a, information_schema.tables b\nwhere a.f1 = b.table_name +client_cs_name=utf8 +connection_cl_name=utf8_general_ci EOF show databases like '%a-b-c%'; diff --git a/mysql-test/t/warnings.test b/mysql-test/t/warnings.test index 12421170eba..176f320e390 100644 --- a/mysql-test/t/warnings.test +++ b/mysql-test/t/warnings.test @@ -225,4 +225,11 @@ insert into t2 values(@q); drop table t1, t2; +# +# Bug#42364 SHOW ERRORS returns empty resultset after dropping non existent table +# +--error ER_BAD_TABLE_ERROR +DROP TABLE t1; +SHOW ERRORS; + --echo End of 5.0 tests |