diff options
author | Marko Mäkelä <marko.makela@mariadb.com> | 2020-05-16 06:27:55 +0300 |
---|---|---|
committer | Marko Mäkelä <marko.makela@mariadb.com> | 2020-05-16 06:27:55 +0300 |
commit | 4f29d776c756ac522ae49c481ea8975dee8787fe (patch) | |
tree | d152b05f3bca9b4022802654c129f0470ed92038 /mysql-test | |
parent | a4996f951d731322acc63033646d950ddbb0f60c (diff) | |
parent | 3eadb135fd7b7e2d40fd6b9a819ac3245043f781 (diff) | |
download | mariadb-git-4f29d776c756ac522ae49c481ea8975dee8787fe.tar.gz |
Merge 10.3 into 10.4
Diffstat (limited to 'mysql-test')
-rw-r--r-- | mysql-test/main/custom_aggregate_functions.result | 35 | ||||
-rw-r--r-- | mysql-test/main/custom_aggregate_functions.test | 36 | ||||
-rw-r--r-- | mysql-test/main/func_math.result | 738 | ||||
-rw-r--r-- | mysql-test/main/func_math.test | 61 | ||||
-rw-r--r-- | mysql-test/main/table_value_constr.result | 11 | ||||
-rw-r--r-- | mysql-test/main/table_value_constr.test | 13 | ||||
-rw-r--r-- | mysql-test/suite/parts/inc/partition_auto_increment.inc | 11 | ||||
-rw-r--r-- | mysql-test/suite/parts/r/partition_auto_increment_innodb.result | 8 | ||||
-rw-r--r-- | mysql-test/suite/parts/r/partition_auto_increment_maria.result | 8 | ||||
-rw-r--r-- | mysql-test/suite/parts/r/partition_auto_increment_memory.result | 8 | ||||
-rw-r--r-- | mysql-test/suite/parts/r/partition_auto_increment_myisam.result | 8 |
11 files changed, 935 insertions, 2 deletions
diff --git a/mysql-test/main/custom_aggregate_functions.result b/mysql-test/main/custom_aggregate_functions.result index b98954b920e..3a9e20437d2 100644 --- a/mysql-test/main/custom_aggregate_functions.result +++ b/mysql-test/main/custom_aggregate_functions.result @@ -1154,6 +1154,40 @@ NULL 8 drop function agg_sum; drop table t1; # +# User defined aggregate functions not working correctly when the schema is changed +# +CREATE SCHEMA IF NOT EXISTS common_schema; +CREATE SCHEMA IF NOT EXISTS another_schema; +DROP FUNCTION IF EXISTS common_schema.add_ints | +Warnings: +Note 1305 FUNCTION common_schema.add_ints does not exist +CREATE FUNCTION common_schema.add_ints(int_1 INT, int_2 INT) RETURNS INT NO SQL +BEGIN +RETURN int_1 + int_2; +END | +DROP FUNCTION IF EXISTS common_schema.sum_ints | +Warnings: +Note 1305 FUNCTION common_schema.sum_ints does not exist +CREATE AGGREGATE FUNCTION common_schema.sum_ints(int_val INT) RETURNS INT +BEGIN +DECLARE result INT DEFAULT 0; +DECLARE CONTINUE HANDLER FOR NOT FOUND RETURN result; +LOOP FETCH GROUP NEXT ROW; +SET result = common_schema.add_ints(result, int_val); +END LOOP; +END | +use common_schema; +SELECT common_schema.sum_ints(seq) FROM (SELECT 1 seq UNION ALL SELECT 2) t; +common_schema.sum_ints(seq) +3 +USE another_schema; +SELECT common_schema.sum_ints(seq) FROM (SELECT 1 seq UNION ALL SELECT 2) t; +common_schema.sum_ints(seq) +3 +drop database common_schema; +drop database another_schema; +USE test; +# # MDEV-18813 PROCEDURE and anonymous blocks silently ignore FETCH GROUP NEXT ROW # CREATE PROCEDURE p1() @@ -1186,3 +1220,4 @@ STARTS CURRENT_TIMESTAMP + INTERVAL 1 MONTH ENDS CURRENT_TIMESTAMP + INTERVAL 1 MONTH + INTERVAL 1 WEEK DO FETCH GROUP NEXT ROW; ERROR HY000: Aggregate specific instruction (FETCH GROUP NEXT ROW) used in a wrong context +# End of 10.4 tests diff --git a/mysql-test/main/custom_aggregate_functions.test b/mysql-test/main/custom_aggregate_functions.test index a10ea44af60..12a09b07fc0 100644 --- a/mysql-test/main/custom_aggregate_functions.test +++ b/mysql-test/main/custom_aggregate_functions.test @@ -966,6 +966,40 @@ select i, sum(i) from t1 group by i with rollup; drop function agg_sum; drop table t1; +--echo # +--echo # User defined aggregate functions not working correctly when the schema is changed +--echo # + +CREATE SCHEMA IF NOT EXISTS common_schema; +CREATE SCHEMA IF NOT EXISTS another_schema; +DELIMITER |; +DROP FUNCTION IF EXISTS common_schema.add_ints | +CREATE FUNCTION common_schema.add_ints(int_1 INT, int_2 INT) RETURNS INT NO SQL +BEGIN + RETURN int_1 + int_2; +END | +DROP FUNCTION IF EXISTS common_schema.sum_ints | +CREATE AGGREGATE FUNCTION common_schema.sum_ints(int_val INT) RETURNS INT +BEGIN + DECLARE result INT DEFAULT 0; + DECLARE CONTINUE HANDLER FOR NOT FOUND RETURN result; + LOOP FETCH GROUP NEXT ROW; + SET result = common_schema.add_ints(result, int_val); + END LOOP; +END | + +DELIMITER ;| + +use common_schema; +SELECT common_schema.sum_ints(seq) FROM (SELECT 1 seq UNION ALL SELECT 2) t; + +USE another_schema; +SELECT common_schema.sum_ints(seq) FROM (SELECT 1 seq UNION ALL SELECT 2) t; + +drop database common_schema; +drop database another_schema; + +USE test; --echo # --echo # MDEV-18813 PROCEDURE and anonymous blocks silently ignore FETCH GROUP NEXT ROW @@ -1016,3 +1050,5 @@ CREATE EVENT ev1 STARTS CURRENT_TIMESTAMP + INTERVAL 1 MONTH ENDS CURRENT_TIMESTAMP + INTERVAL 1 MONTH + INTERVAL 1 WEEK DO FETCH GROUP NEXT ROW; + +--echo # End of 10.4 tests diff --git a/mysql-test/main/func_math.result b/mysql-test/main/func_math.result index 5c7c1c9a105..57425de897c 100644 --- a/mysql-test/main/func_math.result +++ b/mysql-test/main/func_math.result @@ -1015,6 +1015,742 @@ SELECT -9223372036854775808 MOD -9223372036854775808; -9223372036854775808 MOD -9223372036854775808 0 # +# MDEV-22502 MDB crashes in CREATE TABLE AS SELECT when the precision of returning type = 0 +# +CREATE TABLE t1 (d decimal(5,5)); +INSERT INTO t1 VALUES (0.55555); +SELECT TRUNCATE(d,0) FROM t1; +TRUNCATE(d,0) +0 +CREATE TABLE t2 AS SELECT TRUNCATE(d,0) FROM t1; +SELECT * FROM t2; +TRUNCATE(d,0) +0 +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `TRUNCATE(d,0)` decimal(1,0) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1, t2; +# +# MDEV-22503 MDB limits DECIMAL column precision to 16 doing CTAS with floor/ceil over DECIMAL(X,Y) where X > 16 +# +CREATE TABLE t44 (d1 decimal(38,0) DEFAULT NULL); +INSERT INTO t44 VALUES (12345678901234567890123456789012345678); +SELECT FLOOR(d1) FROM t44; +FLOOR(d1) +12345678901234567890123456789012345678 +CREATE TABLE t45 AS SELECT FLOOR(d1) FROM t44; +SELECT * FROM t45; +FLOOR(d1) +12345678901234567890123456789012345678 +SHOW CREATE TABLE t45; +Table Create Table +t45 CREATE TABLE `t45` ( + `FLOOR(d1)` decimal(38,0) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t44, t45; +CREATE PROCEDURE p1(prec INT, scale INT) +BEGIN +DECLARE maxval VARCHAR(128) DEFAULT ''; +SET @type= CONCAT('DECIMAL(', prec, ',', scale,')'); +SET @stmt= CONCAT('CREATE TABLE t1 (a ', @type, ',b ', @type, 'unsigned)'); +PREPARE stmt FROM @stmt; +EXECUTE stmt; +DEALLOCATE PREPARE stmt; +SET maxval= CONCAT(REPEAT('9', prec-scale), '.', REPEAT('9',scale)); +INSERT INTO t1 VALUES (maxval, maxval); +CREATE TABLE t2 AS SELECT a, b, FLOOR(a) AS fa, FLOOR(b) AS fb FROM t1; +SHOW CREATE TABLE t2; +SELECT * FROM t2; +DROP TABLE t1, t2; +END; +$$ +CREATE PROCEDURE p2(prec INT) +BEGIN +DECLARE scale INT DEFAULT 0; +WHILE scale < prec AND scale <= 30 DO +CALL p1(prec, scale); +SET scale= scale + 1; +END WHILE; +END; +$$ +CALL p2(38); +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(38,0) DEFAULT NULL, + `b` decimal(38,0) unsigned DEFAULT NULL, + `fa` decimal(38,0) DEFAULT NULL, + `fb` decimal(38,0) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 99999999999999999999999999999999999999 +b 99999999999999999999999999999999999999 +fa 99999999999999999999999999999999999999 +fb 99999999999999999999999999999999999999 +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(38,1) DEFAULT NULL, + `b` decimal(38,1) unsigned DEFAULT NULL, + `fa` decimal(37,0) DEFAULT NULL, + `fb` decimal(37,0) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 9999999999999999999999999999999999999.9 +b 9999999999999999999999999999999999999.9 +fa 9999999999999999999999999999999999999 +fb 9999999999999999999999999999999999999 +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(38,2) DEFAULT NULL, + `b` decimal(38,2) unsigned DEFAULT NULL, + `fa` decimal(36,0) DEFAULT NULL, + `fb` decimal(36,0) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 999999999999999999999999999999999999.99 +b 999999999999999999999999999999999999.99 +fa 999999999999999999999999999999999999 +fb 999999999999999999999999999999999999 +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(38,3) DEFAULT NULL, + `b` decimal(38,3) unsigned DEFAULT NULL, + `fa` decimal(35,0) DEFAULT NULL, + `fb` decimal(35,0) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 99999999999999999999999999999999999.999 +b 99999999999999999999999999999999999.999 +fa 99999999999999999999999999999999999 +fb 99999999999999999999999999999999999 +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(38,4) DEFAULT NULL, + `b` decimal(38,4) unsigned DEFAULT NULL, + `fa` decimal(34,0) DEFAULT NULL, + `fb` decimal(34,0) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 9999999999999999999999999999999999.9999 +b 9999999999999999999999999999999999.9999 +fa 9999999999999999999999999999999999 +fb 9999999999999999999999999999999999 +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(38,5) DEFAULT NULL, + `b` decimal(38,5) unsigned DEFAULT NULL, + `fa` decimal(33,0) DEFAULT NULL, + `fb` decimal(33,0) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 999999999999999999999999999999999.99999 +b 999999999999999999999999999999999.99999 +fa 999999999999999999999999999999999 +fb 999999999999999999999999999999999 +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(38,6) DEFAULT NULL, + `b` decimal(38,6) unsigned DEFAULT NULL, + `fa` decimal(32,0) DEFAULT NULL, + `fb` decimal(32,0) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 99999999999999999999999999999999.999999 +b 99999999999999999999999999999999.999999 +fa 99999999999999999999999999999999 +fb 99999999999999999999999999999999 +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(38,7) DEFAULT NULL, + `b` decimal(38,7) unsigned DEFAULT NULL, + `fa` decimal(31,0) DEFAULT NULL, + `fb` decimal(31,0) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 9999999999999999999999999999999.9999999 +b 9999999999999999999999999999999.9999999 +fa 9999999999999999999999999999999 +fb 9999999999999999999999999999999 +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(38,8) DEFAULT NULL, + `b` decimal(38,8) unsigned DEFAULT NULL, + `fa` decimal(30,0) DEFAULT NULL, + `fb` decimal(30,0) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 999999999999999999999999999999.99999999 +b 999999999999999999999999999999.99999999 +fa 999999999999999999999999999999 +fb 999999999999999999999999999999 +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(38,9) DEFAULT NULL, + `b` decimal(38,9) unsigned DEFAULT NULL, + `fa` decimal(29,0) DEFAULT NULL, + `fb` decimal(29,0) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 99999999999999999999999999999.999999999 +b 99999999999999999999999999999.999999999 +fa 99999999999999999999999999999 +fb 99999999999999999999999999999 +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(38,10) DEFAULT NULL, + `b` decimal(38,10) unsigned DEFAULT NULL, + `fa` decimal(28,0) DEFAULT NULL, + `fb` decimal(28,0) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 9999999999999999999999999999.9999999999 +b 9999999999999999999999999999.9999999999 +fa 9999999999999999999999999999 +fb 9999999999999999999999999999 +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(38,11) DEFAULT NULL, + `b` decimal(38,11) unsigned DEFAULT NULL, + `fa` decimal(27,0) DEFAULT NULL, + `fb` decimal(27,0) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 999999999999999999999999999.99999999999 +b 999999999999999999999999999.99999999999 +fa 999999999999999999999999999 +fb 999999999999999999999999999 +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(38,12) DEFAULT NULL, + `b` decimal(38,12) unsigned DEFAULT NULL, + `fa` decimal(26,0) DEFAULT NULL, + `fb` decimal(26,0) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 99999999999999999999999999.999999999999 +b 99999999999999999999999999.999999999999 +fa 99999999999999999999999999 +fb 99999999999999999999999999 +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(38,13) DEFAULT NULL, + `b` decimal(38,13) unsigned DEFAULT NULL, + `fa` decimal(25,0) DEFAULT NULL, + `fb` decimal(25,0) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 9999999999999999999999999.9999999999999 +b 9999999999999999999999999.9999999999999 +fa 9999999999999999999999999 +fb 9999999999999999999999999 +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(38,14) DEFAULT NULL, + `b` decimal(38,14) unsigned DEFAULT NULL, + `fa` decimal(24,0) DEFAULT NULL, + `fb` decimal(24,0) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 999999999999999999999999.99999999999999 +b 999999999999999999999999.99999999999999 +fa 999999999999999999999999 +fb 999999999999999999999999 +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(38,15) DEFAULT NULL, + `b` decimal(38,15) unsigned DEFAULT NULL, + `fa` decimal(23,0) DEFAULT NULL, + `fb` decimal(23,0) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 99999999999999999999999.999999999999999 +b 99999999999999999999999.999999999999999 +fa 99999999999999999999999 +fb 99999999999999999999999 +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(38,16) DEFAULT NULL, + `b` decimal(38,16) unsigned DEFAULT NULL, + `fa` decimal(22,0) DEFAULT NULL, + `fb` decimal(22,0) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 9999999999999999999999.9999999999999999 +b 9999999999999999999999.9999999999999999 +fa 9999999999999999999999 +fb 9999999999999999999999 +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(38,17) DEFAULT NULL, + `b` decimal(38,17) unsigned DEFAULT NULL, + `fa` decimal(21,0) DEFAULT NULL, + `fb` decimal(21,0) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 999999999999999999999.99999999999999999 +b 999999999999999999999.99999999999999999 +fa 999999999999999999999 +fb 999999999999999999999 +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(38,18) DEFAULT NULL, + `b` decimal(38,18) unsigned DEFAULT NULL, + `fa` decimal(20,0) DEFAULT NULL, + `fb` decimal(20,0) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 99999999999999999999.999999999999999999 +b 99999999999999999999.999999999999999999 +fa 99999999999999999999 +fb 99999999999999999999 +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(38,19) DEFAULT NULL, + `b` decimal(38,19) unsigned DEFAULT NULL, + `fa` decimal(19,0) DEFAULT NULL, + `fb` decimal(19,0) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 9999999999999999999.9999999999999999999 +b 9999999999999999999.9999999999999999999 +fa 9999999999999999999 +fb 9999999999999999999 +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(38,20) DEFAULT NULL, + `b` decimal(38,20) unsigned DEFAULT NULL, + `fa` decimal(18,0) DEFAULT NULL, + `fb` bigint(17) unsigned DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 999999999999999999.99999999999999999999 +b 999999999999999999.99999999999999999999 +fa 999999999999999999 +fb 999999999999999999 +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(38,21) DEFAULT NULL, + `b` decimal(38,21) unsigned DEFAULT NULL, + `fa` bigint(17) DEFAULT NULL, + `fb` bigint(17) unsigned DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 99999999999999999.999999999999999999999 +b 99999999999999999.999999999999999999999 +fa 99999999999999999 +fb 99999999999999999 +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(38,22) DEFAULT NULL, + `b` decimal(38,22) unsigned DEFAULT NULL, + `fa` bigint(17) DEFAULT NULL, + `fb` bigint(17) unsigned DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 9999999999999999.9999999999999999999999 +b 9999999999999999.9999999999999999999999 +fa 9999999999999999 +fb 9999999999999999 +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(38,23) DEFAULT NULL, + `b` decimal(38,23) unsigned DEFAULT NULL, + `fa` bigint(17) DEFAULT NULL, + `fb` bigint(17) unsigned DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 999999999999999.99999999999999999999999 +b 999999999999999.99999999999999999999999 +fa 999999999999999 +fb 999999999999999 +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(38,24) DEFAULT NULL, + `b` decimal(38,24) unsigned DEFAULT NULL, + `fa` bigint(17) DEFAULT NULL, + `fb` bigint(16) unsigned DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 99999999999999.999999999999999999999999 +b 99999999999999.999999999999999999999999 +fa 99999999999999 +fb 99999999999999 +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(38,25) DEFAULT NULL, + `b` decimal(38,25) unsigned DEFAULT NULL, + `fa` bigint(16) DEFAULT NULL, + `fb` bigint(15) unsigned DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 9999999999999.9999999999999999999999999 +b 9999999999999.9999999999999999999999999 +fa 9999999999999 +fb 9999999999999 +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(38,26) DEFAULT NULL, + `b` decimal(38,26) unsigned DEFAULT NULL, + `fa` bigint(15) DEFAULT NULL, + `fb` bigint(14) unsigned DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 999999999999.99999999999999999999999999 +b 999999999999.99999999999999999999999999 +fa 999999999999 +fb 999999999999 +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(38,27) DEFAULT NULL, + `b` decimal(38,27) unsigned DEFAULT NULL, + `fa` bigint(14) DEFAULT NULL, + `fb` bigint(13) unsigned DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 99999999999.999999999999999999999999999 +b 99999999999.999999999999999999999999999 +fa 99999999999 +fb 99999999999 +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(38,28) DEFAULT NULL, + `b` decimal(38,28) unsigned DEFAULT NULL, + `fa` bigint(13) DEFAULT NULL, + `fb` bigint(12) unsigned DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 9999999999.9999999999999999999999999999 +b 9999999999.9999999999999999999999999999 +fa 9999999999 +fb 9999999999 +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(38,29) DEFAULT NULL, + `b` decimal(38,29) unsigned DEFAULT NULL, + `fa` bigint(12) DEFAULT NULL, + `fb` bigint(11) unsigned DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 999999999.99999999999999999999999999999 +b 999999999.99999999999999999999999999999 +fa 999999999 +fb 999999999 +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(38,30) DEFAULT NULL, + `b` decimal(38,30) unsigned DEFAULT NULL, + `fa` bigint(11) DEFAULT NULL, + `fb` bigint(10) unsigned DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 99999999.999999999999999999999999999999 +b 99999999.999999999999999999999999999999 +fa 99999999 +fb 99999999 +CALL p2(30); +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(30,0) DEFAULT NULL, + `b` decimal(30,0) unsigned DEFAULT NULL, + `fa` decimal(30,0) DEFAULT NULL, + `fb` decimal(31,0) unsigned DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 999999999999999999999999999999 +b 999999999999999999999999999999 +fa 999999999999999999999999999999 +fb 999999999999999999999999999999 +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(30,1) DEFAULT NULL, + `b` decimal(30,1) unsigned DEFAULT NULL, + `fa` decimal(29,0) DEFAULT NULL, + `fb` decimal(30,0) unsigned DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 99999999999999999999999999999.9 +b 99999999999999999999999999999.9 +fa 99999999999999999999999999999 +fb 99999999999999999999999999999 +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(30,2) DEFAULT NULL, + `b` decimal(30,2) unsigned DEFAULT NULL, + `fa` decimal(28,0) DEFAULT NULL, + `fb` decimal(29,0) unsigned DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 9999999999999999999999999999.99 +b 9999999999999999999999999999.99 +fa 9999999999999999999999999999 +fb 9999999999999999999999999999 +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(30,3) DEFAULT NULL, + `b` decimal(30,3) unsigned DEFAULT NULL, + `fa` decimal(27,0) DEFAULT NULL, + `fb` decimal(28,0) unsigned DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 999999999999999999999999999.999 +b 999999999999999999999999999.999 +fa 999999999999999999999999999 +fb 999999999999999999999999999 +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(30,4) DEFAULT NULL, + `b` decimal(30,4) unsigned DEFAULT NULL, + `fa` decimal(26,0) DEFAULT NULL, + `fb` decimal(27,0) unsigned DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 99999999999999999999999999.9999 +b 99999999999999999999999999.9999 +fa 99999999999999999999999999 +fb 99999999999999999999999999 +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(30,5) DEFAULT NULL, + `b` decimal(30,5) unsigned DEFAULT NULL, + `fa` decimal(25,0) DEFAULT NULL, + `fb` decimal(26,0) unsigned DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 9999999999999999999999999.99999 +b 9999999999999999999999999.99999 +fa 9999999999999999999999999 +fb 9999999999999999999999999 +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(30,6) DEFAULT NULL, + `b` decimal(30,6) unsigned DEFAULT NULL, + `fa` decimal(24,0) DEFAULT NULL, + `fb` decimal(25,0) unsigned DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 999999999999999999999999.999999 +b 999999999999999999999999.999999 +fa 999999999999999999999999 +fb 999999999999999999999999 +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(30,7) DEFAULT NULL, + `b` decimal(30,7) unsigned DEFAULT NULL, + `fa` decimal(23,0) DEFAULT NULL, + `fb` decimal(24,0) unsigned DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 99999999999999999999999.9999999 +b 99999999999999999999999.9999999 +fa 99999999999999999999999 +fb 99999999999999999999999 +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(30,8) DEFAULT NULL, + `b` decimal(30,8) unsigned DEFAULT NULL, + `fa` decimal(22,0) DEFAULT NULL, + `fb` decimal(23,0) unsigned DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 9999999999999999999999.99999999 +b 9999999999999999999999.99999999 +fa 9999999999999999999999 +fb 9999999999999999999999 +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(30,9) DEFAULT NULL, + `b` decimal(30,9) unsigned DEFAULT NULL, + `fa` decimal(21,0) DEFAULT NULL, + `fb` decimal(22,0) unsigned DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 999999999999999999999.999999999 +b 999999999999999999999.999999999 +fa 999999999999999999999 +fb 999999999999999999999 +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(30,10) DEFAULT NULL, + `b` decimal(30,10) unsigned DEFAULT NULL, + `fa` decimal(20,0) DEFAULT NULL, + `fb` decimal(21,0) unsigned DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 99999999999999999999.9999999999 +b 99999999999999999999.9999999999 +fa 99999999999999999999 +fb 99999999999999999999 +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(30,11) DEFAULT NULL, + `b` decimal(30,11) unsigned DEFAULT NULL, + `fa` decimal(19,0) DEFAULT NULL, + `fb` decimal(20,0) unsigned DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 9999999999999999999.99999999999 +b 9999999999999999999.99999999999 +fa 9999999999999999999 +fb 9999999999999999999 +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(30,12) DEFAULT NULL, + `b` decimal(30,12) unsigned DEFAULT NULL, + `fa` decimal(18,0) DEFAULT NULL, + `fb` bigint(17) unsigned DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 999999999999999999.999999999999 +b 999999999999999999.999999999999 +fa 999999999999999999 +fb 999999999999999999 +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(30,13) DEFAULT NULL, + `b` decimal(30,13) unsigned DEFAULT NULL, + `fa` bigint(17) DEFAULT NULL, + `fb` bigint(17) unsigned DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 99999999999999999.9999999999999 +b 99999999999999999.9999999999999 +fa 99999999999999999 +fb 99999999999999999 +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(30,14) DEFAULT NULL, + `b` decimal(30,14) unsigned DEFAULT NULL, + `fa` bigint(17) DEFAULT NULL, + `fb` bigint(17) unsigned DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 9999999999999999.99999999999999 +b 9999999999999999.99999999999999 +fa 9999999999999999 +fb 9999999999999999 +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(30,15) DEFAULT NULL, + `b` decimal(30,15) unsigned DEFAULT NULL, + `fa` bigint(17) DEFAULT NULL, + `fb` bigint(17) unsigned DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 999999999999999.999999999999999 +b 999999999999999.999999999999999 +fa 999999999999999 +fb 999999999999999 +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(30,16) DEFAULT NULL, + `b` decimal(30,16) unsigned DEFAULT NULL, + `fa` bigint(17) DEFAULT NULL, + `fb` bigint(16) unsigned DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 99999999999999.9999999999999999 +b 99999999999999.9999999999999999 +fa 99999999999999 +fb 99999999999999 +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(30,17) DEFAULT NULL, + `b` decimal(30,17) unsigned DEFAULT NULL, + `fa` bigint(16) DEFAULT NULL, + `fb` bigint(15) unsigned DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 9999999999999.99999999999999999 +b 9999999999999.99999999999999999 +fa 9999999999999 +fb 9999999999999 +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(30,18) DEFAULT NULL, + `b` decimal(30,18) unsigned DEFAULT NULL, + `fa` bigint(15) DEFAULT NULL, + `fb` bigint(14) unsigned DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 999999999999.999999999999999999 +b 999999999999.999999999999999999 +fa 999999999999 +fb 999999999999 +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(30,19) DEFAULT NULL, + `b` decimal(30,19) unsigned DEFAULT NULL, + `fa` bigint(14) DEFAULT NULL, + `fb` bigint(13) unsigned DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 99999999999.9999999999999999999 +b 99999999999.9999999999999999999 +fa 99999999999 +fb 99999999999 +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(30,20) DEFAULT NULL, + `b` decimal(30,20) unsigned DEFAULT NULL, + `fa` bigint(13) DEFAULT NULL, + `fb` bigint(12) unsigned DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 9999999999.99999999999999999999 +b 9999999999.99999999999999999999 +fa 9999999999 +fb 9999999999 +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(30,21) DEFAULT NULL, + `b` decimal(30,21) unsigned DEFAULT NULL, + `fa` bigint(12) DEFAULT NULL, + `fb` bigint(11) unsigned DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 999999999.999999999999999999999 +b 999999999.999999999999999999999 +fa 999999999 +fb 999999999 +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(30,22) DEFAULT NULL, + `b` decimal(30,22) unsigned DEFAULT NULL, + `fa` bigint(11) DEFAULT NULL, + `fb` bigint(10) unsigned DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 99999999.9999999999999999999999 +b 99999999.9999999999999999999999 +fa 99999999 +fb 99999999 +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(30,23) DEFAULT NULL, + `b` decimal(30,23) unsigned DEFAULT NULL, + `fa` bigint(10) DEFAULT NULL, + `fb` int(9) unsigned DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 9999999.99999999999999999999999 +b 9999999.99999999999999999999999 +fa 9999999 +fb 9999999 +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(30,24) DEFAULT NULL, + `b` decimal(30,24) unsigned DEFAULT NULL, + `fa` int(9) DEFAULT NULL, + `fb` int(8) unsigned DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 999999.999999999999999999999999 +b 999999.999999999999999999999999 +fa 999999 +fb 999999 +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(30,25) DEFAULT NULL, + `b` decimal(30,25) unsigned DEFAULT NULL, + `fa` int(8) DEFAULT NULL, + `fb` int(7) unsigned DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 99999.9999999999999999999999999 +b 99999.9999999999999999999999999 +fa 99999 +fb 99999 +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(30,26) DEFAULT NULL, + `b` decimal(30,26) unsigned DEFAULT NULL, + `fa` int(7) DEFAULT NULL, + `fb` int(6) unsigned DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 9999.99999999999999999999999999 +b 9999.99999999999999999999999999 +fa 9999 +fb 9999 +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(30,27) DEFAULT NULL, + `b` decimal(30,27) unsigned DEFAULT NULL, + `fa` int(6) DEFAULT NULL, + `fb` int(5) unsigned DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 999.999999999999999999999999999 +b 999.999999999999999999999999999 +fa 999 +fb 999 +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(30,28) DEFAULT NULL, + `b` decimal(30,28) unsigned DEFAULT NULL, + `fa` int(5) DEFAULT NULL, + `fb` int(4) unsigned DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 99.9999999999999999999999999999 +b 99.9999999999999999999999999999 +fa 99 +fb 99 +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(30,29) DEFAULT NULL, + `b` decimal(30,29) unsigned DEFAULT NULL, + `fa` int(4) DEFAULT NULL, + `fb` int(3) unsigned DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 9.99999999999999999999999999999 +b 9.99999999999999999999999999999 +fa 9 +fb 9 +DROP PROCEDURE p2; +DROP PROCEDURE p1; +# # End of 10.1 tests # # @@ -1466,7 +2202,7 @@ SET @val = 'a'; EXECUTE stmt1 USING @val; CRC32(?) 3904355907 -DEALLOCATE PREPARE stmt; +DEALLOCATE PREPARE stmt1; SET NAMES utf8; CREATE TABLE t1 (a TEXT) CHARACTER SET = utf8; LOAD DATA INFILE '../../std_data/loaddata_utf8.dat' INTO TABLE t1 CHARACTER SET utf8; diff --git a/mysql-test/main/func_math.test b/mysql-test/main/func_math.test index e2baee8852c..651def577ae 100644 --- a/mysql-test/main/func_math.test +++ b/mysql-test/main/func_math.test @@ -715,6 +715,65 @@ SELECT 9223372036854775808 MOD -9223372036854775808; SELECT -9223372036854775808 MOD 9223372036854775808; SELECT -9223372036854775808 MOD -9223372036854775808; +--echo # +--echo # MDEV-22502 MDB crashes in CREATE TABLE AS SELECT when the precision of returning type = 0 +--echo # + +CREATE TABLE t1 (d decimal(5,5)); +INSERT INTO t1 VALUES (0.55555); +SELECT TRUNCATE(d,0) FROM t1; +CREATE TABLE t2 AS SELECT TRUNCATE(d,0) FROM t1; +SELECT * FROM t2; +SHOW CREATE TABLE t2; +DROP TABLE t1, t2; + + +--echo # +--echo # MDEV-22503 MDB limits DECIMAL column precision to 16 doing CTAS with floor/ceil over DECIMAL(X,Y) where X > 16 +--echo # + +CREATE TABLE t44 (d1 decimal(38,0) DEFAULT NULL); +INSERT INTO t44 VALUES (12345678901234567890123456789012345678); +SELECT FLOOR(d1) FROM t44; +CREATE TABLE t45 AS SELECT FLOOR(d1) FROM t44; +SELECT * FROM t45; +SHOW CREATE TABLE t45; +DROP TABLE t44, t45; + + +DELIMITER $$; +CREATE PROCEDURE p1(prec INT, scale INT) +BEGIN + DECLARE maxval VARCHAR(128) DEFAULT ''; + SET @type= CONCAT('DECIMAL(', prec, ',', scale,')'); + SET @stmt= CONCAT('CREATE TABLE t1 (a ', @type, ',b ', @type, 'unsigned)'); + PREPARE stmt FROM @stmt; + EXECUTE stmt; + DEALLOCATE PREPARE stmt; + SET maxval= CONCAT(REPEAT('9', prec-scale), '.', REPEAT('9',scale)); + INSERT INTO t1 VALUES (maxval, maxval); + CREATE TABLE t2 AS SELECT a, b, FLOOR(a) AS fa, FLOOR(b) AS fb FROM t1; + SHOW CREATE TABLE t2; + SELECT * FROM t2; + DROP TABLE t1, t2; +END; +$$ +CREATE PROCEDURE p2(prec INT) +BEGIN + DECLARE scale INT DEFAULT 0; + WHILE scale < prec AND scale <= 30 DO + CALL p1(prec, scale); + SET scale= scale + 1; + END WHILE; +END; +$$ +DELIMITER ;$$ +--vertical_results +CALL p2(38); +CALL p2(30); +--horizontal_results +DROP PROCEDURE p2; +DROP PROCEDURE p1; --echo # @@ -1013,7 +1072,7 @@ DROP FUNCTION crc32_func; PREPARE stmt1 FROM 'SELECT CRC32(?)'; SET @val = 'a'; EXECUTE stmt1 USING @val; -DEALLOCATE PREPARE stmt; +DEALLOCATE PREPARE stmt1; # Test case for checksum on contents of a file SET NAMES utf8; diff --git a/mysql-test/main/table_value_constr.result b/mysql-test/main/table_value_constr.result index b395a896000..cf4550471d6 100644 --- a/mysql-test/main/table_value_constr.result +++ b/mysql-test/main/table_value_constr.result @@ -2599,3 +2599,14 @@ a 2 1 drop view v1; +# +# MDEV-22560 Crash on a table value constructor with an SP variable +# +BEGIN NOT ATOMIC +DECLARE a INT DEFAULT 0; +VALUES (a) UNION SELECT 1; +END; +$$ +a +0 +1 diff --git a/mysql-test/main/table_value_constr.test b/mysql-test/main/table_value_constr.test index 4464eb7b77b..e7843c604dd 100644 --- a/mysql-test/main/table_value_constr.test +++ b/mysql-test/main/table_value_constr.test @@ -1326,3 +1326,16 @@ create view v1 as with t(a) as (values (2), (1)) select a from t; show create view v1; select * from v1; drop view v1; + + +--echo # +--echo # MDEV-22560 Crash on a table value constructor with an SP variable +--echo # + +DELIMITER $$; +BEGIN NOT ATOMIC + DECLARE a INT DEFAULT 0; + VALUES (a) UNION SELECT 1; +END; +$$ +DELIMITER ;$$ diff --git a/mysql-test/suite/parts/inc/partition_auto_increment.inc b/mysql-test/suite/parts/inc/partition_auto_increment.inc index 199c8680864..4392d04db8a 100644 --- a/mysql-test/suite/parts/inc/partition_auto_increment.inc +++ b/mysql-test/suite/parts/inc/partition_auto_increment.inc @@ -861,6 +861,17 @@ SELECT * FROM t1; DROP TABLE t1; } +if (!$skip_update) +{ +--echo # +--echo # MDEV-19622 Assertion failures in +--echo # ha_partition::set_auto_increment_if_higher upon UPDATE on Aria table +--echo # +CREATE OR REPLACE TABLE t1 (pk INT AUTO_INCREMENT, a INT, KEY(pk)) ENGINE=myisam PARTITION BY HASH(a); +INSERT INTO t1 VALUES (1,1),(2,2); +UPDATE t1 SET pk = 0; +DROP TABLE t1; +} --echo ############################################################################## } diff --git a/mysql-test/suite/parts/r/partition_auto_increment_innodb.result b/mysql-test/suite/parts/r/partition_auto_increment_innodb.result index 6250f28eb00..76f1ddfceae 100644 --- a/mysql-test/suite/parts/r/partition_auto_increment_innodb.result +++ b/mysql-test/suite/parts/r/partition_auto_increment_innodb.result @@ -1101,4 +1101,12 @@ SELECT * FROM t1; a 0 DROP TABLE t1; +# +# MDEV-19622 Assertion failures in +# ha_partition::set_auto_increment_if_higher upon UPDATE on Aria table +# +CREATE OR REPLACE TABLE t1 (pk INT AUTO_INCREMENT, a INT, KEY(pk)) ENGINE=myisam PARTITION BY HASH(a); +INSERT INTO t1 VALUES (1,1),(2,2); +UPDATE t1 SET pk = 0; +DROP TABLE t1; ############################################################################## diff --git a/mysql-test/suite/parts/r/partition_auto_increment_maria.result b/mysql-test/suite/parts/r/partition_auto_increment_maria.result index 5acce3e9492..5a3902475a9 100644 --- a/mysql-test/suite/parts/r/partition_auto_increment_maria.result +++ b/mysql-test/suite/parts/r/partition_auto_increment_maria.result @@ -1148,4 +1148,12 @@ SELECT * FROM t1; a 0 DROP TABLE t1; +# +# MDEV-19622 Assertion failures in +# ha_partition::set_auto_increment_if_higher upon UPDATE on Aria table +# +CREATE OR REPLACE TABLE t1 (pk INT AUTO_INCREMENT, a INT, KEY(pk)) ENGINE=myisam PARTITION BY HASH(a); +INSERT INTO t1 VALUES (1,1),(2,2); +UPDATE t1 SET pk = 0; +DROP TABLE t1; ############################################################################## diff --git a/mysql-test/suite/parts/r/partition_auto_increment_memory.result b/mysql-test/suite/parts/r/partition_auto_increment_memory.result index e622ddaa259..c395f8ed0c9 100644 --- a/mysql-test/suite/parts/r/partition_auto_increment_memory.result +++ b/mysql-test/suite/parts/r/partition_auto_increment_memory.result @@ -1129,4 +1129,12 @@ SELECT * FROM t1; a 0 DROP TABLE t1; +# +# MDEV-19622 Assertion failures in +# ha_partition::set_auto_increment_if_higher upon UPDATE on Aria table +# +CREATE OR REPLACE TABLE t1 (pk INT AUTO_INCREMENT, a INT, KEY(pk)) ENGINE=myisam PARTITION BY HASH(a); +INSERT INTO t1 VALUES (1,1),(2,2); +UPDATE t1 SET pk = 0; +DROP TABLE t1; ############################################################################## diff --git a/mysql-test/suite/parts/r/partition_auto_increment_myisam.result b/mysql-test/suite/parts/r/partition_auto_increment_myisam.result index 4e67094b327..792423096b5 100644 --- a/mysql-test/suite/parts/r/partition_auto_increment_myisam.result +++ b/mysql-test/suite/parts/r/partition_auto_increment_myisam.result @@ -1148,4 +1148,12 @@ SELECT * FROM t1; a 0 DROP TABLE t1; +# +# MDEV-19622 Assertion failures in +# ha_partition::set_auto_increment_if_higher upon UPDATE on Aria table +# +CREATE OR REPLACE TABLE t1 (pk INT AUTO_INCREMENT, a INT, KEY(pk)) ENGINE=myisam PARTITION BY HASH(a); +INSERT INTO t1 VALUES (1,1),(2,2); +UPDATE t1 SET pk = 0; +DROP TABLE t1; ############################################################################## |