FM format modifier does not remove leading zero from year

On Saturday 02 January 2010 10:14:07 am Andrus wrote:

Command:

select to_char(DATE'2009-1-1','FMDD.FMMM.FMYY')

Result observed:

1.1.09

Result expected:

1.1.9

How to fix ?

Andrus.

From what I could see in the source code (src/backend/utils/adt/formatting.c)
the year portion of the string is not run through the FM modifier. A fix would
mean a patch to the above AFAIK.

--
Adrian Klaver
aklaver@comcast.net

Adrian Klaver <aklaver@comcast.net> writes:

From what I could see in the source code
(src/backend/utils/adt/formatting.c) the year portion of the string is
not run through the FM modifier. A fix would mean a patch to the above
AFAIK.

Should it be? Can anyone check how this works on Oracle?

regards, tom lane

On 1/5/2010 10:54 AM, Tom Lane wrote:

Adrian Klaver<aklaver@comcast.net> writes:

From what I could see in the source code
(src/backend/utils/adt/formatting.c) the year portion of the string is
not run through the FM modifier. A fix would mean a patch to the above
AFAIK.

Should it be? Can anyone check how this works on Oracle?

Oracle states clearly in the SQL Reference manual:

"A modifier can appear in a format model more than once. In such a case,
each subsequent occurrence toggles the effects of the modifier."

I get the following results:

select to_char(DATE'2009-1-1','FMDD.FMMM.FMYY') from dual;

1.01.9

select to_char(DATE'2009-1-1','FM DD.MM.YY') from dual

1.1.9

--
Guy Rouillier

Guy Rouillier <guyr-ml1@burntmail.com> writes:

Oracle states clearly in the SQL Reference manual:

"A modifier can appear in a format model more than once. In such a case,
each subsequent occurrence toggles the effects of the modifier."

*Toggles* the effect of the modifier? Egad, what drunken idiot chose
that specification?

That's certainly not how PG is interpreting the FM modifier --- we
suppose it applies to just the specifier it's attached to. Still,
we generally assume that Oracle is the authoritative reference for
how to_char() ought to behave, so maybe we should hold our noses
and change it. Problem is that this would inevitably break
practically every existing use of to_date with FM :-( (not to mention
the other modifiers, which I suppose behave that way too?)

Anyway, your example clearly shows that FM ought to strip leading
zeroes in YY, so that change ought to get made.

regards, tom lane

On 1/6/2010 3:29 PM, Tom Lane wrote:

Guy Rouillier<guyr-ml1@burntmail.com> writes:

Oracle states clearly in the SQL Reference manual:

"A modifier can appear in a format model more than once. In such a case,
each subsequent occurrence toggles the effects of the modifier."

*Toggles* the effect of the modifier? Egad, what drunken idiot chose
that specification?

Eh, tomato, tomahto. If you assume that someone will strip leading
zeroes consistently, the Oracle approach makes sense. That would be a
reasonable assumption to make; why would I strip the zero off the month
but leave it on the day? So, in the unusual case that you want to do
such a thing, you are asked to use a second occurrence of FM to turn
zero suppression back off.

--
Guy Rouillier

Guy Rouillier wrote:

On 1/6/2010 3:29 PM, Tom Lane wrote:

Guy Rouillier<guyr-ml1@burntmail.com> writes:

Oracle states clearly in the SQL Reference manual:

"A modifier can appear in a format model more than once. In such a case,
each subsequent occurrence toggles the effects of the modifier."

*Toggles* the effect of the modifier? Egad, what drunken idiot chose
that specification?

Eh, tomato, tomahto. If you assume that someone will strip leading
zeroes consistently, the Oracle approach makes sense. That would be a
reasonable assumption to make; why would I strip the zero off the month
but leave it on the day? So, in the unusual case that you want to do
such a thing, you are asked to use a second occurrence of FM to turn
zero suppression back off.

I have developed the attached patch which implements FM control of YYY,
YY, and Y specifications. I also documented that we do not match
Oracle's toggle behavior. There are a few effects on regression test
output which are part of this patch.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Bruce Momjian wrote:

Guy Rouillier wrote:

On 1/6/2010 3:29 PM, Tom Lane wrote:

Guy Rouillier<guyr-ml1@burntmail.com> writes:

Oracle states clearly in the SQL Reference manual:

"A modifier can appear in a format model more than once. In such a case,
each subsequent occurrence toggles the effects of the modifier."

*Toggles* the effect of the modifier? Egad, what drunken idiot chose
that specification?

Eh, tomato, tomahto. If you assume that someone will strip leading
zeroes consistently, the Oracle approach makes sense. That would be a
reasonable assumption to make; why would I strip the zero off the month
but leave it on the day? So, in the unusual case that you want to do
such a thing, you are asked to use a second occurrence of FM to turn
zero suppression back off.

I have developed the attached patch which implements FM control of YYY,
YY, and Y specifications. I also documented that we do not match
Oracle's toggle behavior. There are a few effects on regression test
output which are part of this patch.

What's the point of not following Oracle here, since this is solely an
Oracle compatibility function?

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Alvaro Herrera <alvherre@commandprompt.com> writes:

What's the point of not following Oracle here, since this is solely an
Oracle compatibility function?

Changing FM's behavior like that will break approximately every user
of to_char() ...

regards, tom lane

Tom Lane wrote:

Alvaro Herrera <alvherre@commandprompt.com> writes:

What's the point of not following Oracle here, since this is solely an
Oracle compatibility function?

Changing FM's behavior like that will break approximately every user
of to_char() ...

I would love to know why we are finding out about this incompatibility
only in 2010, years after we implemented this. Is no one porting
multi-specification "FM" to_char() strings from Oracle to PostgreSQL?

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Applied.

---------------------------------------------------------------------------

Bruce Momjian wrote:

Guy Rouillier wrote:

On 1/6/2010 3:29 PM, Tom Lane wrote:

Guy Rouillier<guyr-ml1@burntmail.com> writes:

Oracle states clearly in the SQL Reference manual:

"A modifier can appear in a format model more than once. In such a case,
each subsequent occurrence toggles the effects of the modifier."

*Toggles* the effect of the modifier? Egad, what drunken idiot chose
that specification?

Eh, tomato, tomahto. If you assume that someone will strip leading
zeroes consistently, the Oracle approach makes sense. That would be a
reasonable assumption to make; why would I strip the zero off the month
but leave it on the day? So, in the unusual case that you want to do
such a thing, you are asked to use a second occurrence of FM to turn
zero suppression back off.

I have developed the attached patch which implements FM control of YYY,
YY, and Y specifications. I also documented that we do not match
Oracle's toggle behavior. There are a few effects on regression test
output which are part of this patch.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

[ text/x-diff is unsupported, treating like TEXT/PLAIN ]

Index: doc/src/sgml/func.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/func.sgml,v
retrieving revision 1.500
diff -c -c -r1.500 func.sgml
*** doc/src/sgml/func.sgml	1 Feb 2010 15:38:21 -0000	1.500
--- doc/src/sgml/func.sgml	6 Feb 2010 21:14:41 -0000
***************
*** 5174,5180 ****
<para>
<literal>FM</literal> suppresses leading zeroes and trailing blanks
that would otherwise be added to make the output of a pattern be
!        fixed-width.
</para>
</listitem>

--- 5174,5184 ----
<para>
<literal>FM</literal> suppresses leading zeroes and trailing blanks
that would otherwise be added to make the output of a pattern be
!        fixed-width.  In <productname>PostgreSQL</productname>,
!        <literal>FM</literal> modifies only the next specification, while in
!        Oracle <literal>FM</literal> affects all subsequent
!        specifications, and repeated <literal>FM</literal> modifiers
!        toggle fill mode on and off.
</para>
</listitem>

Index: src/backend/utils/adt/formatting.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/utils/adt/formatting.c,v
retrieving revision 1.162
diff -c -c -r1.162 formatting.c
*** src/backend/utils/adt/formatting.c	2 Jan 2010 16:57:53 -0000	1.162
--- src/backend/utils/adt/formatting.c	6 Feb 2010 21:14:42 -0000
***************
*** 515,520 ****
--- 515,521 ----
#define S_th(_s)	(((_s) & DCH_S_th) ? 1 : 0)
#define S_TH_TYPE(_s)	(((_s) & DCH_S_TH) ? TH_UPPER : TH_LOWER)

+ /* Oracle toggles FM behavior, we don't; see docs. */
#define S_FM(_s)	(((_s) & DCH_S_FM) ? 1 : 0)
#define S_SP(_s)	(((_s) & DCH_S_SP) ? 1 : 0)
#define S_TM(_s)	(((_s) & DCH_S_TM) ? 1 : 0)
***************
*** 2411,2438 ****
break;
case DCH_YYY:
case DCH_IYY:
! 				snprintf(buff, sizeof(buff), "%03d",
n->key->id == DCH_YYY ?
ADJUST_YEAR(tm->tm_year, is_interval) :
ADJUST_YEAR(date2isoyear(tm->tm_year,
tm->tm_mon, tm->tm_mday),
is_interval));
i = strlen(buff);
! 				strcpy(s, buff + (i - 3));
if (S_THth(n->suffix))
str_numth(s, s, S_TH_TYPE(n->suffix));
s += strlen(s);
break;
case DCH_YY:
case DCH_IY:
! 				snprintf(buff, sizeof(buff), "%02d",
n->key->id == DCH_YY ?
ADJUST_YEAR(tm->tm_year, is_interval) :
ADJUST_YEAR(date2isoyear(tm->tm_year,
tm->tm_mon, tm->tm_mday),
is_interval));
i = strlen(buff);
! 				strcpy(s, buff + (i - 2));
if (S_THth(n->suffix))
str_numth(s, s, S_TH_TYPE(n->suffix));
s += strlen(s);
--- 2412,2441 ----
break;
case DCH_YYY:
case DCH_IYY:
! 				snprintf(buff, sizeof(buff), "%0*d",
! 						 S_FM(n->suffix) ? 0 : 3,
n->key->id == DCH_YYY ?
ADJUST_YEAR(tm->tm_year, is_interval) :
ADJUST_YEAR(date2isoyear(tm->tm_year,
tm->tm_mon, tm->tm_mday),
is_interval));
i = strlen(buff);
! 				strcpy(s, buff + (i > 3 ? i - 3 : 0));
if (S_THth(n->suffix))
str_numth(s, s, S_TH_TYPE(n->suffix));
s += strlen(s);
break;
case DCH_YY:
case DCH_IY:
! 				snprintf(buff, sizeof(buff), "%0*d",
! 						 S_FM(n->suffix) ? 0 : 2,
n->key->id == DCH_YY ?
ADJUST_YEAR(tm->tm_year, is_interval) :
ADJUST_YEAR(date2isoyear(tm->tm_year,
tm->tm_mon, tm->tm_mday),
is_interval));
i = strlen(buff);
! 				strcpy(s, buff + (i > 2 ? i - 2 : 0));
if (S_THth(n->suffix))
str_numth(s, s, S_TH_TYPE(n->suffix));
s += strlen(s);
***************
*** 2446,2452 ****
tm->tm_mon, tm->tm_mday),
is_interval));
i = strlen(buff);
! 				strcpy(s, buff + (i - 1));
if (S_THth(n->suffix))
str_numth(s, s, S_TH_TYPE(n->suffix));
s += strlen(s);
--- 2449,2455 ----
tm->tm_mon, tm->tm_mday),
is_interval));
i = strlen(buff);
! 				strcpy(s, buff + (i > 1 ? i - 1 : 0));
if (S_THth(n->suffix))
str_numth(s, s, S_TH_TYPE(n->suffix));
s += strlen(s);
Index: src/test/regress/expected/timestamp.out
===================================================================
RCS file: /cvsroot/pgsql/src/test/regress/expected/timestamp.out,v
retrieving revision 1.40
diff -c -c -r1.40 timestamp.out
*** src/test/regress/expected/timestamp.out	22 Mar 2009 01:12:32 -0000	1.40
--- src/test/regress/expected/timestamp.out	6 Feb 2010 21:14:44 -0000
***************
*** 1063,1070 ****
| 1,997 1997 997 97 7 20 1 2 7 45 14 6 2450494
| 1,997 1997 997 97 7 20 1 2 7 46 15 7 2450495
| 1,997 1997 997 97 7 20 1 2 7 47 16 1 2450496
!            | 0,097 97 097 97 7 1 1 2 7 47 16 3 1686042
!            | 0,097 97 097 97 7 1 1 2 7 47 16 7 1756536
| 0,597 597 597 97 7 6 1 2 7 47 16 5 1939157
| 1,097 1097 097 97 7 11 1 2 7 47 16 3 2121778
| 1,697 1697 697 97 7 17 1 2 7 47 16 7 2340924
--- 1063,1070 ----
| 1,997 1997 997 97 7 20 1 2 7 45 14 6 2450494
| 1,997 1997 997 97 7 20 1 2 7 46 15 7 2450495
| 1,997 1997 997 97 7 20 1 2 7 47 16 1 2450496
!            | 0,097 97 97 97 7 1 1 2 7 47 16 3 1686042
!            | 0,097 97 97 97 7 1 1 2 7 47 16 7 1756536
| 0,597 597 597 97 7 6 1 2 7 47 16 5 1939157
| 1,097 1097 097 97 7 11 1 2 7 47 16 3 2121778
| 1,697 1697 697 97 7 17 1 2 7 47 16 7 2340924
***************
*** 1561,1568 ****
| 1997 997 97 7 7 47 5
| 1997 997 97 7 7 48 6
| 1997 997 97 7 7 49 7
!             | 97 097 97 7 7 44 2
!             | 97 097 97 7 7 48 6
| 597 597 97 7 7 46 4
| 1097 097 97 7 7 44 2
| 1697 697 97 7 7 48 6
--- 1561,1568 ----
| 1997 997 97 7 7 47 5
| 1997 997 97 7 7 48 6
| 1997 997 97 7 7 49 7
!             | 97 97 97 7 7 44 2
!             | 97 97 97 7 7 48 6
| 597 597 97 7 7 46 4
| 1097 097 97 7 7 44 2
| 1697 697 97 7 7 48 6
Index: src/test/regress/expected/timestamptz.out
===================================================================
RCS file: /cvsroot/pgsql/src/test/regress/expected/timestamptz.out,v
retrieving revision 1.34
diff -c -c -r1.34 timestamptz.out
*** src/test/regress/expected/timestamptz.out	22 Mar 2009 01:12:32 -0000	1.34
--- src/test/regress/expected/timestamptz.out	6 Feb 2010 21:14:44 -0000
***************
*** 1148,1155 ****
| 1,997 1997 997 97 7 20 1 2 7 45 14 6 2450494
| 1,997 1997 997 97 7 20 1 2 7 46 15 7 2450495
| 1,997 1997 997 97 7 20 1 2 7 47 16 1 2450496
!            | 0,097 97 097 97 7 1 1 2 7 47 16 3 1686042
!            | 0,097 97 097 97 7 1 1 2 7 47 16 7 1756536
| 0,597 597 597 97 7 6 1 2 7 47 16 5 1939157
| 1,097 1097 097 97 7 11 1 2 7 47 16 3 2121778
| 1,697 1697 697 97 7 17 1 2 7 47 16 7 2340924
--- 1148,1155 ----
| 1,997 1997 997 97 7 20 1 2 7 45 14 6 2450494
| 1,997 1997 997 97 7 20 1 2 7 46 15 7 2450495
| 1,997 1997 997 97 7 20 1 2 7 47 16 1 2450496
!            | 0,097 97 97 97 7 1 1 2 7 47 16 3 1686042
!            | 0,097 97 97 97 7 1 1 2 7 47 16 7 1756536
| 0,597 597 597 97 7 6 1 2 7 47 16 5 1939157
| 1,097 1097 097 97 7 11 1 2 7 47 16 3 2121778
| 1,697 1697 697 97 7 17 1 2 7 47 16 7 2340924
***************
*** 1655,1662 ****
| 1997 997 97 7 7 47 5
| 1997 997 97 7 7 48 6
| 1997 997 97 7 7 49 7
!             | 97 097 97 7 7 44 2
!             | 97 097 97 7 7 48 6
| 597 597 97 7 7 46 4
| 1097 097 97 7 7 44 2
| 1697 697 97 7 7 48 6
--- 1655,1662 ----
| 1997 997 97 7 7 47 5
| 1997 997 97 7 7 48 6
| 1997 997 97 7 7 49 7
!             | 97 97 97 7 7 44 2
!             | 97 97 97 7 7 48 6
| 597 597 97 7 7 46 4
| 1097 097 97 7 7 44 2
| 1697 697 97 7 7 48 6

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +