diff options
Diffstat (limited to 'mysql-test/t')
29 files changed, 891 insertions, 35 deletions
diff --git a/mysql-test/t/bug46080-master.opt b/mysql-test/t/bug46080-master.opt index 71024d39356..1be97e178ed 100644 --- a/mysql-test/t/bug46080-master.opt +++ b/mysql-test/t/bug46080-master.opt @@ -1 +1 @@ ---loose-performance-schema=0 --skip-grant-tables --skip-name-resolve --loose-safemalloc-mem-limit=4000000 +--loose-performance-schema=0 --skip-grant-tables --skip-name-resolve diff --git a/mysql-test/t/disabled.def b/mysql-test/t/disabled.def index 7b14c332233..62d11e35922 100644 --- a/mysql-test/t/disabled.def +++ b/mysql-test/t/disabled.def @@ -10,9 +10,10 @@ # ############################################################################## kill : Bug#37780 2008-12-03 HHunger need some changes to be robust enough for pushbuild. -query_cache_28249 : Bug#43861 2009-03-25 main.query_cache_28249 fails sporadically -sp_sync : Bug#48157 2010-02-06 5.5-m3 demands a differnt solution -plugin_load : Bug#42144 2009-12-21 alik plugin_load fails -partition_innodb_plugin : Bug#53307 2010-04-30 VasilDimov valgrind warnings +lowercase_table3 : Bug#54845 2010-06-30 alik main.lowercase_table3 on Mac OSX mysqlhotcopy_myisam : bug#54129 2010-06-04 Horst mysqlhotcopy_archive : bug#54129 2010-06-04 Horst +plugin_load : Bug#42144 2009-12-21 alik plugin_load fails +partition_innodb_plugin : Bug#53307 2010-04-30 VasilDimov valgrind warnings +query_cache_28249 : Bug#43861 2009-03-25 main.query_cache_28249 fails sporadically +sp_sync : Bug#48157 2010-02-06 5.5-m3 demands a differnt solution diff --git a/mysql-test/t/error_simulation.test b/mysql-test/t/error_simulation.test index 7a48a2e3231..f6edacfaa29 100644 --- a/mysql-test/t/error_simulation.test +++ b/mysql-test/t/error_simulation.test @@ -45,7 +45,6 @@ SHOW CREATE TABLE t1; SELECT * FROM t1; DROP TABLE t1; - --echo # --echo # Bug#42064: low memory crash when importing hex strings, in Item_hex_string::Item_hex_string --echo # @@ -60,6 +59,36 @@ SET SESSION debug=DEFAULT; DROP TABLE t1; +-- echo # +-- echo # Bug#41660: Sort-index_merge for non-first join table may require +-- echo # O(#scans) memory +-- echo # + +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9); + +CREATE TABLE t2 (a INT, b INT, filler CHAR(100), KEY(a), KEY(b)); +INSERT INTO t2 SELECT 1000, 1000, 'filler' FROM t1 A, t1 B, t1 C; +INSERT INTO t2 VALUES (1, 1, 'data'); + +--echo # the example query uses LEFT JOIN only for the sake of being able to +--echo # demonstrate the issue with a very small dataset. (left outer join +--echo # disables the use of join buffering, so we get the second table +--echo # re-scanned for every record in the outer table. if we used inner join, +--echo # we would need to have thousands of records and/or more columns in both +--echo # tables so that the join buffer is filled and re-scans are triggered). + +SET SESSION debug = '+d,only_one_Unique_may_be_created'; + +--replace_column 1 x 2 x 3 x 4 x 5 x 6 x 7 x 8 x 9 x +EXPLAIN +SELECT * FROM t1 LEFT JOIN t2 ON ( t2.a < 10 OR t2.b < 10 ); +SELECT * FROM t1 LEFT JOIN t2 ON ( t2.a < 10 OR t2.b < 10 ); + +SET SESSION debug = DEFAULT; + +DROP TABLE t1, t2; + --echo # --echo # End of 5.1 tests --echo # diff --git a/mysql-test/t/analyse.test b/mysql-test/t/func_analyse.test index 05f739bfd69..05f739bfd69 100644 --- a/mysql-test/t/analyse.test +++ b/mysql-test/t/func_analyse.test diff --git a/mysql-test/t/func_digest.test b/mysql-test/t/func_digest.test index 4020ef0f4fc..81f19c7e091 100644 --- a/mysql-test/t/func_digest.test +++ b/mysql-test/t/func_digest.test @@ -481,3 +481,16 @@ SELECT LENGTH(SHA2( '', 224 )) / 2 * 8 = 224; SELECT LENGTH(SHA2( 'any', 256 )) / 2 * 8 = 256; SELECT LENGTH(SHA2( 'size', 384 )) / 2 * 8 = 384; SELECT LENGTH(SHA2( 'computed', 512 )) / 2 * 8 = 512; + +--echo # +--echo # Bug#54661 sha2() returns BINARY result +--echo # +--enable_metadata +SET NAMES binary; +SELECT sha2('1',224); +SET NAMES utf8; +SELECT sha2('1',224); +SET NAMES latin1; +SELECT sha2('1',224); +--disable_metadata + diff --git a/mysql-test/t/func_in.test b/mysql-test/t/func_in.test index 61ae812d874..6efeb2866e6 100644 --- a/mysql-test/t/func_in.test +++ b/mysql-test/t/func_in.test @@ -540,5 +540,20 @@ EXPLAIN SELECT * FROM t1 WHERE c_char IN (NULL, NULL); DROP TABLE t1; --echo # +--echo # Bug#54477: Crash on IN / CASE with NULL arguments +--echo # + +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1), (2); + +SELECT 1 IN (NULL, a) FROM t1; + +SELECT a IN (a, a) FROM t1 GROUP BY a WITH ROLLUP; + +SELECT CASE a WHEN a THEN a END FROM t1 GROUP BY a WITH ROLLUP; + +DROP TABLE t1; + +--echo # --echo End of 5.1 tests diff --git a/mysql-test/t/func_like.test b/mysql-test/t/func_like.test index 741ea5533da..1204d04d9a0 100644 --- a/mysql-test/t/func_like.test +++ b/mysql-test/t/func_like.test @@ -112,5 +112,19 @@ select 'andre%' like 'andreÊ%' escape 'Ê'; # select _cp1251'andre%' like convert('andreÊ%' using cp1251) escape 'Ê'; -# -# End of 4.1 tests + +--echo End of 4.1 tests + + +--echo # +--echo # Bug #54575: crash when joining tables with unique set column +--echo # +CREATE TABLE t1(a SET('a') NOT NULL, UNIQUE KEY(a)); +CREATE TABLE t2(b INT PRIMARY KEY); +INSERT INTO t1 VALUES (); +INSERT INTO t2 VALUES (1), (2), (3); +SELECT 1 FROM t2 JOIN t1 ON 1 LIKE a GROUP BY a; +DROP TABLE t1, t2; + + +--echo End of 5.1 tests diff --git a/mysql-test/t/group_by.test b/mysql-test/t/group_by.test index f90c1dc3c58..f46a20b2db4 100644 --- a/mysql-test/t/group_by.test +++ b/mysql-test/t/group_by.test @@ -1224,3 +1224,26 @@ DROP TABLE t1, t2; --echo # --echo # End of 5.1 tests + +--echo # +--echo # Bug#49771: Incorrect MIN (date) when minimum value is 0000-00-00 +--echo # +CREATE TABLE t1 (f1 int, f2 DATE); + +INSERT INTO t1 VALUES (1,'2004-04-19'), (1,'0000-00-00'), (1,'2004-04-18'), +(2,'2004-05-19'), (2,'0001-01-01'), (3,'2004-04-10'); + +SELECT MIN(f2),MAX(f2) FROM t1; +SELECT f1,MIN(f2),MAX(f2) FROM t1 GROUP BY 1; + +DROP TABLE t1; + +CREATE TABLE t1 ( f1 int, f2 time); +INSERT INTO t1 VALUES (1,'01:27:35'), (1,'06:11:01'), (2,'19:53:05'), +(2,'21:44:25'), (3,'10:55:12'), (3,'05:45:11'), (4,'00:25:00'); + +SELECT MIN(f2),MAX(f2) FROM t1; +SELECT f1,MIN(f2),MAX(f2) FROM t1 GROUP BY 1; + +DROP TABLE t1; +--echo #End of test#49771 diff --git a/mysql-test/t/having.test b/mysql-test/t/having.test index 65bf9518a5c..c808e747523 100644 --- a/mysql-test/t/having.test +++ b/mysql-test/t/having.test @@ -544,3 +544,24 @@ ORDER BY t1.f2; DROP TABLE t1,t2; --echo End of 5.0 tests + +--echo # +--echo # Bug#54416 MAX from JOIN with HAVING returning NULL with 5.1 and Empty set +--echo # + +CREATE TABLE t1 (f1 INT(11), f2 VARCHAR(1), PRIMARY KEY (f1)); +INSERT INTO t1 VALUES (1,'f'); + +CREATE TABLE t2 (f1 INT(11), f2 VARCHAR(1)); +INSERT INTO t2 VALUES (2,'m'); +INSERT INTO t2 VALUES (3,'m'); +INSERT INTO t2 VALUES (11,NULL); +INSERT INTO t2 VALUES (12,'k'); + +SELECT MAX(t1.f1) field1 +FROM t1 JOIN t2 ON t2.f2 LIKE 'x' +HAVING field1 < 7; + +DROP TABLE t1,t2; + +--echo End of 5.1 tests diff --git a/mysql-test/t/information_schema.test b/mysql-test/t/information_schema.test index 456cacf2fe1..87022d65fcc 100644 --- a/mysql-test/t/information_schema.test +++ b/mysql-test/t/information_schema.test @@ -1416,6 +1416,31 @@ DROP USER nonpriv; DROP TABLE db1.t1; DROP DATABASE db1; +--echo +--echo Bug#54422 query with = 'variables' +--echo + +CREATE TABLE variables(f1 INT); +SELECT COLUMN_DEFAULT, TABLE_NAME +FROM INFORMATION_SCHEMA.COLUMNS +WHERE INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = 'variables'; +DROP TABLE variables; + +--echo # +--echo # Bug #53814: NUMERIC_PRECISION for unsigned bigint field is 19, +--echo # should be 20 +--echo # + +CREATE TABLE ubig (a BIGINT, b BIGINT UNSIGNED); + +SELECT TABLE_NAME, COLUMN_NAME, NUMERIC_PRECISION + FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='ubig'; + +INSERT INTO ubig VALUES (0xFFFFFFFFFFFFFFFF,0xFFFFFFFFFFFFFFFF); +SELECT length(CAST(b AS CHAR)) FROM ubig; + +DROP TABLE ubig; + --echo End of 5.1 tests. diff --git a/mysql-test/t/innodb_mysql_lock2.test b/mysql-test/t/innodb_mysql_lock2.test index 048d712183f..4ad30b9f25b 100644 --- a/mysql-test/t/innodb_mysql_lock2.test +++ b/mysql-test/t/innodb_mysql_lock2.test @@ -12,6 +12,10 @@ # Save the initial number of concurrent sessions. --source include/count_sessions.inc +--disable_query_log +CALL mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT"); +--enable_query_log + --echo # --echo # Test how do we handle locking in various cases when --echo # we read data from InnoDB tables. diff --git a/mysql-test/t/loaddata.test b/mysql-test/t/loaddata.test index 27c4cc8a75c..06054d1990d 100644 --- a/mysql-test/t/loaddata.test +++ b/mysql-test/t/loaddata.test @@ -570,4 +570,36 @@ DROP TABLE t1; connection default; disconnect con1; + +--echo # +--echo # Bug #51876 : crash/memory underrun when loading data with ucs2 +--echo # and reverse() function +--echo # + +--echo # Problem # 1 (original report): wrong parsing of ucs2 data +SELECT '00' UNION SELECT '10' INTO OUTFILE 'tmpp.txt'; +CREATE TABLE t1(a INT); +LOAD DATA INFILE 'tmpp.txt' INTO TABLE t1 CHARACTER SET ucs2 +(@b) SET a=REVERSE(@b); +--echo # should return 2 zeroes (as the value is truncated) +SELECT * FROM t1; + +DROP TABLE t1; +let $MYSQLD_DATADIR= `select @@datadir`; +remove_file $MYSQLD_DATADIR/test/tmpp.txt; + + +--echo # Problem # 2 : if you write and read ucs2 data to a file they're lost +SELECT '00' UNION SELECT '10' INTO OUTFILE 'tmpp2.txt' CHARACTER SET ucs2; +CREATE TABLE t1(a INT); +LOAD DATA INFILE 'tmpp2.txt' INTO TABLE t1 CHARACTER SET ucs2 +(@b) SET a=REVERSE(@b); +--echo # should return 0 and 1 (10 reversed) +SELECT * FROM t1; + +DROP TABLE t1; +let $MYSQLD_DATADIR= `select @@datadir`; +remove_file $MYSQLD_DATADIR/test/tmpp2.txt; + + --echo End of 5.1 tests diff --git a/mysql-test/t/lowercase_table2.test b/mysql-test/t/lowercase_table2.test index b8c7f532cde..d8aa4e97fb3 100644 --- a/mysql-test/t/lowercase_table2.test +++ b/mysql-test/t/lowercase_table2.test @@ -16,7 +16,7 @@ DROP DATABASE IF EXISTS `test_$1`; DROP DATABASE IF EXISTS mysqltest_LC2; --enable_warnings -CREATE TABLE T1 (a int); +CREATE TABLE T1 (a int) ENGINE=MyISAM; INSERT INTO T1 VALUES (1); SHOW TABLES LIKE "T1"; SHOW TABLES LIKE "t1"; diff --git a/mysql-test/t/lowercase_view.test b/mysql-test/t/lowercase_view.test index d6612b3e6b9..52be911cde0 100644 --- a/mysql-test/t/lowercase_view.test +++ b/mysql-test/t/lowercase_view.test @@ -160,4 +160,26 @@ SELECT * FROM v1; DROP VIEW v1; DROP TABLE t1; + +--echo End of 5.0 tests. + + +--echo # +--echo # Bug #53095: SELECT column_name FROM INFORMATION_SCHEMA.STATISTICS +--echo # returns nothing +--echo # + +CREATE TABLE `ttt` ( + `f1` char(3) NOT NULL, + PRIMARY KEY (`f1`) +) ENGINE=myisam DEFAULT CHARSET=latin1; + +SELECT count(COLUMN_NAME) FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_NAME = +'TTT'; +SELECT count(*) FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_NAME = 'TTT'; + +DROP TABLE `ttt`; + + --echo End of 5.0 tests. + diff --git a/mysql-test/t/merge.test b/mysql-test/t/merge.test index 29c0eae1df6..a06da03cbcd 100644 --- a/mysql-test/t/merge.test +++ b/mysql-test/t/merge.test @@ -457,7 +457,7 @@ CREATE TABLE t2(a INT) ENGINE=MERGE UNION=(t1); SELECT * FROM t2; DROP TABLE t1, t2; CREATE TABLE t2(a INT) ENGINE=MERGE UNION=(t3); ---error 1168 +--error ER_WRONG_MRG_TABLE SELECT * FROM t2; DROP TABLE t2; @@ -549,11 +549,11 @@ drop table t1; # CREATE TABLE fails # CREATE TABLE tm1(a INT) ENGINE=MERGE UNION=(t1, t2); ---error 1168 +--error ER_WRONG_MRG_TABLE SELECT * FROM tm1; CHECK TABLE tm1; CREATE TABLE t1(a INT); ---error 1168 +--error ER_WRONG_MRG_TABLE SELECT * FROM tm1; CHECK TABLE tm1; CREATE TABLE t2(a BLOB); @@ -887,6 +887,9 @@ CREATE TABLE t4 LIKE t3; SHOW CREATE TABLE t4; --error ER_TABLE_NOT_LOCKED INSERT INTO t4 VALUES (4); +--echo # Temporary tables can be created in spite of LOCK TABLES. +--echo # If the temporary MERGE table uses the locked children only, +--echo # it can even be used. CREATE TEMPORARY TABLE t4 LIKE t3; --error ER_WRONG_MRG_TABLE SHOW CREATE TABLE t4; @@ -913,7 +916,7 @@ DROP TABLE t4; --echo # 2. Normal rename. SELECT * FROM t3 ORDER BY c1; RENAME TABLE t2 TO t5; ---error 1168 +--error ER_WRONG_MRG_TABLE SELECT * FROM t3 ORDER BY c1; RENAME TABLE t5 TO t2; SELECT * FROM t3 ORDER BY c1; @@ -931,7 +934,7 @@ UNLOCK TABLES; --echo # --echo # 4. Alter table rename. ALTER TABLE t2 RENAME TO t5; ---error 1168 +--error ER_WRONG_MRG_TABLE SELECT * FROM t3 ORDER BY c1; ALTER TABLE t5 RENAME TO t2; SELECT * FROM t3 ORDER BY c1; @@ -1170,6 +1173,7 @@ SELECT @a; SELECT * FROM t4 ORDER BY c1; DELETE FROM t4 WHERE c1 = 33; DROP TRIGGER t3_ai; +UNLOCK TABLES; --echo # --echo # Trigger with table use on child DELETE FROM t4 WHERE c1 = 4; @@ -1273,11 +1277,12 @@ DROP TABLE t1, t2, t3; # # Bug#25038 - Waiting TRUNCATE +# Truncate failed with error message when table was in use by MERGE. # # Show that truncate of child table after use of parent table works. -CREATE TABLE t1 (c1 INT) ENGINE= MyISAM; -CREATE TABLE t2 (c1 INT) ENGINE= MyISAM; -CREATE TABLE t3 (c1 INT) ENGINE= MRG_MYISAM UNION= (t1, t2); +CREATE TABLE t1 (c1 INT) ENGINE=MyISAM; +CREATE TABLE t2 (c1 INT) ENGINE=MyISAM; +CREATE TABLE t3 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1,t2); INSERT INTO t1 VALUES (1); INSERT INTO t2 VALUES (2); SELECT * FROM t3; @@ -1429,7 +1434,6 @@ FLUSH TABLES m1, t1; UNLOCK TABLES; DROP TABLE t1, m1; - # # Bug#35068 - Assertion fails when reading from i_s.tables # and there is incorrect merge table @@ -1816,7 +1820,7 @@ CREATE TABLE m1 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1,mysql_test1.t2) INSERT INTO t1 VALUES (1); INSERT INTO mysql_test1.t2 VALUES (2); SELECT * FROM m1; -#--copy_file $MYSQLTEST_VARDIR/master-data/test/m1.MRG /tmp/mysql-test-m1.MRG +#--copy_file $MYSQLTEST_DATADIR/test/m1.MRG /tmp/mysql-test-m1.MRG DROP TABLE t1, mysql_test1.t2, m1; DROP DATABASE mysql_test1; # @@ -2105,6 +2109,14 @@ DROP FUNCTION f1; DROP TABLE tm1, t1, t2, t3, t4, t5; --echo # +--echo # Bug#47633 - assert in ha_myisammrg::info during OPTIMIZE +--echo # +CREATE TEMPORARY TABLE t1 (c1 INT); +ALTER TABLE t1 ENGINE=MERGE UNION(t_not_exists,t1); +OPTIMIZE TABLE t1; +DROP TABLE t1; + +--echo # --echo # Bug47098 assert in MDL_context::destroy on HANDLER --echo # <damaged merge table> OPEN --echo # @@ -2186,6 +2198,18 @@ ALTER TABLE m1 ADD INDEX (c1); UNLOCK TABLES; DROP TABLE m1, t1; +--echo # +--echo # If children are to be altered, they need an explicit lock. +--echo # +CREATE TABLE t1 (c1 INT); +CREATE TABLE m1 (c1 INT) ENGINE=MRG_MyISAM UNION=(t1); +LOCK TABLE m1 WRITE; +--error ER_TABLE_NOT_LOCKED_FOR_WRITE +ALTER TABLE t1 ADD INDEX (c1); +LOCK TABLE m1 WRITE, t1 WRITE; +ALTER TABLE t1 ADD INDEX (c1); +UNLOCK TABLES; +DROP TABLE m1, t1; --echo # --echo # Test for bug #37371 "CREATE TABLE LIKE merge loses UNION parameter" diff --git a/mysql-test/t/mysql-bug45236-master.opt b/mysql-test/t/mysql-bug45236-master.opt deleted file mode 100644 index fc1887bca47..00000000000 --- a/mysql-test/t/mysql-bug45236-master.opt +++ /dev/null @@ -1 +0,0 @@ ---loose-skip-safemalloc diff --git a/mysql-test/t/mysql_delimiter_19799.sql b/mysql-test/t/mysql_delimiter_19799.sql index 2a3d4378492..2a3d4378492 100755..100644 --- a/mysql-test/t/mysql_delimiter_19799.sql +++ b/mysql-test/t/mysql_delimiter_19799.sql diff --git a/mysql-test/t/mysql_upgrade.test b/mysql-test/t/mysql_upgrade.test index 83d97954222..3ea612d2ca7 100644 --- a/mysql-test/t/mysql_upgrade.test +++ b/mysql-test/t/mysql_upgrade.test @@ -108,6 +108,23 @@ DROP PROCEDURE testproc; --cat_file $MYSQLTEST_VARDIR/tmp/41569.txt --remove_file $MYSQLTEST_VARDIR/tmp/41569.txt + +--echo # +--echo # Bug #53613: mysql_upgrade incorrectly revokes +--echo # TRIGGER privilege on given table +--echo # + +GRANT USAGE ON *.* TO 'user3'@'%'; +GRANT ALL PRIVILEGES ON `roelt`.`test2` TO 'user3'@'%'; +--echo Run mysql_upgrade with all privileges on a user +--exec $MYSQL_UPGRADE --skip-verbose --force 2>&1 +SHOW GRANTS FOR 'user3'@'%'; + +DROP USER 'user3'@'%'; + +--echo End of 5.1 tests + + # # Test the --upgrade-system-tables option # diff --git a/mysql-test/t/mysqlbinlog.test b/mysql-test/t/mysqlbinlog.test index 8b250dbb586..e375cb7299f 100644 --- a/mysql-test/t/mysqlbinlog.test +++ b/mysql-test/t/mysqlbinlog.test @@ -36,6 +36,7 @@ load data infile '../../std_data/words.dat' into table t1; load data infile '../../std_data/words.dat' into table t1; load data infile '../../std_data/words.dat' into table t1; # simple query to show more in second binlog +--let $binlog_start_pos=query_get_value(SHOW MASTER STATUS, Position, 1) insert into t1 values ("Alas"); flush logs; @@ -75,7 +76,7 @@ select "--- --start-position --" as ""; --enable_query_log --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR --replace_regex /SQL_LOAD_MB-[0-9]-[0-9]/SQL_LOAD_MB-#-#/ ---exec $MYSQL_BINLOG --short-form --local-load=$MYSQLTEST_VARDIR/tmp/ --start-position=1074 $MYSQLD_DATADIR/master-bin.000002 +--exec $MYSQL_BINLOG --short-form --local-load=$MYSQLTEST_VARDIR/tmp/ --start-position=$binlog_start_pos $MYSQLD_DATADIR/master-bin.000002 # These are tests for remote binlog. # They should return the same as previous test. @@ -111,8 +112,7 @@ select "--- --start-position --" as ""; --enable_query_log --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR --replace_regex /SQL_LOAD_MB-[0-9]-[0-9]/SQL_LOAD_MB-#-#/ ---exec $MYSQL_BINLOG --short-form --local-load=$MYSQLTEST_VARDIR/tmp/ --read-from-remote-server --start-position=1074 --user=root --host=127.0.0.1 --port=$MASTER_MYPORT master-bin.000002 - +--exec $MYSQL_BINLOG --short-form --local-load=$MYSQLTEST_VARDIR/tmp/ --read-from-remote-server --start-position=$binlog_start_pos --user=root --host=127.0.0.1 --port=$MASTER_MYPORT master-bin.000002 # Bug#7853 mysqlbinlog does not accept input from stdin --disable_query_log @@ -124,7 +124,9 @@ select "--- reading stdin --" as ""; --replace_result $MYSQL_TEST_DIR MYSQL_TEST_DIR --replace_regex /SQL_LOAD_MB-[0-9]-[0-9]/SQL_LOAD_MB-#-#/ ---exec $MYSQL_BINLOG --short-form --start-position=79 - < $MYSQL_TEST_DIR/std_data/trunc_binlog.000001 +# postion is constant to correspond to an event in pre-recorded binlog +--let $binlog_start_pos=79 +--exec $MYSQL_BINLOG --short-form --start-position=$binlog_start_pos - < $MYSQL_TEST_DIR/std_data/trunc_binlog.000001 drop table t1,t2; # diff --git a/mysql-test/t/mysqlbinlog2.test b/mysql-test/t/mysqlbinlog2.test index 1f0da6bcf54..dcd92262a83 100644 --- a/mysql-test/t/mysqlbinlog2.test +++ b/mysql-test/t/mysqlbinlog2.test @@ -21,7 +21,9 @@ create table t1 (a int auto_increment not null primary key, b char(3)); insert into t1 values(null, "a"); insert into t1 values(null, "b"); set timestamp=@a+2; +--let $binlog_pos_760=query_get_value(SHOW MASTER STATUS, Position, 1) insert into t1 values(null, "c"); +--let $binlog_pos_951=query_get_value(SHOW BINLOG EVENTS in 'master-bin.000001' from $binlog_pos_760, Pos, 4) set timestamp=@a+4; insert into t1 values(null, "d"); insert into t1 values(null, "e"); @@ -29,6 +31,8 @@ insert into t1 values(null, "e"); flush logs; set timestamp=@a+1; # this could happen on a slave insert into t1 values(null, "f"); +--let $binlog_pos_135=query_get_value(SHOW BINLOG EVENTS in 'master-bin.000002', Pos, 3) +--let $binlog_pos_203=query_get_value(SHOW BINLOG EVENTS in 'master-bin.000002', Pos, 4) # delimiters are for easier debugging in future @@ -50,15 +54,15 @@ select "--- offset --" as ""; --disable_query_log select "--- start-position --" as ""; --enable_query_log ---exec $MYSQL_BINLOG --short-form --start-position=760 $MYSQLD_DATADIR/master-bin.000001 +--exec $MYSQL_BINLOG --short-form --start-position=$binlog_pos_760 $MYSQLD_DATADIR/master-bin.000001 --disable_query_log select "--- stop-position --" as ""; --enable_query_log ---exec $MYSQL_BINLOG --short-form --stop-position=760 $MYSQLD_DATADIR/master-bin.000001 +--exec $MYSQL_BINLOG --short-form --stop-position=$binlog_pos_760 $MYSQLD_DATADIR/master-bin.000001 --disable_query_log select "--- start and stop positions ---" as ""; --enable_query_log ---exec $MYSQL_BINLOG --short-form --start-position=760 --stop-position 951 $MYSQLD_DATADIR/master-bin.000001 +--exec $MYSQL_BINLOG --short-form --start-position=$binlog_pos_760 --stop-position=$binlog_pos_951 $MYSQLD_DATADIR/master-bin.000001 --disable_query_log select "--- start-datetime --" as ""; --enable_query_log @@ -84,11 +88,11 @@ select "--- offset --" as ""; --disable_query_log select "--- start-position --" as ""; --enable_query_log ---exec $MYSQL_BINLOG --short-form --start-position=760 $MYSQLD_DATADIR/master-bin.000001 $MYSQLD_DATADIR/master-bin.000002 +--exec $MYSQL_BINLOG --short-form --start-position=$binlog_pos_760 $MYSQLD_DATADIR/master-bin.000001 $MYSQLD_DATADIR/master-bin.000002 --disable_query_log select "--- stop-position --" as ""; --enable_query_log ---exec $MYSQL_BINLOG --short-form --stop-position=203 $MYSQLD_DATADIR/master-bin.000001 $MYSQLD_DATADIR/master-bin.000002 +--exec $MYSQL_BINLOG --short-form --stop-position=$binlog_pos_203 $MYSQLD_DATADIR/master-bin.000001 $MYSQLD_DATADIR/master-bin.000002 --disable_query_log select "--- start-datetime --" as ""; --enable_query_log @@ -111,15 +115,15 @@ select "--- offset --" as ""; --disable_query_log select "--- start-position --" as ""; --enable_query_log ---exec $MYSQL_BINLOG --short-form --start-position=760 --read-from-remote-server --user=root --host=127.0.0.1 --port=$MASTER_MYPORT master-bin.000001 +--exec $MYSQL_BINLOG --short-form --start-position=$binlog_pos_760 --read-from-remote-server --user=root --host=127.0.0.1 --port=$MASTER_MYPORT master-bin.000001 --disable_query_log select "--- stop-position --" as ""; --enable_query_log ---exec $MYSQL_BINLOG --short-form --stop-position=760 --read-from-remote-server --user=root --host=127.0.0.1 --port=$MASTER_MYPORT master-bin.000001 +--exec $MYSQL_BINLOG --short-form --stop-position=$binlog_pos_760 --read-from-remote-server --user=root --host=127.0.0.1 --port=$MASTER_MYPORT master-bin.000001 --disable_query_log select "--- start and stop positions ---" as ""; --enable_query_log ---exec $MYSQL_BINLOG --short-form --start-position=760 --stop-position 951 --read-from-remote-server --user=root --host=127.0.0.1 --port=$MASTER_MYPORT master-bin.000001 +--exec $MYSQL_BINLOG --short-form --start-position=$binlog_pos_760 --stop-position=$binlog_pos_951 --read-from-remote-server --user=root --host=127.0.0.1 --port=$MASTER_MYPORT master-bin.000001 --disable_query_log select "--- start-datetime --" as ""; --enable_query_log @@ -142,11 +146,11 @@ select "--- offset --" as ""; --disable_query_log select "--- start-position --" as ""; --enable_query_log ---exec $MYSQL_BINLOG --short-form --start-position=760 --read-from-remote-server --user=root --host=127.0.0.1 --port=$MASTER_MYPORT master-bin.000001 master-bin.000002 +--exec $MYSQL_BINLOG --short-form --start-position=$binlog_pos_760 --read-from-remote-server --user=root --host=127.0.0.1 --port=$MASTER_MYPORT master-bin.000001 master-bin.000002 --disable_query_log select "--- stop-position --" as ""; --enable_query_log ---exec $MYSQL_BINLOG --short-form --stop-position=135 --read-from-remote-server --user=root --host=127.0.0.1 --port=$MASTER_MYPORT master-bin.000001 master-bin.000002 +--exec $MYSQL_BINLOG --short-form --stop-position=$binlog_pos_135 --read-from-remote-server --user=root --host=127.0.0.1 --port=$MASTER_MYPORT master-bin.000001 master-bin.000002 --disable_query_log select "--- start-datetime --" as ""; --enable_query_log diff --git a/mysql-test/t/partition_innodb.test b/mysql-test/t/partition_innodb.test index ccca4df9882..30f5894716c 100644 --- a/mysql-test/t/partition_innodb.test +++ b/mysql-test/t/partition_innodb.test @@ -452,3 +452,19 @@ SELECT * FROM t1; COMMIT; DROP TABLE t1; --enable_parsing + +--echo # +--echo # Bug#54783: optimize table crashes with invalid timestamp default value and NO_ZERO_DATE +--echo # + +--disable_warnings +DROP TABLE IF EXISTS t1; +--enable_warnings + +CREATE TABLE t1 (a INT, b TIMESTAMP DEFAULT '0000-00-00 00:00:00') + ENGINE=INNODB PARTITION BY LINEAR HASH (a) PARTITIONS 1; +SET @old_mode = @@sql_mode; +SET SESSION sql_mode = 'NO_ZERO_DATE'; +OPTIMIZE TABLE t1; +SET SESSION sql_mode = @old_mode; +DROP TABLE t1; diff --git a/mysql-test/t/rpl_mysqldump_slave.test b/mysql-test/t/rpl_mysqldump_slave.test index 5723f1282aa..8f27646ba08 100644 --- a/mysql-test/t/rpl_mysqldump_slave.test +++ b/mysql-test/t/rpl_mysqldump_slave.test @@ -15,11 +15,14 @@ use test; connection slave; # Execute mysqldump with --dump-slave +--replace_regex /MASTER_LOG_POS=[0-9]+/MASTER_LOG_POS=BINLOG_START/ --exec $MYSQL_DUMP_SLAVE --compact --dump-slave test # Execute mysqldump with --dump-slave and --apply-slave-statements +--replace_regex /MASTER_LOG_POS=[0-9]+/MASTER_LOG_POS=BINLOG_START/ --exec $MYSQL_DUMP_SLAVE --compact --dump-slave --apply-slave-statements test +--replace_regex /MASTER_LOG_POS=[0-9]+/MASTER_LOG_POS=BINLOG_START/ --replace_result $MASTER_MYPORT MASTER_MYPORT # Execute mysqldump with --dump-slave ,--apply-slave-statements and --include-master-host-port --exec $MYSQL_DUMP_SLAVE --compact --dump-slave --apply-slave-statements --include-master-host-port test diff --git a/mysql-test/t/select.test b/mysql-test/t/select.test index 1e53461f665..495b6002986 100644 --- a/mysql-test/t/select.test +++ b/mysql-test/t/select.test @@ -4117,4 +4117,38 @@ SELECT * FROM t1 WHERE 102 < c; DROP TABLE t1; +--echo # +--echo # Bug #54459: Assertion failed: param.sort_length, +--echo # file .\filesort.cc, line 149 (part II) +--echo # +CREATE TABLE t1(a ENUM('') NOT NULL); +INSERT INTO t1 VALUES (), (), (); +EXPLAIN SELECT 1 FROM t1 ORDER BY a COLLATE latin1_german2_ci; +SELECT 1 FROM t1 ORDER BY a COLLATE latin1_german2_ci; +DROP TABLE t1; + + --echo End of 5.1 tests + +--echo # +--echo # Bug#54515: Crash in opt_range.cc::get_best_group_min_max on +--echo # SELECT from VIEW with GROUP BY +--echo # + +CREATE TABLE t1 ( + col_int_key int DEFAULT NULL, + KEY int_key (col_int_key) +) ; + +INSERT INTO t1 VALUES (1),(2); + +CREATE VIEW view_t1 AS + SELECT t1.col_int_key AS col_int_key + FROM t1; + +SELECT col_int_key FROM view_t1 GROUP BY col_int_key; + +DROP VIEW view_t1; +DROP TABLE t1; + +--echo # End of test BUG#54515 diff --git a/mysql-test/t/single_delete_update.test b/mysql-test/t/single_delete_update.test new file mode 100644 index 00000000000..e3ee13f891c --- /dev/null +++ b/mysql-test/t/single_delete_update.test @@ -0,0 +1,481 @@ +# +# Single table specific update/delete tests (mysql_update and mysql_delete) +# + +--echo # +--echo # Bug #30584: delete with order by and limit clauses does not use +--echo # limit efficiently +--echo # + +CREATE TABLE t1 (i INT); +INSERT INTO t1 VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19), + (20),(21),(22),(23),(24),(25); + +CREATE TABLE t2(a INT, i INT PRIMARY KEY); +INSERT INTO t2 (i) SELECT i FROM t1; + +FLUSH STATUS; +SELECT * FROM t2 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5; +SHOW SESSION STATUS LIKE 'Sort%'; +SHOW STATUS LIKE 'Handler_read_%'; + +FLUSH STATUS; +DELETE FROM t2 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5; +SHOW SESSION STATUS LIKE 'Sort%'; +SHOW STATUS LIKE 'Handler_read_%'; +SELECT * FROM t2 WHERE i > 10 AND i <= 18 ORDER BY i; + +DROP TABLE t2; + +--echo # +--echo # index on field prefix: +--echo # + +CREATE TABLE t2(a INT, i CHAR(2), INDEX(i(1))); +INSERT INTO t2 (i) SELECT i FROM t1; + +FLUSH STATUS; +SELECT * FROM t2 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5; +SHOW SESSION STATUS LIKE 'Sort%'; +SHOW STATUS LIKE 'Handler_read_%'; + +FLUSH STATUS; +DELETE FROM t2 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5; +SHOW SESSION STATUS LIKE 'Sort%'; +SHOW STATUS LIKE 'Handler_read_%'; +SELECT * FROM t2 WHERE i > 10 AND i <= 18 ORDER BY i; + +DROP TABLE t2; + +--echo # +--echo # constant inside ORDER BY list, should use filesort +--echo # on a small table +--echo # + +CREATE TABLE t2(a INT, b INT, c INT, d INT, INDEX(a, b, c)); +INSERT INTO t2 (a, b, c) SELECT i, i, i FROM t1; + +FLUSH STATUS; +SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5; +SHOW SESSION STATUS LIKE 'Sort%'; +SHOW STATUS LIKE 'Handler_read_%'; + +let $cnt = `SELECT COUNT(*) FROM t2 WHERE b = 10`; + +FLUSH STATUS; +DELETE FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5; +SHOW SESSION STATUS LIKE 'Sort%'; +SHOW STATUS LIKE 'Handler_read_%'; +--echo ## should be 5 (previous LIMIT) +eval SELECT $cnt - COUNT(*) FROM t2 WHERE b = 10; + +DROP TABLE t2; + +--echo # +--echo # same test as above (constant inside ORDER BY list), but with +--echo # a larger table - should not use filesort +--echo # + +CREATE TABLE t2(a INT, b INT, c INT, d INT, INDEX(a, b, c)); +INSERT INTO t2 (a, b, c) SELECT i, i, i FROM t1; + +INSERT INTO t2 (a, b, c) SELECT t1.i, t1.i, t1.i FROM t1, t1 x1, t1 x2; + +FLUSH STATUS; +SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5; +SHOW SESSION STATUS LIKE 'Sort%'; +SHOW STATUS LIKE 'Handler_read_%'; + +let $cnt = `SELECT COUNT(*) FROM t2 WHERE b = 10`; + +FLUSH STATUS; +DELETE FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5; +SHOW SESSION STATUS LIKE 'Sort%'; +SHOW STATUS LIKE 'Handler_read_%'; +--echo ## should be 5 (previous LIMIT) +eval SELECT $cnt - COUNT(*) FROM t2 WHERE b = 10; + +DROP TABLE t2; + +--echo # +--echo # as above + partial index, should use filesort +--echo # + +CREATE TABLE t2 (a CHAR(2), b CHAR(2), c CHAR(2), d CHAR(2), INDEX (a,b(1),c)); +INSERT INTO t2 SELECT i, i, i, i FROM t1; + +FLUSH STATUS; +SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5; +SHOW SESSION STATUS LIKE 'Sort%'; +SHOW STATUS LIKE 'Handler_read_%'; + +FLUSH STATUS; +DELETE FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5; +SHOW SESSION STATUS LIKE 'Sort%'; +SHOW STATUS LIKE 'Handler_read_%'; +SELECT * FROM t2 WHERE b = 10 ORDER BY a, c; + +DROP TABLE t2; + +--echo # +--echo # as above but index is without HA_READ_ORDER flag, should use filesort +--echo # + +CREATE TABLE t2 (a CHAR(2), b CHAR(2), c CHAR(2), d CHAR(2), INDEX (a,b,c)) ENGINE=HEAP; +INSERT INTO t2 SELECT i, i, i, i FROM t1; + +FLUSH STATUS; +SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5; +SHOW SESSION STATUS LIKE 'Sort%'; +SHOW STATUS LIKE 'Handler_read_%'; + +FLUSH STATUS; +DELETE FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5; +SHOW SESSION STATUS LIKE 'Sort%'; +SHOW STATUS LIKE 'Handler_read_%'; +SELECT * FROM t2 WHERE b = 10 ORDER BY a, c; + +DROP TABLE t2; + +--echo # +--echo # quick select is Index Merge, should use filesort +--echo # + +CREATE TABLE t2 (i INT, key1 INT, key2 INT, INDEX (key1), INDEX (key2)); +INSERT INTO t2 (key1, key2) SELECT i, i FROM t1; + +FLUSH STATUS; +SELECT * FROM t2 WHERE key1 < 13 or key2 < 14 ORDER BY key1; +SHOW SESSION STATUS LIKE 'Sort%'; +SHOW STATUS LIKE 'Handler_read_%'; +--replace_column 1 x 2 x 3 x 4 x 5 x 6 x 7 x 8 x 9 x 10 x +EXPLAIN EXTENDED SELECT * FROM t2 WHERE key1 < 13 or key2 < 14 ORDER BY key1; + +FLUSH STATUS; +DELETE FROM t2 WHERE key1 < 13 or key2 < 14 ORDER BY key1; +SHOW SESSION STATUS LIKE 'Sort%'; +SHOW STATUS LIKE 'Handler_read_%'; +SELECT * FROM t2 WHERE key1 < 13 or key2 < 14 ORDER BY key1; +--replace_column 1 x 2 x 3 x 4 x 5 x 6 x 7 x 8 x 9 x 10 x +EXPLAIN EXTENDED SELECT * FROM t2 WHERE key1 < 13 or key2 < 14 ORDER BY key1; +DROP TABLE t2; + +--echo # +--echo # reverse quick select, should not use filesort +--echo # + +CREATE TABLE t2(a INT, i INT PRIMARY KEY); +INSERT INTO t2 (i) SELECT i FROM t1; + +FLUSH STATUS; +SELECT * FROM t2 WHERE i > 10 AND i <= 18 ORDER BY i DESC LIMIT 5; +SHOW SESSION STATUS LIKE 'Sort%'; +SHOW STATUS LIKE 'Handler_read_%'; + +FLUSH STATUS; +DELETE FROM t2 WHERE i > 10 AND i <= 18 ORDER BY i DESC LIMIT 5; +SHOW SESSION STATUS LIKE 'Sort%'; +SHOW STATUS LIKE 'Handler_read_%'; +SELECT * FROM t2 WHERE i > 10 AND i <= 18 ORDER BY i; + +DROP TABLE t2; + +--echo # +--echo # mixed sorting direction, should use filesort +--echo # + +CREATE TABLE t2 (a CHAR(2), b CHAR(2), c CHAR(2), INDEX (a, b)); +INSERT INTO t2 SELECT i, i, i FROM t1; + +FLUSH STATUS; +SELECT * FROM t2 ORDER BY a, b DESC LIMIT 5; +SHOW SESSION STATUS LIKE 'Sort%'; +SHOW STATUS LIKE 'Handler_read_%'; + +FLUSH STATUS; +DELETE FROM t2 ORDER BY a, b DESC LIMIT 5; +SHOW SESSION STATUS LIKE 'Sort%'; +SHOW STATUS LIKE 'Handler_read_%'; +SELECT * FROM t2 ORDER BY a, b DESC; + +DROP TABLE t2; + +--echo # +--echo # LIMIT with no WHERE and DESC direction, should not use filesort +--echo # + +CREATE TABLE t2 (a CHAR(2), b CHAR(2), c CHAR(2), INDEX (a, b)); +INSERT INTO t2 (a, b) SELECT i, i FROM t1; +INSERT INTO t2 (a, b) SELECT t1.i, t1.i FROM t1, t1 x1, t1 x2; + +FLUSH STATUS; +SELECT * FROM t2 ORDER BY a, b LIMIT 5; +SHOW SESSION STATUS LIKE 'Sort%'; +SHOW STATUS LIKE 'Handler_read_%'; + +FLUSH STATUS; +SELECT * FROM t2 ORDER BY a DESC, b DESC LIMIT 5; +SHOW SESSION STATUS LIKE 'Sort%'; +SHOW STATUS LIKE 'Handler_read_%'; + +FLUSH STATUS; +DELETE FROM t2 ORDER BY a DESC, b DESC LIMIT 5; +SHOW SESSION STATUS LIKE 'Sort%'; +SHOW STATUS LIKE 'Handler_read_%'; +SELECT * FROM t2 WHERE c = 10 ORDER BY a DESC, b DESC; + +DROP TABLE t1, t2; + + +--echo # +--echo # Bug #36569: UPDATE ... WHERE ... ORDER BY... always does a filesort +--echo # even if not required +--echo # + +CREATE TABLE t1 (i INT); +INSERT INTO t1 VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19), + (20),(21),(22),(23),(24),(25); + +CREATE TABLE t2(a INT, i INT PRIMARY KEY); +INSERT INTO t2 (i) SELECT i FROM t1; + +FLUSH STATUS; +SELECT * FROM t2 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5; +SHOW SESSION STATUS LIKE 'Sort%'; +SHOW STATUS LIKE 'Handler_read_%'; + +FLUSH STATUS; +UPDATE t2 SET a = 10 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5; +SHOW SESSION STATUS LIKE 'Sort%'; +SHOW STATUS LIKE 'Handler_read_%'; +SELECT * FROM t2 WHERE i > 10 AND i <= 18 ORDER BY i; + +DROP TABLE t2; + +--echo # +--echo # index on field prefix: +--echo # + +CREATE TABLE t2(a INT, i CHAR(2), INDEX(i(1))); +INSERT INTO t2 (i) SELECT i FROM t1; + +FLUSH STATUS; +SELECT * FROM t2 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5; +SHOW SESSION STATUS LIKE 'Sort%'; +SHOW STATUS LIKE 'Handler_read_%'; + +FLUSH STATUS; +UPDATE t2 SET a = 10 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5; +SHOW SESSION STATUS LIKE 'Sort%'; +SHOW STATUS LIKE 'Handler_read_%'; +SELECT * FROM t2 WHERE i > 10 AND i <= 18 ORDER BY i; + +DROP TABLE t2; + +--echo # +--echo # constant inside ORDER BY list, should use filesort +--echo # on a small table +--echo # + +CREATE TABLE t2(a INT, b INT, c INT, d INT, INDEX(a, b, c)); +INSERT INTO t2 (a, b, c) SELECT i, i, i FROM t1; + +FLUSH STATUS; +SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5; +SHOW SESSION STATUS LIKE 'Sort%'; +SHOW STATUS LIKE 'Handler_read_%'; + +let $cnt = `SELECT COUNT(*) FROM t2 WHERE b = 10`; + +FLUSH STATUS; +UPDATE t2 SET d = 10 WHERE b = 10 ORDER BY a, c LIMIT 5; +SHOW SESSION STATUS LIKE 'Sort%'; +SHOW STATUS LIKE 'Handler_read_%'; +--echo ## should be 5 (previous LIMIT) +SELECT COUNT(*) FROM t2 WHERE b = 10 AND d = 10 ORDER BY a, c; + +DROP TABLE t2; + +--echo # +--echo # same test as above (constant inside ORDER BY list), but with +--echo # a larger table - should not use filesort +--echo # + +CREATE TABLE t2(a INT, b INT, c INT, d INT, INDEX(a, b, c)); +INSERT INTO t2 (a, b, c) SELECT i, i, i FROM t1; + +INSERT INTO t2 (a, b, c) SELECT t1.i, t1.i, t1.i FROM t1, t1 x1, t1 x2; + +FLUSH STATUS; +SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5; +SHOW SESSION STATUS LIKE 'Sort%'; +SHOW STATUS LIKE 'Handler_read_%'; + +let $cnt = `SELECT COUNT(*) FROM t2 WHERE b = 10`; + +FLUSH STATUS; +UPDATE t2 SET d = 10 WHERE b = 10 ORDER BY a, c LIMIT 5; +SHOW SESSION STATUS LIKE 'Sort%'; +SHOW STATUS LIKE 'Handler_read_%'; +--echo ## should be 5 (previous LIMIT) +SELECT COUNT(*) FROM t2 WHERE b = 10 AND d = 10 ORDER BY a, c; + +DROP TABLE t2; + +--echo # +--echo # as above + partial index, should use filesort +--echo # + +CREATE TABLE t2 (a CHAR(2), b CHAR(2), c CHAR(2), d CHAR(2), INDEX (a,b(1),c)); +INSERT INTO t2 SELECT i, i, i, i FROM t1; + +FLUSH STATUS; +SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5; +SHOW SESSION STATUS LIKE 'Sort%'; +SHOW STATUS LIKE 'Handler_read_%'; + +FLUSH STATUS; +UPDATE t2 SET d = 10 WHERE b = 10 ORDER BY a, c LIMIT 5; +SHOW SESSION STATUS LIKE 'Sort%'; +SHOW STATUS LIKE 'Handler_read_%'; +SELECT * FROM t2 WHERE b = 10 ORDER BY a, c; + +DROP TABLE t2; + +--echo # +--echo # as above but index is without HA_READ_ORDER flag, should use filesort +--echo # + +CREATE TABLE t2 (a CHAR(2), b CHAR(2), c CHAR(2), d CHAR(2), INDEX (a,b,c)) ENGINE=HEAP; +INSERT INTO t2 SELECT i, i, i, i FROM t1; + +FLUSH STATUS; +SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5; +SHOW SESSION STATUS LIKE 'Sort%'; +SHOW STATUS LIKE 'Handler_read_%'; + +FLUSH STATUS; +UPDATE t2 SET d = 10 WHERE b = 10 ORDER BY a, c LIMIT 5; +SHOW SESSION STATUS LIKE 'Sort%'; +SHOW STATUS LIKE 'Handler_read_%'; +SELECT * FROM t2 WHERE b = 10 ORDER BY a, c; + +DROP TABLE t2; + +--echo # +--echo # quick select is Index Merge, should use filesort +--echo # + +CREATE TABLE t2 (i INT, key1 INT, key2 INT, INDEX (key1), INDEX (key2)); +INSERT INTO t2 (key1, key2) SELECT i, i FROM t1; + +FLUSH STATUS; +SELECT * FROM t2 WHERE key1 < 13 or key2 < 14 ORDER BY key1; +SHOW SESSION STATUS LIKE 'Sort%'; +SHOW STATUS LIKE 'Handler_read_%'; +--replace_column 1 x 2 x 3 x 4 x 5 x 6 x 7 x 8 x 9 x 10 x +EXPLAIN EXTENDED SELECT * FROM t2 WHERE key1 < 13 or key2 < 14 ORDER BY key1; + +FLUSH STATUS; +UPDATE t2 SET i = 123 WHERE key1 < 13 or key2 < 14 ORDER BY key1; +SHOW SESSION STATUS LIKE 'Sort%'; +SHOW STATUS LIKE 'Handler_read_%'; +SELECT * FROM t2 WHERE key1 < 13 or key2 < 14 ORDER BY key1; +--replace_column 1 x 2 x 3 x 4 x 5 x 6 x 7 x 8 x 9 x 10 x +EXPLAIN EXTENDED SELECT * FROM t2 WHERE key1 < 13 or key2 < 14 ORDER BY key1; + +DROP TABLE t2; + +--echo # +--echo # reverse quick select, should not use filesort +--echo # + +CREATE TABLE t2(a INT, i INT PRIMARY KEY); +INSERT INTO t2 (i) SELECT i FROM t1; + +FLUSH STATUS; +SELECT * FROM t2 WHERE i > 10 AND i <= 18 ORDER BY i DESC LIMIT 5; +SHOW SESSION STATUS LIKE 'Sort%'; +SHOW STATUS LIKE 'Handler_read_%'; + +FLUSH STATUS; +UPDATE t2 SET a = 10 WHERE i > 10 AND i <= 18 ORDER BY i DESC LIMIT 5; +SHOW SESSION STATUS LIKE 'Sort%'; +SHOW STATUS LIKE 'Handler_read_%'; +SELECT * FROM t2 WHERE i > 10 AND i <= 18 ORDER BY i; + +DROP TABLE t2; + +--echo # +--echo # mixed sorting direction, should use filesort +--echo # + +CREATE TABLE t2 (a CHAR(2), b CHAR(2), c CHAR(2), INDEX (a, b)); +INSERT INTO t2 SELECT i, i, i FROM t1; + +FLUSH STATUS; +SELECT * FROM t2 ORDER BY a, b DESC LIMIT 5; +SHOW SESSION STATUS LIKE 'Sort%'; +SHOW STATUS LIKE 'Handler_read_%'; + +FLUSH STATUS; +UPDATE t2 SET c = 10 ORDER BY a, b DESC LIMIT 5; +SHOW SESSION STATUS LIKE 'Sort%'; +SHOW STATUS LIKE 'Handler_read_%'; +SELECT * FROM t2 WHERE c = 10 ORDER BY a, b DESC; + +DROP TABLE t2; + +--echo # +--echo # LIMIT with no WHERE and DESC direction, should not use filesort +--echo # + +CREATE TABLE t2 (a CHAR(2), b CHAR(2), c CHAR(2), INDEX (a, b)); +INSERT INTO t2 (a, b) SELECT i, i FROM t1; +INSERT INTO t2 (a, b) SELECT t1.i, t1.i FROM t1, t1 x1, t1 x2; + +FLUSH STATUS; +SELECT * FROM t2 ORDER BY a, b LIMIT 5; +SHOW SESSION STATUS LIKE 'Sort%'; +SHOW STATUS LIKE 'Handler_read_%'; + +FLUSH STATUS; +SELECT * FROM t2 ORDER BY a DESC, b DESC LIMIT 5; +SHOW SESSION STATUS LIKE 'Sort%'; +SHOW STATUS LIKE 'Handler_read_%'; + +FLUSH STATUS; +UPDATE t2 SET c = 10 ORDER BY a DESC, b DESC LIMIT 5; +SHOW SESSION STATUS LIKE 'Sort%'; +SHOW STATUS LIKE 'Handler_read_%'; +SELECT * FROM t2 WHERE c = 10 ORDER BY a DESC, b DESC; + +DROP TABLE t1, t2; + + +--echo # +--echo # Bug #53742: UPDATEs have no effect after applying patch for bug 36569 +--echo # + +--disable_warnings +CREATE TABLE t1 ( + pk INT NOT NULL AUTO_INCREMENT, + c1_idx CHAR(1) DEFAULT 'y', + c2 INT, + PRIMARY KEY (pk), + INDEX c1_idx (c1_idx) +) ENGINE=InnoDB; +--enable_warnings + +INSERT INTO t1 VALUES (), (), (), (); + +SELECT * FROM t1 WHERE c1_idx = 'y' ORDER BY pk DESC LIMIT 2; +UPDATE t1 SET c2 = 0 WHERE c1_idx = 'y' ORDER BY pk DESC LIMIT 2; +SELECT * FROM t1 WHERE c1_idx = 'y' ORDER BY pk DESC LIMIT 2; +SELECT * FROM t1 WHERE c1_idx = 'y' ORDER BY pk DESC; + +DELETE FROM t1 WHERE c1_idx = 'y' ORDER BY pk DESC LIMIT 2; +SELECT * FROM t1 WHERE c1_idx = 'y' ORDER BY pk DESC; + +DROP TABLE t1; + diff --git a/mysql-test/t/sp_trans_log.test b/mysql-test/t/sp_trans_log.test index f162ee5cea6..972c3dd36eb 100644 --- a/mysql-test/t/sp_trans_log.test +++ b/mysql-test/t/sp_trans_log.test @@ -34,7 +34,10 @@ end| reset master| --error ER_DUP_ENTRY insert into t2 values (bug23333(),1)| -# the following must show there is (are) events after the query */ +# the following must show there are events after the query +# the binlog_limit is used to hide the differences between the mixed +# and row logging formats after BUG#53259 +let $binlog_limit= 0, 4| source include/show_binlog_events.inc| select count(*),@a from t1 /* must be 1,1 */| diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index 06aaf5dfb7f..2e442e7f897 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -3921,3 +3921,28 @@ GROUP BY DROP TABLE t3; DROP TABLE t2; DROP TABLE t1; + + +--echo # +--echo # Bug #52711: Segfault when doing EXPLAIN SELECT with +--echo # union...order by (select... where...) +--echo # + +CREATE TABLE t1 (a VARCHAR(10), FULLTEXT KEY a (a)); +INSERT INTO t1 VALUES (1),(2); +CREATE TABLE t2 (b INT); +INSERT INTO t2 VALUES (1),(2); + +--echo # Should not crash +--disable_result_log +EXPLAIN +SELECT * FROM t2 UNION SELECT * FROM t2 + ORDER BY (SELECT * FROM t1 WHERE MATCH(a) AGAINST ('+abc' IN BOOLEAN MODE)); + +--echo # Should not crash +SELECT * FROM t2 UNION SELECT * FROM t2 + ORDER BY (SELECT * FROM t1 WHERE MATCH(a) AGAINST ('+abc' IN BOOLEAN MODE)); +DROP TABLE t1,t2; +--enable_result_log + +--echo End of 5.1 tests diff --git a/mysql-test/t/subselect4.test b/mysql-test/t/subselect4.test index 440eca22828..fb0f58bf804 100644 --- a/mysql-test/t/subselect4.test +++ b/mysql-test/t/subselect4.test @@ -62,3 +62,32 @@ FROM t3 WHERE 1 = 0 GROUP BY 1; DROP TABLE t1,t2,t3; --echo End of 5.0 tests. + +--echo # +--echo # Bug#53236 Segfault in DTCollation::set(DTCollation&) +--echo # + +CREATE TABLE t1 ( + pk INTEGER AUTO_INCREMENT, + col_varchar VARCHAR(1), + PRIMARY KEY (pk) +) +; + +INSERT INTO t1 (col_varchar) +VALUES +('w'), +('m') +; + +SELECT table1.pk +FROM ( t1 AS table1 JOIN t1 AS table2 ON (table1.col_varchar = + table2.col_varchar) ) +WHERE ( 1, 2 ) IN ( SELECT SUBQUERY1_t1.pk AS SUBQUERY1_field1, + SUBQUERY1_t1.pk AS SUBQUERY1_field2 + FROM ( t1 AS SUBQUERY1_t1 JOIN t1 AS SUBQUERY1_t2 + ON (SUBQUERY1_t2.col_varchar = + SUBQUERY1_t1.col_varchar) ) ) +; + +drop table t1; diff --git a/mysql-test/t/update.test b/mysql-test/t/update.test index f6708828a6b..c515f8873d8 100644 --- a/mysql-test/t/update.test +++ b/mysql-test/t/update.test @@ -483,3 +483,23 @@ UPDATE IGNORE v1 SET a = 1; SET SESSION sql_safe_updates = DEFAULT; DROP TABLE t1; DROP VIEW v1; + +--echo # +--echo # Bug#54734 assert in Diagnostics_area::set_ok_status +--echo # + +--disable_warnings +DROP TABLE IF EXISTS t1, not_exists; +DROP FUNCTION IF EXISTS f1; +DROP VIEW IF EXISTS v1; +--enable_warnings + +CREATE TABLE t1 (PRIMARY KEY(pk)) AS SELECT 1 AS pk; +CREATE FUNCTION f1() RETURNS INTEGER RETURN (SELECT 1 FROM not_exists); +CREATE VIEW v1 AS SELECT pk FROM t1 WHERE f1() = 13; +--error ER_VIEW_INVALID +UPDATE v1 SET pk = 7 WHERE pk > 0; + +DROP VIEW v1; +DROP FUNCTION f1; +DROP TABLE t1; diff --git a/mysql-test/t/windows.test b/mysql-test/t/windows.test index b7d31948d23..b7d31948d23 100755..100644 --- a/mysql-test/t/windows.test +++ b/mysql-test/t/windows.test |