mirror of
https://github.com/MariaDB/server.git
synced 2025-11-03 20:36:16 +01:00
487 lines
16 KiB
Text
487 lines
16 KiB
Text
#
|
|
# MDEV-25015 Custom formatting of strings in MariaDB queries
|
|
#
|
|
#
|
|
# Normal Test Cases
|
|
#
|
|
select sformat('string test');
|
|
sformat('string test')
|
|
string test
|
|
select sformat(0);
|
|
sformat(0)
|
|
0
|
|
select sformat('C');
|
|
sformat('C')
|
|
C
|
|
select sformat(-4.2);
|
|
sformat(-4.2)
|
|
-4.2
|
|
select sformat(5, 5, 5);
|
|
sformat(5, 5, 5)
|
|
5
|
|
select sformat('{} {}', 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
|
|
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
|
|
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
|
|
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
|
|
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0) as x;
|
|
x
|
|
0 0
|
|
select sformat('{{{}}}', 0);
|
|
sformat('{{{}}}', 0)
|
|
{0}
|
|
select sformat('{{{}{{', 0);
|
|
sformat('{{{}{{', 0)
|
|
{0{
|
|
select sformat('{{{{{}{{', 'param1');
|
|
sformat('{{{{{}{{', 'param1')
|
|
{{param1{
|
|
select sformat(' {{ {{ {} {{ ', 'param1');
|
|
sformat(' {{ {{ {} {{ ', 'param1')
|
|
{ { param1 {
|
|
select sformat(' {{ {} {}', 'param1', 'param2');
|
|
sformat(' {{ {} {}', 'param1', 'param2')
|
|
{ param1 param2
|
|
select sformat('A{}C{}E{}', 'B', 'D', 'F');
|
|
sformat('A{}C{}E{}', 'B', 'D', 'F')
|
|
ABCDEF
|
|
select sformat('{} {}', FALSE, TRUE);
|
|
sformat('{} {}', FALSE, TRUE)
|
|
0 1
|
|
select sformat('Add € != {} != {}?', '$', '£');
|
|
sformat('Add € != {} != {}?', '$', '£')
|
|
Add € != $ != £?
|
|
select sformat('Check {} != {} != {}?', '€', '$', '£');
|
|
sformat('Check {} != {} != {}?', '€', '$', '£')
|
|
Check € != $ != £?
|
|
select sformat('{}{}{}', 1, 2, 3);
|
|
sformat('{}{}{}', 1, 2, 3)
|
|
123
|
|
select sformat('Float {} Boolean {} Number {}', 3.14159, True, -50);
|
|
sformat('Float {} Boolean {} Number {}', 3.14159, True, -50)
|
|
Float 3.14159 Boolean 1 Number -50
|
|
select sformat('SUM {} + {} = {}', 2, 3, 2+3);
|
|
sformat('SUM {} + {} = {}', 2, 3, 2+3)
|
|
SUM 2 + 3 = 5
|
|
select sformat('Numbers {} {} {}', 1, 1.11, 1.111);
|
|
sformat('Numbers {} {} {}', 1, 1.11, 1.111)
|
|
Numbers 1 1.11 1.111
|
|
select sformat('what {} is {}?', 'time', 'it');
|
|
sformat('what {} is {}?', 'time', 'it')
|
|
what time is it?
|
|
select sformat('{} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {}
|
|
{} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {}
|
|
{} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {}
|
|
{} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {}', 1, 2, 3, 4, 5, 6,
|
|
7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32,
|
|
33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57,
|
|
58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82,
|
|
83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99, 100, 101, 102, 103, 104, 105,
|
|
106, 107, 108, 109, 110, 111, 112, 113, 114, 115, 116, 117, 118, 119, 120) as x;
|
|
x
|
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28
|
|
29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61
|
|
62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94
|
|
95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120
|
|
select sformat('{} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {}
|
|
{} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {}
|
|
{} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {}
|
|
{} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {}', '1', '2', '3 ',
|
|
'4', '5', '6', '7', '8', '9', '10', '11', '12', '13', '14', '15', '16', '17', '18', '19', '20',
|
|
'21', '22', '23', '24', '25', '26', '27', '28', '29', '30', '31', '32', '33', '34', '35', '36',
|
|
'37', '38', '39', '40', '41', '42', '43', '44', '45', '46', '47', '48', '49', '50', '51', '52',
|
|
'53', '54', '55', '56', '57', '58', '59', '60', '61', '62', '63', '64', '65', '66', '67', '68',
|
|
'69', '70', '71', '72', '73', '74', '75', '76', '77', '78', '79', '80', '81', '82', '83', '84',
|
|
'85', '86', '87', '88', '89', '90', '91', '92', '93', '94', '95', '96', '97', '98', '99', '100',
|
|
'101', '102', '103', '104', '105', '106', '107', '108', '109', '110', '111', '112', '113', '114',
|
|
'115', '116', '117', '118', '119', '120') as x;
|
|
x
|
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28
|
|
29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61
|
|
62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94
|
|
95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120
|
|
#
|
|
# Error Test Cases
|
|
#
|
|
select sformat('R={ }', 42);
|
|
sformat('R={ }', 42)
|
|
NULL
|
|
Warnings:
|
|
Warning 4183 SFORMAT error: invalid format string
|
|
select sformat(NULL, 'Null', 'Test');
|
|
sformat(NULL, 'Null', 'Test')
|
|
NULL
|
|
select sformat('Null Test {} {} ', 0, NULL);
|
|
sformat('Null Test {} {} ', 0, NULL)
|
|
NULL
|
|
select sformat(NULL);
|
|
sformat(NULL)
|
|
NULL
|
|
select sformat('My { Test');
|
|
sformat('My { Test')
|
|
NULL
|
|
Warnings:
|
|
Warning 4183 SFORMAT error: invalid format string
|
|
select sformat('Test { {');
|
|
sformat('Test { {')
|
|
NULL
|
|
Warnings:
|
|
Warning 4183 SFORMAT error: invalid format string
|
|
select sformat('A{}C{}E{}', 'B', 'D');
|
|
sformat('A{}C{}E{}', 'B', 'D')
|
|
NULL
|
|
Warnings:
|
|
Warning 4183 SFORMAT error: argument not found
|
|
select sformat();
|
|
ERROR 42000: Incorrect parameter count in the call to native function 'sformat'
|
|
#
|
|
# Table Test Cases
|
|
#
|
|
create table t1 (param1 text, param2 text, param3 int);
|
|
insert into t1 values ('string: {}, number: {}', 'xyz', 123),
|
|
('something {} went {} wrong {}', 'foo', 0),
|
|
('Test case {} -> {}', 'Print', -32);
|
|
select sformat(param1, param2, param3) from t1;
|
|
sformat(param1, param2, param3)
|
|
string: xyz, number: 123
|
|
NULL
|
|
Test case Print -> -32
|
|
Warnings:
|
|
Warning 4183 SFORMAT error: argument not found
|
|
drop table t1;
|
|
create table t2 (param1 FLOAT, param2 SMALLINT, param3 CHAR, param4 DATE);
|
|
insert into t2 values (0.0025, 25, 'A', DATE('2020-06-29')),
|
|
(0.0005, 5, 'B', DATE('2020-6-29')),
|
|
(5.5555, -5, 'C', DATE('200629')),
|
|
(-9, -9, 'D', DATE('20*06*29'));
|
|
select sformat('p1 {:.4f} p2 {} p3 {} p4 {}', param1, param2, param3, param4) x from t2;
|
|
x
|
|
p1 0.0025 p2 25 p3 A p4 2020-06-29
|
|
p1 0.0005 p2 5 p3 B p4 2020-06-29
|
|
p1 5.5555 p2 -5 p3 C p4 2020-06-29
|
|
p1 -9.0000 p2 -9 p3 D p4 2020-06-29
|
|
drop table t2;
|
|
set names utf8;
|
|
create table t3 (format_str text character set 'latin1',
|
|
first_param text character set 'utf8',
|
|
second_param text character set 'latin1');
|
|
insert into t3 values ('test 1 {} {}', UNHEX('C3A5'), UNHEX('E5'));
|
|
select sformat(format_str, first_param, second_param) from t3;
|
|
sformat(format_str, first_param, second_param)
|
|
test 1 å å
|
|
select HEX(sformat(format_str, first_param, second_param)) from t3;
|
|
HEX(sformat(format_str, first_param, second_param))
|
|
74657374203120C3A520C3A5
|
|
drop table t3;
|
|
set names latin1;
|
|
create table t4 (p1 bit(8), p2 boolean, p3 DECIMAL, p4 TIMESTAMP);
|
|
insert into t4 values (42, TRUE, 42, '2021-08-18 16:50:07'),
|
|
(24, FALSE, 24, '0000-00-00 00:00:00');
|
|
select sformat('{}: {} {} {} {}', 'Data', p1, p2, p3, p4) from t4;
|
|
sformat('{}: {} {} {} {}', 'Data', p1, p2, p3, p4)
|
|
Data: 42 1 42 2021-08-18 16:50:07
|
|
Data: 24 0 24 0000-00-00 00:00:00
|
|
drop table t4;
|
|
set names utf8;
|
|
create table t5 (param text character SET utf8 COLLATE utf8_general_ci);
|
|
insert into t5 values (UNHEX('C3A5')),(UNHEX('C5BB')),(UNHEX('e0b1bb')),
|
|
(UNHEX('C38A')), (NULL);
|
|
select sformat('{}', param) from t5;
|
|
sformat('{}', param)
|
|
å
|
|
Ż
|
|
౻
|
|
Ê
|
|
NULL
|
|
select HEX(sformat('{}', param)) from t5;
|
|
HEX(sformat('{}', param))
|
|
C3A5
|
|
C5BB
|
|
E0B1BB
|
|
C38A
|
|
NULL
|
|
drop table t5;
|
|
set names latin1;
|
|
#
|
|
# Format Test Cases
|
|
#
|
|
select sformat('Num {:L}', 13800000000);
|
|
sformat('Num {:L}', 13800000000)
|
|
Num 13,800,000,000
|
|
select sformat('Num [{:20}]', 42);
|
|
sformat('Num [{:20}]', 42)
|
|
Num [ 42]
|
|
select sformat('Number: {:*^{}}', 4, 5);
|
|
sformat('Number: {:*^{}}', 4, 5)
|
|
Number: **4**
|
|
select sformat('{:02} - {:02} - {:02}', 1, 2, 3);
|
|
sformat('{:02} - {:02} - {:02}', 1, 2, 3)
|
|
01 - 02 - 03
|
|
select sformat('Character {:c}', 104);
|
|
sformat('Character {:c}', 104)
|
|
Character h
|
|
select sformat('String {:s}', 'hello');
|
|
sformat('String {:s}', 'hello')
|
|
String hello
|
|
select sformat('Large {0:+010.4g}', 392.64);
|
|
sformat('Large {0:+010.4g}', 392.64)
|
|
Large +0000392.6
|
|
select sformat('Large {:g}', 392.65);
|
|
sformat('Large {:g}', 392.65)
|
|
Large 392.65
|
|
select sformat('Float {:.2f}', 42.0);
|
|
sformat('Float {:.2f}', 42.0)
|
|
Float 42.00
|
|
select sformat('Float {:f}', 42.0);
|
|
sformat('Float {:f}', 42.0)
|
|
Float 42.000000
|
|
select sformat('Number {:d}', 42);
|
|
sformat('Number {:d}', 42)
|
|
Number 42
|
|
select sformat('Number {:{}}', 5, 5);
|
|
sformat('Number {:{}}', 5, 5)
|
|
Number 5
|
|
select sformat('Number [{:10}]', 9999);
|
|
sformat('Number [{:10}]', 9999)
|
|
Number [ 9999]
|
|
select sformat('Number {:.3}', 3.1416);
|
|
sformat('Number {:.3}', 3.1416)
|
|
Number 3.14
|
|
select sformat('int: {0:d}; hex: {0:x}; oct: {0:o}', 42);
|
|
sformat('int: {0:d}; hex: {0:x}; oct: {0:o}', 42)
|
|
int: 42; hex: 2a; oct: 52
|
|
select sformat('int: {0:d}; hex: {0:#x}; oct: {0:#o}', 42);
|
|
sformat('int: {0:d}; hex: {0:#x}; oct: {0:#o}', 42)
|
|
int: 42; hex: 0x2a; oct: 052
|
|
select sformat('The Hexadecimal version of {0} is {0:X}', 255);
|
|
sformat('The Hexadecimal version of {0} is {0:X}', 255)
|
|
The Hexadecimal version of 255 is FF
|
|
select sformat('The Hexadecimal version of {0} is {0:x}', 255);
|
|
sformat('The Hexadecimal version of {0} is {0:x}', 255)
|
|
The Hexadecimal version of 255 is ff
|
|
select sformat('The octal version of {0} is {0:o}', 10);
|
|
sformat('The octal version of {0} is {0:o}', 10)
|
|
The octal version of 10 is 12
|
|
select sformat('The binary version of {0} is {0:b}', 5);
|
|
sformat('The binary version of {0} is {0:b}', 5)
|
|
The binary version of 5 is 101
|
|
select sformat('{:+f}; {:+f}', 3.14, -3.14);
|
|
sformat('{:+f}; {:+f}', 3.14, -3.14)
|
|
+3.140000; -3.140000
|
|
select sformat('{: f}; {: f}', 3.14, -3.14);
|
|
sformat('{: f}; {: f}', 3.14, -3.14)
|
|
3.140000; -3.140000
|
|
select sformat('{:-f}; {:-f}', 3.14, -3.14);
|
|
sformat('{:-f}; {:-f}', 3.14, -3.14)
|
|
3.140000; -3.140000
|
|
select sformat('The temperature is between {: } and {: } degrees celsius.', -3, 7) x;
|
|
x
|
|
The temperature is between -3 and 7 degrees celsius.
|
|
select sformat('The temperature is between {:-} and {:-} degrees celsius.', -3, 7) x;
|
|
x
|
|
The temperature is between -3 and 7 degrees celsius.
|
|
select sformat('The temperature is between {:+} and {:+} degrees celsius.', -3, 7) x;
|
|
x
|
|
The temperature is between -3 and +7 degrees celsius.
|
|
select sformat('We have {:<8} chickens.', 49);
|
|
sformat('We have {:<8} chickens.', 49)
|
|
We have 49 chickens.
|
|
select sformat('Center alimgn [{:*^10}]', 'data');
|
|
sformat('Center alimgn [{:*^10}]', 'data')
|
|
Center alimgn [***data***]
|
|
select sformat('Center aling [{:^10}].', 'data');
|
|
sformat('Center aling [{:^10}].', 'data')
|
|
Center aling [ data ].
|
|
select sformat('Right aling [{:>10}].', 'data');
|
|
sformat('Right aling [{:>10}].', 'data')
|
|
Right aling [ data].
|
|
select sformat('Left align [{:<10}].', 'data');
|
|
sformat('Left align [{:<10}].', 'data')
|
|
Left align [data ].
|
|
select sformat('{0}{1}{0}', 'abra', 'cad');
|
|
sformat('{0}{1}{0}', 'abra', 'cad')
|
|
abracadabra
|
|
select sformat('Change Order {1} {0}', 'second', 'first');
|
|
sformat('Change Order {1} {0}', 'second', 'first')
|
|
Change Order first second
|
|
#
|
|
# Failed Format Test Cases
|
|
#
|
|
select sformat('Test {:c}', 'word');
|
|
sformat('Test {:c}', 'word')
|
|
NULL
|
|
Warnings:
|
|
Warning 4183 SFORMAT error: invalid type specifier
|
|
select sformat('Test {one} {two} {three}', 1, 2, 3);
|
|
sformat('Test {one} {two} {three}', 1, 2, 3)
|
|
NULL
|
|
Warnings:
|
|
Warning 4183 SFORMAT error: argument not found
|
|
select sformat('Number {:{<}', 8);
|
|
sformat('Number {:{<}', 8)
|
|
NULL
|
|
Warnings:
|
|
Warning 4183 SFORMAT error: invalid fill character '{'
|
|
select sformat('Number {:10000000000}', 5);
|
|
sformat('Number {:10000000000}', 5)
|
|
NULL
|
|
Warnings:
|
|
Warning 4183 SFORMAT error: number is too big
|
|
select sformat('1={1} 2={2} 0={0}', 0, 1);
|
|
sformat('1={1} 2={2} 0={0}', 0, 1)
|
|
NULL
|
|
Warnings:
|
|
Warning 4183 SFORMAT error: argument not found
|
|
select sformat('Number {:.2d}', 42);
|
|
sformat('Number {:.2d}', 42)
|
|
NULL
|
|
Warnings:
|
|
Warning 4183 SFORMAT error: precision not allowed for this argument type
|
|
select sformat('You scored {:.0%}', 0.25);
|
|
sformat('You scored {:.0%}', 0.25)
|
|
NULL
|
|
Warnings:
|
|
Warning 4183 SFORMAT error: invalid type specifier
|
|
select sformat('You scored {:%}', 0.25);
|
|
sformat('You scored {:%}', 0.25)
|
|
NULL
|
|
Warnings:
|
|
Warning 4183 SFORMAT error: invalid type specifier
|
|
select sformat('The price is {:f} dollars.', 45);
|
|
sformat('The price is {:f} dollars.', 45)
|
|
NULL
|
|
Warnings:
|
|
Warning 4183 SFORMAT error: invalid type specifier
|
|
select sformat('The price is {:.2f} dollars.', 45);
|
|
sformat('The price is {:.2f} dollars.', 45)
|
|
NULL
|
|
Warnings:
|
|
Warning 4183 SFORMAT error: precision not allowed for this argument type
|
|
select sformat('We have {:E} chickens.', 5);
|
|
sformat('We have {:E} chickens.', 5)
|
|
NULL
|
|
Warnings:
|
|
Warning 4183 SFORMAT error: invalid type specifier
|
|
select sformat('We have {:e} chickens.', 5);
|
|
sformat('We have {:e} chickens.', 5)
|
|
NULL
|
|
Warnings:
|
|
Warning 4183 SFORMAT error: invalid type specifier
|
|
select sformat('The universe is {:,} years old.', 13800000000);
|
|
sformat('The universe is {:,} years old.', 13800000000)
|
|
NULL
|
|
Warnings:
|
|
Warning 4183 SFORMAT error: invalid type specifier
|
|
select sformat('The universe is {:_} years old.', 13800000000);
|
|
sformat('The universe is {:_} years old.', 13800000000)
|
|
NULL
|
|
Warnings:
|
|
Warning 4183 SFORMAT error: invalid type specifier
|
|
select sformat('String {:-}', 'hello');
|
|
sformat('String {:-}', 'hello')
|
|
NULL
|
|
Warnings:
|
|
Warning 4183 SFORMAT error: format specifier requires numeric argument
|
|
#
|
|
# Table Format Test Cases
|
|
#
|
|
create table t1 (pformat text, data1 FLOAT, data2 INT,
|
|
data3 VARCHAR(30));
|
|
insert into t1 values ('{:.2f} {: } |{:*^15}', 1.11, 1, 'example I'),
|
|
('{:.1f} {:-} |{:^15}', -2.22, -2, 'example II'),
|
|
('{:.2f} {:+} |{:<15}', 3.33, 3, 'example III'),
|
|
('{:.4f} {:d} |{:^15}', -4.44, -4, 'example IV');
|
|
select sformat(pformat, data1, data2, data3) from t1;
|
|
sformat(pformat, data1, data2, data3)
|
|
1.11 1 |***example I***
|
|
-2.2 -2 | example II
|
|
3.33 +3 |example III
|
|
-4.4400 -4 | example IV
|
|
drop table t1;
|
|
create table t2 (a double);
|
|
insert into t2 values (3.14159265358979323846);
|
|
select a, sformat('{:.15f}', a) from t2;
|
|
a sformat('{:.15f}', a)
|
|
3.141592653589793 3.141592653589793
|
|
drop table t2;
|
|
#
|
|
# Unsupported/disabled features
|
|
#
|
|
select sformat('{:p}', '50');
|
|
sformat('{:p}', '50')
|
|
NULL
|
|
Warnings:
|
|
Warning 4183 SFORMAT error: invalid type specifier
|
|
#
|
|
# ucs2 test
|
|
#
|
|
select sformat('={}=', _ucs2 x'006100620063');
|
|
sformat('={}=', _ucs2 x'006100620063')
|
|
=abc=
|
|
set names utf8;
|
|
select sformat(_ucs2 x'003D007B007D003D', _ucs2 x'0442043504410442');
|
|
sformat(_ucs2 x'003D007B007D003D', _ucs2 x'0442043504410442')
|
|
=тест=
|
|
select hex(sformat(_ucs2 x'003D007B007D003D', _ucs2 x'0442043504410442')) x;
|
|
x
|
|
003D0442043504410442003D
|
|
create table t1 as select sformat(_ucs2 x'003D007B007D003D', _ucs2 x'0442043504410442') as x;
|
|
show create table t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`x` longtext CHARACTER SET ucs2 COLLATE ucs2_general_ci DEFAULT NULL
|
|
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
|
|
drop table t1;
|
|
set names latin1;
|
|
#
|
|
# ps parameters
|
|
#
|
|
prepare s from 'select sformat("={:d}=", ?)';
|
|
execute s using 100;
|
|
sformat("={:d}=", ?)
|
|
=100=
|
|
execute s using 'abc';
|
|
sformat("={:d}=", ?)
|
|
NULL
|
|
Warnings:
|
|
Warning 4183 SFORMAT error: invalid type specifier
|
|
#
|
|
# MDEV-26691 SFORMAT: Pass down FLOAT as FLOAT, without upcast to DOUBLE
|
|
#
|
|
select sformat('{}', cast(1.1 as float));
|
|
sformat('{}', cast(1.1 as float))
|
|
1.1
|
|
#
|
|
# MDEV-26646 SFORMAT Does not allow @variable use
|
|
#
|
|
set names utf8;
|
|
set @a=3.14;
|
|
select sformat('{:f}', @a);
|
|
sformat('{:f}', @a)
|
|
3.140000
|
|
set names latin1;
|
|
#
|
|
# End of 10.7 tests
|
|
#
|
|
#
|
|
# Start of 10.8 tests
|
|
#
|
|
#
|
|
# MDEV-29646 sformat('Num [{:20}]', 42) gives incorrect result in view
|
|
#
|
|
create view v1 as select sformat('Num [{:20}]', 42);
|
|
select * from v1;
|
|
sformat('Num [{:20}]', 42)
|
|
Num [ 42]
|
|
drop view v1;
|
|
create view v1 as SELECT sformat('Square root of [{:d}] is [{:.20}]', 2, sqrt(2));
|
|
select * from v1;
|
|
sformat('Square root of [{:d}] is [{:.20}]', 2, sqrt(2))
|
|
Square root of [2] is [1.4142135623730951455]
|
|
drop view v1;
|
|
create table t1 (a text, b int, c text);
|
|
insert t1 values ('[{} -> {}]', 10, '{}'), ('[{:20} <- {}]', 1, '{:30}');
|
|
select sformat(a,b,c) from t1;
|
|
sformat(a,b,c)
|
|
[10 -> {}]
|
|
[ 1 <- {:30}]
|
|
drop table t1;
|