[MDEV-25015] Custom formatting of strings in MariaDB queries - Jira

Robert Bindar created issue - 2021-03-01 11:55

Robert Bindar made changes - 2021-03-01 11:56

Field	Original Value	New Value
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, which would leverage the fact that the server already know the datatype of each field used in the formatting scheme: SELECT sformat('arg1: {}, arg2: {}', col1, col2) from table; Also this one has the advantage of being able to pass 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.	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, which would leverage the fact that the server already know the datatype of each field used in the formatting scheme: SELECT sformat('arg1: {}, arg2: {}', col1, col2) from table; Also this one has the advantage of being able to pass 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.

Robert Bindar made changes - 2021-03-01 11:57

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, which would leverage the fact that the server already know the datatype of each field used in the formatting scheme:

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

Also this one has the advantage of being able to pass 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.

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:

{code:sql}SELECT printf('%s %s %s', first_name, last_name, job_title) from employees;{code}

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

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

Also this one has the advantage of being able to pass 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.

Robert Bindar made changes - 2021-03-01 11:58

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:

{code:sql}SELECT printf('%s %s %s', first_name, last_name, job_title) from employees;{code}

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

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

Also this one has the advantage of being able to pass 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.

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:

{code:sql}SELECT printf('%s %s %s', first_name, last_name, job_title) from employees;{code}

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

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

Also this one has the advantage of being able to pass formatting options within the curly braces:
{code:sql}
# 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;
{code}

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.

Robert Bindar made changes - 2021-03-01 11:59

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:

{code:sql}SELECT printf('%s %s %s', first_name, last_name, job_title) from employees;{code}

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

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

Also this one has the advantage of being able to pass formatting options within the curly braces:
{code:sql}
# 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;
{code}

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.

Formatting more complex strings in a SELECT statement can get awkward when there are many {noformat}concat(){noformat}, {noformat}format(){noformat}, 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:

{code:sql}SELECT printf('%s %s %s', first_name, last_name, job_title) from employees;{code}

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

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

Also this one has the advantage of being able to pass formatting options within the curly braces:
{code:sql}
# 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;
{code}

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.

Robert Bindar made changes - 2021-03-01 12:00

Description

Formatting more complex strings in a SELECT statement can get awkward when there are many {noformat}concat(){noformat}, {noformat}format(){noformat}, 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:

{code:sql}SELECT printf('%s %s %s', first_name, last_name, job_title) from employees;{code}

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

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

Also this one has the advantage of being able to pass formatting options within the curly braces:
{code:sql}
# 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;
{code}

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.

Formatting more complex strings in a SELECT statement can get awkward when there are many {{monospaced}}concat(){{monospaced}}, {{monospaced}}format(){{monospaced}}, 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:

{code:sql}SELECT printf('%s %s %s', first_name, last_name, job_title) from employees;{code}

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

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

Also this one has the advantage of being able to pass formatting options within the curly braces:
{code:sql}
# 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;
{code}

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.

Robert Bindar made changes - 2021-03-01 12:00

Description

Formatting more complex strings in a SELECT statement can get awkward when there are many {{monospaced}}concat(){{monospaced}}, {{monospaced}}format(){{monospaced}}, 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:

{code:sql}SELECT printf('%s %s %s', first_name, last_name, job_title) from employees;{code}

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

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

Also this one has the advantage of being able to pass formatting options within the curly braces:
{code:sql}
# 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;
{code}

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.

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:

{code:sql}SELECT printf('%s %s %s', first_name, last_name, job_title) from employees;{code}

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

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

Also this one has the advantage of being able to pass formatting options within the curly braces:
{code:sql}
# 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;
{code}

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.

Robert Bindar made changes - 2021-03-01 12:01

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:

{code:sql}SELECT printf('%s %s %s', first_name, last_name, job_title) from employees;{code}

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

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

Also this one has the advantage of being able to pass formatting options within the curly braces:
{code:sql}
# 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;
{code}

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.

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:

{code:sql}SELECT printf('%s %s %s', first_name, last_name, job_title) from employees;{code}

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

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

Also this one has the advantage of being able to pass formatting options within the curly braces:
{code:sql}
# 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;
{code}

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.

Sergei Golubchik made changes - 2021-03-01 13:38

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:

{code:sql}SELECT printf('%s %s %s', first_name, last_name, job_title) from employees;{code}

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

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

Also this one has the advantage of being able to pass formatting options within the curly braces:
{code:sql}
# 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;
{code}

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.

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:

{code:sql}SELECT printf('%s %s, %s', first_name, last_name, job_title) from employees;{code}

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

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

Also this one has the advantage of being able to pass formatting options within the curly braces:
{code:sql}
# 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;
{code}

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.

Sergei Golubchik added a comment - 2021-03-01 13:39

is there anything comparable in other DBMS-es?

Sergei Golubchik added a comment - 2021-03-01 13:39 is there anything comparable in other DBMS-es?

Sergei Golubchik made changes - 2021-03-01 13:39

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:

{code:sql}SELECT printf('%s %s, %s', first_name, last_name, job_title) from employees;{code}

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

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

Also this one has the advantage of being able to pass formatting options within the curly braces:
{code:sql}
# 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;
{code}

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.

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:

{code:sql}SELECT printf('%s %s, %s', first_name, last_name, job_title) from employees;{code}

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

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

Also this one has the advantage of being able to pass formatting options within the curly braces:
{code:sql}
-- 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;
{code}

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.

Sergei Golubchik made changes - 2021-03-01 13:41

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:

{code:sql}SELECT printf('%s %s, %s', first_name, last_name, job_title) from employees;{code}

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

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

Also this one has the advantage of being able to pass formatting options within the curly braces:
{code:sql}
-- 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;
{code}

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.

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:

{code:sql}SELECT printf('%s %s, %s', first_name, last_name, job_title) from employees;{code}

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 know the datatype of each field used in the formatting scheme:

{code:sql}SELECT sformat('arg1: {}, arg2: {}', col1, col2) from table;{code}
In that syntax one passes formatting options within the curly braces:
{code:sql}
-- 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;
{code}

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.

Robert Bindar made changes - 2021-03-02 09:28

Fix Version/s		10.7 [ 24805 ]
Fix Version/s	10.6 [ 24028 ]

Sergei Golubchik added a comment - 2021-03-27 21:14

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

Sergei Golubchik added a comment - 2021-03-27 21:14 Didn't find in Oracle, DB2, or SQL Server. PostgreSQL has https://www.postgresql.org/docs/9.3/functions-string.html#FUNCTIONS-STRING-FORMAT

Sergei Golubchik made changes - 2021-04-09 11:12

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:

{code:sql}SELECT printf('%s %s, %s', first_name, last_name, job_title) from employees;{code}

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 know the datatype of each field used in the formatting scheme:

{code:sql}SELECT sformat('arg1: {}, arg2: {}', col1, col2) from table;{code}
In that syntax one passes formatting options within the curly braces:
{code:sql}
-- 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;
{code}

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.

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:

{code:sql}SELECT printf('%s %s, %s', first_name, last_name, job_title) from employees;{code}

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:

{code:sql}SELECT sformat('arg1: {}, arg2: {}', col1, col2) from table;{code}
In that syntax one passes formatting options within the curly braces:
{code:sql}
-- 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;
{code}

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.

Sergei Golubchik added a comment - 2021-05-12 07:39

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.

Sergei Golubchik added a comment - 2021-05-12 07:39 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.

Vicențiu Ciorbaru made changes - 2021-05-25 07:54

Assignee

Robert Bindar [ robertbindar ]

Vicențiu Ciorbaru [ cvicentiu ]

Sergei Golubchik made changes - 2021-06-14 15:03

Remote Link

This issue links to "PostgreSQL (Web Link)" [ 31357 ]

Sergei Golubchik made changes - 2021-06-14 15:05

Remote Link

This issue links to "Python 3 (Web Link)" [ 31358 ]

Sergei Golubchik made changes - 2021-06-14 15:06

Remote Link

This issue links to "Rust (Web Link)" [ 31359 ]

Sergei Golubchik made changes - 2021-06-14 15:07

Remote Link

This issue links to "C printf (Web Link)" [ 31360 ]

Sergei Golubchik made changes - 2021-06-29 07:45

Remote Link

This issue links to "{fmt} — implementation of C++20 std::format (Web Link)" [ 31408 ]

Sergei Golubchik made changes - 2021-08-08 15:21

Link

This issue blocks MDEV-19784 [ MDEV-19784 ]

Sergei Golubchik made changes - 2021-09-04 12:40

Priority

Minor [ 4 ]

Critical [ 2 ]

Sergei Golubchik made changes - 2021-09-04 17:00

Assignee

Vicențiu Ciorbaru [ cvicentiu ]

Sergei Golubchik [ serg ]

Sergei Golubchik made changes - 2021-09-04 17:00

Status

Open [ 1 ]

Confirmed [ 10101 ]

Sergei Golubchik made changes - 2021-09-04 17:00

Status

Confirmed [ 10101 ]

In Review [ 10002 ]

Sergei Golubchik made changes - 2021-09-05 19:48

Link

This issue blocks TODO-3121 [ TODO-3121 ]

Sergei Golubchik made changes - 2021-09-05 19:50

Assignee	Sergei Golubchik [ serg ]	Elena Stepanova [ elenst ]
Status	In Review [ 10002 ]	Stalled [ 10000 ]

Sergei Golubchik made changes - 2021-09-05 19:50

Link

This issue relates to TODO-3118 [ TODO-3118 ]

Sergei Golubchik added a comment - 2021-09-05 19:56 - edited

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

Sergei Golubchik added a comment - 2021-09-05 19:56 - edited 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

Roel Van de Paar made changes - 2021-09-18 03:24

Assignee

Elena Stepanova [ elenst ]

Roel Van de Paar [ roel ]

Roel Van de Paar added a comment - 2021-09-18 03:30

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

Roel Van de Paar added a comment - 2021-09-18 03:30 serg Thank you for the solid MTR testcase main/func_sformat.test

Roel Van de Paar made changes - 2021-09-18 04:30

Status

Stalled [ 10000 ]

In Progress [ 3 ]

Roel Van de Paar added a comment - 2021-09-18 05:03

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).

Roel Van de Paar added a comment - 2021-09-18 05:03 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).

Roel Van de Paar added a comment - 2021-09-18 05:05 - edited

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

Roel Van de Paar added a comment - 2021-09-18 05:05 - edited 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

Roel Van de Paar added a comment - 2021-09-18 05:10 - edited

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.

Roel Van de Paar added a comment - 2021-09-18 05:10 - edited 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.

Roel Van de Paar made changes - 2021-09-18 07:46

Status

In Progress [ 3 ]

Stalled [ 10000 ]

Roel Van de Paar added a comment - 2021-09-18 07:46

I consider the feature OK to push.

Roel Van de Paar added a comment - 2021-09-18 07:46 I consider the feature OK to push.

Roel Van de Paar made changes - 2021-09-18 07:47

Assignee

Roel Van de Paar [ roel ]

Sergei Golubchik [ serg ]

Sergei Golubchik added a comment - 2021-09-18 10:31 - edited

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

Sergei Golubchik added a comment - 2021-09-18 10:31 - edited 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

Sergei Golubchik made changes - 2021-09-18 10:46

Assignee

Sergei Golubchik [ serg ]

Roel Van de Paar [ roel ]

Roel Van de Paar added a comment - 2021-09-20 06:58

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.

Roel Van de Paar added a comment - 2021-09-20 06:58 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.

Roel Van de Paar made changes - 2021-09-20 07:02

Link

This issue causes ~~MDEV-26646~~ [ ~~MDEV-26646~~ ]

Roel Van de Paar added a comment - 2021-09-20 07:02 - edited

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

Roel Van de Paar added a comment - 2021-09-20 07:02 - edited 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

Roel Van de Paar made changes - 2021-09-20 07:56

Link

This issue relates to MDEV-26648 [ MDEV-26648 ]

Roel Van de Paar made changes - 2021-09-20 08:08

Link

This issue relates to MDEV-26649 [ MDEV-26649 ]

Sergei Golubchik made changes - 2021-09-20 18:45

Link

This issue causes MDEV-26648 [ MDEV-26648 ]

Sergei Golubchik made changes - 2021-09-20 18:45

Link

This issue relates to MDEV-26648 [ MDEV-26648 ]

Sergei Golubchik made changes - 2021-09-20 18:45

Link

This issue causes MDEV-26649 [ MDEV-26649 ]

Sergei Golubchik made changes - 2021-09-20 18:45

Link

This issue relates to MDEV-26649 [ MDEV-26649 ]

Sergei Golubchik made changes - 2021-09-20 18:47

Link

This issue causes MDEV-26653 [ MDEV-26653 ]

Roel Van de Paar added a comment - 2021-09-25 06:42 - edited

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

Roel Van de Paar added a comment - 2021-09-25 06:42 - edited 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

Roel Van de Paar added a comment - 2021-09-25 06:50

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)

Roel Van de Paar added a comment - 2021-09-25 06:50 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)

Sergei Golubchik added a comment - 2021-09-25 14:03

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.

Sergei Golubchik added a comment - 2021-09-25 14:03 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.

Roel Van de Paar added a comment - 2021-09-26 20:00

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

Roel Van de Paar added a comment - 2021-09-26 20:00 Logged as MDEV-26691 SFORMAT: Pass down FLOAT as FLOAT, without upcast to DOUBLE

Roel Van de Paar made changes - 2021-09-26 20:00

Link

This issue causes ~~MDEV-26691~~ [ ~~MDEV-26691~~ ]

Roel Van de Paar added a comment - 2021-09-30 00:03 - 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 Van de Paar added a comment - 2021-09-30 00:03 - 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 Van de Paar added a comment - 2021-09-30 00:26

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 Van de Paar added a comment - 2021-09-30 00:26 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 Van de Paar made changes - 2021-09-30 00:31

Assignee

Roel Van de Paar [ roel ]

Sergei Golubchik [ serg ]

Sergei Golubchik made changes - 2021-10-12 11:51

Fix Version/s		10.7.1 [ 26120 ]
Fix Version/s	10.7 [ 24805 ]
Resolution		Fixed [ 1 ]
Status	Stalled [ 10000 ]	Closed [ 6 ]

Sergei Golubchik made changes - 2021-12-06 21:24

Workflow

MariaDB v3 [ 119616 ]

MariaDB v4 [ 134400 ]

Federico Razzoli added a comment - 2021-12-27 02:03 - 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.

Federico Razzoli added a comment - 2021-12-27 02:03 - 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.

Sergei Golubchik added a comment - 2021-12-27 13:16

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.

Sergei Golubchik added a comment - 2021-12-27 13:16 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.

Federico Razzoli added a comment - 2021-12-27 14:12

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.

Federico Razzoli added a comment - 2021-12-27 14:12 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.

Ralf Gebhardt made changes - 2023-03-21 11:00

Labels

gsoc21

Preview_10.7 gsoc21

Elena Stepanova made changes - 2023-04-07 18:33

Link

This issue relates to MDEV-31024 [ MDEV-31024 ]

MariaDB Server

Custom formatting of strings in MariaDB queries

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration