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