1--echo # 2--echo # Start of 10.4 tests 3--echo # 4 5--echo # 6--echo # MDEV-16426 Optimizer erroneously treats equal constants of different formats as same 7--echo # 8 9# It's important for CHARSET('a') and CHARSET(0x61) to have different lengths in this test. 10# 'latin1' and 'binary' have same lengths, so using 'utf8'. 11SET NAMES utf8; 12CREATE TABLE t1 (a DECIMAL(10,3)); 13INSERT INTO t1 VALUES (10.0),(10.1); 14SELECT CHARSET('a'),CHARSET(0x61),LENGTH(CHARSET('a'))+a,LENGTH(CHARSET(0x61))+a FROM t1; 15SELECT * FROM t1 WHERE LENGTH(CHARSET('a'))+a<=>LENGTH(CHARSET(0x61))+a; 16EXPLAIN EXTENDED SELECT * FROM t1 WHERE LENGTH(CHARSET('a'))+a<=>LENGTH(CHARSET(0x61))+a; 17DROP TABLE t1; 18 19--echo # 20--echo # MDEV-23320 Hex hybrid constants 0xHHHH work badly in rounding functions 21--echo # 22 23--vertical_results 24DELIMITER $$; 25BEGIN NOT ATOMIC 26 DECLARE arg TEXT DEFAULT ''; 27 DECLARE 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'; 34 FOR i IN 1..9 35 DO 36 SET arg= CONCAT('0x',REPEAT('FF',i)); 37 SELECT i, arg; 38 EXECUTE IMMEDIATE REPLACE(query,'0xFFFFFFFFFFFFFFFF', arg); 39 SHOW CREATE TABLE t1; 40 SELECT * FROM t1; 41 DROP TABLE t1; 42 END FOR; 43END; 44$$ 45DELIMITER ;$$ 46--horizontal_results 47 48 49--echo # 50--echo # MDEV-23368 ROUND(18446744073709551615,-11) returns a wrong result 51--echo # 52 53SELECT ROUND(0xFFFFFFFFFFFFFFFF,-10), ROUND(0xFFFFFFFFFFFFFFFF,-11); 54CREATE TABLE t1 AS SELECT ROUND(0xFFFFFFFFFFFFFFFF,-10), ROUND(0xFFFFFFFFFFFFFFFF,-11); 55SHOW CREATE TABLE t1; 56SELECT * FROM t1; 57DROP TABLE t1; 58 59 60--echo # 61--echo # MDEV-23366 ROUND(18446744073709551615,rand()*0) returns a wrong result 62--echo # 63 64--vertical_results 65SELECT 66 ROUND(0xFFFFFFFFFFFFFFFF,NULL) AS c1, 67 ROUND(0xFFFFFFFFFFFFFFFF,rand()*0) AS c2, 68 ROUND(0xFFFFFFFFFFFFFFFF,-1) AS c3, 69 ROUND(0xFFFFFFFFFFFFFFFF,-19) AS c4, 70 ROUND(0xFFFFFFFFFFFFFFFF,rand()*0-19) AS c5; 71 72CREATE OR REPLACE TABLE t1 AS 73SELECT 74 ROUND(0xFFFFFFFFFFFFFFFF,NULL) AS c1, 75 ROUND(0xFFFFFFFFFFFFFFFF,rand()*0) AS c2, 76 ROUND(0xFFFFFFFFFFFFFFFF,-1) AS c3, 77 ROUND(0xFFFFFFFFFFFFFFFF,-19) AS c4, 78 ROUND(0xFFFFFFFFFFFFFFFF,rand()*0-19) AS c5; 79 80SELECT * FROM t1; 81SHOW CREATE TABLE t1; 82DROP TABLE t1; 83--horizontal_results 84 85--echo # 86--echo # End of 10.4 tests 87--echo # 88