diff options
Diffstat (limited to 'mysql-test')
60 files changed, 1453 insertions, 175 deletions
diff --git a/mysql-test/extra/rpl_tests/rpl_charset.test b/mysql-test/extra/rpl_tests/rpl_charset.test index 37ca2e28eec..e67122cbb4c 100644 --- a/mysql-test/extra/rpl_tests/rpl_charset.test +++ b/mysql-test/extra/rpl_tests/rpl_charset.test @@ -117,4 +117,32 @@ update t1 set pk='test' where pk=@p; drop table t1; # End of 4.1 tests + + +--echo # +--echo # Start of 10.3 tests +--echo # + + +--echo # +--echo # MDEV-12685 Oracle-compatible function CHR() +--echo # + +connection master; +CREATE DATABASE db1 DEFAULT CHARACTER SET latin1 COLLATE latin1_bin; +USE db1; +CREATE TABLE t1 AS SELECT CHR(0x60); +sync_slave_with_master; +SHOW CREATE TABLE db1.t1; +connection master; +USE test; +DROP DATABASE db1; +sync_slave_with_master; + + +--echo # +--echo # End of 10.3 tests +--echo # + + --source include/rpl_end.inc diff --git a/mysql-test/r/cte_recursive.result b/mysql-test/r/cte_recursive.result index f691db27239..946ba16ac5c 100644 --- a/mysql-test/r/cte_recursive.result +++ b/mysql-test/r/cte_recursive.result @@ -130,7 +130,7 @@ select t2.a+1 from t1,t2 where t1.a=t2.a select * from t1; show columns from v1; Field Type Null Key Default Extra -a bigint(20) YES NULL +a bigint(12) YES NULL # WITH RECURSIVE : types of t1 columns are determined by anchor parts create view v2 as with recursive diff --git a/mysql-test/r/ctype_binary.result b/mysql-test/r/ctype_binary.result index 6c16b10f1ee..79080bde8ed 100644 --- a/mysql-test/r/ctype_binary.result +++ b/mysql-test/r/ctype_binary.result @@ -3037,7 +3037,7 @@ EXPLAIN EXTENDED SELECT a, LENGTH(a) FROM t1 WHERE a NOT LIKE 'a ' AND a='a'; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`a` AS `a`,length(`test`.`t1`.`a`) AS `LENGTH(a)` from `test`.`t1` where `test`.`t1`.`a` = 'a' +Note 1003 select `test`.`t1`.`a` AS `a`,octet_length(`test`.`t1`.`a`) AS `LENGTH(a)` from `test`.`t1` where `test`.`t1`.`a` = 'a' DROP TABLE t1; # # End of MDEV-8694 @@ -3070,7 +3070,7 @@ EXPLAIN EXTENDED SELECT a, LENGTH(a), CRC32(a) FROM t1 WHERE a='a' AND CRC32(a)= id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`a` AS `a`,length(`test`.`t1`.`a`) AS `LENGTH(a)`,crc32(`test`.`t1`.`a`) AS `CRC32(a)` from `test`.`t1` where `test`.`t1`.`a` = 'a' +Note 1003 select `test`.`t1`.`a` AS `a`,octet_length(`test`.`t1`.`a`) AS `LENGTH(a)`,crc32(`test`.`t1`.`a`) AS `CRC32(a)` from `test`.`t1` where `test`.`t1`.`a` = 'a' SELECT a, HEX(a) FROM t1 WHERE HEX(a)='61'; a HEX(a) a 61 diff --git a/mysql-test/r/ctype_cp1250_ch.result b/mysql-test/r/ctype_cp1250_ch.result index d4f75778ace..5799331f73e 100644 --- a/mysql-test/r/ctype_cp1250_ch.result +++ b/mysql-test/r/ctype_cp1250_ch.result @@ -220,7 +220,7 @@ EXPLAIN EXTENDED SELECT a, LENGTH(a) FROM t1 WHERE a NOT LIKE 'a ' AND a='a'; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`a` AS `a`,length(`test`.`t1`.`a`) AS `LENGTH(a)` from `test`.`t1` where `test`.`t1`.`a` not like 'a ' and `test`.`t1`.`a` = 'a' +Note 1003 select `test`.`t1`.`a` AS `a`,octet_length(`test`.`t1`.`a`) AS `LENGTH(a)` from `test`.`t1` where `test`.`t1`.`a` not like 'a ' and `test`.`t1`.`a` = 'a' DROP TABLE t1; # # End of MDEV-8694 diff --git a/mysql-test/r/ctype_latin1.result b/mysql-test/r/ctype_latin1.result index 4b92c8100ce..dab6bfc61df 100644 --- a/mysql-test/r/ctype_latin1.result +++ b/mysql-test/r/ctype_latin1.result @@ -7817,7 +7817,7 @@ EXPLAIN EXTENDED SELECT a, LENGTH(a) FROM t1 WHERE a NOT LIKE 'a ' AND a='a'; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`a` AS `a`,length(`test`.`t1`.`a`) AS `LENGTH(a)` from `test`.`t1` where `test`.`t1`.`a` = 'a' and `test`.`t1`.`a` not like 'a ' +Note 1003 select `test`.`t1`.`a` AS `a`,octet_length(`test`.`t1`.`a`) AS `LENGTH(a)` from `test`.`t1` where `test`.`t1`.`a` = 'a' and `test`.`t1`.`a` not like 'a ' DROP TABLE t1; # # End of MDEV-8694 @@ -7938,7 +7938,7 @@ EXPLAIN EXTENDED SELECT a, LENGTH(a) FROM t1 WHERE a NOT LIKE 'a ' AND a='a'; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`a` AS `a`,length(`test`.`t1`.`a`) AS `LENGTH(a)` from `test`.`t1` where `test`.`t1`.`a` = 'a' and `test`.`t1`.`a` not like 'a ' +Note 1003 select `test`.`t1`.`a` AS `a`,octet_length(`test`.`t1`.`a`) AS `LENGTH(a)` from `test`.`t1` where `test`.`t1`.`a` = 'a' and `test`.`t1`.`a` not like 'a ' DROP TABLE t1; # # End of MDEV-8694 @@ -8160,7 +8160,7 @@ EXPLAIN EXTENDED SELECT a, LENGTH(a), CRC32(a) FROM t1 WHERE a='a' AND CRC32(a)= id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`a` AS `a`,length(`test`.`t1`.`a`) AS `LENGTH(a)`,crc32(`test`.`t1`.`a`) AS `CRC32(a)` from `test`.`t1` where `test`.`t1`.`a` = 'a' and crc32(`test`.`t1`.`a`) = 3904355907 +Note 1003 select `test`.`t1`.`a` AS `a`,octet_length(`test`.`t1`.`a`) AS `LENGTH(a)`,crc32(`test`.`t1`.`a`) AS `CRC32(a)` from `test`.`t1` where `test`.`t1`.`a` = 'a' and crc32(`test`.`t1`.`a`) = 3904355907 SELECT a, HEX(a) FROM t1 WHERE HEX(a)='61'; a HEX(a) a 61 @@ -8188,7 +8188,7 @@ EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='a' AND LENGTH(a)=2; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 'a' and length(`test`.`t1`.`a`) = 2 +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 'a' and octet_length(`test`.`t1`.`a`) = 2 DROP TABLE t1; # # MDEV-8712 Wrong result for SELECT..WHERE latin1_bin_column=_latin1'a' AND latin1_bin_column='A' diff --git a/mysql-test/r/ctype_latin2_ch.result b/mysql-test/r/ctype_latin2_ch.result index 87357a49197..a396bc77fb6 100644 --- a/mysql-test/r/ctype_latin2_ch.result +++ b/mysql-test/r/ctype_latin2_ch.result @@ -160,7 +160,7 @@ EXPLAIN EXTENDED SELECT a, LENGTH(a) FROM t1 WHERE a NOT LIKE 'a ' AND a='a'; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`a` AS `a`,length(`test`.`t1`.`a`) AS `LENGTH(a)` from `test`.`t1` where `test`.`t1`.`a` = 'a' and `test`.`t1`.`a` not like 'a ' +Note 1003 select `test`.`t1`.`a` AS `a`,octet_length(`test`.`t1`.`a`) AS `LENGTH(a)` from `test`.`t1` where `test`.`t1`.`a` = 'a' and `test`.`t1`.`a` not like 'a ' DROP TABLE t1; # # End of MDEV-8694 diff --git a/mysql-test/r/ctype_tis620.result b/mysql-test/r/ctype_tis620.result index d5b4c632c31..cf66c81c647 100644 --- a/mysql-test/r/ctype_tis620.result +++ b/mysql-test/r/ctype_tis620.result @@ -3230,7 +3230,7 @@ EXPLAIN EXTENDED SELECT a, LENGTH(a) FROM t1 WHERE a NOT LIKE 'a ' AND a='a'; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`a` AS `a`,length(`test`.`t1`.`a`) AS `LENGTH(a)` from `test`.`t1` where `test`.`t1`.`a` = 'a' and `test`.`t1`.`a` not like 'a ' +Note 1003 select `test`.`t1`.`a` AS `a`,octet_length(`test`.`t1`.`a`) AS `LENGTH(a)` from `test`.`t1` where `test`.`t1`.`a` = 'a' and `test`.`t1`.`a` not like 'a ' DROP TABLE t1; # # End of MDEV-8694 @@ -3446,7 +3446,7 @@ EXPLAIN EXTENDED SELECT a, LENGTH(a) FROM t1 WHERE a NOT LIKE 'a ' AND a='a'; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`a` AS `a`,length(`test`.`t1`.`a`) AS `LENGTH(a)` from `test`.`t1` where `test`.`t1`.`a` = 'a' and `test`.`t1`.`a` not like 'a ' +Note 1003 select `test`.`t1`.`a` AS `a`,octet_length(`test`.`t1`.`a`) AS `LENGTH(a)` from `test`.`t1` where `test`.`t1`.`a` = 'a' and `test`.`t1`.`a` not like 'a ' DROP TABLE t1; # # End of MDEV-8694 diff --git a/mysql-test/r/ctype_uca.result b/mysql-test/r/ctype_uca.result index 6c6dceba9cb..725e744c44e 100644 --- a/mysql-test/r/ctype_uca.result +++ b/mysql-test/r/ctype_uca.result @@ -8547,7 +8547,7 @@ EXPLAIN EXTENDED SELECT a, LENGTH(a) FROM t1 WHERE a NOT LIKE 'a ' AND a='a'; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`a` AS `a`,length(`test`.`t1`.`a`) AS `LENGTH(a)` from `test`.`t1` where `test`.`t1`.`a` not like 'a ' and `test`.`t1`.`a` = 'a' +Note 1003 select `test`.`t1`.`a` AS `a`,octet_length(`test`.`t1`.`a`) AS `LENGTH(a)` from `test`.`t1` where `test`.`t1`.`a` not like 'a ' and `test`.`t1`.`a` = 'a' DROP TABLE t1; # # End of MDEV-8694 @@ -8705,7 +8705,7 @@ EXPLAIN EXTENDED SELECT a, LENGTH(a) FROM t1 WHERE a NOT LIKE 'a ' AND a='a'; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`a` AS `a`,length(`test`.`t1`.`a`) AS `LENGTH(a)` from `test`.`t1` where `test`.`t1`.`a` not like 'a ' and `test`.`t1`.`a` = 'a' +Note 1003 select `test`.`t1`.`a` AS `a`,octet_length(`test`.`t1`.`a`) AS `LENGTH(a)` from `test`.`t1` where `test`.`t1`.`a` not like 'a ' and `test`.`t1`.`a` = 'a' DROP TABLE t1; # # End of MDEV-8694 diff --git a/mysql-test/r/ctype_ucs.result b/mysql-test/r/ctype_ucs.result index 5b2fadb8168..101cfc47cf1 100644 --- a/mysql-test/r/ctype_ucs.result +++ b/mysql-test/r/ctype_ucs.result @@ -5465,7 +5465,7 @@ EXPLAIN EXTENDED SELECT a, LENGTH(a) FROM t1 WHERE a NOT LIKE 'a ' AND a='a'; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`a` AS `a`,length(`test`.`t1`.`a`) AS `LENGTH(a)` from `test`.`t1` where `test`.`t1`.`a` = 'a' and `test`.`t1`.`a` not like 'a ' +Note 1003 select `test`.`t1`.`a` AS `a`,octet_length(`test`.`t1`.`a`) AS `LENGTH(a)` from `test`.`t1` where `test`.`t1`.`a` = 'a' and `test`.`t1`.`a` not like 'a ' DROP TABLE t1; # # End of MDEV-8694 @@ -5586,7 +5586,7 @@ EXPLAIN EXTENDED SELECT a, LENGTH(a) FROM t1 WHERE a NOT LIKE 'a ' AND a='a'; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`a` AS `a`,length(`test`.`t1`.`a`) AS `LENGTH(a)` from `test`.`t1` where `test`.`t1`.`a` = 'a' and `test`.`t1`.`a` not like 'a ' +Note 1003 select `test`.`t1`.`a` AS `a`,octet_length(`test`.`t1`.`a`) AS `LENGTH(a)` from `test`.`t1` where `test`.`t1`.`a` = 'a' and `test`.`t1`.`a` not like 'a ' DROP TABLE t1; # # End of MDEV-8694 diff --git a/mysql-test/r/ctype_utf8.result b/mysql-test/r/ctype_utf8.result index f6a88d49579..87ca13cd743 100644 --- a/mysql-test/r/ctype_utf8.result +++ b/mysql-test/r/ctype_utf8.result @@ -6918,7 +6918,7 @@ EXPLAIN EXTENDED SELECT a, LENGTH(a) FROM t1 WHERE a NOT LIKE 'a ' AND a='a'; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`a` AS `a`,length(`test`.`t1`.`a`) AS `LENGTH(a)` from `test`.`t1` where `test`.`t1`.`a` = 'a' and `test`.`t1`.`a` not like 'a ' +Note 1003 select `test`.`t1`.`a` AS `a`,octet_length(`test`.`t1`.`a`) AS `LENGTH(a)` from `test`.`t1` where `test`.`t1`.`a` = 'a' and `test`.`t1`.`a` not like 'a ' DROP TABLE t1; # # End of MDEV-8694 @@ -7039,7 +7039,7 @@ EXPLAIN EXTENDED SELECT a, LENGTH(a) FROM t1 WHERE a NOT LIKE 'a ' AND a='a'; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`a` AS `a`,length(`test`.`t1`.`a`) AS `LENGTH(a)` from `test`.`t1` where `test`.`t1`.`a` not like 'a ' and `test`.`t1`.`a` = 'a' +Note 1003 select `test`.`t1`.`a` AS `a`,octet_length(`test`.`t1`.`a`) AS `LENGTH(a)` from `test`.`t1` where `test`.`t1`.`a` not like 'a ' and `test`.`t1`.`a` = 'a' DROP TABLE t1; # # End of MDEV-8694 diff --git a/mysql-test/r/default.result b/mysql-test/r/default.result index 253cb412146..c18db932afa 100644 --- a/mysql-test/r/default.result +++ b/mysql-test/r/default.result @@ -2901,7 +2901,7 @@ SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` varchar(10) DEFAULT NULL, - `b` int(11) DEFAULT length(`a`), + `b` int(11) DEFAULT octet_length(`a`), `c` int(11) DEFAULT char_length(`a`), `d` int(11) DEFAULT bit_length(`a`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 diff --git a/mysql-test/r/func_compress.result b/mysql-test/r/func_compress.result index 6857813559f..60ddd866507 100644 --- a/mysql-test/r/func_compress.result +++ b/mysql-test/r/func_compress.result @@ -22,7 +22,7 @@ explain extended select uncompressed_length(compress(@test_compress_string))=len id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 select uncompressed_length(compress(@`test_compress_string`)) = length(@`test_compress_string`) AS `uncompressed_length(compress(@test_compress_string))=length(@test_compress_string)` +Note 1003 select uncompressed_length(compress(@`test_compress_string`)) = octet_length(@`test_compress_string`) AS `uncompressed_length(compress(@test_compress_string))=length(@test_compress_string)` select uncompressed_length(compress(@test_compress_string)); uncompressed_length(compress(@test_compress_string)) 117 diff --git a/mysql-test/r/func_str.result b/mysql-test/r/func_str.result index 9d3e5587426..28ed3cef9b4 100644 --- a/mysql-test/r/func_str.result +++ b/mysql-test/r/func_str.result @@ -966,7 +966,7 @@ explain extended select length('\n\t\r\b\0\_\%\\'); id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 select length('\n \r\0\\_\\%\\') AS `length('\n\t\r\b\0\_\%\\')` +Note 1003 select octet_length('\n \r\0\\_\\%\\') AS `length('\n\t\r\b\0\_\%\\')` explain extended select bit_length('\n\t\r\b\0\_\%\\'); id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used @@ -986,7 +986,7 @@ explain extended select length('hello'); id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 select length('hello') AS `length('hello')` +Note 1003 select octet_length('hello') AS `length('hello')` explain extended select char(ascii('h')); id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used @@ -1425,7 +1425,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 const PRIMARY PRIMARY 12 const 1 100.00 Using index 1 SIMPLE t1 ref code code 13 const 3 100.00 Using where; Using index Warnings: -Note 1003 select `test`.`t1`.`code` AS `code`,'a12' AS `id` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`code` = 'a12' and length(`test`.`t1`.`code`) = 5 +Note 1003 select `test`.`t1`.`code` AS `code`,'a12' AS `id` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`code` = 'a12' and octet_length(`test`.`t1`.`code`) = 5 DROP TABLE t1,t2; select encode(NULL, NULL); encode(NULL, NULL) @@ -4843,6 +4843,22 @@ YQ== 61 Yq== 62 DROP TABLE t1; # +# MDEV-12685 Oracle-compatible function CHR() +# +select chr(65); +chr(65) +A +create database mysqltest1 CHARACTER SET = 'utf8' COLLATE = 'utf8_bin'; +use mysqltest1; +select charset(chr(65)), length(chr(65)),char_length(chr(65)); +charset(chr(65)) length(chr(65)) char_length(chr(65)) +utf8 1 1 +select charset(chr(14844588)), length(chr(14844588)),char_length(chr(14844588)); +charset(chr(14844588)) length(chr(14844588)) char_length(chr(14844588)) +utf8 3 1 +drop database mysqltest1; +use test; +# # End of 10.1 tests # # diff --git a/mysql-test/r/func_time.result b/mysql-test/r/func_time.result index a05c6a78cdb..4a37c56d28f 100644 --- a/mysql-test/r/func_time.result +++ b/mysql-test/r/func_time.result @@ -950,10 +950,10 @@ sec_to_time(1) + 0, from_unixtime(1) + 0; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `now() - now()` bigint(21) NOT NULL, - `curtime() - curtime()` bigint(12) NOT NULL, - `sec_to_time(1) + 0` bigint(12) DEFAULT NULL, - `from_unixtime(1) + 0` bigint(21) DEFAULT NULL + `now() - now()` bigint(16) NOT NULL, + `curtime() - curtime()` int(9) NOT NULL, + `sec_to_time(1) + 0` int(9) DEFAULT NULL, + `from_unixtime(1) + 0` bigint(16) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; SELECT SEC_TO_TIME(3300000); @@ -2054,11 +2054,11 @@ SEC_TO_TIME(1.123456)+0.1, SEC_TO_TIME(1.1234567)+0.1; SHOW COLUMNS FROM t1; Field Type Null Key Default Extra -SEC_TO_TIME(1)+0.1 decimal(12,1) YES NULL -SEC_TO_TIME(1.1)+0.1 decimal(13,1) YES NULL -SEC_TO_TIME(1.12)+0.1 decimal(14,2) YES NULL -SEC_TO_TIME(1.123456)+0.1 decimal(18,6) YES NULL -SEC_TO_TIME(1.1234567)+0.1 decimal(18,6) YES NULL +SEC_TO_TIME(1)+0.1 decimal(9,1) YES NULL +SEC_TO_TIME(1.1)+0.1 decimal(9,1) YES NULL +SEC_TO_TIME(1.12)+0.1 decimal(10,2) YES NULL +SEC_TO_TIME(1.123456)+0.1 decimal(14,6) YES NULL +SEC_TO_TIME(1.1234567)+0.1 decimal(14,6) YES NULL DROP TABLE t1; CREATE TABLE t1 (a DATE) ENGINE=MyISAM; INSERT INTO t1 VALUES ('2005-05-04'),('2000-02-23'); diff --git a/mysql-test/r/gis.result b/mysql-test/r/gis.result index 1a4a4bb48fd..df773a15ec2 100644 --- a/mysql-test/r/gis.result +++ b/mysql-test/r/gis.result @@ -4300,5 +4300,23 @@ t2 CREATE TABLE `t2` ( ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DROP PROCEDURE p1; # +# MDEV-12798 Item_param does not preserve exact field type in EXECUTE IMMEDIATE 'CREATE TABLE AS SELECT ?' USING POINT(1,1) +# +EXECUTE IMMEDIATE 'CREATE TABLE t1 AS SELECT ?' USING POINT(1,1); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `?` point NOT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +PREPARE stmt FROM 'CREATE OR REPLACE TABLE t1 AS SELECT ?'; +EXECUTE stmt USING POINT(1,1); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `?` point NOT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +# # End of 10.3 tests # diff --git a/mysql-test/r/metadata.result b/mysql-test/r/metadata.result index 3ff332e03cc..cc249a7eab7 100644 --- a/mysql-test/r/metadata.result +++ b/mysql-test/r/metadata.result @@ -147,7 +147,7 @@ id data data 2 female no select t1.id from t1 union select t2.id from t2; Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr -def id id 1 4 1 Y 32768 0 63 +def id id 246 4 1 Y 32768 0 63 id 1 2 @@ -168,12 +168,12 @@ def aaa @arg00 @arg00 8 20 1 Y 32768 0 63 1 select 1 union select 1; Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr -def 1 1 3 11 1 N 32769 0 63 +def 1 1 3 1 1 N 32769 0 63 1 1 select * from (select 1 union select 1) aaa; Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr -def aaa 1 1 3 11 1 N 32769 0 63 +def aaa 1 1 3 1 1 N 32769 0 63 1 1 drop table t1; diff --git a/mysql-test/r/mysqld--help.result b/mysql-test/r/mysqld--help.result index d1bcd243ae9..564269319cb 100644 --- a/mysql-test/r/mysqld--help.result +++ b/mysql-test/r/mysqld--help.result @@ -1404,7 +1404,7 @@ performance-schema-max-rwlock-instances -1 performance-schema-max-socket-classes 10 performance-schema-max-socket-instances -1 performance-schema-max-stage-classes 150 -performance-schema-max-statement-classes 189 +performance-schema-max-statement-classes 190 performance-schema-max-table-handles -1 performance-schema-max-table-instances -1 performance-schema-max-thread-classes 50 diff --git a/mysql-test/r/range.result b/mysql-test/r/range.result index 28f5cf635d0..735a0069ba0 100644 --- a/mysql-test/r/range.result +++ b/mysql-test/r/range.result @@ -2581,7 +2581,7 @@ EXPLAIN "ref": ["test.t1.a"], "rows": 12, "filtered": 100, - "attached_condition": "(t1.a,t2.e) in (<cache>((3,3)),<cache>((7,7)),<cache>((8,8))) and length(t2.f) = 1" + "attached_condition": "(t1.a,t2.e) in (<cache>((3,3)),<cache>((7,7)),<cache>((8,8))) and octet_length(t2.f) = 1" } } } @@ -2666,7 +2666,7 @@ EXPLAIN "used_key_parts": ["e"], "rows": 6, "filtered": 100, - "attached_condition": "(t2.d,t2.e) in (<cache>((4,4)),<cache>((7,7)),<cache>((8,8))) and length(t2.f) = 1 and t2.d is not null" + "attached_condition": "(t2.d,t2.e) in (<cache>((4,4)),<cache>((7,7)),<cache>((8,8))) and octet_length(t2.f) = 1 and t2.d is not null" }, "table": { "table_name": "t1", @@ -2716,7 +2716,7 @@ EXPLAIN "rows": 5, "filtered": 100, "index_condition": "t2.d is not null", - "attached_condition": "(t2.d,t2.e) in (<cache>((4,4)),<cache>((7,7)),<cache>((8,8))) and length(t2.f) = 1" + "attached_condition": "(t2.d,t2.e) in (<cache>((4,4)),<cache>((7,7)),<cache>((8,8))) and octet_length(t2.f) = 1" }, "table": { "table_name": "t1", @@ -2776,7 +2776,7 @@ EXPLAIN "ref": ["test.t1.a"], "rows": 3, "filtered": 100, - "attached_condition": "(t1.a,t2.e) in ((4,t1.a + 1),(7,t1.a + 1),(8,t1.a + 1)) and length(t2.f) = 1" + "attached_condition": "(t1.a,t2.e) in ((4,t1.a + 1),(7,t1.a + 1),(8,t1.a + 1)) and octet_length(t2.f) = 1" } } } @@ -2830,7 +2830,7 @@ EXPLAIN "ref": ["test.t1.a"], "rows": 3, "filtered": 100, - "attached_condition": "(t1.a,t2.e) in ((t2.e,t1.a + 1),<cache>((7,7)),<cache>((8,8))) and length(t2.f) = 1" + "attached_condition": "(t1.a,t2.e) in ((t2.e,t1.a + 1),<cache>((7,7)),<cache>((8,8))) and octet_length(t2.f) = 1" } } } @@ -2870,7 +2870,7 @@ EXPLAIN "rows": 13, "filtered": 100, "index_condition": "t1.a is not null", - "attached_condition": "(t1.a,2) in (<cache>((2,2)),<cache>((7,7)),<cache>((8,8))) and length(t1.c) = 1" + "attached_condition": "(t1.a,2) in (<cache>((2,2)),<cache>((7,7)),<cache>((8,8))) and octet_length(t1.c) = 1" }, "table": { "table_name": "t2", @@ -2882,7 +2882,7 @@ EXPLAIN "ref": ["test.t1.a"], "rows": 3, "filtered": 100, - "attached_condition": "length(t2.f) = 1" + "attached_condition": "octet_length(t2.f) = 1" } } } @@ -2958,7 +2958,7 @@ EXPLAIN "rows": 13, "filtered": 100, "index_condition": "t1.a is not null", - "attached_condition": "(t1.a,1 + 1) in (<cache>((2,2)),<cache>((7,7)),<cache>((8,8))) and length(t1.c) = 1" + "attached_condition": "(t1.a,1 + 1) in (<cache>((2,2)),<cache>((7,7)),<cache>((8,8))) and octet_length(t1.c) = 1" }, "table": { "table_name": "t2", @@ -2970,7 +2970,7 @@ EXPLAIN "ref": ["test.t1.a"], "rows": 3, "filtered": 100, - "attached_condition": "length(t2.f) = 1" + "attached_condition": "octet_length(t2.f) = 1" } } } diff --git a/mysql-test/r/range_mrr_icp.result b/mysql-test/r/range_mrr_icp.result index f2860aaab76..afce3c66c21 100644 --- a/mysql-test/r/range_mrr_icp.result +++ b/mysql-test/r/range_mrr_icp.result @@ -2588,7 +2588,7 @@ EXPLAIN "ref": ["test.t1.a"], "rows": 12, "filtered": 100, - "attached_condition": "(t1.a,t2.e) in (<cache>((3,3)),<cache>((7,7)),<cache>((8,8))) and length(t2.f) = 1" + "attached_condition": "(t1.a,t2.e) in (<cache>((3,3)),<cache>((7,7)),<cache>((8,8))) and octet_length(t2.f) = 1" } } } @@ -2673,7 +2673,7 @@ EXPLAIN "used_key_parts": ["e"], "rows": 6, "filtered": 100, - "attached_condition": "(t2.d,t2.e) in (<cache>((4,4)),<cache>((7,7)),<cache>((8,8))) and length(t2.f) = 1 and t2.d is not null", + "attached_condition": "(t2.d,t2.e) in (<cache>((4,4)),<cache>((7,7)),<cache>((8,8))) and octet_length(t2.f) = 1 and t2.d is not null", "mrr_type": "Rowid-ordered scan" }, "table": { @@ -2724,7 +2724,7 @@ EXPLAIN "rows": 5, "filtered": 100, "index_condition": "t2.d is not null", - "attached_condition": "(t2.d,t2.e) in (<cache>((4,4)),<cache>((7,7)),<cache>((8,8))) and length(t2.f) = 1", + "attached_condition": "(t2.d,t2.e) in (<cache>((4,4)),<cache>((7,7)),<cache>((8,8))) and octet_length(t2.f) = 1", "mrr_type": "Rowid-ordered scan" }, "table": { @@ -2786,7 +2786,7 @@ EXPLAIN "ref": ["test.t1.a"], "rows": 3, "filtered": 100, - "attached_condition": "(t1.a,t2.e) in ((4,t1.a + 1),(7,t1.a + 1),(8,t1.a + 1)) and length(t2.f) = 1" + "attached_condition": "(t1.a,t2.e) in ((4,t1.a + 1),(7,t1.a + 1),(8,t1.a + 1)) and octet_length(t2.f) = 1" } } } @@ -2840,7 +2840,7 @@ EXPLAIN "ref": ["test.t1.a"], "rows": 3, "filtered": 100, - "attached_condition": "(t1.a,t2.e) in ((t2.e,t1.a + 1),<cache>((7,7)),<cache>((8,8))) and length(t2.f) = 1" + "attached_condition": "(t1.a,t2.e) in ((t2.e,t1.a + 1),<cache>((7,7)),<cache>((8,8))) and octet_length(t2.f) = 1" } } } @@ -2880,7 +2880,7 @@ EXPLAIN "rows": 13, "filtered": 100, "index_condition": "t1.a is not null", - "attached_condition": "(t1.a,2) in (<cache>((2,2)),<cache>((7,7)),<cache>((8,8))) and length(t1.c) = 1", + "attached_condition": "(t1.a,2) in (<cache>((2,2)),<cache>((7,7)),<cache>((8,8))) and octet_length(t1.c) = 1", "mrr_type": "Rowid-ordered scan" }, "table": { @@ -2893,7 +2893,7 @@ EXPLAIN "ref": ["test.t1.a"], "rows": 3, "filtered": 100, - "attached_condition": "length(t2.f) = 1" + "attached_condition": "octet_length(t2.f) = 1" } } } @@ -2969,7 +2969,7 @@ EXPLAIN "rows": 13, "filtered": 100, "index_condition": "t1.a is not null", - "attached_condition": "(t1.a,1 + 1) in (<cache>((2,2)),<cache>((7,7)),<cache>((8,8))) and length(t1.c) = 1", + "attached_condition": "(t1.a,1 + 1) in (<cache>((2,2)),<cache>((7,7)),<cache>((8,8))) and octet_length(t1.c) = 1", "mrr_type": "Rowid-ordered scan" }, "table": { @@ -2982,7 +2982,7 @@ EXPLAIN "ref": ["test.t1.a"], "rows": 3, "filtered": 100, - "attached_condition": "length(t2.f) = 1" + "attached_condition": "octet_length(t2.f) = 1" } } } diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index b83ee20e784..95a872f1498 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -1260,7 +1260,7 @@ a SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( - `a` bigint(20) NOT NULL + `a` int(3) NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; create table t1 (a int); diff --git a/mysql-test/r/subselect_no_exists_to_in.result b/mysql-test/r/subselect_no_exists_to_in.result index 87f33d1dbf4..7f07b974bb2 100644 --- a/mysql-test/r/subselect_no_exists_to_in.result +++ b/mysql-test/r/subselect_no_exists_to_in.result @@ -1264,7 +1264,7 @@ a SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( - `a` bigint(20) NOT NULL + `a` int(3) NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; create table t1 (a int); diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result index 626990f2c9e..57c7a979d61 100644 --- a/mysql-test/r/subselect_no_mat.result +++ b/mysql-test/r/subselect_no_mat.result @@ -1267,7 +1267,7 @@ a SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( - `a` bigint(20) NOT NULL + `a` int(3) NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; create table t1 (a int); diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result index 3637604646f..6e8be4a02a7 100644 --- a/mysql-test/r/subselect_no_opts.result +++ b/mysql-test/r/subselect_no_opts.result @@ -1263,7 +1263,7 @@ a SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( - `a` bigint(20) NOT NULL + `a` int(3) NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; create table t1 (a int); diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result index afebd27c192..1b437f6919d 100644 --- a/mysql-test/r/subselect_no_scache.result +++ b/mysql-test/r/subselect_no_scache.result @@ -1266,7 +1266,7 @@ a SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( - `a` bigint(20) NOT NULL + `a` int(3) NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; create table t1 (a int); diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result index 0f0fc59341e..6094c7e029d 100644 --- a/mysql-test/r/subselect_no_semijoin.result +++ b/mysql-test/r/subselect_no_semijoin.result @@ -1263,7 +1263,7 @@ a SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( - `a` bigint(20) NOT NULL + `a` int(3) NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; create table t1 (a int); diff --git a/mysql-test/r/temp_table.result b/mysql-test/r/temp_table.result index b833f4f9768..d1bec4a2af5 100644 --- a/mysql-test/r/temp_table.result +++ b/mysql-test/r/temp_table.result @@ -181,20 +181,20 @@ CREATE TABLE t2 ( c FLOAT(30,18) ); INSERT INTO t2 VALUES( 123456 ); SELECT AVG( c ) FROM t1 UNION SELECT 1; AVG( c ) -12139 -1 +12139.000000000000000000 +1.000000000000000000 SELECT 1 UNION SELECT AVG( c ) FROM t1; 1 -1 -12139 +1.000000000000000000 +12139.000000000000000000 SELECT 1 UNION SELECT * FROM t2 UNION SELECT 1; 1 -1 -123456 +1.000000000000000000 +123456.000000000000000000 SELECT c/1 FROM t1 UNION SELECT 1; c/1 -12139 -1 +12139.000000000000000000 +1.000000000000000000 DROP TABLE t1, t2; create temporary table t1 (a int); insert into t1 values (4711); diff --git a/mysql-test/r/type_date.result b/mysql-test/r/type_date.result index 7c7494903b6..d56fac962fe 100644 --- a/mysql-test/r/type_date.result +++ b/mysql-test/r/type_date.result @@ -615,14 +615,14 @@ SELECT * FROM t1 WHERE LENGTH(a)=11+RAND() AND a=' 2001-01-01'; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = DATE'2001-01-01' and <cache>(length(DATE'2001-01-01')) = 11 + rand() +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = DATE'2001-01-01' and <cache>(octet_length(DATE'2001-01-01')) = 11 + rand() EXPLAIN EXTENDED SELECT * FROM t1 WHERE LENGTH(a)=11+RAND() AND a=' garbage '; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: Warning 1292 Incorrect datetime value: ' garbage ' -Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = DATE'0000-00-00' and <cache>(length(DATE'0000-00-00')) = 11 + rand() +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = DATE'0000-00-00' and <cache>(octet_length(DATE'0000-00-00')) = 11 + rand() DROP TABLE t1; CREATE TABLE t1 (a DATE); INSERT INTO t1 VALUES ('2001-01-01'),('2001-01-01'); @@ -641,7 +641,7 @@ SELECT * FROM t1 WHERE LENGTH(a)=8+RAND() AND a='20010101'; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = DATE'2001-01-01' and <cache>(length(DATE'2001-01-01')) = 8 + rand() +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = DATE'2001-01-01' and <cache>(octet_length(DATE'2001-01-01')) = 8 + rand() DROP TABLE t1; # # MDEV-8706 Wrong result for SELECT..WHERE time_column=TIMESTAMP'2015-08-30 00:00:00' AND time_column='00:00:00' @@ -670,7 +670,7 @@ SELECT * FROM t1 WHERE LENGTH(a)=30+RAND() AND a=TIME'00:00:00'; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = DATE'2015-08-30' and <cache>(length(DATE'2015-08-30')) = 30 + rand() +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = DATE'2015-08-30' and <cache>(octet_length(DATE'2015-08-30')) = 30 + rand() DROP TABLE t1; CREATE TABLE t1 (a DATE); INSERT INTO t1 VALUES ('2015-08-30'),('2015-08-31'); @@ -695,7 +695,7 @@ SELECT * FROM t1 WHERE LENGTH(a)=30+RAND() AND a=TIME'24:00:00'; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = DATE'2015-08-31' and <cache>(length(DATE'2015-08-31')) = 30 + rand() +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = DATE'2015-08-31' and <cache>(octet_length(DATE'2015-08-31')) = 30 + rand() DROP TABLE t1; # In this example '00:00:00' is not recognized as TIME'00:00:00' # and is treated as DATE'0000-00-00'. @@ -721,7 +721,7 @@ SELECT * FROM t1 WHERE LENGTH(a)=30+RAND() AND a='00:00:00'; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = DATE'0000-00-00' and <cache>(length(DATE'0000-00-00')) = 30 + rand() +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = DATE'0000-00-00' and <cache>(octet_length(DATE'0000-00-00')) = 30 + rand() DROP TABLE t1; CREATE TABLE t1 (a DATE); INSERT INTO t1 VALUES ('2015-08-30'),('2015-08-31'); @@ -746,7 +746,7 @@ SELECT * FROM t1 WHERE LENGTH(a)=30+RAND() AND a=TIMESTAMP'2015-08-30 00:00:00'; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIMESTAMP'2015-08-30 00:00:00' and <cache>(length(DATE'2015-08-30')) = 30 + rand() +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIMESTAMP'2015-08-30 00:00:00' and <cache>(octet_length(DATE'2015-08-30')) = 30 + rand() DROP TABLE t1; CREATE TABLE t1 (a DATE); INSERT INTO t1 VALUES ('2015-08-30'),('2015-08-31'); @@ -769,7 +769,7 @@ SELECT * FROM t1 WHERE LENGTH(a)=30+RAND() AND a=TIMESTAMP'2015-08-30 00:00:00.1 id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIMESTAMP'2015-08-30 00:00:00.1' and <cache>(length(DATE'2015-08-30')) = 30 + rand() +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIMESTAMP'2015-08-30 00:00:00.1' and <cache>(octet_length(DATE'2015-08-30')) = 30 + rand() DROP TABLE t1; CREATE TABLE t1 (a DATE); INSERT INTO t1 VALUES ('2015-08-30'),('2015-08-31'); @@ -794,7 +794,7 @@ SELECT * FROM t1 WHERE LENGTH(a)=30+RAND() AND a='2015-08-30 00:00:00'; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = DATE'2015-08-30' and <cache>(length(DATE'2015-08-30')) = 30 + rand() +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = DATE'2015-08-30' and <cache>(octet_length(DATE'2015-08-30')) = 30 + rand() DROP TABLE t1; SET timestamp=DEFAULT; # diff --git a/mysql-test/r/type_datetime.result b/mysql-test/r/type_datetime.result index 4dd214b61b3..95e421821c1 100644 --- a/mysql-test/r/type_datetime.result +++ b/mysql-test/r/type_datetime.result @@ -984,7 +984,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: Warning 1292 Truncated incorrect datetime value: '2001-01-01 00:00:00x' -Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIMESTAMP'2001-01-01 00:00:00' and <cache>(length(TIMESTAMP'2001-01-01 00:00:00')) <> 30 + rand() +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIMESTAMP'2001-01-01 00:00:00' and <cache>(octet_length(TIMESTAMP'2001-01-01 00:00:00')) <> 30 + rand() DROP TABLE t1; CREATE TABLE t1 (a DATETIME);; INSERT INTO t1 VALUES ('2001-01-01 00:00:00'),('2001-01-01 00:00:01'); @@ -1006,14 +1006,14 @@ SELECT * FROM t1 WHERE LENGTH(a)=19+RAND() AND a=' 2001-01-01 00:00:00'; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIMESTAMP'2001-01-01 00:00:00' and <cache>(length(TIMESTAMP'2001-01-01 00:00:00')) = 19 + rand() +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIMESTAMP'2001-01-01 00:00:00' and <cache>(octet_length(TIMESTAMP'2001-01-01 00:00:00')) = 19 + rand() EXPLAIN EXTENDED SELECT * FROM t1 WHERE LENGTH(a)=30+RAND() AND a=' garbage '; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: Warning 1292 Incorrect datetime value: ' garbage ' -Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIMESTAMP'0000-00-00 00:00:00' and <cache>(length(TIMESTAMP'0000-00-00 00:00:00')) = 30 + rand() +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIMESTAMP'0000-00-00 00:00:00' and <cache>(octet_length(TIMESTAMP'0000-00-00 00:00:00')) = 30 + rand() DROP TABLE t1; CREATE TABLE t1 (a DATETIME);; INSERT INTO t1 VALUES ('2001-01-01 00:00:00'),('2001-01-01 00:00:01'); @@ -1038,7 +1038,7 @@ SELECT * FROM t1 WHERE LENGTH(a)=30+RAND() AND a=TIMESTAMP'2001-01-01 00:00:00.0 id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIMESTAMP'2001-01-01 00:00:00.000000' and <cache>(length(TIMESTAMP'2001-01-01 00:00:00')) = 30 + rand() +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIMESTAMP'2001-01-01 00:00:00.000000' and <cache>(octet_length(TIMESTAMP'2001-01-01 00:00:00')) = 30 + rand() DROP TABLE t1; CREATE TABLE t1 (a DATETIME(6));; INSERT INTO t1 VALUES ('2001-01-01 00:00:00.000000'),('2001-01-01 00:00:01.000000'); @@ -1063,7 +1063,7 @@ SELECT * FROM t1 WHERE LENGTH(a)=40+RAND() AND a=TIMESTAMP'2001-01-01 00:00:00.0 id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIMESTAMP'2001-01-01 00:00:00.000000' and <cache>(length(TIMESTAMP'2001-01-01 00:00:00.000000')) = 40 + rand() +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIMESTAMP'2001-01-01 00:00:00.000000' and <cache>(octet_length(TIMESTAMP'2001-01-01 00:00:00.000000')) = 40 + rand() DROP TABLE t1; SET timestamp=UNIX_TIMESTAMP('2001-01-01 10:20:30'); CREATE TABLE t1 (a DATETIME);; @@ -1089,7 +1089,7 @@ SELECT * FROM t1 WHERE LENGTH(a)=40+RAND() AND a=TIME'00:00:00'; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIMESTAMP'2001-01-01 00:00:00' and <cache>(length(TIMESTAMP'2001-01-01 00:00:00')) = 40 + rand() +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIMESTAMP'2001-01-01 00:00:00' and <cache>(octet_length(TIMESTAMP'2001-01-01 00:00:00')) = 40 + rand() DROP TABLE t1; # # MDEV-8795 Equal expression propagation does not work for temporal literals diff --git a/mysql-test/r/type_datetime_hires.result b/mysql-test/r/type_datetime_hires.result index e2c2f83a96c..003ecf15e69 100644 --- a/mysql-test/r/type_datetime_hires.result +++ b/mysql-test/r/type_datetime_hires.result @@ -131,18 +131,18 @@ show create table t2; Table Create Table t2 CREATE TABLE `t2` ( `a` datetime(4) DEFAULT NULL, - `a+0` decimal(25,4) DEFAULT NULL, - `a-1` decimal(25,4) DEFAULT NULL, - `a*1` decimal(25,4) DEFAULT NULL, - `a/2` decimal(28,8) DEFAULT NULL + `a+0` decimal(19,4) DEFAULT NULL, + `a-1` decimal(19,4) DEFAULT NULL, + `a*1` decimal(19,4) DEFAULT NULL, + `a/2` decimal(22,8) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 show create table t3; Table Create Table t3 CREATE TABLE `t3` ( `max(a)` datetime(4) DEFAULT NULL, `min(a)` datetime(4) DEFAULT NULL, - `sum(a)` decimal(46,4) DEFAULT NULL, - `avg(a)` decimal(28,8) DEFAULT NULL + `sum(a)` decimal(40,4) DEFAULT NULL, + `avg(a)` decimal(22,8) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1, t2, t3; create table t1 (f0_datetime datetime(0), f1_datetime datetime(1), f2_datetime datetime(2), f3_datetime datetime(3), f4_datetime datetime(4), f5_datetime datetime(5), f6_datetime datetime(6)); diff --git a/mysql-test/r/type_float.result b/mysql-test/r/type_float.result index 43aed60749e..9a92ff21e9f 100644 --- a/mysql-test/r/type_float.result +++ b/mysql-test/r/type_float.result @@ -232,12 +232,12 @@ insert into t2 values ("1.23456780"); create table t3 select * from t2 union select * from t1; select * from t3; d -1.2345678 -100000000 +1.234567800 +100000000.000000000 show create table t3; Table Create Table t3 CREATE TABLE `t3` ( - `d` double DEFAULT NULL + `d` double(18,9) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1, t2, t3; create table t1 select 105213674794682365.00 + 0.0 x; @@ -562,7 +562,7 @@ SELECT * FROM t1 WHERE LENGTH(a)!=RAND() AND a=100e0; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 100e0 and <cache>(length(100)) <> rand() +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 100e0 and <cache>(octet_length(100)) <> rand() DROP TABLE t1; CREATE TABLE t1 (a DOUBLE(10,1)); INSERT INTO t1 VALUES (1.1),(1.2),(1.3); @@ -582,7 +582,7 @@ SELECT * FROM t1 WHERE LENGTH(a)!=RAND() AND a=1.10e0; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 1.10e0 and <cache>(length(1.1)) <> rand() +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 1.10e0 and <cache>(octet_length(1.1)) <> rand() DROP TABLE t1; CREATE TABLE t1 (a DOUBLE(10,2)); INSERT INTO t1 VALUES (1.1),(1.2),(1.3); @@ -602,7 +602,7 @@ SELECT * FROM t1 WHERE LENGTH(a)!=RAND() AND a=1.10e0; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 1.10e0 and <cache>(length(1.10)) <> rand() +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 1.10e0 and <cache>(octet_length(1.10)) <> rand() DROP TABLE t1; CREATE TABLE t1 (a DOUBLE(10,3)); INSERT INTO t1 VALUES (1.1),(1.2),(1.3); @@ -622,7 +622,7 @@ SELECT * FROM t1 WHERE LENGTH(a)!=RAND() AND a=1.10e0; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 1.10e0 and <cache>(length(1.100)) <> rand() +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 1.10e0 and <cache>(octet_length(1.100)) <> rand() DROP TABLE t1; # # MDEV-8741 Equal field propagation leaves some remainders after simplifying WHERE zerofill_column=2010 AND zerofill_column>=2010 diff --git a/mysql-test/r/type_newdecimal.result b/mysql-test/r/type_newdecimal.result index ab0e2dbc71d..ba84ff50504 100644 --- a/mysql-test/r/type_newdecimal.result +++ b/mysql-test/r/type_newdecimal.result @@ -2086,7 +2086,7 @@ SELECT * FROM t1 WHERE LENGTH(a)!=rand() AND a=1.10; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 1.10 and <cache>(length(1.1)) <> rand() +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 1.10 and <cache>(octet_length(1.1)) <> rand() DROP TABLE t1; CREATE TABLE t1 (a DECIMAL(10,2)); INSERT INTO t1 VALUES (1.1),(1.2),(1.3); @@ -2106,7 +2106,7 @@ SELECT * FROM t1 WHERE LENGTH(a)!=rand() AND a=1.10; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 1.10 and <cache>(length(1.10)) <> rand() +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 1.10 and <cache>(octet_length(1.10)) <> rand() DROP TABLE t1; CREATE TABLE t1 (a DECIMAL(10,3)); INSERT INTO t1 VALUES (1.1),(1.2),(1.3); @@ -2126,7 +2126,7 @@ SELECT * FROM t1 WHERE LENGTH(a)!=rand() AND a=1.10; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 1.10 and <cache>(length(1.100)) <> rand() +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 1.10 and <cache>(octet_length(1.100)) <> rand() DROP TABLE t1; # # MDEV-8741 Equal field propagation leaves some remainders after simplifying WHERE zerofill_column=2010 AND zerofill_column>=2010 diff --git a/mysql-test/r/type_time.result b/mysql-test/r/type_time.result index 3f125598f9b..1600732cec3 100644 --- a/mysql-test/r/type_time.result +++ b/mysql-test/r/type_time.result @@ -221,7 +221,7 @@ MAX(t0)+1 101011 SHOW COLUMNS FROM t2; Field Type Null Key Default Extra -MAX(t0)+1 bigint(12) YES NULL +MAX(t0)+1 int(9) YES NULL DROP TABLE t2,t1; CREATE TABLE t1 (t0 TIME); INSERT INTO t1 VALUES ('10:10:10'); @@ -234,7 +234,7 @@ MAX(t0)+1.1 101011.1 SHOW COLUMNS FROM t2; Field Type Null Key Default Extra -MAX(t0)+1.1 decimal(12,1) YES NULL +MAX(t0)+1.1 decimal(9,1) YES NULL DROP TABLE t2,t1; CREATE TABLE t1 (t0 TIME); INSERT INTO t1 VALUES ('10:10:10'); @@ -260,7 +260,7 @@ MAX(t1)+1 101011.0 SHOW COLUMNS FROM t2; Field Type Null Key Default Extra -MAX(t1)+1 decimal(13,1) YES NULL +MAX(t1)+1 decimal(9,1) YES NULL DROP TABLE t2,t1; CREATE TABLE t1 (t0 DATETIME); INSERT INTO t1 VALUES ('2001-01-01 10:10:10'); @@ -273,7 +273,7 @@ MAX(t0)+1 20010101101011 SHOW COLUMNS FROM t2; Field Type Null Key Default Extra -MAX(t0)+1 bigint(21) YES NULL +MAX(t0)+1 bigint(16) YES NULL DROP TABLE t2,t1; CREATE TABLE t1 (t0 DATETIME); INSERT INTO t1 VALUES ('2001-01-01 10:10:10'); @@ -286,7 +286,7 @@ MAX(t0)+1.1 20010101101011.1 SHOW COLUMNS FROM t2; Field Type Null Key Default Extra -MAX(t0)+1.1 decimal(21,1) YES NULL +MAX(t0)+1.1 decimal(16,1) YES NULL DROP TABLE t2,t1; CREATE TABLE t1 (t0 DATETIME); INSERT INTO t1 VALUES ('2001-01-01 10:10:10'); @@ -312,7 +312,7 @@ MAX(t1)+1 20010101101011.0 SHOW COLUMNS FROM t2; Field Type Null Key Default Extra -MAX(t1)+1 decimal(22,1) YES NULL +MAX(t1)+1 decimal(16,1) YES NULL DROP TABLE t2,t1; # # MDEV-4858 Wrong results for a huge unsigned value inserted into a TIME column @@ -424,7 +424,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: Warning 1292 Truncated incorrect time value: '00:00:00x' -Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIME'00:00:00' and <cache>(length(TIME'00:00:00')) = 30 + rand() +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIME'00:00:00' and <cache>(octet_length(TIME'00:00:00')) = 30 + rand() DROP TABLE t1; # Trailing fractional digits in string literals CREATE TABLE t1 (a TIME); @@ -447,7 +447,7 @@ SELECT * FROM t1 WHERE LENGTH(a)=30+RAND() AND a='00:00:00.000000'; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIME'00:00:00' and <cache>(length(TIME'00:00:00')) = 30 + rand() +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIME'00:00:00' and <cache>(octet_length(TIME'00:00:00')) = 30 + rand() DROP TABLE t1; # Trailing fractional digits in temporal literals CREATE TABLE t1 (a TIME); @@ -470,7 +470,7 @@ SELECT * FROM t1 WHERE LENGTH(a)=30+RAND() AND a=TIME'00:00:00.000000'; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIME'00:00:00.000000' and <cache>(length(TIME'00:00:00')) = 30 + rand() +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIME'00:00:00.000000' and <cache>(octet_length(TIME'00:00:00')) = 30 + rand() DROP TABLE t1; # Trailing fractional digits in temporal literals, same precision CREATE TABLE t1 (a TIME(6)); @@ -490,7 +490,7 @@ SELECT * FROM t1 WHERE LENGTH(a)=30+RAND() AND a=TIME'00:00:00.000000'; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIME'00:00:00.000000' and <cache>(length(TIME'00:00:00.000000')) = 30 + rand() +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIME'00:00:00.000000' and <cache>(octet_length(TIME'00:00:00.000000')) = 30 + rand() DROP TABLE t1; # Leading spaces in string literals CREATE TABLE t1 (a TIME); @@ -513,7 +513,7 @@ SELECT * FROM t1 WHERE LENGTH(a)=30+RAND() AND a=' 00:00:00'; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIME'00:00:00' and <cache>(length(TIME'00:00:00')) = 30 + rand() +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIME'00:00:00' and <cache>(octet_length(TIME'00:00:00')) = 30 + rand() DROP TABLE t1; # Numeric format in string literals CREATE TABLE t1 (a TIME); @@ -536,7 +536,7 @@ SELECT * FROM t1 WHERE LENGTH(a)=30+RAND() AND a='000000'; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIME'00:00:00' and <cache>(length(TIME'00:00:00')) = 30 + rand() +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIME'00:00:00' and <cache>(octet_length(TIME'00:00:00')) = 30 + rand() DROP TABLE t1; # # MDEV-8766 Wrong result for SELECT..WHERE LENGTH(time_column)=8 AND time_column=TIMESTAMP'2001-01-01 10:20:31' @@ -565,7 +565,7 @@ SELECT * FROM t1 WHERE LENGTH(a)=30+RAND() AND a=TIMESTAMP'2001-01-01 10:20:31'; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIME'10:20:31' and <cache>(length(TIME'10:20:31')) = 30 + rand() +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIME'10:20:31' and <cache>(octet_length(TIME'10:20:31')) = 30 + rand() DROP TABLE t1; # TIMESTAMP literal with a bigger scale and fractional second truncation # Ok to propagate with precision truncation @@ -589,7 +589,7 @@ SELECT * FROM t1 WHERE LENGTH(a)=30+RAND() AND a=TIMESTAMP'2001-01-01 10:20:31.1 id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIME'10:20:31.123000' and <cache>(length(TIME'10:20:31')) = 30 + rand() +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIME'10:20:31.123000' and <cache>(octet_length(TIME'10:20:31')) = 30 + rand() DROP TABLE t1; # TIMESTAMP literal with a bigger scale and no fractional second truncation # Ok to propagate @@ -615,7 +615,7 @@ SELECT * FROM t1 WHERE LENGTH(a)=30+RAND() AND a=TIMESTAMP'2001-01-01 10:20:31.0 id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIME'10:20:31' and <cache>(length(TIME'10:20:31')) = 30 + rand() +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIME'10:20:31' and <cache>(octet_length(TIME'10:20:31')) = 30 + rand() DROP TABLE t1; # TIMESTAMP literal with a smaller scale # Ok to propagate @@ -638,7 +638,7 @@ SELECT * FROM t1 WHERE LENGTH(a)=30+RAND() AND a=TIMESTAMP'2001-01-01 10:20:31.1 id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIME'10:20:31.123000' and <cache>(length(TIME'10:20:31.123000')) = 30 + rand() +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIME'10:20:31.123000' and <cache>(octet_length(TIME'10:20:31.123000')) = 30 + rand() DROP TABLE t1; # TIME literal with a bigger scale and fractional second truncation # Ok to propagate with precision truncation @@ -662,7 +662,7 @@ SELECT * FROM t1 WHERE LENGTH(a)=30+RAND() AND a=TIME'10:20:31.123'; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIME'10:20:31.123' and <cache>(length(TIME'10:20:31')) = 30 + rand() +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIME'10:20:31.123' and <cache>(octet_length(TIME'10:20:31')) = 30 + rand() DROP TABLE t1; # TIME literal with a bigger scale and no fractional second truncation # Ok to propagate @@ -688,7 +688,7 @@ SELECT * FROM t1 WHERE LENGTH(a)=30+RAND() AND a=TIME'10:20:31.000'; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIME'10:20:31.000' and <cache>(length(TIME'10:20:31')) = 30 + rand() +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIME'10:20:31.000' and <cache>(octet_length(TIME'10:20:31')) = 30 + rand() DROP TABLE t1; # TIME literal with a smaller scale # Ok to propagate @@ -711,7 +711,7 @@ SELECT * FROM t1 WHERE LENGTH(a)=30+RAND() AND a=TIME'10:20:31.123'; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIME'10:20:31.123' and <cache>(length(TIME'10:20:31.123000')) = 30 + rand() +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIME'10:20:31.123' and <cache>(octet_length(TIME'10:20:31.123000')) = 30 + rand() DROP TABLE t1; # TIME-alike string literal with a bigger scale and fractional second truncation # Ok to propagate with precision truncation @@ -735,7 +735,7 @@ SELECT * FROM t1 WHERE LENGTH(a)=30+RAND() AND a='10:20:31.123'; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIME'10:20:31.123000' and <cache>(length(TIME'10:20:31')) = 30 + rand() +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIME'10:20:31.123000' and <cache>(octet_length(TIME'10:20:31')) = 30 + rand() DROP TABLE t1; # TIME-alike string literal with a bigger scale and no fractional second truncation # Ok to propagate @@ -761,7 +761,7 @@ SELECT * FROM t1 WHERE LENGTH(a)=30+RAND() AND a='10:20:31.000'; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIME'10:20:31' and <cache>(length(TIME'10:20:31')) = 30 + rand() +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIME'10:20:31' and <cache>(octet_length(TIME'10:20:31')) = 30 + rand() DROP TABLE t1; # TIME-alike string literal with a smaller scale # Ok to propagate @@ -784,7 +784,7 @@ SELECT * FROM t1 WHERE LENGTH(a)=30+RAND() AND a='10:20:31.123'; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIME'10:20:31.123000' and <cache>(length(TIME'10:20:31.123000')) = 30 + rand() +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIME'10:20:31.123000' and <cache>(octet_length(TIME'10:20:31.123000')) = 30 + rand() DROP TABLE t1; SET timestamp=DEFAULT; SET @@old_mode=zero_date_time_cast; @@ -821,7 +821,7 @@ SELECT * FROM t1 WHERE a=TIMESTAMP'0000-00-00 10:20:30' AND LENGTH(a)=30+RAND(); id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 8 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIME'10:20:30' and <cache>(length(TIME'10:20:30')) = 30 + rand() +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIME'10:20:30' and <cache>(octet_length(TIME'10:20:30')) = 30 + rand() # Old mode, TIMESTAMP literal, zon-zero YYYYMMDD, no propagation SELECT * FROM t1 WHERE a=TIMESTAMP'0000-00-01 10:20:30'; a @@ -834,13 +834,13 @@ SELECT * FROM t1 WHERE a=TIMESTAMP'0000-00-01 10:20:30' AND LENGTH(a)=8; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 8 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIMESTAMP'0000-00-01 10:20:30' and length(`test`.`t1`.`a`) = 8 +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIMESTAMP'0000-00-01 10:20:30' and octet_length(`test`.`t1`.`a`) = 8 EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=TIMESTAMP'0000-00-01 10:20:30' AND LENGTH(a)=30+RAND(); id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 8 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIMESTAMP'0000-00-01 10:20:30' and length(`test`.`t1`.`a`) = 30 + rand() +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIMESTAMP'0000-00-01 10:20:30' and octet_length(`test`.`t1`.`a`) = 30 + rand() # Old mode, TIMESTAMP-alike string literal, zero YYYYMMDD, Ok to propagate SELECT * FROM t1 WHERE a='0000-00-00 10:20:30'; a @@ -859,7 +859,7 @@ SELECT * FROM t1 WHERE a='0000-00-00 10:20:30' AND LENGTH(a)=30+RAND(); id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 8 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIME'10:20:30' and <cache>(length(TIME'10:20:30')) = 30 + rand() +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIME'10:20:30' and <cache>(octet_length(TIME'10:20:30')) = 30 + rand() # Old mode, TIMESTAMP-alike literal, zon-zero YYYYMMDD, no propagation SELECT * FROM t1 WHERE a='0000-00-01 10:20:30'; a @@ -872,13 +872,13 @@ SELECT * FROM t1 WHERE a='0000-00-01 10:20:30' AND LENGTH(a)=8; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 8 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = '0000-00-01 10:20:30' and length(`test`.`t1`.`a`) = 8 +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = '0000-00-01 10:20:30' and octet_length(`test`.`t1`.`a`) = 8 EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='0000-00-01 10:20:30' AND LENGTH(a)=30+RAND(); id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 8 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = '0000-00-01 10:20:30' and length(`test`.`t1`.`a`) = 30 + rand() +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = '0000-00-01 10:20:30' and octet_length(`test`.`t1`.`a`) = 30 + rand() DROP TABLE t1; SET @@old_mode=DEFAULT; # diff --git a/mysql-test/r/type_time_hires.result b/mysql-test/r/type_time_hires.result index a9345a7e83f..47185116bea 100644 --- a/mysql-test/r/type_time_hires.result +++ b/mysql-test/r/type_time_hires.result @@ -145,18 +145,18 @@ show create table t2; Table Create Table t2 CREATE TABLE `t2` ( `a` time(4) DEFAULT NULL, - `a+0` decimal(16,4) DEFAULT NULL, - `a-1` decimal(16,4) DEFAULT NULL, - `a*1` decimal(16,4) DEFAULT NULL, - `a/2` decimal(19,8) DEFAULT NULL + `a+0` decimal(12,4) DEFAULT NULL, + `a-1` decimal(12,4) DEFAULT NULL, + `a*1` decimal(12,4) DEFAULT NULL, + `a/2` decimal(15,8) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 show create table t3; Table Create Table t3 CREATE TABLE `t3` ( `max(a)` time(4) DEFAULT NULL, `min(a)` time(4) DEFAULT NULL, - `sum(a)` decimal(37,4) DEFAULT NULL, - `avg(a)` decimal(19,8) DEFAULT NULL + `sum(a)` decimal(33,4) DEFAULT NULL, + `avg(a)` decimal(15,8) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1, t2, t3; create table t1 (f0_time time(0), f1_time time(1), f2_time time(2), f3_time time(3), f4_time time(4), f5_time time(5), f6_time time(6)); diff --git a/mysql-test/r/type_timestamp.result b/mysql-test/r/type_timestamp.result index cf4f6df4b04..b0405bc4ad7 100644 --- a/mysql-test/r/type_timestamp.result +++ b/mysql-test/r/type_timestamp.result @@ -840,7 +840,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: Warning 1292 Truncated incorrect datetime value: '2001-01-01 00:00:00x' -Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIMESTAMP'2001-01-01 00:00:00' and <cache>(length(TIMESTAMP'2001-01-01 00:00:00')) <> 30 + rand() +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIMESTAMP'2001-01-01 00:00:00' and <cache>(octet_length(TIMESTAMP'2001-01-01 00:00:00')) <> 30 + rand() DROP TABLE t1; CREATE TABLE t1 (a TIMESTAMP);; INSERT INTO t1 VALUES ('2001-01-01 00:00:00'),('2001-01-01 00:00:01'); @@ -862,14 +862,14 @@ SELECT * FROM t1 WHERE LENGTH(a)=19+RAND() AND a=' 2001-01-01 00:00:00'; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIMESTAMP'2001-01-01 00:00:00' and <cache>(length(TIMESTAMP'2001-01-01 00:00:00')) = 19 + rand() +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIMESTAMP'2001-01-01 00:00:00' and <cache>(octet_length(TIMESTAMP'2001-01-01 00:00:00')) = 19 + rand() EXPLAIN EXTENDED SELECT * FROM t1 WHERE LENGTH(a)=30+RAND() AND a=' garbage '; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: Warning 1292 Incorrect datetime value: ' garbage ' -Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIMESTAMP'0000-00-00 00:00:00' and <cache>(length(TIMESTAMP'0000-00-00 00:00:00')) = 30 + rand() +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIMESTAMP'0000-00-00 00:00:00' and <cache>(octet_length(TIMESTAMP'0000-00-00 00:00:00')) = 30 + rand() DROP TABLE t1; CREATE TABLE t1 (a TIMESTAMP);; INSERT INTO t1 VALUES ('2001-01-01 00:00:00'),('2001-01-01 00:00:01'); @@ -894,7 +894,7 @@ SELECT * FROM t1 WHERE LENGTH(a)=30+RAND() AND a=TIMESTAMP'2001-01-01 00:00:00.0 id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIMESTAMP'2001-01-01 00:00:00.000000' and <cache>(length(TIMESTAMP'2001-01-01 00:00:00')) = 30 + rand() +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIMESTAMP'2001-01-01 00:00:00.000000' and <cache>(octet_length(TIMESTAMP'2001-01-01 00:00:00')) = 30 + rand() DROP TABLE t1; CREATE TABLE t1 (a TIMESTAMP(6));; INSERT INTO t1 VALUES ('2001-01-01 00:00:00.000000'),('2001-01-01 00:00:01.000000'); @@ -919,7 +919,7 @@ SELECT * FROM t1 WHERE LENGTH(a)=40+RAND() AND a=TIMESTAMP'2001-01-01 00:00:00.0 id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIMESTAMP'2001-01-01 00:00:00.000000' and <cache>(length(TIMESTAMP'2001-01-01 00:00:00.000000')) = 40 + rand() +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIMESTAMP'2001-01-01 00:00:00.000000' and <cache>(octet_length(TIMESTAMP'2001-01-01 00:00:00.000000')) = 40 + rand() DROP TABLE t1; SET timestamp=UNIX_TIMESTAMP('2001-01-01 10:20:30'); CREATE TABLE t1 (a TIMESTAMP);; @@ -945,7 +945,7 @@ SELECT * FROM t1 WHERE LENGTH(a)=40+RAND() AND a=TIME'00:00:00'; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIMESTAMP'2001-01-01 00:00:00' and <cache>(length(TIMESTAMP'2001-01-01 00:00:00')) = 40 + rand() +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIMESTAMP'2001-01-01 00:00:00' and <cache>(octet_length(TIMESTAMP'2001-01-01 00:00:00')) = 40 + rand() DROP TABLE t1; # # End of 10.1 tests diff --git a/mysql-test/r/type_timestamp_hires.result b/mysql-test/r/type_timestamp_hires.result index 7179277aa9a..fa6adc075ed 100644 --- a/mysql-test/r/type_timestamp_hires.result +++ b/mysql-test/r/type_timestamp_hires.result @@ -131,18 +131,18 @@ show create table t2; Table Create Table t2 CREATE TABLE `t2` ( `a` timestamp(4) NOT NULL DEFAULT current_timestamp(4) ON UPDATE current_timestamp(4), - `a+0` decimal(25,4) NOT NULL, - `a-1` decimal(25,4) NOT NULL, - `a*1` decimal(25,4) NOT NULL, - `a/2` decimal(28,8) DEFAULT NULL + `a+0` decimal(19,4) NOT NULL, + `a-1` decimal(19,4) NOT NULL, + `a*1` decimal(19,4) NOT NULL, + `a/2` decimal(22,8) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 show create table t3; Table Create Table t3 CREATE TABLE `t3` ( `max(a)` timestamp(4) NULL DEFAULT NULL, `min(a)` timestamp(4) NULL DEFAULT NULL, - `sum(a)` decimal(46,4) DEFAULT NULL, - `avg(a)` decimal(28,8) DEFAULT NULL + `sum(a)` decimal(40,4) DEFAULT NULL, + `avg(a)` decimal(22,8) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1, t2, t3; create table t1 (f0_timestamp timestamp(0), f1_timestamp timestamp(1), f2_timestamp timestamp(2), f3_timestamp timestamp(3), f4_timestamp timestamp(4), f5_timestamp timestamp(5), f6_timestamp timestamp(6)); diff --git a/mysql-test/r/union.result b/mysql-test/r/union.result index 9949defebf7..e0aa93d5c97 100644 --- a/mysql-test/r/union.result +++ b/mysql-test/r/union.result @@ -852,7 +852,7 @@ select * from t1; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `1` int(11) NOT NULL DEFAULT 0 + `1` int(2) NOT NULL DEFAULT 0 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; create table t1 select _latin1"test" union select _latin2"testt" ; @@ -1608,7 +1608,7 @@ NULL binary(0) YES NULL CREATE TABLE t5 SELECT NULL UNION SELECT NULL; DESC t5; Field Type Null Key Default Extra -NULL binary(0) YES NULL +NULL null YES NULL CREATE TABLE t6 SELECT * FROM (SELECT * FROM (SELECT NULL)a) b UNION SELECT a FROM t1; DESC t6; @@ -2195,16 +2195,223 @@ CREATE OR REPLACE TABLE t1 AS SELECT 1 UNION SELECT 1; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( - `1` int(11) NOT NULL DEFAULT 0 + `1` int(1) NOT NULL DEFAULT 0 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DROP TABLE t1; CREATE OR REPLACE TABLE t1 AS SELECT * FROM (SELECT 1 UNION SELECT 1) AS t0; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( - `1` int(11) NOT NULL DEFAULT 0 + `1` int(1) NOT NULL DEFAULT 0 +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +# +# MDEV-9495 Wrong field type for a UNION of a signed and an unsigned INT expression +# +CREATE TABLE t1 (a INT, b INT UNSIGNED); +INSERT INTO t1 VALUES (0x7FFFFFFF,0xFFFFFFFF); +CREATE TABLE t2 AS SELECT a FROM t1 UNION SELECT b FROM t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `a` decimal(10,0) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +SELECT * FROM t2 ORDER BY a; +a +2147483647 +4294967295 +DROP TABLE t2; +CREATE TABLE t2 AS SELECT COALESCE(a,b), COALESCE(b,a) FROM t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `COALESCE(a,b)` decimal(10,0) DEFAULT NULL, + `COALESCE(b,a)` decimal(10,0) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +SELECT * FROM t2; +COALESCE(a,b) COALESCE(b,a) +2147483647 4294967295 +DROP TABLE t2; +DROP TABLE t1; +# +# MDEV-9497 UNION and COALESCE produce different field types for DECIMAL+INT +# +CREATE TABLE t1 AS SELECT COALESCE(10.1,CAST(10 AS UNSIGNED)) AS a; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` decimal(3,1) NOT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +CREATE OR REPLACE TABLE t1 AS SELECT 10.1 AS a UNION SELECT CAST(10 AS UNSIGNED); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` decimal(3,1) NOT NULL DEFAULT 0.0 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DROP TABLE t1; # +# MDEV-12594 UNION between fixed length double columns does not always preserve scale +# +CREATE TABLE t1 (a FLOAT(20,4), b FLOAT(20,3), c FLOAT(20,4)); +INSERT INTO t1 VALUES (1111,2222,3333); +CREATE TABLE t2 AS SELECT a FROM t1 UNION SELECT a FROM t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `a` float(20,4) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t2; +CREATE OR REPLACE TABLE t2 SELECT a FROM t1 UNION SELECT c FROM t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `a` float(20,4) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t2; +CREATE OR REPLACE TABLE t2 SELECT b FROM t1 UNION SELECT b FROM t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `b` float(20,3) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t2; +CREATE OR REPLACE TABLE t2 SELECT c FROM t1 UNION SELECT c FROM t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `c` float(20,4) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t2; +CREATE OR REPLACE TABLE t2 SELECT c FROM t1 UNION SELECT a FROM t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `c` float(20,4) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t2; +CREATE OR REPLACE TABLE t2 AS SELECT a FROM t1 UNION SELECT b FROM t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `a` float(21,4) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t2; +CREATE OR REPLACE TABLE t2 AS SELECT b FROM t1 UNION SELECT a FROM t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `b` float(21,4) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t2; +DROP TABLE t1; +# Corner case +CREATE TABLE t1 (a FLOAT(255,4), b FLOAT(255,3)); +INSERT INTO t1 VALUES (1111,2222); +CREATE OR REPLACE TABLE t2 AS SELECT b FROM t1 UNION SELECT a FROM t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `b` float(255,4) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t2; +DROP TABLE t1; +# +# MDEV-12595 UNION converts INT to BIGINT +# +CREATE TABLE t1 AS SELECT +1, +-1, +COALESCE(1,1), +COALESCE(-1,-1), +COALESCE(1,-1), +COALESCE(-1,1); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `1` int(1) NOT NULL, + `-1` int(2) NOT NULL, + `COALESCE(1,1)` int(1) NOT NULL, + `COALESCE(-1,-1)` int(2) NOT NULL, + `COALESCE(1,-1)` int(2) NOT NULL, + `COALESCE(-1,1)` int(2) NOT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +CREATE TABLE t1 AS SELECT 1 AS c1,1 AS c2,-1 AS c3,-1 AS c4 UNION SELECT 1,-1,1,-1; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` int(1) NOT NULL DEFAULT 0, + `c2` int(2) NOT NULL DEFAULT 0, + `c3` int(2) NOT NULL DEFAULT 0, + `c4` int(2) NOT NULL DEFAULT 0 +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +# +# MDEV-12599 UNION is not symmetric when mixing INT and CHAR +# +CREATE OR REPLACE TABLE t1 AS SELECT 1 AS c1, 'a' AS c2 UNION SELECT 'a', 1; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(1) NOT NULL DEFAULT '', + `c2` varchar(1) NOT NULL DEFAULT '' +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +CREATE OR REPLACE TABLE t1 AS SELECT 11112222 AS c1, 'a' AS c2 UNION SELECT 'a', 11112222; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(8) NOT NULL DEFAULT '', + `c2` varchar(8) NOT NULL DEFAULT '' +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +CREATE OR REPLACE TABLE t1 AS SELECT 111122223333 AS c1, 'a' AS c2 UNION SELECT 'a', 111122223333; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(12) NOT NULL DEFAULT '', + `c2` varchar(12) NOT NULL DEFAULT '' +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +CREATE OR REPLACE TABLE t1 AS SELECT 1111222233334444 AS c1, 'a' AS c2 UNION SELECT 'a', 1111222233334444; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(16) NOT NULL DEFAULT '', + `c2` varchar(16) NOT NULL DEFAULT '' +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +CREATE TABLE t1 (a INT(3), b VARCHAR(1)); +CREATE TABLE t2 AS SELECT a,b FROM t1 UNION SELECT b,a FROM t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `a` varchar(11) DEFAULT NULL, + `b` varchar(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t2; +DROP TABLE t1; +CREATE TABLE t1 (a BIGINT(3), b VARCHAR(1)); +CREATE TABLE t2 AS SELECT a,b FROM t1 UNION SELECT b,a FROM t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `a` varchar(20) DEFAULT NULL, + `b` varchar(20) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t2; +DROP TABLE t1; +CREATE TABLE t1 (a BIGINT(12), b VARCHAR(1)); +CREATE TABLE t2 AS SELECT a,b FROM t1 UNION SELECT b,a FROM t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `a` varchar(20) DEFAULT NULL, + `b` varchar(20) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t2; +DROP TABLE t1; +# # End of 10.3 tests # diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result index 14b92e93133..2bb12c3d380 100644 --- a/mysql-test/r/view.result +++ b/mysql-test/r/view.result @@ -4582,7 +4582,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where 1 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a` from `test`.`t1` left join `test`.`t2` on(`test`.`t2`.`a` = `test`.`t1`.`a`) where `test`.`t1`.`b` = 1 or `test`.`t1`.`a` = 'a' and length(`test`.`t1`.`a`) >= `test`.`t1`.`b` +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a` from `test`.`t1` left join `test`.`t2` on(`test`.`t2`.`a` = `test`.`t1`.`a`) where `test`.`t1`.`b` = 1 or `test`.`t1`.`a` = 'a' and octet_length(`test`.`t1`.`a`) >= `test`.`t1`.`b` DROP VIEW v1; DROP TABLE t1,t2; # Bug#798625: duplicate of the previous one, but without crash diff --git a/mysql-test/suite/funcs_1/r/innodb_func_view.result b/mysql-test/suite/funcs_1/r/innodb_func_view.result index 04e689cc651..481787e4eaa 100644 --- a/mysql-test/suite/funcs_1/r/innodb_func_view.result +++ b/mysql-test/suite/funcs_1/r/innodb_func_view.result @@ -857,7 +857,7 @@ my_varbinary_1000, id FROM t1_values WHERE select_id = 155 OR select_id IS NULL order by id; SHOW CREATE VIEW v1; View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select length(`t1_values`.`my_varbinary_1000`) AS `LENGTH(my_varbinary_1000)`,`t1_values`.`my_varbinary_1000` AS `my_varbinary_1000`,`t1_values`.`id` AS `id` from `t1_values` latin1 latin1_swedish_ci +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select octet_length(`t1_values`.`my_varbinary_1000`) AS `LENGTH(my_varbinary_1000)`,`t1_values`.`my_varbinary_1000` AS `my_varbinary_1000`,`t1_values`.`id` AS `id` from `t1_values` latin1 latin1_swedish_ci SELECT v1.* FROM v1 WHERE v1.id IN (SELECT id FROM t1_values WHERE select_id = 155 OR select_id IS NULL) order by id; @@ -871,7 +871,7 @@ my_binary_30, id FROM t1_values WHERE select_id = 154 OR select_id IS NULL order by id; SHOW CREATE VIEW v1; View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select length(`t1_values`.`my_binary_30`) AS `LENGTH(my_binary_30)`,`t1_values`.`my_binary_30` AS `my_binary_30`,`t1_values`.`id` AS `id` from `t1_values` latin1 latin1_swedish_ci +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select octet_length(`t1_values`.`my_binary_30`) AS `LENGTH(my_binary_30)`,`t1_values`.`my_binary_30` AS `my_binary_30`,`t1_values`.`id` AS `id` from `t1_values` latin1 latin1_swedish_ci SELECT v1.* FROM v1 WHERE v1.id IN (SELECT id FROM t1_values WHERE select_id = 154 OR select_id IS NULL) order by id; @@ -885,7 +885,7 @@ my_varchar_1000, id FROM t1_values WHERE select_id = 153 OR select_id IS NULL order by id; SHOW CREATE VIEW v1; View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select length(`t1_values`.`my_varchar_1000`) AS `LENGTH(my_varchar_1000)`,`t1_values`.`my_varchar_1000` AS `my_varchar_1000`,`t1_values`.`id` AS `id` from `t1_values` latin1 latin1_swedish_ci +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select octet_length(`t1_values`.`my_varchar_1000`) AS `LENGTH(my_varchar_1000)`,`t1_values`.`my_varchar_1000` AS `my_varchar_1000`,`t1_values`.`id` AS `id` from `t1_values` latin1 latin1_swedish_ci SELECT v1.* FROM v1 WHERE v1.id IN (SELECT id FROM t1_values WHERE select_id = 153 OR select_id IS NULL) order by id; @@ -899,7 +899,7 @@ my_char_30, id FROM t1_values WHERE select_id = 152 OR select_id IS NULL order by id; SHOW CREATE VIEW v1; View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select length(`t1_values`.`my_char_30`) AS `LENGTH(my_char_30)`,`t1_values`.`my_char_30` AS `my_char_30`,`t1_values`.`id` AS `id` from `t1_values` latin1 latin1_swedish_ci +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select octet_length(`t1_values`.`my_char_30`) AS `LENGTH(my_char_30)`,`t1_values`.`my_char_30` AS `my_char_30`,`t1_values`.`id` AS `id` from `t1_values` latin1 latin1_swedish_ci SELECT v1.* FROM v1 WHERE v1.id IN (SELECT id FROM t1_values WHERE select_id = 152 OR select_id IS NULL) order by id; diff --git a/mysql-test/suite/funcs_1/r/memory_func_view.result b/mysql-test/suite/funcs_1/r/memory_func_view.result index 765869a312d..3a75e4abac5 100644 --- a/mysql-test/suite/funcs_1/r/memory_func_view.result +++ b/mysql-test/suite/funcs_1/r/memory_func_view.result @@ -858,7 +858,7 @@ my_varbinary_1000, id FROM t1_values WHERE select_id = 155 OR select_id IS NULL order by id; SHOW CREATE VIEW v1; View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select length(`t1_values`.`my_varbinary_1000`) AS `LENGTH(my_varbinary_1000)`,`t1_values`.`my_varbinary_1000` AS `my_varbinary_1000`,`t1_values`.`id` AS `id` from `t1_values` latin1 latin1_swedish_ci +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select octet_length(`t1_values`.`my_varbinary_1000`) AS `LENGTH(my_varbinary_1000)`,`t1_values`.`my_varbinary_1000` AS `my_varbinary_1000`,`t1_values`.`id` AS `id` from `t1_values` latin1 latin1_swedish_ci SELECT v1.* FROM v1 WHERE v1.id IN (SELECT id FROM t1_values WHERE select_id = 155 OR select_id IS NULL) order by id; @@ -872,7 +872,7 @@ my_binary_30, id FROM t1_values WHERE select_id = 154 OR select_id IS NULL order by id; SHOW CREATE VIEW v1; View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select length(`t1_values`.`my_binary_30`) AS `LENGTH(my_binary_30)`,`t1_values`.`my_binary_30` AS `my_binary_30`,`t1_values`.`id` AS `id` from `t1_values` latin1 latin1_swedish_ci +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select octet_length(`t1_values`.`my_binary_30`) AS `LENGTH(my_binary_30)`,`t1_values`.`my_binary_30` AS `my_binary_30`,`t1_values`.`id` AS `id` from `t1_values` latin1 latin1_swedish_ci SELECT v1.* FROM v1 WHERE v1.id IN (SELECT id FROM t1_values WHERE select_id = 154 OR select_id IS NULL) order by id; @@ -886,7 +886,7 @@ my_varchar_1000, id FROM t1_values WHERE select_id = 153 OR select_id IS NULL order by id; SHOW CREATE VIEW v1; View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select length(`t1_values`.`my_varchar_1000`) AS `LENGTH(my_varchar_1000)`,`t1_values`.`my_varchar_1000` AS `my_varchar_1000`,`t1_values`.`id` AS `id` from `t1_values` latin1 latin1_swedish_ci +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select octet_length(`t1_values`.`my_varchar_1000`) AS `LENGTH(my_varchar_1000)`,`t1_values`.`my_varchar_1000` AS `my_varchar_1000`,`t1_values`.`id` AS `id` from `t1_values` latin1 latin1_swedish_ci SELECT v1.* FROM v1 WHERE v1.id IN (SELECT id FROM t1_values WHERE select_id = 153 OR select_id IS NULL) order by id; @@ -900,7 +900,7 @@ my_char_30, id FROM t1_values WHERE select_id = 152 OR select_id IS NULL order by id; SHOW CREATE VIEW v1; View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select length(`t1_values`.`my_char_30`) AS `LENGTH(my_char_30)`,`t1_values`.`my_char_30` AS `my_char_30`,`t1_values`.`id` AS `id` from `t1_values` latin1 latin1_swedish_ci +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select octet_length(`t1_values`.`my_char_30`) AS `LENGTH(my_char_30)`,`t1_values`.`my_char_30` AS `my_char_30`,`t1_values`.`id` AS `id` from `t1_values` latin1 latin1_swedish_ci SELECT v1.* FROM v1 WHERE v1.id IN (SELECT id FROM t1_values WHERE select_id = 152 OR select_id IS NULL) order by id; diff --git a/mysql-test/suite/funcs_1/r/myisam_func_view.result b/mysql-test/suite/funcs_1/r/myisam_func_view.result index 765869a312d..3a75e4abac5 100644 --- a/mysql-test/suite/funcs_1/r/myisam_func_view.result +++ b/mysql-test/suite/funcs_1/r/myisam_func_view.result @@ -858,7 +858,7 @@ my_varbinary_1000, id FROM t1_values WHERE select_id = 155 OR select_id IS NULL order by id; SHOW CREATE VIEW v1; View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select length(`t1_values`.`my_varbinary_1000`) AS `LENGTH(my_varbinary_1000)`,`t1_values`.`my_varbinary_1000` AS `my_varbinary_1000`,`t1_values`.`id` AS `id` from `t1_values` latin1 latin1_swedish_ci +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select octet_length(`t1_values`.`my_varbinary_1000`) AS `LENGTH(my_varbinary_1000)`,`t1_values`.`my_varbinary_1000` AS `my_varbinary_1000`,`t1_values`.`id` AS `id` from `t1_values` latin1 latin1_swedish_ci SELECT v1.* FROM v1 WHERE v1.id IN (SELECT id FROM t1_values WHERE select_id = 155 OR select_id IS NULL) order by id; @@ -872,7 +872,7 @@ my_binary_30, id FROM t1_values WHERE select_id = 154 OR select_id IS NULL order by id; SHOW CREATE VIEW v1; View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select length(`t1_values`.`my_binary_30`) AS `LENGTH(my_binary_30)`,`t1_values`.`my_binary_30` AS `my_binary_30`,`t1_values`.`id` AS `id` from `t1_values` latin1 latin1_swedish_ci +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select octet_length(`t1_values`.`my_binary_30`) AS `LENGTH(my_binary_30)`,`t1_values`.`my_binary_30` AS `my_binary_30`,`t1_values`.`id` AS `id` from `t1_values` latin1 latin1_swedish_ci SELECT v1.* FROM v1 WHERE v1.id IN (SELECT id FROM t1_values WHERE select_id = 154 OR select_id IS NULL) order by id; @@ -886,7 +886,7 @@ my_varchar_1000, id FROM t1_values WHERE select_id = 153 OR select_id IS NULL order by id; SHOW CREATE VIEW v1; View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select length(`t1_values`.`my_varchar_1000`) AS `LENGTH(my_varchar_1000)`,`t1_values`.`my_varchar_1000` AS `my_varchar_1000`,`t1_values`.`id` AS `id` from `t1_values` latin1 latin1_swedish_ci +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select octet_length(`t1_values`.`my_varchar_1000`) AS `LENGTH(my_varchar_1000)`,`t1_values`.`my_varchar_1000` AS `my_varchar_1000`,`t1_values`.`id` AS `id` from `t1_values` latin1 latin1_swedish_ci SELECT v1.* FROM v1 WHERE v1.id IN (SELECT id FROM t1_values WHERE select_id = 153 OR select_id IS NULL) order by id; @@ -900,7 +900,7 @@ my_char_30, id FROM t1_values WHERE select_id = 152 OR select_id IS NULL order by id; SHOW CREATE VIEW v1; View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select length(`t1_values`.`my_char_30`) AS `LENGTH(my_char_30)`,`t1_values`.`my_char_30` AS `my_char_30`,`t1_values`.`id` AS `id` from `t1_values` latin1 latin1_swedish_ci +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select octet_length(`t1_values`.`my_char_30`) AS `LENGTH(my_char_30)`,`t1_values`.`my_char_30` AS `my_char_30`,`t1_values`.`id` AS `id` from `t1_values` latin1 latin1_swedish_ci SELECT v1.* FROM v1 WHERE v1.id IN (SELECT id FROM t1_values WHERE select_id = 152 OR select_id IS NULL) order by id; diff --git a/mysql-test/suite/gcol/r/gcol_supported_sql_funcs_innodb.result b/mysql-test/suite/gcol/r/gcol_supported_sql_funcs_innodb.result index f8f447d493d..5e165b42be7 100644 --- a/mysql-test/suite/gcol/r/gcol_supported_sql_funcs_innodb.result +++ b/mysql-test/suite/gcol/r/gcol_supported_sql_funcs_innodb.result @@ -915,7 +915,7 @@ Table Create Table t1 CREATE TABLE `t1` ( `a` varchar(10) DEFAULT NULL, `b` varchar(10) DEFAULT NULL, - `c` varchar(20) GENERATED ALWAYS AS (insert(`a`,length(`a`),length(`b`),`b`)) VIRTUAL + `c` varchar(20) GENERATED ALWAYS AS (insert(`a`,octet_length(`a`),octet_length(`b`),`b`)) VIRTUAL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 insert into t1 values ('start,','end',default); select * from t1; @@ -978,7 +978,7 @@ show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` varchar(10) DEFAULT NULL, - `b` int(11) GENERATED ALWAYS AS (length(`a`)) VIRTUAL + `b` int(11) GENERATED ALWAYS AS (octet_length(`a`)) VIRTUAL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 insert into t1 values ('text',default); select * from t1; @@ -1142,7 +1142,7 @@ show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` varchar(10) DEFAULT NULL, - `b` int(11) GENERATED ALWAYS AS (length(`a`)) VIRTUAL + `b` int(11) GENERATED ALWAYS AS (octet_length(`a`)) VIRTUAL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 insert into t1 values ('text',default); select * from t1; diff --git a/mysql-test/suite/gcol/r/gcol_supported_sql_funcs_myisam.result b/mysql-test/suite/gcol/r/gcol_supported_sql_funcs_myisam.result index 9db5f0c7c38..7a18bf2d8fb 100644 --- a/mysql-test/suite/gcol/r/gcol_supported_sql_funcs_myisam.result +++ b/mysql-test/suite/gcol/r/gcol_supported_sql_funcs_myisam.result @@ -915,7 +915,7 @@ Table Create Table t1 CREATE TABLE `t1` ( `a` varchar(10) DEFAULT NULL, `b` varchar(10) DEFAULT NULL, - `c` varchar(20) GENERATED ALWAYS AS (insert(`a`,length(`a`),length(`b`),`b`)) VIRTUAL + `c` varchar(20) GENERATED ALWAYS AS (insert(`a`,octet_length(`a`),octet_length(`b`),`b`)) VIRTUAL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 insert into t1 values ('start,','end',default); select * from t1; @@ -978,7 +978,7 @@ show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` varchar(10) DEFAULT NULL, - `b` int(11) GENERATED ALWAYS AS (length(`a`)) VIRTUAL + `b` int(11) GENERATED ALWAYS AS (octet_length(`a`)) VIRTUAL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 insert into t1 values ('text',default); select * from t1; @@ -1142,7 +1142,7 @@ show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` varchar(10) DEFAULT NULL, - `b` int(11) GENERATED ALWAYS AS (length(`a`)) VIRTUAL + `b` int(11) GENERATED ALWAYS AS (octet_length(`a`)) VIRTUAL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 insert into t1 values ('text',default); select * from t1; diff --git a/mysql-test/suite/rpl/r/rpl_charset.result b/mysql-test/suite/rpl/r/rpl_charset.result index a96cb26dd9d..5bda623172b 100644 --- a/mysql-test/suite/rpl/r/rpl_charset.result +++ b/mysql-test/suite/rpl/r/rpl_charset.result @@ -121,4 +121,27 @@ primary key (`pk`) set @p=_latin1 'test'; update t1 set pk='test' where pk=@p; drop table t1; +# +# Start of 10.3 tests +# +# +# MDEV-12685 Oracle-compatible function CHR() +# +connection master; +CREATE DATABASE db1 DEFAULT CHARACTER SET latin1 COLLATE latin1_bin; +USE db1; +CREATE TABLE t1 AS SELECT CHR(0x60); +connection slave; +SHOW CREATE TABLE db1.t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `CHR(0x60)` varchar(4) COLLATE latin1_bin DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin +connection master; +USE test; +DROP DATABASE db1; +connection slave; +# +# End of 10.3 tests +# include/rpl_end.inc diff --git a/mysql-test/suite/sql_sequence/alter.result b/mysql-test/suite/sql_sequence/alter.result new file mode 100644 index 00000000000..43afe2377c3 --- /dev/null +++ b/mysql-test/suite/sql_sequence/alter.result @@ -0,0 +1,238 @@ +drop table if exists t1; +Warnings: +Note 1051 Unknown table 'test.t1' +# +# Test alter sequence +# +CREATE SEQUENCE t1 nocache engine=myisam; +select * from t1; +next_value min_value max_value start increment cache cycle round +1 1 9223372036854775806 1 1 0 0 0 +select next value for t1; +next value for t1 +1 +alter sequence t1 start=50; +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` start with 50 minvalue 1 maxvalue 9223372036854775806 increment by 1 nocache nocycle ENGINE=MyISAM +select * from t1; +next_value min_value max_value start increment cache cycle round +2 1 9223372036854775806 50 1 0 0 0 +select next value for t1; +next value for t1 +2 +alter sequence t1 minvalue=-100; +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` start with 50 minvalue -100 maxvalue 9223372036854775806 increment by 1 nocache nocycle ENGINE=MyISAM +select * from t1; +next_value min_value max_value start increment cache cycle round +3 -100 9223372036854775806 50 1 0 0 0 +alter sequence t1 minvalue=100 start=100; +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` start with 100 minvalue 100 maxvalue 9223372036854775806 increment by 1 nocache nocycle ENGINE=MyISAM +select * from t1; +next_value min_value max_value start increment cache cycle round +3 100 9223372036854775806 100 1 0 0 0 +alter sequence t1 maxvalue=500; +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` start with 100 minvalue 100 maxvalue 500 increment by 1 nocache nocycle ENGINE=MyISAM +select * from t1; +next_value min_value max_value start increment cache cycle round +3 100 500 100 1 0 0 0 +drop sequence t1; +CREATE SEQUENCE t1 engine=myisam; +alter sequence t1 nocache; +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 nocache nocycle ENGINE=MyISAM +alter sequence t1 cache=100; +flush tables; +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 100 nocycle ENGINE=MyISAM +alter sequence t1 nocache; +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 nocache nocycle ENGINE=MyISAM +flush tables; +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 nocache nocycle ENGINE=MyISAM +select * from t1; +next_value min_value max_value start increment cache cycle round +1 1 9223372036854775806 1 1 0 0 0 +select next value for t1; +next value for t1 +1 +select next value for t1; +next value for t1 +2 +select next value for t1; +next value for t1 +3 +select next_value, round from t1; +next_value round +4 0 +drop sequence t1; +CREATE SEQUENCE t1 maxvalue=100 engine=myisam; +alter sequence t1 no maxvalue; +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM +select * from t1; +next_value min_value max_value start increment cache cycle round +1 1 9223372036854775806 1 1 1000 0 0 +alter sequence t1 cycle; +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 cycle ENGINE=MyISAM +alter sequence t1 nocycle; +alter sequence t1 start=15 restart minvalue=10 maxvalue=20 cycle; +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` start with 15 minvalue 10 maxvalue 20 increment by 1 cache 1000 cycle ENGINE=MyISAM +select * from t1; +next_value min_value max_value start increment cache cycle round +15 10 20 15 1 1000 1 0 +select NEXT VALUE for t1 from seq_1_to_10; +NEXT VALUE for t1 +15 +16 +17 +18 +19 +20 +10 +11 +12 +13 +alter sequence t1 restart with 17 minvalue=10 maxvalue=20 cycle; +select NEXT VALUE for t1 from seq_1_to_10; +NEXT VALUE for t1 +17 +18 +19 +20 +10 +11 +12 +13 +14 +15 +drop sequence t1; +CREATE SEQUENCE t1 maxvalue=100; +alter sequence t1 increment=-2 start with 50 minvalue=-100; +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` start with 50 minvalue -100 maxvalue 100 increment by -2 cache 1000 nocycle ENGINE=MyISAM +select * from t1; +next_value min_value max_value start increment cache cycle round +1 -100 100 50 -2 1000 0 0 +select NEXT VALUE for t1 from seq_1_to_10; +NEXT VALUE for t1 +1 +-1 +-3 +-5 +-7 +-9 +-11 +-13 +-15 +-17 +drop sequence t1; +# +# InnoDB (some things work different with InnoDB) + +CREATE SEQUENCE t1 cache 10 engine=innodb; +select * from t1; +next_value min_value max_value start increment cache cycle round +1 1 9223372036854775806 1 1 10 0 0 +select next value for t1; +next value for t1 +1 +alter sequence t1 start=100; +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` start with 100 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 10 nocycle ENGINE=InnoDB +select * from t1; +next_value min_value max_value start increment cache cycle round +11 1 9223372036854775806 100 1 10 0 0 +select next value for t1; +next value for t1 +11 +drop sequence t1; +# +# ALTER TABLE +# +CREATE SEQUENCE t1 engine=innodb; +select next value for t1; +next value for t1 +1 +alter table t1 rename t2; +select next value for t2; +next value for t2 +1001 +rename table t2 to t1; +select next value for t1; +next value for t1 +2001 +alter table t1 comment="foo"; +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=InnoDB COMMENT='foo' +alter table t1 engine=myisam; +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM COMMENT='foo' +alter table t1 engine=innodb; +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=InnoDB COMMENT='foo' +select * from t1; +next_value min_value max_value start increment cache cycle round +3001 1 9223372036854775806 1 1 1000 0 0 +drop sequence t1; +CREATE SEQUENCE t1 engine=myisam; +alter sequence t1 minvalue=100; +ERROR HY000: Sequence 'test.t1' values are conflicting +drop sequence t1; +CREATE SEQUENCE t1 engine=myisam; +alter sequence t1 minvalue=25 maxvalue=20; +ERROR HY000: Sequence 'test.t1' values are conflicting +drop sequence t1; +create table t1 (a int); +alter sequence t1 minvalue=100; +ERROR 42S02: 'test.t1' is not a SEQUENCE +drop table t1; +alter sequence if exists t1 minvalue=100; +Warnings: +Note 4067 Unknown SEQUENCE: 'test.t1' +alter sequence t1 minvalue=100; +ERROR 42S02: Table 'test.t1' doesn't exist +create sequence t1; +alter sequence t1; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1 +drop sequence t1; +CREATE SEQUENCE t1 maxvalue=100; +alter sequence t1 increment=-2 start with 50; +select next value for t1; +next value for t1 +1 +select next value for t1; +ERROR HY000: Sequence 'test.t1' has run out +select * from t1; +next_value min_value max_value start increment cache cycle round +0 1 100 50 -2 1000 0 0 +alter sequence t1 restart; +select next value for t1; +next value for t1 +50 +alter sequence t1 restart with 90; +select next value for t1; +next value for t1 +90 +drop sequence t1; diff --git a/mysql-test/suite/sql_sequence/alter.test b/mysql-test/suite/sql_sequence/alter.test new file mode 100644 index 00000000000..7454f5900f7 --- /dev/null +++ b/mysql-test/suite/sql_sequence/alter.test @@ -0,0 +1,139 @@ +--source include/have_sequence.inc +--source include/have_innodb.inc + +drop table if exists t1; + +--echo # +--echo # Test alter sequence +--echo # + +CREATE SEQUENCE t1 nocache engine=myisam; +select * from t1; +select next value for t1; +alter sequence t1 start=50; +show create sequence t1; +select * from t1; +select next value for t1; + +alter sequence t1 minvalue=-100; +show create sequence t1; +select * from t1; +alter sequence t1 minvalue=100 start=100; +show create sequence t1; +select * from t1; + +alter sequence t1 maxvalue=500; +show create sequence t1; +select * from t1; +drop sequence t1; + +CREATE SEQUENCE t1 engine=myisam; +alter sequence t1 nocache; +show create sequence t1; +alter sequence t1 cache=100; +flush tables; +show create sequence t1; +alter sequence t1 nocache; +show create sequence t1; +flush tables; +show create sequence t1; +select * from t1; +select next value for t1; +select next value for t1; +select next value for t1; +select next_value, round from t1; +drop sequence t1; + +CREATE SEQUENCE t1 maxvalue=100 engine=myisam; +alter sequence t1 no maxvalue; +show create sequence t1; +select * from t1; +alter sequence t1 cycle; +show create sequence t1; +alter sequence t1 nocycle; +alter sequence t1 start=15 restart minvalue=10 maxvalue=20 cycle; +show create sequence t1; +select * from t1; +select NEXT VALUE for t1 from seq_1_to_10; +alter sequence t1 restart with 17 minvalue=10 maxvalue=20 cycle; +select NEXT VALUE for t1 from seq_1_to_10; +drop sequence t1; + +CREATE SEQUENCE t1 maxvalue=100; +alter sequence t1 increment=-2 start with 50 minvalue=-100; +show create sequence t1; +select * from t1; +select NEXT VALUE for t1 from seq_1_to_10; +drop sequence t1; + +--echo # +--echo # InnoDB (some things work different with InnoDB) +--echo + +CREATE SEQUENCE t1 cache 10 engine=innodb; +select * from t1; +select next value for t1; +alter sequence t1 start=100; +show create sequence t1; +select * from t1; +select next value for t1; +drop sequence t1; + +--echo # +--echo # ALTER TABLE +--echo # + +CREATE SEQUENCE t1 engine=innodb; +select next value for t1; +alter table t1 rename t2; +select next value for t2; +rename table t2 to t1; +select next value for t1; +alter table t1 comment="foo"; +show create sequence t1; +alter table t1 engine=myisam; +show create sequence t1; +alter table t1 engine=innodb; +show create sequence t1; +select * from t1; +drop sequence t1; + +# +# Some error testing +# + +CREATE SEQUENCE t1 engine=myisam; +--error ER_SEQUENCE_INVALID_DATA +alter sequence t1 minvalue=100; +drop sequence t1; + +CREATE SEQUENCE t1 engine=myisam; +--error ER_SEQUENCE_INVALID_DATA +alter sequence t1 minvalue=25 maxvalue=20; +drop sequence t1; + +create table t1 (a int); +--error ER_NOT_SEQUENCE +alter sequence t1 minvalue=100; +drop table t1; + +alter sequence if exists t1 minvalue=100; +--error ER_NO_SUCH_TABLE +alter sequence t1 minvalue=100; + +create sequence t1; +--error ER_PARSE_ERROR +alter sequence t1; +drop sequence t1; + +CREATE SEQUENCE t1 maxvalue=100; +alter sequence t1 increment=-2 start with 50; +select next value for t1; +--error ER_SEQUENCE_RUN_OUT +select next value for t1; +select * from t1; +alter sequence t1 restart; +select next value for t1; +alter sequence t1 restart with 90; +select next value for t1; +drop sequence t1; diff --git a/mysql-test/suite/sql_sequence/create.result b/mysql-test/suite/sql_sequence/create.result index 59dfe62acee..4962752c7d9 100644 --- a/mysql-test/suite/sql_sequence/create.result +++ b/mysql-test/suite/sql_sequence/create.result @@ -186,6 +186,8 @@ create sequence t1 start with 10 maxvalue=9223372036854775807; ERROR HY000: Sequence 'test.t1' values are conflicting create sequence t1 start with 10 minvalue=-9223372036854775808; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '9223372036854775808' at line 1 +create sequence t1 RESTART WITH 10; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'RESTART' at line 1 create or replace sequence t1 start with 10 NO MINVALUE minvalue=1; drop sequence t1; create sequence t1; diff --git a/mysql-test/suite/sql_sequence/create.test b/mysql-test/suite/sql_sequence/create.test index 6cb6dedd91b..cf094c2cedd 100644 --- a/mysql-test/suite/sql_sequence/create.test +++ b/mysql-test/suite/sql_sequence/create.test @@ -118,6 +118,8 @@ create or replace sequence t1 start with 10 min_value=1 NO MINVALUE; create sequence t1 start with 10 maxvalue=9223372036854775807; --error ER_PARSE_ERROR create sequence t1 start with 10 minvalue=-9223372036854775808; +--error ER_PARSE_ERROR +create sequence t1 RESTART WITH 10; # This should probably give an error create or replace sequence t1 start with 10 NO MINVALUE minvalue=1; diff --git a/mysql-test/suite/sql_sequence/next.result b/mysql-test/suite/sql_sequence/next.result index fc28152a2b7..84f91bc0bdd 100644 --- a/mysql-test/suite/sql_sequence/next.result +++ b/mysql-test/suite/sql_sequence/next.result @@ -390,6 +390,16 @@ next_value min_value max_value start increment cache cycle round select next value for s1; next value for s1 3984356 +explain extended select next value for s1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 select nextval(`test`.`s1`) AS `next value for s1` +explain extended select previous value for s1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 select lastval(`test`.`s1`) AS `previous value for s1` drop sequence s1; create table t1 (a int); select next value for t1; diff --git a/mysql-test/suite/sql_sequence/next.test b/mysql-test/suite/sql_sequence/next.test index 426ee5709a1..472feafb2c6 100644 --- a/mysql-test/suite/sql_sequence/next.test +++ b/mysql-test/suite/sql_sequence/next.test @@ -182,9 +182,10 @@ drop table t1,s1; CREATE OR REPLACE SEQUENCE s1 MINVALUE 1 MAXVALUE 9999999999 INCREMENT BY 1 START WITH 3984356 nocache CYCLE engine='innodb'; select * from s1; select next value for s1; +explain extended select next value for s1; +explain extended select previous value for s1; drop sequence s1; - # # Some error testing # diff --git a/mysql-test/suite/sql_sequence/other.result b/mysql-test/suite/sql_sequence/other.result new file mode 100644 index 00000000000..b9510d46de7 --- /dev/null +++ b/mysql-test/suite/sql_sequence/other.result @@ -0,0 +1,42 @@ +# +# Create and check +# +create sequence s1 engine=innodb; +check table s1; +Table Op Msg_type Msg_text +test.s1 check note The storage engine for the table doesn't support check +select next value for s1; +next value for s1 +1 +flush tables; +check table s1; +Table Op Msg_type Msg_text +test.s1 check note The storage engine for the table doesn't support check +select next value for s1; +next value for s1 +1001 +flush tables; +repair table s1; +Table Op Msg_type Msg_text +test.s1 repair note The storage engine for the table doesn't support repair +select next value for s1; +next value for s1 +2001 +drop sequence s1; +create or replace sequence s1 engine=innodb; +select next value for s1; +next value for s1 +1 +repair table s1; +Table Op Msg_type Msg_text +test.s1 repair note The storage engine for the table doesn't support repair +check table s1; +Table Op Msg_type Msg_text +test.s1 check note The storage engine for the table doesn't support check +select next value for s1; +next value for s1 +1001 +select * from s1; +next_value min_value max_value start increment cache cycle round +2001 1 9223372036854775806 1 1 1000 0 0 +drop sequence s1; diff --git a/mysql-test/suite/sql_sequence/other.test b/mysql-test/suite/sql_sequence/other.test new file mode 100644 index 00000000000..c9ed326004a --- /dev/null +++ b/mysql-test/suite/sql_sequence/other.test @@ -0,0 +1,29 @@ +--source include/have_sequence.inc +--source include/have_innodb.inc + +# +# Test various combinations of operations on sequence +# + +--echo # +--echo # Create and check +--echo # + +create sequence s1 engine=innodb; +check table s1; +select next value for s1; +flush tables; +check table s1; +select next value for s1; +flush tables; +repair table s1; +select next value for s1; +drop sequence s1; + +create or replace sequence s1 engine=innodb; +select next value for s1; +repair table s1; +check table s1; +select next value for s1; +select * from s1; +drop sequence s1; diff --git a/mysql-test/suite/sql_sequence/read_only.test b/mysql-test/suite/sql_sequence/read_only.test index d8743617ad2..04dab2bb525 100644 --- a/mysql-test/suite/sql_sequence/read_only.test +++ b/mysql-test/suite/sql_sequence/read_only.test @@ -1,4 +1,5 @@ --source include/have_innodb.inc +--source include/not_embedded.inc # # Test innodb read only diff --git a/mysql-test/suite/sql_sequence/setval.result b/mysql-test/suite/sql_sequence/setval.result new file mode 100644 index 00000000000..2fe46ff90c2 --- /dev/null +++ b/mysql-test/suite/sql_sequence/setval.result @@ -0,0 +1,246 @@ +drop table if exists t1; +Warnings: +Note 1051 Unknown table 'test.t1' +# +# Test setval function +# +CREATE SEQUENCE t1 cache 10 engine=myisam; +select next_value,round from t1; +next_value round +1 0 +do setval(t1,10); +select next_value,round from t1; +next_value round +11 0 +select next value for t1; +next value for t1 +11 +do setval(t1,12,1); +select next_value,round from t1; +next_value round +21 0 +select next value for t1; +next value for t1 +13 +do setval(t1,15,0); +select next_value,round from t1; +next_value round +21 0 +select next value for t1; +next value for t1 +15 +select setval(t1,16,0); +setval(t1,16,0) +16 +select next value for t1; +next value for t1 +16 +do setval(t1,1000,0); +select next value for t1; +next value for t1 +1000 +select next_value,round from t1; +next_value round +1010 0 +do setval(t1,2000,0); +select next value for t1; +next value for t1 +2000 +select next_value,round from t1; +next_value round +2010 0 +select setval(t1,1000,0); +setval(t1,1000,0) +NULL +select next value for t1; +next value for t1 +2001 +select setval(t1,1000,TRUE); +setval(t1,1000,TRUE) +NULL +select next value for t1; +next value for t1 +2002 +select next_value,round from t1; +next_value round +2010 0 +select setval(t1,2002,0); +setval(t1,2002,0) +NULL +select next value for t1; +next value for t1 +2003 +select setval(t1,2010,0); +setval(t1,2010,0) +2010 +select next value for t1; +next value for t1 +2010 +select next_value,round from t1; +next_value round +2020 0 +drop sequence t1; +# +# Testing with cycle +# +CREATE SEQUENCE t1 cache=10 maxvalue=100 cycle engine=innodb; +select next_value,round from t1; +next_value round +1 0 +select setval(t1,100,0); +setval(t1,100,0) +100 +select next_value,round from t1; +next_value round +100 0 +select next value for t1; +next value for t1 +100 +select next_value,round from t1; +next_value round +101 0 +select setval(t1,100,0); +setval(t1,100,0) +NULL +select next_value,round from t1; +next_value round +101 0 +select next value for t1; +next value for t1 +1 +select next_value,round from t1; +next_value round +11 1 +select next value for t1; +next value for t1 +2 +select setval(t1,100,0,1); +setval(t1,100,0,1) +100 +select next_value,round from t1; +next_value round +100 1 +select next value for t1; +next value for t1 +100 +select setval(t1,100,1,2); +setval(t1,100,1,2) +100 +select next_value,round from t1; +next_value round +101 2 +select next value for t1; +next value for t1 +1 +select setval(t1,100,0,3); +setval(t1,100,0,3) +100 +select next_value,round from t1; +next_value round +100 3 +select next value for t1; +next value for t1 +100 +drop sequence t1; +# +# Testing extreme values +# +CREATE SEQUENCE t1 cache=10 maxvalue=100 engine=innodb; +select next_value,round from t1; +next_value round +1 0 +select setval(t1,200); +setval(t1,200) +200 +select next_value,round from t1; +next_value round +101 0 +select next value for t1; +ERROR HY000: Sequence 'test.t1' has run out +drop sequence t1; +CREATE SEQUENCE t1 cache=10 maxvalue=100 cycle engine=innodb; +select next_value,round from t1; +next_value round +1 0 +select setval(t1,200); +setval(t1,200) +200 +select next_value,round from t1; +next_value round +101 0 +select next value for t1; +next value for t1 +1 +drop sequence t1; +CREATE SEQUENCE t1 cache=10 maxvalue=0 increment=-10; +select setval(t1,-10); +setval(t1,-10) +-10 +select next_value,round from t1; +next_value round +-20 0 +select next value for t1; +next value for t1 +-20 +select setval(t1,-15); +setval(t1,-15) +NULL +select next_value,round from t1; +next_value round +-120 0 +select next value for t1; +next value for t1 +-30 +select setval(t1,-500,FALSE); +setval(t1,-500,FALSE) +-500 +select next value for t1; +next value for t1 +-500 +select next value for t1; +next value for t1 +-510 +select setval(t1,-525,0); +setval(t1,-525,0) +-525 +select next value for t1; +next value for t1 +-525 +select next value for t1; +next value for t1 +-535 +drop sequence t1; +CREATE SEQUENCE t1 cache=10 maxvalue=0 increment=-10; +select setval(t1,-10,0); +setval(t1,-10,0) +-10 +select next_value,round from t1; +next_value round +-10 0 +select next value for t1; +next value for t1 +-10 +drop sequence t1; +# +# Other testing +# +CREATE SEQUENCE t1; +select setval(t1,10,0),setval(t1,15,1),setval(t1,5,1); +setval(t1,10,0) setval(t1,15,1) setval(t1,5,1) +10 15 NULL +select next value for t1; +next value for t1 +16 +select next_value,round from t1; +next_value round +1016 0 +explain extended select setval(t1,100),setval(t1,100,TRUE),setval(t1,100,FALSE,50); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 select setval(`test`.`t1`,100,1,0) AS `setval(t1,100)`,setval(`test`.`t1`,100,1,0) AS `setval(t1,100,TRUE)`,setval(`test`.`t1`,100,0,50) AS `setval(t1,100,FALSE,50)` +drop sequence t1; +create table t1 (a int); +select setval(t1,10); +ERROR 42S02: 'test.t1' is not a SEQUENCE +drop table t1; diff --git a/mysql-test/suite/sql_sequence/setval.test b/mysql-test/suite/sql_sequence/setval.test new file mode 100644 index 00000000000..fe0c0669494 --- /dev/null +++ b/mysql-test/suite/sql_sequence/setval.test @@ -0,0 +1,126 @@ +--source include/have_sequence.inc +--source include/have_innodb.inc + +drop table if exists t1; + +--echo # +--echo # Test setval function +--echo # + +CREATE SEQUENCE t1 cache 10 engine=myisam; +select next_value,round from t1; +do setval(t1,10); +select next_value,round from t1; +select next value for t1; +do setval(t1,12,1); +select next_value,round from t1; +select next value for t1; +do setval(t1,15,0); +select next_value,round from t1; +select next value for t1; +select setval(t1,16,0); +select next value for t1; +do setval(t1,1000,0); +select next value for t1; +select next_value,round from t1; +do setval(t1,2000,0); +select next value for t1; +select next_value,round from t1; +# Set smaller value +select setval(t1,1000,0); +select next value for t1; +select setval(t1,1000,TRUE); +select next value for t1; +select next_value,round from t1; +select setval(t1,2002,0); +select next value for t1; +select setval(t1,2010,0); +select next value for t1; +select next_value,round from t1; +drop sequence t1; + +--echo # +--echo # Testing with cycle +--echo # + +CREATE SEQUENCE t1 cache=10 maxvalue=100 cycle engine=innodb; +select next_value,round from t1; +select setval(t1,100,0); +select next_value,round from t1; +select next value for t1; +select next_value,round from t1; +select setval(t1,100,0); +select next_value,round from t1; +select next value for t1; +select next_value,round from t1; +select next value for t1; +select setval(t1,100,0,1); +select next_value,round from t1; +select next value for t1; +select setval(t1,100,1,2); +select next_value,round from t1; +select next value for t1; +select setval(t1,100,0,3); +select next_value,round from t1; +select next value for t1; +drop sequence t1; + +--echo # +--echo # Testing extreme values +--echo # + +CREATE SEQUENCE t1 cache=10 maxvalue=100 engine=innodb; +select next_value,round from t1; +select setval(t1,200); +select next_value,round from t1; +--error ER_SEQUENCE_RUN_OUT +select next value for t1; +drop sequence t1; + +CREATE SEQUENCE t1 cache=10 maxvalue=100 cycle engine=innodb; +select next_value,round from t1; +select setval(t1,200); +select next_value,round from t1; +select next value for t1; +drop sequence t1; + +CREATE SEQUENCE t1 cache=10 maxvalue=0 increment=-10; +select setval(t1,-10); +select next_value,round from t1; +select next value for t1; +select setval(t1,-15); +select next_value,round from t1; +select next value for t1; +select setval(t1,-500,FALSE); +select next value for t1; +select next value for t1; +select setval(t1,-525,0); +select next value for t1; +select next value for t1; +drop sequence t1; + +CREATE SEQUENCE t1 cache=10 maxvalue=0 increment=-10; +select setval(t1,-10,0); +select next_value,round from t1; +select next value for t1; +drop sequence t1; + +--echo # +--echo # Other testing +--echo # + +CREATE SEQUENCE t1; +select setval(t1,10,0),setval(t1,15,1),setval(t1,5,1); +select next value for t1; +select next_value,round from t1; +explain extended select setval(t1,100),setval(t1,100,TRUE),setval(t1,100,FALSE,50); +drop sequence t1; + +# +# Some error testing +# + +create table t1 (a int); +--error ER_NOT_SEQUENCE +select setval(t1,10); +drop table t1; diff --git a/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result b/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result index 459e0126fea..7ceea1a295c 100644 --- a/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result +++ b/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result @@ -2909,9 +2909,9 @@ READ_ONLY YES COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_MAX_STATEMENT_CLASSES SESSION_VALUE NULL -GLOBAL_VALUE 189 +GLOBAL_VALUE 190 GLOBAL_VALUE_ORIGIN COMPILE-TIME -DEFAULT_VALUE 189 +DEFAULT_VALUE 190 VARIABLE_SCOPE GLOBAL VARIABLE_TYPE BIGINT UNSIGNED VARIABLE_COMMENT Maximum number of statement instruments. diff --git a/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result b/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result index 48514eea424..957485b4e4b 100644 --- a/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result +++ b/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result @@ -3105,9 +3105,9 @@ READ_ONLY YES COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_MAX_STATEMENT_CLASSES SESSION_VALUE NULL -GLOBAL_VALUE 189 +GLOBAL_VALUE 190 GLOBAL_VALUE_ORIGIN COMPILE-TIME -DEFAULT_VALUE 189 +DEFAULT_VALUE 190 VARIABLE_SCOPE GLOBAL VARIABLE_TYPE BIGINT UNSIGNED VARIABLE_COMMENT Maximum number of statement instruments. diff --git a/mysql-test/suite/vcol/r/vcol_supported_sql_funcs.result b/mysql-test/suite/vcol/r/vcol_supported_sql_funcs.result index 9413dbdace7..dd767071dc9 100644 --- a/mysql-test/suite/vcol/r/vcol_supported_sql_funcs.result +++ b/mysql-test/suite/vcol/r/vcol_supported_sql_funcs.result @@ -915,7 +915,7 @@ Table Create Table t1 CREATE TABLE `t1` ( `a` varchar(10) DEFAULT NULL, `b` varchar(10) DEFAULT NULL, - `c` varchar(20) GENERATED ALWAYS AS (insert(`a`,length(`a`),length(`b`),`b`)) VIRTUAL + `c` varchar(20) GENERATED ALWAYS AS (insert(`a`,octet_length(`a`),octet_length(`b`),`b`)) VIRTUAL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 insert into t1 values ('start,','end',default); select * from t1; @@ -978,7 +978,7 @@ show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` varchar(10) DEFAULT NULL, - `b` int(11) GENERATED ALWAYS AS (length(`a`)) VIRTUAL + `b` int(11) GENERATED ALWAYS AS (octet_length(`a`)) VIRTUAL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 insert into t1 values ('text',default); select * from t1; @@ -1142,7 +1142,7 @@ show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` varchar(10) DEFAULT NULL, - `b` int(11) GENERATED ALWAYS AS (length(`a`)) VIRTUAL + `b` int(11) GENERATED ALWAYS AS (octet_length(`a`)) VIRTUAL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 insert into t1 values ('text',default); select * from t1; diff --git a/mysql-test/t/func_str.test b/mysql-test/t/func_str.test index bb3251b3e31..29a9510db00 100644 --- a/mysql-test/t/func_str.test +++ b/mysql-test/t/func_str.test @@ -1853,6 +1853,16 @@ SELECT f1,HEX(f2) FROM t1 WHERE f1='YQ==' AND (f2= from_base64( SELECT f1,HEX(f2) FROM t1 WHERE f1='YQ==' AND (f2= from_base64("Yq==") OR f2= from_base64("YQ==")); DROP TABLE t1; +--echo # +--echo # MDEV-12685 Oracle-compatible function CHR() +--echo # +select chr(65); +create database mysqltest1 CHARACTER SET = 'utf8' COLLATE = 'utf8_bin'; +use mysqltest1; +select charset(chr(65)), length(chr(65)),char_length(chr(65)); +select charset(chr(14844588)), length(chr(14844588)),char_length(chr(14844588)); +drop database mysqltest1; +use test; --echo # --echo # End of 10.1 tests diff --git a/mysql-test/t/gis.test b/mysql-test/t/gis.test index 34797337b1d..ab9c792d523 100644 --- a/mysql-test/t/gis.test +++ b/mysql-test/t/gis.test @@ -2294,5 +2294,18 @@ CALL p1('geometrycollection'); DROP PROCEDURE p1; --echo # +--echo # MDEV-12798 Item_param does not preserve exact field type in EXECUTE IMMEDIATE 'CREATE TABLE AS SELECT ?' USING POINT(1,1) +--echo # +EXECUTE IMMEDIATE 'CREATE TABLE t1 AS SELECT ?' USING POINT(1,1); +SHOW CREATE TABLE t1; +DROP TABLE t1; + +PREPARE stmt FROM 'CREATE OR REPLACE TABLE t1 AS SELECT ?'; +EXECUTE stmt USING POINT(1,1); +SHOW CREATE TABLE t1; +DROP TABLE t1; + + +--echo # --echo # End of 10.3 tests --echo # diff --git a/mysql-test/t/union.test b/mysql-test/t/union.test index 04ab71588be..ce8b2bc9c2a 100644 --- a/mysql-test/t/union.test +++ b/mysql-test/t/union.test @@ -1545,5 +1545,132 @@ SHOW CREATE TABLE t1; DROP TABLE t1; --echo # +--echo # MDEV-9495 Wrong field type for a UNION of a signed and an unsigned INT expression +--echo # +CREATE TABLE t1 (a INT, b INT UNSIGNED); +INSERT INTO t1 VALUES (0x7FFFFFFF,0xFFFFFFFF); +CREATE TABLE t2 AS SELECT a FROM t1 UNION SELECT b FROM t1; +SHOW CREATE TABLE t2; +SELECT * FROM t2 ORDER BY a; +DROP TABLE t2; +CREATE TABLE t2 AS SELECT COALESCE(a,b), COALESCE(b,a) FROM t1; +SHOW CREATE TABLE t2; +SELECT * FROM t2; +DROP TABLE t2; +DROP TABLE t1; + +--echo # +--echo # MDEV-9497 UNION and COALESCE produce different field types for DECIMAL+INT +--echo # +CREATE TABLE t1 AS SELECT COALESCE(10.1,CAST(10 AS UNSIGNED)) AS a; +SHOW CREATE TABLE t1; +DROP TABLE t1; +CREATE OR REPLACE TABLE t1 AS SELECT 10.1 AS a UNION SELECT CAST(10 AS UNSIGNED); +SHOW CREATE TABLE t1; +DROP TABLE t1; + +--echo # +--echo # MDEV-12594 UNION between fixed length double columns does not always preserve scale +--echo # +CREATE TABLE t1 (a FLOAT(20,4), b FLOAT(20,3), c FLOAT(20,4)); +INSERT INTO t1 VALUES (1111,2222,3333); + +CREATE TABLE t2 AS SELECT a FROM t1 UNION SELECT a FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t2; + +CREATE OR REPLACE TABLE t2 SELECT a FROM t1 UNION SELECT c FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t2; + +CREATE OR REPLACE TABLE t2 SELECT b FROM t1 UNION SELECT b FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t2; + +CREATE OR REPLACE TABLE t2 SELECT c FROM t1 UNION SELECT c FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t2; + +CREATE OR REPLACE TABLE t2 SELECT c FROM t1 UNION SELECT a FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t2; + +CREATE OR REPLACE TABLE t2 AS SELECT a FROM t1 UNION SELECT b FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t2; + +CREATE OR REPLACE TABLE t2 AS SELECT b FROM t1 UNION SELECT a FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t2; + +DROP TABLE t1; + +--echo # Corner case +CREATE TABLE t1 (a FLOAT(255,4), b FLOAT(255,3)); +INSERT INTO t1 VALUES (1111,2222); +CREATE OR REPLACE TABLE t2 AS SELECT b FROM t1 UNION SELECT a FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t2; +DROP TABLE t1; + + +--echo # +--echo # MDEV-12595 UNION converts INT to BIGINT +--echo # +CREATE TABLE t1 AS SELECT + 1, + -1, + COALESCE(1,1), + COALESCE(-1,-1), + COALESCE(1,-1), + COALESCE(-1,1); +SHOW CREATE TABLE t1; +DROP TABLE t1; +CREATE TABLE t1 AS SELECT 1 AS c1,1 AS c2,-1 AS c3,-1 AS c4 UNION SELECT 1,-1,1,-1; +SHOW CREATE TABLE t1; +DROP TABLE t1; + + +--echo # +--echo # MDEV-12599 UNION is not symmetric when mixing INT and CHAR +--echo # + +CREATE OR REPLACE TABLE t1 AS SELECT 1 AS c1, 'a' AS c2 UNION SELECT 'a', 1; +SHOW CREATE TABLE t1; +DROP TABLE t1; + +CREATE OR REPLACE TABLE t1 AS SELECT 11112222 AS c1, 'a' AS c2 UNION SELECT 'a', 11112222; +SHOW CREATE TABLE t1; +DROP TABLE t1; + + +CREATE OR REPLACE TABLE t1 AS SELECT 111122223333 AS c1, 'a' AS c2 UNION SELECT 'a', 111122223333; +SHOW CREATE TABLE t1; +DROP TABLE t1; + +CREATE OR REPLACE TABLE t1 AS SELECT 1111222233334444 AS c1, 'a' AS c2 UNION SELECT 'a', 1111222233334444; +SHOW CREATE TABLE t1; +DROP TABLE t1; + +CREATE TABLE t1 (a INT(3), b VARCHAR(1)); +CREATE TABLE t2 AS SELECT a,b FROM t1 UNION SELECT b,a FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t2; +DROP TABLE t1; + +CREATE TABLE t1 (a BIGINT(3), b VARCHAR(1)); +CREATE TABLE t2 AS SELECT a,b FROM t1 UNION SELECT b,a FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t2; +DROP TABLE t1; + +CREATE TABLE t1 (a BIGINT(12), b VARCHAR(1)); +CREATE TABLE t2 AS SELECT a,b FROM t1 UNION SELECT b,a FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t2; +DROP TABLE t1; + + +--echo # --echo # End of 10.3 tests --echo # |