From edbf124515b36dc2a5ba40ca469d0d70507734a3 Mon Sep 17 00:00:00 2001 From: Monty Date: Wed, 13 May 2020 23:30:34 +0300 Subject: Ensure that auto_increment fields are marked properly on update MDEV-19622 Assertion failures in ha_partition::set_auto_increment_if_higher upon UPDATE on Aria table --- mysql-test/suite/parts/inc/partition_auto_increment.inc | 11 +++++++++++ .../suite/parts/r/partition_auto_increment_innodb.result | 8 ++++++++ .../suite/parts/r/partition_auto_increment_maria.result | 8 ++++++++ .../suite/parts/r/partition_auto_increment_memory.result | 8 ++++++++ .../suite/parts/r/partition_auto_increment_myisam.result | 8 ++++++++ 5 files changed, 43 insertions(+) (limited to 'mysql-test') 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; ############################################################################## -- cgit v1.2.1 From 910c31928ee62646f06e1584e43071df34738afb Mon Sep 17 00:00:00 2001 From: Alexander Barkov Date: Wed, 13 May 2020 18:46:28 +0400 Subject: MDEV-22503 MDB limits DECIMAL column precision to 16 doing CTAS with floor/ceil over DECIMAL(X,Y) where X > 16 The DECIMAL data type branch in Item_func_int_val::fix_length_and_dec() incorrectly used DOUBLE-style length calculation, which resulted in a smaller data type than the actual result of FLOOR()/CEIL() needs. --- mysql-test/r/func_math.result | 718 ++++++++++++++++++++++++++++++++++++++++++ mysql-test/t/func_math.test | 47 +++ 2 files changed, 765 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/func_math.result b/mysql-test/r/func_math.result index c92549ae45e..23815bcfed4 100644 --- a/mysql-test/r/func_math.result +++ b/mysql-test/r/func_math.result @@ -994,5 +994,723 @@ SELECT -9223372036854775808 MOD -9223372036854775808; -9223372036854775808 MOD -9223372036854775808 0 # +# 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` int(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` int(11) DEFAULT NULL, + `fb` int(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` int(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` int(11) DEFAULT NULL, + `fb` int(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` int(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 # diff --git a/mysql-test/t/func_math.test b/mysql-test/t/func_math.test index 89e9c4f4d49..6edc342e9df 100644 --- a/mysql-test/t/func_math.test +++ b/mysql-test/t/func_math.test @@ -720,6 +720,53 @@ SELECT -9223372036854775808 MOD 9223372036854775808; SELECT -9223372036854775808 MOD -9223372036854775808; +--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 # --echo # End of 10.1 tests -- cgit v1.2.1 From 31f34b20f3295db7e99877dcfe61b5798a6cfe95 Mon Sep 17 00:00:00 2001 From: Alexander Barkov Date: Thu, 14 May 2020 11:41:27 +0400 Subject: MDEV-22502 MDB crashes in CREATE TABLE AS SELECT when the precision of returning type = 0. TRUNCATE(decimal_5_5) erroneously tried to create a DECIMAL(0,0) column. Creating a DECIMAL(1,0) column instead. --- mysql-test/r/func_math.result | 18 ++++++++++++++++++ mysql-test/t/func_math.test | 12 ++++++++++++ 2 files changed, 30 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/func_math.result b/mysql-test/r/func_math.result index 23815bcfed4..fcb2e73e623 100644 --- a/mysql-test/r/func_math.result +++ b/mysql-test/r/func_math.result @@ -994,6 +994,24 @@ 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); diff --git a/mysql-test/t/func_math.test b/mysql-test/t/func_math.test index 6edc342e9df..56eb7640f65 100644 --- a/mysql-test/t/func_math.test +++ b/mysql-test/t/func_math.test @@ -719,6 +719,18 @@ 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 -- cgit v1.2.1 From 1408e26d0b15ea95d3d017bb059cd65b53b00a86 Mon Sep 17 00:00:00 2001 From: Alexander Barkov Date: Fri, 15 May 2020 06:15:10 +0400 Subject: MDEV-22560 Crash on a table value constructor with an SP variable fix_fields_for_tvc() could call fix_fields() for Items that have already been fixed before. Changing fix_fields() to fix_fields_if_needed(). --- mysql-test/main/table_value_constr.result | 11 +++++++++++ mysql-test/main/table_value_constr.test | 13 +++++++++++++ 2 files changed, 24 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/main/table_value_constr.result b/mysql-test/main/table_value_constr.result index 03e378e68f2..b00cfd28dcc 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 ;$$ -- cgit v1.2.1 From d49233caf696ba5896ff9119ca1a07039368ab23 Mon Sep 17 00:00:00 2001 From: Varun Gupta Date: Thu, 14 May 2020 18:38:49 +0530 Subject: MDEV-18100: User defined aggregate functions not working correctly when the schema is changed The issue here was that when the schema was changed the value for the THD::server_status is ored with SERVER_SESSION_STATE_CHANGED. For custom aggregate functions, currently we check if the server_status is equal to SERVER_STATUS_LAST_ROW_SENT then we should terminate the execution of the custom aggregate function as there are no more rows to fetch. So the check should be that if the server status has the bit set for SERVER_STATUS_LAST_ROW_SENT then we should terminate the execution of the custom aggregate function. --- mysql-test/main/custom_aggregate_functions.result | 33 +++++++++++++++++++++++ mysql-test/main/custom_aggregate_functions.test | 33 +++++++++++++++++++++++ 2 files changed, 66 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/main/custom_aggregate_functions.result b/mysql-test/main/custom_aggregate_functions.result index 0a27334f58e..8ad7b4b4dc0 100644 --- a/mysql-test/main/custom_aggregate_functions.result +++ b/mysql-test/main/custom_aggregate_functions.result @@ -1153,3 +1153,36 @@ i sum(i) 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; diff --git a/mysql-test/main/custom_aggregate_functions.test b/mysql-test/main/custom_aggregate_functions.test index ab799b48bdb..4d9dd0a4929 100644 --- a/mysql-test/main/custom_aggregate_functions.test +++ b/mysql-test/main/custom_aggregate_functions.test @@ -965,3 +965,36 @@ select i, sum(i) from t1 group by i with rollup; # Cleanup 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; -- cgit v1.2.1