Details
-
Task
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
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
-
-
- In Review
-
- causes
-
MDEV-26646 SFORMAT Does not allow @variable use
-
- Closed
-
-
-
- Open
-
-
-
- Open
-
-
-
- Open
-
-
-
- Closed
-
- relates to
-
-
- Confirmed
-
- links to
Activity
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.
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.
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.
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.
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)