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

Custom formatting of strings in MariaDB queries

Details

    Description

      Formatting more complex strings in a SELECT statement can get awkward when there are many concat(), format(), etc calls involved.
      It would be very cool and helpful to have a function that takes an input string and a formatting specification and returns string formatted using the rules the user passed in the specification.

      A great example for such a function is the classic C printf function, which, in this context, would look something like:

      SELECT printf('%s %s, %s', first_name, last_name, job_title) from employees;

      But it doesn't necessarily need to look this way, an alternative syntax could be Python-ish, which would leverage the fact that the server already knows the datatype of each field used in the formatting scheme:

      SELECT sformat('arg1: {}, arg2: {}', col1, col2) from table;

      In that syntax one passes formatting options within the curly braces:

      -- Print 'arg1: col1, arg2: col2'  where col1 from table is of datetime type and should be printed as: 'Sunday November 2021'
      		 
      SELECT sformat('arg1: {%W %M %Y}, arg2: {}', col1, col2) from table;

      Ideally, this new function should use, behind the scenes, the existing builtin formatting functions in MariaDB (e.g. date_format(), format()) and even future formatting functions (e.g. MySQL's format_bytes(), format_pico_time()), so the syntax has to be designed in a smart way to accommodate easily future additions.

      Attachments

        Issue Links

          blocks

          Task - A task that needs to be done. MDEV-19784 C++11 protectors for my_error

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-26646 SFORMAT Does not allow @variable use

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Task - A task that needs to be done. MDEV-26648 SFORMAT auto-typing

          • Major - Major loss of function.
          • Open

          Task - A task that needs to be done. MDEV-26649 SFORMAT: Temporal type support (DATE) missing

          • Major - Major loss of function.
          • Open

          Task - A task that needs to be done. MDEV-26653 SFORMAT: DECIMAL support is missing

          • Major - Major loss of function.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-26691 SFORMAT: Pass down FLOAT as FLOAT, without upcast to DOUBLE

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed
          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-31024 Server crash / ASAN use-after-poison in Binary_string::free_buffer / Item_func_sformat::~Item_func_sformat

          • Major - Major loss of function.
          • Confirmed
          links to

          Web Link C printf

          Web Link PostgreSQL

          Web Link Python 3

          Web Link Rust

          Web Link {fmt} — implementation of C++20 std::format

          Activity

            Ascending order - Click to sort in descending order
            View 16 older comments
            Roel Roel Van de Paar added a comment - - edited

            Though it's covered in the testcase (input (5,5,5)) should this give a warning ("extraneous inputs/parameter found") - or the existing ERROR 1582 (42000): Incorrect parameter count in the call to native function 'sformat'?

            10.7.0 53b2c1f4664a3cb90f583979d9aa2771b7e1c98d (Debug)

            		 
            10.7.0-dbg>SELECT SFORMAT(1,2,3);
            		 
            +----------------+
            		 
            | SFORMAT(1,2,3) |
            		 
            +----------------+
            		 
            | 1              |
            		 
            +----------------+
            		 
            1 row in set (0.000 sec)

            Roel Roel Van de Paar added a comment - - edited Though it's covered in the testcase ( input (5,5,5) ) should this give a warning ("extraneous inputs/parameter found") - or the existing ERROR 1582 (42000): Incorrect parameter count in the call to native function 'sformat' ? 10.7.0 53b2c1f4664a3cb90f583979d9aa2771b7e1c98d (Debug) 10.7.0-dbg>SELECT SFORMAT(1,2,3); +----------------+ | SFORMAT(1,2,3) | +----------------+ | 1 | +----------------+ 1 row in set (0.000 sec)
            Roel Roel Van de Paar added a comment -

            Further testing has not revealed any additional issues save the one above.

            Re-assigning back to serg for push to trunk if not done already.

            Roel Roel Van de Paar added a comment - Further testing has not revealed any additional issues save the one above. Re-assigning back to serg for push to trunk if not done already.
            f_razzoli Federico Razzoli added a comment - - edited

            Trying to use named parameters ('{name}') doesn't return a specific error, though I couldn't find a way to make it work. Please clarify how to use named parameters in the docs, or produce an error that clarifies that this is not supported.

            f_razzoli Federico Razzoli added a comment - - edited Trying to use named parameters ('{name}') doesn't return a specific error, though I couldn't find a way to make it work. Please clarify how to use named parameters in the docs, or produce an error that clarifies that this is not supported.
            serg Sergei Golubchik added a comment -

            This is the error message that libfmt returns. And yes named parameters don't work. It'd open a very special can of worms, consider, for example,

            select sformat(concat("Column value: {", col, "}")) from t1;

            which shoud show the value of a column which name is stored in the column col.

            I'm not saying it's impossible to make it work, but it'd be very complex with tons of vague corner cases. We decided not to go there for now.

            serg Sergei Golubchik added a comment - This is the error message that libfmt returns. And yes named parameters don't work. It'd open a very special can of worms, consider, for example, select sformat(concat( "Column value: {" , col, "}" )) from t1; which shoud show the value of a column which name is stored in the column col . I'm not saying it's impossible to make it work, but it'd be very complex with tons of vague corner cases. We decided not to go there for now.
            f_razzoli Federico Razzoli added a comment -

            I think there are ways to write obscure queries both with named and positional arguments. But it's reasonable to omit a feature from libfmt, I just think that it should be more clear.

            f_razzoli Federico Razzoli added a comment - I think there are ways to write obscure queries both with named and positional arguments. But it's reasonable to omit a feature from libfmt, I just think that it should be more clear.

            People

              serg Sergei Golubchik
              robertbindar Robert Bindar
              Votes:
              1 Vote for this issue
              Watchers:
              8 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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