diff options
Diffstat (limited to 'mysql-test')
53 files changed, 1236 insertions, 137 deletions
diff --git a/mysql-test/include/varchar.inc b/mysql-test/include/varchar.inc index 13b4315f2b8..70b563e871c 100644 --- a/mysql-test/include/varchar.inc +++ b/mysql-test/include/varchar.inc @@ -229,6 +229,7 @@ drop table t1; # # Bug #9489: problem with hash indexes +# Bug #10802: Index is not used if table using BDB engine on HP-UX # create table t1(a int, b varchar(12), key ba(b, a)); diff --git a/mysql-test/lib/mtr_cases.pl b/mysql-test/lib/mtr_cases.pl index 12714ddc1ad..158fd602ef8 100644 --- a/mysql-test/lib/mtr_cases.pl +++ b/mysql-test/lib/mtr_cases.pl @@ -53,21 +53,20 @@ sub collect_test_cases ($) { else { # ---------------------------------------------------------------------- - # Skip some tests listed in disabled.def + # Disable some tests listed in disabled.def # ---------------------------------------------------------------------- - my %skiplist; - my $skipfile= "$testdir/disabled.def"; - if ( open(SKIPFILE, $skipfile) ) + my %disabled; + if ( open(DISABLED, "$testdir/disabled.def" ) ) { - while ( <SKIPFILE> ) + while ( <DISABLED> ) { chomp; if ( /^\s*(\S+)\s*:\s*(.*?)\s*$/ ) { - $skiplist{$1}= $2; + $disabled{$1}= $2; } } - close SKIPFILE; + close DISABLED; } foreach my $elem ( sort readdir(TESTDIR) ) { @@ -75,7 +74,7 @@ sub collect_test_cases ($) { next if ! defined $tname; next if $::opt_do_test and ! defined mtr_match_prefix($elem,$::opt_do_test); - collect_one_test_case($testdir,$resdir,$tname,$elem,$cases,\%skiplist); + collect_one_test_case($testdir,$resdir,$tname,$elem,$cases,\%disabled); } closedir TESTDIR; } @@ -119,7 +118,7 @@ sub collect_one_test_case($$$$$$) { my $tname= shift; my $elem= shift; my $cases= shift; - my $skiplist=shift; + my $disabled=shift; my $path= "$testdir/$elem"; @@ -188,7 +187,7 @@ sub collect_one_test_case($$$$$$) { my $slave_mi_file= "$testdir/$tname.slave-mi"; my $master_sh= "$testdir/$tname-master.sh"; my $slave_sh= "$testdir/$tname-slave.sh"; - my $disabled= "$testdir/$tname.disabled"; + my $disabled_file= "$testdir/$tname.disabled"; $tinfo->{'master_opt'}= $::glob_win32 ? ["--default-time-zone=+3:00"] : []; $tinfo->{'slave_opt'}= $::glob_win32 ? ["--default-time-zone=+3:00"] : []; @@ -292,18 +291,18 @@ sub collect_one_test_case($$$$$$) { } # FIXME why this late? - if ( $skiplist->{$tname} ) + if ( $disabled->{$tname} ) { $tinfo->{'skip'}= 1; $tinfo->{'disable'}= 1; # Sub type of 'skip' - $tinfo->{'comment'}= $skiplist->{$tname} if $skiplist->{$tname}; + $tinfo->{'comment'}= $disabled->{$tname} if $disabled->{$tname}; } - if ( -f $disabled ) + if ( -f $disabled_file ) { $tinfo->{'skip'}= 1; $tinfo->{'disable'}= 1; # Sub type of 'skip' - $tinfo->{'comment'}= mtr_fromfile($disabled); + $tinfo->{'comment'}= mtr_fromfile($disabled_file); } # We can't restart a running server that may be in use diff --git a/mysql-test/lib/mtr_misc.pl b/mysql-test/lib/mtr_misc.pl index c1aab340a16..9a12d842998 100644 --- a/mysql-test/lib/mtr_misc.pl +++ b/mysql-test/lib/mtr_misc.pl @@ -7,6 +7,7 @@ use strict; sub mtr_full_hostname (); +sub mtr_short_hostname (); sub mtr_init_args ($); sub mtr_add_arg ($$); sub mtr_path_exists(@); @@ -21,6 +22,7 @@ sub mtr_exe_exists(@); # We want the fully qualified host name and hostname() may have returned # only the short name. So we use the resolver to find out. +# Note that this might fail on some platforms sub mtr_full_hostname () { @@ -35,6 +37,13 @@ sub mtr_full_hostname () { return $hostname; } +sub mtr_short_hostname () { + + my $hostname= hostname(); + $hostname =~ s/\..+$//; + return $hostname; +} + # FIXME move to own lib sub mtr_init_args ($) { diff --git a/mysql-test/lib/mtr_process.pl b/mysql-test/lib/mtr_process.pl index 1eb4f6b7c58..1f18968031c 100644 --- a/mysql-test/lib/mtr_process.pl +++ b/mysql-test/lib/mtr_process.pl @@ -186,8 +186,8 @@ sub spawn_parent_impl { if ( $mode eq 'run' or $mode eq 'test' ) { my $exit_value= -1; - my $signal_num= 0; - my $dumped_core= 0; +# my $signal_num= 0; +# my $dumped_core= 0; if ( $mode eq 'run' ) { @@ -199,9 +199,10 @@ sub spawn_parent_impl { mtr_error("$path ($pid) got lost somehow"); } - $exit_value= $? >> 8; - $signal_num= $? & 127; - $dumped_core= $? & 128; + $exit_value= $?; +# $exit_value= $? >> 8; +# $signal_num= $? & 127; +# $dumped_core= $? & 128; return $exit_value; } @@ -229,9 +230,10 @@ sub spawn_parent_impl { if ( $ret_pid == $pid ) { # We got termination of mysqltest, we are done - $exit_value= $? >> 8; - $signal_num= $? & 127; - $dumped_core= $? & 128; + $exit_value= $?; +# $exit_value= $? >> 8; +# $signal_num= $? & 127; +# $dumped_core= $? & 128; last; } @@ -473,6 +475,7 @@ sub mtr_stop_mysqld_servers ($) { } else { + # Server is dead, we remove the pidfile if any # Race, could have been removed between I tested with -f # and the unlink() below, so I better check again with -f @@ -502,10 +505,12 @@ sub mtr_stop_mysqld_servers ($) { # that for true Win32 processes, kill(0,$pid) will not return 1. # ---------------------------------------------------------------------- + start_reap_all(); # Avoid zombies + SIGNAL: foreach my $sig (15,9) { - my $retries= 10; # 10 seconds + my $retries= 20; # FIXME 20 seconds, this is silly! kill($sig, keys %mysqld_pids); while ( $retries-- and kill(0, keys %mysqld_pids) ) { @@ -514,6 +519,8 @@ sub mtr_stop_mysqld_servers ($) { } } + stop_reap_all(); # Get into control again + # ---------------------------------------------------------------------- # Now, we check if all we can find using kill(0,$pid) are dead, # and just assume the rest are. We cleanup socket and PID files. @@ -632,7 +639,8 @@ sub mtr_mysqladmin_shutdown () { $mysql_admin_pids{$pid}= 1; } - # We wait blocking, we wait for the last one anyway + # As mysqladmin is such a simple program, we trust it to terminate. + # I.e. we wait blocking, and wait wait for them all before we go on. while (keys %mysql_admin_pids) { foreach my $pid (keys %mysql_admin_pids) @@ -651,7 +659,8 @@ sub mtr_mysqladmin_shutdown () { my $timeout= 20; # 20 seconds max my $res= 1; # If we just fall through, we are done - + # in the sense that the servers don't + # listen to their ports any longer TIME: while ( $timeout-- ) { @@ -669,6 +678,8 @@ sub mtr_mysqladmin_shutdown () { last; # If we got here, we are done } + $timeout or mtr_debug("At least one server is still listening to its port"); + sleep(5) if $::glob_win32; # FIXME next startup fails if no sleep return $res; @@ -752,6 +763,7 @@ sub mtr_ping_mysqld_server () { # ############################################################################## +# FIXME check that the pidfile contains the expected pid! sub sleep_until_file_created ($$$) { my $pidfile= shift; @@ -762,7 +774,7 @@ sub sleep_until_file_created ($$$) { { if ( -r $pidfile ) { - return 1; + return $pid; } # Check if it died after the fork() was successful @@ -793,8 +805,12 @@ sub sleep_until_file_created ($$$) { # ############################################################################## +# FIXME something is wrong, we sometimes terminate with "Hangup" written +# to tty, and no STDERR output telling us why. + sub mtr_exit ($) { my $code= shift; +# cluck("Called mtr_exit()"); local $SIG{HUP} = 'IGNORE'; kill('HUP', -$$); exit($code); diff --git a/mysql-test/lib/mtr_report.pl b/mysql-test/lib/mtr_report.pl index 0af34d11a3f..b9dab6b8d32 100644 --- a/mysql-test/lib/mtr_report.pl +++ b/mysql-test/lib/mtr_report.pl @@ -10,7 +10,6 @@ sub mtr_report_test_name($); sub mtr_report_test_passed($); sub mtr_report_test_failed($); sub mtr_report_test_skipped($); -sub mtr_report_test_disabled($); sub mtr_show_failed_diff ($); sub mtr_report_stats ($); diff --git a/mysql-test/mysql-test-run.pl b/mysql-test/mysql-test-run.pl index 0bc32c9eaeb..ead23011582 100755 --- a/mysql-test/mysql-test-run.pl +++ b/mysql-test/mysql-test-run.pl @@ -341,7 +341,6 @@ sub main () { if ( ! $glob_use_running_server ) { - if ( $opt_start_dirty ) { kill_running_server(); @@ -356,7 +355,7 @@ sub main () { } } - if ( $opt_start_and_exit or $opt_start_dirty ) + if ( $opt_start_dirty ) { if ( ndbcluster_start() ) { @@ -371,16 +370,13 @@ sub main () { mtr_error("Can't start the mysqld server"); } } + elsif ( $opt_bench ) + { + run_benchmarks(shift); # Shift what? Extra arguments?! + } else { - if ( $opt_bench ) - { - run_benchmarks(shift); # Shift what? Extra arguments?! - } - else - { - run_tests(); - } + run_tests(); } mtr_exit(0); @@ -418,7 +414,7 @@ sub initial_setup () { $opt_source_dist= 1; } - $glob_hostname= mtr_full_hostname(); + $glob_hostname= mtr_short_hostname(); # 'basedir' is always parent of "mysql-test" directory $glob_mysql_test_dir= cwd(); @@ -1488,6 +1484,16 @@ sub run_testcase ($) { } # ---------------------------------------------------------------------- + # If --start-and-exit given, stop here to let user manually run tests + # ---------------------------------------------------------------------- + + if ( $opt_start_and_exit ) + { + mtr_report("\nServers started, exiting"); + exit(0); + } + + # ---------------------------------------------------------------------- # Run the test case # ---------------------------------------------------------------------- @@ -2248,7 +2254,8 @@ Misc options script-debug Debug this script itself compress Use the compressed protocol between client and server timer Show test case execution time - start-and-exit Only initiate and start the "mysqld" servers + start-and-exit Only initiate and start the "mysqld" servers, use the startup + settings for the specified test case if any start-dirty Only start the "mysqld" servers without initiation fast Don't try to cleanup from earlier runs reorder Reorder tests to get less server restarts diff --git a/mysql-test/r/ctype_cp932.result b/mysql-test/r/ctype_cp932.result index 2661ff5e3c9..08206a91b7b 100755 --- a/mysql-test/r/ctype_cp932.result +++ b/mysql-test/r/ctype_cp932.result @@ -11315,6 +11315,20 @@ DROP TABLE t1; DROP TABLE t2; DROP TABLE t3; DROP TABLE t4; +RESET MASTER; +CREATE TABLE t1(f1 blob); +PREPARE stmt1 FROM 'INSERT INTO t1 VALUES(?)'; +SET @var1= x'8300'; +EXECUTE stmt1 USING @var1; +SHOW BINLOG EVENTS FROM 98; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 98 Query 1 185 use `test`; CREATE TABLE t1(f1 blob) +master-bin.000001 185 User var 1 224 @`var1`=_binary 0x8300 COLLATE binary +master-bin.000001 224 Query 1 317 use `test`; INSERT INTO t1 VALUES(@'var1') +SELECT HEX(f1) FROM t1; +HEX(f1) +8300 +DROP table t1; SET collation_connection='cp932_japanese_ci'; create table t1 select repeat('a',4000) a; delete from t1; diff --git a/mysql-test/r/derived.result b/mysql-test/r/derived.result index 586be94301f..7c9d88acf90 100644 --- a/mysql-test/r/derived.result +++ b/mysql-test/r/derived.result @@ -111,7 +111,7 @@ a b 1 a 2 b 3 c -explain select * from (select * from t1,t2 where t1.a=t2.a) t1; +explain select * from (select t1.*, t2.a as t2a from t1,t2 where t1.a=t2.a) t1; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <derived2> system NULL NULL NULL NULL 1 2 DERIVED t2 system NULL NULL NULL NULL 1 @@ -363,3 +363,8 @@ a 3 3 drop table t1, t2, t3; +create table t1 (a int); +create table t2 (a int); +select * from (select * from t1,t2) foo; +ERROR 42S21: Duplicate column name 'a' +drop table t1,t2; diff --git a/mysql-test/r/distinct.result b/mysql-test/r/distinct.result index 3ad2b73f1d3..00436019f85 100644 --- a/mysql-test/r/distinct.result +++ b/mysql-test/r/distinct.result @@ -464,6 +464,46 @@ SELECT DISTINCT html,SUM(rout)/(SUM(rin)+1) as 'prod' FROM t1 GROUP BY rin; html prod 1 0.0000 drop table t1; +CREATE TABLE t1 (a int); +INSERT INTO t1 VALUES (1),(2),(3),(4),(5); +SELECT DISTINCT a, 1 FROM t1; +a 1 +1 1 +2 1 +3 1 +4 1 +5 1 +SELECT DISTINCT 1, a FROM t1; +1 a +1 1 +1 2 +1 3 +1 4 +1 5 +CREATE TABLE t2 (a int, b int); +INSERT INTO t2 VALUES (1,1),(2,2),(2,3),(2,4),(3,5); +SELECT DISTINCT a, b, 2 FROM t2; +a b 2 +1 1 2 +2 2 2 +2 3 2 +2 4 2 +3 5 2 +SELECT DISTINCT 2, a, b FROM t2; +2 a b +2 1 1 +2 2 2 +2 2 3 +2 2 4 +2 3 5 +SELECT DISTINCT a, 2, b FROM t2; +a 2 b +1 2 1 +2 2 2 +2 2 3 +2 2 4 +3 2 5 +DROP TABLE t1,t2; create table t1 (id int, dsc varchar(50)); insert into t1 values (1, "line number one"), (2, "line number two"), (3, "line number three"); select distinct id, IFNULL(dsc, '-') from t1; diff --git a/mysql-test/r/func_date_add.result b/mysql-test/r/func_date_add.result index 50889943b56..841d13a6ea6 100644 --- a/mysql-test/r/func_date_add.result +++ b/mysql-test/r/func_date_add.result @@ -45,3 +45,29 @@ visitor_id mts 465931136 2000-03-18 16:09:53 1092858576 2000-03-19 01:34:45 drop table t1; +set sql_mode='traditional'; +create table t1 (d date); +insert into t1 (d) select date_sub('2000-01-01', INTERVAL 2001 YEAR); +ERROR 22008: Datetime function: datetime field overflow +insert into t1 (d) select date_add('2000-01-01',interval 8000 year); +ERROR 22008: Datetime function: datetime field overflow +insert into t1 values (date_add(NULL, INTERVAL 1 DAY)); +insert into t1 values (date_add('2000-01-04', INTERVAL NULL DAY)); +set sql_mode=''; +insert into t1 (d) select date_sub('2000-01-01', INTERVAL 2001 YEAR); +Warnings: +Warning 1441 Datetime function: datetime field overflow +insert into t1 (d) select date_add('2000-01-01',interval 8000 year); +Warnings: +Warning 1441 Datetime function: datetime field overflow +insert into t1 values (date_add(NULL, INTERVAL 1 DAY)); +insert into t1 values (date_add('2000-01-04', INTERVAL NULL DAY)); +select * from t1; +d +NULL +NULL +NULL +NULL +NULL +NULL +drop table t1; diff --git a/mysql-test/r/func_misc.result b/mysql-test/r/func_misc.result index 670b8754e30..36666fc827d 100644 --- a/mysql-test/r/func_misc.result +++ b/mysql-test/r/func_misc.result @@ -70,3 +70,27 @@ select timediff(b, a) >= '00:00:03' from t1; timediff(b, a) >= '00:00:03' 1 drop table t1; +set global query_cache_size=1355776; +create table t1 (a int); +insert into t1 values (1),(1),(1); +create table t2 (a datetime default null, b datetime default null); +insert into t2 set a = now(); +select a from t1 where sleep(1); +a +update t2 set b = now() where b is null; +insert into t2 set a = now(); +select a from t1 where sleep(a); +a +update t2 set b = now() where b is null; +insert into t2 set a = now(); +select a from t1 where sleep(1); +a +update t2 set b = now() where b is null; +select timediff(b, a) >= '00:00:03' from t2; +timediff(b, a) >= '00:00:03' +1 +1 +1 +drop table t2; +drop table t1; +set global query_cache_size=default; diff --git a/mysql-test/r/func_time.result b/mysql-test/r/func_time.result index 28eb48790e0..e51dc113f97 100644 --- a/mysql-test/r/func_time.result +++ b/mysql-test/r/func_time.result @@ -247,9 +247,13 @@ date_add("1997-12-31 23:59:59",INTERVAL -100000 DAY) select date_add("1997-12-31 23:59:59",INTERVAL 100000 MONTH); date_add("1997-12-31 23:59:59",INTERVAL 100000 MONTH) NULL +Warnings: +Warning 1441 Datetime function: datetime field overflow select date_add("1997-12-31 23:59:59",INTERVAL -100000 YEAR); date_add("1997-12-31 23:59:59",INTERVAL -100000 YEAR) NULL +Warnings: +Warning 1441 Datetime function: datetime field overflow select date_add("1997-12-31 23:59:59",INTERVAL "10000:1" MINUTE_SECOND); date_add("1997-12-31 23:59:59",INTERVAL "10000:1" MINUTE_SECOND) 1998-01-07 22:40:00 @@ -301,6 +305,8 @@ NULL select date_add("9999-12-31 23:59:59",INTERVAL 1 SECOND); date_add("9999-12-31 23:59:59",INTERVAL 1 SECOND) NULL +Warnings: +Warning 1441 Datetime function: datetime field overflow select date_sub("0000-00-00 00:00:00",INTERVAL 1 SECOND); date_sub("0000-00-00 00:00:00",INTERVAL 1 SECOND) NULL @@ -375,15 +381,23 @@ SELECT "1900-01-01 00:00:00" + INTERVAL 1<<20 HOUR; SELECT "1900-01-01 00:00:00" + INTERVAL 1<<38 SECOND; "1900-01-01 00:00:00" + INTERVAL 1<<38 SECOND NULL +Warnings: +Warning 1441 Datetime function: datetime field overflow SELECT "1900-01-01 00:00:00" + INTERVAL 1<<33 MINUTE; "1900-01-01 00:00:00" + INTERVAL 1<<33 MINUTE NULL +Warnings: +Warning 1441 Datetime function: datetime field overflow SELECT "1900-01-01 00:00:00" + INTERVAL 1<<30 HOUR; "1900-01-01 00:00:00" + INTERVAL 1<<30 HOUR NULL +Warnings: +Warning 1441 Datetime function: datetime field overflow SELECT "1900-01-01 00:00:00" + INTERVAL "1000000000:214748364700" MINUTE_SECOND; "1900-01-01 00:00:00" + INTERVAL "1000000000:214748364700" MINUTE_SECOND NULL +Warnings: +Warning 1441 Datetime function: datetime field overflow create table t1 (ctime varchar(20)); insert into t1 values ('2001-01-12 12:23:40'); select ctime, hour(ctime) from t1; diff --git a/mysql-test/r/information_schema.result b/mysql-test/r/information_schema.result index e0fb15a83c5..404a20d5e9e 100644 --- a/mysql-test/r/information_schema.result +++ b/mysql-test/r/information_schema.result @@ -165,7 +165,7 @@ c varchar(64) utf8_general_ci NO select,insert,update,references select * from information_schema.COLUMNS where table_name="t1" and column_name= "a"; TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME ORDINAL_POSITION COLUMN_DEFAULT IS_NULLABLE DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE CHARACTER_SET_NAME COLLATION_NAME COLUMN_TYPE COLUMN_KEY EXTRA PRIVILEGES COLUMN_COMMENT -NULL mysqltest t1 a 1 NULL YES int NULL NULL 10 NULL NULL NULL int(11) select,insert,update,references +NULL mysqltest t1 a 1 NULL YES int NULL NULL 10 0 NULL NULL int(11) select,insert,update,references show columns from mysqltest.t1 where field like "%a%"; Field Type Null Key Default Extra a int(11) YES NULL @@ -535,7 +535,7 @@ c float(5,2) NULL NULL 5 2 d decimal(6,4) NULL NULL 6 4 e float NULL NULL 12 NULL f decimal(6,3) NULL NULL 6 3 -g int(11) NULL NULL 10 NULL +g int(11) NULL NULL 10 0 h double(10,3) NULL NULL 10 3 i double NULL NULL 22 NULL drop table t1; @@ -585,8 +585,8 @@ select TABLE_NAME,TABLE_TYPE,ENGINE from information_schema.tables where table_schema='information_schema' limit 2; TABLE_NAME TABLE_TYPE ENGINE -CHARACTER_SETS TEMPORARY MEMORY -COLLATIONS TEMPORARY MEMORY +CHARACTER_SETS SYSTEM VIEW MEMORY +COLLATIONS SYSTEM VIEW MEMORY show tables from information_schema like "T%"; Tables_in_information_schema (T%) TABLES @@ -598,10 +598,10 @@ ERROR HY000: Can't create database 'information_schema'; database exists use information_schema; show full tables like "T%"; Tables_in_information_schema (T%) Table_type -TABLES TEMPORARY -TABLE_CONSTRAINTS TEMPORARY -TABLE_PRIVILEGES TEMPORARY -TRIGGERS TEMPORARY +TABLES SYSTEM VIEW +TABLE_CONSTRAINTS SYSTEM VIEW +TABLE_PRIVILEGES SYSTEM VIEW +TRIGGERS SYSTEM VIEW create table t1(a int); ERROR 42S02: Unknown table 't1' in information_schema use test; @@ -941,11 +941,11 @@ select column_name, NUMERIC_PRECISION, NUMERIC_SCALE from information_schema.columns where table_name='t1'; column_name NUMERIC_PRECISION NUMERIC_SCALE -f1 3 NULL -f2 5 NULL -f3 7 NULL -f4 10 NULL -f5 19 NULL +f1 3 0 +f2 5 0 +f3 7 0 +f4 10 0 +f5 19 0 f6 1 NULL f7 64 NULL drop table t1; @@ -958,3 +958,16 @@ trigger_schema trigger_name test tr1 use test; drop table t1; +create table t1 (a int not null, b int); +use information_schema; +select column_name, column_default from columns +where table_schema='test' and table_name='t1'; +column_name column_default +a NULL +b NULL +use test; +show columns from t1; +Field Type Null Key Default Extra +a int(11) NO +b int(11) YES NULL +drop table t1; diff --git a/mysql-test/r/innodb.result b/mysql-test/r/innodb.result index 69620d5d527..91d72045169 100644 --- a/mysql-test/r/innodb.result +++ b/mysql-test/r/innodb.result @@ -904,7 +904,7 @@ insert into mysqltest.t3 values(1); commit; drop database mysqltest; show tables from mysqltest; -Got one of the listed errors +ERROR 42000: Unknown database 'mysqltest' set autocommit=0; create table t1 (a int not null) engine= innodb; insert into t1 values(1),(2); diff --git a/mysql-test/r/join.result b/mysql-test/r/join.result index b7a2f0ce4f4..5ea863b4bdb 100644 --- a/mysql-test/r/join.result +++ b/mysql-test/r/join.result @@ -121,6 +121,10 @@ id catid stateid countyid drop table t1, t2; create table t1 (a int primary key); insert into t1 values(1),(2); +select a from t1 as t1 left join t1 as t2 using (a) left join t1 as t3 using (a) left join t1 as t4 using (a) left join t1 as t5 using (a) left join t1 as t6 using (a) left join t1 as t7 using (a) left join t1 as t8 using (a) left join t1 as t9 using (a) left join t1 as t10 using (a) left join t1 as t11 using (a) left join t1 as t12 using (a) left join t1 as t13 using (a) left join t1 as t14 using (a) left join t1 as t15 using (a) left join t1 as t16 using (a) left join t1 as t17 using (a) left join t1 as t18 using (a) left join t1 as t19 using (a) left join t1 as t20 using (a) left join t1 as t21 using (a) left join t1 as t22 using (a) left join t1 as t23 using (a) left join t1 as t24 using (a) left join t1 as t25 using (a) left join t1 as t26 using (a) left join t1 as t27 using (a) left join t1 as t28 using (a) left join t1 as t29 using (a) left join t1 as t30 using (a) left join t1 as t31 using (a); +a +1 +2 select a from t1 as t1 left join t1 as t2 using (a) left join t1 as t3 using (a) left join t1 as t4 using (a) left join t1 as t5 using (a) left join t1 as t6 using (a) left join t1 as t7 using (a) left join t1 as t8 using (a) left join t1 as t9 using (a) left join t1 as t10 using (a) left join t1 as t11 using (a) left join t1 as t12 using (a) left join t1 as t13 using (a) left join t1 as t14 using (a) left join t1 as t15 using (a) left join t1 as t16 using (a) left join t1 as t17 using (a) left join t1 as t18 using (a) left join t1 as t19 using (a) left join t1 as t20 using (a) left join t1 as t21 using (a) left join t1 as t22 using (a) left join t1 as t23 using (a) left join t1 as t24 using (a) left join t1 as t25 using (a) left join t1 as t26 using (a) left join t1 as t27 using (a) left join t1 as t28 using (a) left join t1 as t29 using (a) left join t1 as t30 using (a) left join t1 as t31 using (a) left join t1 as t32 using (a) left join t1 as t33 using (a) left join t1 as t34 using (a) left join t1 as t35 using (a) left join t1 as t36 using (a) left join t1 as t37 using (a) left join t1 as t38 using (a) left join t1 as t39 using (a) left join t1 as t40 using (a) left join t1 as t41 using (a) left join t1 as t42 using (a) left join t1 as t43 using (a) left join t1 as t44 using (a) left join t1 as t45 using (a) left join t1 as t46 using (a) left join t1 as t47 using (a) left join t1 as t48 using (a) left join t1 as t49 using (a) left join t1 as t50 using (a) left join t1 as t51 using (a) left join t1 as t52 using (a) left join t1 as t53 using (a) left join t1 as t54 using (a) left join t1 as t55 using (a) left join t1 as t56 using (a) left join t1 as t57 using (a) left join t1 as t58 using (a) left join t1 as t59 using (a) left join t1 as t60 using (a) left join t1 as t61 using (a) left join t1 as t62 using (a) left join t1 as t63 using (a) left join t1 as t64 using (a) left join t1 as t65 using (a); ERROR HY000: Too many tables; MySQL can only use XX tables in a join drop table t1; diff --git a/mysql-test/r/metadata.result b/mysql-test/r/metadata.result index dcd32bf477b..50b0b6ae294 100644 --- a/mysql-test/r/metadata.result +++ b/mysql-test/r/metadata.result @@ -85,3 +85,14 @@ def aaa 1 1 8 20 1 N 32769 0 63 1 1 drop table t1; +create table t1 (i int); +insert into t1 values (1),(2),(3); +select * from t1 where i = 2; +drop table t1;// +affected rows: 0 +affected rows: 3 +info: Records: 3 Duplicates: 0 Warnings: 0 +i +2 +affected rows: 1 +affected rows: 0 diff --git a/mysql-test/r/ndb_alter_table.result b/mysql-test/r/ndb_alter_table.result index 6f4d84edef5..ed49e7f3943 100644 --- a/mysql-test/r/ndb_alter_table.result +++ b/mysql-test/r/ndb_alter_table.result @@ -179,8 +179,24 @@ a b c 2 two two alter table t1 drop index c; select * from t1 where b = 'two'; -ERROR HY000: Table definition has changed, please retry transaction +a b c +2 two two select * from t1 where b = 'two'; a b c 2 two two drop table t1; +create table t3 (a int primary key) engine=ndbcluster; +begin; +insert into t3 values (1); +alter table t3 rename t4; +delete from t3; +insert into t3 values (1); +commit; +select * from t3; +ERROR HY000: Can't lock file (errno: 155) +select * from t4; +a +1 +drop table t4; +show tables; +Tables_in_test diff --git a/mysql-test/r/row.result b/mysql-test/r/row.result index f7f7e3e8429..1762587415d 100644 --- a/mysql-test/r/row.result +++ b/mysql-test/r/row.result @@ -58,7 +58,7 @@ SELECT (1,2,3)=(1,NULL,3); NULL SELECT (1,2,3)=(1,NULL,0); (1,2,3)=(1,NULL,0) -NULL +0 SELECT ROW(1,2,3)=ROW(1,2,3); ROW(1,2,3)=ROW(1,2,3) 1 @@ -175,3 +175,9 @@ ROW(2,10) <=> ROW(3,4) SELECT ROW(NULL,10) <=> ROW(3,NULL); ROW(NULL,10) <=> ROW(3,NULL) 0 +SELECT ROW(1,1,1) = ROW(1,1,1) as `1`, ROW(1,1,1) = ROW(1,2,1) as `0`, ROW(1,NULL,1) = ROW(2,2,1) as `0`, ROW(1,NULL,1) = ROW(1,2,2) as `0`, ROW(1,NULL,1) = ROW(1,2,1) as `null` ; +1 0 0 0 null +1 0 0 0 NULL +select row(NULL,1)=(2,0); +row(NULL,1)=(2,0) +0 diff --git a/mysql-test/r/rpl_delete_all.result b/mysql-test/r/rpl_delete_all.result index 5ed221823e8..1aa556270c9 100644 --- a/mysql-test/r/rpl_delete_all.result +++ b/mysql-test/r/rpl_delete_all.result @@ -9,7 +9,7 @@ drop database if exists mysqltest; Warnings: Note 1008 Can't drop database 'mysqltest'; database doesn't exist show tables from mysqltest; -ERROR HY000: Can't read dir of './mysqltest/' (Errcode: X) +ERROR 42000: Unknown database 'mysqltest' create table t1 (a int); drop table if exists t1; Warnings: diff --git a/mysql-test/r/rpl_sp.result b/mysql-test/r/rpl_sp.result index 15180abe8fd..b31951e93c5 100644 --- a/mysql-test/r/rpl_sp.result +++ b/mysql-test/r/rpl_sp.result @@ -109,6 +109,7 @@ call foo4(); Got one of the listed errors show warnings; Level Code Message +Error 1142 INSERT command denied to user 'zedjzlcsjhd'@'localhost' for table 't1' Warning 1417 A routine failed and has neither NO SQL nor READS SQL DATA in its declaration and binary logging is enabled; if non-transactional tables were updated, the binary log will miss their changes call foo3(); show warnings; @@ -117,6 +118,7 @@ call foo4(); Got one of the listed errors show warnings; Level Code Message +Error 1142 INSERT command denied to user 'zedjzlcsjhd'@'localhost' for table 't1' Warning 1417 A routine failed and has neither NO SQL nor READS SQL DATA in its declaration and binary logging is enabled; if non-transactional tables were updated, the binary log will miss their changes alter procedure foo4 sql security invoker; call foo4(); diff --git a/mysql-test/r/rpl_trigger.result b/mysql-test/r/rpl_trigger.result new file mode 100644 index 00000000000..db824c9c423 --- /dev/null +++ b/mysql-test/r/rpl_trigger.result @@ -0,0 +1,108 @@ +stop slave; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; +reset master; +reset slave; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; +start slave; +create table t1 (a int auto_increment, primary key (a), b int, rand_value double not null); +create table t2 (a int auto_increment, primary key (a), b int); +create table t3 (a int auto_increment, primary key (a), name varchar(64) not null, old_a int, old_b int, rand_value double not null); +create trigger t1 before insert on t1 for each row +begin +insert into t3 values (NULL, "t1", new.a, new.b, rand()); +end| +create trigger t2 after insert on t2 for each row +begin +insert into t3 values (NULL, "t2", new.a, new.b, rand()); +end| +insert into t3 values(100,"log",0,0,0); +SET @@RAND_SEED1=658490765, @@RAND_SEED2=635893186; +insert into t1 values(1,1,rand()),(NULL,2,rand()); +insert into t2 (b) values(last_insert_id()); +insert into t2 values(3,0),(NULL,0); +insert into t2 values(NULL,0),(500,0); +select a,b, truncate(rand_value,4) from t1; +a b truncate(rand_value,4) +1 1 0.4320 +2 2 0.3055 +select * from t2; +a b +1 2 +3 0 +4 0 +5 0 +500 0 +select a,name, old_a, old_b, truncate(rand_value,4) from t3; +a name old_a old_b truncate(rand_value,4) +100 log 0 0 0.0000 +101 t1 1 1 0.3203 +102 t1 0 2 0.5666 +103 t2 1 2 0.9164 +104 t2 3 0 0.8826 +105 t2 4 0 0.6635 +106 t2 5 0 0.6699 +107 t2 500 0 0.3593 + +--- On slave -- +select a,b, truncate(rand_value,4) from t1; +a b truncate(rand_value,4) +1 1 0.4320 +2 2 0.3055 +select * from t2; +a b +1 2 +3 0 +4 0 +5 0 +500 0 +select a,name, old_a, old_b, truncate(rand_value,4) from t3; +a name old_a old_b truncate(rand_value,4) +100 log 0 0 0.0000 +101 t1 1 1 0.3203 +102 t1 0 2 0.5666 +103 t2 1 2 0.9164 +104 t2 3 0 0.8826 +105 t2 4 0 0.6635 +106 t2 5 0 0.6699 +107 t2 500 0 0.3593 +drop table t1,t2,t3; +select get_lock("bug12480",2); +get_lock("bug12480",2) +1 +create table t1 (a datetime,b datetime, c datetime); +drop function if exists bug12480; +Warnings: +Note 1305 FUNCTION bug12480 does not exist +create function bug12480() returns datetime +begin +set @a=get_lock("bug12480",2); +return now(); +end| +create trigger t1_first before insert on t1 +for each row begin +set @a=get_lock("bug12480",2); +set new.b= now(); +set new.c= bug12480(); +end +| +insert into t1 set a = now(); +select a=b && a=c from t1; +a=b && a=c +1 + +--- On slave -- +select a=b && a=c from t1; +a=b && a=c +1 +test +1 +truncate table t1; +drop trigger t1_first; +insert into t1 values ("2003-03-03","2003-03-03","2003-03-03"),(bug12480(),bug12480(),bug12480()),(now(),now(),now()); +select a=b && a=c from t1; +a=b && a=c +1 +1 +1 +drop function bug12480; +drop table t1; diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result index d3409bf8d39..ed075c8a6ad 100644 --- a/mysql-test/r/select.result +++ b/mysql-test/r/select.result @@ -2218,6 +2218,23 @@ a 1 2 3 +select * from (t1 as t2 left join t1 as t3 using (a)) right join t1 on t1.a>1; +a a +NULL 1 +1 2 +2 2 +3 2 +1 3 +2 3 +3 3 +select * from t1 right join (t1 as t2 left join t1 as t3 using (a)) on t1.a>1; +a a +2 1 +3 1 +2 2 +3 2 +2 3 +3 3 select * from (t1 as t2 left join t1 as t3 using (a)) right outer join t1 using ( a ); a 1 @@ -2739,3 +2756,12 @@ DROP TABLE t1,t2; select x'10' + 0, X'10' + 0, b'10' + 0, B'10' + 0; x'10' + 0 X'10' + 0 b'10' + 0 B'10' + 0 16 16 2 2 +create table t1 (f1 varchar(6) default NULL, f2 int(6) primary key not null); +create table t2 (f3 varchar(5) not null, f4 varchar(5) not null, UNIQUE KEY UKEY (f3,f4)); +insert into t1 values (" 2", 2); +insert into t2 values (" 2", " one "),(" 2", " two "); +select * from t1 left join t2 on f1 = f3; +f1 f2 f3 f4 + 2 2 2 one + 2 2 2 two +drop table t1,t2; diff --git a/mysql-test/r/select_safe.result b/mysql-test/r/select_safe.result index 5d458c40f34..feac9efcb13 100644 --- a/mysql-test/r/select_safe.result +++ b/mysql-test/r/select_safe.result @@ -84,7 +84,7 @@ set local max_join_size=8; select * from (select * from t1) x; ERROR 42000: The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is okay set local max_join_size=1; -select * from (select * from t1 a, t1 b) x; +select * from (select a.a as aa, b.a as ba from t1 a, t1 b) x; ERROR 42000: The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is okay set local max_join_size=1; select * from (select 1 union select 2 union select 3) x; diff --git a/mysql-test/r/show_check.result b/mysql-test/r/show_check.result index f1c536ed1da..c143c7f0f29 100644 --- a/mysql-test/r/show_check.result +++ b/mysql-test/r/show_check.result @@ -512,3 +512,48 @@ t1 CREATE TABLE `t1` ( KEY `c2` USING BTREE (`c2`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DROP TABLE t1; +CREATE TABLE txt1(a int); +CREATE TABLE tyt2(a int); +CREATE TABLE urkunde(a int); +FLUSH TABLES; +SELECT 1 FROM mysql.db, mysql.proc, mysql.user, mysql.time_zone, mysql.time_zone_name, txt1, tyt2, urkunde LIMIT 0; +1 +SHOW OPEN TABLES; +Database Table In_use Name_locked +mysql db 0 0 +test urkunde 0 0 +mysql time_zone 0 0 +mysql user 0 0 +test txt1 0 0 +mysql proc 0 0 +test tyt2 0 0 +mysql time_zone_name 0 0 +SHOW OPEN TABLES FROM mysql; +Database Table In_use Name_locked +mysql db 0 0 +mysql time_zone 0 0 +mysql user 0 0 +mysql proc 0 0 +mysql time_zone_name 0 0 +SHOW OPEN TABLES FROM mysql LIKE 'u%'; +Database Table In_use Name_locked +mysql user 0 0 +SHOW OPEN TABLES LIKE 't%'; +Database Table In_use Name_locked +mysql time_zone 0 0 +test txt1 0 0 +test tyt2 0 0 +mysql time_zone_name 0 0 +SHOW OPEN TABLES LIKE '%o%'; +Database Table In_use Name_locked +mysql time_zone 0 0 +mysql proc 0 0 +mysql time_zone_name 0 0 +FLUSH TABLES; +SHOW OPEN TABLES; +Database Table In_use Name_locked +DROP TABLE txt1; +DROP TABLE tyt2; +DROP TABLE urkunde; +SHOW TABLES FROM non_existing_database; +ERROR 42000: Unknown database 'non_existing_database' diff --git a/mysql-test/r/sp-security.result b/mysql-test/r/sp-security.result index 184978e4a0d..eb2e2ce334e 100644 --- a/mysql-test/r/sp-security.result +++ b/mysql-test/r/sp-security.result @@ -236,3 +236,18 @@ drop procedure bug7291_2; drop procedure bug7291_0; REVOKE ALL PRIVILEGES, GRANT OPTION FROM user1@localhost; drop user user1@localhost; +drop database if exists mysqltest_1; +create database mysqltest_1; +create procedure mysqltest_1.p1() +begin +select 1 from dual; +end// +grant usage on *.* to mysqltest_1@localhost; +call mysqltest_1.p1(); +ERROR 42000: execute command denied to user 'mysqltest_1'@'localhost' for routine 'mysqltest_1.p1' +call mysqltest_1.p1(); +ERROR 42000: execute command denied to user 'mysqltest_1'@'localhost' for routine 'mysqltest_1.p1' +drop procedure mysqltest_1.p1; +drop database mysqltest_1; +revoke usage on *.* from mysqltest_1@localhost; +drop user mysqltest_1@localhost; diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result index 2aea7be6c12..d1d41035475 100644 --- a/mysql-test/r/sp.result +++ b/mysql-test/r/sp.result @@ -3085,4 +3085,85 @@ column_name bug10055(t.column_name) id id data data drop function bug10055| +drop function if exists f_bug11247| +drop procedure if exists p_bug11247| +create function f_bug11247(param int) +returns int +return param + 1| +create procedure p_bug11247(lim int) +begin +declare v int default 0; +while v < lim do +set v= f_bug11247(v); +end while; +end| +call p_bug11247(10)| +drop function f_bug11247| +drop procedure p_bug11247| +drop procedure if exists bug12168| +drop table if exists t1, t2| +create table t1 (a int)| +insert into t1 values (1),(2),(3),(4)| +create table t2 (a int)| +create procedure bug12168(arg1 char(1)) +begin +declare b, c integer; +if arg1 = 'a' then +begin +declare c1 cursor for select a from t1 where a % 2; +declare continue handler for not found set b = 1; +set b = 0; +open c1; +c1_repeat: repeat +fetch c1 into c; +if (b = 1) then +leave c1_repeat; +end if; +insert into t2 values (c); +until b = 1 +end repeat; +end; +end if; +if arg1 = 'b' then +begin +declare c2 cursor for select a from t1 where not a % 2; +declare continue handler for not found set b = 1; +set b = 0; +open c2; +c2_repeat: repeat +fetch c2 into c; +if (b = 1) then +leave c2_repeat; +end if; +insert into t2 values (c); +until b = 1 +end repeat; +end; +end if; +end| +call bug12168('a')| +select * from t2| +a +1 +3 +truncate t2| +call bug12168('b')| +select * from t2| +a +2 +4 +truncate t2| +call bug12168('a')| +select * from t2| +a +1 +3 +truncate t2| +call bug12168('b')| +select * from t2| +a +2 +4 +truncate t2| +drop procedure if exists bug12168| drop table t1,t2; diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index 6c05ce10592..d3ab359702e 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -923,7 +923,7 @@ select a, (select a,b,c from t1 where t1.a=t2.a) = ROW(a,2,'a'),(select c from t a (select a,b,c from t1 where t1.a=t2.a) = ROW(a,2,'a') (select c from t1 where a=t2.a) 1 1 a 2 0 b -NULL NULL NULL +NULL 0 NULL select a, (select a,b,c from t1 where t1.a=t2.a) = ROW(a,3,'b'),(select c from t1 where a=t2.a) from t2; a (select a,b,c from t1 where t1.a=t2.a) = ROW(a,3,'b') (select c from t1 where a=t2.a) 1 0 a @@ -933,7 +933,7 @@ select a, (select a,b,c from t1 where t1.a=t2.a) = ROW(a,4,'c'),(select c from t a (select a,b,c from t1 where t1.a=t2.a) = ROW(a,4,'c') (select c from t1 where a=t2.a) 1 0 a 2 0 b -NULL NULL NULL +NULL 0 NULL drop table t1,t2; create table t1 (a int, b real, c varchar(10)); insert into t1 values (1, 1, 'a'), (2,2,'b'), (NULL, 2, 'b'); @@ -2829,6 +2829,23 @@ SELECT * FROM t1 WHERE (a,b) IN (('aaa','aaa'), ('aaa','bbb')); a b aaa aaa DROP TABLE t1; +CREATE TABLE t1 (a int); +CREATE TABLE t2 (a int, b int); +CREATE TABLE t3 (b int NOT NULL); +INSERT INTO t1 VALUES (1), (2), (3), (4); +INSERT INTO t2 VALUES (1,10), (3,30); +SELECT * FROM t2 LEFT JOIN t3 ON t2.b=t3.b +WHERE t3.b IS NOT NULL OR t2.a > 10; +a b b +SELECT * FROM t1 +WHERE t1.a NOT IN (SELECT a FROM t2 LEFT JOIN t3 ON t2.b=t3.b +WHERE t3.b IS NOT NULL OR t2.a > 10); +a +1 +2 +3 +4 +DROP TABLE t1,t2,t3; create table t1 (df decimal(5,1)); insert into t1 values(1.1); insert into t1 values(2.2); diff --git a/mysql-test/r/trigger.result b/mysql-test/r/trigger.result index 7e359d3703e..312a7a90fc9 100644 --- a/mysql-test/r/trigger.result +++ b/mysql-test/r/trigger.result @@ -664,6 +664,39 @@ end| update t1 set data = 1; update t1 set data = 2; drop table t1; +create table t1 (c1 int, c2 datetime); +create trigger tr1 before insert on t1 for each row +begin +set new.c2= '2004-04-01'; +select 'hello'; +end| +ERROR 0A000: Not allowed to return a result set from a trigger +insert into t1 (c1) values (1),(2),(3); +select * from t1; +c1 c2 +1 NULL +2 NULL +3 NULL +drop procedure if exists bug11587; +create procedure bug11587(x char(16)) +begin +select "hello"; +select "hello again"; +end| +create trigger tr1 before insert on t1 for each row +begin +call bug11587(); +set new.c2= '2004-04-02'; +end| +insert into t1 (c1) values (4),(5),(6); +ERROR 0A000: PROCEDURE test.bug11587 can't return a result set in the given context +select * from t1; +c1 c2 +1 NULL +2 NULL +3 NULL +drop procedure bug11587; +drop table t1; create table t1 (f1 integer); create table t2 (f2 integer); create trigger t1_ai after insert on t1 diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result index 2af2f06ad52..f56c6558e4f 100644 --- a/mysql-test/r/view.result +++ b/mysql-test/r/view.result @@ -6,7 +6,7 @@ create view v1 (c,d) as select a,b from t1; ERROR 42S02: Table 'test.t1' doesn't exist create temporary table t1 (a int, b int); create view v1 (c) as select b+1 from t1; -ERROR HY000: View's SELECT contains a temporary table 't1' +ERROR HY000: View's SELECT refers to a temporary table 't1' drop table t1; create table t1 (a int, b int); insert into t1 values (1,2), (1,3), (2,4), (2,5), (3,10); @@ -2097,3 +2097,21 @@ select * from v1; f1 1 drop view v1; +create table t1 (f1 int); +create table t2 (f1 int); +insert into t1 values (1); +insert into t2 values (2); +create view v1 as select * from t1 union select * from t2 union all select * from t2; +select * from v1; +f1 +1 +2 +2 +drop view v1; +drop table t1,t2; +CREATE TEMPORARY TABLE t1 (a int); +CREATE FUNCTION f1 () RETURNS int RETURN (SELECT COUNT(*) FROM t1); +CREATE VIEW v1 AS SELECT f1(); +ERROR HY000: View's SELECT refers to a temporary table 't1' +DROP FUNCTION f1; +DROP TABLE t1; diff --git a/mysql-test/t/ctype_cp932.test b/mysql-test/t/ctype_cp932.test index fed87af3adc..3d630311b3a 100644 --- a/mysql-test/t/ctype_cp932.test +++ b/mysql-test/t/ctype_cp932.test @@ -398,6 +398,27 @@ DROP TABLE t2; DROP TABLE t3; DROP TABLE t4; +# Test prepared statement with 0x8300 sequence in parameter while +# running with cp932 client character set. +RESET MASTER; +CREATE TABLE t1(f1 blob); +PREPARE stmt1 FROM 'INSERT INTO t1 VALUES(?)'; +SET @var1= x'8300'; +# TODO: Note that this doesn't actually test the code which was added for +# bug#11338 because this syntax for prepared statements causes the PS to +# be replicated differently than if we executed the PS from C or Java. +# Using this syntax, variable names are inserted into the binlog instead +# of values. The real goal of this test is to check the code that was +# added to Item_param::query_val_str() in order to do hex encoding of +# PS parameters when the client character set is cp932; +# Bug#11338 has an example java program which can be used to verify this +# code (and I have used it to test the fix) until there is some way to +# exercise this code from mysql-test-run. +EXECUTE stmt1 USING @var1; +SHOW BINLOG EVENTS FROM 98; +SELECT HEX(f1) FROM t1; +DROP table t1; +# end test for bug#11338 SET collation_connection='cp932_japanese_ci'; -- source include/ctype_filesort.inc diff --git a/mysql-test/t/derived.test b/mysql-test/t/derived.test index 8d51b4666e7..f52e12849e4 100644 --- a/mysql-test/t/derived.test +++ b/mysql-test/t/derived.test @@ -42,7 +42,7 @@ CREATE TABLE t2 (a int not null); insert into t2 values(1); select * from (select * from t1 where t1.a=(select a from t2 where t2.a=t1.a)) a; select * from (select * from t1 where t1.a=(select t2.a from t2 where t2.a=t1.a) union select t1.a, t1.b from t1) a; -explain select * from (select * from t1,t2 where t1.a=t2.a) t1; +explain select * from (select t1.*, t2.a as t2a from t1,t2 where t1.a=t2.a) t1; drop table t1, t2; create table t1(a int not null, t char(8), index(a)); disable_query_log; @@ -249,4 +249,13 @@ select * from t1 union distinct select * from t2 union all select * from t3; select * from (select * from t1 union distinct select * from t2 union all select * from t3) X; drop table t1, t2, t3; +# +# Bug #11864 non unique names are allowed in subquery +# +create table t1 (a int); +create table t2 (a int); +--error 1060 +select * from (select * from t1,t2) foo; +drop table t1,t2; + # End of 4.1 tests diff --git a/mysql-test/t/distinct.test b/mysql-test/t/distinct.test index 562926d160c..6483284633f 100644 --- a/mysql-test/t/distinct.test +++ b/mysql-test/t/distinct.test @@ -334,6 +334,22 @@ SELECT DISTINCT html,SUM(rout)/(SUM(rin)+1) as 'prod' FROM t1 GROUP BY rin; drop table t1; # +# Test cases for #12625: DISTINCT for a list with constants +# + +CREATE TABLE t1 (a int); +INSERT INTO t1 VALUES (1),(2),(3),(4),(5); +SELECT DISTINCT a, 1 FROM t1; +SELECT DISTINCT 1, a FROM t1; + +CREATE TABLE t2 (a int, b int); +INSERT INTO t2 VALUES (1,1),(2,2),(2,3),(2,4),(3,5); +SELECT DISTINCT a, b, 2 FROM t2; +SELECT DISTINCT 2, a, b FROM t2; +SELECT DISTINCT a, 2, b FROM t2; + +DROP TABLE t1,t2; + # Bug 9784 DISTINCT IFNULL truncates data # create table t1 (id int, dsc varchar(50)); diff --git a/mysql-test/t/func_date_add.test b/mysql-test/t/func_date_add.test index b768e4fec61..e01fce30577 100644 --- a/mysql-test/t/func_date_add.test +++ b/mysql-test/t/func_date_add.test @@ -41,4 +41,27 @@ select visitor_id,max(ts) as mts from t1 group by visitor_id having DATE_ADD(mts,INTERVAL 3 MONTH) < NOW(); drop table t1; +# +# Bug #10627: Invalid date turned to NULL from date_sub/date_add in +# traditional mode +# +set sql_mode='traditional'; +create table t1 (d date); +--error S22008 +insert into t1 (d) select date_sub('2000-01-01', INTERVAL 2001 YEAR); +--error S22008 +insert into t1 (d) select date_add('2000-01-01',interval 8000 year); +# No warnings/errors from the next two +insert into t1 values (date_add(NULL, INTERVAL 1 DAY)); +insert into t1 values (date_add('2000-01-04', INTERVAL NULL DAY)); +set sql_mode=''; +# These will all work now, and we'll end up with some NULL entries in the +# table and some warnings. +insert into t1 (d) select date_sub('2000-01-01', INTERVAL 2001 YEAR); +insert into t1 (d) select date_add('2000-01-01',interval 8000 year); +insert into t1 values (date_add(NULL, INTERVAL 1 DAY)); +insert into t1 values (date_add('2000-01-04', INTERVAL NULL DAY)); +select * from t1; +drop table t1; + # End of 4.1 tests diff --git a/mysql-test/t/func_misc.test b/mysql-test/t/func_misc.test index f4cbacb93bb..4a618a56483 100644 --- a/mysql-test/t/func_misc.test +++ b/mysql-test/t/func_misc.test @@ -55,3 +55,26 @@ select sleep(3); update t1 set b = now(); select timediff(b, a) >= '00:00:03' from t1; drop table t1; + +# +# Bug #12689: SLEEP() gets incorrectly cached/optimized-away +# +set global query_cache_size=1355776; +create table t1 (a int); +insert into t1 values (1),(1),(1); +create table t2 (a datetime default null, b datetime default null); +insert into t2 set a = now(); +select a from t1 where sleep(1); +update t2 set b = now() where b is null; +insert into t2 set a = now(); +select a from t1 where sleep(a); +update t2 set b = now() where b is null; +insert into t2 set a = now(); +select a from t1 where sleep(1); +update t2 set b = now() where b is null; +select timediff(b, a) >= '00:00:03' from t2; +drop table t2; +drop table t1; +set global query_cache_size=default; + +# End of 5.0 tests diff --git a/mysql-test/t/information_schema.test b/mysql-test/t/information_schema.test index 93b200b8a7c..04d53828035 100644 --- a/mysql-test/t/information_schema.test +++ b/mysql-test/t/information_schema.test @@ -615,6 +615,7 @@ show create database information_schema; # # Bug #11057 information_schema: columns table has some questionable contents +# Bug #12301 information_schema: NUMERIC_SCALE must be 0 for integer columns # create table t1(f1 LONGBLOB, f2 LONGTEXT); select column_name,data_type,CHARACTER_OCTET_LENGTH, @@ -639,3 +640,14 @@ select trigger_schema, trigger_name from triggers where trigger_name='tr1'; use test; drop table t1; + +# +# Bug#12518 COLUMN_DEFAULT has wrong value if NOT NULL is set +# +create table t1 (a int not null, b int); +use information_schema; +select column_name, column_default from columns + where table_schema='test' and table_name='t1'; +use test; +show columns from t1; +drop table t1; diff --git a/mysql-test/t/innodb.test b/mysql-test/t/innodb.test index 8d51af4f22f..3eaf408af0f 100644 --- a/mysql-test/t/innodb.test +++ b/mysql-test/t/innodb.test @@ -586,7 +586,7 @@ insert into mysqltest.t3 values(1); commit; drop database mysqltest; # Don't check error message ---error 12,12 +--error 1049 show tables from mysqltest; # diff --git a/mysql-test/t/join.test b/mysql-test/t/join.test index 06e89e6cb9d..2e82bad8abb 100644 --- a/mysql-test/t/join.test +++ b/mysql-test/t/join.test @@ -111,9 +111,7 @@ drop table t1, t2; create table t1 (a int primary key); insert into t1 values(1),(2); -# TODO: WL#2486 - the query fails in PS mode with error: -# Cross dependency found in OUTER JOIN; examine your ON conditions -#select a from t1 as t1 left join t1 as t2 using (a) left join t1 as t3 using (a) left join t1 as t4 using (a) left join t1 as t5 using (a) left join t1 as t6 using (a) left join t1 as t7 using (a) left join t1 as t8 using (a) left join t1 as t9 using (a) left join t1 as t10 using (a) left join t1 as t11 using (a) left join t1 as t12 using (a) left join t1 as t13 using (a) left join t1 as t14 using (a) left join t1 as t15 using (a) left join t1 as t16 using (a) left join t1 as t17 using (a) left join t1 as t18 using (a) left join t1 as t19 using (a) left join t1 as t20 using (a) left join t1 as t21 using (a) left join t1 as t22 using (a) left join t1 as t23 using (a) left join t1 as t24 using (a) left join t1 as t25 using (a) left join t1 as t26 using (a) left join t1 as t27 using (a) left join t1 as t28 using (a) left join t1 as t29 using (a) left join t1 as t30 using (a) left join t1 as t31 using (a); +select a from t1 as t1 left join t1 as t2 using (a) left join t1 as t3 using (a) left join t1 as t4 using (a) left join t1 as t5 using (a) left join t1 as t6 using (a) left join t1 as t7 using (a) left join t1 as t8 using (a) left join t1 as t9 using (a) left join t1 as t10 using (a) left join t1 as t11 using (a) left join t1 as t12 using (a) left join t1 as t13 using (a) left join t1 as t14 using (a) left join t1 as t15 using (a) left join t1 as t16 using (a) left join t1 as t17 using (a) left join t1 as t18 using (a) left join t1 as t19 using (a) left join t1 as t20 using (a) left join t1 as t21 using (a) left join t1 as t22 using (a) left join t1 as t23 using (a) left join t1 as t24 using (a) left join t1 as t25 using (a) left join t1 as t26 using (a) left join t1 as t27 using (a) left join t1 as t28 using (a) left join t1 as t29 using (a) left join t1 as t30 using (a) left join t1 as t31 using (a); --replace_result "31 tables" "XX tables" "61 tables" "XX tables" --error 1116 select a from t1 as t1 left join t1 as t2 using (a) left join t1 as t3 using (a) left join t1 as t4 using (a) left join t1 as t5 using (a) left join t1 as t6 using (a) left join t1 as t7 using (a) left join t1 as t8 using (a) left join t1 as t9 using (a) left join t1 as t10 using (a) left join t1 as t11 using (a) left join t1 as t12 using (a) left join t1 as t13 using (a) left join t1 as t14 using (a) left join t1 as t15 using (a) left join t1 as t16 using (a) left join t1 as t17 using (a) left join t1 as t18 using (a) left join t1 as t19 using (a) left join t1 as t20 using (a) left join t1 as t21 using (a) left join t1 as t22 using (a) left join t1 as t23 using (a) left join t1 as t24 using (a) left join t1 as t25 using (a) left join t1 as t26 using (a) left join t1 as t27 using (a) left join t1 as t28 using (a) left join t1 as t29 using (a) left join t1 as t30 using (a) left join t1 as t31 using (a) left join t1 as t32 using (a) left join t1 as t33 using (a) left join t1 as t34 using (a) left join t1 as t35 using (a) left join t1 as t36 using (a) left join t1 as t37 using (a) left join t1 as t38 using (a) left join t1 as t39 using (a) left join t1 as t40 using (a) left join t1 as t41 using (a) left join t1 as t42 using (a) left join t1 as t43 using (a) left join t1 as t44 using (a) left join t1 as t45 using (a) left join t1 as t46 using (a) left join t1 as t47 using (a) left join t1 as t48 using (a) left join t1 as t49 using (a) left join t1 as t50 using (a) left join t1 as t51 using (a) left join t1 as t52 using (a) left join t1 as t53 using (a) left join t1 as t54 using (a) left join t1 as t55 using (a) left join t1 as t56 using (a) left join t1 as t57 using (a) left join t1 as t58 using (a) left join t1 as t59 using (a) left join t1 as t60 using (a) left join t1 as t61 using (a) left join t1 as t62 using (a) left join t1 as t63 using (a) left join t1 as t64 using (a) left join t1 as t65 using (a); diff --git a/mysql-test/t/kill.test b/mysql-test/t/kill.test index 09ad3f59c10..3503d5fde1d 100644 --- a/mysql-test/t/kill.test +++ b/mysql-test/t/kill.test @@ -15,6 +15,7 @@ connection con1; drop table if exists t1; --enable_warnings +--disable_reconnect create table t1 (kill_id int); insert into t1 values(connection_id()); @@ -25,7 +26,6 @@ kill @id; connection con1; ---disable_reconnect # this statement should fail --error 2006,2013 select 1; diff --git a/mysql-test/t/metadata.test b/mysql-test/t/metadata.test index b4edd15f5ef..65338448555 100644 --- a/mysql-test/t/metadata.test +++ b/mysql-test/t/metadata.test @@ -49,4 +49,16 @@ drop table t1; --disable_metadata +# +# Bug #11688: Bad mysql_info() results in multi-results +# +--enable_info +delimiter //; +create table t1 (i int); +insert into t1 values (1),(2),(3); +select * from t1 where i = 2; +drop table t1;// +delimiter ;// +--disable_info + # End of 4.1 tests diff --git a/mysql-test/t/ndb_alter_table.test b/mysql-test/t/ndb_alter_table.test index bfc278d709b..0f7b0bb7edc 100644 --- a/mysql-test/t/ndb_alter_table.test +++ b/mysql-test/t/ndb_alter_table.test @@ -151,7 +151,6 @@ select * from t1 where b = 'two'; connection server1; alter table t1 drop index c; connection server2; ---error 1412 select * from t1 where b = 'two'; select * from t1 where b = 'two'; connection server1; @@ -180,4 +179,29 @@ drop table t1; #select count(*) from t2; #drop table t2; +connection server1; +create table t3 (a int primary key) engine=ndbcluster; + +connection server2; +begin; +insert into t3 values (1); + +connection server1; +alter table t3 rename t4; + +connection server2; +# This should work as transaction is ongoing... +delete from t3; +insert into t3 values (1); +commit; + +# This should fail as its a new transaction +--error 1015 +select * from t3; +select * from t4; +drop table t4; +show tables; +connection server1; + # End of 4.1 tests + diff --git a/mysql-test/t/ndb_config.test b/mysql-test/t/ndb_config.test index 66287bf6d29..ab3063af672 100644 --- a/mysql-test/t/ndb_config.test +++ b/mysql-test/t/ndb_config.test @@ -1,4 +1,5 @@ -- source include/have_ndb.inc +-- source include/ndb_default_cluster.inc -- source include/not_embedded.inc --exec $NDB_TOOLS_DIR/ndb_config --no-defaults --query=type,nodeid,host 2> /dev/null diff --git a/mysql-test/t/row.test b/mysql-test/t/row.test index 4becef1c2b7..6301cc0f584 100644 --- a/mysql-test/t/row.test +++ b/mysql-test/t/row.test @@ -86,3 +86,9 @@ SELECT ROW(2,10) <=> ROW(3,4); SELECT ROW(NULL,10) <=> ROW(3,NULL); # End of 4.1 tests + +# +# Correct NULL handling in row comporison (BUG#12509) +# +SELECT ROW(1,1,1) = ROW(1,1,1) as `1`, ROW(1,1,1) = ROW(1,2,1) as `0`, ROW(1,NULL,1) = ROW(2,2,1) as `0`, ROW(1,NULL,1) = ROW(1,2,2) as `0`, ROW(1,NULL,1) = ROW(1,2,1) as `null` ; +select row(NULL,1)=(2,0); diff --git a/mysql-test/t/rpl_delete_all.test b/mysql-test/t/rpl_delete_all.test index db33ee3bb86..e0c0757bbc2 100644 --- a/mysql-test/t/rpl_delete_all.test +++ b/mysql-test/t/rpl_delete_all.test @@ -7,7 +7,7 @@ drop database if exists mysqltest; sync_slave_with_master; # can't read dir --replace_result "Errcode: 1" "Errcode: X" "Errcode: 2" "Errcode: X" \\ / ---error 12 +--error 1049 show tables from mysqltest; connection slave; diff --git a/mysql-test/t/rpl_trigger.test b/mysql-test/t/rpl_trigger.test new file mode 100644 index 00000000000..715222f0314 --- /dev/null +++ b/mysql-test/t/rpl_trigger.test @@ -0,0 +1,118 @@ +# +# Test of triggers with replication +# + +source include/master-slave.inc; + +# +# #12482: Triggers has side effects with auto_increment values +# + +create table t1 (a int auto_increment, primary key (a), b int, rand_value double not null); +create table t2 (a int auto_increment, primary key (a), b int); +create table t3 (a int auto_increment, primary key (a), name varchar(64) not null, old_a int, old_b int, rand_value double not null); + +delimiter |; +create trigger t1 before insert on t1 for each row +begin + insert into t3 values (NULL, "t1", new.a, new.b, rand()); +end| + +create trigger t2 after insert on t2 for each row +begin + insert into t3 values (NULL, "t2", new.a, new.b, rand()); +end| +delimiter ;| + +insert into t3 values(100,"log",0,0,0); + +# Ensure we always have same random numbers +SET @@RAND_SEED1=658490765, @@RAND_SEED2=635893186; + +# Emulate that we have rows 2-9 deleted on the slave +insert into t1 values(1,1,rand()),(NULL,2,rand()); +insert into t2 (b) values(last_insert_id()); +insert into t2 values(3,0),(NULL,0); +insert into t2 values(NULL,0),(500,0); + +select a,b, truncate(rand_value,4) from t1; +select * from t2; +select a,name, old_a, old_b, truncate(rand_value,4) from t3; +save_master_pos; +connection slave; +sync_with_master; +--disable_query_log +select "--- On slave --" as ""; +--enable_query_log +select a,b, truncate(rand_value,4) from t1; +select * from t2; +select a,name, old_a, old_b, truncate(rand_value,4) from t3; +connection master; +drop table t1,t2,t3; + +# +# #12480: NOW() is not constant in a trigger +# #12481: Using NOW() in a stored function breaks statement based replication +# + +# Start by getting a lock on 'bug12480' to be able to use get_lock() as sleep() +connect (con2,localhost,root,,); +connection con2; +select get_lock("bug12480",2); +connection default; + +create table t1 (a datetime,b datetime, c datetime); +--ignore_warnings +drop function if exists bug12480; +--enable_warnings + +delimiter |; + +create function bug12480() returns datetime +begin + set @a=get_lock("bug12480",2); + return now(); +end| + +create trigger t1_first before insert on t1 +for each row begin + set @a=get_lock("bug12480",2); + set new.b= now(); + set new.c= bug12480(); +end +| + +delimiter ;| +insert into t1 set a = now(); +select a=b && a=c from t1; +let $time=`select a from t1`; + +save_master_pos; +connection slave; +sync_with_master; +--disable_query_log +select "--- On slave --" as ""; +--enable_query_log +select a=b && a=c from t1; +--disable_query_log +eval select a='$time' as 'test' from t1; +--enable_query_log + +connection master; +disconnect con2; + +truncate table t1; +drop trigger t1_first; + +insert into t1 values ("2003-03-03","2003-03-03","2003-03-03"),(bug12480(),bug12480(),bug12480()),(now(),now(),now()); +select a=b && a=c from t1; + +drop function bug12480; +drop table t1; + +# +# End of test +# +save_master_pos; +connection slave; +sync_with_master; diff --git a/mysql-test/t/select.test b/mysql-test/t/select.test index 1de0831ad84..b8975749fd6 100644 --- a/mysql-test/t/select.test +++ b/mysql-test/t/select.test @@ -1850,10 +1850,8 @@ select * from t1 left join (t1 as t2 left join t1 as t3 using (a)) using ( a ); select * from (t1 as t2 left join t1 as t3 using (a)) natural left join t1; select * from t1 natural left join (t1 as t2 left join t1 as t3 using (a)); # right join on -# TODO: WL#2486 - there is a problem in the order of tables in RIGHT JOIN -# check how we set next_name_resolution_table -# select * from (t1 as t2 left join t1 as t3 using (a)) right join t1 on t1.a>1; -# select * from t1 right join (t1 as t2 left join t1 as t3 using (a)) on t1.a>1; +select * from (t1 as t2 left join t1 as t3 using (a)) right join t1 on t1.a>1; +select * from t1 right join (t1 as t2 left join t1 as t3 using (a)) on t1.a>1; # right [outer] joing using select * from (t1 as t2 left join t1 as t3 using (a)) right outer join t1 using ( a ); select * from t1 right outer join (t1 as t2 left join t1 as t3 using (a)) using ( a ); @@ -2350,3 +2348,13 @@ DROP TABLE t1,t2; # select x'10' + 0, X'10' + 0, b'10' + 0, B'10' + 0; + +# +# Bug #11398 Bug in field_conv() results in wrong result of join with index +# +create table t1 (f1 varchar(6) default NULL, f2 int(6) primary key not null); +create table t2 (f3 varchar(5) not null, f4 varchar(5) not null, UNIQUE KEY UKEY (f3,f4)); +insert into t1 values (" 2", 2); +insert into t2 values (" 2", " one "),(" 2", " two "); +select * from t1 left join t2 on f1 = f3; +drop table t1,t2; diff --git a/mysql-test/t/select_safe.test b/mysql-test/t/select_safe.test index 1da700c9adf..481779e76d7 100644 --- a/mysql-test/t/select_safe.test +++ b/mysql-test/t/select_safe.test @@ -78,7 +78,7 @@ select * from (select * from t1) x; set local max_join_size=1; --error 1104 -select * from (select * from t1 a, t1 b) x; +select * from (select a.a as aa, b.a as ba from t1 a, t1 b) x; set local max_join_size=1; --error 1104 diff --git a/mysql-test/t/show_check.test b/mysql-test/t/show_check.test index efe3504ad7d..b9fc991dc80 100644 --- a/mysql-test/t/show_check.test +++ b/mysql-test/t/show_check.test @@ -387,3 +387,26 @@ SHOW CREATE TABLE t1; DROP TABLE t1; # End of 4.1 tests +# +# BUG 12183 - SHOW OPEN TABLES behavior doesn't match grammar +# First we close all open tables with FLUSH tables and then we open some. +CREATE TABLE txt1(a int); +CREATE TABLE tyt2(a int); +CREATE TABLE urkunde(a int); +FLUSH TABLES; +SELECT 1 FROM mysql.db, mysql.proc, mysql.user, mysql.time_zone, mysql.time_zone_name, txt1, tyt2, urkunde LIMIT 0; +SHOW OPEN TABLES; +SHOW OPEN TABLES FROM mysql; +SHOW OPEN TABLES FROM mysql LIKE 'u%'; +SHOW OPEN TABLES LIKE 't%'; +SHOW OPEN TABLES LIKE '%o%'; +FLUSH TABLES; +SHOW OPEN TABLES; +DROP TABLE txt1; +DROP TABLE tyt2; +DROP TABLE urkunde; +# +# BUG #12591 (SHOW TABLES FROM dbname produces wrong error message) +# +--error 1049 +SHOW TABLES FROM non_existing_database; diff --git a/mysql-test/t/sp-error.test b/mysql-test/t/sp-error.test index b9abdb1b74a..f68ea1b31a3 100644 --- a/mysql-test/t/sp-error.test +++ b/mysql-test/t/sp-error.test @@ -840,19 +840,19 @@ end| # # Some things are caught when parsing ---error ER_SP_NO_RETSET_IN_FUNC +--error ER_SP_NO_RETSET create function bug8408() returns int begin select * from t1; return 0; end| ---error ER_SP_NO_RETSET_IN_FUNC +--error ER_SP_NO_RETSET create function bug8408() returns int begin show warnings; return 0; end| ---error ER_SP_NO_RETSET_IN_FUNC +--error ER_SP_NO_RETSET create function bug8408(a int) returns int begin declare b int; diff --git a/mysql-test/t/sp-security.test b/mysql-test/t/sp-security.test index 15fcba5ebe9..6f1332f80d5 100644 --- a/mysql-test/t/sp-security.test +++ b/mysql-test/t/sp-security.test @@ -371,3 +371,45 @@ drop procedure bug7291_0; disconnect user1; REVOKE ALL PRIVILEGES, GRANT OPTION FROM user1@localhost; drop user user1@localhost; + +# +# Bug #12318: Wrong error message when accessing an inaccessible stored +# procedure in another database when the current database is +# information_schema. +# + +--disable_warnings +drop database if exists mysqltest_1; +--enable_warnings + +create database mysqltest_1; +delimiter //; +create procedure mysqltest_1.p1() +begin + select 1 from dual; +end// +delimiter ;// + +grant usage on *.* to mysqltest_1@localhost; + +connect (n1,localhost,mysqltest_1,,information_schema,$MASTER_MYPORT,$MASTER_MYSOCK); +connection n1; +--error 1370 +call mysqltest_1.p1(); +disconnect n1; +# Test also without a current database +connect (n2,localhost,mysqltest_1,,*NO-ONE*,$MASTER_MYPORT,$MASTER_MYSOCK); +connection n2; +--error 1370 +call mysqltest_1.p1(); +disconnect n2; + +connection default; + +drop procedure mysqltest_1.p1; +drop database mysqltest_1; + +revoke usage on *.* from mysqltest_1@localhost; +drop user mysqltest_1@localhost; + +# End of 5.0 bugs. diff --git a/mysql-test/t/sp.test b/mysql-test/t/sp.test index 017e1654c95..f3e7c3e07a0 100644 --- a/mysql-test/t/sp.test +++ b/mysql-test/t/sp.test @@ -2468,25 +2468,26 @@ drop table t3| # # BUG#4318 # -#QQ Don't know if HANDLER commands can work with SPs, or at all... -# -#create table t3 (s1 int)| -#insert into t3 values (3), (4)| -# -#--disable_warnings -#drop procedure if exists bug4318| -#--enable_warnings -#create procedure bug4318() -# handler t3 read next| -# -#handler t3 open| -## Expect no results, as tables are closed, but there shouldn't be any errors -#call bug4318()| -#call bug4318()| -#handler t3 close| -# -#drop procedure bug4318| -#drop table t3| + +--disable_parsing Don't know if HANDLER commands can work with SPs, or at all.. +create table t3 (s1 int)| +insert into t3 values (3), (4)| + +--disable_warnings +drop procedure if exists bug4318| +--enable_warnings +create procedure bug4318() + handler t3 read next| + +handler t3 open| +# Expect no results, as tables are closed, but there shouldn't be any errors +call bug4318()| +call bug4318()| +handler t3 close| + +drop procedure bug4318| +drop table t3| +--enable_parsing # # BUG#4902: Stored procedure with SHOW WARNINGS leads to packet error @@ -2834,26 +2835,27 @@ drop table t3| # # BUG#6022: Stored procedure shutdown problem with self-calling function. # -# This part of test is disabled until we implement support for -# recursive stored functions. -#--disable_warnings -#drop function if exists bug6022| -#--enable_warnings -# -#--disable_warnings -#drop function if exists bug6022| -#--enable_warnings -#create function bug6022(x int) returns int -#begin -# if x < 0 then -# return 0; -# else -# return bug6022(x-1); -# end if; -#end| -# -#select bug6022(5)| -#drop function bug6022| + +--disable_parsing until we implement support for recursive stored functions. +--disable_warnings +drop function if exists bug6022| +--enable_warnings + +--disable_warnings +drop function if exists bug6022| +--enable_warnings +create function bug6022(x int) returns int +begin + if x < 0 then + return 0; + else + return bug6022(x-1); + end if; +end| + +select bug6022(5)| +drop function bug6022| +--enable_parsing # # BUG#6029: Stored procedure specific handlers should have priority @@ -3760,27 +3762,28 @@ drop procedure if exists bug7088_1| drop procedure if exists bug7088_2| --enable_warnings -# psergey: temporarily disabled until Bar fixes BUG#11986 -# create procedure bug6063() -# l�bel: begin end| -# call bug6063()| -# # QQ Known bug: this will not show the label correctly. -# show create procedure bug6063| -# -# set character set utf8| -# create procedure bug7088_1() -# label1: begin end label1| -# create procedure bug7088_2() -# läbel1: begin end| -# call bug7088_1()| -# call bug7088_2()| -# set character set default| -# show create procedure bug7088_1| -# show create procedure bug7088_2| -# -# drop procedure bug6063| -# drop procedure bug7088_1| -# drop procedure bug7088_2| +--disable_parsing temporarily disabled until Bar fixes BUG#11986 +create procedure bug6063() + l�bel: begin end| +call bug6063()| +# QQ Known bug: this will not show the label correctly. +show create procedure bug6063| + +set character set utf8| +create procedure bug7088_1() + label1: begin end label1| +create procedure bug7088_2() + läbel1: begin end| +call bug7088_1()| +call bug7088_2()| +set character set default| +show create procedure bug7088_1| +show create procedure bug7088_2| + +drop procedure bug6063| +drop procedure bug7088_1| +drop procedure bug7088_2| +--enable_parsing # # BUG#9565: "Wrong locking in stored procedure if a sub-sequent procedure @@ -3868,6 +3871,131 @@ where t.table_schema = 'test' and t.table_name = 't1'| drop function bug10055| # +# Bug #12297 "SP crashes the server if data inserted inside a lon loop" +# The test for memleak bug, so actually there is no way to test it +# from the suite. The test below could be used to check SP memory +# consumption by passing large input parameter. +# + +# +# Note: the test is currenly disabled because of the +# Bug #12637: SP crashes the server if it has update query with user var +# & binlog is enabled. +# + +--disable_warnings +#drop procedure if exists bug12297| +--enable_warnings + +#create procedure bug12297(lim int) +#begin +# set @x = 0; +# repeat +# insert into t1(id,data) +# values('aa', @x); +# set @x = @x + 1; +# until @x >= lim +# end repeat; +#end| + +#call bug12297(10)| +#drop procedure bug12297| + +# +# Bug #11247 "Stored procedures: Function calls in long loops leak memory" +# One more memleak bug test. One could use this test to check that the memory +# isn't leaking by increasing the input value for p_bug11247. +# + +--disable_warnings +drop function if exists f_bug11247| +drop procedure if exists p_bug11247| +--enable_warnings + +create function f_bug11247(param int) + returns int +return param + 1| + +create procedure p_bug11247(lim int) +begin + declare v int default 0; + + while v < lim do + set v= f_bug11247(v); + end while; +end| + +call p_bug11247(10)| +drop function f_bug11247| +drop procedure p_bug11247| +# +# BUG#12168: "'DECLARE CONTINUE HANDLER FOR NOT FOUND ...' in conditional +# handled incorrectly" +# +--disable_warnings +drop procedure if exists bug12168| +drop table if exists t1, t2| +--enable_warnings + +create table t1 (a int)| +insert into t1 values (1),(2),(3),(4)| + +create table t2 (a int)| + +create procedure bug12168(arg1 char(1)) +begin + declare b, c integer; + if arg1 = 'a' then + begin + declare c1 cursor for select a from t1 where a % 2; + declare continue handler for not found set b = 1; + set b = 0; + open c1; + c1_repeat: repeat + fetch c1 into c; + if (b = 1) then + leave c1_repeat; + end if; + + insert into t2 values (c); + until b = 1 + end repeat; + end; + end if; + if arg1 = 'b' then + begin + declare c2 cursor for select a from t1 where not a % 2; + declare continue handler for not found set b = 1; + set b = 0; + open c2; + c2_repeat: repeat + fetch c2 into c; + if (b = 1) then + leave c2_repeat; + end if; + + insert into t2 values (c); + until b = 1 + end repeat; + end; + end if; +end| + +call bug12168('a')| +select * from t2| +truncate t2| +call bug12168('b')| +select * from t2| +truncate t2| +call bug12168('a')| +select * from t2| +truncate t2| +call bug12168('b')| +select * from t2| +truncate t2| +drop procedure if exists bug12168| + +# # BUG#NNNN: New bug synopsis # #--disable_warnings diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index 56d2be72ed7..22c5471dee2 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -1801,6 +1801,24 @@ SELECT * FROM t1 WHERE (a,b) IN (('aaa','aaa'), ('aaa','bbb')); DROP TABLE t1; +# +# Bug #11479: subquery over left join with an empty inner table +# + +CREATE TABLE t1 (a int); +CREATE TABLE t2 (a int, b int); +CREATE TABLE t3 (b int NOT NULL); +INSERT INTO t1 VALUES (1), (2), (3), (4); +INSERT INTO t2 VALUES (1,10), (3,30); + +SELECT * FROM t2 LEFT JOIN t3 ON t2.b=t3.b + WHERE t3.b IS NOT NULL OR t2.a > 10; +SELECT * FROM t1 + WHERE t1.a NOT IN (SELECT a FROM t2 LEFT JOIN t3 ON t2.b=t3.b + WHERE t3.b IS NOT NULL OR t2.a > 10); + +DROP TABLE t1,t2,t3; + # End of 4.1 tests # diff --git a/mysql-test/t/trigger.test b/mysql-test/t/trigger.test index 62f14941579..9920f203c94 100644 --- a/mysql-test/t/trigger.test +++ b/mysql-test/t/trigger.test @@ -665,6 +665,7 @@ drop table t1; # Test for bug #11973 "SELECT .. INTO var_name; in trigger cause # crash on update" + create table t1 (id int, data int, username varchar(16)); insert into t1 (id, data) values (1, 0); delimiter |; @@ -684,6 +685,49 @@ connection addconroot; update t1 set data = 2; connection default; +disconnect addconroot; +drop table t1; + +# +# #11587 Trigger causes lost connection error +# + +create table t1 (c1 int, c2 datetime); +delimiter |; +--error ER_SP_NO_RETSET +create trigger tr1 before insert on t1 for each row +begin + set new.c2= '2004-04-01'; + select 'hello'; +end| +delimiter ;| + +insert into t1 (c1) values (1),(2),(3); +select * from t1; + +--disable_warnings +drop procedure if exists bug11587; +--enable_warnings + +delimiter |; +create procedure bug11587(x char(16)) +begin + select "hello"; + select "hello again"; +end| + +create trigger tr1 before insert on t1 for each row +begin + call bug11587(); + set new.c2= '2004-04-02'; +end| +delimiter ;| + +--error 1312 +insert into t1 (c1) values (4),(5),(6); +select * from t1; + +drop procedure bug11587; drop table t1; # Test for bug #11896 "Partial locking in case of recursive trigger diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test index ceff7af401c..a5b72a16972 100644 --- a/mysql-test/t/view.test +++ b/mysql-test/t/view.test @@ -1942,3 +1942,27 @@ DROP TABLE t1,t2,t3,t4,t5; create view v1 as select timestampdiff(day,'1997-01-01 00:00:00','1997-01-02 00:00:00') as f1; select * from v1; drop view v1; + +# +# Bug #10624 Views with multiple UNION and UNION ALL produce incorrect results +# +create table t1 (f1 int); +create table t2 (f1 int); +insert into t1 values (1); +insert into t2 values (2); +create view v1 as select * from t1 union select * from t2 union all select * from t2; +select * from v1; +drop view v1; +drop table t1,t2; +# +# Test for bug #10970: view referring a temporary table indirectly +# + +CREATE TEMPORARY TABLE t1 (a int); +CREATE FUNCTION f1 () RETURNS int RETURN (SELECT COUNT(*) FROM t1); +-- error 1352 +CREATE VIEW v1 AS SELECT f1(); + +DROP FUNCTION f1; +DROP TABLE t1; + |