| 1 |
--echo # |
| 2 |
--echo # Tests for the Performance Schema native function format_bytes() |
| 3 |
--echo # |
| 4 |
--echo |
| 5 |
|
| 6 |
SELECT format_bytes(NULL); |
| 7 |
--echo |
| 8 |
SELECT format_bytes(0); |
| 9 |
--echo |
| 10 |
SELECT format_bytes(1); |
| 11 |
--echo |
| 12 |
SELECT format_bytes(1023); |
| 13 |
--echo |
| 14 |
SELECT format_bytes(1024); |
| 15 |
--echo |
| 16 |
SELECT format_bytes(1025); |
| 17 |
--echo |
| 18 |
SELECT format_bytes(1024 * 1024 - 200); |
| 19 |
--echo |
| 20 |
SELECT format_bytes(1024 * 1024 - 1); |
| 21 |
--echo |
| 22 |
SELECT format_bytes(1024 * 1024); |
| 23 |
--echo |
| 24 |
SELECT format_bytes(1024 * 1024 + 1); |
| 25 |
--echo |
| 26 |
SELECT format_bytes(1024 * 1024 + 200); |
| 27 |
--echo |
| 28 |
SELECT format_bytes(1024 * 1024 * 1024 - 1); |
| 29 |
--echo |
| 30 |
SELECT format_bytes(1024 * 1024 * 1024); |
| 31 |
--echo |
| 32 |
SELECT format_bytes(1024 * 1024 * 1024 + 1); |
| 33 |
--echo |
| 34 |
SELECT format_bytes(1024 * 1024 * 1024 * 1024 - 1); |
| 35 |
--echo |
| 36 |
SELECT format_bytes(1024 * 1024 * 1024 * 1024); |
| 37 |
--echo |
| 38 |
SELECT format_bytes(1024 * 1024 * 1024 * 1024 + 1); |
| 39 |
--echo |
| 40 |
SELECT format_bytes(1024 * 1024 * 1024 * 1024 * 1024 - 1); |
| 41 |
--echo |
| 42 |
SELECT format_bytes(1024 * 1024 * 1024 * 1024 * 1024); |
| 43 |
--echo |
| 44 |
SELECT format_bytes(1024 * 1024 * 1024 * 1024 * 1024 + 1); |
| 45 |
--echo |
| 46 |
SELECT format_bytes(power(2, 63)); |
| 47 |
|
| 48 |
--echo |
| 49 |
--echo ## 1024^6 Eib |
| 50 |
SELECT format_bytes(1152921504606846976-1); |
| 51 |
--echo |
| 52 |
SELECT format_bytes(x'1000000000000000'-1); |
| 53 |
|
| 54 |
--echo |
| 55 |
SELECT format_bytes(1180591620717411434000); |
| 56 |
|
| 57 |
--echo |
| 58 |
--echo ## Negative values are ok |
| 59 |
SELECT format_bytes(1024 * 1024 * -1); |
| 60 |
|
| 61 |
--echo |
| 62 |
--echo ## Force exponent format |
| 63 |
SELECT format_bytes(118059162071741143500099); |
| 64 |
--echo |
| 65 |
SELECT format_bytes(-118059162071741143500099); |
| 66 |
--echo |
| 67 |
SELECT format_bytes(pow(2,400)); |
| 68 |
|
| 69 |
--echo |
| 70 |
--echo ## Valid hybrid number |
| 71 |
SELECT format_bytes((pow(2, 63) - 1) * 2 + 1); |
| 72 |
|
| 73 |
--echo |
| 74 |
--echo ## Text input |
| 75 |
SELECT format_bytes("foo"); |
| 76 |
--echo |
| 77 |
SELECT format_bytes(""); |
| 78 |
--echo |
| 79 |
SELECT format_bytes("118059162071741143500099"); |
| 80 |
--echo |
| 81 |
SELECT format_bytes("-118059162071741143500099"); |
| 82 |
--echo |
| 83 |
--echo ## Recognizes up to first non-numeric |
| 84 |
SELECT format_bytes("40000 * 2000"); |
| 85 |
--echo |
| 86 |
SELECT format_bytes("40000 foo 2000"); |
| 87 |
|
| 88 |
--echo ## Aggregate functions |
| 89 |
USE test; |
| 90 |
--echo |
| 91 |
CREATE TABLE memory_counts (id VARCHAR(10), bytes BIGINT UNSIGNED DEFAULT NULL) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4; |
| 92 |
--echo |
| 93 |
# Max BIGINT unsigned is 18 446 744 073 709 551 615 |
| 94 |
INSERT INTO memory_counts VALUES ('Bytes', 512); |
| 95 |
INSERT INTO memory_counts VALUES ('10 KiB', 10 * 1024); |
| 96 |
INSERT INTO memory_counts VALUES ('20 MiB', 20 * pow(1024,2)); |
| 97 |
--echo |
| 98 |
SELECT sum(bytes), format_bytes(sum(bytes)) FROM memory_counts; |
| 99 |
|
| 100 |
--echo |
| 101 |
INSERT INTO memory_counts VALUES ('30 GiB', 30 * pow(1024,3)); |
| 102 |
--echo |
| 103 |
SELECT sum(bytes), format_bytes(sum(bytes)) FROM memory_counts; |
| 104 |
|
| 105 |
--echo |
| 106 |
INSERT INTO memory_counts VALUES ('40 TiB', 40 * pow(1024,4)); |
| 107 |
--echo |
| 108 |
SELECT sum(bytes), format_bytes(sum(bytes)) FROM memory_counts; |
| 109 |
|
| 110 |
--echo |
| 111 |
INSERT INTO memory_counts VALUES ('50 PiB', 50 * pow(1024,5)); |
| 112 |
--echo |
| 113 |
SELECT sum(bytes), format_bytes(sum(bytes)) FROM memory_counts; |
| 114 |
|
| 115 |
--echo |
| 116 |
INSERT INTO memory_counts VALUES ('1 EiB', pow(1024,6)); |
| 117 |
--echo |
| 118 |
SELECT id, format_bytes(bytes), bytes FROM memory_counts; |
| 119 |
--echo |
| 120 |
SELECT sum(bytes), format_bytes(sum(bytes)) FROM memory_counts; |
| 121 |
--echo |
| 122 |
SELECT avg(bytes), format_bytes(avg(bytes)) FROM memory_counts; |
| 123 |
--echo |
| 124 |
SELECT max(bytes), format_bytes(max(bytes)) FROM memory_counts; |
| 125 |
--echo |
| 126 |
SELECT min(bytes), format_bytes(min(bytes)) FROM memory_counts; |
| 127 |
--echo |
| 128 |
DROP TABLE memory_counts; |
| 129 |
|
| 130 |
--echo |
| 131 |
--echo ## Handling of NULL (bug#30525561) |
| 132 |
|
| 133 |
SELECT mybytes, |
| 134 |
FORMAT_BYTES(mybytes) AS PS_BYTES, |
| 135 |
sys.format_bytes(mybytes) AS SYS_BYTES |
| 136 |
FROM (SELECT 1234 AS mybytes |
| 137 |
UNION ALL |
| 138 |
SELECT 1234567890 |
| 139 |
UNION ALL |
| 140 |
SELECT NULL |
| 141 |
UNION ALL |
| 142 |
SELECT 1234 |
| 143 |
UNION ALL |
| 144 |
SELECT 1234567890 |
| 145 |
) t; |
| 146 |
|