| 1 |
--echo # |
| 2 |
--echo # Tests for the Performance Schema native function format_pico_time() |
| 3 |
--echo # |
| 4 |
--echo |
| 5 |
|
| 6 |
SELECT format_pico_time(NULL); |
| 7 |
--echo |
| 8 |
SELECT format_pico_time(0); |
| 9 |
--echo |
| 10 |
SELECT format_pico_time(1); |
| 11 |
--echo |
| 12 |
SELECT format_pico_time(999); |
| 13 |
--echo |
| 14 |
SELECT format_pico_time(1000); |
| 15 |
--echo |
| 16 |
SELECT format_pico_time(1001); |
| 17 |
--echo |
| 18 |
SELECT format_pico_time(999999); |
| 19 |
--echo |
| 20 |
SELECT format_pico_time(1000000); |
| 21 |
--echo |
| 22 |
SELECT format_pico_time(1000001); |
| 23 |
--echo |
| 24 |
SELECT format_pico_time(1010000); |
| 25 |
--echo |
| 26 |
SELECT format_pico_time(987654321); |
| 27 |
--echo |
| 28 |
SELECT format_pico_time(1000000000); |
| 29 |
--echo |
| 30 |
SELECT format_pico_time(999876000000); |
| 31 |
--echo |
| 32 |
SELECT format_pico_time(999999999999); |
| 33 |
--echo |
| 34 |
SELECT format_pico_time(1000000000000); |
| 35 |
--echo |
| 36 |
SELECT format_pico_time(59000000000000); |
| 37 |
--echo |
| 38 |
SELECT format_pico_time(60000000000000); |
| 39 |
--echo |
| 40 |
SELECT format_pico_time(3549000000000000); |
| 41 |
--echo |
| 42 |
SELECT format_pico_time(3599000000000000); |
| 43 |
--echo |
| 44 |
SELECT format_pico_time(3600000000000000); |
| 45 |
--echo |
| 46 |
SELECT format_pico_time(power(2, 63)); |
| 47 |
--echo |
| 48 |
SELECT format_pico_time((power(2, 63) - 1) * 2 + 1); |
| 49 |
--echo |
| 50 |
SELECT format_pico_time(36000000.495523); |
| 51 |
--echo |
| 52 |
SELECT format_pico_time(1000 * pow(10,12) * 86400); |
| 53 |
--echo |
| 54 |
SELECT format_pico_time(86400000000000000000); |
| 55 |
--echo |
| 56 |
SELECT format_pico_time(86400000000000000000+5000); |
| 57 |
|
| 58 |
--echo |
| 59 |
--echo ## Negative values are ok |
| 60 |
SELECT format_pico_time(1010000 * -1); |
| 61 |
|
| 62 |
--echo |
| 63 |
--echo ## Force exponent |
| 64 |
SELECT format_pico_time(8650000000000000000099); |
| 65 |
--echo |
| 66 |
SELECT format_pico_time(-8650000000000000000099); |
| 67 |
--echo |
| 68 |
SELECT format_pico_time(8640000000000000000099 * 2); |
| 69 |
--echo |
| 70 |
|
| 71 |
--echo |
| 72 |
--echo ## Text input |
| 73 |
SELECT format_pico_time("foo"); |
| 74 |
--echo |
| 75 |
SELECT format_pico_time(""); |
| 76 |
--echo |
| 77 |
SELECT format_pico_time("118059162071741143500099"); |
| 78 |
--echo |
| 79 |
SELECT format_pico_time("-118059162071741143500099"); |
| 80 |
--echo |
| 81 |
--echo ## Recognizes up to first non-numeric |
| 82 |
SELECT format_pico_time("40000 * 2000"); |
| 83 |
--echo |
| 84 |
SELECT format_pico_time("40000 foo 2000"); |
| 85 |
|
| 86 |
--echo ## Aggregate functions |
| 87 |
USE test; |
| 88 |
--echo |
| 89 |
CREATE TABLE timer_waits (id VARCHAR(10), wait BIGINT UNSIGNED DEFAULT NULL) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4; |
| 90 |
--echo |
| 91 |
# Max BIGINT unsigned is 18 446 744 073 709 551 615 |
| 92 |
INSERT INTO timer_waits VALUES ('1 sec', 1000000000000); |
| 93 |
INSERT INTO timer_waits VALUES ('1 min', 60000000000000); |
| 94 |
INSERT INTO timer_waits VALUES ('1 hour', 3600000000000000); |
| 95 |
INSERT INTO timer_waits VALUES ('1 day', 86400000000000000); |
| 96 |
INSERT INTO timer_waits VALUES ('100 days', 8640000000000000000); |
| 97 |
--echo |
| 98 |
SELECT id, format_pico_time(wait), wait FROM timer_waits; |
| 99 |
--echo |
| 100 |
SELECT sum(wait), format_pico_time(sum(wait)) FROM timer_waits; |
| 101 |
--echo |
| 102 |
SELECT avg(wait), format_pico_time(avg(wait)) FROM timer_waits; |
| 103 |
--echo |
| 104 |
SELECT min(wait), format_pico_time(min(wait)) FROM timer_waits; |
| 105 |
--echo |
| 106 |
SELECT max(wait), format_pico_time(max(wait)) FROM timer_waits; |
| 107 |
--echo |
| 108 |
DROP TABLE timer_waits; |
| 109 |
|
| 110 |
--echo |
| 111 |
--echo ## Handling of NULL (bug#30525561) |
| 112 |
|
| 113 |
SELECT mytime, |
| 114 |
FORMAT_PICO_TIME(mytime) AS PS_TIME, |
| 115 |
sys.format_time(mytime) AS SYS_TIME |
| 116 |
FROM (SELECT 1234 AS mytime |
| 117 |
UNION ALL |
| 118 |
SELECT 1234567890 |
| 119 |
UNION ALL |
| 120 |
SELECT NULL |
| 121 |
UNION ALL |
| 122 |
SELECT 1234 |
| 123 |
UNION ALL |
| 124 |
SELECT 1234567890 |
| 125 |
) t; |
| 126 |
|