# # Start of 10.4 tests # # # MDEV-16426 Optimizer erroneously treats equal constants of different formats as same # SET NAMES utf8; CREATE TABLE t1 (a DECIMAL(10,3)); INSERT INTO t1 VALUES (10.0),(10.1); SELECT CHARSET('a'),CHARSET(0x61),LENGTH(CHARSET('a'))+a,LENGTH(CHARSET(0x61))+a FROM t1; CHARSET('a') CHARSET(0x61) LENGTH(CHARSET('a'))+a LENGTH(CHARSET(0x61))+a utf8 binary 14.000 16.000 utf8 binary 14.100 16.100 SELECT * FROM t1 WHERE LENGTH(CHARSET('a'))+a<=>LENGTH(CHARSET(0x61))+a; a EXPLAIN EXTENDED SELECT * FROM t1 WHERE LENGTH(CHARSET('a'))+a<=>LENGTH(CHARSET(0x61))+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` from `test`.`t1` where (octet_length(charset('a'))) + `test`.`t1`.`a` <=> (octet_length(charset(0x61))) + `test`.`t1`.`a` DROP TABLE t1; # # MDEV-23320 Hex hybrid constants 0xHHHH work badly in rounding functions # BEGIN NOT ATOMIC DECLARE arg TEXT DEFAULT ''; DECLARE query TEXT DEFAULT 'CREATE TABLE t1 AS SELECT ' '0xFFFFFFFFFFFFFFFF+0 AS c1,' 'FLOOR(0xFFFFFFFFFFFFFFFF) AS c2,' 'CEILING(0xFFFFFFFFFFFFFFFF) AS c3,' 'ROUND(0xFFFFFFFFFFFFFFFF) AS c4,' 'TRUNCATE(0xFFFFFFFFFFFFFFFF,0) AS c5'; FOR i IN 1..9 DO SET arg= CONCAT('0x',REPEAT('FF',i)); SELECT i, arg; EXECUTE IMMEDIATE REPLACE(query,'0xFFFFFFFFFFFFFFFF', arg); SHOW CREATE TABLE t1; SELECT * FROM t1; DROP TABLE t1; END FOR; END; $$ i 1 arg 0xFF Table t1 Create Table CREATE TABLE `t1` ( `c1` int(4) unsigned NOT NULL, `c2` int(3) unsigned NOT NULL, `c3` int(3) unsigned NOT NULL, `c4` int(3) unsigned NOT NULL, `c5` int(3) unsigned NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 c1 255 c2 255 c3 255 c4 255 c5 255 i 2 arg 0xFFFF Table t1 Create Table CREATE TABLE `t1` ( `c1` int(6) unsigned NOT NULL, `c2` int(5) unsigned NOT NULL, `c3` int(5) unsigned NOT NULL, `c4` int(5) unsigned NOT NULL, `c5` int(5) unsigned NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 c1 65535 c2 65535 c3 65535 c4 65535 c5 65535 i 3 arg 0xFFFFFF Table t1 Create Table CREATE TABLE `t1` ( `c1` int(9) unsigned NOT NULL, `c2` int(8) unsigned NOT NULL, `c3` int(8) unsigned NOT NULL, `c4` int(8) unsigned NOT NULL, `c5` int(8) unsigned NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 c1 16777215 c2 16777215 c3 16777215 c4 16777215 c5 16777215 i 4 arg 0xFFFFFFFF Table t1 Create Table CREATE TABLE `t1` ( `c1` bigint(11) unsigned NOT NULL, `c2` bigint(10) unsigned NOT NULL, `c3` bigint(10) unsigned NOT NULL, `c4` bigint(10) unsigned NOT NULL, `c5` bigint(10) unsigned NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 c1 4294967295 c2 4294967295 c3 4294967295 c4 4294967295 c5 4294967295 i 5 arg 0xFFFFFFFFFF Table t1 Create Table CREATE TABLE `t1` ( `c1` bigint(14) unsigned NOT NULL, `c2` bigint(13) unsigned NOT NULL, `c3` bigint(13) unsigned NOT NULL, `c4` bigint(13) unsigned NOT NULL, `c5` bigint(13) unsigned NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 c1 1099511627775 c2 1099511627775 c3 1099511627775 c4 1099511627775 c5 1099511627775 i 6 arg 0xFFFFFFFFFFFF Table t1 Create Table CREATE TABLE `t1` ( `c1` bigint(16) unsigned NOT NULL, `c2` bigint(15) unsigned NOT NULL, `c3` bigint(15) unsigned NOT NULL, `c4` bigint(15) unsigned NOT NULL, `c5` bigint(15) unsigned NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 c1 281474976710655 c2 281474976710655 c3 281474976710655 c4 281474976710655 c5 281474976710655 i 7 arg 0xFFFFFFFFFFFFFF Table t1 Create Table CREATE TABLE `t1` ( `c1` bigint(18) unsigned NOT NULL, `c2` bigint(17) unsigned NOT NULL, `c3` bigint(17) unsigned NOT NULL, `c4` bigint(17) unsigned NOT NULL, `c5` bigint(17) unsigned NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 c1 72057594037927935 c2 72057594037927935 c3 72057594037927935 c4 72057594037927935 c5 72057594037927935 i 8 arg 0xFFFFFFFFFFFFFFFF Table t1 Create Table CREATE TABLE `t1` ( `c1` bigint(21) unsigned NOT NULL, `c2` bigint(20) unsigned NOT NULL, `c3` bigint(20) unsigned NOT NULL, `c4` bigint(20) unsigned NOT NULL, `c5` bigint(20) unsigned NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 c1 18446744073709551615 c2 18446744073709551615 c3 18446744073709551615 c4 18446744073709551615 c5 18446744073709551615 i 9 arg 0xFFFFFFFFFFFFFFFFFF Table t1 Create Table CREATE TABLE `t1` ( `c1` bigint(21) unsigned NOT NULL, `c2` bigint(20) unsigned NOT NULL, `c3` bigint(20) unsigned NOT NULL, `c4` bigint(20) unsigned NOT NULL, `c5` bigint(20) unsigned NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 c1 18446744073709551615 c2 18446744073709551615 c3 18446744073709551615 c4 18446744073709551615 c5 18446744073709551615 # # MDEV-23368 ROUND(18446744073709551615,-11) returns a wrong result # SELECT ROUND(0xFFFFFFFFFFFFFFFF,-10), ROUND(0xFFFFFFFFFFFFFFFF,-11); ROUND(0xFFFFFFFFFFFFFFFF,-10) ROUND(0xFFFFFFFFFFFFFFFF,-11) 18446744070000000000 18446744100000000000 CREATE TABLE t1 AS SELECT ROUND(0xFFFFFFFFFFFFFFFF,-10), ROUND(0xFFFFFFFFFFFFFFFF,-11); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `ROUND(0xFFFFFFFFFFFFFFFF,-10)` decimal(21,0) unsigned NOT NULL, `ROUND(0xFFFFFFFFFFFFFFFF,-11)` decimal(21,0) unsigned NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 SELECT * FROM t1; ROUND(0xFFFFFFFFFFFFFFFF,-10) ROUND(0xFFFFFFFFFFFFFFFF,-11) 18446744070000000000 18446744100000000000 DROP TABLE t1; # # MDEV-23366 ROUND(18446744073709551615,rand()*0) returns a wrong result # SELECT ROUND(0xFFFFFFFFFFFFFFFF,NULL) AS c1, ROUND(0xFFFFFFFFFFFFFFFF,rand()*0) AS c2, ROUND(0xFFFFFFFFFFFFFFFF,-1) AS c3, ROUND(0xFFFFFFFFFFFFFFFF,-19) AS c4, ROUND(0xFFFFFFFFFFFFFFFF,rand()*0-19) AS c5; c1 NULL c2 18446744073709551615 c3 18446744073709551620 c4 20000000000000000000 c5 20000000000000000000 CREATE OR REPLACE TABLE t1 AS SELECT ROUND(0xFFFFFFFFFFFFFFFF,NULL) AS c1, ROUND(0xFFFFFFFFFFFFFFFF,rand()*0) AS c2, ROUND(0xFFFFFFFFFFFFFFFF,-1) AS c3, ROUND(0xFFFFFFFFFFFFFFFF,-19) AS c4, ROUND(0xFFFFFFFFFFFFFFFF,rand()*0-19) AS c5; SELECT * FROM t1; c1 NULL c2 18446744073709551615 c3 18446744073709551620 c4 20000000000000000000 c5 20000000000000000000 SHOW CREATE TABLE t1; Table t1 Create Table CREATE TABLE `t1` ( `c1` bigint(20) unsigned DEFAULT NULL, `c2` decimal(21,0) unsigned NOT NULL, `c3` decimal(21,0) unsigned NOT NULL, `c4` decimal(21,0) unsigned NOT NULL, `c5` decimal(21,0) unsigned NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DROP TABLE t1; # # End of 10.4 tests #