MDEV-19629: format_pico_time implementation

8 files changed, 548 insertions, 2 deletions

diff --git a/libmysqld/CMakeLists.txt b/libmysqld/CMakeLists.txt
index 5d5cc35e1be..8727aa54992 100644
--- a/libmysqld/CMakeLists.txt
+++ b/libmysqld/CMakeLists.txt
@@ -64,7 +64,7 @@ SET(SQL_EMBEDDED_SOURCES emb_qcache.cc libmysqld.c lib_sql.cc
            ../sql/item.cc ../sql/item_create.cc ../sql/item_func.cc 
            ../sql/item_geofunc.cc ../sql/item_row.cc ../sql/item_strfunc.cc 
            ../sql/item_subselect.cc ../sql/item_sum.cc ../sql/item_timefunc.cc 
-           ../sql/item_xmlfunc.cc ../sql/item_jsonfunc.cc
+           ../sql/item_xmlfunc.cc ../sql/item_jsonfunc.cc ../sql/item_pfsfunc.cc
            ../sql/key.cc ../sql/lock.cc ../sql/log.cc 
            ../sql/log_event.cc ../sql/log_event_server.cc 
            ../sql/mf_iocache.cc ../sql/my_decimal.cc 
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);
diff --git a/sql/CMakeLists.txt b/sql/CMakeLists.txt
index 4938f8da02b..be0e2d214e0 100644
--- a/sql/CMakeLists.txt
+++ b/sql/CMakeLists.txt
@@ -104,7 +104,7 @@ SET (SQL_SOURCE
                handler.cc
                hostname.cc init.cc item.cc item_buff.cc item_cmpfunc.cc
                item_create.cc item_func.cc item_geofunc.cc item_row.cc
-               item_strfunc.cc item_subselect.cc item_sum.cc item_timefunc.cc
+               item_strfunc.cc item_subselect.cc item_sum.cc item_timefunc.cc item_pfsfunc.cc
                key.cc log.cc lock.cc
                log_event.cc log_event_server.cc
                rpl_record.cc rpl_reporting.cc
diff --git a/sql/item.h b/sql/item.h
index 5956b810d51..515a9abb310 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -6371,6 +6371,7 @@ public:
 #include "item_jsonfunc.h"
 #include "item_create.h"
 #include "item_vers.h"
+#include "item_pfsfunc.h"
 #endif
 
 /**
diff --git a/sql/item_create.cc b/sql/item_create.cc
index 0e2bcc7b1be..dc8359346a0 100644
--- a/sql/item_create.cc
+++ b/sql/item_create.cc
@@ -761,6 +761,19 @@ protected:
 };
 
 
+class Create_func_format_pico_time : public Create_func_arg1
+{
+public:
+    virtual Item *create_1_arg(THD *thd, Item *arg1);
+
+    static Create_func_format_pico_time s_singleton;
+
+protected:
+    Create_func_format_pico_time() = default;
+    virtual ~Create_func_format_pico_time() = default;
+};
+
+
 class Create_func_format : public Create_native_func
 {
 public:
@@ -3570,6 +3583,15 @@ Create_func_floor::create_1_arg(THD *thd, Item *arg1)
 }
 
 
+Create_func_format_pico_time Create_func_format_pico_time::s_singleton;
+
+Item*
+Create_func_format_pico_time::create_1_arg(THD *thd, Item *arg1)
+{
+  return new (thd->mem_root) Item_func_pfs_format_pico_time(thd, arg1);
+}
+
+
 Create_func_format Create_func_format::s_singleton;
 
 Item*
@@ -5762,6 +5784,7 @@ Native_func_registry func_array[] =
   { { STRING_WITH_LEN("FIELD") }, BUILDER(Create_func_field)},
   { { STRING_WITH_LEN("FIND_IN_SET") }, BUILDER(Create_func_find_in_set)},
   { { STRING_WITH_LEN("FLOOR") }, BUILDER(Create_func_floor)},
+  { { STRING_WITH_LEN("FORMAT_PICO_TIME") }, BUILDER(Create_func_format_pico_time)},
   { { STRING_WITH_LEN("FORMAT") }, BUILDER(Create_func_format)},
   { { STRING_WITH_LEN("FOUND_ROWS") }, BUILDER(Create_func_found_rows)},
   { { STRING_WITH_LEN("FROM_BASE64") }, BUILDER(Create_func_from_base64)},
diff --git a/sql/item_pfsfunc.cc b/sql/item_pfsfunc.cc
new file mode 100644
index 00000000000..8ac7df50866
--- /dev/null
+++ b/sql/item_pfsfunc.cc
@@ -0,0 +1,135 @@
+/* Copyright (c) 2000, 2023, Oracle and/or its affiliates.
+
+   This program is free software; you can redistribute it and/or modify
+   it under the terms of the GNU General Public License, version 2.0,
+   as published by the Free Software Foundation.
+
+   This program is also distributed with certain software (including
+   but not limited to OpenSSL) that is licensed under separate terms,
+   as designated in a particular file or component or in included license
+   documentation.  The authors of MySQL hereby grant you an additional
+   permission to link the program and your derivative works with the
+   separately licensed software that they have included with MySQL.
+
+   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, version 2.0, 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 */
+
+/**
+  @file
+
+  @brief
+  This file defines all performance schema native functions
+    FORMAT_PICO_TIME()
+*/
+
+#ifdef USE_PRAGMA_IMPLEMENTATION
+#pragma implementation				// gcc: Class implementation
+#endif
+
+#include "mariadb.h"                          // HAVE_*
+
+/*
+  It is necessary to include set_var.h instead of item.h because there
+  are dependencies on include order for set_var.h and item.h. This
+  will be resolved later.
+*/
+#include "set_var.h"
+
+/** format_pico_time() */
+
+bool Item_func_pfs_format_pico_time::fix_length_and_dec(THD *)
+{
+  set_maybe_null();
+  /* Format is 'AAAA.BB UUU' = 11 characters or 'AAA ps' = 6 characters. */
+  m_value.set_charset(&my_charset_utf8mb3_general_ci);
+  return false;
+}
+
+
+String *Item_func_pfs_format_pico_time::val_str(String *str __attribute__ ((__unused__)))
+{
+  /* Evaluate the argument */
+  double time_val= args[0]->val_real();
+
+  /* If argument is null, return null. */
+  null_value= args[0]->null_value;
+  if (null_value)
+    return 0;
+
+  constexpr uint64_t nano{1000};
+  constexpr uint64_t micro{1000 * nano};
+  constexpr uint64_t milli{1000 * micro};
+  constexpr uint64_t sec{1000 * milli};
+  constexpr uint64_t min{60 * sec};
+  constexpr uint64_t hour{60 * min};
+  constexpr uint64_t day{24 * hour};
+
+  /* Declaring 'volatile' as workaround for 32-bit optimization bug. */
+  volatile double time_abs= abs(time_val);
+
+  uint64_t divisor;
+  int len;
+  const char *unit;
+
+  /* SI-approved time units. */
+  if (time_abs >= day)
+  {
+    divisor= day;
+    unit= "d";
+  }
+  else if (time_abs >= hour)
+  {
+    divisor= hour;
+    unit= "h";
+  }
+  else if (time_abs >= min)
+  {
+    divisor= min;
+    unit= "min";
+  }
+  else if (time_abs >= sec)
+  {
+    divisor= sec;
+    unit= "s";
+  }
+  else if (time_abs >= milli)
+  {
+    divisor= milli;
+    unit= "ms";
+  }
+  else if (time_abs >= micro)
+  {
+    divisor= micro;
+    unit= "us";
+  }
+  else if (time_abs >= nano)
+  {
+    divisor= nano;
+    unit= "ns";
+  }
+  else
+  {
+    divisor= 1;
+    unit= "ps";
+  }
+
+  if (divisor == 1)
+    len= snprintf(m_value_buffer, sizeof(m_value_buffer), "%3d %s", (int)time_val, unit);
+  else
+  {
+    double value= time_val / divisor;
+    if (abs(value) >= 100000.0)
+      len= snprintf(m_value_buffer, sizeof(m_value_buffer), "%4.2e %s", value, unit);
+    else
+      len= snprintf(m_value_buffer, sizeof(m_value_buffer), "%4.2f %s", value, unit);
+  }
+
+  m_value.set(m_value_buffer, len, &my_charset_utf8mb3_general_ci);
+  return &m_value;
+}
diff --git a/sql/item_pfsfunc.h b/sql/item_pfsfunc.h
new file mode 100644
index 00000000000..ee3c111e15c
--- /dev/null
+++ b/sql/item_pfsfunc.h
@@ -0,0 +1,54 @@
+#ifndef ITEM_PFS_FUNC_INCLUDED
+#define ITEM_PFS_FUNC_INCLUDED
+
+/* Copyright (c) 2000, 2023, Oracle and/or its affiliates.
+
+   This program is free software; you can redistribute it and/or modify
+   it under the terms of the GNU General Public License, version 2.0,
+   as published by the Free Software Foundation.
+
+   This program is also distributed with certain software (including
+   but not limited to OpenSSL) that is licensed under separate terms,
+   as designated in a particular file or component or in included license
+   documentation.  The authors of MySQL hereby grant you an additional
+   permission to link the program and your derivative works with the
+   separately licensed software that they have included with MySQL.
+
+   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, version 2.0, 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 */
+
+#ifdef USE_PRAGMA_INTERFACE
+#pragma interface			/* gcc class implementation */
+#endif
+
+#include "item_func.h"      // Item_str_func, Item_int_func
+
+/** format_pico_time() */
+
+class Item_func_pfs_format_pico_time : public Item_str_func {
+  String m_value;
+  char m_value_buffer[12];
+
+public:
+  Item_func_pfs_format_pico_time(THD *thd, Item *a)
+  : Item_str_func(thd, a){};
+  String *val_str(String *str __attribute__ ((__unused__))) override;
+  LEX_CSTRING func_name_cstring() const override
+  {
+    static LEX_CSTRING name= {STRING_WITH_LEN("format_pico_time")};
+    return name;
+  }
+  bool fix_length_and_dec(THD *thd) override;
+  Item *get_copy(THD *thd) override
+  {
+    return get_item_copy<Item_func_pfs_format_pico_time>(thd, this);
+  }
+};
+
+#endif