[MDEV-25015] Custom formatting of strings in MariaDB queries Created: 2021-03-01  Updated: 2023-04-07  Resolved: 2021-10-12

Status: Closed
Project: MariaDB Server
Component/s: Server
Fix Version/s: 10.7.1

Type: Task Priority: Critical
Reporter: Robert Bindar Assignee: Sergei Golubchik
Resolution: Fixed Votes: 1
Labels: Preview_10.7, gsoc21

Issue Links:
Blocks
blocks MDEV-19784 C++11 protectors for my_error In Review
Problem/Incident
causes MDEV-26646 SFORMAT Does not allow @variable use Closed
causes MDEV-26648 Feature: SFORMAT auto-typing Open
causes MDEV-26649 SFORMAT: Temporal type support (DATE)... Open
causes MDEV-26653 SFORMAT: DECIMAL support is missing Open
causes MDEV-26691 SFORMAT: Pass down FLOAT as FLOAT, wi... Closed
Relates
relates to MDEV-31024 Server crash / ASAN use-after-poison ... Confirmed

 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.

 Comments   
Comment by Sergei Golubchik [ 2021-03-01 ]

is there anything comparable in other DBMS-es?

Comment by Sergei Golubchik [ 2021-03-27 ]

Didn't find in Oracle, DB2, or SQL Server.
PostgreSQL has https://www.postgresql.org/docs/9.3/functions-string.html#FUNCTIONS-STRING-FORMAT

Comment by Sergei Golubchik [ 2021-05-12 ]

Perhaps, suggesting {%W %M %Y} wasn't such a great idea, one can always do

SELECT sformat('arg1: {}', date_format(col1, '%W %M %Y')) from table

May be it'd be more consistent to stick to python-ish format and not allow completely different syntax inside curly braces for different types.

Having PostgreSQL-like "sql literal" and "identifier" (also like %`s in my_vsnprinf()) modifiers would be quite useful, though.

Comment by Sergei Golubchik [ 2021-09-05 ]

just to record the conclusion. We ended up using fmt library.

Changes/limitation:

  • temporal data are handled as strings (no native support yet)
  • decimal data are handled as doubles (no native support yet)
  • row values are syntax errors (the parser cannot do it)
  • {:р} is not supported, obviously
  • {:L} uses separators as in FORMAT() function, and does not look at the OS locale
Comment by Roel Van de Paar [ 2021-09-18 ]

serg Thank you for the solid MTR testcase main/func_sformat.test

Comment by Roel Van de Paar [ 2021-09-18 ]

serg Hi! There is a compiler error when installing libfmt-dev on Ubuntu 20.04.3 LTS and compiling:

[ 91%] Built target ma_test_loghandler_first_lsn-t
 
[ 91%] Building CXX object sql/CMakeFiles/sql.dir/lock.cc.o
 
/test/MDEV-25015-10.7_dbg/sql/item_strfunc.cc: In member function ‘virtual String* Item_func_sformat::val_str(String*)’:
 
/test/MDEV-25015-10.7_dbg/sql/item_strfunc.cc:1392:27: error: ‘fmt::detail’ has not been declared
 
 1392 |       vargs[carg-1]= fmt::detail::make_arg<ctx>(args[carg]->val_int());
 
      |                           ^~~~~~
 
/test/MDEV-25015-10.7_dbg/sql/item_strfunc.cc:1392:47: error: expected primary-expression before ‘>’ token
 
 1392 |       vargs[carg-1]= fmt::detail::make_arg<ctx>(args[carg]->val_int());
 
      |                                               ^
 
/test/MDEV-25015-10.7_dbg/sql/item_strfunc.cc:1396:27: error: ‘fmt::detail’ has not been declared
 
 1396 |       vargs[carg-1]= fmt::detail::make_arg<ctx>(args[carg]->val_real());
 
      |                           ^~~~~~
 
/test/MDEV-25015-10.7_dbg/sql/item_strfunc.cc:1396:47: error: expected primary-expression before ‘>’ token
 
 1396 |       vargs[carg-1]= fmt::detail::make_arg<ctx>(args[carg]->val_real());
 
      |                                               ^
 
/test/MDEV-25015-10.7_dbg/sql/item_strfunc.cc:1405:29: error: ‘fmt::detail’ has not been declared
 
 1405 |         vargs[carg-1]= fmt::detail::make_arg<ctx>(parg->ptr()[0]);
 
      |                             ^~~~~~
 
/test/MDEV-25015-10.7_dbg/sql/item_strfunc.cc:1405:49: error: expected primary-expression before ‘>’ token
 
 1405 |         vargs[carg-1]= fmt::detail::make_arg<ctx>(parg->ptr()[0]);
 
      |                                                 ^
 
/test/MDEV-25015-10.7_dbg/sql/item_strfunc.cc:1407:29: error: ‘fmt::detail’ has not been declared
 
 1407 |         vargs[carg-1]= fmt::detail::make_arg<ctx>(*parg);
 
      |                             ^~~~~~
 
/test/MDEV-25015-10.7_dbg/sql/item_strfunc.cc:1407:49: error: expected primary-expression before ‘>’ token
 
 1407 |         vargs[carg-1]= fmt::detail::make_arg<ctx>(*parg);
 
      |                                                 ^
 
[ 91%] Building CXX object sql/CMakeFiles/sql.dir/log_event.cc.o
 
make[2]: *** [sql/CMakeFiles/sql.dir/build.make:418: sql/CMakeFiles/sql.dir/item_strfunc.cc.o] Error 1
 
make[2]: *** Waiting for unfinished jobs....
 
make[1]: *** [CMakeFiles/Makefile2:11022: sql/CMakeFiles/sql.dir/all] Error 2
 
make: *** [Makefile:163: all] Error 2

Compiling with that package not installed or purged works fine (and SFORMAT works in that case too).

Comment by Roel Van de Paar [ 2021-09-18 ]

serg Looking at TODO-3121, it could be the minimum version? The standard version (from sudo apt install libfmt-dev on 20.04.3 LTS) is 6.1 (libfmt-dev_6.1.2+ds-2_amd64.deb).

And, version 7 is not available by default E: Unable to locate package libfmt7-dev

Comment by Roel Van de Paar [ 2021-09-18 ]

Testing (without libfmt-dev installed, which works, ref question in TODO-3121) shows;

  • Feature looks stable and well implemented.
  • Created MTR test is extensive and also stable, even when run in parallel + many times.
  • When the same SQL is taken and run in 1k parallel threads using pquery, feature also remains stable.
  • Idem for CLI (2k threads) and still performant (SFORMAT).
  • Format rendering is ultra fast.
  • No ASAN nor UBSAN errors were detected.
Comment by Roel Van de Paar [ 2021-09-18 ]

I consider the feature OK to push.

Comment by Sergei Golubchik [ 2021-09-18 ]

Roel, you should use preview-10.7-MDEV-25015-sformat branch, as listed in the TODO-3118. bb-10.7-MDEV-25015-sformat is old and leaks memory every time SFORMAT is called.

Yes, our implementation needs libfmt at least 7.0, Ubuntu's 6.1 is too old. Thanks, for pointing it out, I'll fix it.

By the way, it's not my code or test cases, it's a GSoC'21 project implemented by Alan Cueva

Comment by Roel Van de Paar [ 2021-09-20 ]

serg Thank you. I went back to double check the logs, and that was indeed the branch I used for testing.

git clone --depth=1 --recurse-submodules -j8 --branch=preview-10.7-MDEV-25015-sformat https://github.com/MariaDB/server.git MDEV-25015-10.7

I see. Thank you Alan Cueva for a well done testcase. Ack on libmft.

Comment by Roel Van de Paar [ 2021-09-20 ]

Logged MDEV-26646 SFORMAT Does not allow @variable use (Warning 4183 SFORMAT error: invalid type specifier)
Logged MDEV-26648 Feature: SFORMAT auto-typing
Logged MDEV-26649 SFORMAT: Temporal type support (DATE) missing

Comment by Roel Van de Paar [ 2021-09-25 ]

serg Could you please add this to the testcase (subqueries were not included yet):

CREATE TABLE t1 (c1 FLOAT);
 
INSERT INTO t1 VALUES (1.1),(2.2);
 
SELECT SFORMAT ('Float 1.1: {:+f}', (SELECT c1 FROM t1 LIMIT 1));
 
SELECT SFORMAT ('Error ERROR 1242 (21000): Subquery returns more than 1 row: {:+f}', (SELECT c1 FROM t1));
 
SELECT SFORMAT ('Float 2.2: {:+f}', (SELECT c1 FROM t1 WHERE c1>2));
 
SELECT SFORMAT ('Float 1.1: {:+f}', (SELECT * FROM t1 LIMIT 1));
 
SELECT SFORMAT ('Error ERROR 1242 (21000): Subquery returns more than 1 row: {:+f}', (SELECT * FROM t1));
 
DROP TABLE t1;
 
CREATE TABLE t1 (c1 FLOAT,c2 FLOAT);
 
INSERT INTO t1 VALUES (1.1,2.2);
 
SELECT SFORMAT ('Float 1.1: {:+f}', (SELECT c1 FROM t1 LIMIT 1));
 
SELECT SFORMAT ('ERROR 1241 (21000): Operand should contain 1 column(s) {:+f}', (SELECT c1,c2 FROM t1 LIMIT 1));
 
SELECT SFORMAT ('ERROR 1241 (21000): Operand should contain 1 column(s) {:+f}', (SELECT * FROM t1 LIMIT 1));
 
DROP TABLE t1;

Thank you

Comment by Roel Van de Paar [ 2021-09-25 ]

Note sure if this could be considered a bug?

10.7.0 53b2c1f4664a3cb90f583979d9aa2771b7e1c98d (Debug)

 
10.7.0-dbg>CREATE TABLE t1 (c1 FLOAT);
 
Query OK, 0 rows affected (0.013 sec)
 
 
 
10.7.0-dbg>INSERT INTO t1 VALUES (1.1);
 
Query OK, 1 row affected (0.003 sec)
 
 
 
10.7.0-dbg>SELECT * FROM t1;
 
+------+
 
| c1   |
 
+------+
 
|  1.1 |
 
+------+
 
1 row in set (0.001 sec)
 
 
 
10.7.0-dbg>SELECT SFORMAT('{}',(SELECT 1.1));
 
+----------------------------+
 
| SFORMAT('{}',(SELECT 1.1)) |
 
+----------------------------+
 
| 1.1                        |
 
+----------------------------+
 
1 row in set (0.000 sec)
 
 
 
10.7.0-dbg>SELECT SFORMAT('{}',(SELECT c1 FROM t1));
 
+-----------------------------------+
 
| SFORMAT('{}',(SELECT c1 FROM t1)) |
 
+-----------------------------------+
 
| 1.100000023841858                 |
 
+-----------------------------------+
 
1 row in set (0.001 sec)

Comment by Sergei Golubchik [ 2021-09-25 ]

yes, let's consider it a bug. libfmt can print floats just fine, so we should pass down floats as floats without upcasting them to double.

Comment by Roel Van de Paar [ 2021-09-26 ]

Logged as MDEV-26691 SFORMAT: Pass down FLOAT as FLOAT, without upcast to DOUBLE

Comment by Roel Van de Paar [ 2021-09-30 ]

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)

Comment by Roel Van de Paar [ 2021-09-30 ]

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.

Comment by Federico Razzoli [ 2021-12-27 ]

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.

Comment by Sergei Golubchik [ 2021-12-27 ]

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.

Comment by Federico Razzoli [ 2021-12-27 ]

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.

Generated at Thu Feb 08 09:34:28 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.