diff options
author | Ahmed Ibrahim <ahmed.ibr.hashim@gmail.com> | 2023-03-19 02:57:39 +0200 |
---|---|---|
committer | Anel <an3l@users.noreply.github.com> | 2023-03-27 16:34:29 +0200 |
commit | d9808f79de992964ed802d27984c9031d72e7b9a (patch) | |
tree | 5916b35f1ffc1ec6c330e1ba0710b17a5b568fd6 /mysql-test/suite | |
parent | 31487f4b2b6a3c038a39bb68ce96e9c400ca25d6 (diff) | |
download | mariadb-git-d9808f79de992964ed802d27984c9031d72e7b9a.tar.gz |
MDEV-19629: format_pico_time implementation
Diffstat (limited to 'mysql-test/suite')
-rw-r--r-- | mysql-test/suite/perfschema/r/pfs_functions.result | 213 | ||||
-rw-r--r-- | mysql-test/suite/perfschema/t/pfs_functions.test | 120 |
2 files changed, 333 insertions, 0 deletions
diff --git a/mysql-test/suite/perfschema/r/pfs_functions.result b/mysql-test/suite/perfschema/r/pfs_functions.result new file mode 100644 index 00000000000..322f64c1ce7 --- /dev/null +++ b/mysql-test/suite/perfschema/r/pfs_functions.result @@ -0,0 +1,213 @@ +# MDEV-19629: Implement MySQL 8.0 native functions: format_bytes(), format_pico_time() and ps_thread_id() +# +# Tests for the Performance Schema 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 diff --git a/mysql-test/suite/perfschema/t/pfs_functions.test b/mysql-test/suite/perfschema/t/pfs_functions.test new file mode 100644 index 00000000000..a175d3edeea --- /dev/null +++ b/mysql-test/suite/perfschema/t/pfs_functions.test @@ -0,0 +1,120 @@ +# Testcases for PFS functions are backported from MySQL + +--echo # MDEV-19629: Implement MySQL 8.0 native functions: format_bytes(), format_pico_time() and ps_thread_id() +--echo # +--echo # Tests for the Performance Schema native function format_pico_time() +--echo # +--echo + +SELECT format_pico_time(NULL); +--echo +SELECT format_pico_time(0); +--echo +SELECT format_pico_time(1); +--echo +SELECT format_pico_time(999); +--echo +SELECT format_pico_time(1000); +--echo +SELECT format_pico_time(1001); +--echo +SELECT format_pico_time(999999); +--echo +SELECT format_pico_time(1000000); +--echo +SELECT format_pico_time(1000001); +--echo +SELECT format_pico_time(1010000); +--echo +SELECT format_pico_time(987654321); +--echo +SELECT format_pico_time(1000000000); +--echo +SELECT format_pico_time(999876000000); +--echo +SELECT format_pico_time(999999999999); +--echo +SELECT format_pico_time(1000000000000); +--echo +SELECT format_pico_time(59000000000000); +--echo +SELECT format_pico_time(60000000000000); +--echo +SELECT format_pico_time(3549000000000000); +--echo +SELECT format_pico_time(3599000000000000); +--echo +SELECT format_pico_time(3600000000000000); +--echo +SELECT format_pico_time(power(2, 63)); +--echo +SELECT format_pico_time((power(2, 63) - 1) * 2 + 1); +--echo +SELECT format_pico_time(36000000.495523); +--echo +SELECT format_pico_time(1000 * pow(10,12) * 86400); +--echo +SELECT format_pico_time(86400000000000000000); +--echo +SELECT format_pico_time(86400000000000000000+5000); + +--echo +--echo ## Negative values are ok +SELECT format_pico_time(1010000 * -1); + +--echo +--echo ## Force exponent +SELECT format_pico_time(8650000000000000000099); +--echo +SELECT format_pico_time(-8650000000000000000099); +--echo +SELECT format_pico_time(8640000000000000000099 * 2); +--echo + +--echo +--echo ## Text input +SELECT format_pico_time("foo"); +--echo +SELECT format_pico_time(""); +--echo +SELECT format_pico_time("118059162071741143500099"); +--echo +SELECT format_pico_time("-118059162071741143500099"); +--echo +--echo ## Recognizes up to first non-numeric +SELECT format_pico_time("40000 * 2000"); +--echo +SELECT format_pico_time("40000 foo 2000"); + +--echo ## Aggregate functions +USE test; +--echo +CREATE TABLE timer_waits (id VARCHAR(10), wait BIGINT UNSIGNED DEFAULT NULL) DEFAULT CHARSET = utf8mb4; +--echo +# Max BIGINT unsigned is 18 446 744 073 709 551 615 +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); +--echo +SELECT id, format_pico_time(wait), wait FROM timer_waits; +--echo +SELECT sum(wait), format_pico_time(sum(wait)) FROM timer_waits; +--echo +SELECT avg(wait), format_pico_time(avg(wait)) FROM timer_waits; +--echo +SELECT min(wait), format_pico_time(min(wait)) FROM timer_waits; +--echo +SELECT max(wait), format_pico_time(max(wait)) FROM timer_waits; +--echo +DROP TABLE timer_waits; + +--echo ## Using Scientific Notation +SELECT format_pico_time(3e9); +--echo +SELECT format_pico_time(4e6); +--echo +SELECT format_pico_time(5e3); +--echo +SELECT format_pico_time(6e2); |