diff options
Diffstat (limited to 'mysql-test/r')
36 files changed, 390 insertions, 149 deletions
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 |