diff options
Diffstat (limited to 'mysql-test')
51 files changed, 1307 insertions, 27 deletions
diff --git a/mysql-test/extra/rpl_tests/rpl_lower_case_table_names.test b/mysql-test/extra/rpl_tests/rpl_lower_case_table_names.test new file mode 100644 index 00000000000..fa48142ee91 --- /dev/null +++ b/mysql-test/extra/rpl_tests/rpl_lower_case_table_names.test @@ -0,0 +1,141 @@ +# BUG#37656 +# +# This test aims at checking whether lower_case_table_names=1 option works +# for database names and table names. +# +# This test checks the following (when lower_case_table_names=1 is set on slave): +# (i) creating a database on upper case on master results in lower case +# database name on slave +# (ii) creating tables with upper case names on master results in lower case +# table names on slave +# (iii) loading data infile into capitalized table name on master replicates to +# lower case table name on slave +# (iv) Propagating changes from upper case table names on into correspondent +# lower case table names on slave works. + + +# setup: create database and tables +-- echo ******** [ MASTER ] ******** +-- let $dbname_upper= BUG_37656 +-- let $dbname_lower= `SELECT LOWER('$dbname_upper')` +-- eval CREATE DATABASE $dbname_upper +-- eval use $dbname_upper + +# assert: database names are in upper case in master and lower +# case in slave +-- eval show databases like '$dbname_upper' +sync_slave_with_master; +-- echo ******** [ SLAVE ] ******** +--eval show databases like '$dbname_lower' + +-- connection master +-- echo ******** [ MASTER ] ******** +CREATE TABLE T1 (a int); +-- eval CREATE TABLE T2 (b int) ENGINE=$engine +CREATE TABLE T3 (txt TEXT); + +# assert: that tables exist on master with upper case names +show tables; + +# assert: that tables exist on slave but with lower case names +-- sync_slave_with_master +-- echo ******** [ SLAVE ] ******** +-- eval use $dbname_lower +show tables; + +# action: lets create t1 for asserting below that t1 does not get changes +# from master (slave configured with --replicate-ignore-db=$dbname_lower.t1) +CREATE TABLE t1 (a INT); + +# action: fill data into tables +-- connection master +-- echo ******** [ MASTER ] ******** +-- eval use $dbname_upper +INSERT INTO T1 VALUES (1); +INSERT INTO T2 VALUES (1); +if (`SELECT @@session.binlog_format != 'ROW'`) +{ + -- eval LOAD DATA INFILE '../../std_data/words.dat' INTO TABLE $dbname_upper.T3 +} + +if (`SELECT @@session.binlog_format = 'ROW'`) +{ + use test; + -- eval INSERT INTO $dbname_upper.T1 VALUES (2) + -- eval INSERT INTO $dbname_upper.T2 VALUES (2) + -- eval LOAD DATA INFILE '../../std_data/words.dat' INTO TABLE $dbname_upper.T3 +} +# assert: lower case tables on lower case database on slave +# get updates from upper case tables on upper case +# database on master +-- sync_slave_with_master +-- echo ******** [ SLAVE ] ******** + +# assert: changes for slave's t1 were filterd out +if (`SELECT count(*) != 0 FROM t1`) +{ + -- echo UNEXPECTED DATA on $dbname_lower.t1 as table is filtered by replicate-ignore-table rules +} + +-- let $diff_tables=master:$dbname_upper.T2, slave:$dbname_lower.t2 +-- source include/diff_tables.inc + +-- let $diff_tables=master:$dbname_upper.T3, slave:$dbname_lower.t3 +-- source include/diff_tables.inc + +# clean up +-- connection master +-- echo ******** [ MASTER ] ******** +-- eval DROP DATABASE $dbname_upper +-- sync_slave_with_master + + +# +# BUG#50653: drop procedure implicitely treats db name in a case sensitive way +# + +-- connection master + +-- let $dbname= B50653 +-- let $procname= b50653_proc + +-- eval CREATE DATABASE $dbname +-- eval USE $dbname +-- eval CREATE PROCEDURE $procname() BEGIN SELECT 1; END + +if (`SELECT count(*) = 1 FROM mysql.proc WHERE name like '$dbname'`) +{ + -- die Procedure not created on MASTER +} + +-- sync_slave_with_master +if (`SELECT count(*) = 1 FROM mysql.proc WHERE name like '$dbname'`) +{ + -- die Procedure not created on SLAVE +} + +-- connection master +-- eval DROP PROCEDURE $procname + +if (`SELECT count(*) FROM mysql.proc WHERE name like '$dbname'`) +{ + -- die Procedure not dropped on MASTER +} + +-- sync_slave_with_master +if (`SELECT count(*) FROM mysql.proc WHERE name like '$dbname'`) +{ + -- die Procedure not dropped on SLAVE +} + +-- let $last_error = query_get_value("SHOW SLAVE STATUS", Last_SQL_Errno, 1) +if ($last_error) +{ + -- die UNEXPECTED SLAVE SQL error: $last_error +} + +-- connection master +-- eval DROP DATABASE $dbname +-- sync_slave_with_master + +-- source include/rpl_end.inc diff --git a/mysql-test/lib/My/Config.pm b/mysql-test/lib/My/Config.pm index 1d8d1617fce..d6924a3c866 100644 --- a/mysql-test/lib/My/Config.pm +++ b/mysql-test/lib/My/Config.pm @@ -195,14 +195,10 @@ sub value { my ($self, $option_name)= @_; my $option= $self->option($option_name); - if (! defined($option) and defined $ENV{$option_name}) { + if (! defined($option)) { my $value= $ENV{$option_name}; $option= My::Config::Option->new($option_name, $value); } - - croak "No option named '$option_name' in group '$self->{name}'" - if ! defined($option); - return $option->value(); } diff --git a/mysql-test/mysql-test-run.pl b/mysql-test/mysql-test-run.pl index a9e6df8d12d..7c65e8f68b8 100755 --- a/mysql-test/mysql-test-run.pl +++ b/mysql-test/mysql-test-run.pl @@ -1461,7 +1461,7 @@ sub command_line_setup { foreach my $fs (@tmpfs_locations) { - if ( -d $fs ) + if ( -d $fs && -w $fs ) { my $template= "var_${opt_build_thread}_XXXX"; $opt_mem= tempdir( $template, DIR => $fs, CLEANUP => 0); diff --git a/mysql-test/r/alter_table.result b/mysql-test/r/alter_table.result index 8e61031f8ac..56bb5fe0020 100644 --- a/mysql-test/r/alter_table.result +++ b/mysql-test/r/alter_table.result @@ -1392,3 +1392,53 @@ t1 CREATE TABLE `t1` ( `consultant_id` bigint(20) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8 DROP TABLE t1; +# +# BUG#27788685: NO WARNING WHEN TRUNCATING A STRING WITH DATA LOSS +# +SET GLOBAL max_allowed_packet=17825792; +CREATE TABLE t1 (t1_fld1 TEXT); +CREATE TABLE t2 (t2_fld1 MEDIUMTEXT); +CREATE TABLE t3 (t3_fld1 LONGTEXT); +INSERT INTO t1 VALUES (REPEAT('a',300)); +INSERT INTO t2 VALUES (REPEAT('b',65680)); +INSERT INTO t3 VALUES (REPEAT('c',16777300)); +SELECT LENGTH(t1_fld1) FROM t1; +LENGTH(t1_fld1) +300 +SELECT LENGTH(t2_fld1) FROM t2; +LENGTH(t2_fld1) +65680 +SELECT LENGTH(t3_fld1) FROM t3; +LENGTH(t3_fld1) +16777300 +# With strict mode +SET SQL_MODE='STRICT_ALL_TABLES'; +ALTER TABLE t1 CHANGE `t1_fld1` `my_t1_fld1` TINYTEXT; +ERROR 22001: Data too long for column 'my_t1_fld1' at row 1 +ALTER TABLE t2 CHANGE `t2_fld1` `my_t2_fld1` TEXT; +ERROR 22001: Data too long for column 'my_t2_fld1' at row 1 +ALTER TABLE t3 CHANGE `t3_fld1` `my_t3_fld1` MEDIUMTEXT; +ERROR 22001: Data too long for column 'my_t3_fld1' at row 1 +# With non-strict mode +SET SQL_MODE=''; +ALTER TABLE t1 CHANGE `t1_fld1` `my_t1_fld1` TINYTEXT; +Warnings: +Warning 1265 Data truncated for column 'my_t1_fld1' at row 1 +ALTER TABLE t2 CHANGE `t2_fld1` `my_t2_fld1` TEXT; +Warnings: +Warning 1265 Data truncated for column 'my_t2_fld1' at row 1 +ALTER TABLE t3 CHANGE `t3_fld1` `my_t3_fld1` MEDIUMTEXT; +Warnings: +Warning 1265 Data truncated for column 'my_t3_fld1' at row 1 +SELECT LENGTH(my_t1_fld1) FROM t1; +LENGTH(my_t1_fld1) +255 +SELECT LENGTH(my_t2_fld1) FROM t2; +LENGTH(my_t2_fld1) +65535 +SELECT LENGTH(my_t3_fld1) FROM t3; +LENGTH(my_t3_fld1) +16777215 +DROP TABLE t1, t2, t3; +SET SQL_MODE=default; +SET GLOBAL max_allowed_packet=default; diff --git a/mysql-test/r/derived_opt.result b/mysql-test/r/derived_opt.result index dc9b5910af1..bbca7f2ac90 100644 --- a/mysql-test/r/derived_opt.result +++ b/mysql-test/r/derived_opt.result @@ -499,9 +499,42 @@ where D1.a= t1.a; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 10 Using where -1 PRIMARY <derived2> hash_ALL key0 #hash#key0 5 test.t1.a 100 Using join buffer (flat, BNLH join) +1 PRIMARY <derived2> ref key0 key0 5 test.t1.a 10 2 DERIVED t2 ALL NULL NULL NULL NULL 100 Using filesort set join_cache_level=@tmp_jcl; set optimizer_switch=@tmp_os; drop table t1, t2; +# +# Bug mdev-17382: equi-join of derived table with join_cache_level=4 +# +CREATE TABLE t1 ( +id int NOT NULL, +amount decimal DEFAULT NULL, +PRIMARY KEY (id) +); +CREATE TABLE t2 ( +id int NOT NULL, +name varchar(50) DEFAULT NULL, +PRIMARY KEY (id) +); +INSERT INTO t1 VALUES +(1, 10.0000), (2, 20.0000), (3, 30.0000), (4, 40.0000), +(5, NULL), (6, NULL), (7, 70.0000), (8, 80.0000); +INSERT INTO t2 VALUES +(1,'A'), (2,'B'), (3,'C'), (4,'D'), (5, NULL), (6, NULL), +(7,'E'), (8,'F'), (9,'G'), (10,'H'), (11, NULL), (12, NULL); +set join_cache_level=4; +EXPLAIN +SELECT t2.id,t2.name,t.total_amt +FROM t2 +LEFT JOIN +(SELECT id, sum(amount) total_amt FROM t1 GROUP BY id) AS t +ON t2.id=t.id +WHERE t2.id < 3; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 range PRIMARY PRIMARY 4 NULL 3 Using index condition +1 PRIMARY <derived2> ref key0 key0 5 test.t2.id 2 +2 DERIVED t1 ALL NULL NULL NULL NULL 8 Using temporary; Using filesort +set join_cache_level=default; +DROP TABLE t1,t2; set optimizer_switch=@exit_optimizer_switch; diff --git a/mysql-test/r/derived_view.result b/mysql-test/r/derived_view.result index f7062473a3f..12811ebc6b3 100644 --- a/mysql-test/r/derived_view.result +++ b/mysql-test/r/derived_view.result @@ -1101,7 +1101,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t3 system NULL NULL NULL NULL 1 100.00 2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 select 6 AS `a`,5 AS `b` from `test`.`t3` where (not(<expr_cache><6,5>(<in_optimizer>((6,5),<exists>(select 7,5 having (trigcond(((<cache>(6) = 7) or isnull(7))) and trigcond(((<cache>(5) = 5) or isnull(5))) and trigcond(<is_not_null_test>(7)) and trigcond(<is_not_null_test>(5)))))))) +Note 1003 select 6 AS `a`,5 AS `b` from `test`.`t3` where (not(<expr_cache><6,5>(<in_optimizer>((6,5),<exists>(select 7,5 having (trigcond(((<cache>(6) = 7) or isnull(/*always not null*/ 1))) and trigcond(((<cache>(5) = 5) or isnull(/*always not null*/ 1))) and trigcond(<is_not_null_test>(7)) and trigcond(<is_not_null_test>(5)))))))) SELECT t.a,t.b FROM t3 RIGHT JOIN ((SELECT * FROM t1) AS t, t2) ON t2.b != 0 WHERE (t.a,t.b) NOT IN (SELECT 7, 5); a b @@ -1115,7 +1115,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t3 system NULL NULL NULL NULL 1 100.00 3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 select 6 AS `a`,5 AS `b` from `test`.`t3` where (not(<expr_cache><6,5>(<in_optimizer>((6,5),<exists>(select 7,5 having (trigcond(((<cache>(6) = 7) or isnull(7))) and trigcond(((<cache>(5) = 5) or isnull(5))) and trigcond(<is_not_null_test>(7)) and trigcond(<is_not_null_test>(5)))))))) +Note 1003 select 6 AS `a`,5 AS `b` from `test`.`t3` where (not(<expr_cache><6,5>(<in_optimizer>((6,5),<exists>(select 7,5 having (trigcond(((<cache>(6) = 7) or isnull(/*always not null*/ 1))) and trigcond(((<cache>(5) = 5) or isnull(/*always not null*/ 1))) and trigcond(<is_not_null_test>(7)) and trigcond(<is_not_null_test>(5)))))))) SELECT t.a,t.b FROM t3 RIGHT JOIN (v1 AS t, t2) ON t2.b != 0 WHERE (t.a,t.b) NOT IN (SELECT 7, 5); a b @@ -1129,7 +1129,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t3 system NULL NULL NULL NULL 1 100.00 2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 select 6 AS `a`,5 AS `b` from `test`.`t3` where (not(<expr_cache><6,5>(<in_optimizer>((6,5),<exists>(select 7,5 having (trigcond(((<cache>(6) = 7) or isnull(7))) and trigcond(((<cache>(5) = 5) or isnull(5))) and trigcond(<is_not_null_test>(7)) and trigcond(<is_not_null_test>(5)))))))) +Note 1003 select 6 AS `a`,5 AS `b` from `test`.`t3` where (not(<expr_cache><6,5>(<in_optimizer>((6,5),<exists>(select 7,5 having (trigcond(((<cache>(6) = 7) or isnull(/*always not null*/ 1))) and trigcond(((<cache>(5) = 5) or isnull(/*always not null*/ 1))) and trigcond(<is_not_null_test>(7)) and trigcond(<is_not_null_test>(5)))))))) DROP VIEW v1; DROP TABLE t1,t2,t3; # diff --git a/mysql-test/r/func_isnull.result b/mysql-test/r/func_isnull.result index 88c5bfd5468..a97d4a67939 100644 --- a/mysql-test/r/func_isnull.result +++ b/mysql-test/r/func_isnull.result @@ -106,5 +106,25 @@ Note 1003 select `test`.`t2`.`d1` AS `d1`,`test`.`t1`.`d1` AS `d1` from `test`.` DROP VIEW v1; DROP TABLE t1,t2; # +# MDEV-15475: Assertion `!table || (!table->read_set || +# bitmap_is_set(table->read_set, field_index))' +# failed on EXPLAIN EXTENDED with constant table and view +# +CREATE TABLE t1 (pk INT PRIMARY KEY) ENGINE=MyISAM; +CREATE VIEW v1 AS SELECT * FROM t1; +INSERT INTO t1 VALUES (1); +EXPLAIN EXTENDED SELECT ISNULL(pk) FROM v1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 system NULL NULL NULL NULL 1 100.00 +Warnings: +Note 1003 select isnull(/*always not null*/ 1) AS `ISNULL(pk)` from dual +EXPLAIN EXTENDED SELECT IFNULL(pk,0) FROM v1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 system NULL NULL NULL NULL 1 100.00 +Warnings: +Note 1003 select ifnull(1,0) AS `IFNULL(pk,0)` from dual +DROP VIEW v1; +DROP TABLE t1; +# # End of 5.5 tests # diff --git a/mysql-test/r/func_time.result b/mysql-test/r/func_time.result index cffed8eae5b..9d478b10d8a 100644 --- a/mysql-test/r/func_time.result +++ b/mysql-test/r/func_time.result @@ -2657,6 +2657,167 @@ SEC_TO_TIME(MAKEDATE(0,RAND(~0))) 838:59:59 Warnings: Warning 1292 Truncated incorrect time value: '20000101' +SELECT PERIOD_DIFF(2018, AES_ENCRYPT('Rae Bareli', 'Rae Bareli')); +PERIOD_DIFF(2018, AES_ENCRYPT('Rae Bareli', 'Rae Bareli')) +24257 +# +# MDEV-17249 MAKETIME(-1e50,0,0) returns a wrong result +# +SELECT +MAKETIME(1e10,0,0), +MAKETIME(-1e10,0,0), +MAKETIME(1e50,0,0), +MAKETIME(-1e50,0,0), +MAKETIME(COALESCE(1e50),0,0), +MAKETIME(COALESCE(-1e50),0,0); +MAKETIME(1e10,0,0) 838:59:59 +MAKETIME(-1e10,0,0) -838:59:59 +MAKETIME(1e50,0,0) 838:59:59 +MAKETIME(-1e50,0,0) -838:59:59 +MAKETIME(COALESCE(1e50),0,0) 838:59:59 +MAKETIME(COALESCE(-1e50),0,0) -838:59:59 +Warnings: +Level Warning +Code 1292 +Message Truncated incorrect time value: '10000000000:00:00' +Level Warning +Code 1292 +Message Truncated incorrect time value: '-10000000000:00:00' +Level Warning +Code 1292 +Message Truncated incorrect time value: '9223372036854775807:00:00' +Level Warning +Code 1292 +Message Truncated incorrect time value: '-9223372036854775808:00:00' +Level Warning +Code 1292 +Message Truncated incorrect time value: '9223372036854775807:00:00' +Level Warning +Code 1292 +Message Truncated incorrect time value: '-9223372036854775808:00:00' +CREATE TABLE t1 (a FLOAT); +INSERT INTO t1 VALUES (1e30),(-1e30); +SELECT MAKETIME(a,0,0) FROM t1; +MAKETIME(a,0,0) +838:59:59 +-838:59:59 +Warnings: +Warning 1292 Truncated incorrect time value: '9223372036854775807:00:00' +Warning 1292 Truncated incorrect time value: '-9223372036854775808:00:00' +DROP TABLE t1; +# +# MDEV-17244 MAKETIME(900,0,0.111) returns a wrong result +# +SELECT MAKETIME(900,0,0); +MAKETIME(900,0,0) +838:59:59 +Warnings: +Warning 1292 Truncated incorrect time value: '900:00:00' +SELECT MAKETIME(900,0,0.1); +MAKETIME(900,0,0.1) +838:59:59.9 +Warnings: +Warning 1292 Truncated incorrect time value: '900:00:00' +SELECT MAKETIME(900,0,0.11); +MAKETIME(900,0,0.11) +838:59:59.99 +Warnings: +Warning 1292 Truncated incorrect time value: '900:00:00' +SELECT MAKETIME(900,0,0.111); +MAKETIME(900,0,0.111) +838:59:59.999 +Warnings: +Warning 1292 Truncated incorrect time value: '900:00:00' +SELECT MAKETIME(900,0,0.1111); +MAKETIME(900,0,0.1111) +838:59:59.9999 +Warnings: +Warning 1292 Truncated incorrect time value: '900:00:00' +SELECT MAKETIME(900,0,0.11111); +MAKETIME(900,0,0.11111) +838:59:59.99999 +Warnings: +Warning 1292 Truncated incorrect time value: '900:00:00' +SELECT MAKETIME(900,0,0.111111); +MAKETIME(900,0,0.111111) +838:59:59.999999 +Warnings: +Warning 1292 Truncated incorrect time value: '900:00:00' +SELECT MAKETIME(900,0,0.1111111); +MAKETIME(900,0,0.1111111) +838:59:59.999999 +Warnings: +Warning 1292 Truncated incorrect time value: '900:00:00' +SELECT MAKETIME(900,0,0.11111111); +MAKETIME(900,0,0.11111111) +838:59:59.999999 +Warnings: +Warning 1292 Truncated incorrect time value: '900:00:00' +SELECT MAKETIME(900,0,0.111111111); +MAKETIME(900,0,0.111111111) +838:59:59.999999 +Warnings: +Warning 1292 Truncated incorrect time value: '900:00:00' +SELECT MAKETIME(900,0,EXP(1)); +MAKETIME(900,0,EXP(1)) +838:59:59.999999 +Warnings: +Warning 1292 Truncated incorrect time value: '900:00:02' +SELECT MAKETIME(-900,0,0); +MAKETIME(-900,0,0) +-838:59:59 +Warnings: +Warning 1292 Truncated incorrect time value: '-900:00:00' +SELECT MAKETIME(-900,0,0.1); +MAKETIME(-900,0,0.1) +-838:59:59.9 +Warnings: +Warning 1292 Truncated incorrect time value: '-900:00:00' +SELECT MAKETIME(-900,0,0.11); +MAKETIME(-900,0,0.11) +-838:59:59.99 +Warnings: +Warning 1292 Truncated incorrect time value: '-900:00:00' +SELECT MAKETIME(-900,0,0.111); +MAKETIME(-900,0,0.111) +-838:59:59.999 +Warnings: +Warning 1292 Truncated incorrect time value: '-900:00:00' +SELECT MAKETIME(-900,0,0.1111); +MAKETIME(-900,0,0.1111) +-838:59:59.9999 +Warnings: +Warning 1292 Truncated incorrect time value: '-900:00:00' +SELECT MAKETIME(-900,0,0.11111); +MAKETIME(-900,0,0.11111) +-838:59:59.99999 +Warnings: +Warning 1292 Truncated incorrect time value: '-900:00:00' +SELECT MAKETIME(-900,0,0.111111); +MAKETIME(-900,0,0.111111) +-838:59:59.999999 +Warnings: +Warning 1292 Truncated incorrect time value: '-900:00:00' +SELECT MAKETIME(-900,0,0.1111111); +MAKETIME(-900,0,0.1111111) +-838:59:59.999999 +Warnings: +Warning 1292 Truncated incorrect time value: '-900:00:00' +SELECT MAKETIME(-900,0,0.11111111); +MAKETIME(-900,0,0.11111111) +-838:59:59.999999 +Warnings: +Warning 1292 Truncated incorrect time value: '-900:00:00' +SELECT MAKETIME(-900,0,0.111111111); +MAKETIME(-900,0,0.111111111) +-838:59:59.999999 +Warnings: +Warning 1292 Truncated incorrect time value: '-900:00:00' +SELECT MAKETIME(-900,0,EXP(1)); +MAKETIME(-900,0,EXP(1)) +-838:59:59.999999 +Warnings: +Warning 1292 Truncated incorrect time value: '-900:00:02' # # End of 5.5 tests # diff --git a/mysql-test/r/grant.result b/mysql-test/r/grant.result index 85e42ea96ee..60f2eb90e2d 100644 --- a/mysql-test/r/grant.result +++ b/mysql-test/r/grant.result @@ -2581,3 +2581,24 @@ foo@localhost foo@127.0.0.1 # Clean-up DROP USER foo@'127.0.0.1'; # End of Bug#12766319 +create user foo@localhost; +create database foodb; +grant create routine on foodb.* to foo@localhost; +create procedure fooproc() select 'i am fooproc'; +show grants; +Grants for foo@localhost +GRANT USAGE ON *.* TO 'foo'@'localhost' +GRANT CREATE ROUTINE ON `foodb`.* TO 'foo'@'localhost' +GRANT EXECUTE, ALTER ROUTINE ON PROCEDURE `test`.`fooproc` TO 'foo'@'localhost' +rename table mysql.procs_priv to mysql.procs_priv1; +flush privileges; +ERROR 42S02: Table 'mysql.procs_priv' doesn't exist +show grants for foo@localhost; +Grants for foo@localhost +GRANT USAGE ON *.* TO 'foo'@'localhost' +GRANT CREATE ROUTINE ON `foodb`.* TO 'foo'@'localhost' +GRANT EXECUTE, ALTER ROUTINE ON PROCEDURE `test`.`fooproc` TO 'foo'@'localhost' +rename table mysql.procs_priv1 to mysql.procs_priv; +drop user foo@localhost; +drop procedure fooproc; +drop database foodb; diff --git a/mysql-test/r/innodb_mrr_cpk.result b/mysql-test/r/innodb_mrr_cpk.result index 99ed73a3e83..355eae70edb 100644 --- a/mysql-test/r/innodb_mrr_cpk.result +++ b/mysql-test/r/innodb_mrr_cpk.result @@ -226,7 +226,7 @@ set join_cache_level=3; explain SELECT 1 FROM (SELECT url, id FROM t2 LIMIT 1 OFFSET 20) derived RIGHT JOIN t1 ON t1.id = derived.id; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL # -1 PRIMARY <derived2> hash_ALL key0 #hash#key0 25 test.t1.id # Using join buffer (flat, BNLH join) +1 PRIMARY <derived2> ref key0 key0 25 test.t1.id # 2 DERIVED t2 ALL NULL NULL NULL NULL # set join_cache_level= @tmp_mdev5037; drop table t0,t1,t2; diff --git a/mysql-test/r/join.result b/mysql-test/r/join.result index 7b0e7807e39..2e5ee30b54e 100644 --- a/mysql-test/r/join.result +++ b/mysql-test/r/join.result @@ -1514,11 +1514,13 @@ ERROR 42S22: Unknown column 'f' in 'from clause' DROP TABLE t; CREATE TABLE t (f INT); CALL p; -ERROR 42S22: Unknown column 'f' in 'from clause' +f DROP TABLE t; CREATE TABLE t (i INT); CALL p; -ERROR 42S22: Unknown column 'f' in 'from clause' +ERROR 42S22: Unknown column 't1.f' in 'field list' +CALL p; +ERROR 42S22: Unknown column 't1.f' in 'field list' DROP PROCEDURE p; DROP TABLE t; CREATE TABLE t1 (a INT, b INT); diff --git a/mysql-test/r/lowercase_fs_off.result b/mysql-test/r/lowercase_fs_off.result index c3284b225dd..362661d8911 100644 --- a/mysql-test/r/lowercase_fs_off.result +++ b/mysql-test/r/lowercase_fs_off.result @@ -63,3 +63,44 @@ CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET new.a= 1; RENAME TABLE t1 TO T1; ALTER TABLE T1 RENAME t1; DROP TABLE t1; +create database TEST; +create procedure TEST.pr() begin end; +create procedure test.pr() begin end; +Phase 1/4: Fixing views +Phase 2/4: Fixing table and database names +Phase 3/4: Checking and upgrading tables +Processing databases +information_schema +TEST +mtr +mtr.global_suppressions OK +mtr.test_suppressions OK +mysql +mysql.columns_priv OK +mysql.db OK +mysql.event OK +mysql.func OK +mysql.help_category OK +mysql.help_keyword OK +mysql.help_relation OK +mysql.help_topic OK +mysql.host OK +mysql.ndb_binlog_index OK +mysql.plugin OK +mysql.proc OK +mysql.procs_priv OK +mysql.proxies_priv OK +mysql.servers OK +mysql.tables_priv OK +mysql.time_zone OK +mysql.time_zone_leap_second OK +mysql.time_zone_name OK +mysql.time_zone_transition OK +mysql.time_zone_transition_type OK +mysql.user OK +performance_schema +test +Phase 4/4: Running 'mysql_fix_privilege_tables' +OK +drop procedure test.pr; +drop database TEST; diff --git a/mysql-test/r/sp-security.result b/mysql-test/r/sp-security.result index 88908b05f46..39237b7f322 100644 --- a/mysql-test/r/sp-security.result +++ b/mysql-test/r/sp-security.result @@ -617,3 +617,24 @@ SELECT 1 latin1 latin1_swedish_ci latin1_swedish_ci # Connection default DROP USER user2@localhost; DROP DATABASE db1; +create user foo@local_ost; +create user foo@`local\_ost` identified by 'nevermore'; +create database foodb; +grant create routine on foodb.* to foo@local_ost; +select user(), current_user(); +user() current_user() +foo@localhost foo@local_ost +show grants; +Grants for foo@local_ost +GRANT USAGE ON *.* TO 'foo'@'local_ost' +GRANT CREATE ROUTINE ON `foodb`.* TO 'foo'@'local_ost' +create procedure fooproc() select 'i am fooproc'; +show grants; +Grants for foo@local_ost +GRANT USAGE ON *.* TO 'foo'@'local_ost' +GRANT CREATE ROUTINE ON `foodb`.* TO 'foo'@'local_ost' +GRANT EXECUTE, ALTER ROUTINE ON PROCEDURE `test`.`fooproc` TO 'foo'@'local_ost' +drop user foo@local_ost; +drop user foo@`local\_ost`; +drop procedure fooproc; +drop database foodb; diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result index 823c6f78cee..4535056242a 100644 --- a/mysql-test/r/sp.result +++ b/mysql-test/r/sp.result @@ -8055,4 +8055,21 @@ SET S.CLOSE_YN = '' where 1=1; drop function if exists f1; drop table t1,t2; +# +# MDEV-16957: Server crashes in Field_iterator_natural_join::next +# upon 2nd execution of SP +# +CREATE TABLE t1 (a INT, b VARCHAR(32)); +CREATE PROCEDURE sp() SELECT * FROM t1 AS t1x JOIN t1 AS t1y USING (c); +CALL sp; +ERROR 42S22: Unknown column 'c' in 'from clause' +CALL sp; +ERROR 42S22: Unknown column 'c' in 'from clause' +CALL sp; +ERROR 42S22: Unknown column 'c' in 'from clause' +alter table t1 add column c int; +CALL sp; +c a b a b +DROP PROCEDURE sp; +DROP TABLE t1; # End of 5.5 test diff --git a/mysql-test/r/subselect_mat.result b/mysql-test/r/subselect_mat.result index eca3b760b65..efc348a26ce 100644 --- a/mysql-test/r/subselect_mat.result +++ b/mysql-test/r/subselect_mat.result @@ -499,7 +499,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where 2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <expr_cache><`test`.`t1`.`a1`,`test`.`t1`.`a2`>(<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(select '1 - 01','2 - 01' having (((<cache>(`test`.`t1`.`a1`) = '1 - 01') or isnull('1 - 01')) and ((<cache>(`test`.`t1`.`a2`) = '2 - 01') or isnull('2 - 01')) and <is_not_null_test>('1 - 01') and <is_not_null_test>('2 - 01'))))) +Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <expr_cache><`test`.`t1`.`a1`,`test`.`t1`.`a2`>(<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(select '1 - 01','2 - 01' having (((<cache>(`test`.`t1`.`a1`) = '1 - 01') or isnull(/*always not null*/ 1)) and ((<cache>(`test`.`t1`.`a2`) = '2 - 01') or isnull(/*always not null*/ 1)) and <is_not_null_test>('1 - 01') and <is_not_null_test>('2 - 01'))))) select * from t1 where (a1, a2) in (select '1 - 01', '2 - 01'); a1 a2 1 - 01 2 - 01 @@ -509,7 +509,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where 2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <expr_cache><`test`.`t1`.`a1`,`test`.`t1`.`a2`>(<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(select '1 - 01','2 - 01' having (((<cache>(`test`.`t1`.`a1`) = '1 - 01') or isnull('1 - 01')) and ((<cache>(`test`.`t1`.`a2`) = '2 - 01') or isnull('2 - 01')) and <is_not_null_test>('1 - 01') and <is_not_null_test>('2 - 01'))))) +Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <expr_cache><`test`.`t1`.`a1`,`test`.`t1`.`a2`>(<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(select '1 - 01','2 - 01' having (((<cache>(`test`.`t1`.`a1`) = '1 - 01') or isnull(/*always not null*/ 1)) and ((<cache>(`test`.`t1`.`a2`) = '2 - 01') or isnull(/*always not null*/ 1)) and <is_not_null_test>('1 - 01') and <is_not_null_test>('2 - 01'))))) select * from t1 where (a1, a2) in (select '1 - 01', '2 - 01' from dual); a1 a2 1 - 01 2 - 01 @@ -1896,7 +1896,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 100.00 Warnings: -Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from <materialize> (select max(`test`.`t2`.`c`) from `test`.`t2`) join `test`.`t1` where ((`test`.`t1`.`b` = 7) and (`test`.`t1`.`a` = `<subquery2>`.`MAX(c)`) and (<cache>(isnull(`<subquery2>`.`MAX(c)`)) or (`<subquery2>`.`MAX(c)` = 7))) +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from <materialize> (select max(`test`.`t2`.`c`) from `test`.`t2`) join `test`.`t1` where ((`test`.`t1`.`b` = 7) and (`test`.`t1`.`a` = `<subquery2>`.`MAX(c)`) and (<cache>(isnull(/*always not null*/ 1)) or (`<subquery2>`.`MAX(c)` = 7))) SELECT * FROM t1 WHERE a IN (SELECT MAX(c) FROM t2) AND b=7 AND (a IS NULL OR a=b); a b diff --git a/mysql-test/r/subselect_sj_mat.result b/mysql-test/r/subselect_sj_mat.result index 180c182a51a..fd9435e8a39 100644 --- a/mysql-test/r/subselect_sj_mat.result +++ b/mysql-test/r/subselect_sj_mat.result @@ -520,7 +520,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where 2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <expr_cache><`test`.`t1`.`a1`,`test`.`t1`.`a2`>(<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(select '1 - 01','2 - 01' having (((<cache>(`test`.`t1`.`a1`) = '1 - 01') or isnull('1 - 01')) and ((<cache>(`test`.`t1`.`a2`) = '2 - 01') or isnull('2 - 01')) and <is_not_null_test>('1 - 01') and <is_not_null_test>('2 - 01'))))) +Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <expr_cache><`test`.`t1`.`a1`,`test`.`t1`.`a2`>(<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(select '1 - 01','2 - 01' having (((<cache>(`test`.`t1`.`a1`) = '1 - 01') or isnull(/*always not null*/ 1)) and ((<cache>(`test`.`t1`.`a2`) = '2 - 01') or isnull(/*always not null*/ 1)) and <is_not_null_test>('1 - 01') and <is_not_null_test>('2 - 01'))))) select * from t1 where (a1, a2) in (select '1 - 01', '2 - 01'); a1 a2 1 - 01 2 - 01 @@ -530,7 +530,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where 2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <expr_cache><`test`.`t1`.`a1`,`test`.`t1`.`a2`>(<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(select '1 - 01','2 - 01' having (((<cache>(`test`.`t1`.`a1`) = '1 - 01') or isnull('1 - 01')) and ((<cache>(`test`.`t1`.`a2`) = '2 - 01') or isnull('2 - 01')) and <is_not_null_test>('1 - 01') and <is_not_null_test>('2 - 01'))))) +Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <expr_cache><`test`.`t1`.`a1`,`test`.`t1`.`a2`>(<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(select '1 - 01','2 - 01' having (((<cache>(`test`.`t1`.`a1`) = '1 - 01') or isnull(/*always not null*/ 1)) and ((<cache>(`test`.`t1`.`a2`) = '2 - 01') or isnull(/*always not null*/ 1)) and <is_not_null_test>('1 - 01') and <is_not_null_test>('2 - 01'))))) select * from t1 where (a1, a2) in (select '1 - 01', '2 - 01' from dual); a1 a2 1 - 01 2 - 01 @@ -1934,7 +1934,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 100.00 Warnings: -Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from <materialize> (select max(`test`.`t2`.`c`) from `test`.`t2`) join `test`.`t1` where ((`test`.`t1`.`b` = 7) and (`test`.`t1`.`a` = `<subquery2>`.`MAX(c)`) and (<cache>(isnull(`<subquery2>`.`MAX(c)`)) or (`<subquery2>`.`MAX(c)` = 7))) +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from <materialize> (select max(`test`.`t2`.`c`) from `test`.`t2`) join `test`.`t1` where ((`test`.`t1`.`b` = 7) and (`test`.`t1`.`a` = `<subquery2>`.`MAX(c)`) and (<cache>(isnull(/*always not null*/ 1)) or (`<subquery2>`.`MAX(c)` = 7))) SELECT * FROM t1 WHERE a IN (SELECT MAX(c) FROM t2) AND b=7 AND (a IS NULL OR a=b); a b diff --git a/mysql-test/r/type_float.result b/mysql-test/r/type_float.result index f41c2e7688b..f8574167de3 100644 --- a/mysql-test/r/type_float.result +++ b/mysql-test/r/type_float.result @@ -447,3 +447,39 @@ End of 5.0 tests select format(truncate('1.7976931348623157E+308',-12),1,'fr_BE') as foo; foo 0 +# +# MDEV-17249 MAKETIME(-1e50,0,0) returns a wrong result +# +SELECT LEFT('a',EXP(50)); +LEFT('a',EXP(50)) +a +SELECT LEFT('a', COALESCE(1e30)); +LEFT('a', COALESCE(1e30)) +a +CREATE TABLE t1 (a FLOAT); +INSERT INTO t1 VALUES (1e30); +SELECT LEFT('a',a), LEFT('a',1e30) FROM t1; +LEFT('a',a) LEFT('a',1e30) +a a +DROP TABLE t1; +PREPARE stmt FROM 'SELECT LEFT(111,?)'; +SET @a=1e30; +EXECUTE stmt USING @a; +LEFT(111,?) +111 +DEALLOCATE PREPARE stmt; +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(2),(3); +SELECT LEFT('a',(SELECT 1e30 FROM t1 LIMIT 1)); +LEFT('a',(SELECT 1e30 FROM t1 LIMIT 1)) +a +DROP TABLE t1; +CREATE TABLE t1 (a DOUBLE); +INSERT INTO t1 VALUES (1e30),(0); +SELECT LEFT('a', SUM(a)) FROM t1; +LEFT('a', SUM(a)) +a +SELECT LEFT('a', AVG(a)) FROM t1; +LEFT('a', AVG(a)) +a +DROP TABLE t1; diff --git a/mysql-test/suite/heap/heap_btree.result b/mysql-test/suite/heap/heap_btree.result index 12a011778c6..83d1bcb6c92 100644 --- a/mysql-test/suite/heap/heap_btree.result +++ b/mysql-test/suite/heap/heap_btree.result @@ -379,3 +379,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range uniq_id uniq_id 8 NULL 4 Using where drop table t1; End of 5.3 tests +create table t1 (id int, a varchar(300) not null, key using btree(a)) engine=heap; +insert t1 values (1, repeat('a', 300)); +drop table t1; +End of 5.5 tests diff --git a/mysql-test/suite/heap/heap_btree.test b/mysql-test/suite/heap/heap_btree.test index 02c09f52263..aca41c430b3 100644 --- a/mysql-test/suite/heap/heap_btree.test +++ b/mysql-test/suite/heap/heap_btree.test @@ -279,3 +279,12 @@ explain select 0+a from t1 where a in (869751,736494,226312,802616); drop table t1; --echo End of 5.3 tests + +# +# Bug#27799513: POTENTIAL DOUBLE FREE OR CORRUPTION OF HEAP INFO (HP_INFO) +# +create table t1 (id int, a varchar(300) not null, key using btree(a)) engine=heap; +insert t1 values (1, repeat('a', 300)); +drop table t1; + +--echo End of 5.5 tests diff --git a/mysql-test/suite/maria/fulltext2.result b/mysql-test/suite/maria/fulltext2.result new file mode 100644 index 00000000000..1e4e6636ef6 --- /dev/null +++ b/mysql-test/suite/maria/fulltext2.result @@ -0,0 +1,86 @@ +CREATE TABLE t1 ( +i int(10) unsigned not null auto_increment primary key, +a varchar(255) not null, +FULLTEXT KEY (a) +) ENGINE=Aria ROW_FORMAT=DYNAMIC MAX_ROWS=2000000000000; +repair table t1 quick; +Table Op Msg_type Msg_text +test.t1 repair status OK +check table t1; +Table Op Msg_type Msg_text +test.t1 check status OK +repair table t1; +Table Op Msg_type Msg_text +test.t1 repair status OK +check table t1; +Table Op Msg_type Msg_text +test.t1 check status OK +repair table t1; +Table Op Msg_type Msg_text +test.t1 repair status OK +select count(*) from t1 where match a against ('aaaxxx'); +count(*) +0 +select count(*) from t1 where match a against ('aaayyy'); +count(*) +150 +select count(*) from t1 where match a against ('aaaxxx' in boolean mode); +count(*) +1024 +select count(*) from t1 where match a against ('aaayyy' in boolean mode); +count(*) +150 +select count(*) from t1 where match a against ('aaax*' in boolean mode); +count(*) +1024 +select count(*) from t1 where match a against ('aaay*' in boolean mode); +count(*) +150 +select count(*) from t1 where match a against ('aaa*' in boolean mode); +count(*) +1174 +insert t1 (a) values ('aaaxxx'),('aaayyy'); +select count(*) from t1 where match a against ('aaaxxx' in boolean mode); +count(*) +1025 +select count(*) from t1 where match a against ('aaayyy'); +count(*) +151 +insert t1 (a) values ('aaaxxx 000000'); +select count(*) from t1 where match a against ('000000'); +count(*) +1 +delete from t1 where match a against ('000000'); +select count(*) from t1 where match a against ('000000'); +count(*) +0 +select count(*) from t1 where match a against ('aaaxxx'); +count(*) +0 +select count(*) from t1 where match a against ('aaaxxx' in boolean mode); +count(*) +1025 +select count(*) from t1 where match a against ('aaayyy' in boolean mode); +count(*) +151 +select count(*) from t1 where a = 'aaaxxx'; +count(*) +1025 +select count(*) from t1 where a = 'aaayyy'; +count(*) +151 +insert t1 (a) values ('aaaxxx 000000'); +select count(*) from t1 where match a against ('000000'); +count(*) +1 +select count(*) from t1 where match a against ('aaaxxx' in boolean mode); +count(*) +1026 +update t1 set a='aaaxxx' where a = 'aaayyy'; +select count(*) from t1 where match a against ('aaaxxx' in boolean mode); +count(*) +1177 +select count(*) from t1 where match a against ('aaayyy' in boolean mode); +count(*) +0 +drop table t1; diff --git a/mysql-test/suite/maria/fulltext2.test b/mysql-test/suite/maria/fulltext2.test new file mode 100644 index 00000000000..060b748eb4f --- /dev/null +++ b/mysql-test/suite/maria/fulltext2.test @@ -0,0 +1,77 @@ +# +# test of new fulltext search features +# + +let collation=utf8_unicode_ci; +source include/have_collation.inc; + +# +# two-level tree +# + +CREATE TABLE t1 ( + i int(10) unsigned not null auto_increment primary key, + a varchar(255) not null, + FULLTEXT KEY (a) +) ENGINE=Aria ROW_FORMAT=DYNAMIC MAX_ROWS=2000000000000; + +# two-level entry, second-level tree with depth 2 +disable_query_log; +let $1=1024; +while ($1) +{ + eval insert t1 (a) values ('aaaxxx'); + dec $1; +} + +# one-level entry (entries) +let $1=150; +while ($1) +{ + eval insert t1 (a) values ('aaayyy'); + dec $1; +} +enable_query_log; + +repair table t1 quick; +check table t1; +repair table t1; +check table t1; +repair table t1; + +select count(*) from t1 where match a against ('aaaxxx'); +select count(*) from t1 where match a against ('aaayyy'); +select count(*) from t1 where match a against ('aaaxxx' in boolean mode); +select count(*) from t1 where match a against ('aaayyy' in boolean mode); + +select count(*) from t1 where match a against ('aaax*' in boolean mode); +select count(*) from t1 where match a against ('aaay*' in boolean mode); +select count(*) from t1 where match a against ('aaa*' in boolean mode); + +# mi_write: +insert t1 (a) values ('aaaxxx'),('aaayyy'); +# call to enlarge_root() below +select count(*) from t1 where match a against ('aaaxxx' in boolean mode); +select count(*) from t1 where match a against ('aaayyy'); + +# mi_delete +insert t1 (a) values ('aaaxxx 000000'); +select count(*) from t1 where match a against ('000000'); +delete from t1 where match a against ('000000'); +select count(*) from t1 where match a against ('000000'); +select count(*) from t1 where match a against ('aaaxxx'); +select count(*) from t1 where match a against ('aaaxxx' in boolean mode); +select count(*) from t1 where match a against ('aaayyy' in boolean mode); +# double-check without index +select count(*) from t1 where a = 'aaaxxx'; +select count(*) from t1 where a = 'aaayyy'; + +# update +insert t1 (a) values ('aaaxxx 000000'); +select count(*) from t1 where match a against ('000000'); +select count(*) from t1 where match a against ('aaaxxx' in boolean mode); +update t1 set a='aaaxxx' where a = 'aaayyy'; +select count(*) from t1 where match a against ('aaaxxx' in boolean mode); +select count(*) from t1 where match a against ('aaayyy' in boolean mode); + +drop table t1; diff --git a/mysql-test/suite/maria/maria.result b/mysql-test/suite/maria/maria.result index 2aca47538cb..1cf48b0628d 100644 --- a/mysql-test/suite/maria/maria.result +++ b/mysql-test/suite/maria/maria.result @@ -2723,6 +2723,10 @@ id name -1 dog 2 cat DROP TABLE t1; +CREATE TABLE t1 (pk int, i2 int) ENGINE=Aria; +INSERT INTO t1 VALUES (1,2), (2,3),(3,4); +DELETE FROM tt.*, t1.* USING t1 AS tt LEFT JOIN t1 ON (tt.i2 = t1.pk); +DROP TABLE t1; # # End of 5.5 tests # diff --git a/mysql-test/suite/maria/maria.test b/mysql-test/suite/maria/maria.test index ab8e72dc321..954bd15744e 100644 --- a/mysql-test/suite/maria/maria.test +++ b/mysql-test/suite/maria/maria.test @@ -2012,6 +2012,16 @@ INSERT INTO t1 (name) VALUES ('cat'); SELECT * FROM t1; DROP TABLE t1; +# +# MDEV-16682 +# Assertion `(buff[7] & 7) == HEAD_PAGE' failed. +# + +CREATE TABLE t1 (pk int, i2 int) ENGINE=Aria; +INSERT INTO t1 VALUES (1,2), (2,3),(3,4); +DELETE FROM tt.*, t1.* USING t1 AS tt LEFT JOIN t1 ON (tt.i2 = t1.pk); +DROP TABLE t1; + --echo # --echo # End of 5.5 tests --echo # diff --git a/mysql-test/suite/parts/r/update_and_cache.result b/mysql-test/suite/parts/r/update_and_cache.result new file mode 100644 index 00000000000..52f13e66702 --- /dev/null +++ b/mysql-test/suite/parts/r/update_and_cache.result @@ -0,0 +1,7 @@ +CREATE TABLE t1 (pk INT PRIMARY KEY, a INT); +INSERT INTO t1 VALUES (1,10),(2,20); +CREATE TABLE t2 (b INT) PARTITION BY KEY (b) PARTITIONS 2; +INSERT INTO t2 VALUES (1),(2); +DELETE t2 FROM t2 WHERE b BETWEEN 5 AND 9; +UPDATE t2 JOIN t1 SET b = 5; +DROP TABLE t1, t2; diff --git a/mysql-test/suite/parts/t/update_and_cache.test b/mysql-test/suite/parts/t/update_and_cache.test new file mode 100644 index 00000000000..08ade807422 --- /dev/null +++ b/mysql-test/suite/parts/t/update_and_cache.test @@ -0,0 +1,12 @@ +--source include/have_partition.inc + +CREATE TABLE t1 (pk INT PRIMARY KEY, a INT); +INSERT INTO t1 VALUES (1,10),(2,20); + +CREATE TABLE t2 (b INT) PARTITION BY KEY (b) PARTITIONS 2; +INSERT INTO t2 VALUES (1),(2); + +DELETE t2 FROM t2 WHERE b BETWEEN 5 AND 9; +UPDATE t2 JOIN t1 SET b = 5; + +DROP TABLE t1, t2; diff --git a/mysql-test/suite/perfschema/r/relaylog.result b/mysql-test/suite/perfschema/r/relaylog.result index a1d10265f4d..8acaf023b34 100644 --- a/mysql-test/suite/perfschema/r/relaylog.result +++ b/mysql-test/suite/perfschema/r/relaylog.result @@ -20,7 +20,7 @@ from performance_schema.file_summary_by_instance where file_name like "%master-%" order by file_name; FILE_NAME EVENT_NAME COUNT_READ COUNT_WRITE SUM_NUMBER_OF_BYTES_READ SUM_NUMBER_OF_BYTES_WRITE master-bin.000001 wait/io/file/sql/binlog MANY MANY MANY MANY -master-bin.index wait/io/file/sql/binlog_index NONE MANY NONE MANY +master-bin.index wait/io/file/sql/binlog_index MANY MANY MANY MANY select * from performance_schema.file_summary_by_instance where file_name like "%slave-%" order by file_name; FILE_NAME EVENT_NAME COUNT_READ COUNT_WRITE SUM_NUMBER_OF_BYTES_READ SUM_NUMBER_OF_BYTES_WRITE @@ -36,7 +36,7 @@ from performance_schema.file_summary_by_instance where event_name like "%binlog%" order by file_name; FILE_NAME EVENT_NAME COUNT_READ COUNT_WRITE SUM_NUMBER_OF_BYTES_READ SUM_NUMBER_OF_BYTES_WRITE master-bin.000001 wait/io/file/sql/binlog MANY MANY MANY MANY -master-bin.index wait/io/file/sql/binlog_index NONE MANY NONE MANY +master-bin.index wait/io/file/sql/binlog_index MANY MANY MANY MANY select EVENT_NAME, if (count_read > 0, "MANY", "NONE") as COUNT_READ, @@ -47,7 +47,7 @@ from performance_schema.file_summary_by_event_name where event_name like "%binlog%" order by event_name; EVENT_NAME COUNT_READ COUNT_WRITE SUM_NUMBER_OF_BYTES_READ SUM_NUMBER_OF_BYTES_WRITE wait/io/file/sql/binlog MANY MANY MANY MANY -wait/io/file/sql/binlog_index NONE MANY NONE MANY +wait/io/file/sql/binlog_index MANY MANY MANY MANY select EVENT_NAME, if (count_star > 0, "MANY", "NONE") as COUNT_STAR @@ -95,7 +95,7 @@ where file_name like "%slave-%" order by file_name; FILE_NAME EVENT_NAME COUNT_READ COUNT_WRITE SUM_NUMBER_OF_BYTES_READ SUM_NUMBER_OF_BYTES_WRITE slave-bin.000001 wait/io/file/sql/binlog MANY MANY MANY MANY -slave-bin.index wait/io/file/sql/binlog_index NONE MANY NONE MANY +slave-bin.index wait/io/file/sql/binlog_index MANY MANY MANY MANY slave-relay-bin.000001 wait/io/file/sql/relaylog MANY MANY MANY MANY slave-relay-bin.000002 wait/io/file/sql/relaylog MANY MANY MANY MANY slave-relay-bin.index wait/io/file/sql/relaylog_index MANY MANY MANY MANY @@ -111,7 +111,7 @@ from performance_schema.file_summary_by_instance where event_name like "%binlog%" order by file_name; FILE_NAME EVENT_NAME COUNT_READ COUNT_WRITE SUM_NUMBER_OF_BYTES_READ SUM_NUMBER_OF_BYTES_WRITE slave-bin.000001 wait/io/file/sql/binlog MANY MANY MANY MANY -slave-bin.index wait/io/file/sql/binlog_index NONE MANY NONE MANY +slave-bin.index wait/io/file/sql/binlog_index MANY MANY MANY MANY select EVENT_NAME, if (count_read > 0, "MANY", "NONE") as COUNT_READ, @@ -122,7 +122,7 @@ from performance_schema.file_summary_by_event_name where event_name like "%binlog%" order by event_name; EVENT_NAME COUNT_READ COUNT_WRITE SUM_NUMBER_OF_BYTES_READ SUM_NUMBER_OF_BYTES_WRITE wait/io/file/sql/binlog MANY MANY MANY MANY -wait/io/file/sql/binlog_index NONE MANY NONE MANY +wait/io/file/sql/binlog_index MANY MANY MANY MANY select EVENT_NAME, if (count_star > 0, "MANY", "NONE") as COUNT_STAR diff --git a/mysql-test/suite/rpl/r/rpl_15919.result b/mysql-test/suite/rpl/r/rpl_15919.result new file mode 100644 index 00000000000..0c176624cf7 --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_15919.result @@ -0,0 +1,16 @@ +include/master-slave.inc +[connection master] +create table RPL(a int); +insert into RPL values(1); +select * from rpl; +a +1 +insert into RPL values(3); +insert into rpl values(4); +select * from rpl; +a +1 +3 +4 +drop table RPL; +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/r/rpl_lcase_tblnames_rewrite_db.result b/mysql-test/suite/rpl/r/rpl_lcase_tblnames_rewrite_db.result new file mode 100644 index 00000000000..3feb01d92fc --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_lcase_tblnames_rewrite_db.result @@ -0,0 +1,33 @@ +include/master-slave.inc +[connection master] +SET SQL_LOG_BIN=0; +CREATE DATABASE B37656; +SET SQL_LOG_BIN=1; +CREATE DATABASE BUG37656; +### action: show that database on slave is created in lowercase +SHOW DATABASES LIKE '%37656'; +Database (%37656) +bug37656 +USE B37656; +CREATE TABLE T1 (a int); +INSERT INTO T1 VALUES (1); +### assertion: master contains capitalized case table +SHOW TABLES; +Tables_in_B37656 +T1 +use bug37656; +### assertion: slave contains lowered case table +SHOW TABLES; +Tables_in_bug37656 +t1 +### assertion: master and slave tables do not differ +include/diff_tables.inc [master:B37656.T1, slave:bug37656.t1] +SET SQL_LOG_BIN=0; +DROP DATABASE B37656; +SET SQL_LOG_BIN=1; +SHOW DATABASES LIKE '%37656'; +Database (%37656) +DROP DATABASE BUG37656; +SHOW DATABASES LIKE '%37656'; +Database (%37656) +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/r/rpl_row_lcase_tblnames.result b/mysql-test/suite/rpl/r/rpl_row_lcase_tblnames.result new file mode 100644 index 00000000000..d8d459f4dcc --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_row_lcase_tblnames.result @@ -0,0 +1,47 @@ +include/master-slave.inc +[connection master] +******** [ MASTER ] ******** +CREATE DATABASE BUG_37656; +use BUG_37656; +show databases like 'BUG_37656'; +Database (BUG_37656) +BUG_37656 +******** [ SLAVE ] ******** +show databases like 'bug_37656'; +Database (bug_37656) +bug_37656 +******** [ MASTER ] ******** +CREATE TABLE T1 (a int); +CREATE TABLE T2 (b int) ENGINE=InnoDB; +CREATE TABLE T3 (txt TEXT); +show tables; +Tables_in_BUG_37656 +T1 +T2 +T3 +******** [ SLAVE ] ******** +use bug_37656; +show tables; +Tables_in_bug_37656 +t2 +t3 +CREATE TABLE t1 (a INT); +******** [ MASTER ] ******** +use BUG_37656; +INSERT INTO T1 VALUES (1); +INSERT INTO T2 VALUES (1); +use test; +INSERT INTO BUG_37656.T1 VALUES (2); +INSERT INTO BUG_37656.T2 VALUES (2); +LOAD DATA INFILE '../../std_data/words.dat' INTO TABLE BUG_37656.T3; +******** [ SLAVE ] ******** +include/diff_tables.inc [master:BUG_37656.T2, slave:bug_37656.t2] +include/diff_tables.inc [master:BUG_37656.T3, slave:bug_37656.t3] +******** [ MASTER ] ******** +DROP DATABASE BUG_37656; +CREATE DATABASE B50653; +USE B50653; +CREATE PROCEDURE b50653_proc() BEGIN SELECT 1; END; +DROP PROCEDURE b50653_proc; +DROP DATABASE B50653; +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/r/rpl_row_spatial.result b/mysql-test/suite/rpl/r/rpl_row_spatial.result new file mode 100644 index 00000000000..8f546fc479e --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_row_spatial.result @@ -0,0 +1,14 @@ +include/master-slave.inc +[connection master] +CREATE TABLE t1 (g POINT NOT NULL, SPATIAL INDEX(g)); +INSERT INTO t1 VALUES (ST_GEOMFROMTEXT('Point(1 1)')); +INSERT INTO t1 VALUES (ST_GEOMFROMTEXT('Point(2 1)')); +INSERT INTO t1 VALUES (ST_GEOMFROMTEXT('Point(1 2)')); +INSERT INTO t1 VALUES (ST_GEOMFROMTEXT('Point(2 2)')); +DELETE FROM t1 where MBREqual(g, ST_GEOMFROMTEXT('Point(1 2)')); +select count(*) from t1; +count(*) +3 +DELETE FROM t1; +drop table t1; +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/r/rpl_stm_lcase_tblnames.result b/mysql-test/suite/rpl/r/rpl_stm_lcase_tblnames.result new file mode 100644 index 00000000000..0d60f52a983 --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_stm_lcase_tblnames.result @@ -0,0 +1,44 @@ +include/master-slave.inc +[connection master] +******** [ MASTER ] ******** +CREATE DATABASE BUG_37656; +use BUG_37656; +show databases like 'BUG_37656'; +Database (BUG_37656) +BUG_37656 +******** [ SLAVE ] ******** +show databases like 'bug_37656'; +Database (bug_37656) +bug_37656 +******** [ MASTER ] ******** +CREATE TABLE T1 (a int); +CREATE TABLE T2 (b int) ENGINE=InnoDB; +CREATE TABLE T3 (txt TEXT); +show tables; +Tables_in_BUG_37656 +T1 +T2 +T3 +******** [ SLAVE ] ******** +use bug_37656; +show tables; +Tables_in_bug_37656 +t2 +t3 +CREATE TABLE t1 (a INT); +******** [ MASTER ] ******** +use BUG_37656; +INSERT INTO T1 VALUES (1); +INSERT INTO T2 VALUES (1); +LOAD DATA INFILE '../../std_data/words.dat' INTO TABLE BUG_37656.T3; +******** [ SLAVE ] ******** +include/diff_tables.inc [master:BUG_37656.T2, slave:bug_37656.t2] +include/diff_tables.inc [master:BUG_37656.T3, slave:bug_37656.t3] +******** [ MASTER ] ******** +DROP DATABASE BUG_37656; +CREATE DATABASE B50653; +USE B50653; +CREATE PROCEDURE b50653_proc() BEGIN SELECT 1; END; +DROP PROCEDURE b50653_proc; +DROP DATABASE B50653; +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/rpl_15919-master.opt b/mysql-test/suite/rpl/t/rpl_15919-master.opt new file mode 100644 index 00000000000..9b27aef9bf8 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_15919-master.opt @@ -0,0 +1 @@ +--lower_case_table_names=0 diff --git a/mysql-test/suite/rpl/t/rpl_15919-slave.opt b/mysql-test/suite/rpl/t/rpl_15919-slave.opt new file mode 100644 index 00000000000..62ab6dad1e0 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_15919-slave.opt @@ -0,0 +1 @@ +--lower_case_table_names=1 diff --git a/mysql-test/suite/rpl/t/rpl_15919.test b/mysql-test/suite/rpl/t/rpl_15919.test new file mode 100644 index 00000000000..0462f7fd067 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_15919.test @@ -0,0 +1,16 @@ +--source include/master-slave.inc +--source include/have_innodb.inc +--connection master +create table RPL(a int); +insert into RPL values(1); + +--sync_slave_with_master +select * from rpl; +insert into RPL values(3); +insert into rpl values(4); +select * from rpl; + +--connection master +drop table RPL; + +--source include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/rpl_lcase_tblnames_rewrite_db-slave.opt b/mysql-test/suite/rpl/t/rpl_lcase_tblnames_rewrite_db-slave.opt new file mode 100644 index 00000000000..0031a57a693 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_lcase_tblnames_rewrite_db-slave.opt @@ -0,0 +1 @@ +--lower-case-table-names=1 "--replicate-rewrite-db=b37656->bug37656" diff --git a/mysql-test/suite/rpl/t/rpl_lcase_tblnames_rewrite_db.test b/mysql-test/suite/rpl/t/rpl_lcase_tblnames_rewrite_db.test new file mode 100644 index 00000000000..9c804d8206a --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_lcase_tblnames_rewrite_db.test @@ -0,0 +1,60 @@ +# BUG#37656 +# +# DESCRIPTION +# +# +# This test case is tests whether replication works properly when +# slave is configured with --lower-case-table-names=1 and replication +# rewrite rules are in effect. +# +# It checks four issues: +# +# (i) master contains capitalized table name +# +# (ii) slave contains lowered case table name +# +# (iii) master and slave tables do not differ +# +-- source include/master-slave.inc +-- source include/not_windows.inc + +SET SQL_LOG_BIN=0; +CREATE DATABASE B37656; +SET SQL_LOG_BIN=1; + +-- connection slave +CREATE DATABASE BUG37656; + +-- echo ### action: show that database on slave is created in lowercase +SHOW DATABASES LIKE '%37656'; + +-- connection master +USE B37656; +CREATE TABLE T1 (a int); +INSERT INTO T1 VALUES (1); + +-- echo ### assertion: master contains capitalized case table +SHOW TABLES; + +-- sync_slave_with_master + +use bug37656; + +-- echo ### assertion: slave contains lowered case table +SHOW TABLES; + +-- echo ### assertion: master and slave tables do not differ +let $diff_tables= master:B37656.T1, slave:bug37656.t1; + +-- source include/diff_tables.inc + +-- connection master +SET SQL_LOG_BIN=0; +DROP DATABASE B37656; +SET SQL_LOG_BIN=1; +SHOW DATABASES LIKE '%37656'; + +-- connection slave +DROP DATABASE BUG37656; +SHOW DATABASES LIKE '%37656'; +--source include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/rpl_row_lcase_tblnames-slave.opt b/mysql-test/suite/rpl/t/rpl_row_lcase_tblnames-slave.opt new file mode 100644 index 00000000000..7624b013dcd --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_row_lcase_tblnames-slave.opt @@ -0,0 +1 @@ +--replicate-do-db=bug_37656 --replicate-ignore-table=buG_37656.T1 --replicate-do-table=bUg_37656.T2 --replicate-do-table=bUg_37656.T3 --lower-case-table-names=1 diff --git a/mysql-test/suite/rpl/t/rpl_row_lcase_tblnames.test b/mysql-test/suite/rpl/t/rpl_row_lcase_tblnames.test new file mode 100644 index 00000000000..78a66a7df32 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_row_lcase_tblnames.test @@ -0,0 +1,12 @@ +# BUG#37656 +# +# For details look into extra/rpl_tests/rpl_lower_case_table_names.test +# + +-- source include/master-slave.inc +-- source include/have_innodb.inc +-- source include/not_windows.inc +-- source include/have_binlog_format_row.inc + +-- let $engine=InnoDB +-- source extra/rpl_tests/rpl_lower_case_table_names.test diff --git a/mysql-test/suite/rpl/t/rpl_row_spatial.test b/mysql-test/suite/rpl/t/rpl_row_spatial.test new file mode 100644 index 00000000000..00c3dd7c54d --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_row_spatial.test @@ -0,0 +1,17 @@ +--source include/have_binlog_format_row.inc +--source include/master-slave.inc + +CREATE TABLE t1 (g POINT NOT NULL, SPATIAL INDEX(g)); +INSERT INTO t1 VALUES (ST_GEOMFROMTEXT('Point(1 1)')); +INSERT INTO t1 VALUES (ST_GEOMFROMTEXT('Point(2 1)')); +INSERT INTO t1 VALUES (ST_GEOMFROMTEXT('Point(1 2)')); +INSERT INTO t1 VALUES (ST_GEOMFROMTEXT('Point(2 2)')); +DELETE FROM t1 where MBREqual(g, ST_GEOMFROMTEXT('Point(1 2)')); + +--sync_slave_with_master +select count(*) from t1; + +--connection master +DELETE FROM t1; +drop table t1; +--source include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/rpl_stm_lcase_tblnames-slave.opt b/mysql-test/suite/rpl/t/rpl_stm_lcase_tblnames-slave.opt new file mode 100644 index 00000000000..8be29bbe976 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_stm_lcase_tblnames-slave.opt @@ -0,0 +1 @@ +--replicate-do-db=bug_37656 --replicate-ignore-table=bug_37656.t1 --replicate-do-table=bug_37656.t2 --replicate-do-table=bug_37656.t3 --lower-case-table-names=1 diff --git a/mysql-test/suite/rpl/t/rpl_stm_lcase_tblnames.test b/mysql-test/suite/rpl/t/rpl_stm_lcase_tblnames.test new file mode 100644 index 00000000000..00df8e9d385 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_stm_lcase_tblnames.test @@ -0,0 +1,12 @@ +# BUG#37656 +# +# For details look into extra/rpl_tests/rpl_lower_case_table_names.test +# + +-- source include/master-slave.inc +-- source include/have_innodb.inc +-- source include/not_windows.inc +-- source include/have_binlog_format_mixed_or_statement.inc + +-- let $engine=InnoDB +-- source extra/rpl_tests/rpl_lower_case_table_names.test diff --git a/mysql-test/t/alter_table.test b/mysql-test/t/alter_table.test index ee9616e233d..28dfbaa27ec 100644 --- a/mysql-test/t/alter_table.test +++ b/mysql-test/t/alter_table.test @@ -1272,3 +1272,53 @@ ALTER COLUMN `consultant_id` DROP DEFAULT, MODIFY COLUMN `consultant_id` BIGINT; SHOW CREATE TABLE t1; DROP TABLE t1; + +--echo # +--echo # BUG#27788685: NO WARNING WHEN TRUNCATING A STRING WITH DATA LOSS +--echo # + +SET GLOBAL max_allowed_packet=17825792; + +--connect(con1, localhost, root,,) +CREATE TABLE t1 (t1_fld1 TEXT); +CREATE TABLE t2 (t2_fld1 MEDIUMTEXT); +CREATE TABLE t3 (t3_fld1 LONGTEXT); + +INSERT INTO t1 VALUES (REPEAT('a',300)); +INSERT INTO t2 VALUES (REPEAT('b',65680)); +INSERT INTO t3 VALUES (REPEAT('c',16777300)); + +SELECT LENGTH(t1_fld1) FROM t1; +SELECT LENGTH(t2_fld1) FROM t2; +SELECT LENGTH(t3_fld1) FROM t3; + +--echo # With strict mode +SET SQL_MODE='STRICT_ALL_TABLES'; + +--error ER_DATA_TOO_LONG +ALTER TABLE t1 CHANGE `t1_fld1` `my_t1_fld1` TINYTEXT; +--error ER_DATA_TOO_LONG +ALTER TABLE t2 CHANGE `t2_fld1` `my_t2_fld1` TEXT; +--error ER_DATA_TOO_LONG +ALTER TABLE t3 CHANGE `t3_fld1` `my_t3_fld1` MEDIUMTEXT; + +--echo # With non-strict mode +SET SQL_MODE=''; + +ALTER TABLE t1 CHANGE `t1_fld1` `my_t1_fld1` TINYTEXT; +ALTER TABLE t2 CHANGE `t2_fld1` `my_t2_fld1` TEXT; +ALTER TABLE t3 CHANGE `t3_fld1` `my_t3_fld1` MEDIUMTEXT; + +SELECT LENGTH(my_t1_fld1) FROM t1; +SELECT LENGTH(my_t2_fld1) FROM t2; +SELECT LENGTH(my_t3_fld1) FROM t3; + +# Cleanup +--disconnect con1 +--source include/wait_until_disconnected.inc + +--connection default +DROP TABLE t1, t2, t3; + +SET SQL_MODE=default; +SET GLOBAL max_allowed_packet=default; diff --git a/mysql-test/t/derived_opt.test b/mysql-test/t/derived_opt.test index 7f19553e4e5..aab95f69f26 100644 --- a/mysql-test/t/derived_opt.test +++ b/mysql-test/t/derived_opt.test @@ -363,5 +363,43 @@ set join_cache_level=@tmp_jcl; set optimizer_switch=@tmp_os; drop table t1, t2; +--echo # +--echo # Bug mdev-17382: equi-join of derived table with join_cache_level=4 +--echo # + +CREATE TABLE t1 ( + id int NOT NULL, + amount decimal DEFAULT NULL, +PRIMARY KEY (id) +); + +CREATE TABLE t2 ( + id int NOT NULL, + name varchar(50) DEFAULT NULL, +PRIMARY KEY (id) +); + +INSERT INTO t1 VALUES +(1, 10.0000), (2, 20.0000), (3, 30.0000), (4, 40.0000), +(5, NULL), (6, NULL), (7, 70.0000), (8, 80.0000); + +INSERT INTO t2 VALUES +(1,'A'), (2,'B'), (3,'C'), (4,'D'), (5, NULL), (6, NULL), +(7,'E'), (8,'F'), (9,'G'), (10,'H'), (11, NULL), (12, NULL); + +set join_cache_level=4; + +EXPLAIN +SELECT t2.id,t2.name,t.total_amt + FROM t2 + LEFT JOIN + (SELECT id, sum(amount) total_amt FROM t1 GROUP BY id) AS t + ON t2.id=t.id + WHERE t2.id < 3; + +set join_cache_level=default; + +DROP TABLE t1,t2; + # The following command must be the last one the file set optimizer_switch=@exit_optimizer_switch; diff --git a/mysql-test/t/func_isnull.test b/mysql-test/t/func_isnull.test index 4c59fa3cbe8..7d1a7e83a1a 100644 --- a/mysql-test/t/func_isnull.test +++ b/mysql-test/t/func_isnull.test @@ -83,6 +83,22 @@ SELECT * FROM t2 LEFT JOIN v1 ON t2.d1=v1.d1 WHERE v1.d1 IS NULL; DROP VIEW v1; DROP TABLE t1,t2; + +--echo # +--echo # MDEV-15475: Assertion `!table || (!table->read_set || +--echo # bitmap_is_set(table->read_set, field_index))' +--echo # failed on EXPLAIN EXTENDED with constant table and view +--echo # + +CREATE TABLE t1 (pk INT PRIMARY KEY) ENGINE=MyISAM; +CREATE VIEW v1 AS SELECT * FROM t1; +INSERT INTO t1 VALUES (1); +EXPLAIN EXTENDED SELECT ISNULL(pk) FROM v1; +EXPLAIN EXTENDED SELECT IFNULL(pk,0) FROM v1; +# Cleanup +DROP VIEW v1; +DROP TABLE t1; + --echo # --echo # End of 5.5 tests --echo # diff --git a/mysql-test/t/func_time.test b/mysql-test/t/func_time.test index 8323bd30d2c..4497a4d9668 100644 --- a/mysql-test/t/func_time.test +++ b/mysql-test/t/func_time.test @@ -1629,6 +1629,59 @@ DO TO_DAYS(SEC_TO_TIME(MAKEDATE('',RAND(~(''))))); SELECT TO_DAYS(SEC_TO_TIME(MAKEDATE(0,RAND(~0)))); SELECT SEC_TO_TIME(MAKEDATE(0,RAND(~0))); +# +# MDEV-16810 AddressSanitizer: stack-buffer-overflow in int10_to_str +# +SELECT PERIOD_DIFF(2018, AES_ENCRYPT('Rae Bareli', 'Rae Bareli')); + + +--echo # +--echo # MDEV-17249 MAKETIME(-1e50,0,0) returns a wrong result +--echo # + +--vertical_results +SELECT + MAKETIME(1e10,0,0), + MAKETIME(-1e10,0,0), + MAKETIME(1e50,0,0), + MAKETIME(-1e50,0,0), + MAKETIME(COALESCE(1e50),0,0), + MAKETIME(COALESCE(-1e50),0,0); +--horizontal_results + +CREATE TABLE t1 (a FLOAT); +INSERT INTO t1 VALUES (1e30),(-1e30); +SELECT MAKETIME(a,0,0) FROM t1; +DROP TABLE t1; + +--echo # +--echo # MDEV-17244 MAKETIME(900,0,0.111) returns a wrong result +--echo # + +SELECT MAKETIME(900,0,0); +SELECT MAKETIME(900,0,0.1); +SELECT MAKETIME(900,0,0.11); +SELECT MAKETIME(900,0,0.111); +SELECT MAKETIME(900,0,0.1111); +SELECT MAKETIME(900,0,0.11111); +SELECT MAKETIME(900,0,0.111111); +SELECT MAKETIME(900,0,0.1111111); +SELECT MAKETIME(900,0,0.11111111); +SELECT MAKETIME(900,0,0.111111111); +SELECT MAKETIME(900,0,EXP(1)); + +SELECT MAKETIME(-900,0,0); +SELECT MAKETIME(-900,0,0.1); +SELECT MAKETIME(-900,0,0.11); +SELECT MAKETIME(-900,0,0.111); +SELECT MAKETIME(-900,0,0.1111); +SELECT MAKETIME(-900,0,0.11111); +SELECT MAKETIME(-900,0,0.111111); +SELECT MAKETIME(-900,0,0.1111111); +SELECT MAKETIME(-900,0,0.11111111); +SELECT MAKETIME(-900,0,0.111111111); +SELECT MAKETIME(-900,0,EXP(1)); + --echo # --echo # End of 5.5 tests diff --git a/mysql-test/t/grant.test b/mysql-test/t/grant.test index 5de3328944a..220cc0a248c 100644 --- a/mysql-test/t/grant.test +++ b/mysql-test/t/grant.test @@ -2244,5 +2244,26 @@ DROP USER foo@'127.0.0.1'; --echo # End of Bug#12766319 +# +# Bug#27230925: HANDLE_FATAL_SIGNAL (SIG=11) IN SHOW_ROUTINE_GRANTS +# +create user foo@localhost; +create database foodb; +grant create routine on foodb.* to foo@localhost; +connect con1,localhost,foo; +create procedure fooproc() select 'i am fooproc'; +show grants; +disconnect con1; +connection default; +rename table mysql.procs_priv to mysql.procs_priv1; +error ER_NO_SUCH_TABLE; +flush privileges; +show grants for foo@localhost; +rename table mysql.procs_priv1 to mysql.procs_priv; +drop user foo@localhost; +drop procedure fooproc; +drop database foodb; + + # Wait till we reached the initial number of concurrent sessions --source include/wait_until_count_sessions.inc diff --git a/mysql-test/t/join.test b/mysql-test/t/join.test index feafac57a7e..5a29fe72049 100644 --- a/mysql-test/t/join.test +++ b/mysql-test/t/join.test @@ -1185,12 +1185,13 @@ CREATE TABLE t (f INT); # # The following shouldn't fail as the table is now matching the using # ---error ER_BAD_FIELD_ERROR CALL p; DROP TABLE t; CREATE TABLE t (i INT); --error ER_BAD_FIELD_ERROR CALL p; +--error ER_BAD_FIELD_ERROR +CALL p; DROP PROCEDURE p; DROP TABLE t; diff --git a/mysql-test/t/lowercase_fs_off.test b/mysql-test/t/lowercase_fs_off.test index 1be0351f9bc..8729efde02c 100644 --- a/mysql-test/t/lowercase_fs_off.test +++ b/mysql-test/t/lowercase_fs_off.test @@ -102,3 +102,13 @@ CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET new.a= 1; RENAME TABLE t1 TO T1; ALTER TABLE T1 RENAME t1; DROP TABLE t1; + +# +# MDEV-13912 mysql_upgrade: case (in)sensitivity for stored procedures +# +create database TEST; +create procedure TEST.pr() begin end; +create procedure test.pr() begin end; +--exec $MYSQL_UPGRADE --force 2>&1 +drop procedure test.pr; +drop database TEST; diff --git a/mysql-test/t/sp-security.test b/mysql-test/t/sp-security.test index ca4e6b04f13..3769bb3b4bf 100644 --- a/mysql-test/t/sp-security.test +++ b/mysql-test/t/sp-security.test @@ -995,7 +995,24 @@ disconnect con2; DROP USER user2@localhost; DROP DATABASE db1; +# +# Bug#27407480: AUTOMATIC_SP_PRIVILEGES REQUIRES NEED THE INSERT PRIVILEGES FOR MYSQL.USER TABLE +# +create user foo@local_ost; +create user foo@`local\_ost` identified by 'nevermore'; +create database foodb; +grant create routine on foodb.* to foo@local_ost; +connect con1,localhost,foo; +select user(), current_user(); +show grants; +create procedure fooproc() select 'i am fooproc'; +show grants; +disconnect con1; +connection default; +drop user foo@local_ost; +drop user foo@`local\_ost`; +drop procedure fooproc; +drop database foodb; # Wait till all disconnects are completed --source include/wait_until_count_sessions.inc - diff --git a/mysql-test/t/sp.test b/mysql-test/t/sp.test index 0e42bf3c831..cb93cd31442 100644 --- a/mysql-test/t/sp.test +++ b/mysql-test/t/sp.test @@ -9353,4 +9353,25 @@ where 1=1; drop function if exists f1; drop table t1,t2; +--echo # +--echo # MDEV-16957: Server crashes in Field_iterator_natural_join::next +--echo # upon 2nd execution of SP +--echo # + +CREATE TABLE t1 (a INT, b VARCHAR(32)); +CREATE PROCEDURE sp() SELECT * FROM t1 AS t1x JOIN t1 AS t1y USING (c); +--error ER_BAD_FIELD_ERROR +CALL sp; +--error ER_BAD_FIELD_ERROR +CALL sp; +--error ER_BAD_FIELD_ERROR +CALL sp; +alter table t1 add column c int; +CALL sp; + +# Cleanup +DROP PROCEDURE sp; +DROP TABLE t1; + + --echo # End of 5.5 test diff --git a/mysql-test/t/type_float.test b/mysql-test/t/type_float.test index b33c51566a0..fa45b1041d8 100644 --- a/mysql-test/t/type_float.test +++ b/mysql-test/t/type_float.test @@ -331,3 +331,32 @@ eval select concat((truncate((-1.7976931348623157E+307),(0x1e))), --echo # select format(truncate('1.7976931348623157E+308',-12),1,'fr_BE') as foo; + + +--echo # +--echo # MDEV-17249 MAKETIME(-1e50,0,0) returns a wrong result +--echo # + +SELECT LEFT('a',EXP(50)); +SELECT LEFT('a', COALESCE(1e30)); + +CREATE TABLE t1 (a FLOAT); +INSERT INTO t1 VALUES (1e30); +SELECT LEFT('a',a), LEFT('a',1e30) FROM t1; +DROP TABLE t1; + +PREPARE stmt FROM 'SELECT LEFT(111,?)'; +SET @a=1e30; +EXECUTE stmt USING @a; +DEALLOCATE PREPARE stmt; + +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(2),(3); +SELECT LEFT('a',(SELECT 1e30 FROM t1 LIMIT 1)); +DROP TABLE t1; + +CREATE TABLE t1 (a DOUBLE); +INSERT INTO t1 VALUES (1e30),(0); +SELECT LEFT('a', SUM(a)) FROM t1; +SELECT LEFT('a', AVG(a)) FROM t1; +DROP TABLE t1; |