Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commite87d496

Browse files
committed
Prevent to_number() from losing data when template doesn't match exactly.
Non-data template patterns would consume characters whether or not thosecharacters were what the pattern expected, for exampleSELECT TO_NUMBER('1234', '9,999');produced 134 because the '2' got eaten by the comma pattern. This seemsundesirable, not least because it doesn't happen in Oracle. For the ','and 'G' template patterns, we can fix this by consuming characters onlyif they match what the pattern would output. For non-data patterns suchas 'L' and 'TH', it seems impractical to tighten things up to the point ofconsuming only exact matches to what the pattern would output; but we canimprove matters quite a lot by redefining the behavior as "consume onlycharacters that aren't digits, signs, decimal point, or comma".Also, fix it so that the behavior is to consume the number of *characters*the pattern would output, not the number of *bytes*. The old coding woulddo surprising things with non-ASCII currency symbols, for example. (Itwould be good to apply that rule for literal text as well, but this commitonly fixes it for non-data patterns.)Oliver Ford, reviewed by Thomas Munro and Nathan Wagner, and whacked arounda bit more by meDiscussion:https://postgr.es/m/CAGMVOdvpbMqPf9XWNzOwBpzJfErkydr_fEGhmuDGa015z97mwg@mail.gmail.com
1 parentbe92769 commite87d496

File tree

4 files changed

+197
-32
lines changed

4 files changed

+197
-32
lines changed

‎doc/src/sgml/func.sgml

Lines changed: 22 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -5850,7 +5850,10 @@ SELECT regexp_match('abc01234xyz', '(?:(.*?)(\d+)(.*)){1,1}');
58505850
data based on the given value. Any text that is not a template pattern is
58515851
simply copied verbatim. Similarly, in an input template string (for the
58525852
other functions), template patterns identify the values to be supplied by
5853-
the input data string.
5853+
the input data string. If there are characters in the template string
5854+
that are not template patterns, the corresponding characters in the input
5855+
data string are simply skipped over (whether or not they are equal to the
5856+
template string characters).
58545857
</para>
58555858

58565859
<para>
@@ -6176,13 +6179,15 @@ SELECT regexp_match('abc01234xyz', '(?:(.*?)(\d+)(.*)){1,1}');
61766179
Ordinary text is allowed in <function>to_char</function>
61776180
templates and will be output literally. You can put a substring
61786181
in double quotes to force it to be interpreted as literal text
6179-
even if it containspattern key words. For example, in
6182+
even if it containstemplate patterns. For example, in
61806183
<literal>'"Hello Year "YYYY'</literal>, the <literal>YYYY</literal>
61816184
will be replaced by the year data, but the single <literal>Y</literal> in <literal>Year</literal>
6182-
will not be. In <function>to_date</function>, <function>to_number</function>,
6183-
and <function>to_timestamp</function>, double-quoted strings skip the number of
6184-
input characters contained in the string, e.g. <literal>"XX"</literal>
6185-
skips two input characters.
6185+
will not be.
6186+
In <function>to_date</function>, <function>to_number</function>,
6187+
and <function>to_timestamp</function>, literal text and double-quoted
6188+
strings result in skipping the number of characters contained in the
6189+
string; for example <literal>"XX"</literal> skips two input characters
6190+
(whether or not they are <literal>XX</literal>).
61866191
</para>
61876192
</listitem>
61886193

@@ -6483,6 +6488,17 @@ SELECT regexp_match('abc01234xyz', '(?:(.*?)(\d+)(.*)){1,1}');
64836488
</para>
64846489
</listitem>
64856490

6491+
<listitem>
6492+
<para>
6493+
In <function>to_number</function>, if non-data template patterns such
6494+
as <literal>L</literal> or <literal>TH</literal> are used, the
6495+
corresponding number of input characters are skipped, whether or not
6496+
they match the template pattern, unless they are data characters
6497+
(that is, digits, sign, decimal point, or comma). For
6498+
example, <literal>TH</literal> would skip two non-data characters.
6499+
</para>
6500+
</listitem>
6501+
64866502
<listitem>
64876503
<para>
64886504
<literal>V</literal> with <function>to_char</function>

‎src/backend/utils/adt/formatting.c

Lines changed: 108 additions & 26 deletions
Original file line numberDiff line numberDiff line change
@@ -988,7 +988,7 @@ static char *get_last_relevant_decnum(char *num);
988988
staticvoidNUM_numpart_from_char(NUMProc*Np,intid,intinput_len);
989989
staticvoidNUM_numpart_to_char(NUMProc*Np,intid);
990990
staticchar*NUM_processor(FormatNode*node,NUMDesc*Num,char*inout,
991-
char*number,intfrom_char_input_len,intto_char_out_pre_spaces,
991+
char*number,intinput_len,intto_char_out_pre_spaces,
992992
intsign,boolis_to_char,Oidcollid);
993993
staticDCHCacheEntry*DCH_cache_getnew(constchar*str);
994994
staticDCHCacheEntry*DCH_cache_search(constchar*str);
@@ -4232,6 +4232,14 @@ get_last_relevant_decnum(char *num)
42324232
returnresult;
42334233
}
42344234

4235+
/*
4236+
* These macros are used in NUM_processor() and its subsidiary routines.
4237+
* OVERLOAD_TEST: true if we've reached end of input string
4238+
* AMOUNT_TEST(s): true if at least s characters remain in string
4239+
*/
4240+
#defineOVERLOAD_TEST(Np->inout_p >= Np->inout + input_len)
4241+
#defineAMOUNT_TEST(s)(Np->inout_p <= Np->inout + (input_len - (s)))
4242+
42354243
/* ----------
42364244
* Number extraction for TO_NUMBER()
42374245
* ----------
@@ -4246,9 +4254,6 @@ NUM_numpart_from_char(NUMProc *Np, int id, int input_len)
42464254
(id==NUM_0||id==NUM_9) ?"NUM_0/9" :id==NUM_DEC ?"NUM_DEC" :"???");
42474255
#endif
42484256

4249-
#defineOVERLOAD_TEST(Np->inout_p >= Np->inout + input_len)
4250-
#defineAMOUNT_TEST(_s) (input_len-(Np->inout_p-Np->inout) >= _s)
4251-
42524257
if (OVERLOAD_TEST)
42534258
return;
42544259

@@ -4641,14 +4646,32 @@ NUM_numpart_to_char(NUMProc *Np, int id)
46414646
++Np->num_curr;
46424647
}
46434648

4649+
/*
4650+
* Skip over "n" input characters, but only if they aren't numeric data
4651+
*/
4652+
staticvoid
4653+
NUM_eat_non_data_chars(NUMProc*Np,intn,intinput_len)
4654+
{
4655+
while (n-->0)
4656+
{
4657+
if (OVERLOAD_TEST)
4658+
break;/* end of input */
4659+
if (strchr("0123456789.,+-",*Np->inout_p)!=NULL)
4660+
break;/* it's a data character */
4661+
Np->inout_p+=pg_mblen(Np->inout_p);
4662+
}
4663+
}
4664+
46444665
staticchar*
46454666
NUM_processor(FormatNode*node,NUMDesc*Num,char*inout,
4646-
char*number,intfrom_char_input_len,intto_char_out_pre_spaces,
4667+
char*number,intinput_len,intto_char_out_pre_spaces,
46474668
intsign,boolis_to_char,Oidcollid)
46484669
{
46494670
FormatNode*n;
46504671
NUMProc_Np,
46514672
*Np=&_Np;
4673+
constchar*pattern;
4674+
intpattern_len;
46524675

46534676
MemSet(Np,0,sizeof(NUMProc));
46544677

@@ -4816,9 +4839,11 @@ NUM_processor(FormatNode *node, NUMDesc *Num, char *inout,
48164839
if (!Np->is_to_char)
48174840
{
48184841
/*
4819-
* Check non-string inout end
4842+
* Check at least one character remains to be scanned. (In
4843+
* actions below, must use AMOUNT_TEST if we want to read more
4844+
* characters than that.)
48204845
*/
4821-
if (Np->inout_p >=Np->inout+from_char_input_len)
4846+
if (OVERLOAD_TEST)
48224847
break;
48234848
}
48244849

@@ -4828,12 +4853,16 @@ NUM_processor(FormatNode *node, NUMDesc *Num, char *inout,
48284853
if (n->type==NODE_TYPE_ACTION)
48294854
{
48304855
/*
4831-
* Create/reading digit/zero/blank/sing
4856+
* Create/read digit/zero/blank/sign/special-case
48324857
*
48334858
* 'NUM_S' note: The locale sign is anchored to number and we
48344859
* read/write it when we work with first or last number
4835-
* (NUM_0/NUM_9). This is reason why NUM_S missing in follow
4836-
* switch().
4860+
* (NUM_0/NUM_9). This is why NUM_S is missing in switch().
4861+
*
4862+
* Notice the "Np->inout_p++" at the bottom of the loop. This is
4863+
* why most of the actions advance inout_p one less than you might
4864+
* expect. In cases where we don't want that increment to happen,
4865+
* a switch case ends with "continue" not "break".
48374866
*/
48384867
switch (n->key->id)
48394868
{
@@ -4848,7 +4877,7 @@ NUM_processor(FormatNode *node, NUMDesc *Num, char *inout,
48484877
}
48494878
else
48504879
{
4851-
NUM_numpart_from_char(Np,n->key->id,from_char_input_len);
4880+
NUM_numpart_from_char(Np,n->key->id,input_len);
48524881
break;/* switch() case: */
48534882
}
48544883

@@ -4872,10 +4901,14 @@ NUM_processor(FormatNode *node, NUMDesc *Num, char *inout,
48724901
if (IS_FILLMODE(Np->Num))
48734902
continue;
48744903
}
4904+
if (*Np->inout_p!=',')
4905+
continue;
48754906
}
48764907
break;
48774908

48784909
caseNUM_G:
4910+
pattern=Np->L_thousands_sep;
4911+
pattern_len=strlen(pattern);
48794912
if (Np->is_to_char)
48804913
{
48814914
if (!Np->num_in)
@@ -4884,16 +4917,16 @@ NUM_processor(FormatNode *node, NUMDesc *Num, char *inout,
48844917
continue;
48854918
else
48864919
{
4887-
intx=strlen(Np->L_thousands_sep);
4888-
4889-
memset(Np->inout_p,' ',x);
4890-
Np->inout_p+=x-1;
4920+
/* just in case there are MB chars */
4921+
pattern_len=pg_mbstrlen(pattern);
4922+
memset(Np->inout_p,' ',pattern_len);
4923+
Np->inout_p+=pattern_len-1;
48914924
}
48924925
}
48934926
else
48944927
{
4895-
strcpy(Np->inout_p,Np->L_thousands_sep);
4896-
Np->inout_p+=strlen(Np->inout_p)-1;
4928+
strcpy(Np->inout_p,pattern);
4929+
Np->inout_p+=pattern_len-1;
48974930
}
48984931
}
48994932
else
@@ -4903,18 +4936,33 @@ NUM_processor(FormatNode *node, NUMDesc *Num, char *inout,
49034936
if (IS_FILLMODE(Np->Num))
49044937
continue;
49054938
}
4906-
Np->inout_p+=strlen(Np->L_thousands_sep)-1;
4939+
4940+
/*
4941+
* Because L_thousands_sep typically contains data
4942+
* characters (either '.' or ','), we can't use
4943+
* NUM_eat_non_data_chars here. Instead skip only if
4944+
* the input matches L_thousands_sep.
4945+
*/
4946+
if (AMOUNT_TEST(pattern_len)&&
4947+
strncmp(Np->inout_p,pattern,pattern_len)==0)
4948+
Np->inout_p+=pattern_len-1;
4949+
else
4950+
continue;
49074951
}
49084952
break;
49094953

49104954
caseNUM_L:
4955+
pattern=Np->L_currency_symbol;
49114956
if (Np->is_to_char)
49124957
{
4913-
strcpy(Np->inout_p,Np->L_currency_symbol);
4914-
Np->inout_p+=strlen(Np->inout_p)-1;
4958+
strcpy(Np->inout_p,pattern);
4959+
Np->inout_p+=strlen(pattern)-1;
49154960
}
49164961
else
4917-
Np->inout_p+=strlen(Np->L_currency_symbol)-1;
4962+
{
4963+
NUM_eat_non_data_chars(Np,pg_mbstrlen(pattern),input_len);
4964+
continue;
4965+
}
49184966
break;
49194967

49204968
caseNUM_RN:
@@ -4949,8 +4997,16 @@ NUM_processor(FormatNode *node, NUMDesc *Num, char *inout,
49494997
continue;
49504998

49514999
if (Np->is_to_char)
5000+
{
49525001
strcpy(Np->inout_p,get_th(Np->number,TH_LOWER));
4953-
Np->inout_p+=1;
5002+
Np->inout_p+=1;
5003+
}
5004+
else
5005+
{
5006+
/* All variants of 'th' occupy 2 characters */
5007+
NUM_eat_non_data_chars(Np,2,input_len);
5008+
continue;
5009+
}
49545010
break;
49555011

49565012
caseNUM_TH:
@@ -4959,8 +5015,16 @@ NUM_processor(FormatNode *node, NUMDesc *Num, char *inout,
49595015
continue;
49605016

49615017
if (Np->is_to_char)
5018+
{
49625019
strcpy(Np->inout_p,get_th(Np->number,TH_UPPER));
4963-
Np->inout_p+=1;
5020+
Np->inout_p+=1;
5021+
}
5022+
else
5023+
{
5024+
/* All variants of 'TH' occupy 2 characters */
5025+
NUM_eat_non_data_chars(Np,2,input_len);
5026+
continue;
5027+
}
49645028
break;
49655029

49665030
caseNUM_MI:
@@ -4977,6 +5041,11 @@ NUM_processor(FormatNode *node, NUMDesc *Num, char *inout,
49775041
{
49785042
if (*Np->inout_p=='-')
49795043
*Np->number='-';
5044+
else
5045+
{
5046+
NUM_eat_non_data_chars(Np,1,input_len);
5047+
continue;
5048+
}
49805049
}
49815050
break;
49825051

@@ -4994,23 +5063,31 @@ NUM_processor(FormatNode *node, NUMDesc *Num, char *inout,
49945063
{
49955064
if (*Np->inout_p=='+')
49965065
*Np->number='+';
5066+
else
5067+
{
5068+
NUM_eat_non_data_chars(Np,1,input_len);
5069+
continue;
5070+
}
49975071
}
49985072
break;
49995073

50005074
caseNUM_SG:
50015075
if (Np->is_to_char)
50025076
*Np->inout_p=Np->sign;
5003-
50045077
else
50055078
{
50065079
if (*Np->inout_p=='-')
50075080
*Np->number='-';
50085081
elseif (*Np->inout_p=='+')
50095082
*Np->number='+';
5083+
else
5084+
{
5085+
NUM_eat_non_data_chars(Np,1,input_len);
5086+
continue;
5087+
}
50105088
}
50115089
break;
50125090

5013-
50145091
default:
50155092
continue;
50165093
break;
@@ -5019,7 +5096,12 @@ NUM_processor(FormatNode *node, NUMDesc *Num, char *inout,
50195096
else
50205097
{
50215098
/*
5022-
* Remove to output char from input in TO_CHAR
5099+
* In TO_CHAR, non-pattern characters in the format are copied to
5100+
* the output. In TO_NUMBER, we skip one input character for each
5101+
* non-pattern format character, whether or not it matches the
5102+
* format character. (Currently, that's actually implemented as
5103+
* skipping one input byte per non-pattern format byte, which is
5104+
* wrong...)
50235105
*/
50245106
if (Np->is_to_char)
50255107
*Np->inout_p=n->character;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp