diff options
Diffstat (limited to 'mysql-test')
69 files changed, 1935 insertions, 255 deletions
diff --git a/mysql-test/include/im_check_env.inc b/mysql-test/include/im_check_env.inc new file mode 100644 index 00000000000..169edbac6b3 --- /dev/null +++ b/mysql-test/include/im_check_env.inc @@ -0,0 +1,27 @@ +# This file is intended to be used in each IM-test. It contains stamements, +# that ensure that starting conditions (environment) for the IM-test are as +# expected. + +# Wait for mysqld1 (guarded instance) to start. + +--exec $MYSQL_TEST_DIR/t/wait_for_process.sh $IM_MYSQLD1_PATH_PID 30 started + +# Check the running instances. + +--connect (mysql1_con,localhost,root,,mysql,$IM_MYSQLD1_PORT,$IM_MYSQLD1_SOCK) + +--connection mysql1_con + +SHOW VARIABLES LIKE 'server_id'; + +--connection default + +# Let IM detect that mysqld1 is online. This delay should be longer than +# monitoring interval. + +--sleep 2 + +# Check that IM understands that mysqld1 is online, while mysqld2 is +# offline. + +SHOW INSTANCES; diff --git a/mysql-test/mysql-test-run.pl b/mysql-test/mysql-test-run.pl index 5d6f39bc882..45accee56fb 100755 --- a/mysql-test/mysql-test-run.pl +++ b/mysql-test/mysql-test-run.pl @@ -190,6 +190,7 @@ our $exe_ndb_mgmd; our $exe_slave_mysqld; our $exe_im; our $exe_my_print_defaults; +our $exe_perror; our $lib_udf_example; our $exe_libtool; @@ -856,6 +857,12 @@ sub command_line_setup () { } } + # Check IM arguments + if ( $glob_win32 ) + { + mtr_report("Disable Instance manager - not supported on Windows"); + $opt_skip_im= 1; + } # Check valgrind arguments if ( $opt_valgrind or $opt_valgrind_path or defined $opt_valgrind_options) { @@ -1164,19 +1171,22 @@ sub executable_setup () { "$path_client_bindir/mysqld-max", "$path_client_bindir/mysqld-nt", "$path_client_bindir/mysqld", - "$path_client_bindir/mysqld-debug", "$path_client_bindir/mysqld-max", "$glob_basedir/sql/release/mysqld", "$glob_basedir/sql/debug/mysqld"); + "$path_client_bindir/mysqld-debug", $path_language= mtr_path_exists("$glob_basedir/share/english/", "$glob_basedir/sql/share/english/"); $path_charsetsdir= mtr_path_exists("$glob_basedir/share/charsets", "$glob_basedir/sql/share/charsets"); - $exe_my_print_defaults= mtr_exe_exists("$path_client_bindir/my_print_defaults", "$glob_basedir/extra/release/my_print_defaults", "$glob_basedir/extra/debug/my_print_defaults"); + $exe_perror= + mtr_exe_exists("$path_client_bindir/perror", + "$glob_basedir/extra/release/perror", + "$glob_basedir/extra/debug/perror"); } else { @@ -1190,6 +1200,8 @@ sub executable_setup () { "$glob_basedir/server-tools/instance-manager/mysqlmanager"); $exe_my_print_defaults= mtr_exe_exists("$glob_basedir/extra/my_print_defaults"); + $exe_perror= + mtr_exe_exists("$glob_basedir/extra/perror"); } if ( $glob_use_embedded_server ) @@ -1244,6 +1256,8 @@ sub executable_setup () { "/usr/bin/false"); $exe_my_print_defaults= mtr_exe_exists("$path_client_bindir/my_print_defaults"); + $exe_perror= + mtr_exe_exists("$path_client_bindir/perror"); $path_language= mtr_path_exists("$glob_basedir/share/mysql/english/", "$glob_basedir/share/english/"); @@ -3508,31 +3522,60 @@ sub im_stop($$) { } # Check if all processes shutdown cleanly - my $clean_shutdown= 1; # Assum they did + my $clean_shutdown= 0; - if (kill (0, $instance_manager->{'pid'})) + while (1) { - mtr_warning("IM-main is still alive."); - $clean_shutdown= 0; - } + # Check that IM-main died. - if (defined $instance_manager->{'angel_pid'} && - kill (0, $instance_manager->{'angel_pid'})) - { - mtr_warning("IM-angel is still alive."); - $clean_shutdown= 0; - } + if (kill (0, $instance_manager->{'pid'})) + { + mtr_debug("IM-main is still alive."); + last; + } - foreach my $pid (@mysqld_pids) - { - if (kill (0, $pid)) + # Check that IM-angel died. + + if (defined $instance_manager->{'angel_pid'} && + kill (0, $instance_manager->{'angel_pid'})) { - mtr_warning("Guarded mysqld ($pid) is still alive."); - $clean_shutdown= 0; + mtr_debug("IM-angel is still alive."); + last; } + + # Check that all guarded mysqld-instances died. + + my $guarded_mysqlds_dead= 1; + + foreach my $pid (@mysqld_pids) + { + if (kill (0, $pid)) + { + mtr_debug("Guarded mysqld ($pid) is still alive."); + $guarded_mysqlds_dead= 0; + last; + } + } + + last unless $guarded_mysqlds_dead; + + # Ok, all necessary processes are dead. + + $clean_shutdown= 1; + last; } # Kill leftovers (the order is important). + + if ($clean_shutdown) + { + mtr_debug("IM-shutdown was clean -- all processed died."); + } + else + { + mtr_debug("IM failed to shutdown gracefully. We have to clean the mess..."); + } + unless ($clean_shutdown) { @@ -3553,17 +3596,24 @@ sub im_stop($$) { mtr_kill_processes(\@mysqld_pids); # Complain in error log so that a warning will be shown. - - my $errlog= "$opt_vardir/log/mysql-test-run.pl.err"; - - open (ERRLOG, ">>$errlog") || - mtr_error("Can not open error log ($errlog)"); + # + # TODO: unless BUG#20761 is fixed, we will print the warning + # to stdout, so that it can be seen on console and does not + # produce pushbuild error. + + # my $errlog= "$opt_vardir/log/mysql-test-run.pl.err"; + # + # open (ERRLOG, ">>$errlog") || + # mtr_error("Can not open error log ($errlog)"); + # + # my $ts= localtime(); + # print ERRLOG + # "Warning: [$ts] Instance Manager did not shutdown gracefully.\n"; + # + # close ERRLOG; my $ts= localtime(); - print ERRLOG - "[$where] Warning: [$ts] Instance Manager did not shutdown gracefully.\n"; - - close ERRLOG; + print "[$where] Warning: [$ts] Instance Manager did not shutdown gracefully.\n"; } # That's all. @@ -3746,6 +3796,7 @@ sub run_mysqltest ($) { $ENV{'MYSQL_MY_PRINT_DEFAULTS'}= $exe_my_print_defaults; $ENV{'UDF_EXAMPLE_LIB'}= ($lib_udf_example ? basename($lib_udf_example) : ""); + $ENV{'MY_PERROR'}= $exe_perror; $ENV{'NDB_STATUS_OK'}= $clusters->[0]->{'installed_ok'}; $ENV{'NDB_SLAVE_STATUS_OK'}= $clusters->[0]->{'installed_ok'};; diff --git a/mysql-test/r/cast.result b/mysql-test/r/cast.result index 5e8f5dca711..27a36b0cb7c 100644 --- a/mysql-test/r/cast.result +++ b/mysql-test/r/cast.result @@ -381,3 +381,14 @@ DROP TABLE t1; select cast(NULL as decimal(6)) as t1; t1 NULL +set names latin1; +select hex(cast('a' as char(2) binary)); +hex(cast('a' as char(2) binary)) +61 +select hex(cast('a' as binary(2))); +hex(cast('a' as binary(2))) +6100 +select hex(cast('a' as char(2) binary)); +hex(cast('a' as char(2) binary)) +61 +End of 5.0 tests diff --git a/mysql-test/r/date_formats.result b/mysql-test/r/date_formats.result index ee70327d17b..017631bf8d1 100644 --- a/mysql-test/r/date_formats.result +++ b/mysql-test/r/date_formats.result @@ -452,6 +452,8 @@ create table t1 select str_to_date("2003-01-02 10:11:12.0012", "%Y-%m-%d %H:%i:% str_to_date("10:11:12.0012", "%H:%i:%S.%f") as f2, str_to_date("2003-01-02", "%Y-%m-%d") as f3, str_to_date("02", "%d") as f4, str_to_date("02 10", "%d %H") as f5; +Warnings: +Warning 1265 Data truncated for column 'f4' at row 1 describe t1; Field Type Null Key Default Extra f1 datetime YES NULL @@ -461,7 +463,7 @@ f4 date YES NULL f5 time YES NULL select * from t1; f1 f2 f3 f4 f5 -2003-01-02 10:11:12 10:11:12 2003-01-02 0000-00-02 58:00:00 +2003-01-02 10:11:12 10:11:12 2003-01-02 0000-00-00 58:00:00 drop table t1; create table t1 select "02 10" as a, "%d %H" as b; select str_to_date(a,b) from t1; diff --git a/mysql-test/r/delete.result b/mysql-test/r/delete.result index 05f1c967e77..0946dc8f809 100644 --- a/mysql-test/r/delete.result +++ b/mysql-test/r/delete.result @@ -192,3 +192,13 @@ delete t2.*,t3.* from t1,t2,t3 where t1.a=t2.a AND t2.b=t3.a and t1.b=t3.b; select * from t3; a b drop table t1,t2,t3; +create table t1(a date not null); +insert into t1 values (0); +select * from t1 where a is null; +a +0000-00-00 +delete from t1 where a is null; +select count(*) from t1; +count(*) +0 +drop table t1; diff --git a/mysql-test/r/federated.result b/mysql-test/r/federated.result index 97d138cfc76..77e2d251cb8 100644 --- a/mysql-test/r/federated.result +++ b/mysql-test/r/federated.result @@ -967,6 +967,8 @@ CREATE TABLE federated.t1 ( `blurb` text default '', PRIMARY KEY (blurb_id)) DEFAULT CHARSET=latin1; +Warnings: +Warning 1101 BLOB/TEXT column 'blurb' can't have a default value DROP TABLE IF EXISTS federated.t1; CREATE TABLE federated.t1 ( `blurb_id` int NOT NULL DEFAULT 0, @@ -975,6 +977,8 @@ PRIMARY KEY (blurb_id)) ENGINE="FEDERATED" DEFAULT CHARSET=latin1 CONNECTION='mysql://root@127.0.0.1:SLAVE_PORT/federated/t1'; +Warnings: +Warning 1101 BLOB/TEXT column 'blurb' can't have a default value INSERT INTO federated.t1 VALUES (1, " MySQL supports a number of column types in several categories: numeric types, date and time types, and string (character) types. This chapter first gives an overview of these column types, and then provides a more detailed description of the properties of the types in each category, and a summary of the column type storage requirements. The overview is intentionally brief. The more detailed descriptions should be consulted for additional information about particular column types, such as the allowable formats in which you can specify values."); INSERT INTO federated.t1 VALUES (2, "All arithmetic is done using signed BIGINT or DOUBLE values, so you should not use unsigned big integers larger than 9223372036854775807 (63 bits) except with bit functions! If you do that, some of the last digits in the result may be wrong because of rounding errors when converting a BIGINT value to a DOUBLE."); INSERT INTO federated.t1 VALUES (3, " A floating-point number. p represents the precision. It can be from 0 to 24 for a single-precision floating-point number and from 25 to 53 for a double-precision floating-point number. These types are like the FLOAT and DOUBLE types described immediately following. FLOAT(p) has the same range as the corresponding FLOAT and DOUBLE types, but the display size and number of decimals are undefined. "); diff --git a/mysql-test/r/fulltext_distinct.result b/mysql-test/r/fulltext_distinct.result index de0668ff28c..7fd42fab646 100644 --- a/mysql-test/r/fulltext_distinct.result +++ b/mysql-test/r/fulltext_distinct.result @@ -8,6 +8,8 @@ KEY kt(tag), KEY kv(value(15)), FULLTEXT KEY kvf(value) ) ENGINE=MyISAM; +Warnings: +Warning 1101 BLOB/TEXT column 'value' can't have a default value CREATE TABLE t2 ( id_t2 mediumint unsigned NOT NULL default '0', id_t1 mediumint unsigned NOT NULL default '0', diff --git a/mysql-test/r/fulltext_update.result b/mysql-test/r/fulltext_update.result index 5d3f95b318c..4a615c88fdd 100644 --- a/mysql-test/r/fulltext_update.result +++ b/mysql-test/r/fulltext_update.result @@ -9,6 +9,8 @@ name VARCHAR(80) DEFAULT '' NOT NULL, FULLTEXT(url,description,shortdesc,longdesc), PRIMARY KEY(gnr) ); +Warnings: +Warning 1101 BLOB/TEXT column 'longdesc' can't have a default value insert into test (url,shortdesc,longdesc,description,name) VALUES ("http:/test.at", "kurz", "lang","desc", "name"); insert into test (url,shortdesc,longdesc,description,name) VALUES diff --git a/mysql-test/r/func_compress.result b/mysql-test/r/func_compress.result index a4b780d146a..1a5e64978e6 100644 --- a/mysql-test/r/func_compress.result +++ b/mysql-test/r/func_compress.result @@ -79,3 +79,31 @@ uncompress(a) uncompressed_length(a) NULL NULL a 1 drop table t1; +create table t1 (a varchar(32) not null); +insert into t1 values ('foo'); +explain select * from t1 where uncompress(a) is null; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 system NULL NULL NULL NULL 1 +Warnings: +Error 1259 ZLIB: Input data corrupted +select * from t1 where uncompress(a) is null; +a +foo +Warnings: +Error 1259 ZLIB: Input data corrupted +explain select *, uncompress(a) from t1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 system NULL NULL NULL NULL 1 +select *, uncompress(a) from t1; +a uncompress(a) +foo NULL +Warnings: +Error 1259 ZLIB: Input data corrupted +select *, uncompress(a), uncompress(a) is null from t1; +a uncompress(a) uncompress(a) is null +foo NULL 1 +Warnings: +Error 1259 ZLIB: Input data corrupted +Error 1259 ZLIB: Input data corrupted +drop table t1; +End of 5.0 tests diff --git a/mysql-test/r/func_group.result b/mysql-test/r/func_group.result index 74da6478470..26cf94bb820 100644 --- a/mysql-test/r/func_group.result +++ b/mysql-test/r/func_group.result @@ -990,3 +990,17 @@ FROM t1 t, t2 c WHERE t.a = c.b; minid NULL DROP TABLE t1,t2; +create table t1 select variance(0); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `variance(0)` double(8,4) default NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +create table t1 select stddev(0); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `stddev(0)` double(8,4) default NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; diff --git a/mysql-test/r/func_time.result b/mysql-test/r/func_time.result index 487d40f8a67..7f7228fbf4d 100644 --- a/mysql-test/r/func_time.result +++ b/mysql-test/r/func_time.result @@ -667,6 +667,78 @@ timestampdiff(SQL_TSI_DAY, '1996-02-01', '1996-03-01') as a3, timestampdiff(SQL_TSI_DAY, '2000-02-01', '2000-03-01') as a4; a1 a2 a3 a4 28 28 29 29 +SELECT TIMESTAMPDIFF(day,'2006-01-10 14:30:28','2006-01-11 14:30:27'); +TIMESTAMPDIFF(day,'2006-01-10 14:30:28','2006-01-11 14:30:27') +0 +SELECT TIMESTAMPDIFF(day,'2006-01-10 14:30:28','2006-01-11 14:30:28'); +TIMESTAMPDIFF(day,'2006-01-10 14:30:28','2006-01-11 14:30:28') +1 +SELECT TIMESTAMPDIFF(day,'2006-01-10 14:30:28','2006-01-11 14:30:29'); +TIMESTAMPDIFF(day,'2006-01-10 14:30:28','2006-01-11 14:30:29') +1 +SELECT TIMESTAMPDIFF(day,'2006-01-10 14:30:28','2006-01-12 14:30:27'); +TIMESTAMPDIFF(day,'2006-01-10 14:30:28','2006-01-12 14:30:27') +1 +SELECT TIMESTAMPDIFF(day,'2006-01-10 14:30:28','2006-01-12 14:30:28'); +TIMESTAMPDIFF(day,'2006-01-10 14:30:28','2006-01-12 14:30:28') +2 +SELECT TIMESTAMPDIFF(day,'2006-01-10 14:30:28','2006-01-12 14:30:29'); +TIMESTAMPDIFF(day,'2006-01-10 14:30:28','2006-01-12 14:30:29') +2 +SELECT TIMESTAMPDIFF(week,'2006-01-10 14:30:28','2006-01-17 14:30:27'); +TIMESTAMPDIFF(week,'2006-01-10 14:30:28','2006-01-17 14:30:27') +0 +SELECT TIMESTAMPDIFF(week,'2006-01-10 14:30:28','2006-01-17 14:30:28'); +TIMESTAMPDIFF(week,'2006-01-10 14:30:28','2006-01-17 14:30:28') +1 +SELECT TIMESTAMPDIFF(week,'2006-01-10 14:30:28','2006-01-17 14:30:29'); +TIMESTAMPDIFF(week,'2006-01-10 14:30:28','2006-01-17 14:30:29') +1 +SELECT TIMESTAMPDIFF(week,'2006-01-10 14:30:28','2006-01-24 14:30:27'); +TIMESTAMPDIFF(week,'2006-01-10 14:30:28','2006-01-24 14:30:27') +1 +SELECT TIMESTAMPDIFF(week,'2006-01-10 14:30:28','2006-01-24 14:30:28'); +TIMESTAMPDIFF(week,'2006-01-10 14:30:28','2006-01-24 14:30:28') +2 +SELECT TIMESTAMPDIFF(week,'2006-01-10 14:30:28','2006-01-24 14:30:29'); +TIMESTAMPDIFF(week,'2006-01-10 14:30:28','2006-01-24 14:30:29') +2 +SELECT TIMESTAMPDIFF(month,'2006-01-10 14:30:28','2006-02-10 14:30:27'); +TIMESTAMPDIFF(month,'2006-01-10 14:30:28','2006-02-10 14:30:27') +0 +SELECT TIMESTAMPDIFF(month,'2006-01-10 14:30:28','2006-02-10 14:30:28'); +TIMESTAMPDIFF(month,'2006-01-10 14:30:28','2006-02-10 14:30:28') +1 +SELECT TIMESTAMPDIFF(month,'2006-01-10 14:30:28','2006-02-10 14:30:29'); +TIMESTAMPDIFF(month,'2006-01-10 14:30:28','2006-02-10 14:30:29') +1 +SELECT TIMESTAMPDIFF(month,'2006-01-10 14:30:28','2006-03-10 14:30:27'); +TIMESTAMPDIFF(month,'2006-01-10 14:30:28','2006-03-10 14:30:27') +1 +SELECT TIMESTAMPDIFF(month,'2006-01-10 14:30:28','2006-03-10 14:30:28'); +TIMESTAMPDIFF(month,'2006-01-10 14:30:28','2006-03-10 14:30:28') +2 +SELECT TIMESTAMPDIFF(month,'2006-01-10 14:30:28','2006-03-10 14:30:29'); +TIMESTAMPDIFF(month,'2006-01-10 14:30:28','2006-03-10 14:30:29') +2 +SELECT TIMESTAMPDIFF(year,'2006-01-10 14:30:28','2007-01-10 14:30:27'); +TIMESTAMPDIFF(year,'2006-01-10 14:30:28','2007-01-10 14:30:27') +0 +SELECT TIMESTAMPDIFF(year,'2006-01-10 14:30:28','2007-01-10 14:30:28'); +TIMESTAMPDIFF(year,'2006-01-10 14:30:28','2007-01-10 14:30:28') +1 +SELECT TIMESTAMPDIFF(year,'2006-01-10 14:30:28','2007-01-10 14:30:29'); +TIMESTAMPDIFF(year,'2006-01-10 14:30:28','2007-01-10 14:30:29') +1 +SELECT TIMESTAMPDIFF(year,'2006-01-10 14:30:28','2008-01-10 14:30:27'); +TIMESTAMPDIFF(year,'2006-01-10 14:30:28','2008-01-10 14:30:27') +1 +SELECT TIMESTAMPDIFF(year,'2006-01-10 14:30:28','2008-01-10 14:30:28'); +TIMESTAMPDIFF(year,'2006-01-10 14:30:28','2008-01-10 14:30:28') +2 +SELECT TIMESTAMPDIFF(year,'2006-01-10 14:30:28','2008-01-10 14:30:29'); +TIMESTAMPDIFF(year,'2006-01-10 14:30:28','2008-01-10 14:30:29') +2 select date_add(time,INTERVAL 1 SECOND) from t1; date_add(time,INTERVAL 1 SECOND) NULL diff --git a/mysql-test/r/gis-rtree.result b/mysql-test/r/gis-rtree.result index e2e7a612dec..92fcbfcdc9e 100644 --- a/mysql-test/r/gis-rtree.result +++ b/mysql-test/r/gis-rtree.result @@ -804,6 +804,8 @@ INSERT INTO t2 SELECT GeomFromText(st) FROM t1; ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field drop table t1, t2; CREATE TABLE t1 (`geometry` geometry NOT NULL default '',SPATIAL KEY `gndx` (`geometry`(32))) ENGINE=MyISAM DEFAULT CHARSET=latin1; +Warnings: +Warning 1101 BLOB/TEXT column 'geometry' can't have a default value INSERT INTO t1 (geometry) VALUES (PolygonFromText('POLYGON((-18.6086111000 -66.9327777000, -18.6055555000 -66.8158332999, -18.7186111000 -66.8102777000, -18.7211111000 -66.9269443999, @@ -820,6 +822,8 @@ CREATE TABLE t1 ( c1 geometry NOT NULL default '', SPATIAL KEY i1 (c1(32)) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; +Warnings: +Warning 1101 BLOB/TEXT column 'c1' can't have a default value INSERT INTO t1 (c1) VALUES ( PolygonFromText('POLYGON((-18.6086111000 -66.9327777000, -18.6055555000 -66.8158332999, @@ -834,6 +838,8 @@ CREATE TABLE t1 ( c1 geometry NOT NULL default '', SPATIAL KEY i1 (c1(32)) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; +Warnings: +Warning 1101 BLOB/TEXT column 'c1' can't have a default value INSERT INTO t1 (c1) VALUES ( PolygonFromText('POLYGON((-18.6086111000 -66.9327777000, -18.6055555000 -66.8158332999, diff --git a/mysql-test/r/gis.result b/mysql-test/r/gis.result index 9017e96c8f4..8fff04508d1 100644 --- a/mysql-test/r/gis.result +++ b/mysql-test/r/gis.result @@ -583,6 +583,8 @@ t1 CREATE TABLE `t1` ( drop table t1; CREATE TABLE `t1` (`object_id` bigint(20) unsigned NOT NULL default '0', `geo` geometry NOT NULL default '') ENGINE=MyISAM ; +Warnings: +Warning 1101 BLOB/TEXT column 'geo' can't have a default value insert into t1 values ('85984',GeomFromText('MULTIPOLYGON(((-115.006363 36.305435,-114.992394 36.305202,-114.991219 36.305975,-114.991163 36.306845,-114.989432 36.309452,-114.978275 36.312642,-114.977363 diff --git a/mysql-test/r/have_perror.require b/mysql-test/r/have_perror.require new file mode 100644 index 00000000000..260687c87f0 --- /dev/null +++ b/mysql-test/r/have_perror.require @@ -0,0 +1,2 @@ +have_perror +1 diff --git a/mysql-test/r/im_daemon_life_cycle.result b/mysql-test/r/im_daemon_life_cycle.result index a5526a31384..927ea86dfdd 100644 --- a/mysql-test/r/im_daemon_life_cycle.result +++ b/mysql-test/r/im_daemon_life_cycle.result @@ -1,4 +1,7 @@ Success: the process has been started. +SHOW VARIABLES LIKE 'server_id'; +Variable_name Value +server_id 1 SHOW INSTANCES; instance_name state mysqld1 online diff --git a/mysql-test/r/im_utils.result b/mysql-test/r/im_utils.result index df20a7fb5c7..ee70d7950f8 100644 --- a/mysql-test/r/im_utils.result +++ b/mysql-test/r/im_utils.result @@ -1,4 +1,7 @@ Success: the process has been started. +SHOW VARIABLES LIKE 'server_id'; +Variable_name Value +server_id 1 SHOW INSTANCES; instance_name state mysqld1 online diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result index b199ec5b2fb..aca47a675d4 100644 --- a/mysql-test/r/join_outer.result +++ b/mysql-test/r/join_outer.result @@ -598,6 +598,8 @@ id int(11) DEFAULT '0' NOT NULL, name tinytext DEFAULT '' NOT NULL, UNIQUE id (id) ); +Warnings: +Warning 1101 BLOB/TEXT column 'name' can't have a default value INSERT INTO t1 VALUES (1,'yes'),(2,'no'); CREATE TABLE t2 ( id int(11) DEFAULT '0' NOT NULL, diff --git a/mysql-test/r/mysql.result b/mysql-test/r/mysql.result index d70366a7589..c133e4eb2cb 100644 --- a/mysql-test/r/mysql.result +++ b/mysql-test/r/mysql.result @@ -104,4 +104,14 @@ c_cp932 | 2 | NULL | | 3 | | +------+------+ +create table t1(a int, b varchar(255), c int); +Field Type Null Key Default Extra +a int(11) YES NULL +b varchar(255) YES NULL +c int(11) YES NULL +Field Type Null Key Default Extra +a int(11) YES NULL +b varchar(255) YES NULL +c int(11) YES NULL +drop table t1; End of 5.0 tests diff --git a/mysql-test/r/mysqlcheck.result b/mysql-test/r/mysqlcheck.result index a3c14b13bde..a76402a87b0 100644 --- a/mysql-test/r/mysqlcheck.result +++ b/mysql-test/r/mysqlcheck.result @@ -48,3 +48,10 @@ mysql.time_zone_name OK mysql.time_zone_transition OK mysql.time_zone_transition_type OK mysql.user OK +create table t1 (a int); +create view v1 as select * from t1; +test.t1 OK +test.t1 OK +drop view v1; +drop table t1; +End of 5.0 tests diff --git a/mysql-test/r/mysqldump.result b/mysql-test/r/mysqldump.result index 153ddb7ca2b..9d44756f17c 100644 --- a/mysql-test/r/mysqldump.result +++ b/mysql-test/r/mysqldump.result @@ -2324,7 +2324,7 @@ RETURN a+b */;; /*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE*/;; /*!50003 DROP FUNCTION IF EXISTS `bug9056_func2` */;; /*!50003 SET SESSION SQL_MODE=""*/;; -/*!50003 CREATE*/ /*!50020 DEFINER=`root`@`localhost`*/ /*!50003 FUNCTION `bug9056_func2`(f1 char binary) RETURNS char(1) +/*!50003 CREATE*/ /*!50020 DEFINER=`root`@`localhost`*/ /*!50003 FUNCTION `bug9056_func2`(f1 char binary) RETURNS char(1) CHARSET latin1 begin set f1= concat( 'hello', f1 ); return f1; @@ -2914,6 +2914,22 @@ drop view v1; drop table t1; drop database mysqldump_dbb; use test; +create user mysqltest_1; +create table t1(a int, b varchar(34)); +reset master; +mysqldump: Couldn't execute 'FLUSH TABLES': Access denied; you need the RELOAD privilege for this operation (1227) +mysqldump: Couldn't execute 'FLUSH TABLES': Access denied; you need the RELOAD privilege for this operation (1227) +grant RELOAD on *.* to mysqltest_1@localhost; +mysqldump: Couldn't execute 'SHOW MASTER STATUS': Access denied; you need the SUPER,REPLICATION CLIENT privilege for this operation (1227) +mysqldump: Couldn't execute 'SHOW MASTER STATUS': Access denied; you need the SUPER,REPLICATION CLIENT privilege for this operation (1227) +grant REPLICATION CLIENT on *.* to mysqltest_1@localhost; +CHANGE MASTER TO MASTER_LOG_FILE='master-bin.000001', MASTER_LOG_POS=537; +CREATE TABLE `t1` ( + `a` int(11) default NULL, + `b` varchar(34) default NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1; +drop table t1; +drop user mysqltest_1; End of 5.0 tests create table t1 (a text , b text); create table t2 (a text , b text); diff --git a/mysql-test/r/mysqlshow.result b/mysql-test/r/mysqlshow.result index 942cde83f21..2bf8a58de4e 100644 --- a/mysql-test/r/mysqlshow.result +++ b/mysql-test/r/mysqlshow.result @@ -75,3 +75,52 @@ Database: test 2 rows in set. DROP TABLE t1, t2; +Database: information_schema ++---------------------------------------+ +| Tables | ++---------------------------------------+ +| CHARACTER_SETS | +| COLLATIONS | +| COLLATION_CHARACTER_SET_APPLICABILITY | +| COLUMNS | +| COLUMN_PRIVILEGES | +| KEY_COLUMN_USAGE | +| ROUTINES | +| SCHEMATA | +| SCHEMA_PRIVILEGES | +| STATISTICS | +| TABLES | +| TABLE_CONSTRAINTS | +| TABLE_PRIVILEGES | +| TRIGGERS | +| USER_PRIVILEGES | +| VIEWS | ++---------------------------------------+ +Database: INFORMATION_SCHEMA ++---------------------------------------+ +| Tables | ++---------------------------------------+ +| CHARACTER_SETS | +| COLLATIONS | +| COLLATION_CHARACTER_SET_APPLICABILITY | +| COLUMNS | +| COLUMN_PRIVILEGES | +| KEY_COLUMN_USAGE | +| ROUTINES | +| SCHEMATA | +| SCHEMA_PRIVILEGES | +| STATISTICS | +| TABLES | +| TABLE_CONSTRAINTS | +| TABLE_PRIVILEGES | +| TRIGGERS | +| USER_PRIVILEGES | +| VIEWS | ++---------------------------------------+ +Wildcard: inf_rmation_schema ++--------------------+ +| Databases | ++--------------------+ +| information_schema | ++--------------------+ +End of 5.0 tests diff --git a/mysql-test/r/mysqltest.result b/mysql-test/r/mysqltest.result index ef4dd83564b..a50b1dfd5ab 100644 --- a/mysql-test/r/mysqltest.result +++ b/mysql-test/r/mysqltest.result @@ -440,6 +440,13 @@ select-me ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'insertz error query' at line 1 drop table t1; drop table t1; +sleep; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'sleep' at line 1 +sleep; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'sleep' at line 1 +; +ERROR 42000: Query was empty +End of 5.0 tests select "b" as col1, "c" as col2; col1 col2 b c diff --git a/mysql-test/r/ndb_basic.result b/mysql-test/r/ndb_basic.result index 631165d9fc8..997c7bae1d6 100644 --- a/mysql-test/r/ndb_basic.result +++ b/mysql-test/r/ndb_basic.result @@ -748,6 +748,8 @@ f1 f2 f3 111111 aaaaaa 1 222222 bbbbbb 2 drop table t1; +Illegal ndb error code: 1186 +Enf of 5.0 tests CREATE TABLE t1 (a VARCHAR(255) NOT NULL, CONSTRAINT pk_a PRIMARY KEY (a))engine=ndb; CREATE TABLE t2(a VARCHAR(255) NOT NULL, diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result index a36935a583d..9756ab49ac7 100644 --- a/mysql-test/r/order_by.result +++ b/mysql-test/r/order_by.result @@ -280,6 +280,8 @@ info text NOT NULL default '', ipnr varchar(30) NOT NULL default '', PRIMARY KEY (member_id) ) ENGINE=MyISAM PACK_KEYS=1; +Warnings: +Warning 1101 BLOB/TEXT column 'info' can't have a default value insert into t1 (member_id) values (1),(2),(3); select member_id, nickname, voornaam FROM t1 ORDER by lastchange_datum DESC LIMIT 2; diff --git a/mysql-test/r/perror.result b/mysql-test/r/perror.result new file mode 100644 index 00000000000..4946523bc42 --- /dev/null +++ b/mysql-test/r/perror.result @@ -0,0 +1 @@ +Illegal error code: 10000 diff --git a/mysql-test/r/rpl_sp.result b/mysql-test/r/rpl_sp.result index 24c0a73c717..261e7cb072c 100644 --- a/mysql-test/r/rpl_sp.result +++ b/mysql-test/r/rpl_sp.result @@ -420,4 +420,48 @@ SELECT * FROM t1; col test DROP PROCEDURE p1; + +---> Test for BUG#20438 + +---> Preparing environment... +---> connection: master +DROP PROCEDURE IF EXISTS p1; +DROP FUNCTION IF EXISTS f1; + +---> Synchronizing slave with master... + +---> connection: master + +---> Creating procedure... +/*!50003 CREATE PROCEDURE p1() SET @a = 1 */; +/*!50003 CREATE FUNCTION f1() RETURNS INT RETURN 0 */; + +---> Checking on master... +SHOW CREATE PROCEDURE p1; +Procedure sql_mode Create Procedure +p1 CREATE DEFINER=`root`@`localhost` PROCEDURE `p1`() +SET @a = 1 +SHOW CREATE FUNCTION f1; +Function sql_mode Create Function +f1 CREATE DEFINER=`root`@`localhost` FUNCTION `f1`() RETURNS int(11) +RETURN 0 + +---> Synchronizing slave with master... +---> connection: master + +---> Checking on slave... +SHOW CREATE PROCEDURE p1; +Procedure sql_mode Create Procedure +p1 CREATE DEFINER=`root`@`localhost` PROCEDURE `p1`() +SET @a = 1 +SHOW CREATE FUNCTION f1; +Function sql_mode Create Function +f1 CREATE DEFINER=`root`@`localhost` FUNCTION `f1`() RETURNS int(11) +RETURN 0 + +---> connection: master + +---> Cleaning up... +DROP PROCEDURE p1; +DROP FUNCTION f1; drop table t1; diff --git a/mysql-test/r/rpl_trigger.result b/mysql-test/r/rpl_trigger.result index 53ec2c59cf3..bd6d6e63d57 100644 --- a/mysql-test/r/rpl_trigger.result +++ b/mysql-test/r/rpl_trigger.result @@ -899,3 +899,50 @@ Tables_in_test (t_) SHOW TRIGGERS; Trigger Event Table Statement Timing Created sql_mode Definer RESET MASTER; +START SLAVE; + +---> Test for BUG#20438 + +---> Preparing environment... +---> connection: master +DROP TABLE IF EXISTS t1; +DROP TABLE IF EXISTS t2; + +---> Synchronizing slave with master... + +---> connection: master + +---> Creating objects... +CREATE TABLE t1(c INT); +CREATE TABLE t2(c INT); +/*!50003 CREATE TRIGGER t1_bi BEFORE INSERT ON t1 +FOR EACH ROW +INSERT INTO t2 VALUES(NEW.c * 10) */; + +---> Inserting value... +INSERT INTO t1 VALUES(1); + +---> Checking on master... +SELECT * FROM t1; +c +1 +SELECT * FROM t2; +c +10 + +---> Synchronizing slave with master... +---> connection: master + +---> Checking on slave... +SELECT * FROM t1; +c +1 +SELECT * FROM t2; +c +10 + +---> connection: master + +---> Cleaning up... +DROP TABLE t1; +DROP TABLE t2; diff --git a/mysql-test/r/rpl_view.result b/mysql-test/r/rpl_view.result index f038f4d00cb..4e1a7153b65 100644 --- a/mysql-test/r/rpl_view.result +++ b/mysql-test/r/rpl_view.result @@ -43,3 +43,40 @@ drop view v1; select * from v1 order by a; ERROR 42S02: Table 'test.v1' doesn't exist drop table t1; + +---> Test for BUG#20438 + +---> Preparing environment... +---> connection: master +DROP TABLE IF EXISTS t1; +DROP VIEW IF EXISTS v1; + +---> Synchronizing slave with master... + +---> connection: master + +---> Creating objects... +CREATE TABLE t1(c INT); +/*!50003 CREATE VIEW v1 AS SELECT * FROM t1 */; + +---> Inserting value... +INSERT INTO t1 VALUES(1); + +---> Checking on master... +SELECT * FROM t1; +c +1 + +---> Synchronizing slave with master... +---> connection: master + +---> Checking on slave... +SELECT * FROM t1; +c +1 + +---> connection: master + +---> Cleaning up... +DROP VIEW v1; +DROP TABLE t1; diff --git a/mysql-test/r/show_check.result b/mysql-test/r/show_check.result index f5b3fc99baf..465cb853581 100644 --- a/mysql-test/r/show_check.result +++ b/mysql-test/r/show_check.result @@ -692,3 +692,20 @@ Warning 1541 The syntax 'SHOW PLUGIN' is deprecated and will be removed in MySQL show plugin; show plugins; End of 5.1 tests +CREATE PROCEDURE p1() +BEGIN +SET @s= 'CREATE VIEW v1 AS SELECT SQL_CACHE 1'; +PREPARE stmt FROM @s; +EXECUTE stmt; +DROP PREPARE stmt; +END | +CALL p1(); +SHOW CREATE VIEW v1; +View Create View +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select sql_cache 1 AS `1` +DROP PROCEDURE p1; +DROP VIEW v1; +SHOW TABLES FROM no_such_database; +ERROR 42000: Unknown database 'no_such_database' +SHOW COLUMNS FROM no_such_table; +ERROR 42S02: Table 'test.no_such_table' doesn't exist diff --git a/mysql-test/r/sp-code.result b/mysql-test/r/sp-code.result index 8bdb9a724d7..4ae38861d29 100644 --- a/mysql-test/r/sp-code.result +++ b/mysql-test/r/sp-code.result @@ -155,11 +155,11 @@ Pos Instruction 0 stmt 9 "drop temporary table if exists sudoku..." 1 stmt 1 "create temporary table sudoku_work ( ..." 2 stmt 1 "create temporary table sudoku_schedul..." -3 stmt 95 "call sudoku_init(" +3 stmt 95 "call sudoku_init()" 4 jump_if_not 7(8) p_naive@0 5 stmt 4 "update sudoku_work set cnt = 0 where ..." 6 jump 8 -7 stmt 95 "call sudoku_count(" +7 stmt 95 "call sudoku_count()" 8 stmt 6 "insert into sudoku_schedule (row,col)..." 9 set v_scounter@2 0 10 set v_i@3 1 @@ -199,3 +199,10 @@ Pos Instruction 44 jump 14 45 stmt 9 "drop temporary table sudoku_work, sud..." drop procedure sudoku_solve; +DROP PROCEDURE IF EXISTS p1; +CREATE PROCEDURE p1() CREATE INDEX idx ON t1 (c1); +SHOW PROCEDURE CODE p1; +Pos Instruction +0 stmt 2 "CREATE INDEX idx ON t1 (c1)" +DROP PROCEDURE p1; +End of 5.0 tests. diff --git a/mysql-test/r/sp-error.result b/mysql-test/r/sp-error.result index 63401845f23..2b01415c2d0 100644 --- a/mysql-test/r/sp-error.result +++ b/mysql-test/r/sp-error.result @@ -1174,6 +1174,19 @@ drop procedure bug15091; drop function if exists bug16896; create aggregate function bug16896() returns int return 1; ERROR 42000: AGGREGATE is not supported for stored functions +DROP PROCEDURE IF EXISTS bug14702; +CREATE IF NOT EXISTS PROCEDURE bug14702() +BEGIN +END; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF NOT EXISTS PROCEDURE bug14702() +BEGIN +END' at line 1 +CREATE PROCEDURE IF NOT EXISTS bug14702() +BEGIN +END; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF NOT EXISTS bug14702() +BEGIN +END' at line 1 drop function if exists bug16164; create function bug16164() returns int begin diff --git a/mysql-test/r/sp-security.result b/mysql-test/r/sp-security.result index 4860058a8a1..5272bf47672 100644 --- a/mysql-test/r/sp-security.result +++ b/mysql-test/r/sp-security.result @@ -441,3 +441,55 @@ SELECT Host,User,Password FROM mysql.user WHERE User='user19857'; Host User Password localhost user19857 *82DC221D557298F6CE9961037DB1C90604792F5C DROP USER user19857@localhost; +DROP TABLE IF EXISTS t1; +DROP VIEW IF EXISTS v1; +DROP FUNCTION IF EXISTS f_suid; +DROP PROCEDURE IF EXISTS p_suid; +DROP FUNCTION IF EXISTS f_evil; +DELETE FROM mysql.user WHERE user LIKE 'mysqltest\_%'; +DELETE FROM mysql.db WHERE user LIKE 'mysqltest\_%'; +DELETE FROM mysql.tables_priv WHERE user LIKE 'mysqltest\_%'; +DELETE FROM mysql.columns_priv WHERE user LIKE 'mysqltest\_%'; +FLUSH PRIVILEGES; +CREATE TABLE t1 (i INT); +CREATE FUNCTION f_suid(i INT) RETURNS INT SQL SECURITY DEFINER RETURN 0; +CREATE PROCEDURE p_suid(IN i INT) SQL SECURITY DEFINER SET @c:= 0; +CREATE USER mysqltest_u1@localhost; +GRANT EXECUTE ON test.* TO mysqltest_u1@localhost; +CREATE DEFINER=mysqltest_u1@localhost FUNCTION f_evil () RETURNS INT +SQL SECURITY INVOKER +BEGIN +SET @a:= CURRENT_USER(); +SET @b:= (SELECT COUNT(*) FROM t1); +RETURN @b; +END| +CREATE SQL SECURITY INVOKER VIEW v1 AS SELECT f_evil(); +SELECT COUNT(*) FROM t1; +ERROR 42000: SELECT command denied to user 'mysqltest_u1'@'localhost' for table 't1' +SELECT f_evil(); +ERROR 42000: SELECT command denied to user 'mysqltest_u1'@'localhost' for table 't1' +SELECT @a, @b; +@a @b +mysqltest_u1@localhost NULL +SELECT f_suid(f_evil()); +ERROR 42000: SELECT command denied to user 'mysqltest_u1'@'localhost' for table 't1' +SELECT @a, @b; +@a @b +mysqltest_u1@localhost NULL +CALL p_suid(f_evil()); +ERROR 42000: SELECT command denied to user 'mysqltest_u1'@'localhost' for table 't1' +SELECT @a, @b; +@a @b +mysqltest_u1@localhost NULL +SELECT * FROM v1; +ERROR 42000: SELECT command denied to user 'mysqltest_u1'@'localhost' for table 'v1' +SELECT @a, @b; +@a @b +mysqltest_u1@localhost NULL +DROP VIEW v1; +DROP FUNCTION f_evil; +DROP USER mysqltest_u1@localhost; +DROP PROCEDURE p_suid; +DROP FUNCTION f_suid; +DROP TABLE t1; +End of 5.0 tests. diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result index 8ff180044df..e768ebe36b3 100644 --- a/mysql-test/r/sp.result +++ b/mysql-test/r/sp.result @@ -5057,6 +5057,205 @@ concat('data was: /', var1, '/') data was: /1/ drop table t3| drop procedure bug15217| +DROP PROCEDURE IF EXISTS bug21013 | +CREATE PROCEDURE bug21013(IN lim INT) +BEGIN +DECLARE i INT DEFAULT 0; +WHILE (i < lim) DO +SET @b = LOCATE(_latin1'b', @a, 1); +SET i = i + 1; +END WHILE; +END | +SET @a = _latin2"aaaaaaaaaa" | +CALL bug21013(10) | +DROP PROCEDURE bug21013 | +DROP DATABASE IF EXISTS mysqltest1| +DROP DATABASE IF EXISTS mysqltest2| +CREATE DATABASE mysqltest1 DEFAULT CHARACTER SET utf8| +CREATE DATABASE mysqltest2 DEFAULT CHARACTER SET utf8| +use mysqltest1| +CREATE FUNCTION bug16211_f1() RETURNS CHAR(10) +RETURN ""| +CREATE FUNCTION bug16211_f2() RETURNS CHAR(10) CHARSET koi8r +RETURN ""| +CREATE FUNCTION mysqltest2.bug16211_f3() RETURNS CHAR(10) +RETURN ""| +CREATE FUNCTION mysqltest2.bug16211_f4() RETURNS CHAR(10) CHARSET koi8r +RETURN ""| +SHOW CREATE FUNCTION bug16211_f1| +Function sql_mode Create Function +bug16211_f1 CREATE DEFINER=`root`@`localhost` FUNCTION `bug16211_f1`() RETURNS char(10) CHARSET utf8 +RETURN "" +SHOW CREATE FUNCTION bug16211_f2| +Function sql_mode Create Function +bug16211_f2 CREATE DEFINER=`root`@`localhost` FUNCTION `bug16211_f2`() RETURNS char(10) CHARSET koi8r +RETURN "" +SHOW CREATE FUNCTION mysqltest2.bug16211_f3| +Function sql_mode Create Function +bug16211_f3 CREATE DEFINER=`root`@`localhost` FUNCTION `bug16211_f3`() RETURNS char(10) CHARSET utf8 +RETURN "" +SHOW CREATE FUNCTION mysqltest2.bug16211_f4| +Function sql_mode Create Function +bug16211_f4 CREATE DEFINER=`root`@`localhost` FUNCTION `bug16211_f4`() RETURNS char(10) CHARSET koi8r +RETURN "" +SELECT dtd_identifier +FROM INFORMATION_SCHEMA.ROUTINES +WHERE ROUTINE_SCHEMA = "mysqltest1" AND ROUTINE_NAME = "bug16211_f1"| +dtd_identifier +char(10) CHARSET utf8 +SELECT dtd_identifier +FROM INFORMATION_SCHEMA.ROUTINES +WHERE ROUTINE_SCHEMA = "mysqltest1" AND ROUTINE_NAME = "bug16211_f2"| +dtd_identifier +char(10) CHARSET koi8r +SELECT dtd_identifier +FROM INFORMATION_SCHEMA.ROUTINES +WHERE ROUTINE_SCHEMA = "mysqltest2" AND ROUTINE_NAME = "bug16211_f3"| +dtd_identifier +char(10) CHARSET utf8 +SELECT dtd_identifier +FROM INFORMATION_SCHEMA.ROUTINES +WHERE ROUTINE_SCHEMA = "mysqltest2" AND ROUTINE_NAME = "bug16211_f4"| +dtd_identifier +char(10) CHARSET koi8r +SELECT CHARSET(bug16211_f1())| +CHARSET(bug16211_f1()) +utf8 +SELECT CHARSET(bug16211_f2())| +CHARSET(bug16211_f2()) +koi8r +SELECT CHARSET(mysqltest2.bug16211_f3())| +CHARSET(mysqltest2.bug16211_f3()) +utf8 +SELECT CHARSET(mysqltest2.bug16211_f4())| +CHARSET(mysqltest2.bug16211_f4()) +koi8r +ALTER DATABASE mysqltest1 CHARACTER SET cp1251| +ALTER DATABASE mysqltest2 CHARACTER SET cp1251| +SHOW CREATE FUNCTION bug16211_f1| +Function sql_mode Create Function +bug16211_f1 CREATE DEFINER=`root`@`localhost` FUNCTION `bug16211_f1`() RETURNS char(10) CHARSET utf8 +RETURN "" +SHOW CREATE FUNCTION bug16211_f2| +Function sql_mode Create Function +bug16211_f2 CREATE DEFINER=`root`@`localhost` FUNCTION `bug16211_f2`() RETURNS char(10) CHARSET koi8r +RETURN "" +SHOW CREATE FUNCTION mysqltest2.bug16211_f3| +Function sql_mode Create Function +bug16211_f3 CREATE DEFINER=`root`@`localhost` FUNCTION `bug16211_f3`() RETURNS char(10) CHARSET utf8 +RETURN "" +SHOW CREATE FUNCTION mysqltest2.bug16211_f4| +Function sql_mode Create Function +bug16211_f4 CREATE DEFINER=`root`@`localhost` FUNCTION `bug16211_f4`() RETURNS char(10) CHARSET koi8r +RETURN "" +SELECT dtd_identifier +FROM INFORMATION_SCHEMA.ROUTINES +WHERE ROUTINE_SCHEMA = "mysqltest1" AND ROUTINE_NAME = "bug16211_f1"| +dtd_identifier +char(10) CHARSET utf8 +SELECT dtd_identifier +FROM INFORMATION_SCHEMA.ROUTINES +WHERE ROUTINE_SCHEMA = "mysqltest1" AND ROUTINE_NAME = "bug16211_f2"| +dtd_identifier +char(10) CHARSET koi8r +SELECT dtd_identifier +FROM INFORMATION_SCHEMA.ROUTINES +WHERE ROUTINE_SCHEMA = "mysqltest2" AND ROUTINE_NAME = "bug16211_f3"| +dtd_identifier +char(10) CHARSET utf8 +SELECT dtd_identifier +FROM INFORMATION_SCHEMA.ROUTINES +WHERE ROUTINE_SCHEMA = "mysqltest2" AND ROUTINE_NAME = "bug16211_f4"| +dtd_identifier +char(10) CHARSET koi8r +SELECT CHARSET(bug16211_f1())| +CHARSET(bug16211_f1()) +utf8 +SELECT CHARSET(bug16211_f2())| +CHARSET(bug16211_f2()) +koi8r +SELECT CHARSET(mysqltest2.bug16211_f3())| +CHARSET(mysqltest2.bug16211_f3()) +utf8 +SELECT CHARSET(mysqltest2.bug16211_f4())| +CHARSET(mysqltest2.bug16211_f4()) +koi8r +use test| +DROP DATABASE mysqltest1| +DROP DATABASE mysqltest2| +DROP DATABASE IF EXISTS mysqltest1| +CREATE DATABASE mysqltest1 DEFAULT CHARACTER SET utf8| +use mysqltest1| +CREATE PROCEDURE bug16676_p1( +IN p1 CHAR(10), +INOUT p2 CHAR(10), +OUT p3 CHAR(10)) +BEGIN +SELECT CHARSET(p1), COLLATION(p1); +SELECT CHARSET(p2), COLLATION(p2); +SELECT CHARSET(p3), COLLATION(p3); +END| +CREATE PROCEDURE bug16676_p2( +IN p1 CHAR(10) CHARSET koi8r, +INOUT p2 CHAR(10) CHARSET cp1251, +OUT p3 CHAR(10) CHARSET greek) +BEGIN +SELECT CHARSET(p1), COLLATION(p1); +SELECT CHARSET(p2), COLLATION(p2); +SELECT CHARSET(p3), COLLATION(p3); +END| +SET @v2 = 'b'| +SET @v3 = 'c'| +CALL bug16676_p1('a', @v2, @v3)| +CHARSET(p1) COLLATION(p1) +utf8 utf8_general_ci +CHARSET(p2) COLLATION(p2) +utf8 utf8_general_ci +CHARSET(p3) COLLATION(p3) +utf8 utf8_general_ci +CALL bug16676_p2('a', @v2, @v3)| +CHARSET(p1) COLLATION(p1) +koi8r koi8r_general_ci +CHARSET(p2) COLLATION(p2) +cp1251 cp1251_general_ci +CHARSET(p3) COLLATION(p3) +greek greek_general_ci +use test| +DROP DATABASE mysqltest1| +drop procedure if exists bug19862| +CREATE TABLE t11 (a INT)| +CREATE TABLE t12 (a INT)| +CREATE FUNCTION bug19862(x INT) RETURNS INT +BEGIN +INSERT INTO t11 VALUES (x); +RETURN x+1; +END| +INSERT INTO t12 VALUES (1), (2)| +SELECT bug19862(a) FROM t12 ORDER BY 1| +bug19862(a) +2 +3 +SELECT * FROM t11| +a +1 +2 +DROP TABLE t11, t12| +DROP FUNCTION bug19862| +drop table if exists t3| +drop database if exists mysqltest1| +create table t3 (a int)| +insert into t3 (a) values (1), (2)| +create database mysqltest1| +use mysqltest1| +drop database mysqltest1| +select database()| +database() +NULL +select * from (select 1 as a) as t1 natural join (select * from test.t3) as t2| +a +1 +use test| +drop table t3| drop table if exists t3| drop database if exists mysqltest1| create table t3 (a int)| diff --git a/mysql-test/r/strict.result b/mysql-test/r/strict.result index dd96dc6d983..4a8ca484329 100644 --- a/mysql-test/r/strict.result +++ b/mysql-test/r/strict.result @@ -5,7 +5,9 @@ select @@sql_mode; REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI,STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER DROP TABLE IF EXISTS t1; CREATE TABLE t1 (col1 date); -INSERT INTO t1 VALUES('2004-01-01'),('0000-10-31'),('2004-02-29'); +INSERT INTO t1 VALUES('2004-01-01'),('2004-02-29'); +INSERT INTO t1 VALUES('0000-10-31'); +ERROR 22007: Incorrect date value: '0000-10-31' for column 'col1' at row 1 INSERT INTO t1 VALUES('2004-0-31'); ERROR 22007: Incorrect date value: '2004-0-31' for column 'col1' at row 1 INSERT INTO t1 VALUES('2004-01-02'),('2004-0-31'); @@ -54,7 +56,6 @@ Warning 1265 Data truncated for column 'col1' at row 3 select * from t1; col1 2004-01-01 -0000-10-31 2004-02-29 2004-01-02 2004-01-03 @@ -121,7 +122,9 @@ col1 drop table t1; set @@sql_mode='ansi,traditional'; CREATE TABLE t1 (col1 datetime); -INSERT INTO t1 VALUES('2004-10-31 15:30:00'),('0000-10-31 15:30:00'),('2004-02-29 15:30:00'); +INSERT INTO t1 VALUES('2004-10-31 15:30:00'),('2004-02-29 15:30:00'); +INSERT INTO t1 VALUES('0000-10-31 15:30:00'); +ERROR 22007: Incorrect datetime value: '0000-10-31 15:30:00' for column 'col1' at row 1 INSERT INTO t1 VALUES('2004-0-31 15:30:00'); ERROR 22007: Incorrect datetime value: '2004-0-31 15:30:00' for column 'col1' at row 1 INSERT INTO t1 VALUES('2004-10-0 15:30:00'); @@ -141,7 +144,6 @@ ERROR 22007: Incorrect datetime value: '59' for column 'col1' at row 1 select * from t1; col1 2004-10-31 15:30:00 -0000-10-31 15:30:00 2004-02-29 15:30:00 drop table t1; CREATE TABLE t1 (col1 timestamp); @@ -204,6 +206,7 @@ INSERT INTO t1 (col1) VALUES (STR_TO_DATE('15.10.2004','%d.%m.%Y')); INSERT INTO t1 (col2) VALUES (STR_TO_DATE('15.10.2004 10.15','%d.%m.%Y %H.%i')); INSERT INTO t1 (col3) VALUES (STR_TO_DATE('15.10.2004 10.15','%d.%m.%Y %H.%i')); INSERT INTO t1 (col1) VALUES(STR_TO_DATE('31.10.0000 15.30','%d.%m.%Y %H.%i')); +ERROR 22007: Incorrect date value: '0000-10-31 15:30:00' for column 'col1' at row 1 INSERT INTO t1 (col1) VALUES(STR_TO_DATE('31.0.2004 15.30','%d.%m.%Y %H.%i')); ERROR 22007: Incorrect date value: '2004-00-31 15:30:00' for column 'col1' at row 1 INSERT INTO t1 (col1) VALUES(STR_TO_DATE('0.10.2004 15.30','%d.%m.%Y %H.%i')); @@ -219,6 +222,7 @@ ERROR HY000: Incorrect datetime value: '15.13.2004 15.30' for function str_to_ti INSERT INTO t1 (col1) VALUES(STR_TO_DATE('00.00.0000','%d.%m.%Y')); ERROR 22007: Incorrect date value: '0000-00-00' for column 'col1' at row 1 INSERT INTO t1 (col2) VALUES(STR_TO_DATE('31.10.0000 15.30','%d.%m.%Y %H.%i')); +ERROR 22007: Incorrect datetime value: '0000-10-31 15:30:00' for column 'col2' at row 1 INSERT INTO t1 (col2) VALUES(STR_TO_DATE('31.0.2004 15.30','%d.%m.%Y %H.%i')); ERROR 22007: Incorrect datetime value: '2004-00-31 15:30:00' for column 'col2' at row 1 INSERT INTO t1 (col2) VALUES(STR_TO_DATE('0.10.2004 15.30','%d.%m.%Y %H.%i')); @@ -255,6 +259,7 @@ INSERT INTO t1 (col1) VALUES (CAST('2004-10-15' AS DATE)); INSERT INTO t1 (col2) VALUES (CAST('2004-10-15 10:15' AS DATETIME)); INSERT INTO t1 (col3) VALUES (CAST('2004-10-15 10:15' AS DATETIME)); INSERT INTO t1 (col1) VALUES(CAST('0000-10-31' AS DATE)); +ERROR 22007: Truncated incorrect datetime value: '0000-10-31' INSERT INTO t1 (col1) VALUES(CAST('2004-10-0' AS DATE)); ERROR 22007: Incorrect date value: '2004-10-00' for column 'col1' at row 1 INSERT INTO t1 (col1) VALUES(CAST('2004-0-10' AS DATE)); @@ -262,6 +267,7 @@ ERROR 22007: Incorrect date value: '2004-00-10' for column 'col1' at row 1 INSERT INTO t1 (col1) VALUES(CAST('0000-00-00' AS DATE)); ERROR 22007: Incorrect datetime value: '0000-00-00' INSERT INTO t1 (col2) VALUES(CAST('0000-10-31 15:30' AS DATETIME)); +ERROR 22007: Truncated incorrect datetime value: '0000-10-31 15:30' INSERT INTO t1 (col2) VALUES(CAST('2004-10-0 15:30' AS DATETIME)); ERROR 22007: Incorrect datetime value: '2004-10-00 15:30:00' for column 'col2' at row 1 INSERT INTO t1 (col2) VALUES(CAST('2004-0-10 15:30' AS DATETIME)); @@ -269,7 +275,7 @@ ERROR 22007: Incorrect datetime value: '2004-00-10 15:30:00' for column 'col2' a INSERT INTO t1 (col2) VALUES(CAST('0000-00-00' AS DATETIME)); ERROR 22007: Incorrect datetime value: '0000-00-00' INSERT INTO t1 (col3) VALUES(CAST('0000-10-31 15:30' AS DATETIME)); -ERROR 22007: Incorrect datetime value: '0000-10-31 15:30:00' for column 'col3' at row 1 +ERROR 22007: Truncated incorrect datetime value: '0000-10-31 15:30' INSERT INTO t1 (col3) VALUES(CAST('2004-10-0 15:30' AS DATETIME)); ERROR 22007: Incorrect datetime value: '2004-10-00 15:30:00' for column 'col3' at row 1 INSERT INTO t1 (col3) VALUES(CAST('2004-0-10 15:30' AS DATETIME)); @@ -282,6 +288,7 @@ INSERT INTO t1 (col1) VALUES (CONVERT('2004-10-15',DATE)); INSERT INTO t1 (col2) VALUES (CONVERT('2004-10-15 10:15',DATETIME)); INSERT INTO t1 (col3) VALUES (CONVERT('2004-10-15 10:15',DATETIME)); INSERT INTO t1 (col1) VALUES(CONVERT('0000-10-31' , DATE)); +ERROR 22007: Truncated incorrect datetime value: '0000-10-31' INSERT INTO t1 (col1) VALUES(CONVERT('2004-10-0' , DATE)); ERROR 22007: Incorrect date value: '2004-10-00' for column 'col1' at row 1 INSERT INTO t1 (col1) VALUES(CONVERT('2004-0-10' , DATE)); @@ -289,6 +296,7 @@ ERROR 22007: Incorrect date value: '2004-00-10' for column 'col1' at row 1 INSERT INTO t1 (col1) VALUES(CONVERT('0000-00-00',DATE)); ERROR 22007: Incorrect datetime value: '0000-00-00' INSERT INTO t1 (col2) VALUES(CONVERT('0000-10-31 15:30',DATETIME)); +ERROR 22007: Truncated incorrect datetime value: '0000-10-31 15:30' INSERT INTO t1 (col2) VALUES(CONVERT('2004-10-0 15:30',DATETIME)); ERROR 22007: Incorrect datetime value: '2004-10-00 15:30:00' for column 'col2' at row 1 INSERT INTO t1 (col2) VALUES(CONVERT('2004-0-10 15:30',DATETIME)); @@ -296,7 +304,7 @@ ERROR 22007: Incorrect datetime value: '2004-00-10 15:30:00' for column 'col2' a INSERT INTO t1 (col2) VALUES(CONVERT('0000-00-00',DATETIME)); ERROR 22007: Incorrect datetime value: '0000-00-00' INSERT INTO t1 (col3) VALUES(CONVERT('0000-10-31 15:30',DATETIME)); -ERROR 22007: Incorrect datetime value: '0000-10-31 15:30:00' for column 'col3' at row 1 +ERROR 22007: Truncated incorrect datetime value: '0000-10-31 15:30' INSERT INTO t1 (col3) VALUES(CONVERT('2004-10-0 15:30',DATETIME)); ERROR 22007: Incorrect datetime value: '2004-10-00 15:30:00' for column 'col3' at row 1 INSERT INTO t1 (col3) VALUES(CONVERT('2004-0-10 15:30',DATETIME)); diff --git a/mysql-test/r/type_blob.result b/mysql-test/r/type_blob.result index d05e9403d11..e5adad14267 100644 --- a/mysql-test/r/type_blob.result +++ b/mysql-test/r/type_blob.result @@ -503,6 +503,8 @@ foobar boggle fish 10 drop table t1; create table t1 (id integer auto_increment unique,imagem LONGBLOB not null default ''); +Warnings: +Warning 1101 BLOB/TEXT column 'imagem' can't have a default value insert into t1 (id) values (1); select charset(load_file('../../std_data/words.dat')), @@ -788,3 +790,21 @@ NULL 616100000000 620000000000 drop table t1; +create table t1 (a text default ''); +Warnings: +Warning 1101 BLOB/TEXT column 'a' can't have a default value +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` text +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +insert into t1 values (default); +select * from t1; +a +NULL +drop table t1; +set @@sql_mode='TRADITIONAL'; +create table t1 (a text default ''); +ERROR 42000: BLOB/TEXT column 'a' can't have a default value +set @@sql_mode=''; +End of 5.0 tests diff --git a/mysql-test/r/type_datetime.result b/mysql-test/r/type_datetime.result index 6f1a45f27c5..3ae224879df 100644 --- a/mysql-test/r/type_datetime.result +++ b/mysql-test/r/type_datetime.result @@ -26,6 +26,8 @@ Table Op Msg_type Msg_text test.t1 check status OK delete from t1; insert into t1 values("000101"),("691231"),("700101"),("991231"),("00000101"),("00010101"),("99991231"),("00101000000"),("691231000000"),("700101000000"),("991231235959"),("10000101000000"),("99991231235959"),("20030100000000"),("20030000000000"); +Warnings: +Warning 1264 Out of range value adjusted for column 't' at row 5 insert into t1 values ("2003-003-03"); insert into t1 values ("20030102T131415"),("2001-01-01T01:01:01"), ("2001-1-1T1:01:01"); select * from t1; @@ -34,7 +36,7 @@ t 2069-12-31 00:00:00 1970-01-01 00:00:00 1999-12-31 00:00:00 -0000-01-01 00:00:00 +0000-00-00 00:00:00 0001-01-01 00:00:00 9999-12-31 00:00:00 2000-10-10 00:00:00 diff --git a/mysql-test/r/type_ranges.result b/mysql-test/r/type_ranges.result index 32bb6abf7ac..50867219fd8 100644 --- a/mysql-test/r/type_ranges.result +++ b/mysql-test/r/type_ranges.result @@ -38,6 +38,9 @@ KEY (ulong), KEY (ulonglong,ulong), KEY (options,flags) ); +Warnings: +Warning 1101 BLOB/TEXT column 'mediumblob_col' can't have a default value +Warning 1101 BLOB/TEXT column 'longblob_col' can't have a default value show full fields from t1; Field Type Collation Null Key Default Extra Privileges Comment auto int(5) unsigned NULL NO PRI NULL auto_increment # diff --git a/mysql-test/r/udf.result b/mysql-test/r/udf.result index 624895f71c9..a552282fece 100644 --- a/mysql-test/r/udf.result +++ b/mysql-test/r/udf.result @@ -93,6 +93,12 @@ NULL 0R FR DROP TABLE bug19904; +CREATE DEFINER=CURRENT_USER() FUNCTION should_not_parse +RETURNS STRING SONAME "should_not_parse.so"; +ERROR HY000: Incorrect usage of SONAME and DEFINER +CREATE DEFINER=someone@somewhere FUNCTION should_not_parse +RETURNS STRING SONAME "should_not_parse.so"; +ERROR HY000: Incorrect usage of SONAME and DEFINER create table t1(f1 int); insert into t1 values(1),(2); explain select myfunc_int(f1) from t1 order by 1; diff --git a/mysql-test/r/union.result b/mysql-test/r/union.result index b96f97b535c..42fbf0f6ff1 100644 --- a/mysql-test/r/union.result +++ b/mysql-test/r/union.result @@ -1318,7 +1318,7 @@ t2 CREATE TABLE `t2` ( `f5` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', `f6` varchar(1) CHARACTER SET utf8 DEFAULT NULL, `f7` text, - `f8` text CHARACTER SET utf8 + `f8` mediumtext character set utf8 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1, t2; (select avg(1)) union (select avg(1)) union (select avg(1)) union @@ -1366,3 +1366,8 @@ drop table t1, t2; (select avg(1)) union (select avg(1)) union (select avg(1)); avg(1) NULL +select _utf8'12' union select _latin1'12345'; +12 +12 +12345 +End of 5.0 tests diff --git a/mysql-test/r/user_var.result b/mysql-test/r/user_var.result index 9c3fdf0d8d2..1ff2e9948bd 100644 --- a/mysql-test/r/user_var.result +++ b/mysql-test/r/user_var.result @@ -256,3 +256,39 @@ t1 CREATE TABLE `t1` ( `@first_var` longtext ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; +set @a=18446744071710965857; +select @a; +@a +18446744071710965857 +CREATE TABLE `bigfailure` ( +`afield` BIGINT UNSIGNED NOT NULL +); +INSERT INTO `bigfailure` VALUES (18446744071710965857); +SELECT * FROM bigfailure; +afield +18446744071710965857 +select * from (SELECT afield FROM bigfailure) as b; +afield +18446744071710965857 +select * from bigfailure where afield = (SELECT afield FROM bigfailure); +afield +18446744071710965857 +select * from bigfailure where afield = 18446744071710965857; +afield +18446744071710965857 +select * from bigfailure where afield = 18446744071710965856+1; +afield +18446744071710965857 +SET @a := (SELECT afield FROM bigfailure); +SELECT @a; +@a +18446744071710965857 +SET @a := (select afield from (SELECT afield FROM bigfailure) as b); +SELECT @a; +@a +18446744071710965857 +SET @a := (select * from bigfailure where afield = (SELECT afield FROM bigfailure)); +SELECT @a; +@a +18446744071710965857 +drop table bigfailure; diff --git a/mysql-test/t/cast.test b/mysql-test/t/cast.test index b733a23f398..ed9fdfa087a 100644 --- a/mysql-test/t/cast.test +++ b/mysql-test/t/cast.test @@ -204,7 +204,19 @@ SELECT CAST(v AS DECIMAL), CAST(tt AS DECIMAL), CAST(t AS DECIMAL), CAST(mt AS DECIMAL), CAST(lt AS DECIMAL) from t1; DROP TABLE t1; -# Bug @10237 (CAST(NULL DECIMAL) crashes server) + +# +# Bug #10237 (CAST(NULL DECIMAL) crashes server) # select cast(NULL as decimal(6)) as t1; + +# +# Bug #17903: cast to char results in binary +# +set names latin1; +select hex(cast('a' as char(2) binary)); +select hex(cast('a' as binary(2))); +select hex(cast('a' as char(2) binary)); + +--echo End of 5.0 tests diff --git a/mysql-test/t/delete.test b/mysql-test/t/delete.test index 4284bd2a06d..677ffaa2860 100644 --- a/mysql-test/t/delete.test +++ b/mysql-test/t/delete.test @@ -171,3 +171,14 @@ delete t2.*,t3.* from t1,t2,t3 where t1.a=t2.a AND t2.b=t3.a and t1.b=t3.b; # This should be empty select * from t3; drop table t1,t2,t3; + +# +# Bug #8143: deleting '0000-00-00' values using IS NULL +# + +create table t1(a date not null); +insert into t1 values (0); +select * from t1 where a is null; +delete from t1 where a is null; +select count(*) from t1; +drop table t1; diff --git a/mysql-test/t/func_compress.test b/mysql-test/t/func_compress.test index 4ae749f2343..eeb5d509b94 100644 --- a/mysql-test/t/func_compress.test +++ b/mysql-test/t/func_compress.test @@ -57,3 +57,17 @@ select uncompress(a), uncompressed_length(a) from t1; drop table t1; # End of 4.1 tests + +# +# Bug #18539: uncompress(d) is null: impossible? +# +create table t1 (a varchar(32) not null); +insert into t1 values ('foo'); +explain select * from t1 where uncompress(a) is null; +select * from t1 where uncompress(a) is null; +explain select *, uncompress(a) from t1; +select *, uncompress(a) from t1; +select *, uncompress(a), uncompress(a) is null from t1; +drop table t1; + +--echo End of 5.0 tests diff --git a/mysql-test/t/func_group.test b/mysql-test/t/func_group.test index 2806ffb5ae0..3a62b9feb3a 100644 --- a/mysql-test/t/func_group.test +++ b/mysql-test/t/func_group.test @@ -682,3 +682,15 @@ SELECT SQL_NO_CACHE FROM t1 t, t2 c WHERE t.a = c.b; DROP TABLE t1,t2; + +# +# Bug #10966: Variance functions return wrong data type +# + +create table t1 select variance(0); +show create table t1; +drop table t1; +create table t1 select stddev(0); +show create table t1; +drop table t1; + diff --git a/mysql-test/t/func_time.test b/mysql-test/t/func_time.test index 7987b3f563d..c9623df2650 100644 --- a/mysql-test/t/func_time.test +++ b/mysql-test/t/func_time.test @@ -318,6 +318,37 @@ select timestampdiff(SQL_TSI_DAY, '1986-02-01', '1986-03-01') as a1, timestampdiff(SQL_TSI_DAY, '1996-02-01', '1996-03-01') as a3, timestampdiff(SQL_TSI_DAY, '2000-02-01', '2000-03-01') as a4; +# bug 16226 +SELECT TIMESTAMPDIFF(day,'2006-01-10 14:30:28','2006-01-11 14:30:27'); +SELECT TIMESTAMPDIFF(day,'2006-01-10 14:30:28','2006-01-11 14:30:28'); +SELECT TIMESTAMPDIFF(day,'2006-01-10 14:30:28','2006-01-11 14:30:29'); +SELECT TIMESTAMPDIFF(day,'2006-01-10 14:30:28','2006-01-12 14:30:27'); +SELECT TIMESTAMPDIFF(day,'2006-01-10 14:30:28','2006-01-12 14:30:28'); +SELECT TIMESTAMPDIFF(day,'2006-01-10 14:30:28','2006-01-12 14:30:29'); + +SELECT TIMESTAMPDIFF(week,'2006-01-10 14:30:28','2006-01-17 14:30:27'); +SELECT TIMESTAMPDIFF(week,'2006-01-10 14:30:28','2006-01-17 14:30:28'); +SELECT TIMESTAMPDIFF(week,'2006-01-10 14:30:28','2006-01-17 14:30:29'); +SELECT TIMESTAMPDIFF(week,'2006-01-10 14:30:28','2006-01-24 14:30:27'); +SELECT TIMESTAMPDIFF(week,'2006-01-10 14:30:28','2006-01-24 14:30:28'); +SELECT TIMESTAMPDIFF(week,'2006-01-10 14:30:28','2006-01-24 14:30:29'); + +SELECT TIMESTAMPDIFF(month,'2006-01-10 14:30:28','2006-02-10 14:30:27'); +SELECT TIMESTAMPDIFF(month,'2006-01-10 14:30:28','2006-02-10 14:30:28'); +SELECT TIMESTAMPDIFF(month,'2006-01-10 14:30:28','2006-02-10 14:30:29'); +SELECT TIMESTAMPDIFF(month,'2006-01-10 14:30:28','2006-03-10 14:30:27'); +SELECT TIMESTAMPDIFF(month,'2006-01-10 14:30:28','2006-03-10 14:30:28'); +SELECT TIMESTAMPDIFF(month,'2006-01-10 14:30:28','2006-03-10 14:30:29'); + +SELECT TIMESTAMPDIFF(year,'2006-01-10 14:30:28','2007-01-10 14:30:27'); +SELECT TIMESTAMPDIFF(year,'2006-01-10 14:30:28','2007-01-10 14:30:28'); +SELECT TIMESTAMPDIFF(year,'2006-01-10 14:30:28','2007-01-10 14:30:29'); +SELECT TIMESTAMPDIFF(year,'2006-01-10 14:30:28','2008-01-10 14:30:27'); +SELECT TIMESTAMPDIFF(year,'2006-01-10 14:30:28','2008-01-10 14:30:28'); +SELECT TIMESTAMPDIFF(year,'2006-01-10 14:30:28','2008-01-10 14:30:29'); + +# end of bug + select date_add(time,INTERVAL 1 SECOND) from t1; drop table t1; diff --git a/mysql-test/t/im_daemon_life_cycle.imtest b/mysql-test/t/im_daemon_life_cycle.imtest index 3afc36935f8..fe2345a9987 100644 --- a/mysql-test/t/im_daemon_life_cycle.imtest +++ b/mysql-test/t/im_daemon_life_cycle.imtest @@ -7,21 +7,7 @@ ########################################################################### --source include/im_check_os.inc - -########################################################################### - -# Wait for mysqld1 (guarded instance) to start. - ---exec $MYSQL_TEST_DIR/t/wait_for_process.sh $IM_MYSQLD1_PATH_PID 30 started - -# Let IM detect that mysqld1 is online. This delay should be longer than -# monitoring interval. - ---sleep 3 - -# Check that start conditions are as expected. - -SHOW INSTANCES; +--source include/im_check_env.inc ########################################################################### diff --git a/mysql-test/t/im_life_cycle.imtest b/mysql-test/t/im_life_cycle.imtest index 2cbe53a7b28..35258396415 100644 --- a/mysql-test/t/im_life_cycle.imtest +++ b/mysql-test/t/im_life_cycle.imtest @@ -7,33 +7,7 @@ ########################################################################### --source include/im_check_os.inc - -########################################################################### -# -# 1.1.1. Check that Instance Manager is able: -# - to read definitions of two mysqld-instances; -# - to start the first instance; -# - to understand 'nonguarded' option and keep the second instance down; -# -########################################################################### - ---echo ---echo -------------------------------------------------------------------- ---echo -- 1.1.1. ---echo -------------------------------------------------------------------- - -# Wait for mysqld1 (guarded instance) to start. - ---exec $MYSQL_TEST_DIR/t/wait_for_process.sh $IM_MYSQLD1_PATH_PID 30 started - -# Let IM detect that mysqld1 is online. This delay should be longer than -# monitoring interval. - ---sleep 3 - -# Check that start conditions are as expected. - -SHOW INSTANCES; +--source include/im_check_env.inc ########################################################################### # @@ -54,9 +28,10 @@ START INSTANCE mysqld2; # FIXME: START INSTANCE should be synchronous. --exec $MYSQL_TEST_DIR/t/wait_for_process.sh $IM_MYSQLD2_PATH_PID 30 started -# FIXME: SHOW INSTANCES is not deterministic unless START INSTANCE is -# synchronous. Even waiting for mysqld to start by looking at its pid file is -# not enough, because IM may not detect that mysqld has started. +# FIXME: Result of SHOW INSTANCES here is not deterministic unless START +# INSTANCE is synchronous. Even waiting for mysqld to start by looking at +# its pid file is not enough, because it is unknown when IM detects that +# mysqld has started. # SHOW INSTANCES; --connect (mysql_con,localhost,root,,mysql,$IM_MYSQLD2_PORT,$IM_MYSQLD2_SOCK) @@ -86,9 +61,10 @@ STOP INSTANCE mysqld2; # FIXME: STOP INSTANCE should be synchronous. --exec $MYSQL_TEST_DIR/t/wait_for_process.sh $IM_MYSQLD2_PATH_PID 30 stopped -# FIXME: SHOW INSTANCES is not deterministic unless START INSTANCE is -# synchronous. Even waiting for mysqld to start by looking at its pid file is -# not enough, because IM may not detect that mysqld has started. +# FIXME: Result of SHOW INSTANCES here is not deterministic unless START +# INSTANCE is synchronous. Even waiting for mysqld to start by looking at +# its pid file is not enough, because it is unknown when IM detects that +# mysqld has started. # SHOW INSTANCES; ########################################################################### @@ -114,8 +90,8 @@ START INSTANCE mysqld1; ########################################################################### # -# 1.1.5. Check that Instance Manager reports correct errors for 'STOP INSTANCE' -# command: +# 1.1.5. Check that Instance Manager reports correct errors for +# 'STOP INSTANCE' command: # - if the client tries to start unregistered instance; # - if the client tries to start already stopped instance; # - if the client submits invalid arguments; @@ -146,12 +122,10 @@ STOP INSTANCE mysqld3; --echo -- 1.1.6. --echo -------------------------------------------------------------------- -SHOW INSTANCES; - --exec $MYSQL_TEST_DIR/t/kill_n_check.sh $IM_MYSQLD1_PATH_PID restarted 30 -# Give some time to IM to detect that mysqld was restarted. It should be longer -# than monitoring interval. +# Give some time to IM to detect that mysqld was restarted. It should be +# longer than monitoring interval. --sleep 3 @@ -172,16 +146,18 @@ START INSTANCE mysqld2; # FIXME: START INSTANCE should be synchronous. --exec $MYSQL_TEST_DIR/t/wait_for_process.sh $IM_MYSQLD2_PATH_PID 30 started -# FIXME: SHOW INSTANCES is not deterministic unless START INSTANCE is -# synchronous. Even waiting for mysqld to start by looking at its pid file is -# not enough, because IM may not detect that mysqld has started. +# FIXME: Result of SHOW INSTANCES here is not deterministic unless START +# INSTANCE is synchronous. Even waiting for mysqld to start by looking at +# its pid file is not enough, because it is unknown when IM detects that +# mysqld has started. # SHOW INSTANCES; --exec $MYSQL_TEST_DIR/t/kill_n_check.sh $IM_MYSQLD2_PATH_PID killed 10 -# FIXME: SHOW INSTANCES is not deterministic unless START INSTANCE is -# synchronous. Even waiting for mysqld to start by looking at its pid file is -# not enough, because IM may not detect that mysqld has started. +# FIXME: Result of SHOW INSTANCES here is not deterministic unless START +# INSTANCE is synchronous. Even waiting for mysqld to start by looking at +# its pid file is not enough, because it is unknown when IM detects that +# mysqld has started. # SHOW INSTANCES; ########################################################################### diff --git a/mysql-test/t/im_utils.imtest b/mysql-test/t/im_utils.imtest index 47902eeba52..4c05b342af5 100644 --- a/mysql-test/t/im_utils.imtest +++ b/mysql-test/t/im_utils.imtest @@ -7,36 +7,17 @@ ########################################################################### --source include/im_check_os.inc +--source include/im_check_env.inc ########################################################################### # -# Check starting conditions. This test case assumes that: -# - two mysqld-instances are registered; -# - the first instance is online; -# - the second instance is offline; +# Check 'SHOW INSTANCE OPTIONS' command. # - -# Wait for mysqld1 (guarded instance) to start. - ---exec $MYSQL_TEST_DIR/t/wait_for_process.sh $IM_MYSQLD1_PATH_PID 30 started - -# Let IM detect that mysqld1 is online. This delay should be longer than -# monitoring interval. - ---sleep 3 - -# Check that start conditions are as expected. - -SHOW INSTANCES; - -# -# Check 'SHOW INSTANCE OPTIONS' command: -# - check that options of both offline and online instances are accessible; -# - since configuration of an mysqld-instance contains directories, we should -# completely ignore the second column (values) in order to make the test -# case produce the same results on different installations; -# TODO: ignore values of only directory-specific options. +# Since configuration of an mysqld-instance contains directories, we should +# completely ignore the second column (values) in order to make the test +# case produce the same results on different installations; +# TODO: ignore values of only directory-specific options. # --replace_column 2 VALUE diff --git a/mysql-test/t/mysql.test b/mysql-test/t/mysql.test index 98fadcfc75d..cf6f72570ff 100644 --- a/mysql-test/t/mysql.test +++ b/mysql-test/t/mysql.test @@ -77,6 +77,23 @@ drop table t1; # --exec $MYSQL -t test -e "create table b19564 (i int, s1 char(1)); insert into b19564 values (1, 'x'); insert into b19564 values (2, NULL); insert into b19564 values (3, ' '); select * from b19564 order by i; drop table b19564;" +# +# Bug#19265 describe command does not work from mysql prompt +# + +create table t1(a int, b varchar(255), c int); +--exec $MYSQL test -e "desc t1" +--exec $MYSQL test -e "desc t1\g" +drop table t1; + +--disable_parsing +# +# Bug#21042 mysql client segfaults on importing a mysqldump export +# +--error 1 +--exec $MYSQL test -e "connect verylongdatabasenamethatshouldblowthe256byteslongbufferincom_connectfunctionxkxkxkxkxkxkxkxkxkxkxkxkxkxkxkxkxkxkxkxkxkxkxkxkxkxkxkxkxkxkxkxkxkxkxkxkxkxkxkxkxkxkxkxkxkxkxkxkxkxkxkxkxendcccccccdxxxxxxxxxxxxxxxxxkskskskskkskskskskskskskskskskkskskskskkskskskskskskskskskend" 2>&1 +--enable_parsing + --echo End of 5.0 tests diff --git a/mysql-test/t/mysqlcheck.test b/mysql-test/t/mysqlcheck.test index f5eddff8816..a96dbc74591 100644 --- a/mysql-test/t/mysqlcheck.test +++ b/mysql-test/t/mysqlcheck.test @@ -19,3 +19,17 @@ CREATE SCHEMA test; --replace_result 'Table is already up to date' OK --exec $MYSQL_CHECK --analyze --optimize --databases test information_schema mysql --exec $MYSQL_CHECK --analyze --optimize information_schema schemata + +# +# Bug #16502: mysqlcheck tries to check views +# +create table t1 (a int); +create view v1 as select * from t1; +--replace_result 'Table is already up to date' OK +--exec $MYSQL_CHECK --analyze --optimize --databases test +--replace_result 'Table is already up to date' OK +--exec $MYSQL_CHECK --all-in-1 --analyze --optimize --databases test +drop view v1; +drop table t1; + +--echo End of 5.0 tests diff --git a/mysql-test/t/mysqldump.test b/mysql-test/t/mysqldump.test index c40a21e0ca0..b0cb93ccd85 100644 --- a/mysql-test/t/mysqldump.test +++ b/mysql-test/t/mysqldump.test @@ -1281,8 +1281,49 @@ use mysqldump_dbb; drop view v1; drop table t1; drop database mysqldump_dbb; +# +# Bug#21215 mysqldump creating incomplete backups without warning +# use test; +# Create user without sufficient privs to perform the requested operation +create user mysqltest_1; +create table t1(a int, b varchar(34)); + +# To get consistent output, reset the master, starts over from first log +reset master; + +# Execute mysqldump, will fail on FLUSH TABLES +--error 2 +--exec $MYSQL_DUMP --compact --master-data -u mysqltest_1 test 2>&1 + +# Execute mysqldump, will fail on FLUSH TABLES +# use --force, should no affect behaviour +--error 2 +--exec $MYSQL_DUMP --compact --force --master-data -u mysqltest_1 test 2>&1 + +# Add RELOAD grants +grant RELOAD on *.* to mysqltest_1@localhost; + +# Execute mysqldump, will fail on SHOW MASTER STATUS +--error 2 +--exec $MYSQL_DUMP --compact --master-data -u mysqltest_1 test 2>&1 + +# Execute mysqldump, will fail on SHOW MASTER STATUS. +# use --force, should not alter behaviour +--error 2 +--exec $MYSQL_DUMP --compact --force --master-data -u mysqltest_1 test 2>&1 + +# Add REPLICATION CLIENT grants +grant REPLICATION CLIENT on *.* to mysqltest_1@localhost; + +# Execute mysqldump, should now succeed +--exec $MYSQL_DUMP --compact --master-data -u mysqltest_1 test 2>&1 + +# Clean up +drop table t1; +drop user mysqltest_1; + --echo End of 5.0 tests # diff --git a/mysql-test/t/mysqlshow.test b/mysql-test/t/mysqlshow.test index 78c4ae2b531..9ed93079f57 100644 --- a/mysql-test/t/mysqlshow.test +++ b/mysql-test/t/mysqlshow.test @@ -25,3 +25,12 @@ select "---- -v -t ---------" as ""; select "---- -v -v -t ------" as ""; --exec $MYSQL_SHOW test -v -v -t DROP TABLE t1, t2; + +# +# Bug #19147: mysqlshow INFORMATION_SCHEMA does not work +# +--exec $MYSQL_SHOW information_schema +--exec $MYSQL_SHOW INFORMATION_SCHEMA +--exec $MYSQL_SHOW inf_rmation_schema + +--echo End of 5.0 tests diff --git a/mysql-test/t/mysqltest.test b/mysql-test/t/mysqltest.test index 86cfd66ae2b..04e0532851a 100644 --- a/mysql-test/t/mysqltest.test +++ b/mysql-test/t/mysqltest.test @@ -1121,6 +1121,23 @@ drop table t1; --exec test -s $MYSQLTEST_VARDIR/tmp/bug11731.out drop table t1; +# +# Bug#19890 mysqltest: "query" command is broken +# + +# It should be possible to use the command "query" to force mysqltest to +# send the command to the server although it's a builtin mysqltest command. +--error 1064 +query sleep; + +--error 1064 +--query sleep + +# Just an empty query command +--error 1065 +query ; + +--echo End of 5.0 tests # test for replace_regex --replace_regex /at/b/ @@ -1159,3 +1176,5 @@ insert into t1 values (2,4); --replace_regex /A/C/ /B/D/i /3/2/ /2/1/ select * from t1; drop table t1; + +--echo End of 5.1 tests diff --git a/mysql-test/t/ndb_basic.test b/mysql-test/t/ndb_basic.test index c7fa5aeee1e..7bbc580c9ad 100644 --- a/mysql-test/t/ndb_basic.test +++ b/mysql-test/t/ndb_basic.test @@ -713,6 +713,15 @@ select * from t1 order by f1; select * from t1 order by f2; select * from t1 order by f3; drop table t1; +# Bug#16561 Unknown ERROR msg "ERROR 1186 (HY000): Binlog closed" by perror +# + +# As long there is no error code 1186 defined by NDB +# we should get a message "Illegal ndb error code: 1186" +--error 1 +--exec $MY_PERROR --ndb 1186 2>&1 + +--echo End of 5.0 tests # # Bug #18483 Cannot create table with FK constraint @@ -727,3 +736,5 @@ CREATE TABLE t2(a VARCHAR(255) NOT NULL, CONSTRAINT pk_b_c_id PRIMARY KEY (b,c), CONSTRAINT fk_a FOREIGN KEY(a) REFERENCES t1(a))engine=ndb; drop table t1, t2; + +--echo End of 5.1 tests diff --git a/mysql-test/t/perror.test b/mysql-test/t/perror.test new file mode 100644 index 00000000000..a4b99d8aa22 --- /dev/null +++ b/mysql-test/t/perror.test @@ -0,0 +1,19 @@ +# +# Check if the variable MY_PERROR is set +# +--require r/have_perror.require +disable_query_log; +eval select LENGTH("$MY_PERROR") > 0 as "have_perror"; +enable_query_log; + +--exec $MY_PERROR 150 > /dev/null +--exec $MY_PERROR --silent 120 > /dev/null + +# +# Bug#16561 Unknown ERROR msg "ERROR 1186 (HY000): Binlog closed" by perror +# + +# Test with error code 10000 as it's a common "unknown error" +# As there is no error code defined for 10000, expect error +--error 1 +--exec $MY_PERROR 10000 2>&1 diff --git a/mysql-test/t/ps.test b/mysql-test/t/ps.test index 9165fceb85e..aa7b6ebf266 100644 --- a/mysql-test/t/ps.test +++ b/mysql-test/t/ps.test @@ -491,6 +491,7 @@ deallocate prepare stmt; drop table t1, t2; # +# # Bug#19399 "Stored Procedures 'Lost Connection' when dropping/creating # tables" # Check that multi-delete tables are also cleaned up before re-execution. @@ -538,86 +539,6 @@ SELECT FOUND_ROWS(); deallocate prepare stmt; # -# Bug#8115: equality propagation and prepared statements -# - -create table t1 (a char(3) not null, b char(3) not null, - c char(3) not null, primary key (a, b, c)); -create table t2 like t1; - -# reduced query -prepare stmt from - "select t1.a from (t1 left outer join t2 on t2.a=1 and t1.b=t2.b) - where t1.a=1"; -execute stmt; -execute stmt; -execute stmt; - -# original query -prepare stmt from -"select t1.a, t1.b, t1.c, t2.a, t2.b, t2.c from -(t1 left outer join t2 on t2.a=? and t1.b=t2.b) -left outer join t2 t3 on t3.a=? where t1.a=?"; - -set @a:=1, @b:=1, @c:=1; - -execute stmt using @a, @b, @c; -execute stmt using @a, @b, @c; -execute stmt using @a, @b, @c; - -deallocate prepare stmt; - -drop table t1,t2; - - -# -# Bug#9383: INFORMATION_SCHEMA.COLUMNS, JOIN, Crash, prepared statement -# - -eval SET @aux= "SELECT COUNT(*) - FROM INFORMATION_SCHEMA.COLUMNS A, - INFORMATION_SCHEMA.COLUMNS B - WHERE A.TABLE_SCHEMA = B.TABLE_SCHEMA - AND A.TABLE_NAME = B.TABLE_NAME - AND A.COLUMN_NAME = B.COLUMN_NAME AND - A.TABLE_NAME = 'user'"; - -let $exec_loop_count= 3; -eval prepare my_stmt from @aux; -while ($exec_loop_count) -{ - eval execute my_stmt; - dec $exec_loop_count; -} -deallocate prepare my_stmt; - -# Test CALL in prepared mode -delimiter |; ---disable_warnings -drop procedure if exists p1| -drop table if exists t1| ---enable_warnings -create table t1 (id int)| -insert into t1 values(1)| -create procedure p1(a int, b int) -begin - declare c int; - select max(id)+1 into c from t1; - insert into t1 select a+b; - insert into t1 select a-b; - insert into t1 select a-c; -end| -set @a= 3, @b= 4| -prepare stmt from "call p1(?, ?)"| -execute stmt using @a, @b| -execute stmt using @a, @b| -select * from t1| -deallocate prepare stmt| -drop procedure p1| -drop table t1| -delimiter ;| - -# # Bug#9096 "select doesn't return all matched records if prepared statements # is used" # The bug was is bad co-operation of the optimizer's algorithm which determines @@ -692,35 +613,6 @@ deallocate prepare stmt; drop table t1, t2; # -# Bug#7306 LIMIT ?, ? and also WL#1785 " Prepared statements: implement -# support for placeholders in LIMIT clause." -# Add basic test coverage for the feature. -# -create table t1 (a int); -insert into t1 (a) values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10); -prepare stmt from "select * from t1 limit ?, ?"; -set @offset=0, @limit=1; -execute stmt using @offset, @limit; -select * from t1 limit 0, 1; -set @offset=3, @limit=2; -execute stmt using @offset, @limit; -select * from t1 limit 3, 2; -prepare stmt from "select * from t1 limit ?"; -execute stmt using @limit; ---error 1235 -prepare stmt from "select * from t1 where a in (select a from t1 limit ?)"; -prepare stmt from "select * from t1 union all select * from t1 limit ?, ?"; -set @offset=9; -set @limit=2; -execute stmt using @offset, @limit; -prepare stmt from "(select * from t1 limit ?, ?) union all - (select * from t1 limit ?, ?) order by a limit ?"; -execute stmt using @offset, @limit, @offset, @limit, @limit; - -drop table t1; -deallocate prepare stmt; - -# # Bug#11060 "Server crashes on calling stored procedure with INSERT SELECT # UNION SELECT" aka "Server crashes on re-execution of prepared INSERT ... # SELECT with UNION". @@ -837,22 +729,6 @@ select ??; select ? from t1; --enable_ps_protocol drop table t1; - -# -# Bug#12651 -# (Crash on a PS including a subquery which is a select from a simple view) -# -CREATE TABLE b12651_T1(a int) ENGINE=MYISAM; -CREATE TABLE b12651_T2(b int) ENGINE=MYISAM; -CREATE VIEW b12651_V1 as SELECT b FROM b12651_T2; - -PREPARE b12651 FROM 'SELECT 1 FROM b12651_T1 WHERE a IN (SELECT b FROM b12651_V1)'; -EXECUTE b12651; - -DROP VIEW b12651_V1; -DROP TABLE b12651_T1, b12651_T2; -DEALLOCATE PREPARE b12651; - # # Bug#9359 "Prepared statements take snapshot of system vars at PREPARE # time" @@ -1087,7 +963,172 @@ select @@max_prepared_stmt_count, @@prepared_stmt_count; set global max_prepared_stmt_count= @old_max_prepared_stmt_count; --enable_ps_protocol -# End of 4.1 tests +# +# Bug#19399 "Stored Procedures 'Lost Connection' when dropping/creating +# tables" +# Check that multi-delete tables are also cleaned up before re-execution. +# +--disable_warnings +drop table if exists t1; +create temporary table if not exists t1 (a1 int); +--enable_warnings +# exact delete syntax is essential +prepare stmt from "delete t1 from t1 where (cast(a1/3 as unsigned) * 3) = a1"; +drop temporary table t1; +create temporary table if not exists t1 (a1 int); +# the server crashed on the next statement without the fix +execute stmt; +drop temporary table t1; +create temporary table if not exists t1 (a1 int); +# the problem was in memory corruption: repeat the test just in case +execute stmt; +drop temporary table t1; +create temporary table if not exists t1 (a1 int); +execute stmt; +drop temporary table t1; +deallocate prepare stmt; + +--echo End of 4.1 tests +############################# 5.0 tests start ################################ +# +# +# Bug#6102 "Server crash with prepared statement and blank after +# function name" +# ensure that stored functions are cached when preparing a statement +# before we open tables +# +create table t1 (a varchar(20)); +insert into t1 values ('foo'); +--error 1305 +prepare stmt FROM 'SELECT char_length (a) FROM t1'; +drop table t1; + +# +# Bug#8115: equality propagation and prepared statements +# + +create table t1 (a char(3) not null, b char(3) not null, + c char(3) not null, primary key (a, b, c)); +create table t2 like t1; + +# reduced query +prepare stmt from + "select t1.a from (t1 left outer join t2 on t2.a=1 and t1.b=t2.b) + where t1.a=1"; +execute stmt; +execute stmt; +execute stmt; + +# original query +prepare stmt from +"select t1.a, t1.b, t1.c, t2.a, t2.b, t2.c from +(t1 left outer join t2 on t2.a=? and t1.b=t2.b) +left outer join t2 t3 on t3.a=? where t1.a=?"; + +set @a:=1, @b:=1, @c:=1; + +execute stmt using @a, @b, @c; +execute stmt using @a, @b, @c; +execute stmt using @a, @b, @c; + +deallocate prepare stmt; + +drop table t1,t2; + + +# +# Bug#9383: INFORMATION_SCHEMA.COLUMNS, JOIN, Crash, prepared statement +# + +eval SET @aux= "SELECT COUNT(*) + FROM INFORMATION_SCHEMA.COLUMNS A, + INFORMATION_SCHEMA.COLUMNS B + WHERE A.TABLE_SCHEMA = B.TABLE_SCHEMA + AND A.TABLE_NAME = B.TABLE_NAME + AND A.COLUMN_NAME = B.COLUMN_NAME AND + A.TABLE_NAME = 'user'"; + +let $exec_loop_count= 3; +eval prepare my_stmt from @aux; +while ($exec_loop_count) +{ + eval execute my_stmt; + dec $exec_loop_count; +} +deallocate prepare my_stmt; + +# Test CALL in prepared mode +delimiter |; +--disable_warnings +drop procedure if exists p1| +drop table if exists t1| +--enable_warnings +create table t1 (id int)| +insert into t1 values(1)| +create procedure p1(a int, b int) +begin + declare c int; + select max(id)+1 into c from t1; + insert into t1 select a+b; + insert into t1 select a-b; + insert into t1 select a-c; +end| +set @a= 3, @b= 4| +prepare stmt from "call p1(?, ?)"| +execute stmt using @a, @b| +execute stmt using @a, @b| +select * from t1| +deallocate prepare stmt| +drop procedure p1| +drop table t1| +delimiter ;| + + +# +# Bug#7306 LIMIT ?, ? and also WL#1785 " Prepared statements: implement +# support for placeholders in LIMIT clause." +# Add basic test coverage for the feature. +# +create table t1 (a int); +insert into t1 (a) values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10); +prepare stmt from "select * from t1 limit ?, ?"; +set @offset=0, @limit=1; +execute stmt using @offset, @limit; +select * from t1 limit 0, 1; +set @offset=3, @limit=2; +execute stmt using @offset, @limit; +select * from t1 limit 3, 2; +prepare stmt from "select * from t1 limit ?"; +execute stmt using @limit; +--error 1235 +prepare stmt from "select * from t1 where a in (select a from t1 limit ?)"; +prepare stmt from "select * from t1 union all select * from t1 limit ?, ?"; +set @offset=9; +set @limit=2; +execute stmt using @offset, @limit; +prepare stmt from "(select * from t1 limit ?, ?) union all + (select * from t1 limit ?, ?) order by a limit ?"; +execute stmt using @offset, @limit, @offset, @limit, @limit; + +drop table t1; +deallocate prepare stmt; + +# +# Bug#12651 +# (Crash on a PS including a subquery which is a select from a simple view) +# +CREATE TABLE b12651_T1(a int) ENGINE=MYISAM; +CREATE TABLE b12651_T2(b int) ENGINE=MYISAM; +CREATE VIEW b12651_V1 as SELECT b FROM b12651_T2; + +PREPARE b12651 FROM 'SELECT 1 FROM b12651_T1 WHERE a IN (SELECT b FROM b12651_V1)'; +EXECUTE b12651; + +DROP VIEW b12651_V1; +DROP TABLE b12651_T1, b12651_T2; +DEALLOCATE PREPARE b12651; + + # # Bug #14956: ROW_COUNT() returns incorrect result after EXECUTE of prepared diff --git a/mysql-test/t/rpl_sp.test b/mysql-test/t/rpl_sp.test index f9072b5a711..22ab72df104 100644 --- a/mysql-test/t/rpl_sp.test +++ b/mysql-test/t/rpl_sp.test @@ -433,5 +433,92 @@ SELECT * FROM t1; # Cleanup connection master; DROP PROCEDURE p1; + + +# +# BUG#20438: CREATE statements for views, stored routines and triggers can be +# not replicable. +# + +--echo +--echo ---> Test for BUG#20438 + +# Prepare environment. + +--echo +--echo ---> Preparing environment... +--echo ---> connection: master +--connection master + +--disable_warnings +DROP PROCEDURE IF EXISTS p1; +DROP FUNCTION IF EXISTS f1; +--enable_warnings + +--echo +--echo ---> Synchronizing slave with master... + +--save_master_pos +--connection slave +--sync_with_master + +--echo +--echo ---> connection: master +--connection master + +# Test. + +--echo +--echo ---> Creating procedure... + +/*!50003 CREATE PROCEDURE p1() SET @a = 1 */; + +/*!50003 CREATE FUNCTION f1() RETURNS INT RETURN 0 */; + +--echo +--echo ---> Checking on master... + +SHOW CREATE PROCEDURE p1; +SHOW CREATE FUNCTION f1; + +--echo +--echo ---> Synchronizing slave with master... + +--save_master_pos +--connection slave +--sync_with_master + +--echo ---> connection: master + +--echo +--echo ---> Checking on slave... + +SHOW CREATE PROCEDURE p1; +SHOW CREATE FUNCTION f1; + +# Cleanup. + +--echo +--echo ---> connection: master +--connection master + +--echo +--echo ---> Cleaning up... + +DROP PROCEDURE p1; +DROP FUNCTION f1; + +--save_master_pos +--connection slave +--sync_with_master +--connection master + + +# cleanup +connection master; drop table t1; sync_slave_with_master; + +--echo End of 5.0 tests +--echo End of 5.1 tests + diff --git a/mysql-test/t/rpl_trigger.test b/mysql-test/t/rpl_trigger.test index 591941c19eb..3671763ee18 100644 --- a/mysql-test/t/rpl_trigger.test +++ b/mysql-test/t/rpl_trigger.test @@ -339,6 +339,98 @@ SHOW TRIGGERS; RESET MASTER; +# Restart slave. + +connection slave; +START SLAVE; + + +# +# BUG#20438: CREATE statements for views, stored routines and triggers can be +# not replicable. +# + +--echo +--echo ---> Test for BUG#20438 + +# Prepare environment. + +--echo +--echo ---> Preparing environment... +--echo ---> connection: master +--connection master + +--disable_warnings +DROP TABLE IF EXISTS t1; +DROP TABLE IF EXISTS t2; +--enable_warnings + +--echo +--echo ---> Synchronizing slave with master... + +--save_master_pos +--connection slave +--sync_with_master + +--echo +--echo ---> connection: master +--connection master + +# Test. + +--echo +--echo ---> Creating objects... + +CREATE TABLE t1(c INT); +CREATE TABLE t2(c INT); + +/*!50003 CREATE TRIGGER t1_bi BEFORE INSERT ON t1 + FOR EACH ROW + INSERT INTO t2 VALUES(NEW.c * 10) */; + +--echo +--echo ---> Inserting value... + +INSERT INTO t1 VALUES(1); + +--echo +--echo ---> Checking on master... + +SELECT * FROM t1; +SELECT * FROM t2; + +--echo +--echo ---> Synchronizing slave with master... + +--save_master_pos +--connection slave +--sync_with_master + +--echo ---> connection: master + +--echo +--echo ---> Checking on slave... + +SELECT * FROM t1; +SELECT * FROM t2; + +# Cleanup. + +--echo +--echo ---> connection: master +--connection master + +--echo +--echo ---> Cleaning up... + +DROP TABLE t1; +DROP TABLE t2; + +--save_master_pos +--connection slave +--sync_with_master +--connection master + # # End of tests diff --git a/mysql-test/t/rpl_view.test b/mysql-test/t/rpl_view.test index 0e8c7514488..687c702c79d 100644 --- a/mysql-test/t/rpl_view.test +++ b/mysql-test/t/rpl_view.test @@ -50,3 +50,86 @@ sync_slave_with_master; # Change: Commented out binlog events to work with SBR and RBR #--replace_column 2 # 5 # # show binlog events limit 1,100; + +# +# BUG#20438: CREATE statements for views, stored routines and triggers can be +# not replicable. +# + +--echo +--echo ---> Test for BUG#20438 + +# Prepare environment. + +--echo +--echo ---> Preparing environment... +--echo ---> connection: master +--connection master + +--disable_warnings +DROP TABLE IF EXISTS t1; +DROP VIEW IF EXISTS v1; +--enable_warnings + +--echo +--echo ---> Synchronizing slave with master... + +--save_master_pos +--connection slave +--sync_with_master + +--echo +--echo ---> connection: master +--connection master + +# Test. + +--echo +--echo ---> Creating objects... + +CREATE TABLE t1(c INT); + +/*!50003 CREATE VIEW v1 AS SELECT * FROM t1 */; + +--echo +--echo ---> Inserting value... + +INSERT INTO t1 VALUES(1); + +--echo +--echo ---> Checking on master... + +SELECT * FROM t1; + +--echo +--echo ---> Synchronizing slave with master... + +--save_master_pos +--connection slave +--sync_with_master + +--echo ---> connection: master + +--echo +--echo ---> Checking on slave... + +SELECT * FROM t1; + +# Cleanup. + +--echo +--echo ---> connection: master +--connection master + +--echo +--echo ---> Cleaning up... + +DROP VIEW v1; +DROP TABLE t1; + +--save_master_pos +--connection slave +--sync_with_master +--connection master + +--echo End of 5.0 tests diff --git a/mysql-test/t/show_check.test b/mysql-test/t/show_check.test index 00520df350c..e99387bf695 100644 --- a/mysql-test/t/show_check.test +++ b/mysql-test/t/show_check.test @@ -491,6 +491,17 @@ SHOW CREATE VIEW v1; DROP PROCEDURE p1; DROP VIEW v1; + +# +# Check that SHOW TABLES and SHOW COLUMNS give a error if there is no +# referenced database and table respectively. +# +--error ER_BAD_DB_ERROR +SHOW TABLES FROM no_such_database; +--error ER_NO_SUCH_TABLE +SHOW COLUMNS FROM no_such_table; + +# End of 5.0 tests. --echo End of 5.0 tests. --disable_result_log diff --git a/mysql-test/t/sp-code.test b/mysql-test/t/sp-code.test index 0a26ea644f6..72efa831059 100644 --- a/mysql-test/t/sp-code.test +++ b/mysql-test/t/sp-code.test @@ -190,3 +190,25 @@ delimiter ;// show procedure code sudoku_solve; drop procedure sudoku_solve; + + +# +# Bug#19207: Final parenthesis omitted for CREATE INDEX in Stored +# Procedure +# +# Wrong criteria was used to distinguish the case when there was no +# lookahead performed in the parser. Bug affected only statements +# ending in one-character token without any optional tail, like CREATE +# INDEX and CALL. +# +--disable_warnings +DROP PROCEDURE IF EXISTS p1; +--enable_warnings + +CREATE PROCEDURE p1() CREATE INDEX idx ON t1 (c1); +SHOW PROCEDURE CODE p1; + +DROP PROCEDURE p1; + + +--echo End of 5.0 tests. diff --git a/mysql-test/t/sp-error.test b/mysql-test/t/sp-error.test index 73a64b6feeb..e1179f36dad 100644 --- a/mysql-test/t/sp-error.test +++ b/mysql-test/t/sp-error.test @@ -1709,9 +1709,31 @@ drop function if exists bug16896; --error ER_SP_NO_AGGREGATE create aggregate function bug16896() returns int return 1; +# BUG#14702: misleading error message when syntax error in CREATE +# PROCEDURE +# +# Misleading error message was given when IF NOT EXISTS was used in +# CREATE PROCEDURE. +# +--disable_warnings +DROP PROCEDURE IF EXISTS bug14702; +--enable_warnings + +--error ER_PARSE_ERROR +CREATE IF NOT EXISTS PROCEDURE bug14702() +BEGIN +END; + +--error ER_PARSE_ERROR +CREATE PROCEDURE IF NOT EXISTS bug14702() +BEGIN +END; + + # # End of 5.0 tests # +--echo End of 5.0 tests # # Bug#16164 "Easter egg": check that SHOW AUTHORS is disabled in @@ -1743,6 +1765,7 @@ drop function if exists bug20701| create function bug20701() returns varchar(25) binary return "test"| create function bug20701() returns varchar(25) return "test"| drop function bug20701| +--echo End of 5.1 tests # # BUG#NNNN: New bug synopsis diff --git a/mysql-test/t/sp-security.test b/mysql-test/t/sp-security.test index 591e9a3ed70..0bb8c8bd48f 100644 --- a/mysql-test/t/sp-security.test +++ b/mysql-test/t/sp-security.test @@ -767,4 +767,80 @@ SELECT Host,User,Password FROM mysql.user WHERE User='user19857'; DROP USER user19857@localhost; -# End of 5.0 bugs. +--disconnect con1root +--connection default + + +# +# BUG#18630: Arguments of suid routine calculated in wrong security +# context +# +# Arguments of suid routines were calculated in definer's security +# context instead of caller's context thus creating security hole. +# +--disable_warnings +DROP TABLE IF EXISTS t1; +DROP VIEW IF EXISTS v1; +DROP FUNCTION IF EXISTS f_suid; +DROP PROCEDURE IF EXISTS p_suid; +DROP FUNCTION IF EXISTS f_evil; +--enable_warnings +DELETE FROM mysql.user WHERE user LIKE 'mysqltest\_%'; +DELETE FROM mysql.db WHERE user LIKE 'mysqltest\_%'; +DELETE FROM mysql.tables_priv WHERE user LIKE 'mysqltest\_%'; +DELETE FROM mysql.columns_priv WHERE user LIKE 'mysqltest\_%'; +FLUSH PRIVILEGES; + +CREATE TABLE t1 (i INT); +CREATE FUNCTION f_suid(i INT) RETURNS INT SQL SECURITY DEFINER RETURN 0; +CREATE PROCEDURE p_suid(IN i INT) SQL SECURITY DEFINER SET @c:= 0; + +CREATE USER mysqltest_u1@localhost; +# Thanks to this grant statement privileges of anonymous users on +# 'test' database are not applicable for mysqltest_u1@localhost. +GRANT EXECUTE ON test.* TO mysqltest_u1@localhost; + +delimiter |; +CREATE DEFINER=mysqltest_u1@localhost FUNCTION f_evil () RETURNS INT + SQL SECURITY INVOKER +BEGIN + SET @a:= CURRENT_USER(); + SET @b:= (SELECT COUNT(*) FROM t1); + RETURN @b; +END| +delimiter ;| + +CREATE SQL SECURITY INVOKER VIEW v1 AS SELECT f_evil(); + +connect (conn1, localhost, mysqltest_u1,,); + +--error ER_TABLEACCESS_DENIED_ERROR +SELECT COUNT(*) FROM t1; + +--error ER_TABLEACCESS_DENIED_ERROR +SELECT f_evil(); +SELECT @a, @b; + +--error ER_TABLEACCESS_DENIED_ERROR +SELECT f_suid(f_evil()); +SELECT @a, @b; + +--error ER_TABLEACCESS_DENIED_ERROR +CALL p_suid(f_evil()); +SELECT @a, @b; + +--error ER_TABLEACCESS_DENIED_ERROR +SELECT * FROM v1; +SELECT @a, @b; + +disconnect conn1; +connection default; + +DROP VIEW v1; +DROP FUNCTION f_evil; +DROP USER mysqltest_u1@localhost; +DROP PROCEDURE p_suid; +DROP FUNCTION f_suid; +DROP TABLE t1; + +--echo End of 5.0 tests. diff --git a/mysql-test/t/sp.test b/mysql-test/t/sp.test index f029678a66e..8f83767b883 100644 --- a/mysql-test/t/sp.test +++ b/mysql-test/t/sp.test @@ -5962,6 +5962,189 @@ drop table t3| drop procedure bug15217| +# +# BUG#21013: Performance Degrades when importing data that uses +# Trigger and Stored Procedure +# +# This is a performance and memory leak test. Run with large number +# passed to bug21013() procedure. +# +--disable_warnings +DROP PROCEDURE IF EXISTS bug21013 | +--enable_warnings + +CREATE PROCEDURE bug21013(IN lim INT) +BEGIN + DECLARE i INT DEFAULT 0; + WHILE (i < lim) DO + SET @b = LOCATE(_latin1'b', @a, 1); + SET i = i + 1; + END WHILE; +END | + +SET @a = _latin2"aaaaaaaaaa" | +CALL bug21013(10) | + +DROP PROCEDURE bug21013 | + + +# +# BUG#16211: Stored function return type for strings is ignored +# + +# Prepare: create database with fixed, pre-defined character set. + +--disable_warnings +DROP DATABASE IF EXISTS mysqltest1| +DROP DATABASE IF EXISTS mysqltest2| +--enable_warnings + +CREATE DATABASE mysqltest1 DEFAULT CHARACTER SET utf8| +CREATE DATABASE mysqltest2 DEFAULT CHARACTER SET utf8| + +# Test case: + +use mysqltest1| + +# - Create two stored functions -- with and without explicit CHARSET-clause +# for return value; + +CREATE FUNCTION bug16211_f1() RETURNS CHAR(10) + RETURN ""| + +CREATE FUNCTION bug16211_f2() RETURNS CHAR(10) CHARSET koi8r + RETURN ""| + +CREATE FUNCTION mysqltest2.bug16211_f3() RETURNS CHAR(10) + RETURN ""| + +CREATE FUNCTION mysqltest2.bug16211_f4() RETURNS CHAR(10) CHARSET koi8r + RETURN ""| + +# - Check that CHARSET-clause is specified for the second function; + +SHOW CREATE FUNCTION bug16211_f1| +SHOW CREATE FUNCTION bug16211_f2| + +SHOW CREATE FUNCTION mysqltest2.bug16211_f3| +SHOW CREATE FUNCTION mysqltest2.bug16211_f4| + +SELECT dtd_identifier +FROM INFORMATION_SCHEMA.ROUTINES +WHERE ROUTINE_SCHEMA = "mysqltest1" AND ROUTINE_NAME = "bug16211_f1"| + +SELECT dtd_identifier +FROM INFORMATION_SCHEMA.ROUTINES +WHERE ROUTINE_SCHEMA = "mysqltest1" AND ROUTINE_NAME = "bug16211_f2"| + +SELECT dtd_identifier +FROM INFORMATION_SCHEMA.ROUTINES +WHERE ROUTINE_SCHEMA = "mysqltest2" AND ROUTINE_NAME = "bug16211_f3"| + +SELECT dtd_identifier +FROM INFORMATION_SCHEMA.ROUTINES +WHERE ROUTINE_SCHEMA = "mysqltest2" AND ROUTINE_NAME = "bug16211_f4"| + +SELECT CHARSET(bug16211_f1())| +SELECT CHARSET(bug16211_f2())| + +SELECT CHARSET(mysqltest2.bug16211_f3())| +SELECT CHARSET(mysqltest2.bug16211_f4())| + +# - Alter database character set. + +ALTER DATABASE mysqltest1 CHARACTER SET cp1251| +ALTER DATABASE mysqltest2 CHARACTER SET cp1251| + +# - Check that CHARSET-clause has not changed. + +SHOW CREATE FUNCTION bug16211_f1| +SHOW CREATE FUNCTION bug16211_f2| + +SHOW CREATE FUNCTION mysqltest2.bug16211_f3| +SHOW CREATE FUNCTION mysqltest2.bug16211_f4| + +SELECT dtd_identifier +FROM INFORMATION_SCHEMA.ROUTINES +WHERE ROUTINE_SCHEMA = "mysqltest1" AND ROUTINE_NAME = "bug16211_f1"| + +SELECT dtd_identifier +FROM INFORMATION_SCHEMA.ROUTINES +WHERE ROUTINE_SCHEMA = "mysqltest1" AND ROUTINE_NAME = "bug16211_f2"| + +SELECT dtd_identifier +FROM INFORMATION_SCHEMA.ROUTINES +WHERE ROUTINE_SCHEMA = "mysqltest2" AND ROUTINE_NAME = "bug16211_f3"| + +SELECT dtd_identifier +FROM INFORMATION_SCHEMA.ROUTINES +WHERE ROUTINE_SCHEMA = "mysqltest2" AND ROUTINE_NAME = "bug16211_f4"| + +SELECT CHARSET(bug16211_f1())| +SELECT CHARSET(bug16211_f2())| + +SELECT CHARSET(mysqltest2.bug16211_f3())| +SELECT CHARSET(mysqltest2.bug16211_f4())| + +# Cleanup. + +use test| + +DROP DATABASE mysqltest1| +DROP DATABASE mysqltest2| + + +# +# BUG#16676: Database CHARSET not used for stored procedures +# + +# Prepare: create database with fixed, pre-defined character set. + +--disable_warnings +DROP DATABASE IF EXISTS mysqltest1| +--enable_warnings + +CREATE DATABASE mysqltest1 DEFAULT CHARACTER SET utf8| + +# Test case: + +use mysqltest1| + +# - Create two stored procedures -- with and without explicit CHARSET-clause; + +CREATE PROCEDURE bug16676_p1( + IN p1 CHAR(10), + INOUT p2 CHAR(10), + OUT p3 CHAR(10)) +BEGIN + SELECT CHARSET(p1), COLLATION(p1); + SELECT CHARSET(p2), COLLATION(p2); + SELECT CHARSET(p3), COLLATION(p3); +END| + +CREATE PROCEDURE bug16676_p2( + IN p1 CHAR(10) CHARSET koi8r, + INOUT p2 CHAR(10) CHARSET cp1251, + OUT p3 CHAR(10) CHARSET greek) +BEGIN + SELECT CHARSET(p1), COLLATION(p1); + SELECT CHARSET(p2), COLLATION(p2); + SELECT CHARSET(p3), COLLATION(p3); +END| + +# - Call procedures. + +SET @v2 = 'b'| +SET @v3 = 'c'| + +CALL bug16676_p1('a', @v2, @v3)| +CALL bug16676_p2('a', @v2, @v3)| + +# Cleanup. + +use test| + +DROP DATABASE mysqltest1| # Bug#21002 "Derived table not selecting from a "real" table fails in JOINs" # # A regression caused by the fix for Bug#18444: for derived tables we should @@ -6006,7 +6189,7 @@ SELECT * FROM t11| DROP TABLE t11, t12| DROP FUNCTION bug19862| - +--echo End of 5.0 tests # # BUG#NNNN: New bug synopsis # diff --git a/mysql-test/t/strict.test b/mysql-test/t/strict.test index f6a8824c841..4037e1231cd 100644 --- a/mysql-test/t/strict.test +++ b/mysql-test/t/strict.test @@ -13,7 +13,9 @@ DROP TABLE IF EXISTS t1; # Test INSERT with DATE CREATE TABLE t1 (col1 date); -INSERT INTO t1 VALUES('2004-01-01'),('0000-10-31'),('2004-02-29'); +INSERT INTO t1 VALUES('2004-01-01'),('2004-02-29'); +--error 1292 +INSERT INTO t1 VALUES('0000-10-31'); # All test cases expected to fail should return # SQLSTATE 22007 <invalid date value> @@ -97,7 +99,9 @@ set @@sql_mode='ansi,traditional'; # Test INSERT with DATETIME CREATE TABLE t1 (col1 datetime); -INSERT INTO t1 VALUES('2004-10-31 15:30:00'),('0000-10-31 15:30:00'),('2004-02-29 15:30:00'); +INSERT INTO t1 VALUES('2004-10-31 15:30:00'),('2004-02-29 15:30:00'); +--error 1292 +INSERT INTO t1 VALUES('0000-10-31 15:30:00'); # All test cases expected to fail should return # SQLSTATE 22007 <invalid datetime value> @@ -190,6 +194,7 @@ INSERT INTO t1 (col3) VALUES (STR_TO_DATE('15.10.2004 10.15','%d.%m.%Y %H.%i')); # All test cases expected to fail should return # SQLSTATE 22007 <invalid date value> +--error 1292 INSERT INTO t1 (col1) VALUES(STR_TO_DATE('31.10.0000 15.30','%d.%m.%Y %H.%i')); --error 1292 @@ -211,6 +216,7 @@ INSERT INTO t1 (col1) VALUES(STR_TO_DATE('00.00.0000','%d.%m.%Y')); # All test cases expected to fail should return # SQLSTATE 22007 <invalid datetime value> +--error 1292 INSERT INTO t1 (col2) VALUES(STR_TO_DATE('31.10.0000 15.30','%d.%m.%Y %H.%i')); --error 1292 @@ -264,6 +270,8 @@ INSERT INTO t1 (col3) VALUES (CAST('2004-10-15 10:15' AS DATETIME)); ## Test INSERT with CAST AS DATE into DATE # All test cases expected to fail should return # SQLSTATE 22007 <invalid date value> + +--error 1292 INSERT INTO t1 (col1) VALUES(CAST('0000-10-31' AS DATE)); --error 1292 @@ -290,6 +298,8 @@ INSERT INTO t1 (col1) VALUES(CAST('0000-00-00' AS DATE)); ## Test INSERT with CAST AS DATETIME into DATETIME # All test cases expected to fail should return # SQLSTATE 22007 <invalid datetime value> + +--error 1292 INSERT INTO t1 (col2) VALUES(CAST('0000-10-31 15:30' AS DATETIME)); --error 1292 @@ -356,6 +366,8 @@ INSERT INTO t1 (col3) VALUES (CONVERT('2004-10-15 10:15',DATETIME)); ## Test INSERT with CONVERT to DATE into DATE # All test cases expected to fail should return # SQLSTATE 22007 <invalid date value> + +--error 1292 INSERT INTO t1 (col1) VALUES(CONVERT('0000-10-31' , DATE)); --error 1292 @@ -381,6 +393,8 @@ INSERT INTO t1 (col1) VALUES(CONVERT('0000-00-00',DATE)); ## Test INSERT with CONVERT to DATETIME into DATETIME # All test cases expected to fail should return # SQLSTATE 22007 <invalid datetime value> + +--error 1292 INSERT INTO t1 (col2) VALUES(CONVERT('0000-10-31 15:30',DATETIME)); --error 1292 diff --git a/mysql-test/t/type_blob.test b/mysql-test/t/type_blob.test index 503d7ffc0b9..6d79dcc863b 100644 --- a/mysql-test/t/type_blob.test +++ b/mysql-test/t/type_blob.test @@ -423,3 +423,18 @@ alter table t1 modify a binary(5); select hex(a) from t1 order by a; select hex(concat(a,'\0')) as b from t1 order by concat(a,'\0'); drop table t1; + +# +# Bug #19489: Inconsistent support for DEFAULT in TEXT columns +# +create table t1 (a text default ''); +show create table t1; +insert into t1 values (default); +select * from t1; +drop table t1; +set @@sql_mode='TRADITIONAL'; +--error ER_BLOB_CANT_HAVE_DEFAULT +create table t1 (a text default ''); +set @@sql_mode=''; + +--echo End of 5.0 tests diff --git a/mysql-test/t/udf.test b/mysql-test/t/udf.test index c8843e42f87..96e559f5c05 100644 --- a/mysql-test/t/udf.test +++ b/mysql-test/t/udf.test @@ -110,6 +110,17 @@ SELECT metaphon(v) AS f FROM bug19904; DROP TABLE bug19904; # +# Bug#21269: DEFINER-clause is allowed for UDF-functions +# + +--error ER_WRONG_USAGE +CREATE DEFINER=CURRENT_USER() FUNCTION should_not_parse +RETURNS STRING SONAME "should_not_parse.so"; + +--error ER_WRONG_USAGE +CREATE DEFINER=someone@somewhere FUNCTION should_not_parse +RETURNS STRING SONAME "should_not_parse.so"; +# # Bug#19862: Sort with filesort by function evaluates function twice # create table t1(f1 int); diff --git a/mysql-test/t/union.test b/mysql-test/t/union.test index fdb5f968589..bf5c5e066f0 100644 --- a/mysql-test/t/union.test +++ b/mysql-test/t/union.test @@ -849,3 +849,10 @@ drop table t1, t2; (select avg(1)) union (select avg(1)) union (select avg(1)) union (select avg(1)) union (select avg(1)) union (select avg(1)); +# +# Bug #16881: password() and union select +# (The issue was poor handling of character set aggregation.) +# +select _utf8'12' union select _latin1'12345'; + +--echo End of 5.0 tests diff --git a/mysql-test/t/user_var.test b/mysql-test/t/user_var.test index e1b23a1782f..58c52b59a5a 100644 --- a/mysql-test/t/user_var.test +++ b/mysql-test/t/user_var.test @@ -171,3 +171,34 @@ set @first_var= cast(NULL as CHAR); create table t1 select @first_var; show create table t1; drop table t1; + +# +# Bug #7498 User variable SET saves SIGNED BIGINT as UNSIGNED BIGINT +# + +# First part, set user var to large number and select it +set @a=18446744071710965857; +select @a; + +# Second part, set user var from large number in table +# then select it +CREATE TABLE `bigfailure` ( + `afield` BIGINT UNSIGNED NOT NULL +); +INSERT INTO `bigfailure` VALUES (18446744071710965857); +SELECT * FROM bigfailure; +select * from (SELECT afield FROM bigfailure) as b; +select * from bigfailure where afield = (SELECT afield FROM bigfailure); +select * from bigfailure where afield = 18446744071710965857; +# This is fixed in 5.0, to be uncommented there +#select * from bigfailure where afield = '18446744071710965857'; +select * from bigfailure where afield = 18446744071710965856+1; + +SET @a := (SELECT afield FROM bigfailure); +SELECT @a; +SET @a := (select afield from (SELECT afield FROM bigfailure) as b); +SELECT @a; +SET @a := (select * from bigfailure where afield = (SELECT afield FROM bigfailure)); +SELECT @a; + +drop table bigfailure; |