diff options
Diffstat (limited to 'mysql-test')
39 files changed, 1155 insertions, 33 deletions
diff --git a/mysql-test/main/func_group.result b/mysql-test/main/func_group.result index 9311a556191..57f5744373e 100644 --- a/mysql-test/main/func_group.result +++ b/mysql-test/main/func_group.result @@ -1186,13 +1186,13 @@ i count(*) std(e1/e2) 3 4 0.00000000 select std(s1/s2) from bug22555; std(s1/s2) -0.21325764 +0.21328517 select std(o1/o2) from bug22555; std(o1/o2) 0.2132576358664934 select std(e1/e2) from bug22555; std(e1/e2) -0.21325764 +0.21328517 set @saved_div_precision_increment=@@div_precision_increment; set div_precision_increment=19; select i, count(*), std(s1/s2) from bug22555 group by i order by i; diff --git a/mysql-test/main/grant5.result b/mysql-test/main/grant5.result index 2cc1c11f7d8..972131a24b7 100644 --- a/mysql-test/main/grant5.result +++ b/mysql-test/main/grant5.result @@ -25,6 +25,19 @@ ERROR HY000: Table 'procs_priv' was not locked with LOCK TABLES REVOKE PROCESS ON *.* FROM u; ERROR HY000: Table 'db' was not locked with LOCK TABLES DROP TABLE t1; +create database mysqltest1; +use mysqltest1; +create table t1(id int); +insert t1 values(2); +create user u1@localhost; +grant select on mysqltest1.t1 to u1@localhost; +grant update on mysqltest1.* to u1@localhost; +connect u1, localhost, u1; +update mysqltest1.t1 set id=1 where id=2; +connection default; +disconnect u1; +drop user u1@localhost; +drop database mysqltest1; # # MDEV-20076: SHOW GRANTS does not quote role names properly # diff --git a/mysql-test/main/grant5.test b/mysql-test/main/grant5.test index 054b16c0a6e..e133108516e 100644 --- a/mysql-test/main/grant5.test +++ b/mysql-test/main/grant5.test @@ -34,6 +34,27 @@ REVOKE EXECUTE ON PROCEDURE sp FROM u; REVOKE PROCESS ON *.* FROM u; DROP TABLE t1; +# +# MDEV-23010 UPDATE privilege at Database and Table level fail to update with SELECT command denied to user +# +create database mysqltest1; +use mysqltest1; +create table t1(id int); +insert t1 values(2); +create user u1@localhost; +grant select on mysqltest1.t1 to u1@localhost; +grant update on mysqltest1.* to u1@localhost; +connect u1, localhost, u1; +update mysqltest1.t1 set id=1 where id=2; +connection default; +disconnect u1; +drop user u1@localhost; +drop database mysqltest1; + +# +# End of 10.1 tests +# + --echo # --echo # MDEV-20076: SHOW GRANTS does not quote role names properly --echo # diff --git a/mysql-test/main/lowercase_fs_off.result b/mysql-test/main/lowercase_fs_off.result index 6ff8c1b7f93..3e9aa7cc239 100644 --- a/mysql-test/main/lowercase_fs_off.result +++ b/mysql-test/main/lowercase_fs_off.result @@ -158,3 +158,13 @@ show triggers like '%T1%'; Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation drop table t1; set GLOBAL sql_mode=default; +# +# MDEV-19632 Replication aborts with ER_SLAVE_CONVERSION_FAILED upon CREATE ... SELECT in ORACLE mode +# +# Compatibility schema names respect the filesystem case sensitivity +CREATE TABLE t1 (a MARIADB_SCHEMA.date); +ERROR HY000: Unknown data type: 'MARIADB_SCHEMA.date' +CREATE TABLE t1 (a Mariadb_schema.date); +ERROR HY000: Unknown data type: 'Mariadb_schema.date' +CREATE TABLE t1 (a mariadb_schema.date); +DROP TABLE t1; diff --git a/mysql-test/main/lowercase_fs_off.test b/mysql-test/main/lowercase_fs_off.test index f828773ed06..e8e8dafe584 100644 --- a/mysql-test/main/lowercase_fs_off.test +++ b/mysql-test/main/lowercase_fs_off.test @@ -130,3 +130,18 @@ let $datadir= `select @@datadir`; remove_file $datadir/mysql_upgrade_info; set GLOBAL sql_mode=default; + + +--echo # +--echo # MDEV-19632 Replication aborts with ER_SLAVE_CONVERSION_FAILED upon CREATE ... SELECT in ORACLE mode +--echo # + +--echo # Compatibility schema names respect the filesystem case sensitivity + +--error ER_UNKNOWN_ERROR +CREATE TABLE t1 (a MARIADB_SCHEMA.date); +--error ER_UNKNOWN_ERROR +CREATE TABLE t1 (a Mariadb_schema.date); + +CREATE TABLE t1 (a mariadb_schema.date); +DROP TABLE t1; diff --git a/mysql-test/main/parser_precedence.result b/mysql-test/main/parser_precedence.result index 4330c8a2045..f23295bd61b 100644 --- a/mysql-test/main/parser_precedence.result +++ b/mysql-test/main/parser_precedence.result @@ -619,7 +619,7 @@ select 4 - 3 * 2, (4 - 3) * 2, 4 - (3 * 2); Testing that / is left associative select 15 / 5 / 3, (15 / 5) / 3, 15 / (5 / 3); 15 / 5 / 3 (15 / 5) / 3 15 / (5 / 3) -1.00000000 1.00000000 9.0000 +1.00000000 1.00000000 8.9998 Testing that / has precedence over | select 105 / 5 | 2, (105 / 5) | 2, 105 / (5 | 2); 105 / 5 | 2 (105 / 5) | 2 105 / (5 | 2) diff --git a/mysql-test/main/partition.result b/mysql-test/main/partition.result index ea12158862e..0fe60807308 100644 --- a/mysql-test/main/partition.result +++ b/mysql-test/main/partition.result @@ -2763,5 +2763,45 @@ SELECT 1 FROM t1 WHERE a XOR 'a'; 1 DROP TABLE t1; # +# Bug #25207522: INCORRECT ORDER-BY BEHAVIOR ON A PARTITIONED TABLE +# WITH A COMPOSITE PREFIX INDEX +# +create table t1(id int unsigned not null, +data varchar(2) default null, +key data_idx (data(1),id) +) default charset=utf8 +partition by range (id) ( +partition p10 values less than (10), +partition p20 values less than (20) +); +insert t1 values (6, 'ab'), (4, 'ab'), (5, 'ab'), (16, 'ab'), (14, 'ab'), (15, 'ab'), (5, 'ac'), (15, 'aa') ; +select id from t1 where data = 'ab' order by id; +id +4 +5 +6 +14 +15 +16 +drop table t1; +create table t1(id int unsigned not null, +data text default null, +key data_idx (data(1),id) +) default charset=utf8 +partition by range (id) ( +partition p10 values less than (10), +partition p20 values less than (20) +); +insert t1 values (6, 'ab'), (4, 'ab'), (5, 'ab'), (16, 'ab'), (14, 'ab'), (15, 'ab'), (5, 'ac'), (15, 'aa') ; +select id from t1 where data = 'ab' order by id; +id +4 +5 +6 +14 +15 +16 +drop table t1; +# # End of 10.1 tests # diff --git a/mysql-test/main/partition.test b/mysql-test/main/partition.test index 89db3e92162..0ac91611e43 100644 --- a/mysql-test/main/partition.test +++ b/mysql-test/main/partition.test @@ -2974,5 +2974,33 @@ SELECT 1 FROM t1 WHERE a XOR 'a'; DROP TABLE t1; --echo # +--echo # Bug #25207522: INCORRECT ORDER-BY BEHAVIOR ON A PARTITIONED TABLE +--echo # WITH A COMPOSITE PREFIX INDEX +--echo # +create table t1(id int unsigned not null, + data varchar(2) default null, + key data_idx (data(1),id) +) default charset=utf8 +partition by range (id) ( + partition p10 values less than (10), + partition p20 values less than (20) +); +insert t1 values (6, 'ab'), (4, 'ab'), (5, 'ab'), (16, 'ab'), (14, 'ab'), (15, 'ab'), (5, 'ac'), (15, 'aa') ; +select id from t1 where data = 'ab' order by id; +drop table t1; + +create table t1(id int unsigned not null, + data text default null, + key data_idx (data(1),id) +) default charset=utf8 +partition by range (id) ( + partition p10 values less than (10), + partition p20 values less than (20) +); +insert t1 values (6, 'ab'), (4, 'ab'), (5, 'ab'), (16, 'ab'), (14, 'ab'), (15, 'ab'), (5, 'ac'), (15, 'aa') ; +select id from t1 where data = 'ab' order by id; +drop table t1; + +--echo # --echo # End of 10.1 tests --echo # diff --git a/mysql-test/main/stat_tables_partition.result b/mysql-test/main/stat_tables_partition.result index 12ae2570272..2619026b231 100644 --- a/mysql-test/main/stat_tables_partition.result +++ b/mysql-test/main/stat_tables_partition.result @@ -9,5 +9,38 @@ ANALYZE TABLE t1; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK -SET use_stat_tables = DEFAULT; DROP TABLE t1; +# +# MDEV-21472: ALTER TABLE ... ANALYZE PARTITION ... with EITS reads and locks all rows +# +CREATE TABLE t1 ( +id int(11) auto_increment primary key, +c1 int(11) DEFAULT NULL +) PARTITION BY RANGE (id) ( +PARTITION p0 VALUES LESS THAN (4), +PARTITION p1 VALUES LESS THAN MAXVALUE +); +insert into t1(c1) values (1),(1),(1),(1), (1),(1),(1),(1); +insert into t1(c1) select c1 from t1; +insert into t1(c1) select c1 from t1; +select count(*) from t1; +count(*) +32 +select count(*) from t1 where id <4; +count(*) +3 +flush status; +set session use_stat_tables='preferably'; +# Must NOT show "Engine-independent statistics collected": +alter table t1 analyze partition p0; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +# Should not have Handler_read_rnd_next=34 +show session status like 'Handler_read_rnd%'; +Variable_name Value +Handler_read_rnd 0 +Handler_read_rnd_deleted 0 +Handler_read_rnd_next 34 +drop table t1; +SET use_stat_tables = DEFAULT; diff --git a/mysql-test/main/stat_tables_partition.test b/mysql-test/main/stat_tables_partition.test index 1316e5cca11..11b74818d82 100644 --- a/mysql-test/main/stat_tables_partition.test +++ b/mysql-test/main/stat_tables_partition.test @@ -11,7 +11,33 @@ CREATE TABLE t1 (pk int PRIMARY KEY, a bit(1), INDEX idx(a) INSERT INTO t1 VALUES (1,1),(2,0),(3,0),(4,1); ANALYZE TABLE t1; +DROP TABLE t1; -SET use_stat_tables = DEFAULT; +--echo # +--echo # MDEV-21472: ALTER TABLE ... ANALYZE PARTITION ... with EITS reads and locks all rows +--echo # +CREATE TABLE t1 ( + id int(11) auto_increment primary key, + c1 int(11) DEFAULT NULL +) PARTITION BY RANGE (id) ( + PARTITION p0 VALUES LESS THAN (4), + PARTITION p1 VALUES LESS THAN MAXVALUE +); -DROP TABLE t1; +insert into t1(c1) values (1),(1),(1),(1), (1),(1),(1),(1); +insert into t1(c1) select c1 from t1; +insert into t1(c1) select c1 from t1; + +select count(*) from t1; +select count(*) from t1 where id <4; +flush status; +set session use_stat_tables='preferably'; + +--echo # Must NOT show "Engine-independent statistics collected": +alter table t1 analyze partition p0; + +--echo # Should not have Handler_read_rnd_next=34 +show session status like 'Handler_read_rnd%'; +drop table t1; + +SET use_stat_tables = DEFAULT; diff --git a/mysql-test/main/subselect4.result b/mysql-test/main/subselect4.result index 34a484f3b18..8c8471512cf 100644 --- a/mysql-test/main/subselect4.result +++ b/mysql-test/main/subselect4.result @@ -2585,6 +2585,30 @@ e 2 o 6 DROP TABLE t1, t2; # +# MDEV-19232: Floating point precision / value comparison problem +# +CREATE TABLE t1 (region varchar(60), area decimal(10,0), population decimal(11,0)); +INSERT INTO t1 VALUES ('Central America and the Caribbean',91,11797); +INSERT INTO t1 VALUES ('Central America and the Caribbean',442,66422); +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch='subquery_cache=on'; +SELECT +population, area, population/area, +cast(population/area as DECIMAL(20,9)) FROM t1 LIMIT 1; +population area population/area cast(population/area as DECIMAL(20,9)) +11797 91 129.6374 129.637400000 +SELECT * FROM t1 A +WHERE population/area = (SELECT MAX(population/area) from t1 B where A.region = B.region); +region area population +Central America and the Caribbean 442 66422 +SET optimizer_switch='subquery_cache=off'; +SELECT * FROM t1 A +WHERE population/area = (SELECT MAX(population/area) from t1 B where A.region = B.region); +region area population +Central America and the Caribbean 442 66422 +SET @@optimizer_switch= @save_optimizer_switch; +DROP TABLE t1; +# # MDEV-22852: SIGSEGV in sortlength (optimized builds) # SET @save_optimizer_switch=@@optimizer_switch; diff --git a/mysql-test/main/subselect4.test b/mysql-test/main/subselect4.test index 36490c340e1..1ed79de4598 100644 --- a/mysql-test/main/subselect4.test +++ b/mysql-test/main/subselect4.test @@ -2116,6 +2116,32 @@ SELECT * FROM t1 where ( t1.l1 < ANY (SELECT MAX(t2.v1) FROM t2)); DROP TABLE t1, t2; --echo # +--echo # MDEV-19232: Floating point precision / value comparison problem +--echo # + +CREATE TABLE t1 (region varchar(60), area decimal(10,0), population decimal(11,0)); +INSERT INTO t1 VALUES ('Central America and the Caribbean',91,11797); +INSERT INTO t1 VALUES ('Central America and the Caribbean',442,66422); + +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch='subquery_cache=on'; + +SELECT +population, area, population/area, +cast(population/area as DECIMAL(20,9)) FROM t1 LIMIT 1; + +SELECT * FROM t1 A +WHERE population/area = (SELECT MAX(population/area) from t1 B where A.region = B.region); + +SET optimizer_switch='subquery_cache=off'; +SELECT * FROM t1 A +WHERE population/area = (SELECT MAX(population/area) from t1 B where A.region = B.region); + +SET @@optimizer_switch= @save_optimizer_switch; + +DROP TABLE t1; + +--echo # --echo # MDEV-22852: SIGSEGV in sortlength (optimized builds) --echo # diff --git a/mysql-test/main/subselect_exists2in.result b/mysql-test/main/subselect_exists2in.result index 576559c2e86..e8ef7081b09 100644 --- a/mysql-test/main/subselect_exists2in.result +++ b/mysql-test/main/subselect_exists2in.result @@ -975,4 +975,131 @@ id DROP PROCEDURE p1; DROP TABLE t1; # End of 10.0 tests +# +# MDEV-23221: A subquery causes crash +# +create table t1 ( +location_code varchar(10), +country_id varchar(10) +); +insert into t1 values ('HKG', 'HK'); +insert into t1 values ('NYC', 'US'); +insert into t1 values ('LAX', 'US'); +create table t2 ( +container_id varchar(10), +cntr_activity_type varchar(10), +cntr_dest varchar(10) +); +insert into t2 values ('AAAA1111', 'VSL', 'NYC'); +insert into t2 values ('AAAA1111', 'CUV', 'NYC'); +insert into t2 values ('BBBB2222', 'VSL', 'LAX'); +insert into t2 values ('BBBB2222', 'XYZ', 'LAX'); +# Must not crash or return an error: +select +(select country_id from t1 where location_code = cl1.cntr_dest) as dest_cntry, +(select +max(container_id) +from t2 as cl2 +where +cl2.container_id = cl1.container_id and +cl2.cntr_activity_type = 'CUV' and +exists (select location_code +from t1 +where +location_code = cl2.cntr_dest and +country_id = dest_cntry) +) as CUV +from +t2 cl1; +dest_cntry CUV +US AAAA1111 +US AAAA1111 +US NULL +US NULL +prepare s from "select +(select country_id from t1 where location_code = cl1.cntr_dest) as dest_cntry, +(select +max(container_id) +from t2 as cl2 +where +cl2.container_id = cl1.container_id and +cl2.cntr_activity_type = 'CUV' and +exists (select location_code +from t1 +where +location_code = cl2.cntr_dest and +country_id = dest_cntry) +) as CUV +from +t2 cl1"; +execute s; +dest_cntry CUV +US AAAA1111 +US AAAA1111 +US NULL +US NULL +execute s; +dest_cntry CUV +US AAAA1111 +US AAAA1111 +US NULL +US NULL +drop table t1,t2; +# +# MDEV-20557: SQL query with duplicate table aliases consistently crashes server +# (Just a testcase) +# +create table t1 (id int, id2 int); +create table t2 (id int, id2 int, a int); +create table t3 (id int); +create table t4 (id int); +select (select 1 from t1 where (exists +(select 1 from t2 +where t2.a = (select t4.id from t4 where t4.id = t3.id) and t2.id2 = t1.id2))) dt +from t3; +ERROR 42000: This version of MariaDB doesn't yet support 'SUBQUERY in ROW in left expression of IN/ALL/ANY' +drop table t1,t2,t3,t4; +# +# MDEV-21649: Crash when using nested EXISTS +# (Just a testcase) +# +CREATE TABLE t1 (id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id)); +CREATE TABLE t2 (id INT NOT NULL AUTO_INCREMENT, ip_id INT, PRIMARY KEY(id)); +CREATE TABLE t3 (id INT NOT NULL AUTO_INCREMENT, storage_method_id INT, storage_target_id INT, PRIMARY KEY(id)); +SELECT +W0.`id` +FROM +`t1` W0 +WHERE ( +EXISTS( +SELECT +V0.`id` + FROM +`t2` V0 +WHERE ( +EXISTS( +SELECT +U0.`id` + FROM +`t2` U0 +INNER JOIN `t3` U4 ON (U0.`id` = U4.`storage_target_id`) +WHERE ( +U0.`ip_id` = V0.`ip_id` + AND U4.`storage_method_id` = ( +SELECT +U5.`storage_method_id` + FROM +`t3` U5 +WHERE +U5.`storage_target_id` = V0.`id` + LIMIT +1 +) +) +) +) +) +); +id +drop table t1,t2,t3; set optimizer_switch=default; diff --git a/mysql-test/main/subselect_exists2in.test b/mysql-test/main/subselect_exists2in.test index 2a9947123d4..e27ce57038b 100644 --- a/mysql-test/main/subselect_exists2in.test +++ b/mysql-test/main/subselect_exists2in.test @@ -829,5 +829,117 @@ DROP TABLE t1; --echo # End of 10.0 tests +--echo # +--echo # MDEV-23221: A subquery causes crash +--echo # +create table t1 ( +location_code varchar(10), +country_id varchar(10) +); +insert into t1 values ('HKG', 'HK'); +insert into t1 values ('NYC', 'US'); +insert into t1 values ('LAX', 'US'); + +create table t2 ( +container_id varchar(10), +cntr_activity_type varchar(10), +cntr_dest varchar(10) +); +insert into t2 values ('AAAA1111', 'VSL', 'NYC'); +insert into t2 values ('AAAA1111', 'CUV', 'NYC'); +insert into t2 values ('BBBB2222', 'VSL', 'LAX'); +insert into t2 values ('BBBB2222', 'XYZ', 'LAX'); + +let $query= +select + (select country_id from t1 where location_code = cl1.cntr_dest) as dest_cntry, + (select + max(container_id) + from t2 as cl2 + where + cl2.container_id = cl1.container_id and + cl2.cntr_activity_type = 'CUV' and + exists (select location_code + from t1 + where + location_code = cl2.cntr_dest and + country_id = dest_cntry) + ) as CUV +from + t2 cl1; + +--echo # Must not crash or return an error: +eval $query; + +eval prepare s from "$query"; +execute s; +execute s; + +drop table t1,t2; + +--echo # +--echo # MDEV-20557: SQL query with duplicate table aliases consistently crashes server +--echo # (Just a testcase) +--echo # + +create table t1 (id int, id2 int); +create table t2 (id int, id2 int, a int); +create table t3 (id int); +create table t4 (id int); + +--error ER_NOT_SUPPORTED_YET +select (select 1 from t1 where (exists + (select 1 from t2 + where t2.a = (select t4.id from t4 where t4.id = t3.id) and t2.id2 = t1.id2))) dt +from t3; + +drop table t1,t2,t3,t4; + + +--echo # +--echo # MDEV-21649: Crash when using nested EXISTS +--echo # (Just a testcase) +--echo # +CREATE TABLE t1 (id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id)); +CREATE TABLE t2 (id INT NOT NULL AUTO_INCREMENT, ip_id INT, PRIMARY KEY(id)); +CREATE TABLE t3 (id INT NOT NULL AUTO_INCREMENT, storage_method_id INT, storage_target_id INT, PRIMARY KEY(id)); + +SELECT + W0.`id` +FROM + `t1` W0 +WHERE ( + EXISTS( + SELECT + V0.`id` + FROM + `t2` V0 + WHERE ( + EXISTS( + SELECT + U0.`id` + FROM + `t2` U0 + INNER JOIN `t3` U4 ON (U0.`id` = U4.`storage_target_id`) + WHERE ( + U0.`ip_id` = V0.`ip_id` + AND U4.`storage_method_id` = ( + SELECT + U5.`storage_method_id` + FROM + `t3` U5 + WHERE + U5.`storage_target_id` = V0.`id` + LIMIT + 1 + ) + ) + ) + ) + ) +); + +drop table t1,t2,t3; + #restore defaults set optimizer_switch=default; diff --git a/mysql-test/main/type_float.result b/mysql-test/main/type_float.result index 167c167ad45..2588481ff75 100644 --- a/mysql-test/main/type_float.result +++ b/mysql-test/main/type_float.result @@ -676,6 +676,27 @@ Warnings: Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 2010e0 DROP TABLE t1; # +# MDEV-23282 FLOAT(53,0) badly handles out-of-range values +# +CREATE OR REPLACE TABLE t1 (c1 FLOAT NOT NULL, c2 FLOAT NOT NULL); +INSERT IGNORE INTO t1 VALUES (1e+40, -1e+40); +Warnings: +Warning 1264 Out of range value for column 'c1' at row 1 +Warning 1264 Out of range value for column 'c2' at row 1 +SELECT c1, c2 FROM t1; +c1 c2 +3.40282e38 -3.40282e38 +DROP TABLE t1; +CREATE OR REPLACE TABLE t1 (c1 FLOAT(53,0) NOT NULL, c2 FLOAT(53,0) NOT NULL); +INSERT IGNORE INTO t1 VALUES (1e+40, -1e+40); +Warnings: +Warning 1264 Out of range value for column 'c1' at row 1 +Warning 1264 Out of range value for column 'c2' at row 1 +SELECT c1, c2 FROM t1; +c1 c2 +340282346638528860000000000000000000000 -340282346638528860000000000000000000000 +DROP TABLE t1; +# # End of 10.1 tests # # diff --git a/mysql-test/main/type_float.test b/mysql-test/main/type_float.test index 4665c945a76..ea1829bfdaa 100644 --- a/mysql-test/main/type_float.test +++ b/mysql-test/main/type_float.test @@ -486,6 +486,20 @@ DROP TABLE t1; --echo # +--echo # MDEV-23282 FLOAT(53,0) badly handles out-of-range values +--echo # + +CREATE OR REPLACE TABLE t1 (c1 FLOAT NOT NULL, c2 FLOAT NOT NULL); +INSERT IGNORE INTO t1 VALUES (1e+40, -1e+40); +SELECT c1, c2 FROM t1; +DROP TABLE t1; + +CREATE OR REPLACE TABLE t1 (c1 FLOAT(53,0) NOT NULL, c2 FLOAT(53,0) NOT NULL); +INSERT IGNORE INTO t1 VALUES (1e+40, -1e+40); +SELECT c1, c2 FROM t1; +DROP TABLE t1; + +--echo # --echo # End of 10.1 tests --echo # diff --git a/mysql-test/main/type_newdecimal.result b/mysql-test/main/type_newdecimal.result index d5fc6db107b..07ecef95e5d 100644 --- a/mysql-test/main/type_newdecimal.result +++ b/mysql-test/main/type_newdecimal.result @@ -1532,11 +1532,8 @@ select (1.20396873 * 0.89550000 * 0.68000000 * 1.08721696 * 0.99500000 * 1.01500000 * 1.01500000 * 0.99500000) 0.81298807395367312459230693948000000000 create table t1 as select 5.05 / 0.014; -Warnings: -Note 1265 Data truncated for column '5.05 / 0.014' at row 1 show warnings; Level Code Message -Note 1265 Data truncated for column '5.05 / 0.014' at row 1 show create table t1; Table Create Table t1 CREATE TABLE `t1` ( @@ -1651,8 +1648,6 @@ my_col 0.12345678912345678912345678912345678912 DROP TABLE t1; CREATE TABLE t1 SELECT 1 / .123456789123456789123456789123456789123456789123456789123456789123456789123456789 AS my_col; -Warnings: -Note 1265 Data truncated for column 'my_col' at row 1 DESCRIBE t1; Field Type Null Key Default Extra my_col decimal(65,4) YES NULL diff --git a/mysql-test/main/type_ranges.result b/mysql-test/main/type_ranges.result index 784a394d8b5..199c8a45c97 100644 --- a/mysql-test/main/type_ranges.result +++ b/mysql-test/main/type_ranges.result @@ -91,8 +91,6 @@ DROP INDEX test ON t1; insert into t1 values (10, 1,1,1,1,1,1,1,1,1,1,1,1,1,NULL,0,0,0,1,1,1,1,'one','one'); insert into t1 values (NULL,2,2,2,2,2,2,2,2,2,2,2,2,2,NULL,NULL,NULL,NULL,NULL,NULL,2,2,'two','two,one'); insert ignore into t1 values (0,1/3,3,3,3,3,3,3,3,3,3,3,3,3,NULL,'19970303','10:10:10','19970303101010','','','','3',3,3); -Warnings: -Warning 1265 Data truncated for column 'string' at row 1 insert ignore into t1 values (0,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,NULL,19970807,080706,19970403090807,-1,-1,-1,'-1',-1,-1); Warnings: Warning 1264 Out of range value for column 'utiny' at row 1 @@ -130,7 +128,7 @@ select auto,string,tiny,short,medium,long_int,longlong,real_float,real_double,ut auto string tiny short medium long_int longlong real_float real_double utiny ushort umedium ulong ulonglong mod(floor(time_stamp/1000000),1000000)-mod(curdate(),1000000) date_field time_field date_time blob_col tinyblob_col mediumblob_col longblob_col 10 1 1 1 1 1 1 1.0 1.0000 1 00001 1 1 1 0 0000-00-00 00:00:00 0000-00-00 00:00:00 1 1 1 1 11 2 2 2 2 2 2 2.0 2.0000 2 00002 2 2 2 0 NULL NULL NULL NULL NULL 2 2 -12 0.33333333 3 3 3 3 3 3.0 3.0000 3 00003 3 3 3 0 1997-03-03 10:10:10 1997-03-03 10:10:10 3 +12 0.3333 3 3 3 3 3 3.0 3.0000 3 00003 3 3 3 0 1997-03-03 10:10:10 1997-03-03 10:10:10 3 13 -1 -1 -1 -1 -1 -1 -1.0 -1.0000 0 00000 0 0 0 0 1997-08-07 08:07:06 1997-04-03 09:08:07 -1 -1 -1 -1 14 -429496729 -128 -32768 -8388608 -2147483648 -4294967295 -4294967296.0 -4294967295.0000 0 00000 0 0 0 0 0000-00-00 00:00:00 0000-00-00 00:00:00 -4294967295 -4294967295 -4294967295 -4294967295 15 4294967295 127 32767 8388607 2147483647 4294967295 4294967296.0 4294967295.0000 255 65535 16777215 4294967295 4294967295 0 0000-00-00 00:00:00 0000-00-00 00:00:00 4294967295 4294967295 4294967295 4294967295 @@ -182,7 +180,7 @@ Warning 1265 Data truncated for column 'new_field' at row 7 select * from t2; auto string mediumblob_col new_field 1 2 2 ne -2 0.33333333 ne +2 0.3333 ne 3 -1 -1 ne 4 -429496729 -4294967295 ne 5 4294967295 4294967295 ne diff --git a/mysql-test/mysql-test-run.pl b/mysql-test/mysql-test-run.pl index 4cedfa61cce..d6abda9eb36 100755 --- a/mysql-test/mysql-test-run.pl +++ b/mysql-test/mysql-test-run.pl @@ -177,6 +177,7 @@ my @DEFAULT_SUITES= qw( csv- compat/oracle- compat/mssql- + compat/maxdb- encryption- federated- funcs_1- @@ -687,8 +688,7 @@ sub run_test_server ($$$) { My::CoreDump->show($core_file, $exe_mysqld, $opt_parallel); # Limit number of core files saved - if ($opt_max_save_core > 0 && - $num_saved_cores >= $opt_max_save_core) + if ($num_saved_cores >= $opt_max_save_core) { mtr_report(" - deleting it, already saved", "$opt_max_save_core"); @@ -704,8 +704,7 @@ sub run_test_server ($$$) { }, $worker_savedir); - if ($opt_max_save_datadir > 0 && - $num_saved_datadir >= $opt_max_save_datadir) + if ($num_saved_datadir >= $opt_max_save_datadir) { mtr_report(" - skipping '$worker_savedir/'"); rmtree($worker_savedir); @@ -714,9 +713,9 @@ sub run_test_server ($$$) { { mtr_report(" - saving '$worker_savedir/' to '$savedir/'"); rename($worker_savedir, $savedir); + $num_saved_datadir++; } resfile_print_test(); - $num_saved_datadir++; $num_failed_test++ unless ($result->{retries} || $result->{exp_fail}); @@ -1273,6 +1272,17 @@ sub command_line_setup { report_option('verbose', $opt_verbose); } + # Negative values aren't meaningful on integer options + foreach(grep(/=i$/, keys %options)) + { + if (defined ${$options{$_}} && + do { no warnings "numeric"; int ${$options{$_}} < 0}) + { + my $v= (split /=/)[0]; + die("$v doesn't accept a negative value:"); + } + } + # Find the absolute path to the test directory $glob_mysql_test_dir= cwd(); if ($glob_mysql_test_dir =~ / /) @@ -6421,12 +6431,12 @@ Options for debugging the product test(s) max-save-core Limit the number of core files saved (to avoid filling up disks for heavily crashing server). Defaults to - $opt_max_save_core, set to 0 for no limit. Set - it's default with MTR_MAX_SAVE_CORE + $opt_max_save_core. Set its default with + MTR_MAX_SAVE_CORE max-save-datadir Limit the number of datadir saved (to avoid filling up disks for heavily crashing server). Defaults to - $opt_max_save_datadir, set to 0 for no limit. Set - it's default with MTR_MAX_SAVE_DATADIR + $opt_max_save_datadir. Set its default with + MTR_MAX_SAVE_DATADIR max-test-fail Limit the number of test failures before aborting the current test run. Defaults to $opt_max_test_fail, set to 0 for no limit. Set diff --git a/mysql-test/suite/compat/maxdb/rpl_mariadb_timestamp.result b/mysql-test/suite/compat/maxdb/rpl_mariadb_timestamp.result new file mode 100644 index 00000000000..7c2012945c9 --- /dev/null +++ b/mysql-test/suite/compat/maxdb/rpl_mariadb_timestamp.result @@ -0,0 +1,65 @@ +include/master-slave.inc +[connection master] +# +# MDEV-19632 Replication aborts with ER_SLAVE_CONVERSION_FAILED upon CREATE ... SELECT in ORACLE mode +# +SET timestamp=UNIX_TIMESTAMP('2001-01-01 10:00:00'); +SET sql_mode=DEFAULT; +CREATE TABLE t1 (a TIMESTAMP); +INSERT INTO t1 VALUES (NULL); +INSERT INTO t1 VALUES ('2001-01-01 10:20:30'); +SET sql_mode=MAXDB; +CREATE TABLE t2 SELECT * FROM t1; +SET timestamp=DEFAULT; +include/show_binlog_events.inc +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; CREATE TABLE t1 (a TIMESTAMP) +master-bin.000001 # Gtid # # BEGIN GTID #-#-# +master-bin.000001 # Annotate_rows # # INSERT INTO t1 VALUES (NULL) +master-bin.000001 # Table_map # # table_id: # (test.t1) +master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # COMMIT +master-bin.000001 # Gtid # # BEGIN GTID #-#-# +master-bin.000001 # Annotate_rows # # INSERT INTO t1 VALUES ('2001-01-01 10:20:30') +master-bin.000001 # Table_map # # table_id: # (test.t1) +master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # COMMIT +master-bin.000001 # Gtid # # BEGIN GTID #-#-# +master-bin.000001 # Query # # use `test`; CREATE TABLE "t2" ( + "a" mariadb_schema.timestamp NOT NULL DEFAULT current_timestamp() +) +master-bin.000001 # Annotate_rows # # CREATE TABLE t2 SELECT * FROM t1 +master-bin.000001 # Table_map # # table_id: # (test.t2) +master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # COMMIT +connection slave; +SELECT * FROM t1; +a +2001-01-01 10:00:00 +2001-01-01 10:20:30 +SET sql_mode=DEFAULT; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `a` timestamp NOT NULL DEFAULT current_timestamp() +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +SET sql_mode=MAXDB; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE "t1" ( + "a" mariadb_schema.timestamp NOT NULL DEFAULT current_timestamp() +) +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE "t2" ( + "a" mariadb_schema.timestamp NOT NULL DEFAULT current_timestamp() +) +connection master; +DROP TABLE t1, t2; +include/rpl_end.inc diff --git a/mysql-test/suite/compat/maxdb/rpl_mariadb_timestamp.test b/mysql-test/suite/compat/maxdb/rpl_mariadb_timestamp.test new file mode 100644 index 00000000000..50663229937 --- /dev/null +++ b/mysql-test/suite/compat/maxdb/rpl_mariadb_timestamp.test @@ -0,0 +1,34 @@ +--source include/have_binlog_format_row.inc +--source include/master-slave.inc + +--echo # +--echo # MDEV-19632 Replication aborts with ER_SLAVE_CONVERSION_FAILED upon CREATE ... SELECT in ORACLE mode +--echo # + +SET timestamp=UNIX_TIMESTAMP('2001-01-01 10:00:00'); +SET sql_mode=DEFAULT; +CREATE TABLE t1 (a TIMESTAMP); +INSERT INTO t1 VALUES (NULL); +INSERT INTO t1 VALUES ('2001-01-01 10:20:30'); +SET sql_mode=MAXDB; +CREATE TABLE t2 SELECT * FROM t1; +SET timestamp=DEFAULT; + +--let $binlog_file = LAST +source include/show_binlog_events.inc; + + +--sync_slave_with_master +SELECT * FROM t1; +SET sql_mode=DEFAULT; +SHOW CREATE TABLE t1; +SHOW CREATE TABLE t2; + +SET sql_mode=MAXDB; +SHOW CREATE TABLE t1; +SHOW CREATE TABLE t2; + +--connection master +DROP TABLE t1, t2; + +--source include/rpl_end.inc diff --git a/mysql-test/suite/compat/maxdb/type_timestamp.result b/mysql-test/suite/compat/maxdb/type_timestamp.result new file mode 100644 index 00000000000..355a4e28414 --- /dev/null +++ b/mysql-test/suite/compat/maxdb/type_timestamp.result @@ -0,0 +1,53 @@ +# +# MDEV-19632 Replication aborts with ER_SLAVE_CONVERSION_FAILED upon CREATE ... SELECT in ORACLE mode +# +SET sql_mode=DEFAULT; +CREATE TABLE t1 ( +def_timestamp TIMESTAMP, +mdb_timestamp mariadb_schema.TIMESTAMP, +ora_timestamp oracle_schema.TIMESTAMP, +max_timestamp maxdb_schema.TIMESTAMP +); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `def_timestamp` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), + `mdb_timestamp` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', + `ora_timestamp` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', + `max_timestamp` datetime DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +SET sql_mode=MAXDB; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE "t1" ( + "def_timestamp" mariadb_schema.timestamp NOT NULL DEFAULT current_timestamp(), + "mdb_timestamp" mariadb_schema.timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', + "ora_timestamp" mariadb_schema.timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', + "max_timestamp" datetime DEFAULT NULL +) +DROP TABLE t1; +SET sql_mode=MAXDB; +CREATE TABLE t1 ( +def_timestamp TIMESTAMP, +mdb_timestamp mariadb_schema.TIMESTAMP, +ora_timestamp oracle_schema.TIMESTAMP, +max_timestamp maxdb_schema.TIMESTAMP +); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE "t1" ( + "def_timestamp" datetime DEFAULT NULL, + "mdb_timestamp" mariadb_schema.timestamp NOT NULL DEFAULT current_timestamp(), + "ora_timestamp" mariadb_schema.timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', + "max_timestamp" datetime DEFAULT NULL +) +SET sql_mode=DEFAULT; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `def_timestamp` datetime DEFAULT NULL, + `mdb_timestamp` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), + `ora_timestamp` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', + `max_timestamp` datetime DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; diff --git a/mysql-test/suite/compat/maxdb/type_timestamp.test b/mysql-test/suite/compat/maxdb/type_timestamp.test new file mode 100644 index 00000000000..cd60ffc0795 --- /dev/null +++ b/mysql-test/suite/compat/maxdb/type_timestamp.test @@ -0,0 +1,29 @@ +--echo # +--echo # MDEV-19632 Replication aborts with ER_SLAVE_CONVERSION_FAILED upon CREATE ... SELECT in ORACLE mode +--echo # + + +SET sql_mode=DEFAULT; +CREATE TABLE t1 ( + def_timestamp TIMESTAMP, + mdb_timestamp mariadb_schema.TIMESTAMP, + ora_timestamp oracle_schema.TIMESTAMP, + max_timestamp maxdb_schema.TIMESTAMP +); +SHOW CREATE TABLE t1; +SET sql_mode=MAXDB; +SHOW CREATE TABLE t1; +DROP TABLE t1; + + +SET sql_mode=MAXDB; +CREATE TABLE t1 ( + def_timestamp TIMESTAMP, + mdb_timestamp mariadb_schema.TIMESTAMP, + ora_timestamp oracle_schema.TIMESTAMP, + max_timestamp maxdb_schema.TIMESTAMP +); +SHOW CREATE TABLE t1; +SET sql_mode=DEFAULT; +SHOW CREATE TABLE t1; +DROP TABLE t1; diff --git a/mysql-test/suite/compat/oracle/r/rpl_mariadb_date.result b/mysql-test/suite/compat/oracle/r/rpl_mariadb_date.result new file mode 100644 index 00000000000..9aca6fa3542 --- /dev/null +++ b/mysql-test/suite/compat/oracle/r/rpl_mariadb_date.result @@ -0,0 +1,86 @@ +include/master-slave.inc +[connection master] +SET SQL_MODE=DEFAULT; +CREATE TABLE t1 (a DATE); +INSERT INTO t1 VALUES (NULL); +INSERT INTO t1 VALUES ('2001-01-01'); +SET SQL_MODE= ORACLE; +CREATE TABLE t2 SELECT * FROM t1; +include/show_binlog_events.inc +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; CREATE TABLE t1 (a DATE) +master-bin.000001 # Gtid # # BEGIN GTID #-#-# +master-bin.000001 # Annotate_rows # # INSERT INTO t1 VALUES (NULL) +master-bin.000001 # Table_map # # table_id: # (test.t1) +master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # COMMIT +master-bin.000001 # Gtid # # BEGIN GTID #-#-# +master-bin.000001 # Annotate_rows # # INSERT INTO t1 VALUES ('2001-01-01') +master-bin.000001 # Table_map # # table_id: # (test.t1) +master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # COMMIT +master-bin.000001 # Gtid # # BEGIN GTID #-#-# +master-bin.000001 # Query # # use `test`; CREATE TABLE "t2" ( + "a" mariadb_schema.date DEFAULT NULL +) +master-bin.000001 # Annotate_rows # # CREATE TABLE t2 SELECT * FROM t1 +master-bin.000001 # Table_map # # table_id: # (test.t2) +master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # COMMIT +SET SQL_MODE= DEFAULT; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` date DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `a` date DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +SET SQL_MODE= ORACLE; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE "t1" ( + "a" mariadb_schema.date DEFAULT NULL +) +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE "t2" ( + "a" mariadb_schema.date DEFAULT NULL +) +connection slave; +SELECT * FROM t1; +a +NULL +2001-01-01 +SELECT * FROM t2; +a +NULL +2001-01-01 +SET SQL_MODE= DEFAULT; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` date DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `a` date DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +SET SQL_MODE= ORACLE; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE "t1" ( + "a" mariadb_schema.date DEFAULT NULL +) +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE "t2" ( + "a" mariadb_schema.date DEFAULT NULL +) +connection master; +DROP TABLE t1, t2; +include/rpl_end.inc diff --git a/mysql-test/suite/compat/oracle/r/type_date.result b/mysql-test/suite/compat/oracle/r/type_date.result index 0989fc593d2..40d2a834056 100644 --- a/mysql-test/suite/compat/oracle/r/type_date.result +++ b/mysql-test/suite/compat/oracle/r/type_date.result @@ -6,3 +6,153 @@ t1 CREATE TABLE "t1" ( "a" datetime DEFAULT NULL ) DROP TABLE t1; +# +# MDEV-19632 Replication aborts with ER_SLAVE_CONVERSION_FAILED upon CREATE ... SELECT in ORACLE mode +# +SET sql_mode=DEFAULT; +CREATE TABLE t1 (a unknown.DATE); +ERROR HY000: Unknown data type: 'unknown.date' +SET sql_mode=DEFAULT; +CREATE TABLE t1 ( +def_date DATE, +mdb_date mariadb_schema.DATE, +ora_date oracle_schema.DATE, +max_date maxdb_schema.DATE +); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `def_date` date DEFAULT NULL, + `mdb_date` date DEFAULT NULL, + `ora_date` datetime DEFAULT NULL, + `max_date` date DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +SET sql_mode=ORACLE; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE "t1" ( + "def_date" mariadb_schema.date DEFAULT NULL, + "mdb_date" mariadb_schema.date DEFAULT NULL, + "ora_date" datetime DEFAULT NULL, + "max_date" mariadb_schema.date DEFAULT NULL +) +DROP TABLE t1; +SET sql_mode=ORACLE; +CREATE TABLE t1 ( +def_date DATE, +mdb_date mariadb_schema.DATE, +ora_date oracle_schema.DATE, +max_date maxdb_schema.DATE +); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE "t1" ( + "def_date" datetime DEFAULT NULL, + "mdb_date" mariadb_schema.date DEFAULT NULL, + "ora_date" datetime DEFAULT NULL, + "max_date" mariadb_schema.date DEFAULT NULL +) +SET sql_mode=DEFAULT; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `def_date` datetime DEFAULT NULL, + `mdb_date` date DEFAULT NULL, + `ora_date` datetime DEFAULT NULL, + `max_date` date DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +# +# ALTER..MODIFY and ALTER..CHANGE understand qualifiers +# +SET sql_mode=DEFAULT; +CREATE TABLE t1 (a DATE); +INSERT INTO t1 VALUES ('2001-01-01'); +SET sql_mode=ORACLE; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE "t1" ( + "a" mariadb_schema.date DEFAULT NULL +) +SELECT * FROM t1; +a +2001-01-01 +ALTER TABLE t1 MODIFY a DATE; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE "t1" ( + "a" datetime DEFAULT NULL +) +SELECT * FROM t1; +a +2001-01-01 00:00:00 +ALTER TABLE t1 MODIFY a mariadb_schema.DATE; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE "t1" ( + "a" mariadb_schema.date DEFAULT NULL +) +SELECT * FROM t1; +a +2001-01-01 +ALTER TABLE t1 MODIFY a oracle_schema.DATE; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE "t1" ( + "a" datetime DEFAULT NULL +) +SELECT * FROM t1; +a +2001-01-01 00:00:00 +ALTER TABLE t1 CHANGE a b mariadb_schema.DATE; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE "t1" ( + "b" mariadb_schema.date DEFAULT NULL +) +SELECT * FROM t1; +b +2001-01-01 +ALTER TABLE t1 CHANGE b a oracle_schema.DATE; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE "t1" ( + "a" datetime DEFAULT NULL +) +SELECT * FROM t1; +a +2001-01-01 00:00:00 +DROP TABLE t1; +# +# Qualified syntax is not supported yet in SP +# See MDEV-23353 Qualified data types in SP +# +SET sql_mode=ORACLE; +CREATE FUNCTION f1() RETURN mariadb_schema.DATE AS +BEGIN +RETURN CURRENT_DATE; +END; +$$ +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'mariadb_schema.DATE AS +BEGIN +RETURN CURRENT_DATE; +END' at line 1 +CREATE PROCEDURE p1(a mariadb_schema.DATE) AS +BEGIN +NULL; +END; +$$ +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ') AS +BEGIN +NULL; +END' at line 1 +CREATE PROCEDURE p1() AS +a mariadb_schema.DATE; +BEGIN +NULL; +END; +$$ +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '; +BEGIN +NULL; +END' at line 2 diff --git a/mysql-test/suite/compat/oracle/t/rpl_mariadb_date.test b/mysql-test/suite/compat/oracle/t/rpl_mariadb_date.test new file mode 100644 index 00000000000..b2aff23333b --- /dev/null +++ b/mysql-test/suite/compat/oracle/t/rpl_mariadb_date.test @@ -0,0 +1,38 @@ +--source include/have_binlog_format_row.inc +--source include/master-slave.inc + +SET SQL_MODE=DEFAULT; +CREATE TABLE t1 (a DATE); +INSERT INTO t1 VALUES (NULL); +INSERT INTO t1 VALUES ('2001-01-01'); + +SET SQL_MODE= ORACLE; +CREATE TABLE t2 SELECT * FROM t1; + +--let $binlog_file = LAST +source include/show_binlog_events.inc; + +SET SQL_MODE= DEFAULT; +SHOW CREATE TABLE t1; +SHOW CREATE TABLE t2; + +SET SQL_MODE= ORACLE; +SHOW CREATE TABLE t1; +SHOW CREATE TABLE t2; + +--sync_slave_with_master +SELECT * FROM t1; +SELECT * FROM t2; + +SET SQL_MODE= DEFAULT; +SHOW CREATE TABLE t1; +SHOW CREATE TABLE t2; + +SET SQL_MODE= ORACLE; +SHOW CREATE TABLE t1; +SHOW CREATE TABLE t2; + +# Cleanup +--connection master +DROP TABLE t1, t2; +--source include/rpl_end.inc diff --git a/mysql-test/suite/compat/oracle/t/type_date.test b/mysql-test/suite/compat/oracle/t/type_date.test index 61f7aa53944..36a5e99795f 100644 --- a/mysql-test/suite/compat/oracle/t/type_date.test +++ b/mysql-test/suite/compat/oracle/t/type_date.test @@ -2,3 +2,102 @@ SET sql_mode=ORACLE; CREATE TABLE t1 (a DATE); SHOW CREATE TABLE t1; DROP TABLE t1; + + +--echo # +--echo # MDEV-19632 Replication aborts with ER_SLAVE_CONVERSION_FAILED upon CREATE ... SELECT in ORACLE mode +--echo # + +SET sql_mode=DEFAULT; +--error ER_UNKNOWN_ERROR +CREATE TABLE t1 (a unknown.DATE); + + +SET sql_mode=DEFAULT; +CREATE TABLE t1 ( + def_date DATE, + mdb_date mariadb_schema.DATE, + ora_date oracle_schema.DATE, + max_date maxdb_schema.DATE +); +SHOW CREATE TABLE t1; +SET sql_mode=ORACLE; +SHOW CREATE TABLE t1; +DROP TABLE t1; + + +SET sql_mode=ORACLE; +CREATE TABLE t1 ( + def_date DATE, + mdb_date mariadb_schema.DATE, + ora_date oracle_schema.DATE, + max_date maxdb_schema.DATE +); +SHOW CREATE TABLE t1; +SET sql_mode=DEFAULT; +SHOW CREATE TABLE t1; +DROP TABLE t1; + + +--echo # +--echo # ALTER..MODIFY and ALTER..CHANGE understand qualifiers +--echo # + +SET sql_mode=DEFAULT; +CREATE TABLE t1 (a DATE); +INSERT INTO t1 VALUES ('2001-01-01'); +SET sql_mode=ORACLE; +SHOW CREATE TABLE t1; +SELECT * FROM t1; + +ALTER TABLE t1 MODIFY a DATE; +SHOW CREATE TABLE t1; +SELECT * FROM t1; + +ALTER TABLE t1 MODIFY a mariadb_schema.DATE; +SHOW CREATE TABLE t1; +SELECT * FROM t1; + +ALTER TABLE t1 MODIFY a oracle_schema.DATE; +SHOW CREATE TABLE t1; +SELECT * FROM t1; + +ALTER TABLE t1 CHANGE a b mariadb_schema.DATE; +SHOW CREATE TABLE t1; +SELECT * FROM t1; + +ALTER TABLE t1 CHANGE b a oracle_schema.DATE; +SHOW CREATE TABLE t1; +SELECT * FROM t1; + +DROP TABLE t1; + +--echo # +--echo # Qualified syntax is not supported yet in SP +--echo # See MDEV-23353 Qualified data types in SP +--echo # + +SET sql_mode=ORACLE; +DELIMITER $$; +# Change to this when merging to 10.5: +#--error ER_UNKNOWN_DATA_TYPE +--error ER_PARSE_ERROR +CREATE FUNCTION f1() RETURN mariadb_schema.DATE AS +BEGIN + RETURN CURRENT_DATE; +END; +$$ +--error ER_PARSE_ERROR +CREATE PROCEDURE p1(a mariadb_schema.DATE) AS +BEGIN + NULL; +END; +$$ +--error ER_PARSE_ERROR +CREATE PROCEDURE p1() AS + a mariadb_schema.DATE; +BEGIN + NULL; +END; +$$ +DELIMITER ;$$ diff --git a/mysql-test/suite/engines/iuds/r/insert_decimal.result b/mysql-test/suite/engines/iuds/r/insert_decimal.result index 1e6b7a08558..f167712d048 100644 --- a/mysql-test/suite/engines/iuds/r/insert_decimal.result +++ b/mysql-test/suite/engines/iuds/r/insert_decimal.result @@ -1896,9 +1896,13 @@ Warnings: Warning 1264 Out of range value for column 'c1' at row 3 INSERT IGNORE INTO t5 VALUES('1e+52','-1e+52','1e+52',5),('1e-52','-1e-52','1e-52',6); Warnings: +Warning 1264 Out of range value for column 'c1' at row 1 +Warning 1264 Out of range value for column 'c2' at row 1 Warning 1264 Out of range value for column 'c3' at row 1 INSERT IGNORE INTO t5 VALUES('1e+53','-1e+53','1e+53',7),('1e-53','-1e-53','1e-53',8); Warnings: +Warning 1264 Out of range value for column 'c1' at row 1 +Warning 1264 Out of range value for column 'c2' at row 1 Warning 1264 Out of range value for column 'c3' at row 1 SELECT * FROM t5; c1 c2 c3 c4 diff --git a/mysql-test/suite/galera/disabled.def b/mysql-test/suite/galera/disabled.def index cfab7ce3472..8550b2bd180 100644 --- a/mysql-test/suite/galera/disabled.def +++ b/mysql-test/suite/galera/disabled.def @@ -46,3 +46,4 @@ partition : MDEV-19958 Galera test failure on galera.partition query_cache: MDEV-15805 Test failure on galera.query_cache sql_log_bin : MDEV-21491 galera.sql_log_bin versioning_trx_id : MDEV-18590 galera.versioning_trx_id +MW-328A : MDEV-22666? diff --git a/mysql-test/suite/innodb/r/read_only_recover_committed.result b/mysql-test/suite/innodb/r/read_only_recover_committed.result index e4cfebd1ec0..e7895ce6d46 100644 --- a/mysql-test/suite/innodb/r/read_only_recover_committed.result +++ b/mysql-test/suite/innodb/r/read_only_recover_committed.result @@ -33,6 +33,7 @@ a 20 UPDATE t SET a=3 WHERE a=1; # restart: --innodb-read-only +SET GLOBAL innodb_status_output= @@GLOBAL.innodb_status_output; # Starting with MariaDB 10.2, innodb_read_only implies READ UNCOMMITTED. # In earlier versions, this would return the last committed version # (only a=3; no record for a=20)! diff --git a/mysql-test/suite/innodb/t/read_only_recover_committed.test b/mysql-test/suite/innodb/t/read_only_recover_committed.test index 526d9fc02bf..9ad09bb9b3a 100644 --- a/mysql-test/suite/innodb/t/read_only_recover_committed.test +++ b/mysql-test/suite/innodb/t/read_only_recover_committed.test @@ -59,6 +59,7 @@ SELECT * FROM t; UPDATE t SET a=3 WHERE a=1; --let $restart_parameters= --innodb-read-only --source include/restart_mysqld.inc +SET GLOBAL innodb_status_output= @@GLOBAL.innodb_status_output; --echo # Starting with MariaDB 10.2, innodb_read_only implies READ UNCOMMITTED. --echo # In earlier versions, this would return the last committed version --echo # (only a=3; no record for a=20)! diff --git a/mysql-test/suite/maria/encrypt-no-key.result b/mysql-test/suite/maria/encrypt-no-key.result new file mode 100644 index 00000000000..6745670dfac --- /dev/null +++ b/mysql-test/suite/maria/encrypt-no-key.result @@ -0,0 +1,15 @@ +call mtr.add_suppression('Unknown key id 1. Can''t continue'); +set global aria_encrypt_tables= 1; +create table t1 (pk int primary key, a int, key(a)) engine=aria transactional=1; +alter table t1 disable keys; +insert into t1 values (1,1); +alter table t1 enable keys; +ERROR HY000: Unknown key id 1. Can't continue! +repair table t1 use_frm; +Table Op Msg_type Msg_text +test.t1 repair warning Number of rows changed from 0 to 1 +test.t1 repair Error Unknown key id 1. Can't continue! +test.t1 repair Error Unknown key id 1. Can't continue! +test.t1 repair status OK +drop table t1; +set global aria_encrypt_tables= default; diff --git a/mysql-test/suite/maria/encrypt-no-key.test b/mysql-test/suite/maria/encrypt-no-key.test new file mode 100644 index 00000000000..2d586c50695 --- /dev/null +++ b/mysql-test/suite/maria/encrypt-no-key.test @@ -0,0 +1,14 @@ +# +# MDEV-18496 Crash when Aria encryption is enabled but plugin not available +# +call mtr.add_suppression('Unknown key id 1. Can''t continue'); + +set global aria_encrypt_tables= 1; +create table t1 (pk int primary key, a int, key(a)) engine=aria transactional=1; +alter table t1 disable keys; +insert into t1 values (1,1); +error 192; +alter table t1 enable keys; +repair table t1 use_frm; +drop table t1; +set global aria_encrypt_tables= default; diff --git a/mysql-test/suite/parts/r/partition_debug.result b/mysql-test/suite/parts/r/partition_debug.result index e438cae7a14..96ed9b010b7 100644 --- a/mysql-test/suite/parts/r/partition_debug.result +++ b/mysql-test/suite/parts/r/partition_debug.result @@ -1,3 +1,4 @@ +FLUSH TABLES; # # Bug#13737949: CRASH IN HA_PARTITION::INDEX_INIT # Bug#18694052: SERVER CRASH IN HA_PARTITION::INIT_RECORD_PRIORITY_QUEUE diff --git a/mysql-test/suite/parts/t/partition_debug.test b/mysql-test/suite/parts/t/partition_debug.test index 864452f8d8b..6d7cf3ae8b3 100644 --- a/mysql-test/suite/parts/t/partition_debug.test +++ b/mysql-test/suite/parts/t/partition_debug.test @@ -8,6 +8,10 @@ # Crash tests don't work with embedded --source include/not_embedded.inc +# Make sure system tables are not open, as the test will kill the server +# and it will cause corruption errors in the log +FLUSH TABLES; + # Partitioning test that require debug features --echo # diff --git a/mysql-test/suite/roles/drop_current_role.result b/mysql-test/suite/roles/drop_current_role.result new file mode 100644 index 00000000000..b6de0304d44 --- /dev/null +++ b/mysql-test/suite/roles/drop_current_role.result @@ -0,0 +1,5 @@ +create role r; +set role r; +drop role r; +revoke all on *.* from current_role; +ERROR OP000: Invalid role specification `r` diff --git a/mysql-test/suite/roles/drop_current_role.test b/mysql-test/suite/roles/drop_current_role.test new file mode 100644 index 00000000000..c8d6fc5d9df --- /dev/null +++ b/mysql-test/suite/roles/drop_current_role.test @@ -0,0 +1,9 @@ +--source include/not_embedded.inc +# +# MDEV-22521 Server crashes in traverse_role_graph_up or Assertion `user' fails in traverse_role_graph_impl +# +create role r; +set role r; +drop role r; +error ER_INVALID_ROLE; +revoke all on *.* from current_role; diff --git a/mysql-test/suite/sys_vars/r/div_precision_increment_func.result b/mysql-test/suite/sys_vars/r/div_precision_increment_func.result index c839dd6b289..e16ce76fed9 100644 --- a/mysql-test/suite/sys_vars/r/div_precision_increment_func.result +++ b/mysql-test/suite/sys_vars/r/div_precision_increment_func.result @@ -51,9 +51,9 @@ INSERT into t1(name, salary, income_tax) values('Record_2', 501, 501*2.5/1000); INSERT into t1(name, salary, income_tax) values('Record_3', 210, 210*2.5/1000); SELECT * from t1; id name salary income_tax -1 Record_1 100011 250.027 -2 Record_2 501 1.2525 -3 Record_3 210 0.525 +1 Record_1 100011 250.03 +2 Record_2 501 1.25 +3 Record_3 210 0.53 connect test_con2, localhost, root,,; connection test_con2; ## Verifying session & global value of variable ## @@ -69,11 +69,11 @@ INSERT into t1(name, salary, income_tax) values('Record_5', 501, 501*2.5/1000); INSERT into t1(name, salary, income_tax) values('Record_6', 210, 210*2.5/1000); SELECT * from t1; id name salary income_tax -1 Record_1 100011 250.027 -2 Record_2 501 1.2525 -3 Record_3 210 0.525 -4 Record_4 100011 250.027 -5 Record_5 501 1.2525 +1 Record_1 100011 250.03 +2 Record_2 501 1.25 +3 Record_3 210 0.53 +4 Record_4 100011 250.028 +5 Record_5 501 1.253 6 Record_6 210 0.525 ## Dropping table t1 ## drop table t1; diff --git a/mysql-test/suite/vcol/r/not_supported.result b/mysql-test/suite/vcol/r/not_supported.result index c804cf220d2..c524234e7e5 100644 --- a/mysql-test/suite/vcol/r/not_supported.result +++ b/mysql-test/suite/vcol/r/not_supported.result @@ -29,7 +29,7 @@ set time_zone='+1:00'; flush tables; select * from t1; a b v -1 2 0.3333333330000000000 +1 2 0.3333000000000000000 select * from t8; a b v 1234567890 2 2009-02-14 00:31:30 |