1# 2# Start of 10.4 tests 3# 4# 5# MDEV-16426 Optimizer erroneously treats equal constants of different formats as same 6# 7SET NAMES utf8; 8CREATE TABLE t1 (a DECIMAL(10,3)); 9INSERT INTO t1 VALUES (10.0),(10.1); 10SELECT CHARSET('a'),CHARSET(0x61),LENGTH(CHARSET('a'))+a,LENGTH(CHARSET(0x61))+a FROM t1; 11CHARSET('a') CHARSET(0x61) LENGTH(CHARSET('a'))+a LENGTH(CHARSET(0x61))+a 12utf8 binary 14.000 16.000 13utf8 binary 14.100 16.100 14SELECT * FROM t1 WHERE LENGTH(CHARSET('a'))+a<=>LENGTH(CHARSET(0x61))+a; 15a 16EXPLAIN EXTENDED SELECT * FROM t1 WHERE LENGTH(CHARSET('a'))+a<=>LENGTH(CHARSET(0x61))+a; 17id select_type table type possible_keys key key_len ref rows filtered Extra 181 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where 19Warnings: 20Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where <cache>(octet_length(charset('a'))) + `test`.`t1`.`a` <=> <cache>(octet_length(charset(0x61))) + `test`.`t1`.`a` 21DROP TABLE t1; 22# 23# MDEV-23320 Hex hybrid constants 0xHHHH work badly in rounding functions 24# 25BEGIN NOT ATOMIC 26DECLARE arg TEXT DEFAULT ''; 27DECLARE query TEXT DEFAULT 28'CREATE TABLE t1 AS SELECT ' 29 '0xFFFFFFFFFFFFFFFF+0 AS c1,' 30 'FLOOR(0xFFFFFFFFFFFFFFFF) AS c2,' 31 'CEILING(0xFFFFFFFFFFFFFFFF) AS c3,' 32 'ROUND(0xFFFFFFFFFFFFFFFF) AS c4,' 33 'TRUNCATE(0xFFFFFFFFFFFFFFFF,0) AS c5'; 34FOR i IN 1..9 35DO 36SET arg= CONCAT('0x',REPEAT('FF',i)); 37SELECT i, arg; 38EXECUTE IMMEDIATE REPLACE(query,'0xFFFFFFFFFFFFFFFF', arg); 39SHOW CREATE TABLE t1; 40SELECT * FROM t1; 41DROP TABLE t1; 42END FOR; 43END; 44$$ 45i 1 46arg 0xFF 47Table t1 48Create Table CREATE TABLE `t1` ( 49 `c1` int(4) unsigned NOT NULL, 50 `c2` int(3) unsigned NOT NULL, 51 `c3` int(3) unsigned NOT NULL, 52 `c4` int(3) unsigned NOT NULL, 53 `c5` int(3) unsigned NOT NULL 54) ENGINE=MyISAM DEFAULT CHARSET=latin1 55c1 255 56c2 255 57c3 255 58c4 255 59c5 255 60i 2 61arg 0xFFFF 62Table t1 63Create Table CREATE TABLE `t1` ( 64 `c1` int(6) unsigned NOT NULL, 65 `c2` int(5) unsigned NOT NULL, 66 `c3` int(5) unsigned NOT NULL, 67 `c4` int(5) unsigned NOT NULL, 68 `c5` int(5) unsigned NOT NULL 69) ENGINE=MyISAM DEFAULT CHARSET=latin1 70c1 65535 71c2 65535 72c3 65535 73c4 65535 74c5 65535 75i 3 76arg 0xFFFFFF 77Table t1 78Create Table CREATE TABLE `t1` ( 79 `c1` int(9) unsigned NOT NULL, 80 `c2` int(8) unsigned NOT NULL, 81 `c3` int(8) unsigned NOT NULL, 82 `c4` int(8) unsigned NOT NULL, 83 `c5` int(8) unsigned NOT NULL 84) ENGINE=MyISAM DEFAULT CHARSET=latin1 85c1 16777215 86c2 16777215 87c3 16777215 88c4 16777215 89c5 16777215 90i 4 91arg 0xFFFFFFFF 92Table t1 93Create Table CREATE TABLE `t1` ( 94 `c1` bigint(11) unsigned NOT NULL, 95 `c2` bigint(10) unsigned NOT NULL, 96 `c3` bigint(10) unsigned NOT NULL, 97 `c4` bigint(10) unsigned NOT NULL, 98 `c5` bigint(10) unsigned NOT NULL 99) ENGINE=MyISAM DEFAULT CHARSET=latin1 100c1 4294967295 101c2 4294967295 102c3 4294967295 103c4 4294967295 104c5 4294967295 105i 5 106arg 0xFFFFFFFFFF 107Table t1 108Create Table CREATE TABLE `t1` ( 109 `c1` bigint(14) unsigned NOT NULL, 110 `c2` bigint(13) unsigned NOT NULL, 111 `c3` bigint(13) unsigned NOT NULL, 112 `c4` bigint(13) unsigned NOT NULL, 113 `c5` bigint(13) unsigned NOT NULL 114) ENGINE=MyISAM DEFAULT CHARSET=latin1 115c1 1099511627775 116c2 1099511627775 117c3 1099511627775 118c4 1099511627775 119c5 1099511627775 120i 6 121arg 0xFFFFFFFFFFFF 122Table t1 123Create Table CREATE TABLE `t1` ( 124 `c1` bigint(16) unsigned NOT NULL, 125 `c2` bigint(15) unsigned NOT NULL, 126 `c3` bigint(15) unsigned NOT NULL, 127 `c4` bigint(15) unsigned NOT NULL, 128 `c5` bigint(15) unsigned NOT NULL 129) ENGINE=MyISAM DEFAULT CHARSET=latin1 130c1 281474976710655 131c2 281474976710655 132c3 281474976710655 133c4 281474976710655 134c5 281474976710655 135i 7 136arg 0xFFFFFFFFFFFFFF 137Table t1 138Create Table CREATE TABLE `t1` ( 139 `c1` bigint(18) unsigned NOT NULL, 140 `c2` bigint(17) unsigned NOT NULL, 141 `c3` bigint(17) unsigned NOT NULL, 142 `c4` bigint(17) unsigned NOT NULL, 143 `c5` bigint(17) unsigned NOT NULL 144) ENGINE=MyISAM DEFAULT CHARSET=latin1 145c1 72057594037927935 146c2 72057594037927935 147c3 72057594037927935 148c4 72057594037927935 149c5 72057594037927935 150i 8 151arg 0xFFFFFFFFFFFFFFFF 152Table t1 153Create Table CREATE TABLE `t1` ( 154 `c1` bigint(21) unsigned NOT NULL, 155 `c2` bigint(20) unsigned NOT NULL, 156 `c3` bigint(20) unsigned NOT NULL, 157 `c4` bigint(20) unsigned NOT NULL, 158 `c5` bigint(20) unsigned NOT NULL 159) ENGINE=MyISAM DEFAULT CHARSET=latin1 160c1 18446744073709551615 161c2 18446744073709551615 162c3 18446744073709551615 163c4 18446744073709551615 164c5 18446744073709551615 165i 9 166arg 0xFFFFFFFFFFFFFFFFFF 167Table t1 168Create Table CREATE TABLE `t1` ( 169 `c1` bigint(21) unsigned NOT NULL, 170 `c2` bigint(20) unsigned NOT NULL, 171 `c3` bigint(20) unsigned NOT NULL, 172 `c4` bigint(20) unsigned NOT NULL, 173 `c5` bigint(20) unsigned NOT NULL 174) ENGINE=MyISAM DEFAULT CHARSET=latin1 175c1 18446744073709551615 176c2 18446744073709551615 177c3 18446744073709551615 178c4 18446744073709551615 179c5 18446744073709551615 180# 181# MDEV-23368 ROUND(18446744073709551615,-11) returns a wrong result 182# 183SELECT ROUND(0xFFFFFFFFFFFFFFFF,-10), ROUND(0xFFFFFFFFFFFFFFFF,-11); 184ROUND(0xFFFFFFFFFFFFFFFF,-10) ROUND(0xFFFFFFFFFFFFFFFF,-11) 18518446744070000000000 18446744100000000000 186CREATE TABLE t1 AS SELECT ROUND(0xFFFFFFFFFFFFFFFF,-10), ROUND(0xFFFFFFFFFFFFFFFF,-11); 187SHOW CREATE TABLE t1; 188Table Create Table 189t1 CREATE TABLE `t1` ( 190 `ROUND(0xFFFFFFFFFFFFFFFF,-10)` decimal(21,0) unsigned NOT NULL, 191 `ROUND(0xFFFFFFFFFFFFFFFF,-11)` decimal(21,0) unsigned NOT NULL 192) ENGINE=MyISAM DEFAULT CHARSET=latin1 193SELECT * FROM t1; 194ROUND(0xFFFFFFFFFFFFFFFF,-10) ROUND(0xFFFFFFFFFFFFFFFF,-11) 19518446744070000000000 18446744100000000000 196DROP TABLE t1; 197# 198# MDEV-23366 ROUND(18446744073709551615,rand()*0) returns a wrong result 199# 200SELECT 201ROUND(0xFFFFFFFFFFFFFFFF,NULL) AS c1, 202ROUND(0xFFFFFFFFFFFFFFFF,rand()*0) AS c2, 203ROUND(0xFFFFFFFFFFFFFFFF,-1) AS c3, 204ROUND(0xFFFFFFFFFFFFFFFF,-19) AS c4, 205ROUND(0xFFFFFFFFFFFFFFFF,rand()*0-19) AS c5; 206c1 NULL 207c2 18446744073709551615 208c3 18446744073709551620 209c4 20000000000000000000 210c5 20000000000000000000 211CREATE OR REPLACE TABLE t1 AS 212SELECT 213ROUND(0xFFFFFFFFFFFFFFFF,NULL) AS c1, 214ROUND(0xFFFFFFFFFFFFFFFF,rand()*0) AS c2, 215ROUND(0xFFFFFFFFFFFFFFFF,-1) AS c3, 216ROUND(0xFFFFFFFFFFFFFFFF,-19) AS c4, 217ROUND(0xFFFFFFFFFFFFFFFF,rand()*0-19) AS c5; 218SELECT * FROM t1; 219c1 NULL 220c2 18446744073709551615 221c3 18446744073709551620 222c4 20000000000000000000 223c5 20000000000000000000 224SHOW CREATE TABLE t1; 225Table t1 226Create Table CREATE TABLE `t1` ( 227 `c1` bigint(20) unsigned DEFAULT NULL, 228 `c2` decimal(21,0) unsigned NOT NULL, 229 `c3` decimal(21,0) unsigned NOT NULL, 230 `c4` decimal(21,0) unsigned NOT NULL, 231 `c5` decimal(21,0) unsigned NOT NULL 232) ENGINE=MyISAM DEFAULT CHARSET=latin1 233DROP TABLE t1; 234# 235# End of 10.4 tests 236# 237