diff options
Diffstat (limited to 'mysql-test')
73 files changed, 2357 insertions, 250 deletions
diff --git a/mysql-test/include/ndb_backup.inc b/mysql-test/include/ndb_backup.inc new file mode 100644 index 00000000000..f0a883d4e11 --- /dev/null +++ b/mysql-test/include/ndb_backup.inc @@ -0,0 +1,24 @@ +###################################################### +# By JBM 2006-02-16 So that the code is not repeated # +# in test cases and can be reused. # +###################################################### +--exec $NDB_MGM --no-defaults --ndb-connectstring="localhost:$NDBCLUSTER_PORT" -e "start backup" >> $NDB_TOOLS_OUTPUT + +# there is no neat way to find the backupid, this is a hack to find it... + +--exec $NDB_TOOLS_DIR/ndb_select_all --ndb-connectstring="localhost:$NDBCLUSTER_PORT" -d sys --delimiter=',' SYSTAB_0 | grep 520093696 > $MYSQLTEST_VARDIR/tmp.dat + +CREATE TEMPORARY TABLE IF NOT EXISTS test.backup_info (id INT, backup_id INT) ENGINE = HEAP; + +DELETE FROM test.backup_info; + +LOAD DATA INFILE '../tmp.dat' INTO TABLE test.backup_info FIELDS TERMINATED BY ','; + +--replace_column 1 <the_backup_id> + +SELECT @the_backup_id:=backup_id FROM test.backup_info; + +let the_backup_id=`select @the_backup_id`; + +DROP TABLE test.backup_info; + diff --git a/mysql-test/include/ndb_backup_print.inc b/mysql-test/include/ndb_backup_print.inc new file mode 100644 index 00000000000..57fb279491c --- /dev/null +++ b/mysql-test/include/ndb_backup_print.inc @@ -0,0 +1,6 @@ +--exec $NDB_TOOLS_DIR/ndb_restore --no-defaults $ndb_restore_opts -b $the_backup_id -n 1 $NDB_BACKUP_DIR/BACKUP/BACKUP-$the_backup_id $ndb_restore_filter > $MYSQLTEST_VARDIR/tmp/tmp.dat +--exec $NDB_TOOLS_DIR/ndb_restore --no-defaults $ndb_restore_opts -b $the_backup_id -n 2 $NDB_BACKUP_DIR/BACKUP/BACKUP-$the_backup_id $ndb_restore_filter >> $MYSQLTEST_VARDIR/tmp/tmp.dat +--exec sort $MYSQLTEST_VARDIR/tmp/tmp.dat +--exec rm -f $MYSQLTEST_VARDIR/tmp/tmp.dat +--let ndb_restore_opts= +--let ndb_restore_filter= diff --git a/mysql-test/install_test_db.sh b/mysql-test/install_test_db.sh index 75388769808..716f7f31383 100644 --- a/mysql-test/install_test_db.sh +++ b/mysql-test/install_test_db.sh @@ -22,7 +22,7 @@ if [ x$1 = x"--bin" ]; then BINARY_DIST=1 bindir=../bin - scriptdir=../bin + scriptdir=bin libexecdir=../libexec # Check if it's a binary distribution or a 'make install' @@ -33,7 +33,7 @@ if [ x$1 = x"--bin" ]; then then execdir=../../sbin bindir=../../bin - scriptdir=../../bin + scriptdir=../bin libexecdir=../../libexec else execdir=../bin @@ -43,7 +43,7 @@ else execdir=../sql bindir=../client fix_bin=. - scriptdir=../scripts + scriptdir=scripts libexecdir=../libexec fi @@ -100,18 +100,14 @@ if [ x$BINARY_DIST = x1 ] ; then basedir=.. else basedir=. -EXTRA_ARG="--language=../sql/share/english/ --character-sets-dir=../sql/share/charsets/" +EXTRA_ARG="--windows" fi -mysqld_boot="${MYSQLD_BOOTSTRAP-$mysqld}" +INSTALL_CMD="$scriptdir/mysql_install_db --no-defaults $EXTRA_ARG --basedir=$basedir --datadir=mysql-test/$ldata --srcdir=." +echo "running $INSTALL_CMD" -mysqld_boot="$mysqld_boot --no-defaults --bootstrap --skip-grant-tables \ - --basedir=$basedir --datadir=$ldata \ - --skip-innodb --skip-ndbcluster --skip-bdb \ - $EXTRA_ARG" -echo "running $mysqld_boot" - -if $scriptdir/mysql_create_system_tables test $mdata $hostname | $mysqld_boot +cd .. +if $INSTALL_CMD then exit 0 else diff --git a/mysql-test/mysql-test-run.pl b/mysql-test/mysql-test-run.pl index 565c55b14fd..27e70e84afe 100755 --- a/mysql-test/mysql-test-run.pl +++ b/mysql-test/mysql-test-run.pl @@ -353,7 +353,7 @@ sub do_before_start_slave ($); sub ndbd_start ($$$); sub ndb_mgmd_start ($); sub mysqld_start ($$$); -sub mysqld_arguments ($$$$$); +sub mysqld_arguments ($$$$); sub stop_all_servers (); sub run_mysqltest ($); sub usage ($); @@ -1495,9 +1495,17 @@ sub executable_setup () { if (!$opt_extern) { - # Look for SQL scripts directory - $path_sql_dir= mtr_path_exists("$glob_basedir/share", - "$glob_basedir/scripts"); + # Look for SQL scripts directory + if ( mtr_file_exists("$path_share/mysql_system_tables.sql") ne "") + { + # The SQL scripts are in path_share + $path_sql_dir= $path_share; + } + else + { + $path_sql_dir= mtr_path_exists("$glob_basedir/share", + "$glob_basedir/scripts"); + } if ( $mysql_version_id >= 50100 ) { $exe_mysqlslap= mtr_exe_exists("$path_client_bindir/mysqlslap"); @@ -3606,21 +3614,20 @@ sub do_before_start_slave ($) { } -sub mysqld_arguments ($$$$$) { +sub mysqld_arguments ($$$$) { my $args= shift; - my $type= shift; - my $idx= shift; + my $mysqld= shift; my $extra_opt= shift; my $slave_master_info= shift; + my $idx= $mysqld->{'idx'}; my $sidx= ""; # Index as string, 0 is empty string - if ( $idx > 0 ) + if ( $idx> 0 ) { - $sidx= "$idx"; + $sidx= $idx; } my $prefix= ""; # If mysqltest server arg - if ( $glob_use_embedded_server ) { $prefix= "--server-arg="; @@ -3634,8 +3641,16 @@ sub mysqld_arguments ($$$$$) { if ( $mysql_version_id >= 50036) { - # Prevent the started mysqld to access files outside of vardir - mtr_add_arg($args, "%s--secure-file-priv=%s", $prefix, $opt_vardir); + # By default, prevent the started mysqld to access files outside of vardir + my $secure_file_dir= $opt_vardir; + if ( $opt_suite ne "main" ) + { + # When running a suite other than default allow the mysqld + # access to subdirs of mysql-test/ in order to make it possible + # to "load data" from the suites data/ directory. + $secure_file_dir= $glob_mysql_test_dir; + } + mtr_add_arg($args, "%s--secure-file-priv=%s", $prefix, $secure_file_dir); } if ( $mysql_version_id >= 50000 ) @@ -3657,36 +3672,53 @@ sub mysqld_arguments ($$$$$) { } } + mtr_add_arg($args, "%s--pid-file=%s", $prefix, + $mysqld->{'path_pid'}); + + mtr_add_arg($args, "%s--port=%d", $prefix, + $mysqld->{'port'}); + + mtr_add_arg($args, "%s--socket=%s", $prefix, + $mysqld->{'path_sock'}); + + mtr_add_arg($args, "%s--datadir=%s", $prefix, + $mysqld->{'path_myddir'}); + + + if ( $mysql_version_id >= 50106 ) + { + # Turn on logging to bothe tables and file + mtr_add_arg($args, "%s--log-output=table,file", $prefix); + } + + mtr_add_arg($args, "%s--log=%s", $prefix, $mysqld->{'path_mylog'}); + + # Check if "extra_opt" contains --skip-log-bin my $skip_binlog= grep(/^--skip-log-bin/, @$extra_opt); - if ( $type eq 'master' ) + if ( $mysqld->{'type'} eq 'master' ) { - my $id= $idx > 0 ? $idx + 101 : 1; - if (! ($opt_skip_master_binlog || $skip_binlog) ) { mtr_add_arg($args, "%s--log-bin=%s/log/master-bin%s", $prefix, $opt_vardir, $sidx); } - mtr_add_arg($args, "%s--pid-file=%s", $prefix, - $master->[$idx]->{'path_pid'}); - mtr_add_arg($args, "%s--port=%d", $prefix, - $master->[$idx]->{'port'}); - mtr_add_arg($args, "%s--server-id=%d", $prefix, $id); - mtr_add_arg($args, "%s--socket=%s", $prefix, - $master->[$idx]->{'path_sock'}); - mtr_add_arg($args, "%s--innodb_data_file_path=ibdata1:10M:autoextend", $prefix); + + mtr_add_arg($args, "%s--server-id=%d", $prefix, + $idx > 0 ? $idx + 101 : 1); + + mtr_add_arg($args, "%s--innodb_data_file_path=ibdata1:10M:autoextend", + $prefix); + mtr_add_arg($args, "%s--local-infile", $prefix); - mtr_add_arg($args, "%s--datadir=%s", $prefix, - $master->[$idx]->{'path_myddir'}); if ( $idx > 0 or !$use_innodb) { mtr_add_arg($args, "%s--skip-innodb", $prefix); } - my $cluster= $clusters->[$master->[$idx]->{'cluster'}]; + my $cluster= $clusters->[$mysqld->{'cluster'}]; if ( $opt_skip_ndbcluster || !$cluster->{'pid'}) { @@ -3702,26 +3734,12 @@ sub mysqld_arguments ($$$$$) { mtr_add_arg($args, "%s--ndb-extra-logging", $prefix); } } - - if ( $mysql_version_id <= 50106 ) - { - # Force mysqld to use log files up until 5.1.6 - mtr_add_arg($args, "%s--log=%s", $prefix, $master->[0]->{'path_mylog'}); - } - else - { - # Turn on logging, will be sent to tables - mtr_add_arg($args, "%s--log=", $prefix); - } } - - if ( $type eq 'slave' ) + else { - my $slave_server_id= 2 + $idx; - my $slave_rpl_rank= $slave_server_id; + mtr_error("unknown mysqld type") + unless $mysqld->{'type'} eq 'slave'; - mtr_add_arg($args, "%s--datadir=%s", $prefix, - $slave->[$idx]->{'path_myddir'}); mtr_add_arg($args, "%s--init-rpl-role=slave", $prefix); if (! ( $opt_skip_slave_binlog || $skip_binlog )) { @@ -3731,18 +3749,14 @@ sub mysqld_arguments ($$$$$) { } mtr_add_arg($args, "%s--master-retry-count=10", $prefix); - mtr_add_arg($args, "%s--pid-file=%s", $prefix, - $slave->[$idx]->{'path_pid'}); - mtr_add_arg($args, "%s--port=%d", $prefix, - $slave->[$idx]->{'port'}); + mtr_add_arg($args, "%s--relay-log=%s/log/slave%s-relay-bin", $prefix, $opt_vardir, $sidx); mtr_add_arg($args, "%s--report-host=127.0.0.1", $prefix); mtr_add_arg($args, "%s--report-port=%d", $prefix, - $slave->[$idx]->{'port'}); + $mysqld->{'port'}); mtr_add_arg($args, "%s--report-user=root", $prefix); mtr_add_arg($args, "%s--skip-innodb", $prefix); - mtr_add_arg($args, "%s--skip-ndbcluster", $prefix); mtr_add_arg($args, "%s--skip-slave-start", $prefix); # Directory where slaves find the dumps generated by "load data" @@ -3751,8 +3765,6 @@ sub mysqld_arguments ($$$$$) { my $slave_load_path= "../tmp"; mtr_add_arg($args, "%s--slave-load-tmpdir=%s", $prefix, $slave_load_path); - mtr_add_arg($args, "%s--socket=%s", $prefix, - $slave->[$idx]->{'path_sock'}); mtr_add_arg($args, "%s--set-variable=slave_net_timeout=10", $prefix); if ( @$slave_master_info ) @@ -3770,13 +3782,16 @@ sub mysqld_arguments ($$$$$) { mtr_add_arg($args, "%s--master-password=", $prefix); mtr_add_arg($args, "%s--master-port=%d", $prefix, $master->[0]->{'port'}); # First master + + my $slave_server_id= 2 + $idx; + my $slave_rpl_rank= $slave_server_id; mtr_add_arg($args, "%s--server-id=%d", $prefix, $slave_server_id); mtr_add_arg($args, "%s--rpl-recovery-rank=%d", $prefix, $slave_rpl_rank); } if ( $opt_skip_ndbcluster_slave || - $slave->[$idx]->{'cluster'} == -1 || - !$clusters->[$slave->[$idx]->{'cluster'}]->{'pid'} ) + $mysqld->{'cluster'} == -1 || + !$clusters->[$mysqld->{'cluster'}]->{'pid'} ) { mtr_add_arg($args, "%s--skip-ndbcluster", $prefix); } @@ -3784,41 +3799,22 @@ sub mysqld_arguments ($$$$$) { { mtr_add_arg($args, "%s--ndbcluster", $prefix); mtr_add_arg($args, "%s--ndb-connectstring=%s", $prefix, - $clusters->[$slave->[$idx]->{'cluster'}]->{'connect_string'}); + $clusters->[$mysqld->{'cluster'}]->{'connect_string'}); + if ( $mysql_version_id >= 50100 ) { mtr_add_arg($args, "%s--ndb-extra-logging", $prefix); } } - if ( $mysql_version_id <= 50106 ) - { - # Force mysqld to use log files up until 5.1.6 - mtr_add_arg($args, "%s--log=%s", $prefix, $slave->[0]->{'path_mylog'}); - } - else - { - # Turn on logging, will be sent to tables - mtr_add_arg($args, "%s--log=", $prefix); - } - } # end slave if ( $opt_debug ) { - if ( $type eq 'master' ) - { - mtr_add_arg($args, "%s--debug=d:t:i:A,%s/log/master%s.trace", - $prefix, $path_vardir_trace, $sidx); - } - if ( $type eq 'slave' ) - { - mtr_add_arg($args, "%s--debug=d:t:i:A,%s/log/slave%s.trace", - $prefix, $path_vardir_trace, $sidx); - } + mtr_add_arg($args, "%s--debug=d:t:i:A,%s/log/%s%s.trace", + $prefix, $path_vardir_trace, $mysqld->{'type'}, $sidx); } - # FIXME always set nowdays??? SMALL_SERVER mtr_add_arg($args, "%s--key_buffer_size=1M", $prefix); mtr_add_arg($args, "%s--sort_buffer=256K", $prefix); mtr_add_arg($args, "%s--max_heap_table_size=1M", $prefix); @@ -3844,18 +3840,10 @@ sub mysqld_arguments ($$$$$) { mtr_add_arg($args, "%s--gdb", $prefix); } - # If we should run all tests cases, we will use a local server for that - - if ( -w "/" ) - { - # We are running as root; We need to add the --root argument - mtr_add_arg($args, "%s--user=root", $prefix); - } - my $found_skip_core= 0; foreach my $arg ( @opt_extra_mysqld_opt, @$extra_opt ) { - # Allow --skip-core-file to be set in master.opt file + # Allow --skip-core-file to be set in <testname>-[master|slave].opt file if ($arg eq "--skip-core-file") { $found_skip_core= 1; @@ -3879,7 +3867,7 @@ sub mysqld_arguments ($$$$$) { mtr_add_arg($args, "%s--rpl-recovery-rank=1", $prefix); mtr_add_arg($args, "%s--init-rpl-role=master", $prefix); } - elsif ( $type eq 'master' ) + elsif ( $mysqld->{'type'} eq 'master' ) { mtr_add_arg($args, "%s--open-files-limit=1024", $prefix); } @@ -3930,7 +3918,7 @@ sub mysqld_start ($$$) { valgrind_arguments($args, \$exe); } - mysqld_arguments($args,$type,$idx,$extra_opt,$slave_master_info); + mysqld_arguments($args,$mysqld,$extra_opt,$slave_master_info); if ( $opt_gdb || $opt_manual_gdb) { @@ -4674,7 +4662,7 @@ sub run_mysqltest ($) { if ( $glob_use_embedded_server ) { - mysqld_arguments($args,'master',0,$tinfo->{'master_opt'},[]); + mysqld_arguments($args,$master->[0],$tinfo->{'master_opt'},[]); } # ---------------------------------------------------------------------- diff --git a/mysql-test/r/client_xml.result b/mysql-test/r/client_xml.result index 7395b2433e8..6a148954fcd 100644 --- a/mysql-test/r/client_xml.result +++ b/mysql-test/r/client_xml.result @@ -7,7 +7,7 @@ insert into t1 values (1, 2, 'a&b a<b a>b'); <?xml version="1.0"?> <resultset statement="select * from t1 -"> +" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <row> <field name="a&b">1</field> <field name="a<b">2</field> @@ -34,7 +34,7 @@ insert into t1 values (1, 2, 'a&b a<b a>b'); <?xml version="1.0"?> <resultset statement="select count(*) from t1 -"> +" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <row> <field name="count(*)">1</field> </row> @@ -42,7 +42,7 @@ insert into t1 values (1, 2, 'a&b a<b a>b'); <?xml version="1.0"?> <resultset statement="select 1 < 2 from dual -"> +" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <row> <field name="1 < 2">1</field> </row> @@ -50,7 +50,7 @@ insert into t1 values (1, 2, 'a&b a<b a>b'); <?xml version="1.0"?> <resultset statement="select 1 > 2 from dual -"> +" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <row> <field name="1 > 2">0</field> </row> @@ -58,7 +58,7 @@ insert into t1 values (1, 2, 'a&b a<b a>b'); <?xml version="1.0"?> <resultset statement="select 1 & 3 from dual -"> +" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <row> <field name="1 & 3">1</field> </row> @@ -66,7 +66,7 @@ insert into t1 values (1, 2, 'a&b a<b a>b'); <?xml version="1.0"?> <resultset statement="select null from dual -"> +" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <row> <field name="NULL" xsi:nil="true" /> </row> diff --git a/mysql-test/r/create.result b/mysql-test/r/create.result index 11c1431de7b..0a8fef8d881 100644 --- a/mysql-test/r/create.result +++ b/mysql-test/r/create.result @@ -447,8 +447,8 @@ t2 CREATE TABLE `t2` ( `ifnull(c,c)` mediumint(8) default NULL, `ifnull(d,d)` int(11) default NULL, `ifnull(e,e)` bigint(20) default NULL, - `ifnull(f,f)` float(24,2) default NULL, - `ifnull(g,g)` double(53,3) default NULL, + `ifnull(f,f)` float(3,2) default NULL, + `ifnull(g,g)` double(4,3) default NULL, `ifnull(h,h)` decimal(5,4) default NULL, `ifnull(i,i)` year(4) default NULL, `ifnull(j,j)` date default NULL, diff --git a/mysql-test/r/ctype_uca.result b/mysql-test/r/ctype_uca.result index 3e286c77c00..1fd1493bf1e 100644 --- a/mysql-test/r/ctype_uca.result +++ b/mysql-test/r/ctype_uca.result @@ -2654,3 +2654,12 @@ ii 2 ii 2 İİ 4 İİ 4 ii 2 İİ 4 II 2 ıı 4 II 2 DROP TABLE t1; +CREATE TABLE t1 ( +c1 text character set ucs2 collate ucs2_polish_ci NOT NULL +) ENGINE=MyISAM; +insert into t1 values (''),('a'); +SELECT COUNT(*), c1 FROM t1 GROUP BY c1; +COUNT(*) c1 +1 +1 a +DROP TABLE IF EXISTS t1; diff --git a/mysql-test/r/ctype_ucs.result b/mysql-test/r/ctype_ucs.result index e32c1e8aae0..960953b3c5e 100644 --- a/mysql-test/r/ctype_ucs.result +++ b/mysql-test/r/ctype_ucs.result @@ -839,6 +839,24 @@ lily river drop table t1; deallocate prepare stmt; +set names latin1; +set character_set_connection=ucs2; +select soundex(''),soundex('he'),soundex('hello all folks'),soundex('#3556 in bugdb'); +soundex('') soundex('he') soundex('hello all folks') soundex('#3556 in bugdb') + H000 H4142 I51231 +select hex(soundex('')),hex(soundex('he')),hex(soundex('hello all folks')),hex(soundex('#3556 in bugdb')); +hex(soundex('')) hex(soundex('he')) hex(soundex('hello all folks')) hex(soundex('#3556 in bugdb')) + 0048003000300030 00480034003100340032 004900350031003200330031 +select 'mood' sounds like 'mud'; +'mood' sounds like 'mud' +1 +select hex(soundex(_ucs2 0x041004110412)); +hex(soundex(_ucs2 0x041004110412)) +0410003000300030 +select hex(soundex(_ucs2 0x00BF00C0)); +hex(soundex(_ucs2 0x00BF00C0)) +00C0003000300030 +set names latin1; create table t1(a blob, b text charset utf8, c text charset ucs2); select data_type, character_octet_length, character_maximum_length from information_schema.columns where table_name='t1'; diff --git a/mysql-test/r/ctype_utf8.result b/mysql-test/r/ctype_utf8.result index be1e1742ba6..1c6bc0e05b6 100644 --- a/mysql-test/r/ctype_utf8.result +++ b/mysql-test/r/ctype_utf8.result @@ -854,6 +854,18 @@ select * from t1 where soundex(a) = soundex('test'); id a 1 Test drop table t1; +select soundex(_utf8 0xE99885E8A788E99A8FE697B6E69BB4E696B0E79A84E696B0E997BB); +soundex(_utf8 0xE99885E8A788E99A8FE697B6E69BB4E696B0E79A84E696B0E997BB) +阅000 +select hex(soundex(_utf8 0xE99885E8A788E99A8FE697B6E69BB4E696B0E79A84E696B0E997BB)); +hex(soundex(_utf8 0xE99885E8A788E99A8FE697B6E69BB4E696B0E79A84E696B0E997BB)) +E99885303030 +select soundex(_utf8 0xD091D092D093); +soundex(_utf8 0xD091D092D093) +Б000 +select hex(soundex(_utf8 0xD091D092D093)); +hex(soundex(_utf8 0xD091D092D093)) +D091303030 SET collation_connection='utf8_general_ci'; create table t1 select repeat('a',4000) a; delete from t1; diff --git a/mysql-test/r/delayed.result b/mysql-test/r/delayed.result index 82a308c63e7..b37679847be 100644 --- a/mysql-test/r/delayed.result +++ b/mysql-test/r/delayed.result @@ -250,3 +250,8 @@ SELECT HEX(a) FROM t1; HEX(a) 1 DROP TABLE t1; +CREATE TABLE t1(c1 INT) ENGINE=MyISAM; +CREATE TABLE t2(c1 INT) ENGINE=MERGE UNION=(t1); +INSERT DELAYED INTO t2 VALUES(1); +ERROR HY000: Table storage engine for 't2' doesn't have this option +DROP TABLE t1, t2; diff --git a/mysql-test/r/delete.result b/mysql-test/r/delete.result index ba4e9386312..4bdf1c770d3 100644 --- a/mysql-test/r/delete.result +++ b/mysql-test/r/delete.result @@ -214,3 +214,12 @@ select count(*) from t1; count(*) 0 drop table t1; +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1); +DELETE FROM t1 ORDER BY x; +ERROR 42S22: Unknown column 'x' in 'order clause' +DELETE FROM t1 ORDER BY t2.x; +ERROR 42S22: Unknown column 't2.x' in 'order clause' +DELETE FROM t1 ORDER BY (SELECT x); +ERROR 42S22: Unknown column 'x' in 'field list' +DROP TABLE t1; diff --git a/mysql-test/r/func_gconcat.result b/mysql-test/r/func_gconcat.result index 6989b89833b..71419b5b2c3 100644 --- a/mysql-test/r/func_gconcat.result +++ b/mysql-test/r/func_gconcat.result @@ -728,3 +728,13 @@ f2 group_concat(f1) aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 1 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb 2 drop table t1; +CREATE TABLE t1(a TEXT, b CHAR(20)); +INSERT INTO t1 VALUES ("one.1","one.1"),("two.2","two.2"),("one.3","one.3"); +SELECT GROUP_CONCAT(DISTINCT UCASE(a)) FROM t1; +GROUP_CONCAT(DISTINCT UCASE(a)) +ONE.1,TWO.2,ONE.3 +SELECT GROUP_CONCAT(DISTINCT UCASE(b)) FROM t1; +GROUP_CONCAT(DISTINCT UCASE(b)) +ONE.1,TWO.2,ONE.3 +DROP TABLE t1; +End of 5.0 tests diff --git a/mysql-test/r/func_in.result b/mysql-test/r/func_in.result index fad9a7157e1..87855091699 100644 --- a/mysql-test/r/func_in.result +++ b/mysql-test/r/func_in.result @@ -470,4 +470,9 @@ a Warnings: Warning 1292 Incorrect date value: '19772-07-29' for column 'a' at row 1 DROP TABLE t1,t2,t3,t4; +CREATE TABLE t1 (id int not null); +INSERT INTO t1 VALUES (1),(2); +SELECT id FROM t1 WHERE id IN(4564, (SELECT IF(1=0,1,1/0)) ); +id +DROP TABLE t1; End of 5.0 tests diff --git a/mysql-test/r/gis.result b/mysql-test/r/gis.result index de034d93dc5..749c84a1a6f 100644 --- a/mysql-test/r/gis.result +++ b/mysql-test/r/gis.result @@ -730,6 +730,12 @@ point(b, b) IS NULL linestring(b) IS NULL polygon(b) IS NULL multipoint(b) IS NU 1 1 1 1 1 1 1 0 1 1 1 1 1 1 drop table t1; +CREATE TABLE t1(a POINT) ENGINE=MyISAM; +INSERT INTO t1 VALUES (NULL); +SELECT * FROM t1; +a +NULL +DROP TABLE t1; End of 4.1 tests create table t1 (s1 geometry not null,s2 char(100)); create trigger t1_bu before update on t1 for each row set new.s1 = null; @@ -763,3 +769,14 @@ create table t1 (g geometry not null); insert into t1 values(default); ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field drop table t1; +CREATE TABLE t1 (a GEOMETRY); +CREATE VIEW v1 AS SELECT GeomFromwkb(ASBINARY(a)) FROM t1; +CREATE VIEW v2 AS SELECT a FROM t1; +DESCRIBE v1; +Field Type Null Key Default Extra +GeomFromwkb(ASBINARY(a)) geometry YES NULL +DESCRIBE v2; +Field Type Null Key Default Extra +a geometry YES NULL +DROP VIEW v1,v2; +DROP TABLE t1; diff --git a/mysql-test/r/heap_btree.result b/mysql-test/r/heap_btree.result index 5b9c7f2244f..91f51a95936 100644 --- a/mysql-test/r/heap_btree.result +++ b/mysql-test/r/heap_btree.result @@ -280,6 +280,33 @@ a 1 1 drop table t1; +CREATE TABLE t1 ( +c1 CHAR(3), +c2 INTEGER, +KEY USING BTREE(c1), +KEY USING BTREE(c2) +) ENGINE= MEMORY; +INSERT INTO t1 VALUES ('ABC',0), ('A',0), ('B',0), ('C',0); +UPDATE t1 SET c2= c2 + 1 WHERE c1 = 'A'; +SELECT * FROM t1; +c1 c2 +ABC 0 +A 1 +B 0 +C 0 +DROP TABLE t1; +CREATE TABLE t1 ( +c1 ENUM('1', '2'), +UNIQUE USING BTREE(c1) +) ENGINE= MEMORY DEFAULT CHARSET= utf8; +INSERT INTO t1 VALUES('1'), ('2'); +DROP TABLE t1; +CREATE TABLE t1 ( +c1 SET('1', '2'), +UNIQUE USING BTREE(c1) +) ENGINE= MEMORY DEFAULT CHARSET= utf8; +INSERT INTO t1 VALUES('1'), ('2'); +DROP TABLE t1; End of 4.1 tests CREATE TABLE t1(val INT, KEY USING BTREE(val)) ENGINE=memory; INSERT INTO t1 VALUES(0); diff --git a/mysql-test/r/help.result b/mysql-test/r/help.result index 85ca832828d..de8ed249528 100644 --- a/mysql-test/r/help.result +++ b/mysql-test/r/help.result @@ -1,63 +1,63 @@ -insert into mysql.help_category(help_category_id,name)values(1,'impossible_category_1'); +insert into mysql.help_category(help_category_id,name)values(10001,'impossible_category_1'); Warnings: Warning 1364 Field 'url' doesn't have a default value -select @category1_id:= 1; -@category1_id:= 1 -1 -insert into mysql.help_category(help_category_id,name)values(2,'impossible_category_2'); +select @category1_id:= 10001; +@category1_id:= 10001 +10001 +insert into mysql.help_category(help_category_id,name)values(10002,'impossible_category_2'); Warnings: Warning 1364 Field 'url' doesn't have a default value -select @category2_id:= 2; -@category2_id:= 2 -2 -insert into mysql.help_category(help_category_id,name,parent_category_id)values(3,'impossible_category_3',@category2_id); +select @category2_id:= 10002; +@category2_id:= 10002 +10002 +insert into mysql.help_category(help_category_id,name,parent_category_id)values(10003,'impossible_category_3',@category2_id); Warnings: Warning 1364 Field 'url' doesn't have a default value -select @category3_id:= 3; -@category3_id:= 3 -3 -insert into mysql.help_topic(help_topic_id,name,help_category_id,description,example)values(1,'impossible_function_1',@category1_id,'description of \n impossible_function1\n','example of \n impossible_function1'); +select @category3_id:= 10003; +@category3_id:= 10003 +10003 +insert into mysql.help_topic(help_topic_id,name,help_category_id,description,example)values(10101,'impossible_function_1',@category1_id,'description of \n impossible_function1\n','example of \n impossible_function1'); Warnings: Warning 1364 Field 'url' doesn't have a default value -select @topic1_id:= 1; -@topic1_id:= 1 -1 -insert into mysql.help_topic(help_topic_id,name,help_category_id,description,example)values(2,'impossible_function_2',@category1_id,'description of \n impossible_function2\n','example of \n impossible_function2'); +select @topic1_id:= 10101; +@topic1_id:= 10101 +10101 +insert into mysql.help_topic(help_topic_id,name,help_category_id,description,example)values(10102,'impossible_function_2',@category1_id,'description of \n impossible_function2\n','example of \n impossible_function2'); Warnings: Warning 1364 Field 'url' doesn't have a default value -select @topic2_id:= 2; -@topic2_id:= 2 -2 -insert into mysql.help_topic(help_topic_id,name,help_category_id,description,example)values(3,'impossible_function_3',@category2_id,'description of \n impossible_function3\n','example of \n impossible_function3'); +select @topic2_id:= 10102; +@topic2_id:= 10102 +10102 +insert into mysql.help_topic(help_topic_id,name,help_category_id,description,example)values(10103,'impossible_function_3',@category2_id,'description of \n impossible_function3\n','example of \n impossible_function3'); Warnings: Warning 1364 Field 'url' doesn't have a default value -select @topic3_id:= 3; -@topic3_id:= 3 -3 -insert into mysql.help_topic(help_topic_id,name,help_category_id,description,example)values(4,'impossible_function_4',@category2_id,'description of \n impossible_function4\n','example of \n impossible_function4'); +select @topic3_id:= 10103; +@topic3_id:= 10103 +10103 +insert into mysql.help_topic(help_topic_id,name,help_category_id,description,example)values(10104,'impossible_function_4',@category2_id,'description of \n impossible_function4\n','example of \n impossible_function4'); Warnings: Warning 1364 Field 'url' doesn't have a default value -select @topic4_id:= 4; -@topic4_id:= 4 -4 -insert into mysql.help_topic(help_topic_id,name,help_category_id,description,example)values(5,'impossible_function_7',@category3_id,'description of \n impossible_function5\n','example of \n impossible_function7'); +select @topic4_id:= 10104; +@topic4_id:= 10104 +10104 +insert into mysql.help_topic(help_topic_id,name,help_category_id,description,example)values(10105,'impossible_function_7',@category3_id,'description of \n impossible_function5\n','example of \n impossible_function7'); Warnings: Warning 1364 Field 'url' doesn't have a default value -select @topic5_id:= 5; -@topic5_id:= 5 -5 -insert into mysql.help_keyword(help_keyword_id,name)values(1,'impossible_function_1'); -select @keyword1_id:= 1; -@keyword1_id:= 1 -1 -insert into mysql.help_keyword(help_keyword_id,name)values(2,'impossible_function_5'); -select @keyword2_id:= 2; -@keyword2_id:= 2 -2 -insert into mysql.help_keyword(help_keyword_id,name)values(3,'impossible_function_6'); -select @keyword3_id:= 3; -@keyword3_id:= 3 -3 +select @topic5_id:= 10105; +@topic5_id:= 10105 +10105 +insert into mysql.help_keyword(help_keyword_id,name)values(10201,'impossible_function_1'); +select @keyword1_id:= 10201; +@keyword1_id:= 10201 +10201 +insert into mysql.help_keyword(help_keyword_id,name)values(10202,'impossible_function_5'); +select @keyword2_id:= 10202; +@keyword2_id:= 10202 +10202 +insert into mysql.help_keyword(help_keyword_id,name)values(10203,'impossible_function_6'); +select @keyword3_id:= 10203; +@keyword3_id:= 10203 +10203 insert into mysql.help_relation(help_keyword_id,help_topic_id)values(@keyword1_id,@topic2_id); insert into mysql.help_relation(help_keyword_id,help_topic_id)values(@keyword2_id,@topic1_id); insert into mysql.help_relation(help_keyword_id,help_topic_id)values(@keyword3_id,@topic3_id); diff --git a/mysql-test/r/insert_select.result b/mysql-test/r/insert_select.result index 92b3ea0e42b..a96add7eb9a 100644 --- a/mysql-test/r/insert_select.result +++ b/mysql-test/r/insert_select.result @@ -744,3 +744,32 @@ f1 f2 2 2 10 10 DROP TABLE t1, t2; +SET SQL_MODE='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'; +CREATE TABLE t1 (c VARCHAR(30), INDEX ix_c (c(10))); +CREATE TABLE t2 (d VARCHAR(10)); +INSERT INTO t1 (c) VALUES ('7_chars'), ('13_characters'); +EXPLAIN +SELECT (SELECT SUM(LENGTH(c)) FROM t1 WHERE c='13_characters') FROM t1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 +2 SUBQUERY t1 ref ix_c ix_c 13 const 1 Using where +SELECT (SELECT SUM(LENGTH(c)) FROM t1 WHERE c='13_characters') FROM t1; +(SELECT SUM(LENGTH(c)) FROM t1 WHERE c='13_characters') +13 +13 +INSERT INTO t2 (d) +SELECT (SELECT SUM(LENGTH(c)) FROM t1 WHERE c='13_characters') FROM t1; +INSERT INTO t2 (d) +SELECT (SELECT SUM(LENGTH(c)) FROM t1 WHERE c='7_chars') FROM t1; +INSERT INTO t2 (d) +SELECT (SELECT SUM(LENGTH(c)) FROM t1 WHERE c IN (SELECT t1.c FROM t1)) +FROM t1; +SELECT * FROM t2; +d +13 +13 +7 +7 +20 +20 +DROP TABLE t1,t2; diff --git a/mysql-test/r/insert_update.result b/mysql-test/r/insert_update.result index ef0d8ec239e..fd70fcb9084 100644 --- a/mysql-test/r/insert_update.result +++ b/mysql-test/r/insert_update.result @@ -258,3 +258,81 @@ SELECT LAST_INSERT_ID(); LAST_INSERT_ID() 1 DROP TABLE t1; +SET SQL_MODE='NO_AUTO_VALUE_ON_ZERO'; +CREATE TABLE `t1` ( +`id` int(11) PRIMARY KEY auto_increment, +`f1` varchar(10) NOT NULL UNIQUE +); +INSERT IGNORE INTO t1 (f1) VALUES ("test1") +ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id); +INSERT IGNORE INTO t1 (f1) VALUES ("test1") +ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id); +SELECT LAST_INSERT_ID(); +LAST_INSERT_ID() +1 +SELECT * FROM t1; +id f1 +1 test1 +INSERT IGNORE INTO t1 (f1) VALUES ("test2") +ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id); +SELECT * FROM t1; +id f1 +1 test1 +2 test2 +INSERT IGNORE INTO t1 (f1) VALUES ("test2") +ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id); +SELECT LAST_INSERT_ID(); +LAST_INSERT_ID() +2 +SELECT * FROM t1; +id f1 +1 test1 +2 test2 +INSERT IGNORE INTO t1 (f1) VALUES ("test3") +ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id); +SELECT LAST_INSERT_ID(); +LAST_INSERT_ID() +3 +SELECT * FROM t1; +id f1 +1 test1 +2 test2 +3 test3 +DROP TABLE t1; +CREATE TABLE `t1` ( +`id` int(11) PRIMARY KEY auto_increment, +`f1` varchar(10) NOT NULL UNIQUE +); +INSERT IGNORE INTO t1 (f1) VALUES ("test1") +ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id); +SELECT LAST_INSERT_ID(); +LAST_INSERT_ID() +1 +SELECT * FROM t1; +id f1 +1 test1 +INSERT IGNORE INTO t1 (f1) VALUES ("test1"),("test4") +ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id); +SELECT LAST_INSERT_ID(); +LAST_INSERT_ID() +1 +SELECT * FROM t1; +id f1 +1 test1 +2 test4 +DROP TABLE t1; +CREATE TABLE `t1` ( +`id` int(11) PRIMARY KEY auto_increment, +`f1` varchar(10) NOT NULL UNIQUE, +tim1 timestamp default '2003-01-01 00:00:00' on update current_timestamp +); +INSERT INTO t1 (f1) VALUES ("test1"); +SELECT id, f1 FROM t1; +id f1 +1 test1 +REPLACE INTO t1 VALUES (0,"test1",null); +SELECT id, f1 FROM t1; +id f1 +0 test1 +DROP TABLE t1; +SET SQL_MODE=''; diff --git a/mysql-test/r/loaddata.result b/mysql-test/r/loaddata.result index 83c7b37d914..bef483569d4 100644 --- a/mysql-test/r/loaddata.result +++ b/mysql-test/r/loaddata.result @@ -148,11 +148,11 @@ MYSQLTEST_VARDIR/ set @@secure_file_priv= 0; ERROR HY000: Variable 'secure_file_priv' is a read only variable truncate table t1; -load data infile 'MYSQL_TEST_DIR/Makefile' into table t1; +load data infile 'MYSQL_TEST_DIR/t/loaddata.test' into table t1; ERROR HY000: The MySQL server is running with the --secure-file-priv option so it cannot execute this statement select * from t1; a b c -select load_file("MYSQL_TEST_DIR/Makefile"); -load_file("MYSQL_TEST_DIR/Makefile") +select load_file("MYSQL_TEST_DIR/t/loaddata.test"); +load_file("MYSQL_TEST_DIR/t/loaddata.test") NULL drop table t1, t2; diff --git a/mysql-test/r/merge.result b/mysql-test/r/merge.result index e45e5853c0c..c4419d64a65 100644 --- a/mysql-test/r/merge.result +++ b/mysql-test/r/merge.result @@ -803,11 +803,6 @@ CREATE TABLE tm1(a SMALLINT, b SMALLINT, KEY(a)) ENGINE=MERGE UNION=(t1); SELECT * FROM tm1; ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist DROP TABLE t1, tm1; -CREATE TABLE t1(c1 INT) ENGINE=MyISAM; -CREATE TABLE t2(c1 INT) ENGINE=MERGE UNION=(t1); -INSERT DELAYED INTO t2 VALUES(1); -ERROR HY000: Table storage engine for 't2' doesn't have this option -DROP TABLE t1, t2; CREATE TABLE t1(c1 VARCHAR(1)); CREATE TABLE m1 LIKE t1; ALTER TABLE m1 ENGINE=MERGE UNION=(t1); diff --git a/mysql-test/r/myisam.result b/mysql-test/r/myisam.result index 83359048a8c..94f2f1f72b3 100644 --- a/mysql-test/r/myisam.result +++ b/mysql-test/r/myisam.result @@ -943,6 +943,156 @@ SHOW TABLE STATUS LIKE 't1'; Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment t1 MyISAM 10 Dynamic X X X 72057594037927935 X X X X X X latin1_swedish_ci X max_rows=4100100100 avg_row_length=70100 DROP TABLE t1; +CREATE TABLE t1 (c1 TEXT NOT NULL, KEY c1 (c1(10))) ENGINE=MyISAM; +INSERT INTO t1 VALUES +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), +(''), (''), (''), (''), +(' B'), (' B'), (' B'), (' B'); +SELECT DISTINCT COUNT(*) FROM t1 WHERE c1 = ''; +COUNT(*) +4 +SELECT DISTINCT length(c1), c1 FROM t1 WHERE c1 = ''; +length(c1) c1 +0 +SELECT DISTINCT COUNT(*) FROM t1 IGNORE INDEX (c1) WHERE c1 = ''; +COUNT(*) +4 +SELECT DISTINCT length(c1), c1 FROM t1 IGNORE INDEX (c1) WHERE c1 = ''; +length(c1) c1 +0 +SELECT DISTINCT length(c1), c1 FROM t1 ORDER BY c1; +length(c1) c1 +0 +2 A +2 B +DROP TABLE t1; End of 4.1 tests set storage_engine=MyISAM; drop table if exists t1,t2,t3; diff --git a/mysql-test/r/ndb_blob.result b/mysql-test/r/ndb_blob.result index a5a40cffa91..2864b1dc7c9 100644 --- a/mysql-test/r/ndb_blob.result +++ b/mysql-test/r/ndb_blob.result @@ -76,6 +76,8 @@ commit; select a from t1 where d is null; a 1 +delete from t1 where a=45567; +commit; delete from t1 where a=1; delete from t1 where a=2; commit; diff --git a/mysql-test/r/ndb_restore_print.result b/mysql-test/r/ndb_restore_print.result new file mode 100644 index 00000000000..e05f8e43d1a --- /dev/null +++ b/mysql-test/r/ndb_restore_print.result @@ -0,0 +1,321 @@ +use test; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9,t10; +create table t1 +(pk int key +,a1 BIT(1), a2 BIT(5), a3 BIT(33), a4 BIT(63), a5 BIT(64) +,b1 TINYINT, b2 TINYINT UNSIGNED +,c1 SMALLINT, c2 SMALLINT UNSIGNED +,d1 INT, d2 INT UNSIGNED +,e1 BIGINT, e2 BIGINT UNSIGNED +,f1 CHAR(1) BINARY, f2 CHAR(32) BINARY, f3 CHAR(255) BINARY +,g1 VARCHAR(32) BINARY, g2 VARCHAR(255) BINARY, g3 VARCHAR(1000) BINARY +,h1 BINARY(1), h2 BINARY(8), h3 BINARY(255) +,i1 VARBINARY(32), i2 VARBINARY(255), i3 VARBINARY(1000) +) engine myisam; +insert into t1 values +(1 +,0x1, 0x17, 0x789a, 0x789abcde, 0xfedc0001 +,127, 255 +,32767, 65535 +,2147483647, 4294967295 +,9223372036854775807, 18446744073709551615 +,'1','12345678901234567890123456789012','123456789' + ,'1','12345678901234567890123456789012','123456789' + ,0x12,0x123456789abcdef0, 0x012345 +,0x12,0x123456789abcdef0, 0x00123450 +); +insert into t1 values +(2 +,0, 0, 0, 0, 0 +,-128, 0 +,-32768, 0 +,-2147483648, 0 +,-9223372036854775808, 0 +,'','','' + ,'','','' + ,0x0,0x0,0x0 +,0x0,0x0,0x0 +); +insert into t1 values +(3 +,NULL,NULL,NULL,NULL,NULL +,NULL,NULL +,NULL,NULL +,NULL,NULL +,NULL,NULL +,NULL,NULL,NULL +,NULL,NULL,NULL +,NULL,NULL,NULL +,NULL,NULL,NULL +); +select pk +,hex(a1), hex(a2), hex(a3), hex(a4), hex(a5) +,b1, b2 +,c1 , c2 +,d1 , d2 +,e1 , e2 +,f1 , f2, f3 +,g1 , g2, g3 +,hex(h1), hex(h2), hex(h3) +,hex(i1), hex(i2), hex(i3) +from t1 order by pk; +pk 1 +hex(a1) 1 +hex(a2) 17 +hex(a3) 789A +hex(a4) 789ABCDE +hex(a5) FEDC0001 +b1 127 +b2 255 +c1 32767 +c2 65535 +d1 2147483647 +d2 4294967295 +e1 9223372036854775807 +e2 18446744073709551615 +f1 1 +f2 12345678901234567890123456789012 +f3 123456789 +g1 1 +g2 12345678901234567890123456789012 +g3 123456789 +hex(h1) 12 +hex(h2) 123456789ABCDEF0 +hex(h3) 012345000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 +hex(i1) 12 +hex(i2) 123456789ABCDEF0 +hex(i3) 00123450 +pk 2 +hex(a1) 0 +hex(a2) 0 +hex(a3) 0 +hex(a4) 0 +hex(a5) 0 +b1 -128 +b2 0 +c1 -32768 +c2 0 +d1 -2147483648 +d2 0 +e1 -9223372036854775808 +e2 0 +f1 +f2 +f3 +g1 +g2 +g3 +hex(h1) 00 +hex(h2) 0000000000000000 +hex(h3) 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 +hex(i1) 00 +hex(i2) 00 +hex(i3) 00 +pk 3 +hex(a1) NULL +hex(a2) NULL +hex(a3) NULL +hex(a4) NULL +hex(a5) NULL +b1 NULL +b2 NULL +c1 NULL +c2 NULL +d1 NULL +d2 NULL +e1 NULL +e2 NULL +f1 NULL +f2 NULL +f3 NULL +g1 NULL +g2 NULL +g3 NULL +hex(h1) NULL +hex(h2) NULL +hex(h3) NULL +hex(i1) NULL +hex(i2) NULL +hex(i3) NULL +alter table t1 engine ndb; +select pk +,hex(a1), hex(a2), hex(a3), hex(a4), hex(a5) +,b1, b2 +,c1 , c2 +,d1 , d2 +,e1 , e2 +,f1 , f2, f3 +,g1 , g2, g3 +,hex(h1), hex(h2), hex(h3) +,hex(i1), hex(i2), hex(i3) +from t1 order by pk; +pk 1 +hex(a1) 1 +hex(a2) 17 +hex(a3) 789A +hex(a4) 789ABCDE +hex(a5) FEDC0001 +b1 127 +b2 255 +c1 32767 +c2 65535 +d1 2147483647 +d2 4294967295 +e1 9223372036854775807 +e2 18446744073709551615 +f1 1 +f2 12345678901234567890123456789012 +f3 123456789 +g1 1 +g2 12345678901234567890123456789012 +g3 123456789 +hex(h1) 12 +hex(h2) 123456789ABCDEF0 +hex(h3) 012345000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 +hex(i1) 12 +hex(i2) 123456789ABCDEF0 +hex(i3) 00123450 +pk 2 +hex(a1) 0 +hex(a2) 0 +hex(a3) 0 +hex(a4) 0 +hex(a5) 0 +b1 -128 +b2 0 +c1 -32768 +c2 0 +d1 -2147483648 +d2 0 +e1 -9223372036854775808 +e2 0 +f1 +f2 +f3 +g1 +g2 +g3 +hex(h1) 00 +hex(h2) 0000000000000000 +hex(h3) 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 +hex(i1) 00 +hex(i2) 00 +hex(i3) 00 +pk 3 +hex(a1) NULL +hex(a2) NULL +hex(a3) NULL +hex(a4) NULL +hex(a5) NULL +b1 NULL +b2 NULL +c1 NULL +c2 NULL +d1 NULL +d2 NULL +e1 NULL +e2 NULL +f1 NULL +f2 NULL +f3 NULL +g1 NULL +g2 NULL +g3 NULL +hex(h1) NULL +hex(h2) NULL +hex(h3) NULL +hex(i1) NULL +hex(i2) NULL +hex(i3) NULL +CREATE TEMPORARY TABLE IF NOT EXISTS test.backup_info (id INT, backup_id INT) ENGINE = HEAP; +DELETE FROM test.backup_info; +LOAD DATA INFILE '../tmp.dat' INTO TABLE test.backup_info FIELDS TERMINATED BY ','; +SELECT @the_backup_id:=backup_id FROM test.backup_info; +@the_backup_id:=backup_id +<the_backup_id> +DROP TABLE test.backup_info; +1;0x1;0x17;0x789A;0x789ABCDE;0xFEDC0001;127;255;32767;65535;2147483647;4294967295;9223372036854775807;18446744073709551615;1;12345678901234567890123456789012;123456789;1;12345678901234567890123456789012;123456789;0x12;0x123456789ABCDEF0;0x012345;0x12;0x123456789ABCDEF0;0x00123450 +2;0x0;0x0;0x0;0x0;0x0;-128;0;-32768;0;-2147483648;0;-9223372036854775808;0;;;;;;;0x0;0x0;0x0;0x0;0x0;0x0 +3;\N;\N;\N;\N;\N;\N;\N;\N;\N;\N;\N;\N;\N;\N;\N;\N;\N;\N;\N;\N;\N;\N;\N;\N;\N +1,0x1,0x17,0x789A,0x789ABCDE,0xFEDC0001,127,255,32767,65535,2147483647,4294967295,9223372036854775807,18446744073709551615,'1','12345678901234567890123456789012','123456789','1','12345678901234567890123456789012','123456789',0x12,0x123456789ABCDEF0,0x012345,0x12,0x123456789ABCDEF0,0x00123450 +2,0x0,0x0,0x0,0x0,0x0,-128,0,-32768,0,-2147483648,0,-9223372036854775808,0,'','','','','','',0x0,0x0,0x0,0x0,0x0,0x0 +3,,,,,,,,,,,,,,,,,,,,,,,,, +drop table t1; +create table t1 +(pk int key +,f1 CHAR(1) BINARY, f2 CHAR(32) BINARY, f3 CHAR(255) BINARY +,g1 VARCHAR(32) BINARY, g2 VARCHAR(255) BINARY, g3 VARCHAR(1000) BINARY +,h1 BINARY(1), h2 BINARY(9), h3 BINARY(255) +,i1 VARBINARY(32), i2 VARBINARY(255), i3 VARBINARY(1000) +) engine ndb; +insert into t1 values +(1 +,'1','12345678901234567890123456789012','123456789 ' + ,'1 ','12345678901234567890123456789012 ','123456789 ' + ,0x20,0x123456789abcdef020, 0x012345000020 +,0x1200000020,0x123456789abcdef000000020, 0x00123450000020 +); +create table t2 (pk int key, a int) engine ndb; +create table t3 (pk int key, a int) engine ndb; +create table t4 (pk int key, a int) engine ndb; +insert into t2 values (1,11),(2,12),(3,13),(4,14),(5,15); +insert into t3 values (1,21),(2,22),(3,23),(4,24),(5,25); +insert into t4 values (1,31),(2,32),(3,33),(4,34),(5,35); +CREATE TEMPORARY TABLE IF NOT EXISTS test.backup_info (id INT, backup_id INT) ENGINE = HEAP; +DELETE FROM test.backup_info; +LOAD DATA INFILE '../tmp.dat' INTO TABLE test.backup_info FIELDS TERMINATED BY ','; +SELECT @the_backup_id:=backup_id FROM test.backup_info; +@the_backup_id:=backup_id +<the_backup_id> +DROP TABLE test.backup_info; +'1' '1' '12345678901234567890123456789012' '123456789' '1' '12345678901234567890123456789012' '123456789' '0x20' '0x123456789ABCDEF020' '0x012345000020' '0x1200000020' '0x123456789ABCDEF000000020' '0x00123450000020' + +t1 +-- +1 1 12345678901234567890123456789012 123456789 1 12345678901234567890123456789012 123456789 0x20 0x123456789ABCDEF020 0x012345000020 0x1200000020 0x123456789ABCDEF000000020 0x00123450000020 + +t2 +-- +1 11 +2 12 +3 13 +4 14 +5 15 + +t3 +-- +1 21 +2 22 +3 23 +4 24 +5 25 + +t4 +-- +1 31 +2 32 +3 33 +4 34 +5 35 +drop table t1; +create table t1 +(pk int key +,a1 MEDIUMINT, a2 MEDIUMINT UNSIGNED +) engine ndb; +insert into t1 values(1, 8388607, 16777215); +insert into t1 values(2, -8388608, 0); +insert into t1 values(3, -1, 1); +CREATE TEMPORARY TABLE IF NOT EXISTS test.backup_info (id INT, backup_id INT) ENGINE = HEAP; +DELETE FROM test.backup_info; +LOAD DATA INFILE '../tmp.dat' INTO TABLE test.backup_info FIELDS TERMINATED BY ','; +SELECT @the_backup_id:=backup_id FROM test.backup_info; +@the_backup_id:=backup_id +<the_backup_id> +DROP TABLE test.backup_info; +1;8388607;16777215 +2;-8388608;0 +3;-1;1 +drop table t1; +drop table t2; +drop table t3; +drop table t4; diff --git a/mysql-test/r/ndb_single_user.result b/mysql-test/r/ndb_single_user.result index 711d343fffb..7959d8478e1 100644 --- a/mysql-test/r/ndb_single_user.result +++ b/mysql-test/r/ndb_single_user.result @@ -1,7 +1,7 @@ use test; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9,t10; create table t1 (a int key, b int unique, c int) engine ndb; -ERROR HY000: Can't create table './test/t1.frm' (errno: 155) +ERROR HY000: Can't create table './test/t1.frm' (errno: 299) create table t1 (a int key, b int unique, c int) engine ndb; insert into t1 values (1,1,0),(2,2,0),(3,3,0),(4,4,0),(5,5,0),(6,6,0),(7,7,0),(8,8,0),(9,9,0),(10,10,0); create table t2 as select * from t1; @@ -28,19 +28,32 @@ insert into t1 select * from t2; drop table t1; ERROR 42S02: Unknown table 't1' create index new_index on t1 (c); -ERROR 42S02: Table 'test.t1' doesn't exist +ERROR HY000: Got error 299 'Operation not allowed or aborted due to single user mode' from ndbcluster insert into t1 values (1,1,0),(2,2,0),(3,3,0),(4,4,0),(5,5,0),(6,6,0),(7,7,0),(8,8,0),(9,9,0),(10,10,0); -ERROR 42S02: Table 'test.t1' doesn't exist +ERROR HY000: Got error 299 'Operation not allowed or aborted due to single user mode' from ndbcluster select * from t1 where a = 1; -ERROR 42S02: Table 'test.t1' doesn't exist +ERROR HY000: Got error 299 'Operation not allowed or aborted due to single user mode' from ndbcluster select * from t1 where b = 4; -ERROR 42S02: Table 'test.t1' doesn't exist +ERROR HY000: Got error 299 'Operation not allowed or aborted due to single user mode' from ndbcluster update t1 set b=102 where a = 2; -ERROR 42S02: Table 'test.t1' doesn't exist +ERROR HY000: Got error 299 'Operation not allowed or aborted due to single user mode' from ndbcluster update t1 set b=103 where b = 3; -ERROR 42S02: Table 'test.t1' doesn't exist +ERROR HY000: Got error 299 'Operation not allowed or aborted due to single user mode' from ndbcluster update t1 set b=b+100; -ERROR 42S02: Table 'test.t1' doesn't exist +ERROR HY000: Got error 299 'Operation not allowed or aborted due to single user mode' from ndbcluster update t1 set b=b+100 where a > 7; -ERROR 42S02: Table 'test.t1' doesn't exist +ERROR HY000: Got error 299 'Operation not allowed or aborted due to single user mode' from ndbcluster +BEGIN; +update t1 set b=b+100 where a=1; +BEGIN; +update t1 set b=b+100 where a=2; +update t1 set b=b+100 where a=3; +COMMIT; +update t1 set b=b+100 where a=4; +ERROR HY000: Got error 299 'Operation not allowed or aborted due to single user mode' from ndbcluster +COMMIT; +ERROR HY000: Got error 4350 'Transaction already aborted' from ndbcluster +create table t2 (a int) engine myisam; +alter table t2 add column (b int); +drop table t2; drop table t1; diff --git a/mysql-test/r/range.result b/mysql-test/r/range.result index e342e8d1d7f..58f8d077ac5 100644 --- a/mysql-test/r/range.result +++ b/mysql-test/r/range.result @@ -717,6 +717,147 @@ d8c4177d225791924.30714720 d8c4177d2380fc201.39666693 d8c4177d24ccef970.14957924 DROP TABLE t1; +create table t1 ( +c1 char(10), c2 char(10), c3 char(10), c4 char(10), +c5 char(10), c6 char(10), c7 char(10), c8 char(10), +c9 char(10), c10 char(10), c11 char(10), c12 char(10), +c13 char(10), c14 char(10), c15 char(10), c16 char(10), +index(c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12,c13,c14,c15,c16) +); +insert into t1 (c1) values ('1'),('1'),('1'),('1'); +select * from t1 where +c1 in ("abcdefgh", "123456789", "qwertyuio", "asddfgh", +"abcdefg1", "123456781", "qwertyui1", "asddfg1", +"abcdefg2", "123456782", "qwertyui2", "asddfg2", +"abcdefg3", "123456783", "qwertyui3", "asddfg3", +"abcdefg4", "123456784", "qwertyui4", "asddfg4", +"abcdefg5", "123456785", "qwertyui5", "asddfg5", +"abcdefg6", "123456786", "qwertyui6", "asddfg6", +"abcdefg7", "123456787", "qwertyui7", "asddfg7", +"abcdefg8", "123456788", "qwertyui8", "asddfg8", +"abcdefg9", "123456789", "qwertyui9", "asddfg9", +"abcdefgA", "12345678A", "qwertyuiA", "asddfgA", +"abcdefgB", "12345678B", "qwertyuiB", "asddfgB", +"abcdefgC", "12345678C", "qwertyuiC", "asddfgC") +and c2 in ("abcdefgh", "123456789", "qwertyuio", "asddfgh", +"abcdefg1", "123456781", "qwertyui1", "asddfg1", +"abcdefg2", "123456782", "qwertyui2", "asddfg2", +"abcdefg3", "123456783", "qwertyui3", "asddfg3", +"abcdefg4", "123456784", "qwertyui4", "asddfg4", +"abcdefg5", "123456785", "qwertyui5", "asddfg5", +"abcdefg6", "123456786", "qwertyui6", "asddfg6", +"abcdefg7", "123456787", "qwertyui7", "asddfg7", +"abcdefg8", "123456788", "qwertyui8", "asddfg8", +"abcdefg9", "123456789", "qwertyui9", "asddfg9", +"abcdefgA", "12345678A", "qwertyuiA", "asddfgA", +"abcdefgB", "12345678B", "qwertyuiB", "asddfgB", +"abcdefgC", "12345678C", "qwertyuiC", "asddfgC") +and c3 in ("abcdefgh", "123456789", "qwertyuio", "asddfgh", +"abcdefg1", "123456781", "qwertyui1", "asddfg1", +"abcdefg2", "123456782", "qwertyui2", "asddfg2", +"abcdefg3", "123456783", "qwertyui3", "asddfg3", +"abcdefg4", "123456784", "qwertyui4", "asddfg4", +"abcdefg5", "123456785", "qwertyui5", "asddfg5", +"abcdefg6", "123456786", "qwertyui6", "asddfg6", +"abcdefg7", "123456787", "qwertyui7", "asddfg7", +"abcdefg8", "123456788", "qwertyui8", "asddfg8", +"abcdefg9", "123456789", "qwertyui9", "asddfg9", +"abcdefgA", "12345678A", "qwertyuiA", "asddfgA", +"abcdefgB", "12345678B", "qwertyuiB", "asddfgB", +"abcdefgC", "12345678C", "qwertyuiC", "asddfgC") +and c4 in ("abcdefgh", "123456789", "qwertyuio", "asddfgh", +"abcdefg1", "123456781", "qwertyui1", "asddfg1", +"abcdefg2", "123456782", "qwertyui2", "asddfg2", +"abcdefg3", "123456783", "qwertyui3", "asddfg3", +"abcdefg4", "123456784", "qwertyui4", "asddfg4", +"abcdefg5", "123456785", "qwertyui5", "asddfg5", +"abcdefg6", "123456786", "qwertyui6", "asddfg6", +"abcdefg7", "123456787", "qwertyui7", "asddfg7", +"abcdefg8", "123456788", "qwertyui8", "asddfg8", +"abcdefg9", "123456789", "qwertyui9", "asddfg9", +"abcdefgA", "12345678A", "qwertyuiA", "asddfgA", +"abcdefgB", "12345678B", "qwertyuiB", "asddfgB", +"abcdefgC", "12345678C", "qwertyuiC", "asddfgC") +and c5 in ("abcdefgh", "123456789", "qwertyuio", "asddfgh", +"abcdefg1", "123456781", "qwertyui1", "asddfg1", +"abcdefg2", "123456782", "qwertyui2", "asddfg2", +"abcdefg3", "123456783", "qwertyui3", "asddfg3", +"abcdefg4", "123456784", "qwertyui4", "asddfg4", +"abcdefg5", "123456785", "qwertyui5", "asddfg5", +"abcdefg6", "123456786", "qwertyui6", "asddfg6", +"abcdefg7", "123456787", "qwertyui7", "asddfg7", +"abcdefg8", "123456788", "qwertyui8", "asddfg8", +"abcdefg9", "123456789", "qwertyui9", "asddfg9", +"abcdefgA", "12345678A", "qwertyuiA", "asddfgA", +"abcdefgB", "12345678B", "qwertyuiB", "asddfgB", +"abcdefgC", "12345678C", "qwertyuiC", "asddfgC") +and c6 in ("abcdefgh", "123456789", "qwertyuio", "asddfgh", +"abcdefg1", "123456781", "qwertyui1", "asddfg1", +"abcdefg2", "123456782", "qwertyui2", "asddfg2", +"abcdefg3", "123456783", "qwertyui3", "asddfg3", +"abcdefg4", "123456784", "qwertyui4", "asddfg4", +"abcdefg5", "123456785", "qwertyui5", "asddfg5", +"abcdefg6", "123456786", "qwertyui6", "asddfg6", +"abcdefg7", "123456787", "qwertyui7", "asddfg7", +"abcdefg8", "123456788", "qwertyui8", "asddfg8", +"abcdefg9", "123456789", "qwertyui9", "asddfg9", +"abcdefgA", "12345678A", "qwertyuiA", "asddfgA", +"abcdefgB", "12345678B", "qwertyuiB", "asddfgB", +"abcdefgC", "12345678C", "qwertyuiC", "asddfgC") +and c7 in ("abcdefgh", "123456789", "qwertyuio", "asddfgh", +"abcdefg1", "123456781", "qwertyui1", "asddfg1", +"abcdefg2", "123456782", "qwertyui2", "asddfg2", +"abcdefg3", "123456783", "qwertyui3", "asddfg3", +"abcdefg4", "123456784", "qwertyui4", "asddfg4", +"abcdefg5", "123456785", "qwertyui5", "asddfg5", +"abcdefg6", "123456786", "qwertyui6", "asddfg6", +"abcdefg7", "123456787", "qwertyui7", "asddfg7", +"abcdefg8", "123456788", "qwertyui8", "asddfg8", +"abcdefg9", "123456789", "qwertyui9", "asddfg9", +"abcdefgA", "12345678A", "qwertyuiA", "asddfgA", +"abcdefgB", "12345678B", "qwertyuiB", "asddfgB", +"abcdefgC", "12345678C", "qwertyuiC", "asddfgC") +and c8 in ("abcdefgh", "123456789", "qwertyuio", "asddfgh", +"abcdefg1", "123456781", "qwertyui1", "asddfg1", +"abcdefg2", "123456782", "qwertyui2", "asddfg2", +"abcdefg3", "123456783", "qwertyui3", "asddfg3", +"abcdefg4", "123456784", "qwertyui4", "asddfg4", +"abcdefg5", "123456785", "qwertyui5", "asddfg5", +"abcdefg6", "123456786", "qwertyui6", "asddfg6", +"abcdefg7", "123456787", "qwertyui7", "asddfg7", +"abcdefg8", "123456788", "qwertyui8", "asddfg8", +"abcdefg9", "123456789", "qwertyui9", "asddfg9", +"abcdefgA", "12345678A", "qwertyuiA", "asddfgA", +"abcdefgB", "12345678B", "qwertyuiB", "asddfgB", +"abcdefgC", "12345678C", "qwertyuiC", "asddfgC") +and c9 in ("abcdefgh", "123456789", "qwertyuio", "asddfgh", +"abcdefg1", "123456781", "qwertyui1", "asddfg1", +"abcdefg2", "123456782", "qwertyui2", "asddfg2", +"abcdefg3", "123456783", "qwertyui3", "asddfg3", +"abcdefg4", "123456784", "qwertyui4", "asddfg4", +"abcdefg5", "123456785", "qwertyui5", "asddfg5", +"abcdefg6", "123456786", "qwertyui6", "asddfg6", +"abcdefg7", "123456787", "qwertyui7", "asddfg7", +"abcdefg8", "123456788", "qwertyui8", "asddfg8", +"abcdefg9", "123456789", "qwertyui9", "asddfg9", +"abcdefgA", "12345678A", "qwertyuiA", "asddfgA", +"abcdefgB", "12345678B", "qwertyuiB", "asddfgB", +"abcdefgC", "12345678C", "qwertyuiC", "asddfgC") +and c10 in ("abcdefgh", "123456789", "qwertyuio", "asddfgh", +"abcdefg1", "123456781", "qwertyui1", "asddfg1", +"abcdefg2", "123456782", "qwertyui2", "asddfg2", +"abcdefg3", "123456783", "qwertyui3", "asddfg3", +"abcdefg4", "123456784", "qwertyui4", "asddfg4", +"abcdefg5", "123456785", "qwertyui5", "asddfg5", +"abcdefg6", "123456786", "qwertyui6", "asddfg6", +"abcdefg7", "123456787", "qwertyui7", "asddfg7", +"abcdefg8", "123456788", "qwertyui8", "asddfg8", +"abcdefg9", "123456789", "qwertyui9", "asddfg9", +"abcdefgA", "12345678A", "qwertyuiA", "asddfgA", +"abcdefgB", "12345678B", "qwertyuiB", "asddfgB", +"abcdefgC", "12345678C", "qwertyuiC", "asddfgC"); +c1 c2 c3 c4 c5 c6 c7 c8 c9 c10 c11 c12 c13 c14 c15 c16 +drop table t1; End of 4.1 tests CREATE TABLE t1 ( id int(11) NOT NULL auto_increment, diff --git a/mysql-test/r/row.result b/mysql-test/r/row.result index 5b5f8b7b954..faf9b11d7c9 100644 --- a/mysql-test/r/row.result +++ b/mysql-test/r/row.result @@ -306,3 +306,16 @@ a b a b c 1 1 1 2 1 1 2 1 2 1 DROP TABLE t1,t2; +CREATE TABLE t1( +a int, b int, c int, d int, e int, f int, g int, h int, +PRIMARY KEY (a,b,c,d,e,f,g) +); +INSERT INTO t1 VALUES (1,2,3,4,5,6,7,99); +SELECT h FROM t1 WHERE (a,b,c,d,e,f,g)=(1,2,3,4,5,6,7); +h +99 +SET @x:= (SELECT h FROM t1 WHERE (a,b,c,d,e,f,g)=(1,2,3,4,5,6,7)); +SELECT @x; +@x +99 +DROP TABLE t1; diff --git a/mysql-test/r/rpl_misc_functions.result b/mysql-test/r/rpl_misc_functions.result index c11663b8ac8..526414cec9c 100644 --- a/mysql-test/r/rpl_misc_functions.result +++ b/mysql-test/r/rpl_misc_functions.result @@ -18,6 +18,29 @@ create table t2 like t1; load data local infile 'MYSQLTEST_VARDIR/master-data/test/rpl_misc_functions.outfile' into table t2; select * from t1, t2 where (t1.id=t2.id) and not(t1.i=t2.i and t1.r1=t2.r1 and t1.r2=t2.r2 and t1.p=t2.p); id i r1 r2 p id i r1 r2 p -stop slave; -drop table t1; drop table t1; +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 (col_a double default NULL); +CREATE PROCEDURE test_replication_sp1() +BEGIN +INSERT INTO t1 VALUES (rand()), (rand()); +INSERT INTO t1 VALUES (rand()); +END| +CREATE PROCEDURE test_replication_sp2() +BEGIN +CALL test_replication_sp1(); +CALL test_replication_sp1(); +END| +CREATE FUNCTION test_replication_sf() RETURNS DOUBLE DETERMINISTIC +BEGIN +RETURN (rand() + rand()); +END| +CALL test_replication_sp1(); +CALL test_replication_sp2(); +INSERT INTO t1 VALUES (test_replication_sf()); +INSERT INTO t1 VALUES (test_replication_sf()); +INSERT INTO t1 VALUES (test_replication_sf()); +DROP PROCEDURE IF EXISTS test_replication_sp1; +DROP PROCEDURE IF EXISTS test_replication_sp2; +DROP FUNCTION IF EXISTS test_replication_sf; +DROP TABLE IF EXISTS t1; diff --git a/mysql-test/r/rpl_packet.result b/mysql-test/r/rpl_packet.result index a5c9b43cabb..894bc81b08d 100644 --- a/mysql-test/r/rpl_packet.result +++ b/mysql-test/r/rpl_packet.result @@ -15,3 +15,12 @@ select count(*) from `DB_NAME_OF_MAX_LENGTH_AKA_NAME_LEN_64_BYTES_______________ count(*) 1 drop database DB_NAME_OF_MAX_LENGTH_AKA_NAME_LEN_64_BYTES_____________________; +SET @@global.max_allowed_packet=4096; +SET @@global.net_buffer_length=4096; +STOP SLAVE; +START SLAVE; +CREATE TABLe `t1` (`f1` LONGTEXT) ENGINE=MyISAM; +INSERT INTO `t1`(`f1`) VALUES ('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa2048'); +SHOW STATUS LIKE 'Slave_running'; +Variable_name Value +Slave_running OFF diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result index 31d15981d93..b501d547e0a 100644 --- a/mysql-test/r/select.result +++ b/mysql-test/r/select.result @@ -3636,6 +3636,12 @@ id select_type table type possible_keys key key_len ref rows Extra EXPLAIN SELECT 1 FROM t1 IGNORE INDEX FOR JOIN (a) WHERE a = 1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where +EXPLAIN SELECT 1 FROM t1 USE INDEX FOR JOIN (a) WHERE a = 1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref a a 5 const 1 Using where; Using index +EXPLAIN SELECT 1 FROM t1 FORCE INDEX FOR JOIN (a) WHERE a = 1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref a a 5 const 1 Using where; Using index DROP TABLE t1; CREATE TABLE t1 ( f1 int primary key, f2 int, f3 int, f4 int, f5 int, f6 int, checked_out int); CREATE TABLE t2 ( f11 int PRIMARY KEY ); diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index f2d41bd44ae..d5a1c0b2451 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -3880,3 +3880,70 @@ this is a test. 3 this is a test. 1 this is a test. 2 DROP table t1; +CREATE TABLE t1 (a int, b int); +CREATE TABLE t2 (m int, n int); +INSERT INTO t1 VALUES (2,2), (2,2), (3,3), (3,3), (3,3), (4,4); +INSERT INTO t2 VALUES (1,11), (2,22), (3,32), (4,44), (4,44); +SELECT COUNT(*), a, +(SELECT m FROM t2 WHERE m = count(*) LIMIT 1) +FROM t1 GROUP BY a; +COUNT(*) a (SELECT m FROM t2 WHERE m = count(*) LIMIT 1) +2 2 2 +3 3 3 +1 4 1 +SELECT COUNT(*), a, +(SELECT MIN(m) FROM t2 WHERE m = count(*)) +FROM t1 GROUP BY a; +COUNT(*) a (SELECT MIN(m) FROM t2 WHERE m = count(*)) +2 2 2 +3 3 3 +1 4 1 +SELECT COUNT(*), a +FROM t1 GROUP BY a +HAVING (SELECT MIN(m) FROM t2 WHERE m = count(*)) > 1; +COUNT(*) a +2 2 +3 3 +DROP TABLE t1,t2; +CREATE TABLE t1 (a int, b int); +CREATE TABLE t2 (m int, n int); +INSERT INTO t1 VALUES (2,2), (2,2), (3,3), (3,3), (3,3), (4,4); +INSERT INTO t2 VALUES (1,11), (2,22), (3,32), (4,44), (4,44); +SELECT COUNT(*) c, a, +(SELECT GROUP_CONCAT(COUNT(a)) FROM t2 WHERE m = a) +FROM t1 GROUP BY a; +c a (SELECT GROUP_CONCAT(COUNT(a)) FROM t2 WHERE m = a) +2 2 2 +3 3 3 +1 4 1,1 +SELECT COUNT(*) c, a, +(SELECT GROUP_CONCAT(COUNT(a)+1) FROM t2 WHERE m = a) +FROM t1 GROUP BY a; +c a (SELECT GROUP_CONCAT(COUNT(a)+1) FROM t2 WHERE m = a) +2 2 3 +3 3 4 +1 4 2,2 +DROP table t1,t2; +CREATE TABLE t1 (a int, b int); +INSERT INTO t1 VALUES (2,22),(1,11),(2,22); +SELECT a FROM t1 WHERE (SELECT COUNT(b) FROM DUAL) > 0 GROUP BY a; +a +1 +2 +SELECT a FROM t1 WHERE (SELECT COUNT(b) FROM DUAL) > 1 GROUP BY a; +a +SELECT a FROM t1 t0 +WHERE (SELECT COUNT(t0.b) FROM t1 t WHERE t.b>20) GROUP BY a; +a +1 +2 +SET @@sql_mode='ansi'; +SELECT a FROM t1 WHERE (SELECT COUNT(b) FROM DUAL) > 0 GROUP BY a; +ERROR HY000: Invalid use of group function +SELECT a FROM t1 WHERE (SELECT COUNT(b) FROM DUAL) > 1 GROUP BY a; +ERROR HY000: Invalid use of group function +SELECT a FROM t1 t0 +WHERE (SELECT COUNT(t0.b) FROM t1 t WHERE t.b>20) GROUP BY a; +ERROR HY000: Invalid use of group function +SET @@sql_mode=default; +DROP TABLE t1; diff --git a/mysql-test/r/subselect3.result b/mysql-test/r/subselect3.result index b975ea8cbdc..96a3ee00a59 100644 --- a/mysql-test/r/subselect3.result +++ b/mysql-test/r/subselect3.result @@ -645,3 +645,50 @@ a b Z 2 2 0 3 3 1 drop table t1,t2; +CREATE TABLE t1 (a int, b INT, c CHAR(10) NOT NULL, PRIMARY KEY (a, b)); +INSERT INTO t1 VALUES (1,1,'a'), (1,2,'b'), (1,3,'c'), (1,4,'d'), (1,5,'e'), +(2,1,'f'), (2,2,'g'), (2,3,'h'), (3,4,'i'),(3,3,'j'), (3,2,'k'), (3,1,'l'), +(1,9,'m'); +CREATE TABLE t2 (a int, b INT, c CHAR(10) NOT NULL, PRIMARY KEY (a, b)); +INSERT INTO t2 SELECT * FROM t1; +SELECT a, MAX(b), (SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b)) +as test FROM t1 GROUP BY a; +a MAX(b) test +1 9 m +2 3 h +3 4 i +SELECT * FROM t1 GROUP by t1.a +HAVING (MAX(t1.b) > (SELECT MAX(t2.b) FROM t2 WHERE t2.c < t1.c +HAVING MAX(t2.b+t1.a) < 10)); +a b c +SELECT a,b,c FROM t1 WHERE b in (9,3,4) ORDER BY b,c; +a b c +1 3 c +2 3 h +3 3 j +1 4 d +3 4 i +1 9 m +SELECT a, MAX(b), +(SELECT COUNT(DISTINCT t.c) FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b) +LIMIT 1) +as cnt, +(SELECT t.b FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b) LIMIT 1) +as t_b, +(SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b) LIMIT 1) +as t_b, +(SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b) ORDER BY t.c LIMIT 1) +as t_b +FROM t1 GROUP BY a; +a MAX(b) cnt t_b t_b t_b +1 9 1 9 m m +2 3 1 3 h h +3 4 1 4 i i +SELECT a, MAX(b), +(SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b) LIMIT 1) as test +FROM t1 GROUP BY a; +a MAX(b) test +1 9 m +2 3 h +3 4 i +DROP TABLE t1, t2; diff --git a/mysql-test/r/temp_table.result b/mysql-test/r/temp_table.result index 139a7da77de..d6adf51602b 100644 --- a/mysql-test/r/temp_table.result +++ b/mysql-test/r/temp_table.result @@ -152,3 +152,24 @@ SELECT * FROM t1; i DROP TABLE t1; End of 4.1 tests. +CREATE TABLE t1 ( c FLOAT( 20, 14 ) ); +INSERT INTO t1 VALUES( 12139 ); +CREATE TABLE t2 ( c FLOAT(30,18) ); +INSERT INTO t2 VALUES( 123456 ); +SELECT AVG( c ) FROM t1 UNION SELECT 1; +AVG( c ) +12139 +1 +SELECT 1 UNION SELECT AVG( c ) FROM t1; +1 +1 +12139 +SELECT 1 UNION SELECT * FROM t2 UNION SELECT 1; +1 +1 +123456 +SELECT c/1 FROM t1 UNION SELECT 1; +c/1 +12139 +1 +DROP TABLE t1, t2; diff --git a/mysql-test/r/type_datetime.result b/mysql-test/r/type_datetime.result index 7fc1c4f398d..a9083b29805 100644 --- a/mysql-test/r/type_datetime.result +++ b/mysql-test/r/type_datetime.result @@ -168,6 +168,9 @@ dt 0000-00-00 00:00:00 0000-00-00 00:00:00 drop table t1; +select cast('2006-12-05 22:10:10' as datetime) + 0; +cast('2006-12-05 22:10:10' as datetime) + 0 +20061205221010.000000 CREATE TABLE t1(a DATETIME NOT NULL); INSERT INTO t1 VALUES ('20060606155555'); SELECT a FROM t1 WHERE a=(SELECT MAX(a) FROM t1) AND (a="20060606155555"); diff --git a/mysql-test/r/type_float.result b/mysql-test/r/type_float.result index 0cb77f42caf..188963c5bdf 100644 --- a/mysql-test/r/type_float.result +++ b/mysql-test/r/type_float.result @@ -92,7 +92,7 @@ show create table t2; Table Create Table t2 CREATE TABLE `t2` ( `col1` double default NULL, - `col2` double(53,5) default NULL, + `col2` double(22,5) default NULL, `col3` double default NULL, `col4` double default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 @@ -232,12 +232,12 @@ insert into t2 values ("1.23456780"); create table t3 select * from t2 union select * from t1; select * from t3; d -1.234567800 -100000000.000000000 +1.2345678 +100000000 show create table t3; Table Create Table t3 CREATE TABLE `t3` ( - `d` double(22,9) default NULL + `d` double default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1, t2, t3; create table t1 select 105213674794682365.00 + 0.0 x; diff --git a/mysql-test/r/type_newdecimal.result b/mysql-test/r/type_newdecimal.result index e65e76ded3f..359a929d9a3 100644 --- a/mysql-test/r/type_newdecimal.result +++ b/mysql-test/r/type_newdecimal.result @@ -1423,3 +1423,11 @@ cast(19999999999999999999 as unsigned) 18446744073709551615 Warnings: Error 1292 Truncated incorrect DECIMAL value: '' +create table t1(a decimal(18)); +insert into t1 values(123456789012345678); +alter table t1 modify column a decimal(19); +select * from t1; +a +123456789012345678 +drop table t1; +End of 5.0 tests diff --git a/mysql-test/r/union.result b/mysql-test/r/union.result index 9861b1bffeb..efdd8195fb5 100644 --- a/mysql-test/r/union.result +++ b/mysql-test/r/union.result @@ -554,7 +554,7 @@ aa show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `a` varbinary(20) NOT NULL default '' + `a` varbinary(2) NOT NULL default '' ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; create table t1 SELECT 12 as a UNION select 12.2 as a; @@ -655,7 +655,7 @@ f show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `f` varbinary(24) default NULL + `f` varbinary(12) default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; create table t1 SELECT y from t2 UNION select da from t2; @@ -1381,4 +1381,12 @@ a SELECT a FROM (SELECT a FROM t1 UNION SELECT a FROM t1 ORDER BY c) AS test; ERROR 42S22: Unknown column 'c' in 'order clause' DROP TABLE t1; +(select 1 into @var) union (select 1); +ERROR HY000: Incorrect usage of UNION and INTO +(select 1) union (select 1 into @var); +select @var; +@var +1 +(select 2) union (select 1 into @var); +ERROR 42000: Result consisted of more than one row End of 5.0 tests diff --git a/mysql-test/r/view_grant.result b/mysql-test/r/view_grant.result index 45cf5076fe1..6fec52896c9 100644 --- a/mysql-test/r/view_grant.result +++ b/mysql-test/r/view_grant.result @@ -773,4 +773,25 @@ DROP DATABASE mysqltest_db1; DROP DATABASE mysqltest_db2; DROP USER mysqltest_u1@localhost; DROP USER mysqltest_u2@localhost; +CREATE DATABASE db26813; +USE db26813; +CREATE TABLE t1(f1 INT, f2 INT); +CREATE VIEW v1 AS SELECT f1 FROM t1; +CREATE VIEW v2 AS SELECT f1 FROM t1; +CREATE VIEW v3 AS SELECT f1 FROM t1; +CREATE USER u26813@localhost; +GRANT DROP ON db26813.v1 TO u26813@localhost; +GRANT CREATE VIEW ON db26813.v2 TO u26813@localhost; +GRANT DROP, CREATE VIEW ON db26813.v3 TO u26813@localhost; +GRANT SELECT ON db26813.t1 TO u26813@localhost; +ALTER VIEW v1 AS SELECT f2 FROM t1; +ERROR 42000: CREATE VIEW command denied to user 'u26813'@'localhost' for table 'v1' +ALTER VIEW v2 AS SELECT f2 FROM t1; +ERROR 42000: DROP command denied to user 'u26813'@'localhost' for table 'v2' +ALTER VIEW v3 AS SELECT f2 FROM t1; +SHOW CREATE VIEW v3; +View Create View +v3 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v3` AS select `t1`.`f2` AS `f2` from `t1` +DROP USER u26813@localhost; +DROP DATABASE db26813; End of 5.0 tests. diff --git a/mysql-test/t/ctype_uca.test b/mysql-test/t/ctype_uca.test index 3e49b9de883..64349bc40a6 100644 --- a/mysql-test/t/ctype_uca.test +++ b/mysql-test/t/ctype_uca.test @@ -475,3 +475,13 @@ ALTER TABLE t1 MODIFY a VARCHAR(30) character set utf8 collate utf8_turkish_ci; SELECT a, length(a) la, @l:=lower(a) l, length(@l) ll, @u:=upper(a) u, length(@u) lu FROM t1 ORDER BY id; DROP TABLE t1; + +# +# Bug #27079 Crash while grouping empty ucs2 strings +# +CREATE TABLE t1 ( + c1 text character set ucs2 collate ucs2_polish_ci NOT NULL +) ENGINE=MyISAM; +insert into t1 values (''),('a'); +SELECT COUNT(*), c1 FROM t1 GROUP BY c1; +DROP TABLE IF EXISTS t1; diff --git a/mysql-test/t/ctype_ucs.test b/mysql-test/t/ctype_ucs.test index 5a3720dc431..c3320159c41 100644 --- a/mysql-test/t/ctype_ucs.test +++ b/mysql-test/t/ctype_ucs.test @@ -573,6 +573,20 @@ drop table t1; deallocate prepare stmt; # +# Bug#22638 SOUNDEX broken for international characters +# +set names latin1; +set character_set_connection=ucs2; +select soundex(''),soundex('he'),soundex('hello all folks'),soundex('#3556 in bugdb'); +select hex(soundex('')),hex(soundex('he')),hex(soundex('hello all folks')),hex(soundex('#3556 in bugdb')); +select 'mood' sounds like 'mud'; +# Cyrillic A, BE, VE +select hex(soundex(_ucs2 0x041004110412)); +# Make sure that "U+00BF INVERTED QUESTION MARK" is not considered as letter +select hex(soundex(_ucs2 0x00BF00C0)); +set names latin1; + +# # Bug #14290: character_maximum_length for text fields # create table t1(a blob, b text charset utf8, c text charset ucs2); diff --git a/mysql-test/t/ctype_utf8.test b/mysql-test/t/ctype_utf8.test index 04b7ec78842..79b73fc7880 100644 --- a/mysql-test/t/ctype_utf8.test +++ b/mysql-test/t/ctype_utf8.test @@ -702,6 +702,14 @@ select * from t1 where soundex(a) = soundex('TEST'); select * from t1 where soundex(a) = soundex('test'); drop table t1; +# +# Bug#22638 SOUNDEX broken for international characters +# +select soundex(_utf8 0xE99885E8A788E99A8FE697B6E69BB4E696B0E79A84E696B0E997BB); +select hex(soundex(_utf8 0xE99885E8A788E99A8FE697B6E69BB4E696B0E79A84E696B0E997BB)); +select soundex(_utf8 0xD091D092D093); +select hex(soundex(_utf8 0xD091D092D093)); + SET collation_connection='utf8_general_ci'; -- source include/ctype_filesort.inc diff --git a/mysql-test/t/delayed.test b/mysql-test/t/delayed.test index 773927f6015..13615c8c269 100644 --- a/mysql-test/t/delayed.test +++ b/mysql-test/t/delayed.test @@ -242,3 +242,13 @@ INSERT DELAYED INTO t1 VALUES(1); FLUSH TABLE t1; SELECT HEX(a) FROM t1; DROP TABLE t1; + +# +# Bug#26464 - insert delayed + update + merge = corruption +# +CREATE TABLE t1(c1 INT) ENGINE=MyISAM; +CREATE TABLE t2(c1 INT) ENGINE=MERGE UNION=(t1); +--error 1031 +INSERT DELAYED INTO t2 VALUES(1); +DROP TABLE t1, t2; + diff --git a/mysql-test/t/delete.test b/mysql-test/t/delete.test index 306447dbd5a..36d627209db 100644 --- a/mysql-test/t/delete.test +++ b/mysql-test/t/delete.test @@ -203,3 +203,21 @@ select * from t1 where a is null; delete from t1 where a is null; select count(*) from t1; drop table t1; + +# +# Bug #26186: delete order by, sometimes accept unknown column +# +CREATE TABLE t1 (a INT); INSERT INTO t1 VALUES (1); + +--error ER_BAD_FIELD_ERROR +DELETE FROM t1 ORDER BY x; + +# even columns from a table not used in query (and not even existing) +--error ER_BAD_FIELD_ERROR +DELETE FROM t1 ORDER BY t2.x; + +# subquery (as long as the subquery from is valid or DUAL) +--error ER_BAD_FIELD_ERROR +DELETE FROM t1 ORDER BY (SELECT x); + +DROP TABLE t1; diff --git a/mysql-test/t/disabled.def b/mysql-test/t/disabled.def index 2116e9f51e0..df56165950f 100644 --- a/mysql-test/t/disabled.def +++ b/mysql-test/t/disabled.def @@ -12,4 +12,3 @@ ndb_load : Bug#17233 user_limits : Bug#23921 random failure of user_limits.test -ndb_single_user : Bug#27021 Error codes in mysqld in single user mode varies diff --git a/mysql-test/t/func_gconcat.test b/mysql-test/t/func_gconcat.test index 3ff4b35873b..0dd82864520 100644 --- a/mysql-test/t/func_gconcat.test +++ b/mysql-test/t/func_gconcat.test @@ -497,4 +497,14 @@ select f2,group_concat(f1) from t1 group by f2; --disable_metadata drop table t1; -# End of 4.1 tests +# +# Bug #26815: Unexpected built-in function behavior: group_concat(distinct +# substring_index()) +# +CREATE TABLE t1(a TEXT, b CHAR(20)); +INSERT INTO t1 VALUES ("one.1","one.1"),("two.2","two.2"),("one.3","one.3"); +SELECT GROUP_CONCAT(DISTINCT UCASE(a)) FROM t1; +SELECT GROUP_CONCAT(DISTINCT UCASE(b)) FROM t1; +DROP TABLE t1; + +--echo End of 5.0 tests diff --git a/mysql-test/t/func_in.test b/mysql-test/t/func_in.test index f9749662ec1..77592d015eb 100644 --- a/mysql-test/t/func_in.test +++ b/mysql-test/t/func_in.test @@ -360,4 +360,15 @@ SELECT * FROM t4 WHERE a IN ('1972-02-06','19772-07-29'); DROP TABLE t1,t2,t3,t4; +# +# BUG#27362: IN with a decimal expression that may return NULL +# + +CREATE TABLE t1 (id int not null); +INSERT INTO t1 VALUES (1),(2); + +SELECT id FROM t1 WHERE id IN(4564, (SELECT IF(1=0,1,1/0)) ); + +DROP TABLE t1; + --echo End of 5.0 tests diff --git a/mysql-test/t/gis.test b/mysql-test/t/gis.test index b32764f1f62..4f6104aab3e 100644 --- a/mysql-test/t/gis.test +++ b/mysql-test/t/gis.test @@ -423,6 +423,14 @@ from t1; drop table t1; +# +# Bug #27164: Crash when mixing InnoDB and MyISAM Geospatial tables +# +CREATE TABLE t1(a POINT) ENGINE=MyISAM; +INSERT INTO t1 VALUES (NULL); +SELECT * FROM t1; +DROP TABLE t1; + --echo End of 4.1 tests # @@ -471,3 +479,14 @@ create table t1 (g geometry not null); insert into t1 values(default); drop table t1; +# +# Bug #27300: create view with geometry functions lost columns types +# +CREATE TABLE t1 (a GEOMETRY); +CREATE VIEW v1 AS SELECT GeomFromwkb(ASBINARY(a)) FROM t1; +CREATE VIEW v2 AS SELECT a FROM t1; +DESCRIBE v1; +DESCRIBE v2; + +DROP VIEW v1,v2; +DROP TABLE t1; diff --git a/mysql-test/t/heap_btree.test b/mysql-test/t/heap_btree.test index 03ba8661a3c..d5a4fb7a734 100644 --- a/mysql-test/t/heap_btree.test +++ b/mysql-test/t/heap_btree.test @@ -182,6 +182,37 @@ delete from t1 where a >= 2; select a from t1 order by a; drop table t1; +# +# Bug#26996 - Update of a Field in a Memory Table ends with wrong result +# +CREATE TABLE t1 ( + c1 CHAR(3), + c2 INTEGER, + KEY USING BTREE(c1), + KEY USING BTREE(c2) +) ENGINE= MEMORY; +INSERT INTO t1 VALUES ('ABC',0), ('A',0), ('B',0), ('C',0); +UPDATE t1 SET c2= c2 + 1 WHERE c1 = 'A'; +SELECT * FROM t1; +DROP TABLE t1; + +# +# Bug#24985 - UTF8 ENUM primary key on MEMORY using BTREE +# causes incorrect duplicate entries +# +CREATE TABLE t1 ( + c1 ENUM('1', '2'), + UNIQUE USING BTREE(c1) +) ENGINE= MEMORY DEFAULT CHARSET= utf8; +INSERT INTO t1 VALUES('1'), ('2'); +DROP TABLE t1; +CREATE TABLE t1 ( + c1 SET('1', '2'), + UNIQUE USING BTREE(c1) +) ENGINE= MEMORY DEFAULT CHARSET= utf8; +INSERT INTO t1 VALUES('1'), ('2'); +DROP TABLE t1; + --echo End of 4.1 tests # @@ -205,3 +236,4 @@ INSERT INTO t1 VALUES(NULL),(NULL); DROP TABLE t1; --echo End of 5.0 tests + diff --git a/mysql-test/t/help.test b/mysql-test/t/help.test index ff431fb4ebd..de0cefab76c 100644 --- a/mysql-test/t/help.test +++ b/mysql-test/t/help.test @@ -13,30 +13,30 @@ # impossible_category_3 # impossible_function_7 -insert into mysql.help_category(help_category_id,name)values(1,'impossible_category_1'); -select @category1_id:= 1; -insert into mysql.help_category(help_category_id,name)values(2,'impossible_category_2'); -select @category2_id:= 2; -insert into mysql.help_category(help_category_id,name,parent_category_id)values(3,'impossible_category_3',@category2_id); -select @category3_id:= 3; - -insert into mysql.help_topic(help_topic_id,name,help_category_id,description,example)values(1,'impossible_function_1',@category1_id,'description of \n impossible_function1\n','example of \n impossible_function1'); -select @topic1_id:= 1; -insert into mysql.help_topic(help_topic_id,name,help_category_id,description,example)values(2,'impossible_function_2',@category1_id,'description of \n impossible_function2\n','example of \n impossible_function2'); -select @topic2_id:= 2; -insert into mysql.help_topic(help_topic_id,name,help_category_id,description,example)values(3,'impossible_function_3',@category2_id,'description of \n impossible_function3\n','example of \n impossible_function3'); -select @topic3_id:= 3; -insert into mysql.help_topic(help_topic_id,name,help_category_id,description,example)values(4,'impossible_function_4',@category2_id,'description of \n impossible_function4\n','example of \n impossible_function4'); -select @topic4_id:= 4; -insert into mysql.help_topic(help_topic_id,name,help_category_id,description,example)values(5,'impossible_function_7',@category3_id,'description of \n impossible_function5\n','example of \n impossible_function7'); -select @topic5_id:= 5; - -insert into mysql.help_keyword(help_keyword_id,name)values(1,'impossible_function_1'); -select @keyword1_id:= 1; -insert into mysql.help_keyword(help_keyword_id,name)values(2,'impossible_function_5'); -select @keyword2_id:= 2; -insert into mysql.help_keyword(help_keyword_id,name)values(3,'impossible_function_6'); -select @keyword3_id:= 3; +insert into mysql.help_category(help_category_id,name)values(10001,'impossible_category_1'); +select @category1_id:= 10001; +insert into mysql.help_category(help_category_id,name)values(10002,'impossible_category_2'); +select @category2_id:= 10002; +insert into mysql.help_category(help_category_id,name,parent_category_id)values(10003,'impossible_category_3',@category2_id); +select @category3_id:= 10003; + +insert into mysql.help_topic(help_topic_id,name,help_category_id,description,example)values(10101,'impossible_function_1',@category1_id,'description of \n impossible_function1\n','example of \n impossible_function1'); +select @topic1_id:= 10101; +insert into mysql.help_topic(help_topic_id,name,help_category_id,description,example)values(10102,'impossible_function_2',@category1_id,'description of \n impossible_function2\n','example of \n impossible_function2'); +select @topic2_id:= 10102; +insert into mysql.help_topic(help_topic_id,name,help_category_id,description,example)values(10103,'impossible_function_3',@category2_id,'description of \n impossible_function3\n','example of \n impossible_function3'); +select @topic3_id:= 10103; +insert into mysql.help_topic(help_topic_id,name,help_category_id,description,example)values(10104,'impossible_function_4',@category2_id,'description of \n impossible_function4\n','example of \n impossible_function4'); +select @topic4_id:= 10104; +insert into mysql.help_topic(help_topic_id,name,help_category_id,description,example)values(10105,'impossible_function_7',@category3_id,'description of \n impossible_function5\n','example of \n impossible_function7'); +select @topic5_id:= 10105; + +insert into mysql.help_keyword(help_keyword_id,name)values(10201,'impossible_function_1'); +select @keyword1_id:= 10201; +insert into mysql.help_keyword(help_keyword_id,name)values(10202,'impossible_function_5'); +select @keyword2_id:= 10202; +insert into mysql.help_keyword(help_keyword_id,name)values(10203,'impossible_function_6'); +select @keyword3_id:= 10203; insert into mysql.help_relation(help_keyword_id,help_topic_id)values(@keyword1_id,@topic2_id); insert into mysql.help_relation(help_keyword_id,help_topic_id)values(@keyword2_id,@topic1_id); diff --git a/mysql-test/t/init_connect.test b/mysql-test/t/init_connect.test index c9a18a4003d..0a08559279c 100644 --- a/mysql-test/t/init_connect.test +++ b/mysql-test/t/init_connect.test @@ -2,10 +2,11 @@ # Test of init_connect variable # +# should work with embedded server after mysqltest is fixed +--source include/not_embedded.inc + --source include/add_anonymous_users.inc -# should work with embedded server after mysqltest is fixed --- source include/not_embedded.inc connect (con0,localhost,root,,); connection con0; select hex(@a); diff --git a/mysql-test/t/insert_select.test b/mysql-test/t/insert_select.test index 31508b3d6c4..bbc51be6dc9 100644 --- a/mysql-test/t/insert_select.test +++ b/mysql-test/t/insert_select.test @@ -306,4 +306,29 @@ INSERT INTO t2 (f1, f2) SELECT * FROM t2; DROP TABLE t1, t2; - +# +# Bug #26207: inserts don't work with shortened index +# +SET SQL_MODE='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'; + +CREATE TABLE t1 (c VARCHAR(30), INDEX ix_c (c(10))); +CREATE TABLE t2 (d VARCHAR(10)); +INSERT INTO t1 (c) VALUES ('7_chars'), ('13_characters'); + +EXPLAIN + SELECT (SELECT SUM(LENGTH(c)) FROM t1 WHERE c='13_characters') FROM t1; + +SELECT (SELECT SUM(LENGTH(c)) FROM t1 WHERE c='13_characters') FROM t1; + +INSERT INTO t2 (d) + SELECT (SELECT SUM(LENGTH(c)) FROM t1 WHERE c='13_characters') FROM t1; + +INSERT INTO t2 (d) + SELECT (SELECT SUM(LENGTH(c)) FROM t1 WHERE c='7_chars') FROM t1; + +INSERT INTO t2 (d) + SELECT (SELECT SUM(LENGTH(c)) FROM t1 WHERE c IN (SELECT t1.c FROM t1)) + FROM t1; + +SELECT * FROM t2; +DROP TABLE t1,t2; diff --git a/mysql-test/t/insert_update.test b/mysql-test/t/insert_update.test index b0de66f7fc6..76df4502769 100644 --- a/mysql-test/t/insert_update.test +++ b/mysql-test/t/insert_update.test @@ -195,3 +195,55 @@ SELECT LAST_INSERT_ID(); INSERT t1 (f2) VALUES ('test') ON DUPLICATE KEY UPDATE f1 = LAST_INSERT_ID(f1); SELECT LAST_INSERT_ID(); DROP TABLE t1; + +# +# Bug#23233: 0 as LAST_INSERT_ID() after INSERT .. ON DUPLICATE in the +# NO_AUTO_VALUE_ON_ZERO mode. +# +SET SQL_MODE='NO_AUTO_VALUE_ON_ZERO'; +CREATE TABLE `t1` ( + `id` int(11) PRIMARY KEY auto_increment, + `f1` varchar(10) NOT NULL UNIQUE +); +INSERT IGNORE INTO t1 (f1) VALUES ("test1") + ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id); +INSERT IGNORE INTO t1 (f1) VALUES ("test1") + ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id); +SELECT LAST_INSERT_ID(); +SELECT * FROM t1; +INSERT IGNORE INTO t1 (f1) VALUES ("test2") + ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id); +SELECT * FROM t1; +INSERT IGNORE INTO t1 (f1) VALUES ("test2") + ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id); +SELECT LAST_INSERT_ID(); +SELECT * FROM t1; +INSERT IGNORE INTO t1 (f1) VALUES ("test3") + ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id); +SELECT LAST_INSERT_ID(); +SELECT * FROM t1; +DROP TABLE t1; +CREATE TABLE `t1` ( + `id` int(11) PRIMARY KEY auto_increment, + `f1` varchar(10) NOT NULL UNIQUE +); +INSERT IGNORE INTO t1 (f1) VALUES ("test1") + ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id); +SELECT LAST_INSERT_ID(); +SELECT * FROM t1; +INSERT IGNORE INTO t1 (f1) VALUES ("test1"),("test4") + ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id); +SELECT LAST_INSERT_ID(); +SELECT * FROM t1; +DROP TABLE t1; +CREATE TABLE `t1` ( + `id` int(11) PRIMARY KEY auto_increment, + `f1` varchar(10) NOT NULL UNIQUE, + tim1 timestamp default '2003-01-01 00:00:00' on update current_timestamp +); +INSERT INTO t1 (f1) VALUES ("test1"); +SELECT id, f1 FROM t1; +REPLACE INTO t1 VALUES (0,"test1",null); +SELECT id, f1 FROM t1; +DROP TABLE t1; +SET SQL_MODE=''; diff --git a/mysql-test/t/loaddata.test b/mysql-test/t/loaddata.test index 0dc91c36a09..125a65826ca 100644 --- a/mysql-test/t/loaddata.test +++ b/mysql-test/t/loaddata.test @@ -126,12 +126,12 @@ set @@secure_file_priv= 0; truncate table t1; --replace_result $MYSQL_TEST_DIR MYSQL_TEST_DIR --error 1290 -eval load data infile '$MYSQL_TEST_DIR/Makefile' into table t1; +eval load data infile '$MYSQL_TEST_DIR/t/loaddata.test' into table t1; select * from t1; # Test "load_file" returns NULL --replace_result $MYSQL_TEST_DIR MYSQL_TEST_DIR -eval select load_file("$MYSQL_TEST_DIR/Makefile"); +eval select load_file("$MYSQL_TEST_DIR/t/loaddata.test"); # cleanup drop table t1, t2; diff --git a/mysql-test/t/merge.test b/mysql-test/t/merge.test index 07923fa020c..490010c0a42 100644 --- a/mysql-test/t/merge.test +++ b/mysql-test/t/merge.test @@ -430,16 +430,7 @@ CREATE TABLE tm1(a SMALLINT, b SMALLINT, KEY(a)) ENGINE=MERGE UNION=(t1); SELECT * FROM tm1; DROP TABLE t1, tm1; -# -# Bug#26464 - insert delayed + update + merge = corruption -# -CREATE TABLE t1(c1 INT) ENGINE=MyISAM; -CREATE TABLE t2(c1 INT) ENGINE=MERGE UNION=(t1); ---error 1031 -INSERT DELAYED INTO t2 VALUES(1); -DROP TABLE t1, t2; -# # BUG#26881 - Large MERGE tables report incorrect specification when no # differences in tables # diff --git a/mysql-test/t/myisam.test b/mysql-test/t/myisam.test index 59e3206d1c3..82d10059c65 100644 --- a/mysql-test/t/myisam.test +++ b/mysql-test/t/myisam.test @@ -874,6 +874,150 @@ CREATE TABLE t1 (c1 TEXT) AVG_ROW_LENGTH=70100 MAX_ROWS=4100100100; SHOW TABLE STATUS LIKE 't1'; DROP TABLE t1; +# +# Bug#26231 - select count(*) on myisam table returns wrong value +# when index is used +# +CREATE TABLE t1 (c1 TEXT NOT NULL, KEY c1 (c1(10))) ENGINE=MyISAM; +# Fill at least two key blocks. "Tab, A" must be in both blocks. +INSERT INTO t1 VALUES + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (''), (''), (''), (''), + (' B'), (' B'), (' B'), (' B'); +SELECT DISTINCT COUNT(*) FROM t1 WHERE c1 = ''; +SELECT DISTINCT length(c1), c1 FROM t1 WHERE c1 = ''; +SELECT DISTINCT COUNT(*) FROM t1 IGNORE INDEX (c1) WHERE c1 = ''; +SELECT DISTINCT length(c1), c1 FROM t1 IGNORE INDEX (c1) WHERE c1 = ''; +SELECT DISTINCT length(c1), c1 FROM t1 ORDER BY c1; +DROP TABLE t1; + --echo End of 4.1 tests diff --git a/mysql-test/t/mysqlbinlog-cp932.test b/mysql-test/t/mysqlbinlog-cp932.test index 0e0a4e2bfae..1487606a6c2 100644 --- a/mysql-test/t/mysqlbinlog-cp932.test +++ b/mysql-test/t/mysqlbinlog-cp932.test @@ -1,3 +1,5 @@ +# disabled in embedded until tools running is fixed with embedded +--source include/not_embedded.inc -- source include/have_cp932.inc # Bug#16217 (mysql client did not know how not switch its internal charset) diff --git a/mysql-test/t/ndb_blob.test b/mysql-test/t/ndb_blob.test index d6e0edc89f0..32699017c03 100644 --- a/mysql-test/t/ndb_blob.test +++ b/mysql-test/t/ndb_blob.test @@ -96,6 +96,11 @@ update t1 set d=null where a=1; commit; select a from t1 where d is null; +# bug#24028 - does not occur on MySQL level +# bug#17986 - not seen by us anymore but could show as warning here +delete from t1 where a=45567; +commit; + # pk delete delete from t1 where a=1; delete from t1 where a=2; diff --git a/mysql-test/t/ndb_restore_print.test b/mysql-test/t/ndb_restore_print.test new file mode 100644 index 00000000000..6dbbfdf5933 --- /dev/null +++ b/mysql-test/t/ndb_restore_print.test @@ -0,0 +1,189 @@ +-- source include/have_ndb.inc +-- source include/ndb_default_cluster.inc +-- source include/not_embedded.inc + +--disable_warnings +use test; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9,t10; +--enable_warnings + +# basic datatypes +create table t1 + (pk int key + ,a1 BIT(1), a2 BIT(5), a3 BIT(33), a4 BIT(63), a5 BIT(64) + ,b1 TINYINT, b2 TINYINT UNSIGNED + ,c1 SMALLINT, c2 SMALLINT UNSIGNED + ,d1 INT, d2 INT UNSIGNED + ,e1 BIGINT, e2 BIGINT UNSIGNED + ,f1 CHAR(1) BINARY, f2 CHAR(32) BINARY, f3 CHAR(255) BINARY + ,g1 VARCHAR(32) BINARY, g2 VARCHAR(255) BINARY, g3 VARCHAR(1000) BINARY + ,h1 BINARY(1), h2 BINARY(8), h3 BINARY(255) + ,i1 VARBINARY(32), i2 VARBINARY(255), i3 VARBINARY(1000) + ) engine myisam; + +# max values +insert into t1 values + (1 + ,0x1, 0x17, 0x789a, 0x789abcde, 0xfedc0001 + ,127, 255 + ,32767, 65535 + ,2147483647, 4294967295 + ,9223372036854775807, 18446744073709551615 + ,'1','12345678901234567890123456789012','123456789' + ,'1','12345678901234567890123456789012','123456789' + ,0x12,0x123456789abcdef0, 0x012345 + ,0x12,0x123456789abcdef0, 0x00123450 + ); + +# min values +insert into t1 values + (2 + ,0, 0, 0, 0, 0 + ,-128, 0 + ,-32768, 0 + ,-2147483648, 0 + ,-9223372036854775808, 0 + ,'','','' + ,'','','' + ,0x0,0x0,0x0 + ,0x0,0x0,0x0 + ); + +# null values +insert into t1 values + (3 + ,NULL,NULL,NULL,NULL,NULL + ,NULL,NULL + ,NULL,NULL + ,NULL,NULL + ,NULL,NULL + ,NULL,NULL,NULL + ,NULL,NULL,NULL + ,NULL,NULL,NULL + ,NULL,NULL,NULL + ); + +--vertical_results +select pk + ,hex(a1), hex(a2), hex(a3), hex(a4), hex(a5) + ,b1, b2 + ,c1 , c2 + ,d1 , d2 + ,e1 , e2 + ,f1 , f2, f3 + ,g1 , g2, g3 + ,hex(h1), hex(h2), hex(h3) + ,hex(i1), hex(i2), hex(i3) + from t1 order by pk; + +alter table t1 engine ndb; + +select pk + ,hex(a1), hex(a2), hex(a3), hex(a4), hex(a5) + ,b1, b2 + ,c1 , c2 + ,d1 , d2 + ,e1 , e2 + ,f1 , f2, f3 + ,g1 , g2, g3 + ,hex(h1), hex(h2), hex(h3) + ,hex(i1), hex(i2), hex(i3) + from t1 order by pk; +--horizontal_results + +--source include/ndb_backup.inc + +--let ndb_restore_filter=test t1 +--let ndb_restore_opts=--verbose=0 --print_data --hex --fields-terminated-by=";" +--source include/ndb_backup_print.inc + +--let ndb_restore_filter=test t1 +--let ndb_restore_opts=--verbose=0 --print_data --hex --fields-terminated-by="," --fields-optionally-enclosed-by="'" +--source include/ndb_backup_print.inc + + +drop table t1; + +# some binary char tests with trailing spaces +create table t1 + (pk int key + ,f1 CHAR(1) BINARY, f2 CHAR(32) BINARY, f3 CHAR(255) BINARY + ,g1 VARCHAR(32) BINARY, g2 VARCHAR(255) BINARY, g3 VARCHAR(1000) BINARY + ,h1 BINARY(1), h2 BINARY(9), h3 BINARY(255) + ,i1 VARBINARY(32), i2 VARBINARY(255), i3 VARBINARY(1000) + ) engine ndb; + +insert into t1 values + (1 + ,'1','12345678901234567890123456789012','123456789 ' + ,'1 ','12345678901234567890123456789012 ','123456789 ' + ,0x20,0x123456789abcdef020, 0x012345000020 + ,0x1200000020,0x123456789abcdef000000020, 0x00123450000020 + ); + +create table t2 (pk int key, a int) engine ndb; +create table t3 (pk int key, a int) engine ndb; +create table t4 (pk int key, a int) engine ndb; + +insert into t2 values (1,11),(2,12),(3,13),(4,14),(5,15); +insert into t3 values (1,21),(2,22),(3,23),(4,24),(5,25); +insert into t4 values (1,31),(2,32),(3,33),(4,34),(5,35); + +--source include/ndb_backup.inc +--let ndb_restore_opts=--verbose=0 --print_data --hex --fields-enclosed-by="'" --fields-optionally-enclosed-by="X" +--let ndb_restore_filter=test t1 +--source include/ndb_backup_print.inc + +--exec rm -f $MYSQLTEST_VARDIR/tmp/t1.txt +--exec rm -f $MYSQLTEST_VARDIR/tmp/t2.txt +--exec rm -f $MYSQLTEST_VARDIR/tmp/t3.txt +--exec rm -f $MYSQLTEST_VARDIR/tmp/t4.txt + +--let ndb_restore_opts=--verbose=0 --print_data --hex --tab $MYSQLTEST_VARDIR/tmp --append +--let ndb_restore_filter=test +--source include/ndb_backup_print.inc + +--let $message= t1 +--source include/show_msg.inc +--exec sort $MYSQLTEST_VARDIR/tmp/t1.txt +--let $message= t2 +--source include/show_msg.inc +--exec sort $MYSQLTEST_VARDIR/tmp/t2.txt +--let $message= t3 +--source include/show_msg.inc +--exec sort $MYSQLTEST_VARDIR/tmp/t3.txt +--let $message= t4 +--source include/show_msg.inc +--exec sort $MYSQLTEST_VARDIR/tmp/t4.txt + +--exec rm -f $MYSQLTEST_VARDIR/tmp/t1.txt +--exec rm -f $MYSQLTEST_VARDIR/tmp/t2.txt +--exec rm -f $MYSQLTEST_VARDIR/tmp/t3.txt +--exec rm -f $MYSQLTEST_VARDIR/tmp/t4.txt + +# now test some other datatypes +drop table t1; +create table t1 + (pk int key + ,a1 MEDIUMINT, a2 MEDIUMINT UNSIGNED + ) engine ndb; + +# max values +insert into t1 values(1, 8388607, 16777215); +# min values +insert into t1 values(2, -8388608, 0); +# small values +insert into t1 values(3, -1, 1); + +# backup and print +--source include/ndb_backup.inc + +--let ndb_restore_filter=test t1 +--let ndb_restore_opts=--verbose=0 --print_data --hex --fields-terminated-by=";" +--source include/ndb_backup_print.inc + +# clean up +drop table t1; +drop table t2; +drop table t3; +drop table t4; diff --git a/mysql-test/t/ndb_single_user.test b/mysql-test/t/ndb_single_user.test index c655124f79f..f2f47becb0c 100644 --- a/mysql-test/t/ndb_single_user.test +++ b/mysql-test/t/ndb_single_user.test @@ -51,34 +51,67 @@ insert into t1 select * from t2; --connection server2 --error 1051 drop table t1; ---error 1146 -#--error 1296 +--error 1296 create index new_index on t1 (c); ---error 1146 -#--error 1296 +--error 1296 insert into t1 values (1,1,0),(2,2,0),(3,3,0),(4,4,0),(5,5,0),(6,6,0),(7,7,0),(8,8,0),(9,9,0),(10,10,0); ---error 1146 -#--error 1296 +--error 1296 select * from t1 where a = 1; ---error 1146 -#--error 1296 +--error 1296 select * from t1 where b = 4; ---error 1146 -#--error 1296 +--error 1296 update t1 set b=102 where a = 2; ---error 1146 -#--error 1296 +--error 1296 update t1 set b=103 where b = 3; ---error 1146 -#--error 1296 +--error 1296 update t1 set b=b+100; ---error 1146 -#--error 1296 +--error 1296 update t1 set b=b+100 where a > 7; --exec $NDB_MGM --no-defaults --ndb-connectstring="localhost:$NDBCLUSTER_PORT" -e "exit single user mode" >> $NDB_TOOLS_OUTPUT --exec $NDB_TOOLS_DIR/ndb_waiter --no-defaults >> $NDB_TOOLS_OUTPUT +# +# we should be able to run transaction while in single user mode +# +--connection server1 +BEGIN; +update t1 set b=b+100 where a=1; + +--connection server2 +BEGIN; +update t1 set b=b+100 where a=2; + +# enter single user mode +--exec $NDB_MGM --no-defaults --ndb-connectstring="localhost:$NDBCLUSTER_PORT" -e "enter single user mode $node_id" >> $NDB_TOOLS_OUTPUT +--exec $NDB_TOOLS_DIR/ndb_waiter --no-defaults --ndb-connectstring="localhost:$NDBCLUSTER_PORT" --single-user >> $NDB_TOOLS_OUTPUT + +--connection server1 +update t1 set b=b+100 where a=3; +COMMIT; + +# while on other mysqld it should be aborted +--connection server2 +--error 1296 +update t1 set b=b+100 where a=4; +--error 1296 +COMMIT; + +# Bug #25275 SINGLE USER MODE prevents ALTER on non-ndb +# tables for other mysqld nodes +--connection server2 +create table t2 (a int) engine myisam; +alter table t2 add column (b int); + +# exit single user mode +--exec $NDB_MGM --no-defaults --ndb-connectstring="localhost:$NDBCLUSTER_PORT" -e "exit single user mode" >> $NDB_TOOLS_OUTPUT +--exec $NDB_TOOLS_DIR/ndb_waiter --no-defaults >> $NDB_TOOLS_OUTPUT + # cleanup +--connection server2 +drop table t2; --connection server1 drop table t1; + +# End of 5.0 tests + diff --git a/mysql-test/t/range.test b/mysql-test/t/range.test index 72be4e12c6a..b8d4b91f03d 100644 --- a/mysql-test/t/range.test +++ b/mysql-test/t/range.test @@ -568,6 +568,149 @@ SELECT s.oxid FROM t1 v, t1 s DROP TABLE t1; +# BUG#26624 high mem usage (crash) in range optimizer (depends on order of fields in where) +create table t1 ( + c1 char(10), c2 char(10), c3 char(10), c4 char(10), + c5 char(10), c6 char(10), c7 char(10), c8 char(10), + c9 char(10), c10 char(10), c11 char(10), c12 char(10), + c13 char(10), c14 char(10), c15 char(10), c16 char(10), + index(c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12,c13,c14,c15,c16) +); +insert into t1 (c1) values ('1'),('1'),('1'),('1'); + +# This must run without crash and fast: +select * from t1 where + c1 in ("abcdefgh", "123456789", "qwertyuio", "asddfgh", + "abcdefg1", "123456781", "qwertyui1", "asddfg1", + "abcdefg2", "123456782", "qwertyui2", "asddfg2", + "abcdefg3", "123456783", "qwertyui3", "asddfg3", + "abcdefg4", "123456784", "qwertyui4", "asddfg4", + "abcdefg5", "123456785", "qwertyui5", "asddfg5", + "abcdefg6", "123456786", "qwertyui6", "asddfg6", + "abcdefg7", "123456787", "qwertyui7", "asddfg7", + "abcdefg8", "123456788", "qwertyui8", "asddfg8", + "abcdefg9", "123456789", "qwertyui9", "asddfg9", + "abcdefgA", "12345678A", "qwertyuiA", "asddfgA", + "abcdefgB", "12345678B", "qwertyuiB", "asddfgB", + "abcdefgC", "12345678C", "qwertyuiC", "asddfgC") + and c2 in ("abcdefgh", "123456789", "qwertyuio", "asddfgh", + "abcdefg1", "123456781", "qwertyui1", "asddfg1", + "abcdefg2", "123456782", "qwertyui2", "asddfg2", + "abcdefg3", "123456783", "qwertyui3", "asddfg3", + "abcdefg4", "123456784", "qwertyui4", "asddfg4", + "abcdefg5", "123456785", "qwertyui5", "asddfg5", + "abcdefg6", "123456786", "qwertyui6", "asddfg6", + "abcdefg7", "123456787", "qwertyui7", "asddfg7", + "abcdefg8", "123456788", "qwertyui8", "asddfg8", + "abcdefg9", "123456789", "qwertyui9", "asddfg9", + "abcdefgA", "12345678A", "qwertyuiA", "asddfgA", + "abcdefgB", "12345678B", "qwertyuiB", "asddfgB", + "abcdefgC", "12345678C", "qwertyuiC", "asddfgC") + and c3 in ("abcdefgh", "123456789", "qwertyuio", "asddfgh", + "abcdefg1", "123456781", "qwertyui1", "asddfg1", + "abcdefg2", "123456782", "qwertyui2", "asddfg2", + "abcdefg3", "123456783", "qwertyui3", "asddfg3", + "abcdefg4", "123456784", "qwertyui4", "asddfg4", + "abcdefg5", "123456785", "qwertyui5", "asddfg5", + "abcdefg6", "123456786", "qwertyui6", "asddfg6", + "abcdefg7", "123456787", "qwertyui7", "asddfg7", + "abcdefg8", "123456788", "qwertyui8", "asddfg8", + "abcdefg9", "123456789", "qwertyui9", "asddfg9", + "abcdefgA", "12345678A", "qwertyuiA", "asddfgA", + "abcdefgB", "12345678B", "qwertyuiB", "asddfgB", + "abcdefgC", "12345678C", "qwertyuiC", "asddfgC") + and c4 in ("abcdefgh", "123456789", "qwertyuio", "asddfgh", + "abcdefg1", "123456781", "qwertyui1", "asddfg1", + "abcdefg2", "123456782", "qwertyui2", "asddfg2", + "abcdefg3", "123456783", "qwertyui3", "asddfg3", + "abcdefg4", "123456784", "qwertyui4", "asddfg4", + "abcdefg5", "123456785", "qwertyui5", "asddfg5", + "abcdefg6", "123456786", "qwertyui6", "asddfg6", + "abcdefg7", "123456787", "qwertyui7", "asddfg7", + "abcdefg8", "123456788", "qwertyui8", "asddfg8", + "abcdefg9", "123456789", "qwertyui9", "asddfg9", + "abcdefgA", "12345678A", "qwertyuiA", "asddfgA", + "abcdefgB", "12345678B", "qwertyuiB", "asddfgB", + "abcdefgC", "12345678C", "qwertyuiC", "asddfgC") + and c5 in ("abcdefgh", "123456789", "qwertyuio", "asddfgh", + "abcdefg1", "123456781", "qwertyui1", "asddfg1", + "abcdefg2", "123456782", "qwertyui2", "asddfg2", + "abcdefg3", "123456783", "qwertyui3", "asddfg3", + "abcdefg4", "123456784", "qwertyui4", "asddfg4", + "abcdefg5", "123456785", "qwertyui5", "asddfg5", + "abcdefg6", "123456786", "qwertyui6", "asddfg6", + "abcdefg7", "123456787", "qwertyui7", "asddfg7", + "abcdefg8", "123456788", "qwertyui8", "asddfg8", + "abcdefg9", "123456789", "qwertyui9", "asddfg9", + "abcdefgA", "12345678A", "qwertyuiA", "asddfgA", + "abcdefgB", "12345678B", "qwertyuiB", "asddfgB", + "abcdefgC", "12345678C", "qwertyuiC", "asddfgC") + and c6 in ("abcdefgh", "123456789", "qwertyuio", "asddfgh", + "abcdefg1", "123456781", "qwertyui1", "asddfg1", + "abcdefg2", "123456782", "qwertyui2", "asddfg2", + "abcdefg3", "123456783", "qwertyui3", "asddfg3", + "abcdefg4", "123456784", "qwertyui4", "asddfg4", + "abcdefg5", "123456785", "qwertyui5", "asddfg5", + "abcdefg6", "123456786", "qwertyui6", "asddfg6", + "abcdefg7", "123456787", "qwertyui7", "asddfg7", + "abcdefg8", "123456788", "qwertyui8", "asddfg8", + "abcdefg9", "123456789", "qwertyui9", "asddfg9", + "abcdefgA", "12345678A", "qwertyuiA", "asddfgA", + "abcdefgB", "12345678B", "qwertyuiB", "asddfgB", + "abcdefgC", "12345678C", "qwertyuiC", "asddfgC") + and c7 in ("abcdefgh", "123456789", "qwertyuio", "asddfgh", + "abcdefg1", "123456781", "qwertyui1", "asddfg1", + "abcdefg2", "123456782", "qwertyui2", "asddfg2", + "abcdefg3", "123456783", "qwertyui3", "asddfg3", + "abcdefg4", "123456784", "qwertyui4", "asddfg4", + "abcdefg5", "123456785", "qwertyui5", "asddfg5", + "abcdefg6", "123456786", "qwertyui6", "asddfg6", + "abcdefg7", "123456787", "qwertyui7", "asddfg7", + "abcdefg8", "123456788", "qwertyui8", "asddfg8", + "abcdefg9", "123456789", "qwertyui9", "asddfg9", + "abcdefgA", "12345678A", "qwertyuiA", "asddfgA", + "abcdefgB", "12345678B", "qwertyuiB", "asddfgB", + "abcdefgC", "12345678C", "qwertyuiC", "asddfgC") + and c8 in ("abcdefgh", "123456789", "qwertyuio", "asddfgh", + "abcdefg1", "123456781", "qwertyui1", "asddfg1", + "abcdefg2", "123456782", "qwertyui2", "asddfg2", + "abcdefg3", "123456783", "qwertyui3", "asddfg3", + "abcdefg4", "123456784", "qwertyui4", "asddfg4", + "abcdefg5", "123456785", "qwertyui5", "asddfg5", + "abcdefg6", "123456786", "qwertyui6", "asddfg6", + "abcdefg7", "123456787", "qwertyui7", "asddfg7", + "abcdefg8", "123456788", "qwertyui8", "asddfg8", + "abcdefg9", "123456789", "qwertyui9", "asddfg9", + "abcdefgA", "12345678A", "qwertyuiA", "asddfgA", + "abcdefgB", "12345678B", "qwertyuiB", "asddfgB", + "abcdefgC", "12345678C", "qwertyuiC", "asddfgC") + and c9 in ("abcdefgh", "123456789", "qwertyuio", "asddfgh", + "abcdefg1", "123456781", "qwertyui1", "asddfg1", + "abcdefg2", "123456782", "qwertyui2", "asddfg2", + "abcdefg3", "123456783", "qwertyui3", "asddfg3", + "abcdefg4", "123456784", "qwertyui4", "asddfg4", + "abcdefg5", "123456785", "qwertyui5", "asddfg5", + "abcdefg6", "123456786", "qwertyui6", "asddfg6", + "abcdefg7", "123456787", "qwertyui7", "asddfg7", + "abcdefg8", "123456788", "qwertyui8", "asddfg8", + "abcdefg9", "123456789", "qwertyui9", "asddfg9", + "abcdefgA", "12345678A", "qwertyuiA", "asddfgA", + "abcdefgB", "12345678B", "qwertyuiB", "asddfgB", + "abcdefgC", "12345678C", "qwertyuiC", "asddfgC") + and c10 in ("abcdefgh", "123456789", "qwertyuio", "asddfgh", + "abcdefg1", "123456781", "qwertyui1", "asddfg1", + "abcdefg2", "123456782", "qwertyui2", "asddfg2", + "abcdefg3", "123456783", "qwertyui3", "asddfg3", + "abcdefg4", "123456784", "qwertyui4", "asddfg4", + "abcdefg5", "123456785", "qwertyui5", "asddfg5", + "abcdefg6", "123456786", "qwertyui6", "asddfg6", + "abcdefg7", "123456787", "qwertyui7", "asddfg7", + "abcdefg8", "123456788", "qwertyui8", "asddfg8", + "abcdefg9", "123456789", "qwertyui9", "asddfg9", + "abcdefgA", "12345678A", "qwertyuiA", "asddfgA", + "abcdefgB", "12345678B", "qwertyuiB", "asddfgB", + "abcdefgC", "12345678C", "qwertyuiC", "asddfgC"); +drop table t1; --echo End of 4.1 tests # diff --git a/mysql-test/t/row.test b/mysql-test/t/row.test index 63c611e6be6..1d5c7a543ea 100644 --- a/mysql-test/t/row.test +++ b/mysql-test/t/row.test @@ -139,3 +139,20 @@ EXPLAIN EXTENDED SELECT * FROM t1,t2 WHERE t2.a=t1.a AND (t2.b,t2.c)=(2,1); SELECT * FROM t1,t2 WHERE t2.a=t1.a AND (t2.b,t2.c)=(2,1); DROP TABLE t1,t2; + +# +# Bug #27154: crash (memory corruption) when using row equalities +# + +CREATE TABLE t1( + a int, b int, c int, d int, e int, f int, g int, h int, + PRIMARY KEY (a,b,c,d,e,f,g) +); +INSERT INTO t1 VALUES (1,2,3,4,5,6,7,99); + +SELECT h FROM t1 WHERE (a,b,c,d,e,f,g)=(1,2,3,4,5,6,7); + +SET @x:= (SELECT h FROM t1 WHERE (a,b,c,d,e,f,g)=(1,2,3,4,5,6,7)); +SELECT @x; + +DROP TABLE t1; diff --git a/mysql-test/t/rpl_dual_pos_advance.test b/mysql-test/t/rpl_dual_pos_advance.test index 518fa9df885..074aeec63b1 100644 --- a/mysql-test/t/rpl_dual_pos_advance.test +++ b/mysql-test/t/rpl_dual_pos_advance.test @@ -106,3 +106,9 @@ connection slave; sync_with_master; # End of 4.1 tests + +# Cleanup +# The A->B->A replication causes the master to start writing relay logs +# in var/run, remove them +remove_file $MYSQLTEST_VARDIR/run/master-relay-bin.000001; +remove_file $MYSQLTEST_VARDIR/run/master-relay-bin.index; diff --git a/mysql-test/t/rpl_misc_functions.test b/mysql-test/t/rpl_misc_functions.test index 6e0bda90503..f00beff583a 100644 --- a/mysql-test/t/rpl_misc_functions.test +++ b/mysql-test/t/rpl_misc_functions.test @@ -28,10 +28,76 @@ create table t2 like t1; eval load data local infile '$MYSQLTEST_VARDIR/master-data/test/rpl_misc_functions.outfile' into table t2; # compare them with the replica; the SELECT below should return no row select * from t1, t2 where (t1.id=t2.id) and not(t1.i=t2.i and t1.r1=t2.r1 and t1.r2=t2.r2 and t1.p=t2.p); -stop slave; -drop table t1; connection master; drop table t1; # End of 4.1 tests + +# +# BUG#25543 test calling rand() multiple times on the master in +# a stored procedure. +# + +--disable_warnings +DROP TABLE IF EXISTS t1; +--enable_warnings + +CREATE TABLE t1 (col_a double default NULL); + +DELIMITER |; + +# Use a SP that calls rand() multiple times +CREATE PROCEDURE test_replication_sp1() +BEGIN + INSERT INTO t1 VALUES (rand()), (rand()); + INSERT INTO t1 VALUES (rand()); +END| + +# Use a SP that calls another SP to call rand() multiple times +CREATE PROCEDURE test_replication_sp2() +BEGIN + CALL test_replication_sp1(); + CALL test_replication_sp1(); +END| + +# Use a SF that calls rand() multiple times +CREATE FUNCTION test_replication_sf() RETURNS DOUBLE DETERMINISTIC +BEGIN + RETURN (rand() + rand()); +END| + +DELIMITER ;| + +# Exercise the functions and procedures then compare the results on +# the master to those on the slave. +CALL test_replication_sp1(); +CALL test_replication_sp2(); +INSERT INTO t1 VALUES (test_replication_sf()); +INSERT INTO t1 VALUES (test_replication_sf()); +INSERT INTO t1 VALUES (test_replication_sf()); + +# Record the results of the query on the master +--exec $MYSQL --port=$MASTER_MYPORT test -e "SELECT * FROM test.t1" > $MYSQLTEST_VARDIR/tmp/rpl_rand_master.sql + +--sync_slave_with_master + +# Record the results of the query on the slave +--exec $MYSQL --port=$SLAVE_MYPORT test -e "SELECT * FROM test.t1" > $MYSQLTEST_VARDIR/tmp/rpl_rand_slave.sql + +# Compare the results from the master to the slave. +--exec diff $MYSQLTEST_VARDIR/tmp/rpl_rand_master.sql $MYSQLTEST_VARDIR/tmp/rpl_rand_slave.sql + +# Cleanup +connection master; +--disable_warnings +DROP PROCEDURE IF EXISTS test_replication_sp1; +DROP PROCEDURE IF EXISTS test_replication_sp2; +DROP FUNCTION IF EXISTS test_replication_sf; +DROP TABLE IF EXISTS t1; +--enable_warnings +--sync_slave_with_master + +# If all is good, when can cleanup our dump files. +--system rm $MYSQLTEST_VARDIR/tmp/rpl_rand_master.sql +--system rm $MYSQLTEST_VARDIR/tmp/rpl_rand_slave.sql diff --git a/mysql-test/t/rpl_packet.test b/mysql-test/t/rpl_packet.test index d01979a4731..db6f475dc94 100644 --- a/mysql-test/t/rpl_packet.test +++ b/mysql-test/t/rpl_packet.test @@ -36,4 +36,37 @@ save_master_pos; connection slave; sync_with_master; +# +# Bug #23755: Replicated event larger that max_allowed_packet infinitely re-transmits +# +# Check that a situation when the size of event on the master is greater than +# max_allowed_packet on the slave does not lead to infinite re-transmits. + +connection master; + +# Change the max packet size on master + +SET @@global.max_allowed_packet=4096; +SET @@global.net_buffer_length=4096; + +# Restart slave for new setting to take effect +connection slave; +STOP SLAVE; +START SLAVE; + +# Reconnect to master for new setting to take effect +disconnect master; +connect (master, localhost, root) +connection master; + +CREATE TABLe `t1` (`f1` LONGTEXT) ENGINE=MyISAM; + +INSERT INTO `t1`(`f1`) VALUES ('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa2048'); + +# The slave I/O thread must stop after trying to read the above event +connection slave; +sleep 2; +SHOW STATUS LIKE 'Slave_running'; + + # End of tests diff --git a/mysql-test/t/select.test b/mysql-test/t/select.test index 883ea7bf0b0..c5c7d07ee25 100644 --- a/mysql-test/t/select.test +++ b/mysql-test/t/select.test @@ -3111,6 +3111,8 @@ DROP TABLE t1,t2,t3; CREATE TABLE t1 (a INT, b INT, KEY (a)); INSERT INTO t1 VALUES (1,1),(2,2); EXPLAIN SELECT 1 FROM t1 WHERE a = 1; EXPLAIN SELECT 1 FROM t1 IGNORE INDEX FOR JOIN (a) WHERE a = 1; +EXPLAIN SELECT 1 FROM t1 USE INDEX FOR JOIN (a) WHERE a = 1; +EXPLAIN SELECT 1 FROM t1 FORCE INDEX FOR JOIN (a) WHERE a = 1; DROP TABLE t1; # diff --git a/mysql-test/t/sp-destruct.test b/mysql-test/t/sp-destruct.test index 4f5f1cdcb9b..13ddaa43fad 100644 --- a/mysql-test/t/sp-destruct.test +++ b/mysql-test/t/sp-destruct.test @@ -7,13 +7,10 @@ # In the case of trouble you might want to skip this. # -# We're using --system things that probably doesn't work on Windows. ---source include/not_windows.inc - # Backup proc table ---system rm -rf $MYSQLTEST_VARDIR/master-data/mysql/backup ---system mkdir $MYSQLTEST_VARDIR/master-data/mysql/backup ---system cp $MYSQLTEST_VARDIR/master-data/mysql/proc.* $MYSQLTEST_VARDIR/master-data/mysql/backup/ +--copy_file $MYSQLTEST_VARDIR/master-data/mysql/proc.frm $MYSQLTEST_VARDIR/tmp/proc.frm +--copy_file $MYSQLTEST_VARDIR/master-data/mysql/proc.MYD $MYSQLTEST_VARDIR/tmp/proc.MYD +--copy_file $MYSQLTEST_VARDIR/master-data/mysql/proc.MYI $MYSQLTEST_VARDIR/tmp/proc.MYI use test; @@ -45,22 +42,25 @@ insert into t1 values (0); flush table mysql.proc; # Thrashing the .frm file ---system echo 'saljdlfa' > $MYSQLTEST_VARDIR/master-data/mysql/proc.frm ---replace_result $MYSQLTEST_VARDIR . master-data// '' +--write_file $MYSQLTEST_VARDIR/master-data/mysql/proc.frm +saljdfa +EOF +--replace_result $MYSQLTEST_VARDIR . master-data// '' '\\' '/' --error ER_NOT_FORM_FILE call bug14233(); ---replace_result $MYSQLTEST_VARDIR . master-data// '' +--replace_result $MYSQLTEST_VARDIR . master-data// '' '\\' '/' --error ER_NOT_FORM_FILE create view v1 as select bug14233_f(); ---replace_result $MYSQLTEST_VARDIR . master-data// '' +--replace_result $MYSQLTEST_VARDIR . master-data// '' '\\' '/' --error ER_NOT_FORM_FILE insert into t1 values (0); - flush table mysql.proc; # Drop the mysql.proc table ---system rm $MYSQLTEST_VARDIR/master-data/mysql/proc.* +--remove_file $MYSQLTEST_VARDIR/master-data/mysql/proc.frm +--remove_file $MYSQLTEST_VARDIR/master-data/mysql/proc.MYD +--remove_file $MYSQLTEST_VARDIR/master-data/mysql/proc.MYI --error ER_NO_SUCH_TABLE call bug14233(); --error ER_NO_SUCH_TABLE @@ -69,8 +69,12 @@ create view v1 as select bug14233_f(); insert into t1 values (0); # Restore mysql.proc ---system mv $MYSQLTEST_VARDIR/master-data/mysql/backup/* $MYSQLTEST_VARDIR/master-data/mysql/ ---system rmdir $MYSQLTEST_VARDIR/master-data/mysql/backup +--copy_file $MYSQLTEST_VARDIR/tmp/proc.frm $MYSQLTEST_VARDIR/master-data/mysql/proc.frm +--copy_file $MYSQLTEST_VARDIR/tmp/proc.MYD $MYSQLTEST_VARDIR/master-data/mysql/proc.MYD +--copy_file $MYSQLTEST_VARDIR/tmp/proc.MYI $MYSQLTEST_VARDIR/master-data/mysql/proc.MYI +--remove_file $MYSQLTEST_VARDIR/tmp/proc.frm +--remove_file $MYSQLTEST_VARDIR/tmp/proc.MYD +--remove_file $MYSQLTEST_VARDIR/tmp/proc.MYI flush table mysql.proc; flush privileges; diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index 1655422c51e..182b9b27ef7 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -2741,4 +2741,71 @@ SELECT * FROM (SELECT 'this is ' 'a test.' AS col1, a AS t2 FROM t1) t; DROP table t1; +# +# Bug #27257: COUNT(*) aggregated in outer query +# + +CREATE TABLE t1 (a int, b int); +CREATE TABLE t2 (m int, n int); +INSERT INTO t1 VALUES (2,2), (2,2), (3,3), (3,3), (3,3), (4,4); +INSERT INTO t2 VALUES (1,11), (2,22), (3,32), (4,44), (4,44); + +SELECT COUNT(*), a, + (SELECT m FROM t2 WHERE m = count(*) LIMIT 1) + FROM t1 GROUP BY a; + +SELECT COUNT(*), a, + (SELECT MIN(m) FROM t2 WHERE m = count(*)) + FROM t1 GROUP BY a; + +SELECT COUNT(*), a + FROM t1 GROUP BY a + HAVING (SELECT MIN(m) FROM t2 WHERE m = count(*)) > 1; + +DROP TABLE t1,t2; + +# +# Bug #27229: GROUP_CONCAT in subselect with COUNT() as an argument +# + +CREATE TABLE t1 (a int, b int); +CREATE TABLE t2 (m int, n int); +INSERT INTO t1 VALUES (2,2), (2,2), (3,3), (3,3), (3,3), (4,4); +INSERT INTO t2 VALUES (1,11), (2,22), (3,32), (4,44), (4,44); +SELECT COUNT(*) c, a, + (SELECT GROUP_CONCAT(COUNT(a)) FROM t2 WHERE m = a) + FROM t1 GROUP BY a; + +SELECT COUNT(*) c, a, + (SELECT GROUP_CONCAT(COUNT(a)+1) FROM t2 WHERE m = a) + FROM t1 GROUP BY a; + +DROP table t1,t2; + +# +# Bug #27348: SET FUNCTION used in a subquery from WHERE condition +# + +CREATE TABLE t1 (a int, b int); +INSERT INTO t1 VALUES (2,22),(1,11),(2,22); + +SELECT a FROM t1 WHERE (SELECT COUNT(b) FROM DUAL) > 0 GROUP BY a; +SELECT a FROM t1 WHERE (SELECT COUNT(b) FROM DUAL) > 1 GROUP BY a; + +SELECT a FROM t1 t0 + WHERE (SELECT COUNT(t0.b) FROM t1 t WHERE t.b>20) GROUP BY a; + +SET @@sql_mode='ansi'; +--error 1111 +SELECT a FROM t1 WHERE (SELECT COUNT(b) FROM DUAL) > 0 GROUP BY a; +--error 1111 +SELECT a FROM t1 WHERE (SELECT COUNT(b) FROM DUAL) > 1 GROUP BY a; + +--error 1111 +SELECT a FROM t1 t0 + WHERE (SELECT COUNT(t0.b) FROM t1 t WHERE t.b>20) GROUP BY a; + +SET @@sql_mode=default; + +DROP TABLE t1; diff --git a/mysql-test/t/subselect3.test b/mysql-test/t/subselect3.test index ed8480ba464..e8eae3e2452 100644 --- a/mysql-test/t/subselect3.test +++ b/mysql-test/t/subselect3.test @@ -489,3 +489,45 @@ select a, b, (a,b) in (select a, min(b) from t2 group by a) Z from t1; drop table t1,t2; +# +# Bug #24484: Aggregate function used in column list subquery gives erroneous +# error +# +CREATE TABLE t1 (a int, b INT, c CHAR(10) NOT NULL, PRIMARY KEY (a, b)); +INSERT INTO t1 VALUES (1,1,'a'), (1,2,'b'), (1,3,'c'), (1,4,'d'), (1,5,'e'), + (2,1,'f'), (2,2,'g'), (2,3,'h'), (3,4,'i'),(3,3,'j'), (3,2,'k'), (3,1,'l'), + (1,9,'m'); +CREATE TABLE t2 (a int, b INT, c CHAR(10) NOT NULL, PRIMARY KEY (a, b)); +INSERT INTO t2 SELECT * FROM t1; + +# Gives error, but should work since it is (a, b) is the PK so only one +# given match possible +SELECT a, MAX(b), (SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b)) + as test FROM t1 GROUP BY a; +SELECT * FROM t1 GROUP by t1.a + HAVING (MAX(t1.b) > (SELECT MAX(t2.b) FROM t2 WHERE t2.c < t1.c + HAVING MAX(t2.b+t1.a) < 10)); +#FIXME: Enable this test after fixing bug #27321 +#SELECT a, AVG(b), (SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=AVG(t1.b)) +# AS test FROM t1 GROUP BY a; + +SELECT a,b,c FROM t1 WHERE b in (9,3,4) ORDER BY b,c; + +SELECT a, MAX(b), + (SELECT COUNT(DISTINCT t.c) FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b) + LIMIT 1) + as cnt, + (SELECT t.b FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b) LIMIT 1) + as t_b, + (SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b) LIMIT 1) + as t_b, + (SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b) ORDER BY t.c LIMIT 1) + as t_b + FROM t1 GROUP BY a; + +SELECT a, MAX(b), + (SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b) LIMIT 1) as test + FROM t1 GROUP BY a; + + +DROP TABLE t1, t2; diff --git a/mysql-test/t/temp_table.test b/mysql-test/t/temp_table.test index 8cb9e34ca08..90f868f5932 100644 --- a/mysql-test/t/temp_table.test +++ b/mysql-test/t/temp_table.test @@ -163,3 +163,19 @@ DROP TABLE t1; --echo End of 4.1 tests. + +# +# Bug #24791: Union with AVG-groups generates wrong results +# +CREATE TABLE t1 ( c FLOAT( 20, 14 ) ); +INSERT INTO t1 VALUES( 12139 ); + +CREATE TABLE t2 ( c FLOAT(30,18) ); +INSERT INTO t2 VALUES( 123456 ); + +SELECT AVG( c ) FROM t1 UNION SELECT 1; +SELECT 1 UNION SELECT AVG( c ) FROM t1; +SELECT 1 UNION SELECT * FROM t2 UNION SELECT 1; +SELECT c/1 FROM t1 UNION SELECT 1; + +DROP TABLE t1, t2; diff --git a/mysql-test/t/type_datetime.test b/mysql-test/t/type_datetime.test index 3ad6bdc53e4..3d04eb85cf3 100644 --- a/mysql-test/t/type_datetime.test +++ b/mysql-test/t/type_datetime.test @@ -113,6 +113,12 @@ insert into t1 values ("00-00-00"), ("00-00-00 00:00:00"); select * from t1; drop table t1; +# +# Bug #16546 DATETIME+0 not always coerced the same way +# +select cast('2006-12-05 22:10:10' as datetime) + 0; + + # End of 4.1 tests # diff --git a/mysql-test/t/type_newdecimal.test b/mysql-test/t/type_newdecimal.test index f315e88fd0e..4c6098d2121 100644 --- a/mysql-test/t/type_newdecimal.test +++ b/mysql-test/t/type_newdecimal.test @@ -1120,3 +1120,14 @@ drop table t1; # select cast(19999999999999999999 as unsigned); +# +# Bug #24558: Increasing decimal column length causes data loss +# + +create table t1(a decimal(18)); +insert into t1 values(123456789012345678); +alter table t1 modify column a decimal(19); +select * from t1; +drop table t1; + +--echo End of 5.0 tests diff --git a/mysql-test/t/union.test b/mysql-test/t/union.test index 29a9ee36481..22f09466b1c 100644 --- a/mysql-test/t/union.test +++ b/mysql-test/t/union.test @@ -868,4 +868,13 @@ SELECT a FROM (SELECT a FROM t1 UNION SELECT a FROM t1 ORDER BY c) AS test; DROP TABLE t1; +# +# Bug#23345: Wrongly allowed INTO in a non-last select of a UNION. +# +--error 1221 +(select 1 into @var) union (select 1); +(select 1) union (select 1 into @var); +select @var; +--error 1172 +(select 2) union (select 1 into @var); --echo End of 5.0 tests diff --git a/mysql-test/t/view_grant.test b/mysql-test/t/view_grant.test index 0785b74dd47..b45afe4f312 100644 --- a/mysql-test/t/view_grant.test +++ b/mysql-test/t/view_grant.test @@ -1034,5 +1034,36 @@ DROP DATABASE mysqltest_db2; DROP USER mysqltest_u1@localhost; DROP USER mysqltest_u2@localhost; +# +# Bug#26813: The SUPER privilege is wrongly required to alter a view created +# by another user. +# +connection root; +CREATE DATABASE db26813; +USE db26813; +CREATE TABLE t1(f1 INT, f2 INT); +CREATE VIEW v1 AS SELECT f1 FROM t1; +CREATE VIEW v2 AS SELECT f1 FROM t1; +CREATE VIEW v3 AS SELECT f1 FROM t1; +CREATE USER u26813@localhost; +GRANT DROP ON db26813.v1 TO u26813@localhost; +GRANT CREATE VIEW ON db26813.v2 TO u26813@localhost; +GRANT DROP, CREATE VIEW ON db26813.v3 TO u26813@localhost; +GRANT SELECT ON db26813.t1 TO u26813@localhost; + +connect (u1,localhost,u26813,,db26813); +connection u1; +--error 1142 +ALTER VIEW v1 AS SELECT f2 FROM t1; +--error 1142 +ALTER VIEW v2 AS SELECT f2 FROM t1; +ALTER VIEW v3 AS SELECT f2 FROM t1; + +connection root; +SHOW CREATE VIEW v3; + +DROP USER u26813@localhost; +DROP DATABASE db26813; +disconnect u1; --echo End of 5.0 tests. diff --git a/mysql-test/t/wait_for_socket.sh b/mysql-test/t/wait_for_socket.sh index 8c17c8ac0ac..2fa7d5c5b7e 100755 --- a/mysql-test/t/wait_for_socket.sh +++ b/mysql-test/t/wait_for_socket.sh @@ -61,7 +61,7 @@ fi ########################################################################### -client_args="--silent --socket=$socket_path --connect_timeout=1 " +client_args="--no-defaults --silent --socket=$socket_path --connect_timeout=1 " [ -n "$username" ] && client_args="$client_args --user=$username " [ -n "$password" ] && client_args="$client_args --password=$password " |