diff --git a/functions/format_percentage.sql b/functions/format_percentage.sql new file mode 100644 index 0000000..e1d0ed7 --- /dev/null +++ b/functions/format_percentage.sql @@ -0,0 +1,86 @@ +-- Copyright (c) 2014, 2015, Oracle and/or its affiliates. All rights reserved. +-- +-- This program is free software; you can redistribute it and/or modify +-- it under the terms of the GNU General Public License as published by +-- the Free Software Foundation; version 2 of the License. +-- +-- This program is distributed in the hope that it will be useful, +-- but WITHOUT ANY WARRANTY; without even the implied warranty of +-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +-- GNU General Public License for more details. +-- +-- You should have received a copy of the GNU General Public License +-- along with this program; if not, write to the Free Software +-- Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA + +DROP FUNCTION IF EXISTS format_percentage; + +DELIMITER $$ + +CREATE DEFINER='root'@'localhost' FUNCTION format_percentage ( + -- We feed in and return TEXT here, as aggregates of + -- picoseconds can return numbers larger than BIGINT UNSIGNED + percentage TEXT + ) + RETURNS TEXT CHARSET UTF8 + COMMENT ' + Description + ----------- + + Takes a raw value between 0 and 1, and converts it to a human + readable percentage formatted to two decimal places. + + Parameters + ----------- + + percentage (TEXT): + The raw percentage to format. + + Returns + ----------- + + TEXT + + Example + ----------- + + mysql> select format_percentage(0.99921); + +----------------------------+ + | format_percentage(0.99921) | + +----------------------------+ + | 99.92 % | + +----------------------------+ + 1 row in set (0.01 sec) + + mysql> select format_percentage(0.012); + +--------------------------+ + | format_percentage(0.012) | + +--------------------------+ + | 1.20 % | + +--------------------------+ + 1 row in set (0.00 sec) + + mysql> select format_percentage(0); + +----------------------+ + | format_percentage(0) | + +----------------------+ + | 0.00 % | + +----------------------+ + 1 row in set (0.00 sec) + + mysql> select format_percentage(1); + +----------------------+ + | format_percentage(1) | + +----------------------+ + | 100.00 % | + +----------------------+ + 1 row in set (0.00 sec) + ' + SQL SECURITY INVOKER + DETERMINISTIC + NO SQL + BEGIN + RETURN CONCAT(ROUND(100 * (percentage), 2), ' %'); + END$$ + +DELIMITER ; diff --git a/mysql-test/suite/sysschema/r/all_sys_objects_exist.result b/mysql-test/suite/sysschema/r/all_sys_objects_exist.result index 370ce51..5129c2c 100644 --- a/mysql-test/suite/sysschema/r/all_sys_objects_exist.result +++ b/mysql-test/suite/sysschema/r/all_sys_objects_exist.result @@ -108,6 +108,7 @@ extract_schema_from_file_name FUNCTION extract_table_from_file_name FUNCTION format_bytes FUNCTION format_path FUNCTION +format_percentage FUNCTION format_statement FUNCTION format_time FUNCTION list_add FUNCTION diff --git a/mysql-test/suite/sysschema/r/fn_format_percentage.result b/mysql-test/suite/sysschema/r/fn_format_percentage.result new file mode 100644 index 0000000..51409dc --- /dev/null +++ b/mysql-test/suite/sysschema/r/fn_format_percentage.result @@ -0,0 +1,21 @@ +SELECT sys.format_percentage(NULL); +sys.format_percentage(NULL) +NULL +select sys.format_percentage(0.99921); +sys.format_percentage(0.99921) +99.92 % +select sys.format_percentage(0.012); +sys.format_percentage(0.012) +1.20 % +select sys.format_percentage(0); +sys.format_percentage(0) +0.00 % +select sys.format_percentage(1); +sys.format_percentage(1) +100.00 % +select sys.format_percentage(1.12); +sys.format_percentage(1.12) +112.00 % +select sys.format_percentage(-0.2); +sys.format_percentage(-0.2) +-20.00 % diff --git a/mysql-test/suite/sysschema/t/fn_format_percentage.test b/mysql-test/suite/sysschema/t/fn_format_percentage.test new file mode 100644 index 0000000..36455d4 --- /dev/null +++ b/mysql-test/suite/sysschema/t/fn_format_percentage.test @@ -0,0 +1,19 @@ +-- source include/not_embedded.inc +# Tests for sys schema +# Verify the sys.format_percentage() function perfoms as expected + +# Passing NULL/nothing should return NULL +SELECT sys.format_percentage(NULL); + +# Format valid "normal" percentages +select sys.format_percentage(0.99921); +select sys.format_percentage(0.012); +select sys.format_percentage(0); +select sys.format_percentage(1); + +# Relative percentages can be out of 0-1 range. +# For example: +# 112% faster or -20% regression + +select sys.format_percentage(1.12); +select sys.format_percentage(-0.2); diff --git a/views/p_s/processlist_57.sql b/views/p_s/processlist_57.sql index 4ac5ac4..b73313f 100644 --- a/views/p_s/processlist_57.sql +++ b/views/p_s/processlist_57.sql @@ -32,7 +32,7 @@ -- time: 18 -- current_statement: alter table t1 add column g int -- statement_latency: 18.45 s --- progress: 98.84 +-- progress: 98.84 % -- lock_latency: 265.43 ms -- rows_examined: 0 -- rows_sent: 0 @@ -101,7 +101,7 @@ SELECT pps.thread_id AS thd_id, sys.format_time(esc.timer_wait), NULL) AS statement_latency, IF(esc.end_event_id IS NULL, - ROUND(100 * (estc.work_completed / estc.work_estimated), 2), + format_percentage(estc.work_completed / estc.work_estimated), NULL) AS progress, sys.format_time(esc.lock_time) AS lock_latency, esc.rows_examined AS rows_examined,