diff options
Diffstat (limited to 'mysql-test/main')
31 files changed, 1698 insertions, 19 deletions
diff --git a/mysql-test/main/create.result b/mysql-test/main/create.result index f863070c646..802c41af05d 100644 --- a/mysql-test/main/create.result +++ b/mysql-test/main/create.result @@ -2011,5 +2011,21 @@ CREATE TABLE t1 ( id1 INT, id2 INT, CONSTRAINT `foo` PRIMARY KEY (id1), CONSTRAI Warnings: Warning 1280 Name 'foo' ignored for PRIMARY key. DROP TABLE t1; +# +# 10.4 Test +# +# MDEV-21017: Assertion `!is_set() || (m_status == DA_OK_BULK && +# is_bulk_op())' failed or late ER_PERIOD_FIELD_WRONG_ATTRIBUTES +# upon attempt to create existing table +# +CREATE TABLE t1 (a INT); +LOCK TABLE t1 WRITE; +CREATE TABLE IF NOT EXISTS t1 (s DATE GENERATED ALWAYS AS ('2000-01-01') STORED, +e DATE, PERIOD FOR app(s,e)); +ERROR HY000: Period field `s` cannot be GENERATED ALWAYS AS +UNLOCK TABLES; +DROP TABLE t1; +# End of 10.4 Test CREATE TABLE t1 (id1 INT, id2 INT, primary key (id1), unique index (id2) visible); drop table t1; +# End of 10.5 Test diff --git a/mysql-test/main/create.test b/mysql-test/main/create.test index af12425f107..33f51f19944 100644 --- a/mysql-test/main/create.test +++ b/mysql-test/main/create.test @@ -1873,9 +1873,30 @@ drop table t1; CREATE TABLE t1 ( id1 INT, id2 INT, CONSTRAINT `foo` PRIMARY KEY (id1), CONSTRAINT `bar` UNIQUE KEY(id2)); DROP TABLE t1; +--echo # +--echo # 10.4 Test +--echo # +--echo # MDEV-21017: Assertion `!is_set() || (m_status == DA_OK_BULK && +--echo # is_bulk_op())' failed or late ER_PERIOD_FIELD_WRONG_ATTRIBUTES +--echo # upon attempt to create existing table +--echo # +CREATE TABLE t1 (a INT); +LOCK TABLE t1 WRITE; + +--error ER_PERIOD_FIELD_WRONG_ATTRIBUTES +CREATE TABLE IF NOT EXISTS t1 (s DATE GENERATED ALWAYS AS ('2000-01-01') STORED, +e DATE, PERIOD FOR app(s,e)); + +UNLOCK TABLES; +DROP TABLE t1; + +--echo # End of 10.4 Test + # # MDEV-22199 Add VISIBLE option for indexes in create table # CREATE TABLE t1 (id1 INT, id2 INT, primary key (id1), unique index (id2) visible); drop table t1; + +--echo # End of 10.5 Test diff --git a/mysql-test/main/func_crypt.result b/mysql-test/main/func_crypt.result index aaa6aa61eae..2c42d3dc845 100644 --- a/mysql-test/main/func_crypt.result +++ b/mysql-test/main/func_crypt.result @@ -214,3 +214,4 @@ SELECT * FROM t1; a b hello 12NKz5XM5JeKI DROP TABLE t1; +# End of 10.2 tests diff --git a/mysql-test/main/func_crypt.test b/mysql-test/main/func_crypt.test index d091aa4ae86..118a7023669 100644 --- a/mysql-test/main/func_crypt.test +++ b/mysql-test/main/func_crypt.test @@ -120,3 +120,5 @@ SHOW CREATE TABLE t1; INSERT INTO t1 (a) VALUES ('hello'); SELECT * FROM t1; DROP TABLE t1; + +--echo # End of 10.2 tests diff --git a/mysql-test/main/func_des_encrypt.result b/mysql-test/main/func_des_encrypt.result index b81f96f6ef7..540596589b6 100644 --- a/mysql-test/main/func_des_encrypt.result +++ b/mysql-test/main/func_des_encrypt.result @@ -35,3 +35,41 @@ DES_DECRYPT(DES_ENCRYPT('1234')) DES_DECRYPT(DES_ENCRYPT('12345')) DES_DECRYPT(D 1234 12345 123456 1234567 DROP TABLE t1; End of 5.0 tests +# +# MDEV-23330 Server crash or ASAN negative-size-param in +# my_strnncollsp_binary / SORT_FIELD_ATTR::compare_packed_varstrings +# +CREATE TABLE t1 (a CHAR(240), b BIT(48)); +INSERT INTO t1 VALUES ('a',b'0001'),('b',b'0010'),('c',b'0011'),('d',b'0100'),('e',b'0001'),('f',b'0101'),('g',b'0110'),('h',b'0111'),('i',b'1000'),('j',b'1001'); +SELECT DES_DECRYPT(a, 'x'), HEX(BINARY b) FROM t1 GROUP BY 1, 2 WITH ROLLUP; +DES_DECRYPT(a, 'x') HEX(BINARY b) +a 000000000001 +a NULL +b 000000000002 +b NULL +c 000000000003 +c NULL +d 000000000004 +d NULL +e 000000000001 +e NULL +f 000000000005 +f NULL +g 000000000006 +g NULL +h 000000000007 +h NULL +i 000000000008 +i NULL +j 000000000009 +j NULL +NULL NULL +DROP TABLE t1; +CREATE TABLE t1 (a INT); +INSERT t1 VALUES (1),(2); +SELECT CHAR_LENGTH(a), DES_DECRYPT(a) FROM (SELECT _utf8 0xC2A2 AS a FROM t1) AS t2; +CHAR_LENGTH(a) DES_DECRYPT(a) +1 ¢ +1 ¢ +DROP TABLE t1; +End of 10.5 tests diff --git a/mysql-test/main/func_des_encrypt.test b/mysql-test/main/func_des_encrypt.test index c9661b81cc0..44fc30ff00f 100644 --- a/mysql-test/main/func_des_encrypt.test +++ b/mysql-test/main/func_des_encrypt.test @@ -37,3 +37,24 @@ SELECT DROP TABLE t1; --Echo End of 5.0 tests + +--echo # +--echo # MDEV-23330 Server crash or ASAN negative-size-param in +--echo # my_strnncollsp_binary / SORT_FIELD_ATTR::compare_packed_varstrings +--echo # + +CREATE TABLE t1 (a CHAR(240), b BIT(48)); +INSERT INTO t1 VALUES ('a',b'0001'),('b',b'0010'),('c',b'0011'),('d',b'0100'),('e',b'0001'),('f',b'0101'),('g',b'0110'),('h',b'0111'),('i',b'1000'),('j',b'1001'); +SELECT DES_DECRYPT(a, 'x'), HEX(BINARY b) FROM t1 GROUP BY 1, 2 WITH ROLLUP; +DROP TABLE t1; + +# +# don't change the charset of a literal Item_string +# + +CREATE TABLE t1 (a INT); +INSERT t1 VALUES (1),(2); +SELECT CHAR_LENGTH(a), DES_DECRYPT(a) FROM (SELECT _utf8 0xC2A2 AS a FROM t1) AS t2; +DROP TABLE t1; + +--Echo End of 10.5 tests diff --git a/mysql-test/main/func_group.result b/mysql-test/main/func_group.result index 1fc92ba90cc..9233393fd94 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/func_math.result b/mysql-test/main/func_math.result index 97f66476919..460157df263 100644 --- a/mysql-test/main/func_math.result +++ b/mysql-test/main/func_math.result @@ -348,7 +348,6 @@ truncate(4, cast(-2 as unsigned)) truncate(4, 18446744073709551614) truncate(4, 4 4 0 Warnings: Note 1105 Cast to unsigned converted negative integer to it's positive complement -Note 1105 Cast to unsigned converted negative integer to it's positive complement select round(10000000000000000000, -19), truncate(10000000000000000000, -19); round(10000000000000000000, -19) truncate(10000000000000000000, -19) 10000000000000000000 10000000000000000000 @@ -1784,7 +1783,7 @@ ROUND(10e0,NULL) AS c3; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( - `c1` double DEFAULT NULL, + `c1` int(2) DEFAULT NULL, `c2` double DEFAULT NULL, `c3` double DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 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..879a1eabc25 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_DATA_TYPE +CREATE TABLE t1 (a MARIADB_SCHEMA.date); +--error ER_UNKNOWN_DATA_TYPE +CREATE TABLE t1 (a Mariadb_schema.date); + +CREATE TABLE t1 (a mariadb_schema.date); +DROP TABLE t1; diff --git a/mysql-test/main/order_by.result b/mysql-test/main/order_by.result index f1a6cb086b8..de0d3dc4003 100644 --- a/mysql-test/main/order_by.result +++ b/mysql-test/main/order_by.result @@ -4097,4 +4097,14 @@ Y B A DROP TABLE t1; +# +# MDEV-23414 Assertion `res->charset() == item->collation.collation' failed in Type_handler_string_result::make_packed_sort_key_part +# +CREATE TABLE t1 (a CHAR(3), b BINARY(255)); +INSERT t1 VALUES ('foo','bar'),('baz','qux'); +SELECT COALESCE(a, b) AS f FROM t1 ORDER BY f; +f +baz +foo +DROP TABLE t1; # End of 10.5 tests diff --git a/mysql-test/main/order_by.test b/mysql-test/main/order_by.test index e27822006b5..038bf82b0fb 100644 --- a/mysql-test/main/order_by.test +++ b/mysql-test/main/order_by.test @@ -2533,4 +2533,12 @@ SELECT * FROM t1 ORDER BY a DESC; DROP TABLE t1; +--echo # +--echo # MDEV-23414 Assertion `res->charset() == item->collation.collation' failed in Type_handler_string_result::make_packed_sort_key_part +--echo # +CREATE TABLE t1 (a CHAR(3), b BINARY(255)); +INSERT t1 VALUES ('foo','bar'),('baz','qux'); +SELECT COALESCE(a, b) AS f FROM t1 ORDER BY f; +DROP TABLE t1; + --echo # End of 10.5 tests 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 b132cf90f2c..6f97cd013b3 100644 --- a/mysql-test/main/partition.result +++ b/mysql-test/main/partition.result @@ -2767,5 +2767,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 15f263747fe..b715866fab8 100644 --- a/mysql-test/main/partition.test +++ b/mysql-test/main/partition.test @@ -2977,5 +2977,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 51194717676..2e60a760fd8 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_hex_hybrid.result b/mysql-test/main/type_hex_hybrid.result index 92a7432ae56..26ff385a709 100644 --- a/mysql-test/main/type_hex_hybrid.result +++ b/mysql-test/main/type_hex_hybrid.result @@ -178,5 +178,59 @@ c3 18446744073709551615 c4 18446744073709551615 c5 18446744073709551615 # +# MDEV-23368 ROUND(18446744073709551615,-11) returns a wrong result +# +SELECT ROUND(0xFFFFFFFFFFFFFFFF,-10), ROUND(0xFFFFFFFFFFFFFFFF,-11); +ROUND(0xFFFFFFFFFFFFFFFF,-10) ROUND(0xFFFFFFFFFFFFFFFF,-11) +18446744070000000000 18446744100000000000 +CREATE TABLE t1 AS SELECT ROUND(0xFFFFFFFFFFFFFFFF,-10), ROUND(0xFFFFFFFFFFFFFFFF,-11); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `ROUND(0xFFFFFFFFFFFFFFFF,-10)` decimal(21,0) unsigned NOT NULL, + `ROUND(0xFFFFFFFFFFFFFFFF,-11)` decimal(21,0) unsigned NOT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +SELECT * FROM t1; +ROUND(0xFFFFFFFFFFFFFFFF,-10) ROUND(0xFFFFFFFFFFFFFFFF,-11) +18446744070000000000 18446744100000000000 +DROP TABLE t1; +# +# MDEV-23366 ROUND(18446744073709551615,rand()*0) returns a wrong result +# +SELECT +ROUND(0xFFFFFFFFFFFFFFFF,NULL) AS c1, +ROUND(0xFFFFFFFFFFFFFFFF,rand()*0) AS c2, +ROUND(0xFFFFFFFFFFFFFFFF,-1) AS c3, +ROUND(0xFFFFFFFFFFFFFFFF,-19) AS c4, +ROUND(0xFFFFFFFFFFFFFFFF,rand()*0-19) AS c5; +c1 NULL +c2 18446744073709551615 +c3 18446744073709551620 +c4 20000000000000000000 +c5 20000000000000000000 +CREATE OR REPLACE TABLE t1 AS +SELECT +ROUND(0xFFFFFFFFFFFFFFFF,NULL) AS c1, +ROUND(0xFFFFFFFFFFFFFFFF,rand()*0) AS c2, +ROUND(0xFFFFFFFFFFFFFFFF,-1) AS c3, +ROUND(0xFFFFFFFFFFFFFFFF,-19) AS c4, +ROUND(0xFFFFFFFFFFFFFFFF,rand()*0-19) AS c5; +SELECT * FROM t1; +c1 NULL +c2 18446744073709551615 +c3 18446744073709551620 +c4 20000000000000000000 +c5 20000000000000000000 +SHOW CREATE TABLE t1; +Table t1 +Create Table CREATE TABLE `t1` ( + `c1` bigint(20) unsigned DEFAULT NULL, + `c2` decimal(21,0) unsigned NOT NULL, + `c3` decimal(21,0) unsigned NOT NULL, + `c4` decimal(21,0) unsigned NOT NULL, + `c5` decimal(21,0) unsigned NOT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +# # End of 10.4 tests # diff --git a/mysql-test/main/type_hex_hybrid.test b/mysql-test/main/type_hex_hybrid.test index 77afb45cfbf..175169fbda4 100644 --- a/mysql-test/main/type_hex_hybrid.test +++ b/mysql-test/main/type_hex_hybrid.test @@ -47,5 +47,41 @@ DELIMITER ;$$ --echo # +--echo # MDEV-23368 ROUND(18446744073709551615,-11) returns a wrong result +--echo # + +SELECT ROUND(0xFFFFFFFFFFFFFFFF,-10), ROUND(0xFFFFFFFFFFFFFFFF,-11); +CREATE TABLE t1 AS SELECT ROUND(0xFFFFFFFFFFFFFFFF,-10), ROUND(0xFFFFFFFFFFFFFFFF,-11); +SHOW CREATE TABLE t1; +SELECT * FROM t1; +DROP TABLE t1; + + +--echo # +--echo # MDEV-23366 ROUND(18446744073709551615,rand()*0) returns a wrong result +--echo # + +--vertical_results +SELECT + ROUND(0xFFFFFFFFFFFFFFFF,NULL) AS c1, + ROUND(0xFFFFFFFFFFFFFFFF,rand()*0) AS c2, + ROUND(0xFFFFFFFFFFFFFFFF,-1) AS c3, + ROUND(0xFFFFFFFFFFFFFFFF,-19) AS c4, + ROUND(0xFFFFFFFFFFFFFFFF,rand()*0-19) AS c5; + +CREATE OR REPLACE TABLE t1 AS +SELECT + ROUND(0xFFFFFFFFFFFFFFFF,NULL) AS c1, + ROUND(0xFFFFFFFFFFFFFFFF,rand()*0) AS c2, + ROUND(0xFFFFFFFFFFFFFFFF,-1) AS c3, + ROUND(0xFFFFFFFFFFFFFFFF,-19) AS c4, + ROUND(0xFFFFFFFFFFFFFFFF,rand()*0-19) AS c5; + +SELECT * FROM t1; +SHOW CREATE TABLE t1; +DROP TABLE t1; +--horizontal_results + +--echo # --echo # End of 10.4 tests --echo # diff --git a/mysql-test/main/type_int.result b/mysql-test/main/type_int.result index b75a07251a9..c9763a1453e 100644 --- a/mysql-test/main/type_int.result +++ b/mysql-test/main/type_int.result @@ -413,6 +413,7 @@ COUNT(*) DROP TABLE t1; # # MDEV-23337 Rounding functions create a wrong data type for numeric input +# MDEV-23350 ROUND(bigint_22_or_longer) returns a wrong data type # CREATE PROCEDURE p1(t VARCHAR(64)) BEGIN @@ -420,7 +421,7 @@ SELECT t AS ``; EXECUTE IMMEDIATE REPLACE('CREATE TABLE t1 (a TYPE)', 'TYPE', t); INSERT IGNORE INTO t1 VALUES (-100000000000000000000000000000); INSERT IGNORE INTO t1 VALUES (100000000000000000000000000000); -CREATE TABLE t2 AS SELECT a, ROUND(a), TRUNCATE(a,0), FLOOR(a), CEILING(a) FROM t1; +CREATE TABLE t2 AS SELECT a, ROUND(a), ROUND(a,1), ROUND(a,10), TRUNCATE(a,0), FLOOR(a), CEILING(a) FROM t1; SHOW CREATE TABLE t2; SELECT * FROM t2; DROP TABLE t1, t2; @@ -432,17 +433,23 @@ Table t2 Create Table CREATE TABLE `t2` ( `a` tinyint(4) DEFAULT NULL, `ROUND(a)` tinyint(4) DEFAULT NULL, + `ROUND(a,1)` tinyint(4) DEFAULT NULL, + `ROUND(a,10)` tinyint(4) DEFAULT NULL, `TRUNCATE(a,0)` tinyint(4) DEFAULT NULL, `FLOOR(a)` tinyint(4) DEFAULT NULL, `CEILING(a)` tinyint(4) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 a -128 ROUND(a) -128 +ROUND(a,1) -128 +ROUND(a,10) -128 TRUNCATE(a,0) -128 FLOOR(a) -128 CEILING(a) -128 a 127 ROUND(a) 127 +ROUND(a,1) 127 +ROUND(a,10) 127 TRUNCATE(a,0) 127 FLOOR(a) 127 CEILING(a) 127 @@ -452,17 +459,23 @@ Table t2 Create Table CREATE TABLE `t2` ( `a` smallint(6) DEFAULT NULL, `ROUND(a)` smallint(6) DEFAULT NULL, + `ROUND(a,1)` smallint(6) DEFAULT NULL, + `ROUND(a,10)` smallint(6) DEFAULT NULL, `TRUNCATE(a,0)` smallint(6) DEFAULT NULL, `FLOOR(a)` smallint(6) DEFAULT NULL, `CEILING(a)` smallint(6) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 a -32768 ROUND(a) -32768 +ROUND(a,1) -32768 +ROUND(a,10) -32768 TRUNCATE(a,0) -32768 FLOOR(a) -32768 CEILING(a) -32768 a 32767 ROUND(a) 32767 +ROUND(a,1) 32767 +ROUND(a,10) 32767 TRUNCATE(a,0) 32767 FLOOR(a) 32767 CEILING(a) 32767 @@ -472,17 +485,23 @@ Table t2 Create Table CREATE TABLE `t2` ( `a` mediumint(9) DEFAULT NULL, `ROUND(a)` mediumint(9) DEFAULT NULL, + `ROUND(a,1)` mediumint(9) DEFAULT NULL, + `ROUND(a,10)` mediumint(9) DEFAULT NULL, `TRUNCATE(a,0)` mediumint(9) DEFAULT NULL, `FLOOR(a)` mediumint(9) DEFAULT NULL, `CEILING(a)` mediumint(9) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 a -8388608 ROUND(a) -8388608 +ROUND(a,1) -8388608 +ROUND(a,10) -8388608 TRUNCATE(a,0) -8388608 FLOOR(a) -8388608 CEILING(a) -8388608 a 8388607 ROUND(a) 8388607 +ROUND(a,1) 8388607 +ROUND(a,10) 8388607 TRUNCATE(a,0) 8388607 FLOOR(a) 8388607 CEILING(a) 8388607 @@ -492,17 +511,23 @@ Table t2 Create Table CREATE TABLE `t2` ( `a` int(11) DEFAULT NULL, `ROUND(a)` int(11) DEFAULT NULL, + `ROUND(a,1)` int(11) DEFAULT NULL, + `ROUND(a,10)` int(11) DEFAULT NULL, `TRUNCATE(a,0)` int(11) DEFAULT NULL, `FLOOR(a)` int(11) DEFAULT NULL, `CEILING(a)` int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 a -2147483648 ROUND(a) -2147483648 +ROUND(a,1) -2147483648 +ROUND(a,10) -2147483648 TRUNCATE(a,0) -2147483648 FLOOR(a) -2147483648 CEILING(a) -2147483648 a 2147483647 ROUND(a) 2147483647 +ROUND(a,1) 2147483647 +ROUND(a,10) 2147483647 TRUNCATE(a,0) 2147483647 FLOOR(a) 2147483647 CEILING(a) 2147483647 @@ -512,17 +537,153 @@ Table t2 Create Table CREATE TABLE `t2` ( `a` bigint(20) DEFAULT NULL, `ROUND(a)` bigint(20) DEFAULT NULL, + `ROUND(a,1)` bigint(20) DEFAULT NULL, + `ROUND(a,10)` bigint(20) DEFAULT NULL, `TRUNCATE(a,0)` bigint(20) DEFAULT NULL, `FLOOR(a)` bigint(20) DEFAULT NULL, `CEILING(a)` bigint(20) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 a -9223372036854775808 ROUND(a) -9223372036854775808 +ROUND(a,1) -9223372036854775808 +ROUND(a,10) -9223372036854775808 TRUNCATE(a,0) -9223372036854775808 FLOOR(a) -9223372036854775808 CEILING(a) -9223372036854775808 a 9223372036854775807 ROUND(a) 9223372036854775807 +ROUND(a,1) 9223372036854775807 +ROUND(a,10) 9223372036854775807 +TRUNCATE(a,0) 9223372036854775807 +FLOOR(a) 9223372036854775807 +CEILING(a) 9223372036854775807 +CALL p1('bigint(20)'); + bigint(20) +Table t2 +Create Table CREATE TABLE `t2` ( + `a` bigint(20) DEFAULT NULL, + `ROUND(a)` bigint(20) DEFAULT NULL, + `ROUND(a,1)` bigint(20) DEFAULT NULL, + `ROUND(a,10)` bigint(20) DEFAULT NULL, + `TRUNCATE(a,0)` bigint(20) DEFAULT NULL, + `FLOOR(a)` bigint(20) DEFAULT NULL, + `CEILING(a)` bigint(20) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a -9223372036854775808 +ROUND(a) -9223372036854775808 +ROUND(a,1) -9223372036854775808 +ROUND(a,10) -9223372036854775808 +TRUNCATE(a,0) -9223372036854775808 +FLOOR(a) -9223372036854775808 +CEILING(a) -9223372036854775808 +a 9223372036854775807 +ROUND(a) 9223372036854775807 +ROUND(a,1) 9223372036854775807 +ROUND(a,10) 9223372036854775807 +TRUNCATE(a,0) 9223372036854775807 +FLOOR(a) 9223372036854775807 +CEILING(a) 9223372036854775807 +CALL p1('bigint(21)'); + bigint(21) +Table t2 +Create Table CREATE TABLE `t2` ( + `a` bigint(21) DEFAULT NULL, + `ROUND(a)` bigint(21) DEFAULT NULL, + `ROUND(a,1)` bigint(21) DEFAULT NULL, + `ROUND(a,10)` bigint(21) DEFAULT NULL, + `TRUNCATE(a,0)` bigint(21) DEFAULT NULL, + `FLOOR(a)` bigint(21) DEFAULT NULL, + `CEILING(a)` bigint(21) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a -9223372036854775808 +ROUND(a) -9223372036854775808 +ROUND(a,1) -9223372036854775808 +ROUND(a,10) -9223372036854775808 +TRUNCATE(a,0) -9223372036854775808 +FLOOR(a) -9223372036854775808 +CEILING(a) -9223372036854775808 +a 9223372036854775807 +ROUND(a) 9223372036854775807 +ROUND(a,1) 9223372036854775807 +ROUND(a,10) 9223372036854775807 +TRUNCATE(a,0) 9223372036854775807 +FLOOR(a) 9223372036854775807 +CEILING(a) 9223372036854775807 +CALL p1('bigint(22)'); + bigint(22) +Table t2 +Create Table CREATE TABLE `t2` ( + `a` bigint(22) DEFAULT NULL, + `ROUND(a)` bigint(22) DEFAULT NULL, + `ROUND(a,1)` bigint(22) DEFAULT NULL, + `ROUND(a,10)` bigint(22) DEFAULT NULL, + `TRUNCATE(a,0)` bigint(22) DEFAULT NULL, + `FLOOR(a)` bigint(22) DEFAULT NULL, + `CEILING(a)` bigint(22) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a -9223372036854775808 +ROUND(a) -9223372036854775808 +ROUND(a,1) -9223372036854775808 +ROUND(a,10) -9223372036854775808 +TRUNCATE(a,0) -9223372036854775808 +FLOOR(a) -9223372036854775808 +CEILING(a) -9223372036854775808 +a 9223372036854775807 +ROUND(a) 9223372036854775807 +ROUND(a,1) 9223372036854775807 +ROUND(a,10) 9223372036854775807 +TRUNCATE(a,0) 9223372036854775807 +FLOOR(a) 9223372036854775807 +CEILING(a) 9223372036854775807 +CALL p1('bigint(23)'); + bigint(23) +Table t2 +Create Table CREATE TABLE `t2` ( + `a` bigint(23) DEFAULT NULL, + `ROUND(a)` bigint(23) DEFAULT NULL, + `ROUND(a,1)` bigint(23) DEFAULT NULL, + `ROUND(a,10)` bigint(23) DEFAULT NULL, + `TRUNCATE(a,0)` bigint(23) DEFAULT NULL, + `FLOOR(a)` bigint(23) DEFAULT NULL, + `CEILING(a)` bigint(23) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a -9223372036854775808 +ROUND(a) -9223372036854775808 +ROUND(a,1) -9223372036854775808 +ROUND(a,10) -9223372036854775808 +TRUNCATE(a,0) -9223372036854775808 +FLOOR(a) -9223372036854775808 +CEILING(a) -9223372036854775808 +a 9223372036854775807 +ROUND(a) 9223372036854775807 +ROUND(a,1) 9223372036854775807 +ROUND(a,10) 9223372036854775807 +TRUNCATE(a,0) 9223372036854775807 +FLOOR(a) 9223372036854775807 +CEILING(a) 9223372036854775807 +CALL p1('bigint(30)'); + bigint(30) +Table t2 +Create Table CREATE TABLE `t2` ( + `a` bigint(30) DEFAULT NULL, + `ROUND(a)` bigint(30) DEFAULT NULL, + `ROUND(a,1)` bigint(30) DEFAULT NULL, + `ROUND(a,10)` bigint(30) DEFAULT NULL, + `TRUNCATE(a,0)` bigint(30) DEFAULT NULL, + `FLOOR(a)` bigint(30) DEFAULT NULL, + `CEILING(a)` bigint(30) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a -9223372036854775808 +ROUND(a) -9223372036854775808 +ROUND(a,1) -9223372036854775808 +ROUND(a,10) -9223372036854775808 +TRUNCATE(a,0) -9223372036854775808 +FLOOR(a) -9223372036854775808 +CEILING(a) -9223372036854775808 +a 9223372036854775807 +ROUND(a) 9223372036854775807 +ROUND(a,1) 9223372036854775807 +ROUND(a,10) 9223372036854775807 TRUNCATE(a,0) 9223372036854775807 FLOOR(a) 9223372036854775807 CEILING(a) 9223372036854775807 @@ -532,17 +693,23 @@ Table t2 Create Table CREATE TABLE `t2` ( `a` tinyint(3) unsigned DEFAULT NULL, `ROUND(a)` tinyint(3) unsigned DEFAULT NULL, + `ROUND(a,1)` tinyint(3) unsigned DEFAULT NULL, + `ROUND(a,10)` tinyint(3) unsigned DEFAULT NULL, `TRUNCATE(a,0)` tinyint(3) unsigned DEFAULT NULL, `FLOOR(a)` tinyint(3) unsigned DEFAULT NULL, `CEILING(a)` tinyint(3) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 a 0 ROUND(a) 0 +ROUND(a,1) 0 +ROUND(a,10) 0 TRUNCATE(a,0) 0 FLOOR(a) 0 CEILING(a) 0 a 255 ROUND(a) 255 +ROUND(a,1) 255 +ROUND(a,10) 255 TRUNCATE(a,0) 255 FLOOR(a) 255 CEILING(a) 255 @@ -552,17 +719,23 @@ Table t2 Create Table CREATE TABLE `t2` ( `a` smallint(5) unsigned DEFAULT NULL, `ROUND(a)` smallint(5) unsigned DEFAULT NULL, + `ROUND(a,1)` smallint(5) unsigned DEFAULT NULL, + `ROUND(a,10)` smallint(5) unsigned DEFAULT NULL, `TRUNCATE(a,0)` smallint(5) unsigned DEFAULT NULL, `FLOOR(a)` smallint(5) unsigned DEFAULT NULL, `CEILING(a)` smallint(5) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 a 0 ROUND(a) 0 +ROUND(a,1) 0 +ROUND(a,10) 0 TRUNCATE(a,0) 0 FLOOR(a) 0 CEILING(a) 0 a 65535 ROUND(a) 65535 +ROUND(a,1) 65535 +ROUND(a,10) 65535 TRUNCATE(a,0) 65535 FLOOR(a) 65535 CEILING(a) 65535 @@ -572,17 +745,23 @@ Table t2 Create Table CREATE TABLE `t2` ( `a` mediumint(8) unsigned DEFAULT NULL, `ROUND(a)` mediumint(8) unsigned DEFAULT NULL, + `ROUND(a,1)` mediumint(8) unsigned DEFAULT NULL, + `ROUND(a,10)` mediumint(8) unsigned DEFAULT NULL, `TRUNCATE(a,0)` mediumint(8) unsigned DEFAULT NULL, `FLOOR(a)` mediumint(8) unsigned DEFAULT NULL, `CEILING(a)` mediumint(8) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 a 0 ROUND(a) 0 +ROUND(a,1) 0 +ROUND(a,10) 0 TRUNCATE(a,0) 0 FLOOR(a) 0 CEILING(a) 0 a 16777215 ROUND(a) 16777215 +ROUND(a,1) 16777215 +ROUND(a,10) 16777215 TRUNCATE(a,0) 16777215 FLOOR(a) 16777215 CEILING(a) 16777215 @@ -592,17 +771,23 @@ Table t2 Create Table CREATE TABLE `t2` ( `a` int(10) unsigned DEFAULT NULL, `ROUND(a)` int(10) unsigned DEFAULT NULL, + `ROUND(a,1)` int(10) unsigned DEFAULT NULL, + `ROUND(a,10)` int(10) unsigned DEFAULT NULL, `TRUNCATE(a,0)` int(10) unsigned DEFAULT NULL, `FLOOR(a)` int(10) unsigned DEFAULT NULL, `CEILING(a)` int(10) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 a 0 ROUND(a) 0 +ROUND(a,1) 0 +ROUND(a,10) 0 TRUNCATE(a,0) 0 FLOOR(a) 0 CEILING(a) 0 a 4294967295 ROUND(a) 4294967295 +ROUND(a,1) 4294967295 +ROUND(a,10) 4294967295 TRUNCATE(a,0) 4294967295 FLOOR(a) 4294967295 CEILING(a) 4294967295 @@ -612,22 +797,700 @@ Table t2 Create Table CREATE TABLE `t2` ( `a` bigint(20) unsigned DEFAULT NULL, `ROUND(a)` bigint(20) unsigned DEFAULT NULL, + `ROUND(a,1)` bigint(20) unsigned DEFAULT NULL, + `ROUND(a,10)` bigint(20) unsigned DEFAULT NULL, `TRUNCATE(a,0)` bigint(20) unsigned DEFAULT NULL, `FLOOR(a)` bigint(20) unsigned DEFAULT NULL, `CEILING(a)` bigint(20) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 a 0 ROUND(a) 0 +ROUND(a,1) 0 +ROUND(a,10) 0 TRUNCATE(a,0) 0 FLOOR(a) 0 CEILING(a) 0 a 18446744073709551615 ROUND(a) 18446744073709551615 +ROUND(a,1) 18446744073709551615 +ROUND(a,10) 18446744073709551615 +TRUNCATE(a,0) 18446744073709551615 +FLOOR(a) 18446744073709551615 +CEILING(a) 18446744073709551615 +CALL p1('bigint(20) unsigned'); + bigint(20) unsigned +Table t2 +Create Table CREATE TABLE `t2` ( + `a` bigint(20) unsigned DEFAULT NULL, + `ROUND(a)` bigint(20) unsigned DEFAULT NULL, + `ROUND(a,1)` bigint(20) unsigned DEFAULT NULL, + `ROUND(a,10)` bigint(20) unsigned DEFAULT NULL, + `TRUNCATE(a,0)` bigint(20) unsigned DEFAULT NULL, + `FLOOR(a)` bigint(20) unsigned DEFAULT NULL, + `CEILING(a)` bigint(20) unsigned DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 0 +ROUND(a) 0 +ROUND(a,1) 0 +ROUND(a,10) 0 +TRUNCATE(a,0) 0 +FLOOR(a) 0 +CEILING(a) 0 +a 18446744073709551615 +ROUND(a) 18446744073709551615 +ROUND(a,1) 18446744073709551615 +ROUND(a,10) 18446744073709551615 +TRUNCATE(a,0) 18446744073709551615 +FLOOR(a) 18446744073709551615 +CEILING(a) 18446744073709551615 +CALL p1('bigint(21) unsigned'); + bigint(21) unsigned +Table t2 +Create Table CREATE TABLE `t2` ( + `a` bigint(21) unsigned DEFAULT NULL, + `ROUND(a)` bigint(21) unsigned DEFAULT NULL, + `ROUND(a,1)` bigint(21) unsigned DEFAULT NULL, + `ROUND(a,10)` bigint(21) unsigned DEFAULT NULL, + `TRUNCATE(a,0)` bigint(21) unsigned DEFAULT NULL, + `FLOOR(a)` bigint(21) unsigned DEFAULT NULL, + `CEILING(a)` bigint(21) unsigned DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 0 +ROUND(a) 0 +ROUND(a,1) 0 +ROUND(a,10) 0 +TRUNCATE(a,0) 0 +FLOOR(a) 0 +CEILING(a) 0 +a 18446744073709551615 +ROUND(a) 18446744073709551615 +ROUND(a,1) 18446744073709551615 +ROUND(a,10) 18446744073709551615 +TRUNCATE(a,0) 18446744073709551615 +FLOOR(a) 18446744073709551615 +CEILING(a) 18446744073709551615 +CALL p1('bigint(22) unsigned'); + bigint(22) unsigned +Table t2 +Create Table CREATE TABLE `t2` ( + `a` bigint(22) unsigned DEFAULT NULL, + `ROUND(a)` bigint(22) unsigned DEFAULT NULL, + `ROUND(a,1)` bigint(22) unsigned DEFAULT NULL, + `ROUND(a,10)` bigint(22) unsigned DEFAULT NULL, + `TRUNCATE(a,0)` bigint(22) unsigned DEFAULT NULL, + `FLOOR(a)` bigint(22) unsigned DEFAULT NULL, + `CEILING(a)` bigint(22) unsigned DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 0 +ROUND(a) 0 +ROUND(a,1) 0 +ROUND(a,10) 0 +TRUNCATE(a,0) 0 +FLOOR(a) 0 +CEILING(a) 0 +a 18446744073709551615 +ROUND(a) 18446744073709551615 +ROUND(a,1) 18446744073709551615 +ROUND(a,10) 18446744073709551615 +TRUNCATE(a,0) 18446744073709551615 +FLOOR(a) 18446744073709551615 +CEILING(a) 18446744073709551615 +CALL p1('bigint(23) unsigned'); + bigint(23) unsigned +Table t2 +Create Table CREATE TABLE `t2` ( + `a` bigint(23) unsigned DEFAULT NULL, + `ROUND(a)` bigint(23) unsigned DEFAULT NULL, + `ROUND(a,1)` bigint(23) unsigned DEFAULT NULL, + `ROUND(a,10)` bigint(23) unsigned DEFAULT NULL, + `TRUNCATE(a,0)` bigint(23) unsigned DEFAULT NULL, + `FLOOR(a)` bigint(23) unsigned DEFAULT NULL, + `CEILING(a)` bigint(23) unsigned DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 0 +ROUND(a) 0 +ROUND(a,1) 0 +ROUND(a,10) 0 +TRUNCATE(a,0) 0 +FLOOR(a) 0 +CEILING(a) 0 +a 18446744073709551615 +ROUND(a) 18446744073709551615 +ROUND(a,1) 18446744073709551615 +ROUND(a,10) 18446744073709551615 +TRUNCATE(a,0) 18446744073709551615 +FLOOR(a) 18446744073709551615 +CEILING(a) 18446744073709551615 +CALL p1('bigint(30) unsigned'); + bigint(30) unsigned +Table t2 +Create Table CREATE TABLE `t2` ( + `a` bigint(30) unsigned DEFAULT NULL, + `ROUND(a)` bigint(30) unsigned DEFAULT NULL, + `ROUND(a,1)` bigint(30) unsigned DEFAULT NULL, + `ROUND(a,10)` bigint(30) unsigned DEFAULT NULL, + `TRUNCATE(a,0)` bigint(30) unsigned DEFAULT NULL, + `FLOOR(a)` bigint(30) unsigned DEFAULT NULL, + `CEILING(a)` bigint(30) unsigned DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 0 +ROUND(a) 0 +ROUND(a,1) 0 +ROUND(a,10) 0 +TRUNCATE(a,0) 0 +FLOOR(a) 0 +CEILING(a) 0 +a 18446744073709551615 +ROUND(a) 18446744073709551615 +ROUND(a,1) 18446744073709551615 +ROUND(a,10) 18446744073709551615 TRUNCATE(a,0) 18446744073709551615 FLOOR(a) 18446744073709551615 CEILING(a) 18446744073709551615 DROP PROCEDURE p1; # +# MDEV-23368 ROUND(18446744073709551615,-11) returns a wrong result +# +SELECT ROUND(18446744073709551615,-10), ROUND(18446744073709551615,-11); +ROUND(18446744073709551615,-10) ROUND(18446744073709551615,-11) +18446744070000000000 18446744100000000000 +CREATE TABLE t1 AS SELECT ROUND(18446744073709551615,-10), ROUND(18446744073709551615,-11); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `ROUND(18446744073709551615,-10)` decimal(21,0) unsigned NOT NULL, + `ROUND(18446744073709551615,-11)` decimal(21,0) unsigned NOT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +SELECT * FROM t1; +ROUND(18446744073709551615,-10) ROUND(18446744073709551615,-11) +18446744070000000000 18446744100000000000 +DROP TABLE t1; +# +# MDEV-23367 ROUND(18446744073709551615,-1) returns a wrong result +# +SELECT +ROUND(18446744073709551615,-1) AS c01, +ROUND(18446744073709551615,-19) AS c19; +c01 18446744073709551620 +c19 20000000000000000000 +CREATE OR REPLACE TABLE t1 AS +SELECT +ROUND(18446744073709551615,-1) AS c01, +ROUND(18446744073709551615,-19) AS c19; +SELECT * FROM t1; +c01 18446744073709551620 +c19 20000000000000000000 +SHOW CREATE TABLE t1; +Table t1 +Create Table CREATE TABLE `t1` ( + `c01` decimal(21,0) unsigned NOT NULL, + `c19` decimal(21,0) unsigned NOT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +CREATE PROCEDURE p1(t VARCHAR(64)) +BEGIN +SELECT t AS ``; +EXECUTE IMMEDIATE REPLACE('CREATE TABLE t1 (a TYPE)', 'TYPE', t); +INSERT IGNORE INTO t1 VALUES (-100000000000000000000000000000); +INSERT IGNORE INTO t1 VALUES (100000000000000000000000000000); +CREATE TABLE t2 AS SELECT +a, ROUND(a,-1), ROUND(a,-2), ROUND(a,-19), ROUND(a,-20), ROUND(a,-30) +FROM t1 +ORDER BY a; +SHOW CREATE TABLE t2; +SELECT * FROM t2; +DROP TABLE t1, t2; +END; +$$ +CALL p1('tinyint'); + tinyint +Table t2 +Create Table CREATE TABLE `t2` ( + `a` tinyint(4) DEFAULT NULL, + `ROUND(a,-1)` int(5) DEFAULT NULL, + `ROUND(a,-2)` int(5) DEFAULT NULL, + `ROUND(a,-19)` int(5) DEFAULT NULL, + `ROUND(a,-20)` int(5) DEFAULT NULL, + `ROUND(a,-30)` int(5) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a -128 +ROUND(a,-1) -130 +ROUND(a,-2) -100 +ROUND(a,-19) 0 +ROUND(a,-20) 0 +ROUND(a,-30) 0 +a 127 +ROUND(a,-1) 130 +ROUND(a,-2) 100 +ROUND(a,-19) 0 +ROUND(a,-20) 0 +ROUND(a,-30) 0 +CALL p1('smallint'); + smallint +Table t2 +Create Table CREATE TABLE `t2` ( + `a` smallint(6) DEFAULT NULL, + `ROUND(a,-1)` int(7) DEFAULT NULL, + `ROUND(a,-2)` int(7) DEFAULT NULL, + `ROUND(a,-19)` int(7) DEFAULT NULL, + `ROUND(a,-20)` int(7) DEFAULT NULL, + `ROUND(a,-30)` int(7) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a -32768 +ROUND(a,-1) -32770 +ROUND(a,-2) -32800 +ROUND(a,-19) 0 +ROUND(a,-20) 0 +ROUND(a,-30) 0 +a 32767 +ROUND(a,-1) 32770 +ROUND(a,-2) 32800 +ROUND(a,-19) 0 +ROUND(a,-20) 0 +ROUND(a,-30) 0 +CALL p1('mediumint'); + mediumint +Table t2 +Create Table CREATE TABLE `t2` ( + `a` mediumint(9) DEFAULT NULL, + `ROUND(a,-1)` bigint(10) DEFAULT NULL, + `ROUND(a,-2)` bigint(10) DEFAULT NULL, + `ROUND(a,-19)` bigint(10) DEFAULT NULL, + `ROUND(a,-20)` bigint(10) DEFAULT NULL, + `ROUND(a,-30)` bigint(10) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a -8388608 +ROUND(a,-1) -8388610 +ROUND(a,-2) -8388600 +ROUND(a,-19) 0 +ROUND(a,-20) 0 +ROUND(a,-30) 0 +a 8388607 +ROUND(a,-1) 8388610 +ROUND(a,-2) 8388600 +ROUND(a,-19) 0 +ROUND(a,-20) 0 +ROUND(a,-30) 0 +CALL p1('int'); + int +Table t2 +Create Table CREATE TABLE `t2` ( + `a` int(11) DEFAULT NULL, + `ROUND(a,-1)` bigint(12) DEFAULT NULL, + `ROUND(a,-2)` bigint(12) DEFAULT NULL, + `ROUND(a,-19)` bigint(12) DEFAULT NULL, + `ROUND(a,-20)` bigint(12) DEFAULT NULL, + `ROUND(a,-30)` bigint(12) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a -2147483648 +ROUND(a,-1) -2147483650 +ROUND(a,-2) -2147483600 +ROUND(a,-19) 0 +ROUND(a,-20) 0 +ROUND(a,-30) 0 +a 2147483647 +ROUND(a,-1) 2147483650 +ROUND(a,-2) 2147483600 +ROUND(a,-19) 0 +ROUND(a,-20) 0 +ROUND(a,-30) 0 +CALL p1('bigint'); + bigint +Table t2 +Create Table CREATE TABLE `t2` ( + `a` bigint(20) DEFAULT NULL, + `ROUND(a,-1)` decimal(20,0) DEFAULT NULL, + `ROUND(a,-2)` decimal(20,0) DEFAULT NULL, + `ROUND(a,-19)` decimal(20,0) DEFAULT NULL, + `ROUND(a,-20)` decimal(20,0) DEFAULT NULL, + `ROUND(a,-30)` decimal(20,0) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a -9223372036854775808 +ROUND(a,-1) -9223372036854775810 +ROUND(a,-2) -9223372036854775800 +ROUND(a,-19) -10000000000000000000 +ROUND(a,-20) 0 +ROUND(a,-30) 0 +a 9223372036854775807 +ROUND(a,-1) 9223372036854775810 +ROUND(a,-2) 9223372036854775800 +ROUND(a,-19) 10000000000000000000 +ROUND(a,-20) 0 +ROUND(a,-30) 0 +CALL p1('bigint(20)'); + bigint(20) +Table t2 +Create Table CREATE TABLE `t2` ( + `a` bigint(20) DEFAULT NULL, + `ROUND(a,-1)` decimal(20,0) DEFAULT NULL, + `ROUND(a,-2)` decimal(20,0) DEFAULT NULL, + `ROUND(a,-19)` decimal(20,0) DEFAULT NULL, + `ROUND(a,-20)` decimal(20,0) DEFAULT NULL, + `ROUND(a,-30)` decimal(20,0) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a -9223372036854775808 +ROUND(a,-1) -9223372036854775810 +ROUND(a,-2) -9223372036854775800 +ROUND(a,-19) -10000000000000000000 +ROUND(a,-20) 0 +ROUND(a,-30) 0 +a 9223372036854775807 +ROUND(a,-1) 9223372036854775810 +ROUND(a,-2) 9223372036854775800 +ROUND(a,-19) 10000000000000000000 +ROUND(a,-20) 0 +ROUND(a,-30) 0 +CALL p1('bigint(21)'); + bigint(21) +Table t2 +Create Table CREATE TABLE `t2` ( + `a` bigint(21) DEFAULT NULL, + `ROUND(a,-1)` decimal(21,0) DEFAULT NULL, + `ROUND(a,-2)` decimal(21,0) DEFAULT NULL, + `ROUND(a,-19)` decimal(21,0) DEFAULT NULL, + `ROUND(a,-20)` decimal(21,0) DEFAULT NULL, + `ROUND(a,-30)` decimal(21,0) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a -9223372036854775808 +ROUND(a,-1) -9223372036854775810 +ROUND(a,-2) -9223372036854775800 +ROUND(a,-19) -10000000000000000000 +ROUND(a,-20) 0 +ROUND(a,-30) 0 +a 9223372036854775807 +ROUND(a,-1) 9223372036854775810 +ROUND(a,-2) 9223372036854775800 +ROUND(a,-19) 10000000000000000000 +ROUND(a,-20) 0 +ROUND(a,-30) 0 +CALL p1('bigint(22)'); + bigint(22) +Table t2 +Create Table CREATE TABLE `t2` ( + `a` bigint(22) DEFAULT NULL, + `ROUND(a,-1)` decimal(22,0) DEFAULT NULL, + `ROUND(a,-2)` decimal(22,0) DEFAULT NULL, + `ROUND(a,-19)` decimal(22,0) DEFAULT NULL, + `ROUND(a,-20)` decimal(22,0) DEFAULT NULL, + `ROUND(a,-30)` decimal(22,0) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a -9223372036854775808 +ROUND(a,-1) -9223372036854775810 +ROUND(a,-2) -9223372036854775800 +ROUND(a,-19) -10000000000000000000 +ROUND(a,-20) 0 +ROUND(a,-30) 0 +a 9223372036854775807 +ROUND(a,-1) 9223372036854775810 +ROUND(a,-2) 9223372036854775800 +ROUND(a,-19) 10000000000000000000 +ROUND(a,-20) 0 +ROUND(a,-30) 0 +CALL p1('bigint(23)'); + bigint(23) +Table t2 +Create Table CREATE TABLE `t2` ( + `a` bigint(23) DEFAULT NULL, + `ROUND(a,-1)` decimal(23,0) DEFAULT NULL, + `ROUND(a,-2)` decimal(23,0) DEFAULT NULL, + `ROUND(a,-19)` decimal(23,0) DEFAULT NULL, + `ROUND(a,-20)` decimal(23,0) DEFAULT NULL, + `ROUND(a,-30)` decimal(23,0) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a -9223372036854775808 +ROUND(a,-1) -9223372036854775810 +ROUND(a,-2) -9223372036854775800 +ROUND(a,-19) -10000000000000000000 +ROUND(a,-20) 0 +ROUND(a,-30) 0 +a 9223372036854775807 +ROUND(a,-1) 9223372036854775810 +ROUND(a,-2) 9223372036854775800 +ROUND(a,-19) 10000000000000000000 +ROUND(a,-20) 0 +ROUND(a,-30) 0 +CALL p1('bigint(30)'); + bigint(30) +Table t2 +Create Table CREATE TABLE `t2` ( + `a` bigint(30) DEFAULT NULL, + `ROUND(a,-1)` decimal(30,0) DEFAULT NULL, + `ROUND(a,-2)` decimal(30,0) DEFAULT NULL, + `ROUND(a,-19)` decimal(30,0) DEFAULT NULL, + `ROUND(a,-20)` decimal(30,0) DEFAULT NULL, + `ROUND(a,-30)` decimal(30,0) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a -9223372036854775808 +ROUND(a,-1) -9223372036854775810 +ROUND(a,-2) -9223372036854775800 +ROUND(a,-19) -10000000000000000000 +ROUND(a,-20) 0 +ROUND(a,-30) 0 +a 9223372036854775807 +ROUND(a,-1) 9223372036854775810 +ROUND(a,-2) 9223372036854775800 +ROUND(a,-19) 10000000000000000000 +ROUND(a,-20) 0 +ROUND(a,-30) 0 +CALL p1('tinyint unsigned'); + tinyint unsigned +Table t2 +Create Table CREATE TABLE `t2` ( + `a` tinyint(3) unsigned DEFAULT NULL, + `ROUND(a,-1)` int(4) unsigned DEFAULT NULL, + `ROUND(a,-2)` int(4) unsigned DEFAULT NULL, + `ROUND(a,-19)` int(4) unsigned DEFAULT NULL, + `ROUND(a,-20)` int(4) unsigned DEFAULT NULL, + `ROUND(a,-30)` int(4) unsigned DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 0 +ROUND(a,-1) 0 +ROUND(a,-2) 0 +ROUND(a,-19) 0 +ROUND(a,-20) 0 +ROUND(a,-30) 0 +a 255 +ROUND(a,-1) 260 +ROUND(a,-2) 300 +ROUND(a,-19) 0 +ROUND(a,-20) 0 +ROUND(a,-30) 0 +CALL p1('smallint unsigned'); + smallint unsigned +Table t2 +Create Table CREATE TABLE `t2` ( + `a` smallint(5) unsigned DEFAULT NULL, + `ROUND(a,-1)` int(6) unsigned DEFAULT NULL, + `ROUND(a,-2)` int(6) unsigned DEFAULT NULL, + `ROUND(a,-19)` int(6) unsigned DEFAULT NULL, + `ROUND(a,-20)` int(6) unsigned DEFAULT NULL, + `ROUND(a,-30)` int(6) unsigned DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 0 +ROUND(a,-1) 0 +ROUND(a,-2) 0 +ROUND(a,-19) 0 +ROUND(a,-20) 0 +ROUND(a,-30) 0 +a 65535 +ROUND(a,-1) 65540 +ROUND(a,-2) 65500 +ROUND(a,-19) 0 +ROUND(a,-20) 0 +ROUND(a,-30) 0 +CALL p1('mediumint unsigned'); + mediumint unsigned +Table t2 +Create Table CREATE TABLE `t2` ( + `a` mediumint(8) unsigned DEFAULT NULL, + `ROUND(a,-1)` int(9) unsigned DEFAULT NULL, + `ROUND(a,-2)` int(9) unsigned DEFAULT NULL, + `ROUND(a,-19)` int(9) unsigned DEFAULT NULL, + `ROUND(a,-20)` int(9) unsigned DEFAULT NULL, + `ROUND(a,-30)` int(9) unsigned DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 0 +ROUND(a,-1) 0 +ROUND(a,-2) 0 +ROUND(a,-19) 0 +ROUND(a,-20) 0 +ROUND(a,-30) 0 +a 16777215 +ROUND(a,-1) 16777220 +ROUND(a,-2) 16777200 +ROUND(a,-19) 0 +ROUND(a,-20) 0 +ROUND(a,-30) 0 +CALL p1('int unsigned'); + int unsigned +Table t2 +Create Table CREATE TABLE `t2` ( + `a` int(10) unsigned DEFAULT NULL, + `ROUND(a,-1)` bigint(11) unsigned DEFAULT NULL, + `ROUND(a,-2)` bigint(11) unsigned DEFAULT NULL, + `ROUND(a,-19)` bigint(11) unsigned DEFAULT NULL, + `ROUND(a,-20)` bigint(11) unsigned DEFAULT NULL, + `ROUND(a,-30)` bigint(11) unsigned DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 0 +ROUND(a,-1) 0 +ROUND(a,-2) 0 +ROUND(a,-19) 0 +ROUND(a,-20) 0 +ROUND(a,-30) 0 +a 4294967295 +ROUND(a,-1) 4294967300 +ROUND(a,-2) 4294967300 +ROUND(a,-19) 0 +ROUND(a,-20) 0 +ROUND(a,-30) 0 +CALL p1('bigint unsigned'); + bigint unsigned +Table t2 +Create Table CREATE TABLE `t2` ( + `a` bigint(20) unsigned DEFAULT NULL, + `ROUND(a,-1)` decimal(21,0) unsigned DEFAULT NULL, + `ROUND(a,-2)` decimal(21,0) unsigned DEFAULT NULL, + `ROUND(a,-19)` decimal(21,0) unsigned DEFAULT NULL, + `ROUND(a,-20)` decimal(21,0) unsigned DEFAULT NULL, + `ROUND(a,-30)` decimal(21,0) unsigned DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 0 +ROUND(a,-1) 0 +ROUND(a,-2) 0 +ROUND(a,-19) 0 +ROUND(a,-20) 0 +ROUND(a,-30) 0 +a 18446744073709551615 +ROUND(a,-1) 18446744073709551620 +ROUND(a,-2) 18446744073709551600 +ROUND(a,-19) 20000000000000000000 +ROUND(a,-20) 0 +ROUND(a,-30) 0 +CALL p1('bigint(20) unsigned'); + bigint(20) unsigned +Table t2 +Create Table CREATE TABLE `t2` ( + `a` bigint(20) unsigned DEFAULT NULL, + `ROUND(a,-1)` decimal(21,0) unsigned DEFAULT NULL, + `ROUND(a,-2)` decimal(21,0) unsigned DEFAULT NULL, + `ROUND(a,-19)` decimal(21,0) unsigned DEFAULT NULL, + `ROUND(a,-20)` decimal(21,0) unsigned DEFAULT NULL, + `ROUND(a,-30)` decimal(21,0) unsigned DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 0 +ROUND(a,-1) 0 +ROUND(a,-2) 0 +ROUND(a,-19) 0 +ROUND(a,-20) 0 +ROUND(a,-30) 0 +a 18446744073709551615 +ROUND(a,-1) 18446744073709551620 +ROUND(a,-2) 18446744073709551600 +ROUND(a,-19) 20000000000000000000 +ROUND(a,-20) 0 +ROUND(a,-30) 0 +CALL p1('bigint(21) unsigned'); + bigint(21) unsigned +Table t2 +Create Table CREATE TABLE `t2` ( + `a` bigint(21) unsigned DEFAULT NULL, + `ROUND(a,-1)` decimal(22,0) unsigned DEFAULT NULL, + `ROUND(a,-2)` decimal(22,0) unsigned DEFAULT NULL, + `ROUND(a,-19)` decimal(22,0) unsigned DEFAULT NULL, + `ROUND(a,-20)` decimal(22,0) unsigned DEFAULT NULL, + `ROUND(a,-30)` decimal(22,0) unsigned DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 0 +ROUND(a,-1) 0 +ROUND(a,-2) 0 +ROUND(a,-19) 0 +ROUND(a,-20) 0 +ROUND(a,-30) 0 +a 18446744073709551615 +ROUND(a,-1) 18446744073709551620 +ROUND(a,-2) 18446744073709551600 +ROUND(a,-19) 20000000000000000000 +ROUND(a,-20) 0 +ROUND(a,-30) 0 +CALL p1('bigint(22) unsigned'); + bigint(22) unsigned +Table t2 +Create Table CREATE TABLE `t2` ( + `a` bigint(22) unsigned DEFAULT NULL, + `ROUND(a,-1)` decimal(23,0) unsigned DEFAULT NULL, + `ROUND(a,-2)` decimal(23,0) unsigned DEFAULT NULL, + `ROUND(a,-19)` decimal(23,0) unsigned DEFAULT NULL, + `ROUND(a,-20)` decimal(23,0) unsigned DEFAULT NULL, + `ROUND(a,-30)` decimal(23,0) unsigned DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 0 +ROUND(a,-1) 0 +ROUND(a,-2) 0 +ROUND(a,-19) 0 +ROUND(a,-20) 0 +ROUND(a,-30) 0 +a 18446744073709551615 +ROUND(a,-1) 18446744073709551620 +ROUND(a,-2) 18446744073709551600 +ROUND(a,-19) 20000000000000000000 +ROUND(a,-20) 0 +ROUND(a,-30) 0 +CALL p1('bigint(23) unsigned'); + bigint(23) unsigned +Table t2 +Create Table CREATE TABLE `t2` ( + `a` bigint(23) unsigned DEFAULT NULL, + `ROUND(a,-1)` decimal(24,0) unsigned DEFAULT NULL, + `ROUND(a,-2)` decimal(24,0) unsigned DEFAULT NULL, + `ROUND(a,-19)` decimal(24,0) unsigned DEFAULT NULL, + `ROUND(a,-20)` decimal(24,0) unsigned DEFAULT NULL, + `ROUND(a,-30)` decimal(24,0) unsigned DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 0 +ROUND(a,-1) 0 +ROUND(a,-2) 0 +ROUND(a,-19) 0 +ROUND(a,-20) 0 +ROUND(a,-30) 0 +a 18446744073709551615 +ROUND(a,-1) 18446744073709551620 +ROUND(a,-2) 18446744073709551600 +ROUND(a,-19) 20000000000000000000 +ROUND(a,-20) 0 +ROUND(a,-30) 0 +CALL p1('bigint(30) unsigned'); + bigint(30) unsigned +Table t2 +Create Table CREATE TABLE `t2` ( + `a` bigint(30) unsigned DEFAULT NULL, + `ROUND(a,-1)` decimal(31,0) unsigned DEFAULT NULL, + `ROUND(a,-2)` decimal(31,0) unsigned DEFAULT NULL, + `ROUND(a,-19)` decimal(31,0) unsigned DEFAULT NULL, + `ROUND(a,-20)` decimal(31,0) unsigned DEFAULT NULL, + `ROUND(a,-30)` decimal(31,0) unsigned DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 0 +ROUND(a,-1) 0 +ROUND(a,-2) 0 +ROUND(a,-19) 0 +ROUND(a,-20) 0 +ROUND(a,-30) 0 +a 18446744073709551615 +ROUND(a,-1) 18446744073709551620 +ROUND(a,-2) 18446744073709551600 +ROUND(a,-19) 20000000000000000000 +ROUND(a,-20) 0 +ROUND(a,-30) 0 +DROP PROCEDURE p1; +# +# MDEV-23366 ROUND(18446744073709551615,rand()*0) returns a wrong result +# +SELECT +ROUND(18446744073709551615,NULL) AS c1, +ROUND(18446744073709551615,rand()*0) AS c2, +ROUND(18446744073709551615,rand()*0-19) AS c3; +c1 NULL +c2 18446744073709551615 +c3 20000000000000000000 +CREATE OR REPLACE TABLE t1 AS +SELECT +ROUND(18446744073709551615,NULL) AS c1, +ROUND(18446744073709551615,rand()*0) AS c2, +ROUND(18446744073709551615,rand()*0-19) AS c3; +SELECT * FROM t1; +c1 NULL +c2 18446744073709551615 +c3 20000000000000000000 +SHOW CREATE TABLE t1; +Table t1 +Create Table CREATE TABLE `t1` ( + `c1` bigint(20) unsigned DEFAULT NULL, + `c2` decimal(21,0) unsigned NOT NULL, + `c3` decimal(21,0) unsigned NOT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +# # End of 10.4 tests # # diff --git a/mysql-test/main/type_int.test b/mysql-test/main/type_int.test index 04cb9edf606..c2edb0a9756 100644 --- a/mysql-test/main/type_int.test +++ b/mysql-test/main/type_int.test @@ -295,6 +295,7 @@ DROP TABLE t1; --echo # --echo # MDEV-23337 Rounding functions create a wrong data type for numeric input +--echo # MDEV-23350 ROUND(bigint_22_or_longer) returns a wrong data type --echo # DELIMITER $$; @@ -304,7 +305,7 @@ BEGIN EXECUTE IMMEDIATE REPLACE('CREATE TABLE t1 (a TYPE)', 'TYPE', t); INSERT IGNORE INTO t1 VALUES (-100000000000000000000000000000); INSERT IGNORE INTO t1 VALUES (100000000000000000000000000000); - CREATE TABLE t2 AS SELECT a, ROUND(a), TRUNCATE(a,0), FLOOR(a), CEILING(a) FROM t1; + CREATE TABLE t2 AS SELECT a, ROUND(a), ROUND(a,1), ROUND(a,10), TRUNCATE(a,0), FLOOR(a), CEILING(a) FROM t1; SHOW CREATE TABLE t2; SELECT * FROM t2; DROP TABLE t1, t2; @@ -318,17 +319,123 @@ CALL p1('smallint'); CALL p1('mediumint'); CALL p1('int'); CALL p1('bigint'); +CALL p1('bigint(20)'); +CALL p1('bigint(21)'); +CALL p1('bigint(22)'); +CALL p1('bigint(23)'); +CALL p1('bigint(30)'); CALL p1('tinyint unsigned'); CALL p1('smallint unsigned'); CALL p1('mediumint unsigned'); CALL p1('int unsigned'); CALL p1('bigint unsigned'); +CALL p1('bigint(20) unsigned'); +CALL p1('bigint(21) unsigned'); +CALL p1('bigint(22) unsigned'); +CALL p1('bigint(23) unsigned'); +CALL p1('bigint(30) unsigned'); --horizontal_results DROP PROCEDURE p1; --echo # +--echo # MDEV-23368 ROUND(18446744073709551615,-11) returns a wrong result +--echo # + +SELECT ROUND(18446744073709551615,-10), ROUND(18446744073709551615,-11); +CREATE TABLE t1 AS SELECT ROUND(18446744073709551615,-10), ROUND(18446744073709551615,-11); +SHOW CREATE TABLE t1; +SELECT * FROM t1; +DROP TABLE t1; + +--echo # +--echo # MDEV-23367 ROUND(18446744073709551615,-1) returns a wrong result +--echo # + +--vertical_results +SELECT + ROUND(18446744073709551615,-1) AS c01, + ROUND(18446744073709551615,-19) AS c19; + +CREATE OR REPLACE TABLE t1 AS +SELECT + ROUND(18446744073709551615,-1) AS c01, + ROUND(18446744073709551615,-19) AS c19; + +SELECT * FROM t1; +SHOW CREATE TABLE t1; +DROP TABLE t1; +--horizontal_results + +DELIMITER $$; +CREATE PROCEDURE p1(t VARCHAR(64)) +BEGIN + SELECT t AS ``; + EXECUTE IMMEDIATE REPLACE('CREATE TABLE t1 (a TYPE)', 'TYPE', t); + INSERT IGNORE INTO t1 VALUES (-100000000000000000000000000000); + INSERT IGNORE INTO t1 VALUES (100000000000000000000000000000); + CREATE TABLE t2 AS SELECT + a, ROUND(a,-1), ROUND(a,-2), ROUND(a,-19), ROUND(a,-20), ROUND(a,-30) + FROM t1 + ORDER BY a; + SHOW CREATE TABLE t2; + SELECT * FROM t2; + DROP TABLE t1, t2; +END; +$$ +DELIMITER ;$$ + +--vertical_results +CALL p1('tinyint'); +CALL p1('smallint'); +CALL p1('mediumint'); +CALL p1('int'); +CALL p1('bigint'); +CALL p1('bigint(20)'); +CALL p1('bigint(21)'); +CALL p1('bigint(22)'); +CALL p1('bigint(23)'); +CALL p1('bigint(30)'); + +CALL p1('tinyint unsigned'); +CALL p1('smallint unsigned'); +CALL p1('mediumint unsigned'); +CALL p1('int unsigned'); +CALL p1('bigint unsigned'); +CALL p1('bigint(20) unsigned'); +CALL p1('bigint(21) unsigned'); +CALL p1('bigint(22) unsigned'); +CALL p1('bigint(23) unsigned'); +CALL p1('bigint(30) unsigned'); +--horizontal_results + +DROP PROCEDURE p1; + + +--echo # +--echo # MDEV-23366 ROUND(18446744073709551615,rand()*0) returns a wrong result +--echo # + +--vertical_results +SELECT + ROUND(18446744073709551615,NULL) AS c1, + ROUND(18446744073709551615,rand()*0) AS c2, + ROUND(18446744073709551615,rand()*0-19) AS c3; + +CREATE OR REPLACE TABLE t1 AS +SELECT + ROUND(18446744073709551615,NULL) AS c1, + ROUND(18446744073709551615,rand()*0) AS c2, + ROUND(18446744073709551615,rand()*0-19) AS c3; + +SELECT * FROM t1; +SHOW CREATE TABLE t1; +DROP TABLE t1; +--horizontal_results + + +--echo # --echo # End of 10.4 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 |