diff options
Diffstat (limited to 'mysql-test')
68 files changed, 1170 insertions, 163 deletions
diff --git a/mysql-test/include/commit.inc b/mysql-test/include/commit.inc index 9bd11bf33c1..0d2e3425141 100644 --- a/mysql-test/include/commit.inc +++ b/mysql-test/include/commit.inc @@ -732,6 +732,6 @@ call p_verify_status_increment(0, 0, 0, 0); --echo # --echo # Cleanup --echo # -drop table t1; +drop table t1, t2, t3; drop procedure p_verify_status_increment; drop function f1; diff --git a/mysql-test/include/mix1.inc b/mysql-test/include/mix1.inc index c1fd5cac10c..a4cd6019be8 100644 --- a/mysql-test/include/mix1.inc +++ b/mysql-test/include/mix1.inc @@ -1090,6 +1090,19 @@ SELECT * FROM t1 WHERE id=191 OR id IS NULL ORDER BY d; DROP TABLE t1; +# +# Bug #34223: Assertion failed: (optp->var_type & 127) == 8, +# file .\my_getopt.c, line 830 +# + +set @my_innodb_autoextend_increment=@@global.innodb_autoextend_increment; +set global innodb_autoextend_increment=8; +set global innodb_autoextend_increment=@my_innodb_autoextend_increment; + +set @my_innodb_commit_concurrency=@@global.innodb_commit_concurrency; +set global innodb_commit_concurrency=0; +set global innodb_commit_concurrency=@my_innodb_commit_concurrency; + --echo End of 5.0 tests # Fix for BUG#19243 "wrong LAST_INSERT_ID() after ON DUPLICATE KEY diff --git a/mysql-test/lib/mtr_report.pl b/mysql-test/lib/mtr_report.pl index ecfdc00eaf9..84cb4eddf36 100644 --- a/mysql-test/lib/mtr_report.pl +++ b/mysql-test/lib/mtr_report.pl @@ -386,7 +386,10 @@ sub mtr_report_stats ($) { # rpl_temporary has an error on slave that can be ignored ($testname eq 'rpl.rpl_temporary' and (/Slave: Can\'t find record in \'user\' Error_code: 1032/ - )) + )) or + + # Test case for Bug#31590 produces the following error: + /Out of sort memory; increase server sort buffer size/ ) { next; # Skip these lines diff --git a/mysql-test/r/alter_table.result b/mysql-test/r/alter_table.result index 68b636829fc..3cec1f546d2 100644 --- a/mysql-test/r/alter_table.result +++ b/mysql-test/r/alter_table.result @@ -539,7 +539,7 @@ F2E5F1F2 drop table t1; create table t1 ( a timestamp ); alter table t1 add unique ( a(1) ); -ERROR HY000: Incorrect sub part key; the used key part isn't a string, the used length is longer than the key part, or the storage engine doesn't support unique sub keys +ERROR HY000: Incorrect prefix key; the used key part isn't a string, the used length is longer than the key part, or the storage engine doesn't support unique prefix keys drop table t1; drop table if exists t1; create table t1 (a int, key(a)); @@ -953,12 +953,12 @@ t1 CREATE TABLE `t1` ( KEY `b_2` (`b`(50)) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 CREATE TABLE t2 (a INT, KEY (a(20))); -ERROR HY000: Incorrect sub part key; the used key part isn't a string, the used length is longer than the key part, or the storage engine doesn't support unique sub keys +ERROR HY000: Incorrect prefix key; the used key part isn't a string, the used length is longer than the key part, or the storage engine doesn't support unique prefix keys ALTER TABLE t1 ADD d INT; ALTER TABLE t1 ADD KEY (d(20)); -ERROR HY000: Incorrect sub part key; the used key part isn't a string, the used length is longer than the key part, or the storage engine doesn't support unique sub keys +ERROR HY000: Incorrect prefix key; the used key part isn't a string, the used length is longer than the key part, or the storage engine doesn't support unique prefix keys ALTER TABLE t1 ADD e GEOMETRY NOT NULL, ADD SPATIAL KEY (e(30)); -ERROR HY000: Incorrect sub part key; the used key part isn't a string, the used length is longer than the key part, or the storage engine doesn't support unique sub keys +ERROR HY000: Incorrect prefix key; the used key part isn't a string, the used length is longer than the key part, or the storage engine doesn't support unique prefix keys DROP TABLE t1; CREATE TABLE t1 (s CHAR(8) BINARY); INSERT INTO t1 VALUES ('test'); diff --git a/mysql-test/r/commit_1innodb.result b/mysql-test/r/commit_1innodb.result index 8598e231861..196d65a48ad 100644 --- a/mysql-test/r/commit_1innodb.result +++ b/mysql-test/r/commit_1innodb.result @@ -878,6 +878,6 @@ SUCCESS # # Cleanup # -drop table t1; +drop table t1, t2, t3; drop procedure p_verify_status_increment; drop function f1; diff --git a/mysql-test/r/compare.result b/mysql-test/r/compare.result index c141b255716..44c258d7611 100644 --- a/mysql-test/r/compare.result +++ b/mysql-test/r/compare.result @@ -53,3 +53,41 @@ a b Warnings: Warning 1292 Truncated incorrect DOUBLE value: '' drop table if exists t1; +CREATE TABLE t1 (b int(2) zerofill, c int(2) zerofill); +INSERT INTO t1 (b,c) VALUES (1,2), (1,1), (2,2); +SELECT CONCAT(b,c), CONCAT(b,c) = '0101' FROM t1; +CONCAT(b,c) CONCAT(b,c) = '0101' +0102 0 +0101 1 +0202 0 +EXPLAIN EXTENDED SELECT b,c FROM t1 WHERE b = 1 AND CONCAT(b,c) = '0101'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where ((`test`.`t1`.`b` = 1) and (concat('01',`test`.`t1`.`c`) = '0101')) +SELECT b,c FROM t1 WHERE b = 1 AND CONCAT(b,c) = '0101'; +b c +01 01 +CREATE TABLE t2 (a int); +INSERT INTO t2 VALUES (1),(2); +SELECT a, +(SELECT COUNT(*) FROM t1 +WHERE b = t2.a AND CONCAT(b,c) = CONCAT('0',t2.a,'01')) x +FROM t2 ORDER BY a; +a x +1 1 +2 0 +EXPLAIN EXTENDED +SELECT a, +(SELECT COUNT(*) FROM t1 +WHERE b = t2.a AND CONCAT(b,c) = CONCAT('0',t2.a,'01')) x +FROM t2 ORDER BY a; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 Using filesort +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 3 100.00 Using where +Warnings: +Note 1276 Field or reference 'test.t2.a' of SELECT #2 was resolved in SELECT #1 +Note 1276 Field or reference 'test.t2.a' of SELECT #2 was resolved in SELECT #1 +Note 1003 select `test`.`t2`.`a` AS `a`,(select count(0) AS `COUNT(*)` from `test`.`t1` where ((`test`.`t1`.`b` = `test`.`t2`.`a`) and (concat(`test`.`t1`.`b`,`test`.`t1`.`c`) = concat('0',`test`.`t2`.`a`,'01')))) AS `x` from `test`.`t2` order by `test`.`t2`.`a` +DROP TABLE t1,t2; +End of 5.0 tests diff --git a/mysql-test/r/create.result b/mysql-test/r/create.result index 0dfc9c1e761..81463a3f0f2 100644 --- a/mysql-test/r/create.result +++ b/mysql-test/r/create.result @@ -1545,6 +1545,20 @@ Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 7 drop table t1,t2; +CREATE TABLE t1(c1 VARCHAR(33), KEY USING BTREE (c1)); +DROP TABLE t1; +CREATE TABLE t1(c1 VARCHAR(33), KEY (c1) USING BTREE); +DROP TABLE t1; +CREATE TABLE t1(c1 VARCHAR(33), KEY USING BTREE (c1) USING HASH) ENGINE=MEMORY; +SHOW INDEX FROM t1; +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment +t1 1 c1 1 c1 NULL 0 NULL NULL YES HASH +DROP TABLE t1; +CREATE TABLE t1(c1 VARCHAR(33), KEY USING HASH (c1) USING BTREE) ENGINE=MEMORY; +SHOW INDEX FROM t1; +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment +t1 1 c1 1 c1 A NULL NULL NULL YES BTREE +DROP TABLE t1; End of 5.0 tests CREATE TABLE t1 (a int, b int); insert into t1 values (1,1),(1,2); diff --git a/mysql-test/r/ctype_cp932_binlog_stm.result b/mysql-test/r/ctype_cp932_binlog_stm.result index cbdafc95b2a..946c06f1e07 100644 --- a/mysql-test/r/ctype_cp932_binlog_stm.result +++ b/mysql-test/r/ctype_cp932_binlog_stm.result @@ -40,9 +40,9 @@ IN ind DECIMAL(10,2)) BEGIN INSERT INTO t4 VALUES (ins1, ins2, ind); END -master-bin.000001 783 Query 1 999 use `test`; INSERT INTO t4 VALUES ( NAME_CONST('ins1',_latin1 0x466F6F2773206120426172), NAME_CONST('ins2',_cp932 0xED40ED41ED42), NAME_CONST('ind',47.93)) -master-bin.000001 999 Query 1 1088 use `test`; DROP PROCEDURE bug18293 -master-bin.000001 1088 Query 1 1167 use `test`; DROP TABLE t4 +master-bin.000001 783 Query 1 991 use `test`; INSERT INTO t4 VALUES ( NAME_CONST('ins1',_latin1 0x466F6F2773206120426172), NAME_CONST('ins2',_cp932 0xED40ED41ED42), NAME_CONST('ind',47.93)) +master-bin.000001 991 Query 1 1080 use `test`; DROP PROCEDURE bug18293 +master-bin.000001 1080 Query 1 1159 use `test`; DROP TABLE t4 End of 5.0 tests SHOW BINLOG EVENTS FROM 364; ERROR HY000: Error when executing command SHOW BINLOG EVENTS: Wrong offset or I/O error diff --git a/mysql-test/r/func_gconcat.result b/mysql-test/r/func_gconcat.result index b5bfadf1f57..04f83788571 100644 --- a/mysql-test/r/func_gconcat.result +++ b/mysql-test/r/func_gconcat.result @@ -937,4 +937,19 @@ SELECT GROUP_CONCAT(DISTINCT b, a ORDER BY b) FROM t1; GROUP_CONCAT(DISTINCT b, a ORDER BY b) 11,22,32 DROP TABLE t1, t2, t3; +CREATE TABLE t1(a INT); +INSERT INTO t1 VALUES (),(); +SELECT s1.d1 FROM +( +SELECT +t1.a as d1, +GROUP_CONCAT(DISTINCT t1.a) AS d2 +FROM +t1 AS t1, +t1 AS t2 +GROUP BY 1 +) AS s1; +d1 +NULL +DROP TABLE t1; End of 5.0 tests diff --git a/mysql-test/r/func_math.result b/mysql-test/r/func_math.result index 6a476d12896..b4a07f18521 100644 --- a/mysql-test/r/func_math.result +++ b/mysql-test/r/func_math.result @@ -408,3 +408,22 @@ a DIV 2 0 DROP TABLE t1; End of 5.0 tests +SELECT 1e308 + 1e308; +1e308 + 1e308 +NULL +SELECT -1e308 - 1e308; +-1e308 - 1e308 +NULL +SELECT 1e300 * 1e300; +1e300 * 1e300 +NULL +SELECT 1e300 / 1e-300; +1e300 / 1e-300 +NULL +SELECT EXP(750); +EXP(750) +NULL +SELECT POW(10, 309); +POW(10, 309) +NULL +End of 5.1 tests diff --git a/mysql-test/r/func_misc.result b/mysql-test/r/func_misc.result index 766b0277955..a6b912b4e0a 100644 --- a/mysql-test/r/func_misc.result +++ b/mysql-test/r/func_misc.result @@ -212,6 +212,25 @@ test SELECT NAME_CONST('test', 'test'); test test +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(2),(3); +SELECT NAME_CONST('flag',1) * MAX(a) FROM t1; +NAME_CONST('flag',1) * MAX(a) +3 +SELECT NAME_CONST('flag',1.5) * MAX(a) FROM t1; +NAME_CONST('flag',1.5) * MAX(a) +4.5 +SELECT NAME_CONST('flag',-1) * MAX(a) FROM t1; +NAME_CONST('flag',-1) * MAX(a) +-3 +SELECT NAME_CONST('flag',-1.5) * MAX(a) FROM t1; +NAME_CONST('flag',-1.5) * MAX(a) +-4.5 +SELECT NAME_CONST('flag', SQRT(4)) * MAX(a) FROM t1; +ERROR HY000: Incorrect arguments to NAME_CONST +SELECT NAME_CONST('flag',-SQRT(4)) * MAX(a) FROM t1; +ERROR HY000: Incorrect arguments to NAME_CONST +DROP TABLE t1; CREATE TABLE t1 (a int); INSERT INTO t1 VALUES (5), (2); SELECT NAME_CONST(x,2) FROM (SELECT a x FROM t1) t; diff --git a/mysql-test/r/func_str.result b/mysql-test/r/func_str.result index ea79eefb316..78748a4622f 100644 --- a/mysql-test/r/func_str.result +++ b/mysql-test/r/func_str.result @@ -1431,6 +1431,11 @@ benchmark(100, NULL) select benchmark(NULL, 1+1); benchmark(NULL, 1+1) NULL +select benchmark(-1, 1); +benchmark(-1, 1) +NULL +Warnings: +Error 1411 Incorrect count value: '-1' for function benchmark set @password="password"; set @my_data="clear text to encode"; select md5(encode(@my_data, "password")); diff --git a/mysql-test/r/func_time.result b/mysql-test/r/func_time.result index 9cdf8a3cf50..cc0fb88b791 100644 --- a/mysql-test/r/func_time.result +++ b/mysql-test/r/func_time.result @@ -666,6 +666,8 @@ timestampadd(SQL_TSI_SECOND, 1, date) select timestampadd(SQL_TSI_FRAC_SECOND, 1, date) from t1; timestampadd(SQL_TSI_FRAC_SECOND, 1, date) 2003-01-02 00:00:00.000001 +Warnings: +Warning 1287 The syntax 'FRAC_SECOND' is deprecated and will be removed in MySQL 6.2. Please use MICROSECOND instead select timestampdiff(MONTH, '2001-02-01', '2001-05-01') as a; a 3 @@ -699,6 +701,8 @@ a select timestampdiff(SQL_TSI_FRAC_SECOND, '2001-02-01 12:59:59.120000', '2001-05-01 12:58:58.119999') as a; a 7689538999999 +Warnings: +Warning 1287 The syntax 'FRAC_SECOND' is deprecated and will be removed in MySQL 6.2. Please use MICROSECOND instead select timestampdiff(SQL_TSI_DAY, '1986-02-01', '1986-03-01') as a1, timestampdiff(SQL_TSI_DAY, '1900-02-01', '1900-03-01') as a2, timestampdiff(SQL_TSI_DAY, '1996-02-01', '1996-03-01') as a3, @@ -1069,6 +1073,7 @@ timestampdiff(SQL_TSI_FRAC_SECOND, '2001-02-01 12:59:59.120000', '2001-05-01 12: id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: +Warning 1287 The syntax 'FRAC_SECOND' is deprecated and will be removed in MySQL 6.2. Please use MICROSECOND instead Note 1003 select timestampdiff(WEEK,'2001-02-01','2001-05-01') AS `a1`,timestampdiff(SECOND_FRAC,'2001-02-01 12:59:59.120000','2001-05-01 12:58:58.119999') AS `a2` select time_format('100:00:00', '%H %k %h %I %l'); time_format('100:00:00', '%H %k %h %I %l') @@ -1261,6 +1266,24 @@ DATE_ADD(20071108, INTERVAL 1 DAY) select LAST_DAY('2007-12-06 08:59:19.05') - INTERVAL 1 SECOND; LAST_DAY('2007-12-06 08:59:19.05') - INTERVAL 1 SECOND 2007-12-30 23:59:59 +SELECT TIMESTAMPADD(FRAC_SECOND, 1, '2008-02-18'); +TIMESTAMPADD(FRAC_SECOND, 1, '2008-02-18') +2008-02-18 00:00:00.000001 +Warnings: +Warning 1287 The syntax 'FRAC_SECOND' is deprecated and will be removed in MySQL 6.2. Please use MICROSECOND instead +SELECT TIMESTAMPDIFF(FRAC_SECOND, '2008-02-17', '2008-02-18'); +TIMESTAMPDIFF(FRAC_SECOND, '2008-02-17', '2008-02-18') +86400000000 +Warnings: +Warning 1287 The syntax 'FRAC_SECOND' is deprecated and will be removed in MySQL 6.2. Please use MICROSECOND instead +SELECT DATE_ADD('2008-02-18', INTERVAL 1 FRAC_SECOND); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FRAC_SECOND)' at line 1 +SELECT DATE_SUB('2008-02-18', INTERVAL 1 FRAC_SECOND); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FRAC_SECOND)' at line 1 +SELECT '2008-02-18' + INTERVAL 1 FRAC_SECOND; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FRAC_SECOND' at line 1 +SELECT '2008-02-18' - INTERVAL 1 FRAC_SECOND; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FRAC_SECOND' at line 1 End of 5.0 tests select date_sub("0050-01-01 00:00:01",INTERVAL 2 SECOND); date_sub("0050-01-01 00:00:01",INTERVAL 2 SECOND) diff --git a/mysql-test/r/grant3.result b/mysql-test/r/grant3.result index cc7f46855b2..f38848111ad 100644 --- a/mysql-test/r/grant3.result +++ b/mysql-test/r/grant3.result @@ -138,3 +138,20 @@ SELECT user, host, db, select_priv FROM mysql.db where user = 'CUser2' order by user host db select_priv DROP USER CUser2@localhost; DROP USER CUser2@LOCALHOST; +CREATE DATABASE mysqltest_1; +CREATE TABLE mysqltest_1.t1 (a INT); +CREATE USER 'mysqltest1'@'%'; +GRANT SELECT, UPDATE ON `mysqltest_1`.* TO 'mysqltest1'@'%'; +REVOKE SELECT ON `mysqltest_1`.* FROM 'mysqltest1'@'%'; +GRANT SELECT, UPDATE ON `mysqltest\_1`.* TO 'mysqltest1'@'%'; +FLUSH PRIVILEGES; +SHOW GRANTS; +Grants for mysqltest1@% +GRANT USAGE ON *.* TO 'mysqltest1'@'%' +GRANT SELECT, UPDATE ON `mysqltest\_1`.* TO 'mysqltest1'@'%' +GRANT UPDATE ON `mysqltest_1`.* TO 'mysqltest1'@'%' +SELECT * FROM mysqltest_1.t1; +a +DROP USER 'mysqltest1'@'%'; +DROP DATABASE mysqltest_1; +End of 5.0 tests diff --git a/mysql-test/r/heap.result b/mysql-test/r/heap.result index ddf675e2f73..a2c208ee3e1 100644 --- a/mysql-test/r/heap.result +++ b/mysql-test/r/heap.result @@ -256,6 +256,7 @@ set @a=repeat(' ',20); insert into t1 values (concat('+',@a),concat('+',@a),concat('+',@a)); Warnings: Note 1265 Data truncated for column 'v' at row 1 +Note 1265 Data truncated for column 'c' at row 1 select concat('*',v,'*',c,'*',t,'*') from t1; concat('*',v,'*',c,'*',t,'*') *+ *+*+ * diff --git a/mysql-test/r/innodb.result b/mysql-test/r/innodb.result index 15d451c03ae..bdfa2471cb8 100644 --- a/mysql-test/r/innodb.result +++ b/mysql-test/r/innodb.result @@ -1836,6 +1836,7 @@ set @a=repeat(' ',20); insert into t1 values (concat('+',@a),concat('+',@a),concat('+',@a)); Warnings: Note 1265 Data truncated for column 'v' at row 1 +Note 1265 Data truncated for column 'c' at row 1 select concat('*',v,'*',c,'*',t,'*') from t1; concat('*',v,'*',c,'*',t,'*') *+ *+*+ * diff --git a/mysql-test/r/innodb_mysql.result b/mysql-test/r/innodb_mysql.result index ffc3ca25a99..c7f5e72bb8b 100644 --- a/mysql-test/r/innodb_mysql.result +++ b/mysql-test/r/innodb_mysql.result @@ -1356,6 +1356,12 @@ id type d NULL member 3 NULL member 4 DROP TABLE t1; +set @my_innodb_autoextend_increment=@@global.innodb_autoextend_increment; +set global innodb_autoextend_increment=8; +set global innodb_autoextend_increment=@my_innodb_autoextend_increment; +set @my_innodb_commit_concurrency=@@global.innodb_commit_concurrency; +set global innodb_commit_concurrency=0; +set global innodb_commit_concurrency=@my_innodb_commit_concurrency; End of 5.0 tests CREATE TABLE `t2` ( `k` int(11) NOT NULL auto_increment, diff --git a/mysql-test/r/mix2_myisam.result b/mysql-test/r/mix2_myisam.result index cabc4de8d21..ee10f265933 100644 --- a/mysql-test/r/mix2_myisam.result +++ b/mysql-test/r/mix2_myisam.result @@ -1415,6 +1415,7 @@ set @a=repeat(' ',20); insert into t1 values (concat('+',@a),concat('+',@a),concat('+',@a)); Warnings: Note 1265 Data truncated for column 'v' at row 1 +Note 1265 Data truncated for column 'c' at row 1 select concat('*',v,'*',c,'*',t,'*') from t1; concat('*',v,'*',c,'*',t,'*') *+ *+*+ * diff --git a/mysql-test/r/myisam.result b/mysql-test/r/myisam.result index bf57b6e1006..112a7b7649b 100644 --- a/mysql-test/r/myisam.result +++ b/mysql-test/r/myisam.result @@ -1086,6 +1086,7 @@ set @a=repeat(' ',20); insert into t1 values (concat('+',@a),concat('+',@a),concat('+',@a)); Warnings: Note 1265 Data truncated for column 'v' at row 1 +Note 1265 Data truncated for column 'c' at row 1 select concat('*',v,'*',c,'*',t,'*') from t1; concat('*',v,'*',c,'*',t,'*') *+ *+*+ * diff --git a/mysql-test/r/mysqldump-no-binlog.result b/mysql-test/r/mysqldump-no-binlog.result new file mode 100644 index 00000000000..78bc19b7cba --- /dev/null +++ b/mysql-test/r/mysqldump-no-binlog.result @@ -0,0 +1 @@ +mysqldump: Error: Binlogging on server not active diff --git a/mysql-test/r/null_key.result b/mysql-test/r/null_key.result index 8a440284c53..58c587fe588 100644 --- a/mysql-test/r/null_key.result +++ b/mysql-test/r/null_key.result @@ -429,3 +429,21 @@ Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 5 DROP TABLE t1,t2,t3,t4; +CREATE TABLE t1 ( +a int(11) default NULL, +b int(11) default NULL, +KEY a (a,b) +); +INSERT INTO t1 VALUES (0,10),(0,11),(0,12); +CREATE TABLE t2 ( +a int(11) default NULL, +b int(11) default NULL, +KEY a (a) +); +INSERT INTO t2 VALUES (3,NULL),(3,11),(3,12); +SELECT * FROM t2 inner join t1 WHERE ( t1.a = 0 OR t1.a IS NULL) AND t2.a = 3 AND t2.b = t1.b; +a b a b +3 11 0 11 +3 12 0 12 +drop table t1, t2; +End of 5.0 tests diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result index 3d0f4915d0a..4320a7764de 100644 --- a/mysql-test/r/order_by.result +++ b/mysql-test/r/order_by.result @@ -1416,3 +1416,15 @@ SELECT @tmp_tables_after = @tmp_tables_before; @tmp_tables_after = @tmp_tables_before 1 DROP TABLE t1; +# +# Bug#31590: Wrong error message on sort buffer being too small. +# +create table t1(a int, b tinytext); +insert into t1 values (1,2),(3,2); +set session sort_buffer_size= 30000; +Warnings: +Warning 1292 Truncated incorrect sort_buffer_size value: '30000' +set session max_sort_length= 2180; +select * from t1 order by b; +ERROR HY001: Out of sort memory; increase server sort buffer size +drop table t1; diff --git a/mysql-test/r/partition_not_windows.result b/mysql-test/r/partition_not_windows.result index 31ea300e8ed..501d3a469a1 100644 --- a/mysql-test/r/partition_not_windows.result +++ b/mysql-test/r/partition_not_windows.result @@ -1,16 +1,63 @@ create table t1 (a int) engine myisam partition by range (a) subpartition by hash (a) -(partition p0 VALUES LESS THAN (1) DATA DIRECTORY = 'MYSQLTEST_VARDIR/master-data/tmpdata' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/master-data/tmpinx' +(partition p0 VALUES LESS THAN (1) DATA DIRECTORY = 'MYSQLTEST_VARDIR/tmp' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/tmp' (SUBPARTITION subpart00, SUBPARTITION subpart01)); Checking if file exists before alter ALTER TABLE t1 REORGANIZE PARTITION p0 INTO -(partition p1 VALUES LESS THAN (1) DATA DIRECTORY = 'MYSQLTEST_VARDIR/master-data/tmpdata' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/master-data/tmpinx' +(partition p1 VALUES LESS THAN (1) DATA DIRECTORY = 'MYSQLTEST_VARDIR/tmp' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/tmp' (SUBPARTITION subpart10, SUBPARTITION subpart11), -partition p2 VALUES LESS THAN (2) DATA DIRECTORY = 'MYSQLTEST_VARDIR/master-data/tmpdata' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/master-data/tmpinx' +partition p2 VALUES LESS THAN (2) DATA DIRECTORY = 'MYSQLTEST_VARDIR/tmp' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/tmp' (SUBPARTITION subpart20, SUBPARTITION subpart21)); Checking if file exists after alter drop table t1; +set @org_mode=@@sql_mode; +set @@sql_mode='NO_DIR_IN_CREATE'; +select @@sql_mode; +@@sql_mode +NO_DIR_IN_CREATE +create table t1 (i int ) +partition by range (i) +( +partition p01 values less than (1000) +data directory='/not/existing' + index directory='/not/existing' +); +show create table t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `i` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (i) (PARTITION p01 VALUES LESS THAN (1000) ENGINE = MyISAM) */ +DROP TABLE t1, t2; +set @@sql_mode=@org_mode; +CREATE TABLE t1(a INT) +PARTITION BY KEY (a) +(PARTITION p0 DATA DIRECTORY 'TEST_DIR/master-data/test'); +ERROR HY000: Incorrect arguments to DATA DIRECORY +CREATE TABLE t1(a INT) +PARTITION BY KEY (a) +(PARTITION p0 INDEX DIRECTORY 'TEST_DIR/master-data/test'); +ERROR HY000: Incorrect arguments to INDEX DIRECORY +CREATE TABLE ts (id INT, purchased DATE) +PARTITION BY RANGE(YEAR(purchased)) +SUBPARTITION BY HASH(TO_DAYS(purchased)) ( +PARTITION p0 VALUES LESS THAN (1990) ( +SUBPARTITION s0a +DATA DIRECTORY = 'TEST_DIR/master-data/test', +SUBPARTITION s0b +DATA DIRECTORY = 'TEST_DIR/master-data/test' +)); +ERROR HY000: Incorrect arguments to DATA DIRECORY +CREATE TABLE ts (id INT, purchased DATE) +PARTITION BY RANGE(YEAR(purchased)) +SUBPARTITION BY HASH(TO_DAYS(purchased)) ( +PARTITION p0 VALUES LESS THAN (1990) ( +SUBPARTITION s0a +INDEX DIRECTORY = 'TEST_DIR/master-data/test', +SUBPARTITION s0b +INDEX DIRECTORY = 'TEST_DIR/master-data/test' +)); +ERROR HY000: Incorrect arguments to INDEX DIRECORY DROP TABLE IF EXISTS `example`; CREATE TABLE `example` ( `ID_EXAMPLE` int(10) unsigned NOT NULL AUTO_INCREMENT, diff --git a/mysql-test/r/partition_symlink.result b/mysql-test/r/partition_symlink.result index 8b55124d7eb..bb81e4f34da 100644 --- a/mysql-test/r/partition_symlink.result +++ b/mysql-test/r/partition_symlink.result @@ -13,11 +13,11 @@ USE test; CREATE TABLE t1 (a INT) PARTITION BY LIST (a) ( PARTITION p0 VALUES IN (0) -DATA DIRECTORY 'MYSQLTEST_VARDIR/master-data/mysqltest2' - INDEX DIRECTORY 'MYSQLTEST_VARDIR/master-data/mysqltest2', +DATA DIRECTORY 'MYSQLTEST_VARDIR/tmp' + INDEX DIRECTORY 'MYSQLTEST_VARDIR/tmp', PARTITION p1 VALUES IN (1) -DATA DIRECTORY 'MYSQLTEST_VARDIR/master-data/test' - INDEX DIRECTORY 'MYSQLTEST_VARDIR/master-data/test', +DATA DIRECTORY 'MYSQLTEST_VARDIR/tmp' + INDEX DIRECTORY 'MYSQLTEST_VARDIR/tmp', PARTITION p2 VALUES IN (2) ); # without the patch for bug#32091 this would create @@ -49,32 +49,32 @@ USE mysqltest2; CREATE TABLE t1 (a INT) PARTITION BY LIST (a) ( PARTITION p0 VALUES IN (0) -DATA DIRECTORY 'MYSQLTEST_VARDIR/master-data/mysqltest2' - INDEX DIRECTORY 'MYSQLTEST_VARDIR/master-data/mysqltest2', +DATA DIRECTORY 'MYSQLTEST_VARDIR/tmp' + INDEX DIRECTORY 'MYSQLTEST_VARDIR/tmp', PARTITION p1 VALUES IN (1) -DATA DIRECTORY 'MYSQLTEST_VARDIR/master-data/test' - INDEX DIRECTORY 'MYSQLTEST_VARDIR/master-data/test' +DATA DIRECTORY 'MYSQLTEST_VARDIR/tmp' + INDEX DIRECTORY 'MYSQLTEST_VARDIR/tmp' ); # user mysqltest_1: USE test; CREATE TABLE t1 (a INT) PARTITION BY LIST (a) ( PARTITION p0 VALUES IN (0) -DATA DIRECTORY 'MYSQLTEST_VARDIR/master-data/mysqltest2' - INDEX DIRECTORY 'MYSQLTEST_VARDIR/master-data/mysqltest2', +DATA DIRECTORY 'MYSQLTEST_VARDIR/tmp' + INDEX DIRECTORY 'MYSQLTEST_VARDIR/tmp', PARTITION p1 VALUES IN (1) -DATA DIRECTORY 'MYSQLTEST_VARDIR/master-data/test' - INDEX DIRECTORY 'MYSQLTEST_VARDIR/master-data/test' +DATA DIRECTORY 'MYSQLTEST_VARDIR/tmp' + INDEX DIRECTORY 'MYSQLTEST_VARDIR/tmp' ); Got one of the listed errors CREATE TABLE t1 (a INT) PARTITION BY LIST (a) ( PARTITION p0 VALUES IN (0) -DATA DIRECTORY 'MYSQLTEST_VARDIR/master-data/test' - INDEX DIRECTORY 'MYSQLTEST_VARDIR/master-data/test', +DATA DIRECTORY 'MYSQLTEST_VARDIR/tmp' + INDEX DIRECTORY 'MYSQLTEST_VARDIR/tmp', PARTITION p1 VALUES IN (1) -DATA DIRECTORY 'MYSQLTEST_VARDIR/master-data/mysqltest2' - INDEX DIRECTORY 'MYSQLTEST_VARDIR/master-data/mysqltest2' +DATA DIRECTORY 'MYSQLTEST_VARDIR/tmp' + INDEX DIRECTORY 'MYSQLTEST_VARDIR/tmp' ); Got one of the listed errors # user root (cleanup): diff --git a/mysql-test/r/plugin.result b/mysql-test/r/plugin.result index e4af1535775..782d2a5a9a4 100644 --- a/mysql-test/r/plugin.result +++ b/mysql-test/r/plugin.result @@ -27,3 +27,30 @@ SET GLOBAL example_enum_var= e2; SET GLOBAL example_enum_var= impossible; ERROR 42000: Variable 'enum_var' can't be set to the value of 'impossible' UNINSTALL PLUGIN example; +INSTALL PLUGIN example SONAME 'ha_example.so'; +select @@session.sql_mode into @old_sql_mode; +set session sql_mode=''; +set global example_ulong_var=500; +select @@global.example_ulong_var; +@@global.example_ulong_var +500 +set global example_ulong_var=1111; +Warnings: +Warning 1292 Truncated incorrect ulong_var value: '1111' +select @@global.example_ulong_var; +@@global.example_ulong_var +1000 +set session sql_mode='STRICT_ALL_TABLES'; +set global example_ulong_var=500; +select @@global.example_ulong_var; +@@global.example_ulong_var +500 +set global example_ulong_var=1111; +ERROR 42000: Variable 'ulong_var' can't be set to the value of '1111' +select @@global.example_ulong_var; +@@global.example_ulong_var +500 +set session sql_mode=@old_sql_mode; +set session old=bla; +ERROR HY000: Variable 'old' is a read only variable +UNINSTALL PLUGIN example; diff --git a/mysql-test/r/range.result b/mysql-test/r/range.result index 92db6d8429f..f67a8f4cae6 100644 --- a/mysql-test/r/range.result +++ b/mysql-test/r/range.result @@ -1153,6 +1153,19 @@ explain select * from t1 where dateval >= '2007-01-01 00:00:00' and dateval <= ' id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range dateval dateval 4 NULL 2 Using where drop table t1; +CREATE TABLE t1 ( +a varchar(32), index (a) +) DEFAULT CHARSET=latin1 COLLATE=latin1_bin; +INSERT INTO t1 VALUES +('B'), ('A'), ('A'), ('C'), ('B'), ('A'), ('A'); +SELECT a FROM t1 WHERE a='b' OR a='B'; +a +B +B +EXPLAIN SELECT a FROM t1 WHERE a='b' OR a='B'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range a a 35 NULL 3 Using where; Using index +DROP TABLE t1; 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)); diff --git a/mysql-test/r/row.result b/mysql-test/r/row.result index d3d7988316e..3feaa6255c1 100644 --- a/mysql-test/r/row.result +++ b/mysql-test/r/row.result @@ -434,3 +434,12 @@ SELECT @x; @x 99 DROP TABLE t1; +CREATE TABLE t1 (a INT, b INT); +INSERT INTO t1 VALUES (1,1); +SELECT ROW(a, 1) IN (SELECT SUM(b), 1) FROM t1 GROUP BY a; +ROW(a, 1) IN (SELECT SUM(b), 1) +1 +SELECT ROW(a, 1) IN (SELECT SUM(b), 3) FROM t1 GROUP BY a; +ROW(a, 1) IN (SELECT SUM(b), 3) +0 +DROP TABLE t1; diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result index d3dca16fe4f..4b2464af688 100644 --- a/mysql-test/r/select.result +++ b/mysql-test/r/select.result @@ -4313,6 +4313,33 @@ SELECT * FROM t1 WHERE c1 > NULL + 1; c1 DROP TABLE t1; +CREATE TABLE t1 (a VARCHAR(10) NOT NULL PRIMARY KEY); +INSERT INTO t1 (a) VALUES ('foo0'), ('bar0'), ('baz0'); +SELECT * FROM t1 WHERE a IN (CONCAT('foo', 0), 'bar'); +a +foo0 +DROP TABLE t1; +CREATE TABLE t1 (a INT, b INT); +CREATE TABLE t2 (a INT, c INT, KEY(a)); +INSERT INTO t1 VALUES (1, 1), (2, 2); +INSERT INTO t2 VALUES (1, 1), (1, 2), (1, 3), (1, 4), (1, 5), +(2, 1), (2, 2), (2, 3), (2, 4), (2, 5), +(3, 1), (3, 2), (3, 3), (3, 4), (3, 5), +(4, 1), (4, 2), (4, 3), (4, 4), (4, 5); +FLUSH STATUS; +SELECT DISTINCT b FROM t1 LEFT JOIN t2 USING(a) WHERE c <= 3; +b +1 +2 +SHOW STATUS LIKE 'Handler_read%'; +Variable_name Value +Handler_read_first 0 +Handler_read_key 2 +Handler_read_next 0 +Handler_read_prev 0 +Handler_read_rnd 0 +Handler_read_rnd_next 6 +DROP TABLE t1, t2; End of 5.0 tests create table t1(a INT, KEY (a)); INSERT INTO t1 VALUES (1),(2),(3),(4),(5); diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result index a68be67a8e0..0a2c9a2da85 100644 --- a/mysql-test/r/sp.result +++ b/mysql-test/r/sp.result @@ -6862,6 +6862,23 @@ end// call proc_33618(20); drop table t_33618; drop procedure proc_33618; +# +# Bug#30787: Stored function ignores user defined alias. +# +use test; +drop function if exists func30787; +create table t1(f1 int); +insert into t1 values(1),(2); +create function func30787(p1 int) returns int +begin +return p1; +end | +select (select func30787(f1)) as ttt from t1; +ttt +1 +2 +drop function func30787; +drop table t1; # ------------------------------------------------------------------ # -- End of 5.0 tests # ------------------------------------------------------------------ diff --git a/mysql-test/r/sql_mode.result b/mysql-test/r/sql_mode.result index 2b34ff8c021..401340f204c 100644 --- a/mysql-test/r/sql_mode.result +++ b/mysql-test/r/sql_mode.result @@ -496,3 +496,13 @@ xb x xcx drop table t1; SET @@SQL_MODE=@OLD_SQL_MODE; +create user mysqltest_32753@localhost; +set @OLD_SQL_MODE=@@SESSION.SQL_MODE; +set session sql_mode='PAD_CHAR_TO_FULL_LENGTH'; +flush privileges; +select current_user(); +current_user() +mysqltest_32753@localhost +set session sql_mode=@OLD_SQL_MODE; +flush privileges; +drop user mysqltest_32753@localhost; diff --git a/mysql-test/r/strict.result b/mysql-test/r/strict.result index cc1a2535896..ef58a760297 100644 --- a/mysql-test/r/strict.result +++ b/mysql-test/r/strict.result @@ -895,7 +895,6 @@ ERROR 22003: Out of range value for column 'col1' at row 1 INSERT INTO t1 (col2) VALUES ('-1.2E-3'); ERROR 22003: Out of range value for column 'col2' at row 1 UPDATE t1 SET col1 =col1 * 5000 WHERE col1 > 0; -ERROR 22003: Out of range value for column 'col1' at row 3 UPDATE t1 SET col2 =col2 / 0 WHERE col2 > 0; ERROR 22012: Division by 0 UPDATE t1 SET col2= MOD(col2,0) WHERE col2 > 0; @@ -923,10 +922,10 @@ SELECT * FROM t1; col1 col2 -2.2e-307 0 1e-303 0 -1.7e+308 1.7e+308 +NULL 1.7e+308 -2.2e-307 0 -2e-307 0 -1.7e+308 1.7e+308 +NULL 1.7e+308 0 NULL 2 NULL NULL NULL @@ -934,6 +933,8 @@ NULL NULL DROP TABLE t1; CREATE TABLE t1 (col1 CHAR(5), col2 VARCHAR(6)); INSERT INTO t1 VALUES ('hello', 'hello'),('he', 'he'),('hello ', 'hello '); +Warnings: +Note 1265 Data truncated for column 'col1' at row 3 INSERT INTO t1 (col1) VALUES ('hellobob'); ERROR 22001: Data too long for column 'col1' at row 1 INSERT INTO t1 (col2) VALUES ('hellobob'); diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index 1ef250aae99..c739507f61b 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -4155,6 +4155,41 @@ SELECT ((a1,a2) IN (SELECT * FROM t2 WHERE b2 > 0)) IS NULL FROM t1; 0 0 DROP TABLE t1, t2; +create table t1(a int,b int,key(a),key(b)); +insert into t1(a,b) values (1,2),(2,1),(2,3),(3,4),(5,4),(5,5), +(6,7),(7,4),(5,3); +5 +4 +3 +2 +1 +26 +25 +24 +23 +22 +21 +20 +19 +18 +17 +16 +15 +14 +13 +12 +11 +10 +9 +8 +7 +6 +5 +4 +3 +2 +1 +drop table t1; CREATE TABLE t1 (s1 BINARY(5), s2 VARBINARY(5)); INSERT INTO t1 VALUES (0x41,0x41), (0x42,0x42), (0x43,0x43); SELECT s1, s2 FROM t1 WHERE s2 IN (SELECT s1 FROM t1); diff --git a/mysql-test/r/symlink.result b/mysql-test/r/symlink.result index 7599d80588a..68cc893e507 100644 --- a/mysql-test/r/symlink.result +++ b/mysql-test/r/symlink.result @@ -100,23 +100,15 @@ t1 CREATE TABLE `t1` ( ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; CREATE TABLE t1(a INT) -DATA DIRECTORY='TEST_DIR/master-data/mysql' -INDEX DIRECTORY='TEST_DIR/master-data/mysql'; -RENAME TABLE t1 TO user; -ERROR HY000: Can't create/write to file 'TEST_DIR/master-data/mysql/user.MYI' (Errcode: 17) -DROP TABLE t1; -show create table t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `i` int(11) DEFAULT NULL -) ENGINE=MyISAM DEFAULT CHARSET=latin1 -drop table t1; -show create table t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `i` int(11) DEFAULT NULL -) ENGINE=MyISAM DEFAULT CHARSET=latin1 -drop table t1; +DATA DIRECTORY='TEST_DIR/tmp' +INDEX DIRECTORY='TEST_DIR/tmp'; +ERROR HY000: Can't create/write to file 'TEST_DIR/tmp/t1.MYI' (Errcode: 17) +CREATE TABLE t2(a INT) +DATA DIRECTORY='TEST_DIR/tmp' +INDEX DIRECTORY='TEST_DIR/tmp'; +RENAME TABLE t2 TO t1; +ERROR HY000: Can't create/write to file 'TEST_DIR/tmp/t1.MYI' (Errcode: 17) +DROP TABLE t2; show create table t1; Table Create Table t1 CREATE TEMPORARY TABLE `t1` ( @@ -139,29 +131,28 @@ a 42 drop table t1; End of 4.1 tests -CREATE DATABASE db1; -CREATE DATABASE db2; -USE db2; -INSERT INTO db2.t1 VALUES (1); -SELECT * FROM db2.t1; -b -1 -RESET QUERY CACHE; -USE db1; SET SESSION keep_files_on_create = TRUE; CREATE TABLE t1 (a INT) ENGINE MYISAM; Got one of the listed errors -CREATE TABLE t3 (a INT) Engine=MyISAM; -INSERT INTO t3 VALUES (1),(2),(3); -TRUNCATE TABLE t3; -SELECT * from t3; -a -SET SESSION keep_files_on_create = DEFAULT; -DROP TABLE db2.t1, db1.t3; -DROP DATABASE db1; -DROP DATABASE db2; -USE test; +SET SESSION keep_files_on_create = FALSE; +CREATE TABLE t1 (a INT) ENGINE MYISAM; +DROP TABLE t1; End of 5.0 tests +CREATE TABLE t1(a INT) +INDEX DIRECTORY='TEST_DIR/master-data/mysql'; +ERROR HY000: Incorrect arguments to INDEX DIRECORY +CREATE TABLE t1(a INT) +DATA DIRECTORY='TEST_DIR/master-data/test'; +ERROR HY000: Incorrect arguments to DATA DIRECORY +CREATE TABLE t1(a INT) +DATA DIRECTORY='TEST_DIR/master-data/'; +DROP TABLE t1; +CREATE TABLE t1(a INT) +INDEX DIRECTORY='TEST_DIR/master-data'; +DROP TABLE t1; +CREATE TABLE t1(a INT) +INDEX DIRECTORY='TEST_DIR/master-data_var'; +ERROR HY000: Can't create/write to file 'TEST_DIR/master-data_var/t1.MYI' (Errcode: 2) SET @OLD_SQL_MODE=@@SQL_MODE, @@SQL_MODE='NO_DIR_IN_CREATE'; CREATE TABLE t1(a INT) DATA DIRECTORY='MYSQLTEST_VARDIR/tmp' INDEX DIRECTORY='MYSQLTEST_VARDIR/tmp'; Warnings: diff --git a/mysql-test/r/tablelock.result b/mysql-test/r/tablelock.result index 2ffd8f928a9..6923ad40916 100644 --- a/mysql-test/r/tablelock.result +++ b/mysql-test/r/tablelock.result @@ -46,3 +46,12 @@ CREATE TABLE t2 (a int); lock tables t1 write,t1 as b write, t2 write, t2 as c read; drop table t2,t1; unlock tables; +create temporary table t1(f1 int); +lock tables t1 write; +insert into t1 values (1); +show columns from t1; +Field Type Null Key Default Extra +f1 int(11) YES NULL +insert into t1 values(2); +drop table t1; +unlock tables; diff --git a/mysql-test/r/type_binary.result b/mysql-test/r/type_binary.result index 432c58272a2..00f4183aec3 100644 --- a/mysql-test/r/type_binary.result +++ b/mysql-test/r/type_binary.result @@ -125,6 +125,7 @@ create table t1 (c char(2), vc varchar(2)); insert into t1 values(0x4120, 0x4120); insert into t1 values(0x412020, 0x412020); Warnings: +Note 1265 Data truncated for column 'c' at row 1 Note 1265 Data truncated for column 'vc' at row 1 drop table t1; set @old_sql_mode= @@sql_mode, sql_mode= 'traditional'; diff --git a/mysql-test/r/type_set.result b/mysql-test/r/type_set.result index 877400ab7e1..659215eda33 100644 --- a/mysql-test/r/type_set.result +++ b/mysql-test/r/type_set.result @@ -85,3 +85,11 @@ t1 CREATE TABLE `t1` ( `f1` set('1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18','19','20','21','22','23','24','25','26','27','28','29','30','31','32','33','34','35','36','37','38','39','40','41','42','43','44','45','46','47','48','49','50','51','52','53','54','55','56','57','58','59','60','61','62','63','64','1') DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; +CREATE TABLE t1(c set('1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18','19','20','21','22','23','24','25','26','27','28','29','30','31','32','33','34','35','36','37','38','39','40','41','42','43','44','45','46','47','48','49','50','51','52','53','54','55','56','57','58','59','60','61','62','63','64')); +INSERT INTO t1 VALUES(7); +INSERT INTO t1 VALUES(9223372036854775808); +SELECT * FROM t1; +c +1,2,3 +64 +DROP TABLE t1; diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result index 0e89210cb60..fef88e3bd64 100644 --- a/mysql-test/r/view.result +++ b/mysql-test/r/view.result @@ -3613,6 +3613,30 @@ ERROR HY000: Field of view 'test.v1' underlying table doesn't have a default val set @@sql_mode=@old_mode; drop view v1; drop table t1; +create table t1 (a int, key(a)); +create table t2 (c int); +create view v1 as select a b from t1; +create view v2 as select 1 a from t2, v1 where c in +(select 1 from t1 where b = a); +insert into t1 values (1), (1); +insert into t2 values (1), (1); +prepare stmt from "select * from v2 where a = 1"; +execute stmt; +a +1 +1 +1 +1 +drop view v1, v2; +drop table t1, t2; +CREATE TABLE t1 (a INT); +CREATE VIEW v1 AS SELECT p.a AS a FROM t1 p, t1 q; +INSERT INTO t1 VALUES (1), (1); +SELECT MAX(a), COUNT(DISTINCT a) FROM v1 GROUP BY a; +MAX(a) COUNT(DISTINCT a) +1 1 +DROP VIEW v1; +DROP TABLE t1; # ----------------------------------------------------------------- # -- Bug#34337: Server crash when Altering a view using a table name. # ----------------------------------------------------------------- diff --git a/mysql-test/r/warnings.result b/mysql-test/r/warnings.result index 249cd583345..653309efa42 100644 --- a/mysql-test/r/warnings.result +++ b/mysql-test/r/warnings.result @@ -276,4 +276,42 @@ DROP TABLE t3; DROP PROCEDURE sp1; DROP PROCEDURE sp2; DROP PROCEDURE sp3; +create table t1 (c_char char(255), c_varchar varchar(255), c_tinytext tinytext); +create table t2 (c_tinyblob tinyblob); +set @c = repeat(' ', 256); +set @q = repeat('q', 256); +set sql_mode = ''; +insert into t1 values(@c, @c, @c); +Warnings: +Note 1265 Data truncated for column 'c_char' at row 1 +Note 1265 Data truncated for column 'c_varchar' at row 1 +Note 1265 Data truncated for column 'c_tinytext' at row 1 +insert into t2 values(@c); +Warnings: +Warning 1265 Data truncated for column 'c_tinyblob' at row 1 +insert into t1 values(@q, @q, @q); +Warnings: +Warning 1265 Data truncated for column 'c_char' at row 1 +Warning 1265 Data truncated for column 'c_varchar' at row 1 +Warning 1265 Data truncated for column 'c_tinytext' at row 1 +insert into t2 values(@q); +Warnings: +Warning 1265 Data truncated for column 'c_tinyblob' at row 1 +set sql_mode = 'traditional'; +insert into t1 values(@c, @c, @c); +Warnings: +Note 1265 Data truncated for column 'c_char' at row 1 +Note 1265 Data truncated for column 'c_varchar' at row 1 +Note 1265 Data truncated for column 'c_tinytext' at row 1 +insert into t2 values(@c); +ERROR 22001: Data too long for column 'c_tinyblob' at row 1 +insert into t1 values(@q, NULL, NULL); +ERROR 22001: Data too long for column 'c_char' at row 1 +insert into t1 values(NULL, @q, NULL); +ERROR 22001: Data too long for column 'c_varchar' at row 1 +insert into t1 values(NULL, NULL, @q); +ERROR 22001: Data too long for column 'c_tinytext' at row 1 +insert into t2 values(@q); +ERROR 22001: Data too long for column 'c_tinyblob' at row 1 +drop table t1, t2; End of 5.0 tests diff --git a/mysql-test/suite/parts/inc/partition_check_drop.inc b/mysql-test/suite/parts/inc/partition_check_drop.inc index 88ebdb7ad48..34a921374e6 100644 --- a/mysql-test/suite/parts/inc/partition_check_drop.inc +++ b/mysql-test/suite/parts/inc/partition_check_drop.inc @@ -27,8 +27,7 @@ if ($do_file_tests) --exec ls $MYSQLTEST_VARDIR/master-data/test/t1* > $MYSQLTEST_VARDIR/master-data/test/tmp2 || true if ($with_directories) { ---exec ls $MYSQLTEST_VARDIR/master-data/test/data/t1* >> $MYSQLTEST_VARDIR/master-data/test/tmp2 || true ---exec ls $MYSQLTEST_VARDIR/master-data/test/index/t1* >> $MYSQLTEST_VARDIR/master-data/test/tmp2 || true +--exec ls $MYSQLTEST_VARDIR/tmp/t1* >> $MYSQLTEST_VARDIR/master-data/test/tmp2 || true } eval SET @aux = CONCAT('load_file(''$MYSQLTEST_VARDIR','/master-data/test/tmp2'')'); let $file_list= `SELECT @aux`; @@ -65,8 +64,7 @@ if ($found_garbage) --exec rm -f $MYSQLTEST_VARDIR/master-data/test/t1* || true if ($with_directories) { - --exec rm -f $MYSQLTEST_VARDIR/master-data/test/data/t1* || true - --exec rm -f $MYSQLTEST_VARDIR/master-data/test/index/t1* || true + --exec rm -f $MYSQLTEST_VARDIR/tmp/t1* || true } } --enable_query_log diff --git a/mysql-test/suite/parts/inc/partition_layout_check1.inc b/mysql-test/suite/parts/inc/partition_layout_check1.inc index 9989f4ed9e3..6fa8df25496 100644 --- a/mysql-test/suite/parts/inc/partition_layout_check1.inc +++ b/mysql-test/suite/parts/inc/partition_layout_check1.inc @@ -38,8 +38,7 @@ if ($do_file_tests) --exec ls $MYSQLTEST_VARDIR/master-data/test/t1* > $MYSQLTEST_VARDIR/master-data/test/tmp2 2>&1 || true if ($with_directories) { ---exec ls $MYSQLTEST_VARDIR/master-data/test/data/t1* >> $MYSQLTEST_VARDIR/master-data/test/tmp2 2>&1 || true ---exec ls $MYSQLTEST_VARDIR/master-data/test/index/t1* >> $MYSQLTEST_VARDIR/master-data/test/tmp2 2>&1 || true +--exec ls $MYSQLTEST_VARDIR/tmp/t1* >> $MYSQLTEST_VARDIR/master-data/test/tmp2 2>&1 || true } eval SET @aux = CONCAT('load_file(''$MYSQLTEST_VARDIR','/master-data/test/tmp2'' )'); diff --git a/mysql-test/t/compare.test b/mysql-test/t/compare.test index 337035a8095..8863ed825c2 100644 --- a/mysql-test/t/compare.test +++ b/mysql-test/t/compare.test @@ -46,3 +46,34 @@ insert into t1 values (0x01,0x01); select * from t1 where a=b; select * from t1 where a=b and b=0x01; drop table if exists t1; + +# +# Bug #31887: DML Select statement not returning same results when executed +# in version 5 +# + +CREATE TABLE t1 (b int(2) zerofill, c int(2) zerofill); +INSERT INTO t1 (b,c) VALUES (1,2), (1,1), (2,2); + +SELECT CONCAT(b,c), CONCAT(b,c) = '0101' FROM t1; + +EXPLAIN EXTENDED SELECT b,c FROM t1 WHERE b = 1 AND CONCAT(b,c) = '0101'; +SELECT b,c FROM t1 WHERE b = 1 AND CONCAT(b,c) = '0101'; + +CREATE TABLE t2 (a int); +INSERT INTO t2 VALUES (1),(2); + +SELECT a, + (SELECT COUNT(*) FROM t1 + WHERE b = t2.a AND CONCAT(b,c) = CONCAT('0',t2.a,'01')) x +FROM t2 ORDER BY a; + +EXPLAIN EXTENDED +SELECT a, + (SELECT COUNT(*) FROM t1 + WHERE b = t2.a AND CONCAT(b,c) = CONCAT('0',t2.a,'01')) x +FROM t2 ORDER BY a; + +DROP TABLE t1,t2; + +--echo End of 5.0 tests diff --git a/mysql-test/t/create.test b/mysql-test/t/create.test index 8912fd46b42..28ae38d943a 100644 --- a/mysql-test/t/create.test +++ b/mysql-test/t/create.test @@ -1152,6 +1152,30 @@ create table t2 select sql_big_result f1,count(f2) from t1 group by f1; show status like 'handler_read%'; drop table t1,t2; +# +# Bug #25162: Backing up DB from 5.1 adds 'USING BTREE' to KEYs on table creates +# + +# Show that the old syntax for index type is supported +CREATE TABLE t1(c1 VARCHAR(33), KEY USING BTREE (c1)); +DROP TABLE t1; + +# Show that the new syntax for index type is supported +CREATE TABLE t1(c1 VARCHAR(33), KEY (c1) USING BTREE); +DROP TABLE t1; + +# Show that in case of multiple index type definitions, the last one takes +# precedence + +CREATE TABLE t1(c1 VARCHAR(33), KEY USING BTREE (c1) USING HASH) ENGINE=MEMORY; +SHOW INDEX FROM t1; +DROP TABLE t1; + +CREATE TABLE t1(c1 VARCHAR(33), KEY USING HASH (c1) USING BTREE) ENGINE=MEMORY; +SHOW INDEX FROM t1; +DROP TABLE t1; + + --echo End of 5.0 tests # diff --git a/mysql-test/t/disabled.def b/mysql-test/t/disabled.def index 8ec9cf36751..9e0b681528b 100644 --- a/mysql-test/t/disabled.def +++ b/mysql-test/t/disabled.def @@ -17,7 +17,6 @@ ctype_big5 : BUG#26711 2007-06-21 Lars Test has never worked on Do federated_transactions : Bug#29523 Transactions do not work lowercase_table3 : Bug#32667 lowercase_table3.test reports to error log innodb_mysql : Bug#32724: innodb_mysql.test fails randomly -wait_timeout : Bug#32801 wait_timeout.test fails randomly ctype_create : Bug#32965 main.ctype_create fails status : Bug#32966 main.status fails ps_ddl : Bug#12093 2007-12-14 pending WL#4165 / WL#4166 diff --git a/mysql-test/t/func_gconcat.test b/mysql-test/t/func_gconcat.test index 826e00bf74f..1026ca67a43 100644 --- a/mysql-test/t/func_gconcat.test +++ b/mysql-test/t/func_gconcat.test @@ -640,4 +640,21 @@ SELECT GROUP_CONCAT(DISTINCT b, a ORDER BY b) FROM t1; DROP TABLE t1, t2, t3; +# +# Bug #34747: crash in debug assertion check after derived table +# +CREATE TABLE t1(a INT); +INSERT INTO t1 VALUES (),(); +SELECT s1.d1 FROM +( + SELECT + t1.a as d1, + GROUP_CONCAT(DISTINCT t1.a) AS d2 + FROM + t1 AS t1, + t1 AS t2 + GROUP BY 1 +) AS s1; +DROP TABLE t1; + --echo End of 5.0 tests diff --git a/mysql-test/t/func_math.test b/mysql-test/t/func_math.test index 274a953a314..87b172a6436 100644 --- a/mysql-test/t/func_math.test +++ b/mysql-test/t/func_math.test @@ -248,5 +248,17 @@ INSERT INTO t1 VALUES ('a'); SELECT a DIV 2 FROM t1 UNION SELECT a DIV 2 FROM t1; DROP TABLE t1; - --echo End of 5.0 tests + +# +# Bug #31236: Inconsistent division by zero behavior for floating point numbers +# + +SELECT 1e308 + 1e308; +SELECT -1e308 - 1e308; +SELECT 1e300 * 1e300; +SELECT 1e300 / 1e-300; +SELECT EXP(750); +SELECT POW(10, 309); + +--echo End of 5.1 tests diff --git a/mysql-test/t/func_misc.test b/mysql-test/t/func_misc.test index 29f4f51cd0d..d33dd9b0d14 100644 --- a/mysql-test/t/func_misc.test +++ b/mysql-test/t/func_misc.test @@ -214,6 +214,24 @@ SELECT NAME_CONST('test', -1.0); SELECT NAME_CONST('test', 'test'); # +# Bug #34749: Server crash when using NAME_CONST() with an aggregate function +# + +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(2),(3); +# NAME_CONST() + aggregate. +SELECT NAME_CONST('flag',1) * MAX(a) FROM t1; +SELECT NAME_CONST('flag',1.5) * MAX(a) FROM t1; +# Now, wrap the INT_ITEM in Item_func_neg and watch the pretty explosions +SELECT NAME_CONST('flag',-1) * MAX(a) FROM t1; +SELECT NAME_CONST('flag',-1.5) * MAX(a) FROM t1; +--error ER_WRONG_ARGUMENTS +SELECT NAME_CONST('flag', SQRT(4)) * MAX(a) FROM t1; +--error ER_WRONG_ARGUMENTS +SELECT NAME_CONST('flag',-SQRT(4)) * MAX(a) FROM t1; +DROP TABLE t1; + +# # Bug #27545: erroneous usage of NAME_CONST with a name as the first parameter # resolved against a column name of a derived table hangs the client # diff --git a/mysql-test/t/func_str.test b/mysql-test/t/func_str.test index b6da14211ae..5a5f4024bc4 100644 --- a/mysql-test/t/func_str.test +++ b/mysql-test/t/func_str.test @@ -875,6 +875,13 @@ select benchmark(100, NULL); select benchmark(NULL, 1+1); # +# Bug #20752: BENCHMARK with many iterations returns too quickly +# + +# not a string, but belongs with the above Bug#22684 +select benchmark(-1, 1); + +# # Please note: # 1) The collation of the password is irrelevant, the encryption uses # the binary representation of the string without charset/collation. diff --git a/mysql-test/t/func_time.test b/mysql-test/t/func_time.test index 13c5da1285a..c417e9a02bf 100644 --- a/mysql-test/t/func_time.test +++ b/mysql-test/t/func_time.test @@ -336,7 +336,7 @@ select date_add(date,INTERVAL "1" QUARTER) from t1; select timestampadd(MINUTE, 1, date) from t1; select timestampadd(WEEK, 1, date) from t1; select timestampadd(SQL_TSI_SECOND, 1, date) from t1; -# Prepared statements doesn't support FRAC_SECOND yet +# mysqltest.c discards an expected 'deprecated' warning on prepare stage --disable_ps_protocol select timestampadd(SQL_TSI_FRAC_SECOND, 1, date) from t1; --enable_ps_protocol @@ -351,7 +351,10 @@ select timestampdiff(SQL_TSI_HOUR, '2001-02-01', '2001-05-01') as a; select timestampdiff(SQL_TSI_DAY, '2001-02-01', '2001-05-01') as a; select timestampdiff(SQL_TSI_MINUTE, '2001-02-01 12:59:59', '2001-05-01 12:58:59') as a; select timestampdiff(SQL_TSI_SECOND, '2001-02-01 12:59:59', '2001-05-01 12:58:58') as a; +# mysqltest.c discards an expected 'deprecated' warning on prepare stage +--disable_ps_protocol select timestampdiff(SQL_TSI_FRAC_SECOND, '2001-02-01 12:59:59.120000', '2001-05-01 12:58:58.119999') as a; +--enable_ps_protocol select timestampdiff(SQL_TSI_DAY, '1986-02-01', '1986-03-01') as a1, timestampdiff(SQL_TSI_DAY, '1900-02-01', '1900-03-01') as a2, @@ -783,6 +786,28 @@ select DATE_ADD(20071108, INTERVAL 1 DAY); select LAST_DAY('2007-12-06 08:59:19.05') - INTERVAL 1 SECOND; +# +# Bug#33834: FRAC_SECOND: Applicability not clear in documentation +# +# Show that he use of FRAC_SECOND, for anything other than +# TIMESTAMPADD / TIMESTAMPDIFF, is a server error. + +# mysqltest.c discards an expected 'deprecated' warning on prepare stage +--disable_ps_protocol +SELECT TIMESTAMPADD(FRAC_SECOND, 1, '2008-02-18'); +SELECT TIMESTAMPDIFF(FRAC_SECOND, '2008-02-17', '2008-02-18'); +--enable_ps_protocol + +--error ER_PARSE_ERROR +SELECT DATE_ADD('2008-02-18', INTERVAL 1 FRAC_SECOND); +--error ER_PARSE_ERROR +SELECT DATE_SUB('2008-02-18', INTERVAL 1 FRAC_SECOND); + +--error ER_PARSE_ERROR +SELECT '2008-02-18' + INTERVAL 1 FRAC_SECOND; +--error ER_PARSE_ERROR +SELECT '2008-02-18' - INTERVAL 1 FRAC_SECOND; + --echo End of 5.0 tests # diff --git a/mysql-test/t/grant3.test b/mysql-test/t/grant3.test index fac577ef0ff..8eceb851c29 100644 --- a/mysql-test/t/grant3.test +++ b/mysql-test/t/grant3.test @@ -134,3 +134,29 @@ SELECT user, host, db, select_priv FROM mysql.db where user = 'CUser2' order by DROP USER CUser2@localhost; DROP USER CUser2@LOCALHOST; + + +# +# Bug#31194: Privilege ordering does not order properly for wildcard values +# + +CREATE DATABASE mysqltest_1; +CREATE TABLE mysqltest_1.t1 (a INT); +CREATE USER 'mysqltest1'@'%'; +GRANT SELECT, UPDATE ON `mysqltest_1`.* TO 'mysqltest1'@'%'; +REVOKE SELECT ON `mysqltest_1`.* FROM 'mysqltest1'@'%'; +GRANT SELECT, UPDATE ON `mysqltest\_1`.* TO 'mysqltest1'@'%'; +FLUSH PRIVILEGES; + +connect (conn1,localhost,mysqltest1,,); +connection conn1; +SHOW GRANTS; +SELECT * FROM mysqltest_1.t1; +disconnect conn1; + +connection default; +DROP USER 'mysqltest1'@'%'; +DROP DATABASE mysqltest_1; + + +--echo End of 5.0 tests diff --git a/mysql-test/t/mysqldump-no-binlog-master.opt b/mysql-test/t/mysqldump-no-binlog-master.opt new file mode 100644 index 00000000000..789275fa25e --- /dev/null +++ b/mysql-test/t/mysqldump-no-binlog-master.opt @@ -0,0 +1 @@ +--skip-log-bin diff --git a/mysql-test/t/mysqldump-no-binlog.test b/mysql-test/t/mysqldump-no-binlog.test new file mode 100644 index 00000000000..434b2931792 --- /dev/null +++ b/mysql-test/t/mysqldump-no-binlog.test @@ -0,0 +1,6 @@ +# Embedded server doesn't support external clients +--source include/not_embedded.inc + +--replace_regex /MASTER_LOG_POS=[0-9]+/XX/ +--error 2 +--exec $MYSQL_DUMP --compact --master-data=2 test 2>&1 diff --git a/mysql-test/t/null_key.test b/mysql-test/t/null_key.test index e15aec01d2a..1400c643203 100644 --- a/mysql-test/t/null_key.test +++ b/mysql-test/t/null_key.test @@ -240,3 +240,26 @@ SHOW STATUS LIKE "handler_read%"; DROP TABLE t1,t2,t3,t4; # End of 4.1 tests + +# +# BUG#34945 "ref_or_null queries that are null_rejecting and have a null value crash mysql" +# +CREATE TABLE t1 ( + a int(11) default NULL, + b int(11) default NULL, + KEY a (a,b) +); +INSERT INTO t1 VALUES (0,10),(0,11),(0,12); + +CREATE TABLE t2 ( + a int(11) default NULL, + b int(11) default NULL, + KEY a (a) +); +INSERT INTO t2 VALUES (3,NULL),(3,11),(3,12); + +SELECT * FROM t2 inner join t1 WHERE ( t1.a = 0 OR t1.a IS NULL) AND t2.a = 3 AND t2.b = t1.b; + +drop table t1, t2; +-- echo End of 5.0 tests + diff --git a/mysql-test/t/order_by.test b/mysql-test/t/order_by.test index 71238504d36..898742c2682 100644 --- a/mysql-test/t/order_by.test +++ b/mysql-test/t/order_by.test @@ -836,3 +836,14 @@ eval set @tmp_tables_after = SELECT @tmp_tables_after = @tmp_tables_before; DROP TABLE t1; +--echo # +--echo # Bug#31590: Wrong error message on sort buffer being too small. +--echo # +create table t1(a int, b tinytext); +insert into t1 values (1,2),(3,2); +set session sort_buffer_size= 30000; +set session max_sort_length= 2180; +--error 1038 +select * from t1 order by b; +drop table t1; + diff --git a/mysql-test/t/partition_not_windows.test b/mysql-test/t/partition_not_windows.test index 012b8b50dee..103fb05029e 100644 --- a/mysql-test/t/partition_not_windows.test +++ b/mysql-test/t/partition_not_windows.test @@ -12,12 +12,10 @@ # doesn't remove old directory --disable_query_log ---exec mkdir $MYSQLTEST_VARDIR/master-data/tmpdata || true -eval SET @data_dir = 'DATA DIRECTORY = ''$MYSQLTEST_VARDIR/master-data/tmpdata'''; +eval SET @data_dir = 'DATA DIRECTORY = ''$MYSQLTEST_VARDIR/tmp'''; let $data_directory = `select @data_dir`; ---exec mkdir $MYSQLTEST_VARDIR/master-data/tmpinx || true -eval SET @inx_dir = 'INDEX DIRECTORY = ''$MYSQLTEST_VARDIR/master-data/tmpinx'''; +eval SET @inx_dir = 'INDEX DIRECTORY = ''$MYSQLTEST_VARDIR/tmp'''; let $inx_directory = `select @inx_dir`; --enable_query_log @@ -35,10 +33,10 @@ subpartition by hash (a) --file_exists $MYSQLTEST_VARDIR/master-data/test/t1#P#p0#SP#subpart00.MYI --file_exists $MYSQLTEST_VARDIR/master-data/test/t1#P#p0#SP#subpart01.MYD --file_exists $MYSQLTEST_VARDIR/master-data/test/t1#P#p0#SP#subpart01.MYI ---file_exists $MYSQLTEST_VARDIR/master-data/tmpdata/t1#P#p0#SP#subpart00.MYD ---file_exists $MYSQLTEST_VARDIR/master-data/tmpdata/t1#P#p0#SP#subpart01.MYD ---file_exists $MYSQLTEST_VARDIR/master-data/tmpinx/t1#P#p0#SP#subpart00.MYI ---file_exists $MYSQLTEST_VARDIR/master-data/tmpinx/t1#P#p0#SP#subpart01.MYI +--file_exists $MYSQLTEST_VARDIR/tmp/t1#P#p0#SP#subpart00.MYD +--file_exists $MYSQLTEST_VARDIR/tmp/t1#P#p0#SP#subpart01.MYD +--file_exists $MYSQLTEST_VARDIR/tmp/t1#P#p0#SP#subpart00.MYI +--file_exists $MYSQLTEST_VARDIR/tmp/t1#P#p0#SP#subpart01.MYI --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR eval ALTER TABLE t1 REORGANIZE PARTITION p0 INTO @@ -58,18 +56,81 @@ eval ALTER TABLE t1 REORGANIZE PARTITION p0 INTO --file_exists $MYSQLTEST_VARDIR/master-data/test/t1#P#p2#SP#subpart20.MYI --file_exists $MYSQLTEST_VARDIR/master-data/test/t1#P#p2#SP#subpart21.MYD --file_exists $MYSQLTEST_VARDIR/master-data/test/t1#P#p2#SP#subpart21.MYI ---file_exists $MYSQLTEST_VARDIR/master-data/tmpdata/t1#P#p1#SP#subpart10.MYD ---file_exists $MYSQLTEST_VARDIR/master-data/tmpdata/t1#P#p1#SP#subpart11.MYD ---file_exists $MYSQLTEST_VARDIR/master-data/tmpdata/t1#P#p2#SP#subpart20.MYD ---file_exists $MYSQLTEST_VARDIR/master-data/tmpdata/t1#P#p2#SP#subpart21.MYD ---file_exists $MYSQLTEST_VARDIR/master-data/tmpinx/t1#P#p1#SP#subpart10.MYI ---file_exists $MYSQLTEST_VARDIR/master-data/tmpinx/t1#P#p1#SP#subpart11.MYI ---file_exists $MYSQLTEST_VARDIR/master-data/tmpinx/t1#P#p2#SP#subpart20.MYI ---file_exists $MYSQLTEST_VARDIR/master-data/tmpinx/t1#P#p2#SP#subpart21.MYI +--file_exists $MYSQLTEST_VARDIR/tmp/t1#P#p1#SP#subpart10.MYD +--file_exists $MYSQLTEST_VARDIR/tmp/t1#P#p1#SP#subpart11.MYD +--file_exists $MYSQLTEST_VARDIR/tmp/t1#P#p2#SP#subpart20.MYD +--file_exists $MYSQLTEST_VARDIR/tmp/t1#P#p2#SP#subpart21.MYD +--file_exists $MYSQLTEST_VARDIR/tmp/t1#P#p1#SP#subpart10.MYI +--file_exists $MYSQLTEST_VARDIR/tmp/t1#P#p1#SP#subpart11.MYI +--file_exists $MYSQLTEST_VARDIR/tmp/t1#P#p2#SP#subpart20.MYI +--file_exists $MYSQLTEST_VARDIR/tmp/t1#P#p2#SP#subpart21.MYI drop table t1; ---exec rmdir $MYSQLTEST_VARDIR/master-data/tmpdata || true ---exec rmdir $MYSQLTEST_VARDIR/master-data/tmpinx || true + +# +# Bug #24633 SQL MODE "NO_DIR_IN_CREATE" does not work with partitioned tables +# +disable_query_log; +eval create table t2 (i int ) +partition by range (i) +( + partition p01 values less than (1000) + data directory="$MYSQLTEST_VARDIR/tmp/" + index directory="$MYSQLTEST_VARDIR/tmp/" +); +enable_query_log; + +set @org_mode=@@sql_mode; +set @@sql_mode='NO_DIR_IN_CREATE'; +select @@sql_mode; +create table t1 (i int ) +partition by range (i) +( + partition p01 values less than (1000) + data directory='/not/existing' + index directory='/not/existing' +); +show create table t2; +DROP TABLE t1, t2; +set @@sql_mode=@org_mode; + +# +# Bug#32167: another privilege bypass with DATA/INDEX DIRECTORY +# +--replace_result $MYSQLTEST_VARDIR TEST_DIR +--error 1210 +eval CREATE TABLE t1(a INT) +PARTITION BY KEY (a) +(PARTITION p0 DATA DIRECTORY '$MYSQLTEST_VARDIR/master-data/test'); +--replace_result $MYSQLTEST_VARDIR TEST_DIR +--error 1210 +eval CREATE TABLE t1(a INT) +PARTITION BY KEY (a) +(PARTITION p0 INDEX DIRECTORY '$MYSQLTEST_VARDIR/master-data/test'); + +--replace_result $MYSQLTEST_VARDIR TEST_DIR +--error 1210 +eval CREATE TABLE ts (id INT, purchased DATE) +PARTITION BY RANGE(YEAR(purchased)) +SUBPARTITION BY HASH(TO_DAYS(purchased)) ( +PARTITION p0 VALUES LESS THAN (1990) ( +SUBPARTITION s0a + DATA DIRECTORY = '$MYSQLTEST_VARDIR/master-data/test', +SUBPARTITION s0b + DATA DIRECTORY = '$MYSQLTEST_VARDIR/master-data/test' +)); + +--replace_result $MYSQLTEST_VARDIR TEST_DIR +--error 1210 +eval CREATE TABLE ts (id INT, purchased DATE) +PARTITION BY RANGE(YEAR(purchased)) +SUBPARTITION BY HASH(TO_DAYS(purchased)) ( +PARTITION p0 VALUES LESS THAN (1990) ( +SUBPARTITION s0a + INDEX DIRECTORY = '$MYSQLTEST_VARDIR/master-data/test', +SUBPARTITION s0b + INDEX DIRECTORY = '$MYSQLTEST_VARDIR/master-data/test' +)); # End Windows specific test failures. diff --git a/mysql-test/t/partition_symlink.test b/mysql-test/t/partition_symlink.test index ec809a34090..cf8ba2d2a5f 100644 --- a/mysql-test/t/partition_symlink.test +++ b/mysql-test/t/partition_symlink.test @@ -26,6 +26,10 @@ DROP DATABASE IF EXISTS mysqltest2; # files, but not the other way around (any db-user can use any # directory or file that the mysqld-process can use, via DATA/INDEX DIR) # this is the security flaw that was used in bug#32091 and bug#32111 + +#--exec mkdir $MYSQLTEST_VARDIR/tmp/test || true +#--exec mkdir $MYSQLTEST_VARDIR/tmp/mysqltest2 || true + -- echo # Creating two non colliding tables mysqltest2.t1 and test.t1 -- echo # test.t1 have partitions in mysqltest2-directory! -- echo # user root: @@ -41,11 +45,11 @@ connect(con1,localhost,mysqltest_1,,); eval CREATE TABLE t1 (a INT) PARTITION BY LIST (a) ( PARTITION p0 VALUES IN (0) - DATA DIRECTORY '$MYSQLTEST_VARDIR/master-data/mysqltest2' - INDEX DIRECTORY '$MYSQLTEST_VARDIR/master-data/mysqltest2', + DATA DIRECTORY '$MYSQLTEST_VARDIR/tmp' + INDEX DIRECTORY '$MYSQLTEST_VARDIR/tmp', PARTITION p1 VALUES IN (1) - DATA DIRECTORY '$MYSQLTEST_VARDIR/master-data/test' - INDEX DIRECTORY '$MYSQLTEST_VARDIR/master-data/test', + DATA DIRECTORY '$MYSQLTEST_VARDIR/tmp' + INDEX DIRECTORY '$MYSQLTEST_VARDIR/tmp', PARTITION p2 VALUES IN (2) ); -- echo # without the patch for bug#32091 this would create @@ -76,17 +80,18 @@ connection default; # So it is using/blocking 2 files in (in 2 different directories -- echo # test that symlinks can not overwrite files when CREATE TABLE -- echo # user root: + CREATE DATABASE mysqltest2; USE mysqltest2; -- replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR eval CREATE TABLE t1 (a INT) PARTITION BY LIST (a) ( PARTITION p0 VALUES IN (0) - DATA DIRECTORY '$MYSQLTEST_VARDIR/master-data/mysqltest2' - INDEX DIRECTORY '$MYSQLTEST_VARDIR/master-data/mysqltest2', + DATA DIRECTORY '$MYSQLTEST_VARDIR/tmp' + INDEX DIRECTORY '$MYSQLTEST_VARDIR/tmp', PARTITION p1 VALUES IN (1) - DATA DIRECTORY '$MYSQLTEST_VARDIR/master-data/test' - INDEX DIRECTORY '$MYSQLTEST_VARDIR/master-data/test' + DATA DIRECTORY '$MYSQLTEST_VARDIR/tmp' + INDEX DIRECTORY '$MYSQLTEST_VARDIR/tmp' ); connection con1; -- echo # user mysqltest_1: @@ -96,22 +101,22 @@ connection con1; eval CREATE TABLE t1 (a INT) PARTITION BY LIST (a) ( PARTITION p0 VALUES IN (0) - DATA DIRECTORY '$MYSQLTEST_VARDIR/master-data/mysqltest2' - INDEX DIRECTORY '$MYSQLTEST_VARDIR/master-data/mysqltest2', + DATA DIRECTORY '$MYSQLTEST_VARDIR/tmp' + INDEX DIRECTORY '$MYSQLTEST_VARDIR/tmp', PARTITION p1 VALUES IN (1) - DATA DIRECTORY '$MYSQLTEST_VARDIR/master-data/test' - INDEX DIRECTORY '$MYSQLTEST_VARDIR/master-data/test' + DATA DIRECTORY '$MYSQLTEST_VARDIR/tmp' + INDEX DIRECTORY '$MYSQLTEST_VARDIR/tmp' ); -- replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR -- error 1,1 eval CREATE TABLE t1 (a INT) PARTITION BY LIST (a) ( PARTITION p0 VALUES IN (0) - DATA DIRECTORY '$MYSQLTEST_VARDIR/master-data/test' - INDEX DIRECTORY '$MYSQLTEST_VARDIR/master-data/test', + DATA DIRECTORY '$MYSQLTEST_VARDIR/tmp' + INDEX DIRECTORY '$MYSQLTEST_VARDIR/tmp', PARTITION p1 VALUES IN (1) - DATA DIRECTORY '$MYSQLTEST_VARDIR/master-data/mysqltest2' - INDEX DIRECTORY '$MYSQLTEST_VARDIR/master-data/mysqltest2' + DATA DIRECTORY '$MYSQLTEST_VARDIR/tmp' + INDEX DIRECTORY '$MYSQLTEST_VARDIR/tmp' ); connection default; -- echo # user root (cleanup): @@ -167,3 +172,5 @@ partition by key (a) (partition p0, partition p1 DATA DIRECTORY 'part-data' INDEX DIRECTORY 'part-data'); +#--exec rmdir $MYSQLTEST_VARDIR/tmp/test || true +#--exec rmdir $MYSQLTEST_VARDIR/tmp/mysqltest2 || true diff --git a/mysql-test/t/plugin.test b/mysql-test/t/plugin.test index d8d6d069676..0635a58a4a6 100644 --- a/mysql-test/t/plugin.test +++ b/mysql-test/t/plugin.test @@ -39,3 +39,38 @@ SET GLOBAL example_enum_var= e2; SET GLOBAL example_enum_var= impossible; UNINSTALL PLUGIN example; + + + +# +# Bug #32757 hang with sql_mode set when setting some global variables +# +INSTALL PLUGIN example SONAME 'ha_example.so'; + +select @@session.sql_mode into @old_sql_mode; + +# first, try normal sql_mode (no error, send OK) +set session sql_mode=''; +set global example_ulong_var=500; +select @@global.example_ulong_var; +# overflow -- correct value, but throw warning +set global example_ulong_var=1111; +select @@global.example_ulong_var; + +# now, try STRICT (error occurrs, no message is sent, so send default) +set session sql_mode='STRICT_ALL_TABLES'; +set global example_ulong_var=500; +select @@global.example_ulong_var; +# overflow -- throw warning, do NOT change value +--error ER_WRONG_VALUE_FOR_VAR +set global example_ulong_var=1111; +select @@global.example_ulong_var; + +set session sql_mode=@old_sql_mode; + +# finally, show that conditions that already raised an error are not +# adversely affected (error was already sent, do nothing) +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +set session old=bla; + +UNINSTALL PLUGIN example; diff --git a/mysql-test/t/range.test b/mysql-test/t/range.test index e20f0370e2c..514e3074292 100644 --- a/mysql-test/t/range.test +++ b/mysql-test/t/range.test @@ -955,6 +955,23 @@ explain select * from t1 where dateval >= '2007-01-01 00:00:00' and dateval <= ' drop table t1; +# +# Bug #33833: different or-ed predicates were erroneously merged into one that +# resulted in ref access instead of range access and a wrong result set +# + +CREATE TABLE t1 ( + a varchar(32), index (a) +) DEFAULT CHARSET=latin1 COLLATE=latin1_bin; + +INSERT INTO t1 VALUES + ('B'), ('A'), ('A'), ('C'), ('B'), ('A'), ('A'); + +SELECT a FROM t1 WHERE a='b' OR a='B'; +EXPLAIN SELECT a FROM t1 WHERE a='b' OR a='B'; + +DROP TABLE t1; + # End of 5.0 tests # BUG#22393 fix: Adjust 'ref' estimate if we have 'range' estimate for diff --git a/mysql-test/t/row.test b/mysql-test/t/row.test index 20d044306a6..1601f7afd0e 100644 --- a/mysql-test/t/row.test +++ b/mysql-test/t/row.test @@ -224,3 +224,16 @@ 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; + +# +# Bug #34620: item_row.cc:50: Item_row::illegal_method_call(const char*): +# Assertion `0' failed +# + +CREATE TABLE t1 (a INT, b INT); +INSERT INTO t1 VALUES (1,1); + +SELECT ROW(a, 1) IN (SELECT SUM(b), 1) FROM t1 GROUP BY a; +SELECT ROW(a, 1) IN (SELECT SUM(b), 3) FROM t1 GROUP BY a; + +DROP TABLE t1; diff --git a/mysql-test/t/select.test b/mysql-test/t/select.test index bc0e4ae307f..30abb797e83 100644 --- a/mysql-test/t/select.test +++ b/mysql-test/t/select.test @@ -3661,6 +3661,35 @@ DROP TABLE t1; --echo +########################################################################### + +# +# Bug #33764: Wrong result with IN(), CONCAT() and implicit type conversion +# + +CREATE TABLE t1 (a VARCHAR(10) NOT NULL PRIMARY KEY); +INSERT INTO t1 (a) VALUES ('foo0'), ('bar0'), ('baz0'); +SELECT * FROM t1 WHERE a IN (CONCAT('foo', 0), 'bar'); +DROP TABLE t1; + +# +# Bug #32942 now() - interval '7200' second is NOT pre-calculated, causing "full table scan" +# + +CREATE TABLE t1 (a INT, b INT); +CREATE TABLE t2 (a INT, c INT, KEY(a)); + +INSERT INTO t1 VALUES (1, 1), (2, 2); +INSERT INTO t2 VALUES (1, 1), (1, 2), (1, 3), (1, 4), (1, 5), + (2, 1), (2, 2), (2, 3), (2, 4), (2, 5), + (3, 1), (3, 2), (3, 3), (3, 4), (3, 5), + (4, 1), (4, 2), (4, 3), (4, 4), (4, 5); + +FLUSH STATUS; +SELECT DISTINCT b FROM t1 LEFT JOIN t2 USING(a) WHERE c <= 3; +SHOW STATUS LIKE 'Handler_read%'; +DROP TABLE t1, t2; + --echo End of 5.0 tests # diff --git a/mysql-test/t/sp.test b/mysql-test/t/sp.test index bb9aae6dee6..b9056d86d7e 100644 --- a/mysql-test/t/sp.test +++ b/mysql-test/t/sp.test @@ -7977,6 +7977,25 @@ call proc_33618(20); drop table t_33618; drop procedure proc_33618; +--echo # +--echo # Bug#30787: Stored function ignores user defined alias. +--echo # +use test; +--disable_warnings +drop function if exists func30787; +--enable_warnings +create table t1(f1 int); +insert into t1 values(1),(2); +delimiter |; +create function func30787(p1 int) returns int +begin + return p1; +end | +delimiter ;| +select (select func30787(f1)) as ttt from t1; +drop function func30787; +drop table t1; + ########################################################################### --echo # ------------------------------------------------------------------ diff --git a/mysql-test/t/sql_mode.test b/mysql-test/t/sql_mode.test index 7a9036c8621..acc9cc7979e 100644 --- a/mysql-test/t/sql_mode.test +++ b/mysql-test/t/sql_mode.test @@ -279,3 +279,32 @@ select concat('x',b,'x') from t1; drop table t1; SET @@SQL_MODE=@OLD_SQL_MODE; + + +# +# Bug #32753: PAD_CHAR_TO_FULL_LENGTH is not documented and interferes +# with grant tables +# + +create user mysqltest_32753@localhost; + +# try to make the user-table space-padded +--connection default +set @OLD_SQL_MODE=@@SESSION.SQL_MODE; +set session sql_mode='PAD_CHAR_TO_FULL_LENGTH'; +flush privileges; + +# if user-table is affected by PAD_CHAR_TO_FULL_LENGTH, our connect will fail +# --error 1045 +connect (user_32753,localhost,mysqltest_32753,,test,$MASTER_MYPORT,$MASTER_MYSOCK); +select current_user(); + +# clean up +--connection default +set session sql_mode=@OLD_SQL_MODE; +flush privileges; + +--disconnect user_32753 + +--connection default +drop user mysqltest_32753@localhost; diff --git a/mysql-test/t/strict.test b/mysql-test/t/strict.test index 2b71bf1093c..486f7ce7897 100644 --- a/mysql-test/t/strict.test +++ b/mysql-test/t/strict.test @@ -822,7 +822,6 @@ INSERT INTO t1 (col2) VALUES (-1.1E-3); INSERT INTO t1 (col1) VALUES ('+1.8E+309'); --error 1264 INSERT INTO t1 (col2) VALUES ('-1.2E-3'); ---error 1264 UPDATE t1 SET col1 =col1 * 5000 WHERE col1 > 0; --error 1365 UPDATE t1 SET col2 =col2 / 0 WHERE col2 > 0; diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index c86b0fefbdc..fb3e59cc9d6 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -3016,6 +3016,52 @@ SELECT ((a1,a2) IN (SELECT * FROM t2 WHERE b2 > 0)) IS NULL FROM t1; DROP TABLE t1, t2; # +# Bug31048: Many nested subqueries may cause server crash. +# +create table t1(a int,b int,key(a),key(b)); +insert into t1(a,b) values (1,2),(2,1),(2,3),(3,4),(5,4),(5,5), + (6,7),(7,4),(5,3); + +let $nesting= 26; +let $should_work_nesting= 5; +let $start= select sum(a),a from t1 where a> ( select sum(a) from t1 ; +let $end= )group by a ; +let $start_app= where a> ( select sum(a) from t1 ; +let $end_pre= )group by b limit 1 ; + +--disable_result_log +--disable_query_log +# At least 4 level nesting should work without errors +while ($should_work_nesting) +{ +--echo $should_work_nesting + eval $start $end; + eval explain $start $end; + let $start= $start + $start_app; + let $end= $end_pre + $end; + dec $should_work_nesting; +} +# Other may fail with the 'stack overrun error' +while ($nesting) +{ +--echo $nesting +--error 0,1436 + eval $start $end; +--error 0,1436 + eval explain $start $end; + let $start= $start + $start_app; + let $end= $end_pre + $end; + dec $nesting; +} +--enable_result_log +--enable_query_log +drop table t1; + +# # Bug #28076: inconsistent binary/varbinary comparison # diff --git a/mysql-test/t/symlink.test b/mysql-test/t/symlink.test index eaad45d7d5d..5802e65df1e 100644 --- a/mysql-test/t/symlink.test +++ b/mysql-test/t/symlink.test @@ -123,29 +123,22 @@ drop table t1; # # BUG#32111 - Security Breach via DATA/INDEX DIRECORY and RENAME TABLE # +--write_file $MYSQLTEST_VARDIR/tmp/t1.MYI +EOF --replace_result $MYSQLTEST_VARDIR TEST_DIR +--error 1 eval CREATE TABLE t1(a INT) -DATA DIRECTORY='$MYSQLTEST_VARDIR/master-data/mysql' -INDEX DIRECTORY='$MYSQLTEST_VARDIR/master-data/mysql'; +DATA DIRECTORY='$MYSQLTEST_VARDIR/tmp' +INDEX DIRECTORY='$MYSQLTEST_VARDIR/tmp'; +--replace_result $MYSQLTEST_VARDIR TEST_DIR +eval CREATE TABLE t2(a INT) +DATA DIRECTORY='$MYSQLTEST_VARDIR/tmp' +INDEX DIRECTORY='$MYSQLTEST_VARDIR/tmp'; --replace_result $MYSQLTEST_VARDIR TEST_DIR --error 1 -RENAME TABLE t1 TO user; -DROP TABLE t1; - -# -# Test specifying DATA DIRECTORY that is the same as what would normally -# have been chosen. (Bug #8707) -# -disable_query_log; -eval create table t1 (i int) data directory = "$MYSQLTEST_VARDIR/master-data/test/"; -enable_query_log; -show create table t1; -drop table t1; -disable_query_log; -eval create table t1 (i int) index directory = "$MYSQLTEST_VARDIR/master-data/test/"; -enable_query_log; -show create table t1; -drop table t1; +RENAME TABLE t2 TO t1; +DROP TABLE t2; +--remove_file $MYSQLTEST_VARDIR/tmp/t1.MYI # # Bug#8706 - temporary table with data directory option fails @@ -189,45 +182,44 @@ drop table t1; # # Bug #29325: create table overwrites .MYD file of other table (datadir) # - -CREATE DATABASE db1; -CREATE DATABASE db2; - -USE db2; ---disable_query_log -eval CREATE TABLE t1 (b INT) ENGINE MYISAM -DATA DIRECTORY = '$MYSQLTEST_VARDIR/master-data/db1/'; ---enable_query_log - -INSERT INTO db2.t1 VALUES (1); -SELECT * FROM db2.t1; -RESET QUERY CACHE; - -USE db1; - -#no warning from create table SET SESSION keep_files_on_create = TRUE; +--write_file $MYSQLTEST_VARDIR/master-data/test/t1.MYD +EOF --disable_abort_on_error ---error 1,1 +--error 1,1,ER_TABLE_EXISTS_ERROR CREATE TABLE t1 (a INT) ENGINE MYISAM; +--error 0,1 +--remove_file $MYSQLTEST_VARDIR/master-data/test/t1.MYD; --enable_abort_on_error - -CREATE TABLE t3 (a INT) Engine=MyISAM; -INSERT INTO t3 VALUES (1),(2),(3); -TRUNCATE TABLE t3; -SELECT * from t3; - -SET SESSION keep_files_on_create = DEFAULT; - -DROP TABLE db2.t1, db1.t3; -DROP DATABASE db1; -DROP DATABASE db2; -USE test; - +SET SESSION keep_files_on_create = FALSE; +CREATE TABLE t1 (a INT) ENGINE MYISAM; +DROP TABLE t1; --echo End of 5.0 tests # +# Bug#32167: another privilege bypass with DATA/INDEX DIRECTORY +# +--replace_result $MYSQLTEST_VARDIR TEST_DIR +--error 1210 +eval CREATE TABLE t1(a INT) +INDEX DIRECTORY='$MYSQLTEST_VARDIR/master-data/mysql'; +--replace_result $MYSQLTEST_VARDIR TEST_DIR +--error 1210 +eval CREATE TABLE t1(a INT) +DATA DIRECTORY='$MYSQLTEST_VARDIR/master-data/test'; +--replace_result $MYSQLTEST_VARDIR TEST_DIR +eval CREATE TABLE t1(a INT) +DATA DIRECTORY='$MYSQLTEST_VARDIR/master-data/'; +DROP TABLE t1; +--replace_result $MYSQLTEST_VARDIR TEST_DIR +eval CREATE TABLE t1(a INT) +INDEX DIRECTORY='$MYSQLTEST_VARDIR/master-data'; +DROP TABLE t1; +--replace_result $MYSQLTEST_VARDIR TEST_DIR +--error 1 +eval CREATE TABLE t1(a INT) +INDEX DIRECTORY='$MYSQLTEST_VARDIR/master-data_var'; # BUG#25677 - With --skip-symbolic-links option on, DATA DIRECTORY clause is # silently ignored # diff --git a/mysql-test/t/tablelock.test b/mysql-test/t/tablelock.test index 95533903b45..5ac93f09ac1 100644 --- a/mysql-test/t/tablelock.test +++ b/mysql-test/t/tablelock.test @@ -49,3 +49,16 @@ drop table t2,t1; unlock tables; # End of 4.1 tests + +# +# Bug#23588 SHOW COLUMNS on a temporary table causes locking issues +# +create temporary table t1(f1 int); +lock tables t1 write; +insert into t1 values (1); +show columns from t1; +insert into t1 values(2); +drop table t1; +unlock tables; + +# End of 5.0 tests diff --git a/mysql-test/t/type_set.test b/mysql-test/t/type_set.test index b1c816f3371..c7f8c59de28 100644 --- a/mysql-test/t/type_set.test +++ b/mysql-test/t/type_set.test @@ -56,3 +56,23 @@ set('1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17', '50','51','52','53','54','55','56','57','58','59','60','61','62','63','64','1')); show create table t1; drop table t1; + +# +# Bug#15409: Columns with SET datatype with 64-element sets +# may not be updated with integers +# + +let $i=64; +let $s='$i'; +dec $i; +while ($i) { + let $s='$i',$s; + dec $i; +} +--eval CREATE TABLE t1(c set($s)) +INSERT INTO t1 VALUES(7); +INSERT INTO t1 VALUES(9223372036854775808); +SELECT * FROM t1; +DROP TABLE t1; + +--# echo End of 5.0 tests diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test index f337bcdaf89..43f7d4a6f68 100644 --- a/mysql-test/t/view.test +++ b/mysql-test/t/view.test @@ -3384,7 +3384,6 @@ SELECT profile.person_id AS person_id INSERT INTO t1 VALUES (6,'Sw','A'), (-1136332546,'ols','e'), (0,' *\n','0'), (-717462680,'ENTS Ta','0'), (-904346964,'ndard SQL\n','0'); - INSERT INTO t2 VALUES (1,3,6),(2,4,7),(3,5,8),(4,6,9),(5,1,6),(6,1,7),(7,1,8),(8,1,9),(9,1,10); @@ -3468,6 +3467,40 @@ set @@sql_mode=@old_mode; drop view v1; drop table t1; +# +# Bug #33389: Selecting from a view into a table from within SP or trigger +# crashes server +# + +create table t1 (a int, key(a)); +create table t2 (c int); + +create view v1 as select a b from t1; +create view v2 as select 1 a from t2, v1 where c in + (select 1 from t1 where b = a); + +insert into t1 values (1), (1); +insert into t2 values (1), (1); + +prepare stmt from "select * from v2 where a = 1"; +execute stmt; + +drop view v1, v2; +drop table t1, t2; + +# +# Bug #33049: Assert while running test-as3ap test(mysql-bench suite) +# + +CREATE TABLE t1 (a INT); +CREATE VIEW v1 AS SELECT p.a AS a FROM t1 p, t1 q; + +INSERT INTO t1 VALUES (1), (1); +SELECT MAX(a), COUNT(DISTINCT a) FROM v1 GROUP BY a; + +DROP VIEW v1; +DROP TABLE t1; + ########################################################################### --echo # ----------------------------------------------------------------- diff --git a/mysql-test/t/warnings.test b/mysql-test/t/warnings.test index d0eaaf1a764..9e7e3e4f945 100644 --- a/mysql-test/t/warnings.test +++ b/mysql-test/t/warnings.test @@ -193,4 +193,37 @@ DROP PROCEDURE sp1; DROP PROCEDURE sp2; DROP PROCEDURE sp3; + +# +# Bug#30059: End-space truncation warnings are inconsistent or incorrect +# + +create table t1 (c_char char(255), c_varchar varchar(255), c_tinytext tinytext); +create table t2 (c_tinyblob tinyblob); # not affected by bug, for regression testing +set @c = repeat(' ', 256); +set @q = repeat('q', 256); + +set sql_mode = ''; + +insert into t1 values(@c, @c, @c); +insert into t2 values(@c); +insert into t1 values(@q, @q, @q); +insert into t2 values(@q); + +set sql_mode = 'traditional'; + +insert into t1 values(@c, @c, @c); +--error 1406 +insert into t2 values(@c); +--error 1406 +insert into t1 values(@q, NULL, NULL); +--error 1406 +insert into t1 values(NULL, @q, NULL); +--error 1406 +insert into t1 values(NULL, NULL, @q); +--error 1406 +insert into t2 values(@q); + +drop table t1, t2; + --echo End of 5.0 tests |