Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-19629

Implement MySQL 8.0 native functions: format_bytes(), format_pico_time() and ps_thread_id()

Details

    Description

      While this functions can be implemented as user SQL functions, they have become native on MySQL 8.0.16. They are simple and independent enough that backport them should be easy.
      Implementing them on MariaDB would allow:

      • Better compatibility with sys/performance_schema
      • Nice functions to have natively in any case for formatting reasons or even as a virtual column
      • Better MySQL compatibility, and avoiding user pain when migrating from MySQL from 8.0.16+
      • Better performance by being C rather than user functions/SQL

      https://mysql.wisborg.dk/2019/05/28/performance-schema-functions/

      MySQL implementation

      MariaDB implementation

      • MariaDB doesn't have any native functions
      • MariaDB has sys schema function format_bytes, as stated in sysschema.format_bytes documentation.
      • This change is introduced from 10.6.0 with commits and author 

        commit aa2ff62082c4eb09b1d505af0d0327278c9f7d08
        		 
        Author: Vladislav Vaintroub <wlad@mariadb.com>
        		 
        Date:   Wed Mar 3 10:30:29 2021 +0100
        		 
         
        		 
            MDEV-9077 Use sys schema in bootstrapping, incl. mtr

        and second commit

        commit 4bac804c90c6f8d101ef32aef39813c3d13283ff
        		 
        Author: Vladislav Vaintroub <wlad@mariadb.com>
        		 
        Date:   Wed Mar 17 17:57:11 2021 +0100
        		 
         
        		 
            MDEV-9077 add sys_schema from https://github.com/mysql/mysql-sys

      • Tasks:
        The task can be divided into 3 steps by backporting the patch from MySQL if possible in single commit and if not in separate commits:
      • Create native function format_pico_time - task done as contribution with PR 2558 on 11.0, commit d9808f79
      • Create native function ps_thread_id
      • Create native function ps_current_thread_id
      • Create native function for format_bytes

      Attachments

        Issue Links

          causes

          Bug - A problem which impairs or prevents the functions of the product. MDEV-36020 ASAN errors in Binary_string::replace upon using format_pico_time in view

          • Major - Major loss of function.
          • Open
          includes

          New Feature - A new feature of the product, which has yet to be developed. MDEV-31736 Create native function for format_bytes

          • Major - Major loss of function.
          • Closed
          is part of

          Task - A task that needs to be done. MDEV-28906 MySQL 8.0 desired compatibility

          • Major - Major loss of function.
          • Open
          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-33703 Negative numbers and differnce of behaviour between sys.format_time() and FORMAT_PICO_TIME()

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Open

          Activity

            Ascending order - Click to sort in descending order
            jcrespo Jaime Crespo created issue -
            elenst Elena Stepanova made changes -
            Field Original Value New Value
            Affects Version/s 10.4 [ 22408 ]
            Issue Type Bug [ 1 ] Task [ 3 ]
            elenst Elena Stepanova made changes -
            Labels upsteam upstream-fixed
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 97108 ] MariaDB v4 [ 131095 ]
            greenman Ian Gilfillan made changes -
            greenman Ian Gilfillan made changes -
            Summary Implement MySQL 8.0 native functions: format_bytes(), format_time() and ps_thread_id() Implement MySQL 8.0 native functions: format_bytes(), format_pico_time() and ps_thread_id()
            greenman Ian Gilfillan made changes -
            Labels upstream-fixed beginner-friendly upstream-fixed
            greenman Ian Gilfillan made changes -
            Labels beginner-friendly upstream-fixed beginner-friendly compat80 upstream-fixed
            anel Anel Husakovic made changes -
            Description While this functions can be implemented as user SQL functions, they have become native on MySQL 8.0.16. They are simple and independent enough that backport them should be easy.
            Implementing them on MariaDB would allow:

            * Better compatibility with sys/performance_schema
            * Nice functions to have natively in any case for formatting reasons or even as a virtual column
            * Better MySQL compatibility, and avoiding user pain when migrating from MySQL from 8.0.16+
            * Better performance by being C rather than user functions/SQL

            https://mysql.wisborg.dk/2019/05/28/performance-schema-functions/             		While this functions can be implemented as user SQL functions, they have become native on MySQL 8.0.16. They are simple and independent enough that backport them should be easy.
            Implementing them on MariaDB would allow:

            * Better compatibility with sys/performance_schema
            * Nice functions to have natively in any case for formatting reasons or even as a virtual column
            * Better MySQL compatibility, and avoiding user pain when migrating from MySQL from 8.0.16+
            * Better performance by being C rather than user functions/SQL

            https://mysql.wisborg.dk/2019/05/28/performance-schema-functions/

            h4. MySQL implementation
            - MySQL has native implementation of function:
              1. [format_bytes|https://dev.mysql.com/doc/refman/8.0/en/performance-schema-functions.html#function_format-bytes],
              2. [format_pico_time|https://dev.mysql.com/doc/refman/8.0/en/performance-schema-functions.html#function_format-pico-time]
              3. [ps_thread_id|https://dev.mysql.com/doc/refman/8.0/en/performance-schema-functions.html#function_ps-thread-id]
              4. [ps_current_thread_id|https://dev.mysql.com/doc/refman/8.0/en/performance-schema-functions.html#function_ps-current-thread-id]

            - As well as {{sys}} schema defined functions \[that are marked as deprecated and will be removed\]:
              1. [format_bytes|https://dev.mysql.com/doc/refman/8.0/en/sys-format-bytes.html]
              2. [ps_thread_id|https://dev.mysql.com/doc/refman/8.0/en/sys-ps-thread-id.html]
              3. [format_time|https://dev.mysql.com/doc/refman/8.0/en/sys-format-time.html]

            - In MySQL all native functions are defined in file [item_pfs_func.h|https://github.com/mysql/mysql-server/blob/8.0/sql/item_pfs_func.h], where source code declaration is [item_pfs_func.cc |https://github.com/mysql/mysql-server/blob/8.0/sql/item_pfs_func.cc]. Definition of native functions {{format_pico_time}} for example is [item_create.cc|https://github.com/mysql/mysql-server/blob/8.0/sql/item_create.cc#L1416]
            - Commit and author that introduced native function is
            {code:noformat}
            commit 260e09ea654a9688f588c13f5303bcc34dfc4a7d
            Author: Christopher Powers <chris.powers@oracle.com>
            Date: Thu Dec 13 01:33:24 2018 +0100

                WL#7803 Performance Schema, Native Functions
                
                Added native functions:
                
                  ps_current_thread_id()
                  ps_thread_id()
                  format_bytes()
                  format_pico_time()
            {code}
            - Test are added to {{perfschema}} suite {{suite/perfschema/t/}}
              1. [native_func_format_time|https://github.com/mysql/mysql-server/blob/8.0/mysql-test/suite/perfschema/t/native_func_format_time.test]
              2. [native_func_format_bytes|https://github.com/mysql/mysql-server/blob/8.0/mysql-test/suite/perfschema/t/native_func_format_bytes.test]
              3. [native_func_thread_id|https://github.com/mysql/mysql-server/blob/8.0/mysql-test/suite/perfschema/t/native_func_thread_id.test]
              4. [native_func_thread_id_no_ps|https://github.com/mysql/mysql-server/blob/8.0/mysql-test/suite/perfschema/t/native_func_thread_id_no_ps.test]
              5. [native_func_thread_id_null|https://github.com/mysql/mysql-server/blob/8.0/mysql-test/suite/perfschema/t/native_func_thread_id_null.test]

            h4. MariaDB implementation
            - MariaDB doesn't have any native functions
            - MariaDB has {{sys}} schema function {{format_bytes}}, as stated in [sysschema.format_bytes|https://mariadb.com/kb/en/format_bytes/] documentation.
                 - This change is introduced from {{10.6.0}} with commit and author
            {code:noformat}

            {code}
            anel Anel Husakovic made changes -
            Description While this functions can be implemented as user SQL functions, they have become native on MySQL 8.0.16. They are simple and independent enough that backport them should be easy.
            Implementing them on MariaDB would allow:

            * Better compatibility with sys/performance_schema
            * Nice functions to have natively in any case for formatting reasons or even as a virtual column
            * Better MySQL compatibility, and avoiding user pain when migrating from MySQL from 8.0.16+
            * Better performance by being C rather than user functions/SQL

            https://mysql.wisborg.dk/2019/05/28/performance-schema-functions/

            h4. MySQL implementation
            - MySQL has native implementation of function:
              1. [format_bytes|https://dev.mysql.com/doc/refman/8.0/en/performance-schema-functions.html#function_format-bytes],
              2. [format_pico_time|https://dev.mysql.com/doc/refman/8.0/en/performance-schema-functions.html#function_format-pico-time]
              3. [ps_thread_id|https://dev.mysql.com/doc/refman/8.0/en/performance-schema-functions.html#function_ps-thread-id]
              4. [ps_current_thread_id|https://dev.mysql.com/doc/refman/8.0/en/performance-schema-functions.html#function_ps-current-thread-id]

            - As well as {{sys}} schema defined functions \[that are marked as deprecated and will be removed\]:
              1. [format_bytes|https://dev.mysql.com/doc/refman/8.0/en/sys-format-bytes.html]
              2. [ps_thread_id|https://dev.mysql.com/doc/refman/8.0/en/sys-ps-thread-id.html]
              3. [format_time|https://dev.mysql.com/doc/refman/8.0/en/sys-format-time.html]

            - In MySQL all native functions are defined in file [item_pfs_func.h|https://github.com/mysql/mysql-server/blob/8.0/sql/item_pfs_func.h], where source code declaration is [item_pfs_func.cc |https://github.com/mysql/mysql-server/blob/8.0/sql/item_pfs_func.cc]. Definition of native functions {{format_pico_time}} for example is [item_create.cc|https://github.com/mysql/mysql-server/blob/8.0/sql/item_create.cc#L1416]
            - Commit and author that introduced native function is
            {code:noformat}
            commit 260e09ea654a9688f588c13f5303bcc34dfc4a7d
            Author: Christopher Powers <chris.powers@oracle.com>
            Date: Thu Dec 13 01:33:24 2018 +0100

                WL#7803 Performance Schema, Native Functions
                
                Added native functions:
                
                  ps_current_thread_id()
                  ps_thread_id()
                  format_bytes()
                  format_pico_time()
            {code}
            - Test are added to {{perfschema}} suite {{suite/perfschema/t/}}
              1. [native_func_format_time|https://github.com/mysql/mysql-server/blob/8.0/mysql-test/suite/perfschema/t/native_func_format_time.test]
              2. [native_func_format_bytes|https://github.com/mysql/mysql-server/blob/8.0/mysql-test/suite/perfschema/t/native_func_format_bytes.test]
              3. [native_func_thread_id|https://github.com/mysql/mysql-server/blob/8.0/mysql-test/suite/perfschema/t/native_func_thread_id.test]
              4. [native_func_thread_id_no_ps|https://github.com/mysql/mysql-server/blob/8.0/mysql-test/suite/perfschema/t/native_func_thread_id_no_ps.test]
              5. [native_func_thread_id_null|https://github.com/mysql/mysql-server/blob/8.0/mysql-test/suite/perfschema/t/native_func_thread_id_null.test]

            h4. MariaDB implementation
            - MariaDB doesn't have any native functions
            - MariaDB has {{sys}} schema function {{format_bytes}}, as stated in [sysschema.format_bytes|https://mariadb.com/kb/en/format_bytes/] documentation.
                 - This change is introduced from {{10.6.0}} with commit and author
            {code:noformat}

            {code}             		While this functions can be implemented as user SQL functions, they have become native on MySQL 8.0.16. They are simple and independent enough that backport them should be easy.
            Implementing them on MariaDB would allow:

            * Better compatibility with sys/performance_schema
            * Nice functions to have natively in any case for formatting reasons or even as a virtual column
            * Better MySQL compatibility, and avoiding user pain when migrating from MySQL from 8.0.16+
            * Better performance by being C rather than user functions/SQL

            https://mysql.wisborg.dk/2019/05/28/performance-schema-functions/

            h4. MySQL implementation
            - MySQL has native implementation of function:
              1. [format_bytes|https://dev.mysql.com/doc/refman/8.0/en/performance-schema-functions.html#function_format-bytes],
              2. [format_pico_time|https://dev.mysql.com/doc/refman/8.0/en/performance-schema-functions.html#function_format-pico-time]
              3. [ps_thread_id|https://dev.mysql.com/doc/refman/8.0/en/performance-schema-functions.html#function_ps-thread-id]
              4. [ps_current_thread_id|https://dev.mysql.com/doc/refman/8.0/en/performance-schema-functions.html#function_ps-current-thread-id]

            - As well as {{sys}} schema defined functions \[that are marked as deprecated and will be removed\]:
              1. [format_bytes|https://dev.mysql.com/doc/refman/8.0/en/sys-format-bytes.html]
              2. [ps_thread_id|https://dev.mysql.com/doc/refman/8.0/en/sys-ps-thread-id.html]
              3. [format_time|https://dev.mysql.com/doc/refman/8.0/en/sys-format-time.html]

            - In MySQL all native functions are defined in file [item_pfs_func.h|https://github.com/mysql/mysql-server/blob/8.0/sql/item_pfs_func.h], where source code declaration is [item_pfs_func.cc |https://github.com/mysql/mysql-server/blob/8.0/sql/item_pfs_func.cc]. Definition of native functions {{format_pico_time}} for example is [item_create.cc|https://github.com/mysql/mysql-server/blob/8.0/sql/item_create.cc#L1416]
            - Commit and author that introduced native function is
            {code:noformat}
            commit 260e09ea654a9688f588c13f5303bcc34dfc4a7d
            Author: Christopher Powers <chris.powers@oracle.com>
            Date: Thu Dec 13 01:33:24 2018 +0100

                WL#7803 Performance Schema, Native Functions
                
                Added native functions:
                
                  ps_current_thread_id()
                  ps_thread_id()
                  format_bytes()
                  format_pico_time()
            {code}
            - Test are added to {{perfschema}} suite {{suite/perfschema/t/}}
              1. [native_func_format_time|https://github.com/mysql/mysql-server/blob/8.0/mysql-test/suite/perfschema/t/native_func_format_time.test]
              2. [native_func_format_bytes|https://github.com/mysql/mysql-server/blob/8.0/mysql-test/suite/perfschema/t/native_func_format_bytes.test]
              3. [native_func_thread_id|https://github.com/mysql/mysql-server/blob/8.0/mysql-test/suite/perfschema/t/native_func_thread_id.test]
              4. [native_func_thread_id_no_ps|https://github.com/mysql/mysql-server/blob/8.0/mysql-test/suite/perfschema/t/native_func_thread_id_no_ps.test]
              5. [native_func_thread_id_null|https://github.com/mysql/mysql-server/blob/8.0/mysql-test/suite/perfschema/t/native_func_thread_id_null.test]

            h4. MariaDB implementation
            - MariaDB doesn't have any native functions
            - MariaDB has {{sys}} schema function {{format_bytes}}, as stated in [sysschema.format_bytes|https://mariadb.com/kb/en/format_bytes/] documentation.
                 - This change is introduced from {{10.6.0}} with commits and author
            {code:noformat}
            commit aa2ff62082c4eb09b1d505af0d0327278c9f7d08
            Author: Vladislav Vaintroub <wlad@mariadb.com>
            Date: Wed Mar 3 10:30:29 2021 +0100

                MDEV-9077 Use sys schema in bootstrapping, incl. mtr

            {code}
            anel Anel Husakovic made changes -
            Description While this functions can be implemented as user SQL functions, they have become native on MySQL 8.0.16. They are simple and independent enough that backport them should be easy.
            Implementing them on MariaDB would allow:

            * Better compatibility with sys/performance_schema
            * Nice functions to have natively in any case for formatting reasons or even as a virtual column
            * Better MySQL compatibility, and avoiding user pain when migrating from MySQL from 8.0.16+
            * Better performance by being C rather than user functions/SQL

            https://mysql.wisborg.dk/2019/05/28/performance-schema-functions/

            h4. MySQL implementation
            - MySQL has native implementation of function:
              1. [format_bytes|https://dev.mysql.com/doc/refman/8.0/en/performance-schema-functions.html#function_format-bytes],
              2. [format_pico_time|https://dev.mysql.com/doc/refman/8.0/en/performance-schema-functions.html#function_format-pico-time]
              3. [ps_thread_id|https://dev.mysql.com/doc/refman/8.0/en/performance-schema-functions.html#function_ps-thread-id]
              4. [ps_current_thread_id|https://dev.mysql.com/doc/refman/8.0/en/performance-schema-functions.html#function_ps-current-thread-id]

            - As well as {{sys}} schema defined functions \[that are marked as deprecated and will be removed\]:
              1. [format_bytes|https://dev.mysql.com/doc/refman/8.0/en/sys-format-bytes.html]
              2. [ps_thread_id|https://dev.mysql.com/doc/refman/8.0/en/sys-ps-thread-id.html]
              3. [format_time|https://dev.mysql.com/doc/refman/8.0/en/sys-format-time.html]

            - In MySQL all native functions are defined in file [item_pfs_func.h|https://github.com/mysql/mysql-server/blob/8.0/sql/item_pfs_func.h], where source code declaration is [item_pfs_func.cc |https://github.com/mysql/mysql-server/blob/8.0/sql/item_pfs_func.cc]. Definition of native functions {{format_pico_time}} for example is [item_create.cc|https://github.com/mysql/mysql-server/blob/8.0/sql/item_create.cc#L1416]
            - Commit and author that introduced native function is
            {code:noformat}
            commit 260e09ea654a9688f588c13f5303bcc34dfc4a7d
            Author: Christopher Powers <chris.powers@oracle.com>
            Date: Thu Dec 13 01:33:24 2018 +0100

                WL#7803 Performance Schema, Native Functions
                
                Added native functions:
                
                  ps_current_thread_id()
                  ps_thread_id()
                  format_bytes()
                  format_pico_time()
            {code}
            - Test are added to {{perfschema}} suite {{suite/perfschema/t/}}
              1. [native_func_format_time|https://github.com/mysql/mysql-server/blob/8.0/mysql-test/suite/perfschema/t/native_func_format_time.test]
              2. [native_func_format_bytes|https://github.com/mysql/mysql-server/blob/8.0/mysql-test/suite/perfschema/t/native_func_format_bytes.test]
              3. [native_func_thread_id|https://github.com/mysql/mysql-server/blob/8.0/mysql-test/suite/perfschema/t/native_func_thread_id.test]
              4. [native_func_thread_id_no_ps|https://github.com/mysql/mysql-server/blob/8.0/mysql-test/suite/perfschema/t/native_func_thread_id_no_ps.test]
              5. [native_func_thread_id_null|https://github.com/mysql/mysql-server/blob/8.0/mysql-test/suite/perfschema/t/native_func_thread_id_null.test]

            h4. MariaDB implementation
            - MariaDB doesn't have any native functions
            - MariaDB has {{sys}} schema function {{format_bytes}}, as stated in [sysschema.format_bytes|https://mariadb.com/kb/en/format_bytes/] documentation.
                 - This change is introduced from {{10.6.0}} with commits and author
            {code:noformat}
            commit aa2ff62082c4eb09b1d505af0d0327278c9f7d08
            Author: Vladislav Vaintroub <wlad@mariadb.com>
            Date: Wed Mar 3 10:30:29 2021 +0100

                MDEV-9077 Use sys schema in bootstrapping, incl. mtr

            {code}             		While this functions can be implemented as user SQL functions, they have become native on MySQL 8.0.16. They are simple and independent enough that backport them should be easy.
            Implementing them on MariaDB would allow:

            * Better compatibility with sys/performance_schema
            * Nice functions to have natively in any case for formatting reasons or even as a virtual column
            * Better MySQL compatibility, and avoiding user pain when migrating from MySQL from 8.0.16+
            * Better performance by being C rather than user functions/SQL

            https://mysql.wisborg.dk/2019/05/28/performance-schema-functions/

            h4. MySQL implementation
            - MySQL has native implementation of function:
              1. [format_bytes|https://dev.mysql.com/doc/refman/8.0/en/performance-schema-functions.html#function_format-bytes],
              2. [format_pico_time|https://dev.mysql.com/doc/refman/8.0/en/performance-schema-functions.html#function_format-pico-time]
              3. [ps_thread_id|https://dev.mysql.com/doc/refman/8.0/en/performance-schema-functions.html#function_ps-thread-id]
              4. [ps_current_thread_id|https://dev.mysql.com/doc/refman/8.0/en/performance-schema-functions.html#function_ps-current-thread-id]

            - As well as {{sys}} schema defined functions \[that are marked as deprecated and will be removed\]:
              1. [format_bytes|https://dev.mysql.com/doc/refman/8.0/en/sys-format-bytes.html]
              2. [ps_thread_id|https://dev.mysql.com/doc/refman/8.0/en/sys-ps-thread-id.html]
              3. [format_time|https://dev.mysql.com/doc/refman/8.0/en/sys-format-time.html]

            - In MySQL all native functions are defined in file [item_pfs_func.h|https://github.com/mysql/mysql-server/blob/8.0/sql/item_pfs_func.h], where source code declaration is [item_pfs_func.cc |https://github.com/mysql/mysql-server/blob/8.0/sql/item_pfs_func.cc]. Definition of native functions {{format_pico_time}} for example is [item_create.cc|https://github.com/mysql/mysql-server/blob/8.0/sql/item_create.cc#L1416]
            - Commit and author that introduced native function is
            {code:noformat}
            commit 260e09ea654a9688f588c13f5303bcc34dfc4a7d
            Author: Christopher Powers <chris.powers@oracle.com>
            Date: Thu Dec 13 01:33:24 2018 +0100

                WL#7803 Performance Schema, Native Functions
                
                Added native functions:
                
                  ps_current_thread_id()
                  ps_thread_id()
                  format_bytes()
                  format_pico_time()
            {code}
            - Test are added to {{perfschema}} suite {{suite/perfschema/t/}}
              1. [native_func_format_time|https://github.com/mysql/mysql-server/blob/8.0/mysql-test/suite/perfschema/t/native_func_format_time.test]
              2. [native_func_format_bytes|https://github.com/mysql/mysql-server/blob/8.0/mysql-test/suite/perfschema/t/native_func_format_bytes.test]
              3. [native_func_thread_id|https://github.com/mysql/mysql-server/blob/8.0/mysql-test/suite/perfschema/t/native_func_thread_id.test]
              4. [native_func_thread_id_no_ps|https://github.com/mysql/mysql-server/blob/8.0/mysql-test/suite/perfschema/t/native_func_thread_id_no_ps.test]
              5. [native_func_thread_id_null|https://github.com/mysql/mysql-server/blob/8.0/mysql-test/suite/perfschema/t/native_func_thread_id_null.test]

            h4. MariaDB implementation
            - MariaDB doesn't have any native functions
            - MariaDB has {{sys}} schema function {{format_bytes}}, as stated in [sysschema.format_bytes|https://mariadb.com/kb/en/format_bytes/] documentation.
                 - This change is introduced from {{10.6.0}} with commits and author
            {code:noformat}
            commit aa2ff62082c4eb09b1d505af0d0327278c9f7d08
            Author: Vladislav Vaintroub <wlad@mariadb.com>
            Date: Wed Mar 3 10:30:29 2021 +0100

                MDEV-9077 Use sys schema in bootstrapping, incl. mtr
            {code}
            and second commit
            {code:noformat}
            commit 4bac804c90c6f8d101ef32aef39813c3d13283ff
            Author: Vladislav Vaintroub <wlad@mariadb.com>
            Date: Wed Mar 17 17:57:11 2021 +0100

                MDEV-9077 add sys_schema from https://github.com/mysql/mysql-sys
            {code}
            - *Tasks*:
              The task can be divided into 3 steps by backporting the patch from {{MySQL}} if possible in single commit and if not in separate commits:
               - Create native function {{format_pico_time}}
               - Create native function {{ps_thread_id}}
               - Create native function {{ps_current_thread_id}}
              - Create native function for {{format_bytes}}
            anel Anel Husakovic made changes -
            Assignee Anel Husakovic [ anel ]
            anel Anel Husakovic made changes -
            Description While this functions can be implemented as user SQL functions, they have become native on MySQL 8.0.16. They are simple and independent enough that backport them should be easy.
            Implementing them on MariaDB would allow:

            * Better compatibility with sys/performance_schema
            * Nice functions to have natively in any case for formatting reasons or even as a virtual column
            * Better MySQL compatibility, and avoiding user pain when migrating from MySQL from 8.0.16+
            * Better performance by being C rather than user functions/SQL

            https://mysql.wisborg.dk/2019/05/28/performance-schema-functions/

            h4. MySQL implementation
            - MySQL has native implementation of function:
              1. [format_bytes|https://dev.mysql.com/doc/refman/8.0/en/performance-schema-functions.html#function_format-bytes],
              2. [format_pico_time|https://dev.mysql.com/doc/refman/8.0/en/performance-schema-functions.html#function_format-pico-time]
              3. [ps_thread_id|https://dev.mysql.com/doc/refman/8.0/en/performance-schema-functions.html#function_ps-thread-id]
              4. [ps_current_thread_id|https://dev.mysql.com/doc/refman/8.0/en/performance-schema-functions.html#function_ps-current-thread-id]

            - As well as {{sys}} schema defined functions \[that are marked as deprecated and will be removed\]:
              1. [format_bytes|https://dev.mysql.com/doc/refman/8.0/en/sys-format-bytes.html]
              2. [ps_thread_id|https://dev.mysql.com/doc/refman/8.0/en/sys-ps-thread-id.html]
              3. [format_time|https://dev.mysql.com/doc/refman/8.0/en/sys-format-time.html]

            - In MySQL all native functions are defined in file [item_pfs_func.h|https://github.com/mysql/mysql-server/blob/8.0/sql/item_pfs_func.h], where source code declaration is [item_pfs_func.cc |https://github.com/mysql/mysql-server/blob/8.0/sql/item_pfs_func.cc]. Definition of native functions {{format_pico_time}} for example is [item_create.cc|https://github.com/mysql/mysql-server/blob/8.0/sql/item_create.cc#L1416]
            - Commit and author that introduced native function is
            {code:noformat}
            commit 260e09ea654a9688f588c13f5303bcc34dfc4a7d
            Author: Christopher Powers <chris.powers@oracle.com>
            Date: Thu Dec 13 01:33:24 2018 +0100

                WL#7803 Performance Schema, Native Functions
                
                Added native functions:
                
                  ps_current_thread_id()
                  ps_thread_id()
                  format_bytes()
                  format_pico_time()
            {code}
            - Test are added to {{perfschema}} suite {{suite/perfschema/t/}}
              1. [native_func_format_time|https://github.com/mysql/mysql-server/blob/8.0/mysql-test/suite/perfschema/t/native_func_format_time.test]
              2. [native_func_format_bytes|https://github.com/mysql/mysql-server/blob/8.0/mysql-test/suite/perfschema/t/native_func_format_bytes.test]
              3. [native_func_thread_id|https://github.com/mysql/mysql-server/blob/8.0/mysql-test/suite/perfschema/t/native_func_thread_id.test]
              4. [native_func_thread_id_no_ps|https://github.com/mysql/mysql-server/blob/8.0/mysql-test/suite/perfschema/t/native_func_thread_id_no_ps.test]
              5. [native_func_thread_id_null|https://github.com/mysql/mysql-server/blob/8.0/mysql-test/suite/perfschema/t/native_func_thread_id_null.test]

            h4. MariaDB implementation
            - MariaDB doesn't have any native functions
            - MariaDB has {{sys}} schema function {{format_bytes}}, as stated in [sysschema.format_bytes|https://mariadb.com/kb/en/format_bytes/] documentation.
                 - This change is introduced from {{10.6.0}} with commits and author
            {code:noformat}
            commit aa2ff62082c4eb09b1d505af0d0327278c9f7d08
            Author: Vladislav Vaintroub <wlad@mariadb.com>
            Date: Wed Mar 3 10:30:29 2021 +0100

                MDEV-9077 Use sys schema in bootstrapping, incl. mtr
            {code}
            and second commit
            {code:noformat}
            commit 4bac804c90c6f8d101ef32aef39813c3d13283ff
            Author: Vladislav Vaintroub <wlad@mariadb.com>
            Date: Wed Mar 17 17:57:11 2021 +0100

                MDEV-9077 add sys_schema from https://github.com/mysql/mysql-sys
            {code}
            - *Tasks*:
              The task can be divided into 3 steps by backporting the patch from {{MySQL}} if possible in single commit and if not in separate commits:
               - Create native function {{format_pico_time}}
               - Create native function {{ps_thread_id}}
               - Create native function {{ps_current_thread_id}}
              - Create native function for {{format_bytes}}             		While this functions can be implemented as user SQL functions, they have become native on MySQL 8.0.16. They are simple and independent enough that backport them should be easy.
            Implementing them on MariaDB would allow:

            * Better compatibility with sys/performance_schema
            * Nice functions to have natively in any case for formatting reasons or even as a virtual column
            * Better MySQL compatibility, and avoiding user pain when migrating from MySQL from 8.0.16+
            * Better performance by being C rather than user functions/SQL

            https://mysql.wisborg.dk/2019/05/28/performance-schema-functions/

            h4. MySQL implementation
            - MySQL has native implementation of function:
              1. [format_bytes|https://dev.mysql.com/doc/refman/8.0/en/performance-schema-functions.html#function_format-bytes],
              2. [format_pico_time|https://dev.mysql.com/doc/refman/8.0/en/performance-schema-functions.html#function_format-pico-time]
              3. [ps_thread_id|https://dev.mysql.com/doc/refman/8.0/en/performance-schema-functions.html#function_ps-thread-id]
              4. [ps_current_thread_id|https://dev.mysql.com/doc/refman/8.0/en/performance-schema-functions.html#function_ps-current-thread-id]

            - As well as {{sys}} schema defined functions \[that are marked as deprecated and will be removed\]:
              1. [format_bytes|https://dev.mysql.com/doc/refman/8.0/en/sys-format-bytes.html]
              2. [ps_thread_id|https://dev.mysql.com/doc/refman/8.0/en/sys-ps-thread-id.html]
              3. [format_time|https://dev.mysql.com/doc/refman/8.0/en/sys-format-time.html]

            - In MySQL all native functions are defined in file [item_pfs_func.h|https://github.com/mysql/mysql-server/blob/8.0/sql/item_pfs_func.h], where source code declaration is [item_pfs_func.cc |https://github.com/mysql/mysql-server/blob/8.0/sql/item_pfs_func.cc]. Definition of native functions {{format_pico_time}} for example is [item_create.cc|https://github.com/mysql/mysql-server/blob/8.0/sql/item_create.cc#L1416]
            - Commit and author that introduced native function is
            {code:noformat}
            commit 260e09ea654a9688f588c13f5303bcc34dfc4a7d
            Author: Christopher Powers <chris.powers@oracle.com>
            Date: Thu Dec 13 01:33:24 2018 +0100

                WL#7803 Performance Schema, Native Functions
                
                Added native functions:
                
                  ps_current_thread_id()
                  ps_thread_id()
                  format_bytes()
                  format_pico_time()
            {code}
            - Test are added to {{perfschema}} suite {{suite/perfschema/t/}}
              1. [native_func_format_time|https://github.com/mysql/mysql-server/blob/8.0/mysql-test/suite/perfschema/t/native_func_format_time.test]
              2. [native_func_format_bytes|https://github.com/mysql/mysql-server/blob/8.0/mysql-test/suite/perfschema/t/native_func_format_bytes.test]
              3. [native_func_thread_id|https://github.com/mysql/mysql-server/blob/8.0/mysql-test/suite/perfschema/t/native_func_thread_id.test]
              4. [native_func_thread_id_no_ps|https://github.com/mysql/mysql-server/blob/8.0/mysql-test/suite/perfschema/t/native_func_thread_id_no_ps.test]
              5. [native_func_thread_id_null|https://github.com/mysql/mysql-server/blob/8.0/mysql-test/suite/perfschema/t/native_func_thread_id_null.test]

            h4. MariaDB implementation
            - MariaDB doesn't have any native functions
            - MariaDB has {{sys}} schema function {{format_bytes}}, as stated in [sysschema.format_bytes|https://mariadb.com/kb/en/format_bytes/] documentation.
                 - This change is introduced from {{10.6.0}} with commits and author
            {code:noformat}
            commit aa2ff62082c4eb09b1d505af0d0327278c9f7d08
            Author: Vladislav Vaintroub <wlad@mariadb.com>
            Date: Wed Mar 3 10:30:29 2021 +0100

                MDEV-9077 Use sys schema in bootstrapping, incl. mtr
            {code}
            and second commit
            {code:noformat}
            commit 4bac804c90c6f8d101ef32aef39813c3d13283ff
            Author: Vladislav Vaintroub <wlad@mariadb.com>
            Date: Wed Mar 17 17:57:11 2021 +0100

                MDEV-9077 add sys_schema from https://github.com/mysql/mysql-sys
            {code}
            - *Tasks*:
              The task can be divided into 3 steps by backporting the patch from {{MySQL}} if possible in single commit and if not in separate commits:
               - -Create native function {{format_pico_time}}- - task done as contribution with [PR 2558|https://github.com/MariaDB/server/pull/2558] on 11.0, commit [d9808f79|https://github.com/MariaDB/server/commit/d9808f79de992964ed802d27984c9031d72e7b9a]
               - Create native function {{ps_thread_id}}
               - Create native function {{ps_current_thread_id}}
              - Create native function for {{format_bytes}}
            Abdelfatah Abdelrahman added a comment -

            Is any one working on "Create native function for format_bytes" ? I would like to start working on it.
            I found out that "format_bytes" is deprecated according to this https://dev.mysql.com/doc/refman/8.0/en/sys-format-bytes.html , and there's a new version https://dev.mysql.com/doc/refman/8.0/en/performance-schema-functions.html#function_format-bytes , which one should I work on ? the difference is minimal anyways and it would be easy to implement.

            Abdelfatah Abdelrahman added a comment - Is any one working on "Create native function for format_bytes" ? I would like to start working on it. I found out that "format_bytes" is deprecated according to this https://dev.mysql.com/doc/refman/8.0/en/sys-format-bytes.html , and there's a new version https://dev.mysql.com/doc/refman/8.0/en/performance-schema-functions.html#function_format-bytes , which one should I work on ? the difference is minimal anyways and it would be easy to implement.
            anel Anel Husakovic made changes -
            jcrespo Jaime Crespo added a comment -

            @Abdelrahman please note that what's deprecated is the sys stored procedure, precisely because they exist in MySQL as built-in functions (and why this was filed). The latter version is what's asked.

            I would ask Anel first before taking it, as they started working on one of the subtasks.

            jcrespo Jaime Crespo added a comment - @Abdelrahman please note that what's deprecated is the sys stored procedure, precisely because they exist in MySQL as built-in functions (and why this was filed). The latter version is what's asked. I would ask Anel first before taking it, as they started working on one of the subtasks.
            danblack Daniel Black made changes -
            danblack Daniel Black made changes -
            Assignee Anel Husakovic [ anel ]
            elenst Elena Stepanova made changes -

            People

              Unassigned Unassigned
              jcrespo Jaime Crespo
              Votes:
              1 Vote for this issue
              Watchers:
              7 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.