mirror of
https://github.com/MariaDB/server.git
synced 2025-10-31 10:56:12 +01:00
e5574d8b94
snprintf below uses %4.2f. values above 1023.99 MiB should be shown as 1.00 GiB not as 1024.00 MiB
432 lines
10 KiB
Text
432 lines
10 KiB
Text
# MDEV-19629: Implement MySQL 8.0 native functions: format_bytes(), format_pico_time() and ps_thread_id()
|
|
#
|
|
# Tests for the native function format_pico_time()
|
|
#
|
|
|
|
SELECT format_pico_time(NULL);
|
|
format_pico_time(NULL)
|
|
NULL
|
|
|
|
SELECT format_pico_time(0);
|
|
format_pico_time(0)
|
|
0 ps
|
|
|
|
SELECT format_pico_time(1);
|
|
format_pico_time(1)
|
|
1 ps
|
|
|
|
SELECT format_pico_time(999);
|
|
format_pico_time(999)
|
|
999 ps
|
|
|
|
SELECT format_pico_time(1000);
|
|
format_pico_time(1000)
|
|
1.00 ns
|
|
|
|
SELECT format_pico_time(1001);
|
|
format_pico_time(1001)
|
|
1.00 ns
|
|
|
|
SELECT format_pico_time(999999);
|
|
format_pico_time(999999)
|
|
1000.00 ns
|
|
|
|
SELECT format_pico_time(1000000);
|
|
format_pico_time(1000000)
|
|
1.00 us
|
|
|
|
SELECT format_pico_time(1000001);
|
|
format_pico_time(1000001)
|
|
1.00 us
|
|
|
|
SELECT format_pico_time(1010000);
|
|
format_pico_time(1010000)
|
|
1.01 us
|
|
|
|
SELECT format_pico_time(987654321);
|
|
format_pico_time(987654321)
|
|
987.65 us
|
|
|
|
SELECT format_pico_time(1000000000);
|
|
format_pico_time(1000000000)
|
|
1.00 ms
|
|
|
|
SELECT format_pico_time(999876000000);
|
|
format_pico_time(999876000000)
|
|
999.88 ms
|
|
|
|
SELECT format_pico_time(999999999999);
|
|
format_pico_time(999999999999)
|
|
1000.00 ms
|
|
|
|
SELECT format_pico_time(1000000000000);
|
|
format_pico_time(1000000000000)
|
|
1.00 s
|
|
|
|
SELECT format_pico_time(59000000000000);
|
|
format_pico_time(59000000000000)
|
|
59.00 s
|
|
|
|
SELECT format_pico_time(60000000000000);
|
|
format_pico_time(60000000000000)
|
|
1.00 min
|
|
|
|
SELECT format_pico_time(3549000000000000);
|
|
format_pico_time(3549000000000000)
|
|
59.15 min
|
|
|
|
SELECT format_pico_time(3599000000000000);
|
|
format_pico_time(3599000000000000)
|
|
59.98 min
|
|
|
|
SELECT format_pico_time(3600000000000000);
|
|
format_pico_time(3600000000000000)
|
|
1.00 h
|
|
|
|
SELECT format_pico_time(power(2, 63));
|
|
format_pico_time(power(2, 63))
|
|
106.75 d
|
|
|
|
SELECT format_pico_time((power(2, 63) - 1) * 2 + 1);
|
|
format_pico_time((power(2, 63) - 1) * 2 + 1)
|
|
213.50 d
|
|
|
|
SELECT format_pico_time(36000000.495523);
|
|
format_pico_time(36000000.495523)
|
|
36.00 us
|
|
|
|
SELECT format_pico_time(1000 * pow(10,12) * 86400);
|
|
format_pico_time(1000 * pow(10,12) * 86400)
|
|
1000.00 d
|
|
|
|
SELECT format_pico_time(86400000000000000000);
|
|
format_pico_time(86400000000000000000)
|
|
1000.00 d
|
|
|
|
SELECT format_pico_time(86400000000000000000+5000);
|
|
format_pico_time(86400000000000000000+5000)
|
|
1000.00 d
|
|
|
|
## Negative values are ok
|
|
SELECT format_pico_time(1010000 * -1);
|
|
format_pico_time(1010000 * -1)
|
|
-1.01 us
|
|
|
|
## Force exponent
|
|
SELECT format_pico_time(8650000000000000000099);
|
|
format_pico_time(8650000000000000000099)
|
|
1.00e+05 d
|
|
|
|
SELECT format_pico_time(-8650000000000000000099);
|
|
format_pico_time(-8650000000000000000099)
|
|
-1.00e+05 d
|
|
|
|
SELECT format_pico_time(8640000000000000000099 * 2);
|
|
format_pico_time(8640000000000000000099 * 2)
|
|
2.00e+05 d
|
|
|
|
|
|
## Text input
|
|
SELECT format_pico_time("foo");
|
|
format_pico_time("foo")
|
|
0 ps
|
|
Warnings:
|
|
Warning 1292 Truncated incorrect DOUBLE value: 'foo'
|
|
|
|
SELECT format_pico_time("");
|
|
format_pico_time("")
|
|
0 ps
|
|
Warnings:
|
|
Warning 1292 Truncated incorrect DOUBLE value: ''
|
|
|
|
SELECT format_pico_time("118059162071741143500099");
|
|
format_pico_time("118059162071741143500099")
|
|
1.37e+06 d
|
|
|
|
SELECT format_pico_time("-118059162071741143500099");
|
|
format_pico_time("-118059162071741143500099")
|
|
-1.37e+06 d
|
|
|
|
## Recognizes up to first non-numeric
|
|
SELECT format_pico_time("40000 * 2000");
|
|
format_pico_time("40000 * 2000")
|
|
40.00 ns
|
|
Warnings:
|
|
Warning 1292 Truncated incorrect DOUBLE value: '40000 * 2000'
|
|
|
|
SELECT format_pico_time("40000 foo 2000");
|
|
format_pico_time("40000 foo 2000")
|
|
40.00 ns
|
|
Warnings:
|
|
Warning 1292 Truncated incorrect DOUBLE value: '40000 foo 2000'
|
|
## Aggregate functions
|
|
USE test;
|
|
|
|
CREATE TABLE timer_waits (id VARCHAR(10), wait BIGINT UNSIGNED DEFAULT NULL) DEFAULT CHARSET = utf8mb4;
|
|
|
|
INSERT INTO timer_waits VALUES ('1 sec', 1000000000000);
|
|
INSERT INTO timer_waits VALUES ('1 min', 60000000000000);
|
|
INSERT INTO timer_waits VALUES ('1 hour', 3600000000000000);
|
|
INSERT INTO timer_waits VALUES ('1 day', 86400000000000000);
|
|
INSERT INTO timer_waits VALUES ('100 days', 8640000000000000000);
|
|
|
|
SELECT id, format_pico_time(wait), wait FROM timer_waits;
|
|
id format_pico_time(wait) wait
|
|
1 sec 1.00 s 1000000000000
|
|
1 min 1.00 min 60000000000000
|
|
1 hour 1.00 h 3600000000000000
|
|
1 day 1.00 d 86400000000000000
|
|
100 days 100.00 d 8640000000000000000
|
|
|
|
SELECT sum(wait), format_pico_time(sum(wait)) FROM timer_waits;
|
|
sum(wait) format_pico_time(sum(wait))
|
|
8730061000000000000 101.04 d
|
|
|
|
SELECT avg(wait), format_pico_time(avg(wait)) FROM timer_waits;
|
|
avg(wait) format_pico_time(avg(wait))
|
|
1746012200000000000.0000 20.21 d
|
|
|
|
SELECT min(wait), format_pico_time(min(wait)) FROM timer_waits;
|
|
min(wait) format_pico_time(min(wait))
|
|
1000000000000 1.00 s
|
|
|
|
SELECT max(wait), format_pico_time(max(wait)) FROM timer_waits;
|
|
max(wait) format_pico_time(max(wait))
|
|
8640000000000000000 100.00 d
|
|
|
|
DROP TABLE timer_waits;
|
|
## Using Scientific Notation
|
|
SELECT format_pico_time(3e9);
|
|
format_pico_time(3e9)
|
|
3.00 ms
|
|
|
|
SELECT format_pico_time(4e6);
|
|
format_pico_time(4e6)
|
|
4.00 us
|
|
|
|
SELECT format_pico_time(5e3);
|
|
format_pico_time(5e3)
|
|
5.00 ns
|
|
|
|
SELECT format_pico_time(6e2);
|
|
format_pico_time(6e2)
|
|
600 ps
|
|
#
|
|
# Start of 11.8 tests
|
|
#
|
|
#
|
|
# MDEV-19629 Tests for the native function format_bytes()
|
|
#
|
|
|
|
SELECT format_bytes(NULL);
|
|
format_bytes(NULL)
|
|
NULL
|
|
|
|
SELECT format_bytes(0);
|
|
format_bytes(0)
|
|
0 bytes
|
|
|
|
SELECT format_bytes(1);
|
|
format_bytes(1)
|
|
1 bytes
|
|
|
|
SELECT format_bytes(1023);
|
|
format_bytes(1023)
|
|
1023 bytes
|
|
|
|
SELECT format_bytes(1024);
|
|
format_bytes(1024)
|
|
1.00 KiB
|
|
|
|
SELECT format_bytes(1025);
|
|
format_bytes(1025)
|
|
1.00 KiB
|
|
|
|
SELECT format_bytes(1024 * 1024 - 200);
|
|
format_bytes(1024 * 1024 - 200)
|
|
1023.80 KiB
|
|
|
|
SELECT format_bytes(1024 * 1024 - 1);
|
|
format_bytes(1024 * 1024 - 1)
|
|
1.00 MiB
|
|
|
|
SELECT format_bytes(1024 * 1024);
|
|
format_bytes(1024 * 1024)
|
|
1.00 MiB
|
|
|
|
SELECT format_bytes(1024 * 1024 + 1);
|
|
format_bytes(1024 * 1024 + 1)
|
|
1.00 MiB
|
|
|
|
SELECT format_bytes(1024 * 1024 + 200);
|
|
format_bytes(1024 * 1024 + 200)
|
|
1.00 MiB
|
|
|
|
SELECT format_bytes(1024 * 1024 * 1024 - 1);
|
|
format_bytes(1024 * 1024 * 1024 - 1)
|
|
1.00 GiB
|
|
|
|
SELECT format_bytes(1024 * 1024 * 1024);
|
|
format_bytes(1024 * 1024 * 1024)
|
|
1.00 GiB
|
|
|
|
SELECT format_bytes(1024 * 1024 * 1024 + 1);
|
|
format_bytes(1024 * 1024 * 1024 + 1)
|
|
1.00 GiB
|
|
|
|
SELECT format_bytes(1024 * 1024 * 1024 * 1024 - 1);
|
|
format_bytes(1024 * 1024 * 1024 * 1024 - 1)
|
|
1.00 TiB
|
|
|
|
SELECT format_bytes(1024 * 1024 * 1024 * 1024);
|
|
format_bytes(1024 * 1024 * 1024 * 1024)
|
|
1.00 TiB
|
|
|
|
SELECT format_bytes(1024 * 1024 * 1024 * 1024 + 1);
|
|
format_bytes(1024 * 1024 * 1024 * 1024 + 1)
|
|
1.00 TiB
|
|
|
|
SELECT format_bytes(1024 * 1024 * 1024 * 1024 * 1024 - 1);
|
|
format_bytes(1024 * 1024 * 1024 * 1024 * 1024 - 1)
|
|
1.00 PiB
|
|
|
|
SELECT format_bytes(1024 * 1024 * 1024 * 1024 * 1024);
|
|
format_bytes(1024 * 1024 * 1024 * 1024 * 1024)
|
|
1.00 PiB
|
|
|
|
SELECT format_bytes(1024 * 1024 * 1024 * 1024 * 1024 + 1);
|
|
format_bytes(1024 * 1024 * 1024 * 1024 * 1024 + 1)
|
|
1.00 PiB
|
|
|
|
SELECT format_bytes(power(2, 63));
|
|
format_bytes(power(2, 63))
|
|
8.00 EiB
|
|
|
|
## 1024^6 Eib
|
|
SELECT format_bytes(1152921504606846976-1);
|
|
format_bytes(1152921504606846976-1)
|
|
1.00 EiB
|
|
|
|
SELECT format_bytes(1180591620717411434000);
|
|
format_bytes(1180591620717411434000)
|
|
1024.00 EiB
|
|
|
|
## Negative values are ok
|
|
SELECT format_bytes(1024 * 1024 * -1);
|
|
format_bytes(1024 * 1024 * -1)
|
|
-1.00 MiB
|
|
|
|
## Force exponent format
|
|
SELECT format_bytes(118059162071741143500099);
|
|
format_bytes(118059162071741143500099)
|
|
1.02e+05 EiB
|
|
|
|
SELECT format_bytes(-118059162071741143500099);
|
|
format_bytes(-118059162071741143500099)
|
|
-1.02e+05 EiB
|
|
|
|
SELECT format_bytes(pow(2,400));
|
|
format_bytes(pow(2,400))
|
|
2.24e+102 EiB
|
|
|
|
## Valid hybrid number
|
|
SELECT format_bytes((pow(2, 63) - 1) * 2 + 1);
|
|
format_bytes((pow(2, 63) - 1) * 2 + 1)
|
|
16.00 EiB
|
|
|
|
## Text input
|
|
SELECT format_bytes("foo");
|
|
format_bytes("foo")
|
|
0 bytes
|
|
Warnings:
|
|
Warning 1292 Truncated incorrect DOUBLE value: 'foo'
|
|
|
|
SELECT format_bytes("");
|
|
format_bytes("")
|
|
0 bytes
|
|
Warnings:
|
|
Warning 1292 Truncated incorrect DOUBLE value: ''
|
|
|
|
SELECT format_bytes("118059162071741143500099");
|
|
format_bytes("118059162071741143500099")
|
|
1.02e+05 EiB
|
|
|
|
SELECT format_bytes("-118059162071741143500099");
|
|
format_bytes("-118059162071741143500099")
|
|
-1.02e+05 EiB
|
|
|
|
## Recognizes up to first non-numeric
|
|
SELECT format_bytes("40000 * 2000");
|
|
format_bytes("40000 * 2000")
|
|
39.06 KiB
|
|
Warnings:
|
|
Warning 1292 Truncated incorrect DOUBLE value: '40000 * 2000'
|
|
|
|
SELECT format_bytes("40000 foo 2000");
|
|
format_bytes("40000 foo 2000")
|
|
39.06 KiB
|
|
Warnings:
|
|
Warning 1292 Truncated incorrect DOUBLE value: '40000 foo 2000'
|
|
## Aggregate functions
|
|
USE test;
|
|
|
|
CREATE TABLE memory_counts (id VARCHAR(10), bytes BIGINT UNSIGNED DEFAULT NULL) DEFAULT CHARSET = utf8mb4;
|
|
|
|
INSERT INTO memory_counts VALUES ('Bytes', 512);
|
|
INSERT INTO memory_counts VALUES ('10 KiB', 10 * 1024);
|
|
INSERT INTO memory_counts VALUES ('20 MiB', 20 * pow(1024,2));
|
|
|
|
SELECT sum(bytes), format_bytes(sum(bytes)) FROM memory_counts;
|
|
sum(bytes) format_bytes(sum(bytes))
|
|
20982272 20.01 MiB
|
|
|
|
INSERT INTO memory_counts VALUES ('30 GiB', 30 * pow(1024,3));
|
|
|
|
SELECT sum(bytes), format_bytes(sum(bytes)) FROM memory_counts;
|
|
sum(bytes) format_bytes(sum(bytes))
|
|
32233236992 30.02 GiB
|
|
|
|
INSERT INTO memory_counts VALUES ('40 TiB', 40 * pow(1024,4));
|
|
|
|
SELECT sum(bytes), format_bytes(sum(bytes)) FROM memory_counts;
|
|
sum(bytes) format_bytes(sum(bytes))
|
|
44012698348032 40.03 TiB
|
|
|
|
INSERT INTO memory_counts VALUES ('50 PiB', 50 * pow(1024,5));
|
|
|
|
SELECT sum(bytes), format_bytes(sum(bytes)) FROM memory_counts;
|
|
sum(bytes) format_bytes(sum(bytes))
|
|
56339008040479232 50.04 PiB
|
|
|
|
INSERT INTO memory_counts VALUES ('1 EiB', pow(1024,6));
|
|
|
|
SELECT id, format_bytes(bytes), bytes FROM memory_counts;
|
|
id format_bytes(bytes) bytes
|
|
Bytes 512 bytes 512
|
|
10 KiB 10.00 KiB 10240
|
|
20 MiB 20.00 MiB 20971520
|
|
30 GiB 30.00 GiB 32212254720
|
|
40 TiB 40.00 TiB 43980465111040
|
|
50 PiB 50.00 PiB 56294995342131200
|
|
1 EiB 1.00 EiB 1152921504606846976
|
|
|
|
SELECT sum(bytes), format_bytes(sum(bytes)) FROM memory_counts;
|
|
sum(bytes) format_bytes(sum(bytes))
|
|
1209260512647326208 1.05 EiB
|
|
|
|
SELECT avg(bytes), format_bytes(avg(bytes)) FROM memory_counts;
|
|
avg(bytes) format_bytes(avg(bytes))
|
|
172751501806760886.8571 153.43 PiB
|
|
|
|
SELECT max(bytes), format_bytes(max(bytes)) FROM memory_counts;
|
|
max(bytes) format_bytes(max(bytes))
|
|
1152921504606846976 1.00 EiB
|
|
|
|
SELECT min(bytes), format_bytes(min(bytes)) FROM memory_counts;
|
|
min(bytes) format_bytes(min(bytes))
|
|
512 512 bytes
|
|
|
|
DROP TABLE memory_counts;
|
|
#
|
|
# End of 11.8 tests
|
|
#
|