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

Commit3f1e529

Browse files
committed
Make to_timestamp and friends skip leading spaces before an integer field,
even when not in FM mode. This improves compatibility with Oracle and withour pre-8.4 behavior, as per bug #4862.Brendan JurdAdd a couple of regression test cases for this. In passing, get rid of thelabeling of the individual test cases; doesn't seem to be good for anythingexcept causing extra work when inserting a test...Tom Lane
1 parent18df0ff commit3f1e529

File tree

3 files changed

+157
-120
lines changed

3 files changed

+157
-120
lines changed

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

Lines changed: 7 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,7 +1,7 @@
11
/* -----------------------------------------------------------------------
22
* formatting.c
33
*
4-
* $PostgreSQL: pgsql/src/backend/utils/adt/formatting.c,v 1.157 2009/06/11 14:49:03 momjian Exp $
4+
* $PostgreSQL: pgsql/src/backend/utils/adt/formatting.c,v 1.158 2009/06/22 17:54:30 tgl Exp $
55
*
66
*
77
* Portions Copyright (c) 1999-2009, PostgreSQL Global Development Group
@@ -1817,7 +1817,7 @@ from_char_set_int(int *dest, const int value, const FormatNode *node)
18171817
* 'dest'. If 'dest' is NULL, the result is discarded.
18181818
*
18191819
* In fixed-width mode (the node does not have the FM suffix), consume at most
1820-
* 'len' characters.
1820+
* 'len' characters. However, any leading whitespace isn't counted in 'len'.
18211821
*
18221822
* We use strtol() to recover the integer value from the source string, in
18231823
* accordance with the given FormatNode.
@@ -1840,6 +1840,11 @@ from_char_parse_int_len(int *dest, char **src, const int len, FormatNode *node)
18401840
char*init=*src;
18411841
intused;
18421842

1843+
/*
1844+
* Skip any whitespace before parsing the integer.
1845+
*/
1846+
*src+=strspace_len(*src);
1847+
18431848
Assert(len <=DCH_MAX_ITEM_SIZ);
18441849
used= (int)strlcpy(copy,*src,len+1);
18451850

‎src/test/regress/expected/horology.out

Lines changed: 113 additions & 89 deletions
Original file line numberDiff line numberDiff line change
@@ -2708,157 +2708,181 @@ RESET DateStyle;
27082708
--
27092709
-- to_timestamp()
27102710
--
2711-
SELECT'' AS to_timestamp_1,to_timestamp('0097/Feb/16 --> 08:14:30', 'YYYY/Mon/DD --> HH:MI:SS');
2712-
to_timestamp_1 | to_timestamp
2713-
----------------+------------------------------
2714-
|Sat Feb 16 08:14:30 0097 PST
2711+
SELECT to_timestamp('0097/Feb/16 --> 08:14:30', 'YYYY/Mon/DD --> HH:MI:SS');
2712+
to_timestamp
2713+
------------------------------
2714+
Sat Feb 16 08:14:30 0097 PST
27152715
(1 row)
27162716

2717-
SELECT'' AS to_timestamp_2,to_timestamp('97/2/16 8:14:30', 'FMYYYY/FMMM/FMDD FMHH:FMMI:FMSS');
2718-
to_timestamp_2 | to_timestamp
2719-
----------------+------------------------------
2720-
|Sat Feb 16 08:14:30 0097 PST
2717+
SELECT to_timestamp('97/2/16 8:14:30', 'FMYYYY/FMMM/FMDD FMHH:FMMI:FMSS');
2718+
to_timestamp
2719+
------------------------------
2720+
Sat Feb 16 08:14:30 0097 PST
27212721
(1 row)
27222722

2723-
SELECT'' AS to_timestamp_3,to_timestamp('1985 January 12', 'YYYY FMMonth DD');
2724-
to_timestamp_3 | to_timestamp
2725-
----------------+------------------------------
2726-
|Sat Jan 12 00:00:00 1985 PST
2723+
SELECT to_timestamp('1985 January 12', 'YYYY FMMonth DD');
2724+
to_timestamp
2725+
------------------------------
2726+
Sat Jan 12 00:00:00 1985 PST
27272727
(1 row)
27282728

2729-
SELECT'' AS to_timestamp_4,to_timestamp('My birthday-> Year: 1976, Month: May, Day: 16',
2730-
'"My birthday-> Year" YYYY, "Month:" FMMonth, "Day:" DD');
2731-
to_timestamp_4 | to_timestamp
2732-
----------------+------------------------------
2733-
|Sun May 16 00:00:00 1976 PDT
2729+
SELECT to_timestamp('My birthday-> Year: 1976, Month: May, Day: 16',
2730+
'"My birthday-> Year" YYYY, "Month:" FMMonth, "Day:" DD');
2731+
to_timestamp
2732+
------------------------------
2733+
Sun May 16 00:00:00 1976 PDT
27342734
(1 row)
27352735

2736-
SELECT'' AS to_timestamp_5,to_timestamp('1,582nd VIII 21', 'Y,YYYth FMRM DD');
2737-
to_timestamp_5 | to_timestamp
2738-
----------------+------------------------------
2739-
|Sat Aug 21 00:00:00 1582 PST
2736+
SELECT to_timestamp('1,582nd VIII 21', 'Y,YYYth FMRM DD');
2737+
to_timestamp
2738+
------------------------------
2739+
Sat Aug 21 00:00:00 1582 PST
27402740
(1 row)
27412741

2742-
SELECT'' AS to_timestamp_6,to_timestamp('15 "text between quote marks" 98 54 45',
2743-
E'HH24 "\\text between quote marks\\"" YY MI SS');
2744-
to_timestamp_6 | to_timestamp
2745-
----------------+------------------------------
2746-
|Thu Jan 01 15:54:45 1998 PST
2742+
SELECT to_timestamp('15 "text between quote marks" 98 54 45',
2743+
E'HH24 "\\text between quote marks\\"" YY MI SS');
2744+
to_timestamp
2745+
------------------------------
2746+
Thu Jan 01 15:54:45 1998 PST
27472747
(1 row)
27482748

2749-
SELECT'' AS to_timestamp_7,to_timestamp('05121445482000', 'MMDDHH24MISSYYYY');
2750-
to_timestamp_7 | to_timestamp
2751-
----------------+------------------------------
2752-
|Fri May 12 14:45:48 2000 PDT
2749+
SELECT to_timestamp('05121445482000', 'MMDDHH24MISSYYYY');
2750+
to_timestamp
2751+
------------------------------
2752+
Fri May 12 14:45:48 2000 PDT
27532753
(1 row)
27542754

2755-
SELECT'' AS to_timestamp_8,to_timestamp('2000January09Sunday', 'YYYYFMMonthDDFMDay');
2756-
to_timestamp_8 | to_timestamp
2757-
----------------+------------------------------
2758-
|Sun Jan 09 00:00:00 2000 PST
2755+
SELECT to_timestamp('2000January09Sunday', 'YYYYFMMonthDDFMDay');
2756+
to_timestamp
2757+
------------------------------
2758+
Sun Jan 09 00:00:00 2000 PST
27592759
(1 row)
27602760

2761-
SELECT'' AS to_timestamp_9,to_timestamp('97/Feb/16', 'YYMonDD');
2761+
SELECT to_timestamp('97/Feb/16', 'YYMonDD');
27622762
ERROR: invalid value "/Fe" for "Mon"
27632763
DETAIL: The given value did not match any of the allowed values for this field.
2764-
SELECT'' AS to_timestamp_10,to_timestamp('19971116', 'YYYYMMDD');
2765-
to_timestamp_10 | to_timestamp
2766-
-----------------+------------------------------
2767-
|Sun Nov 16 00:00:00 1997 PST
2764+
SELECT to_timestamp('19971116', 'YYYYMMDD');
2765+
to_timestamp
2766+
------------------------------
2767+
Sun Nov 16 00:00:00 1997 PST
27682768
(1 row)
27692769

2770-
SELECT'' AS to_timestamp_11,to_timestamp('20000-1116', 'YYYY-MMDD');
2771-
to_timestamp_11 | to_timestamp
2772-
-----------------+-------------------------------
2773-
|Thu Nov 16 00:00:00 20000 PST
2770+
SELECT to_timestamp('20000-1116', 'YYYY-MMDD');
2771+
to_timestamp
2772+
-------------------------------
2773+
Thu Nov 16 00:00:00 20000 PST
27742774
(1 row)
27752775

2776-
SELECT'' AS to_timestamp_12,to_timestamp('9-1116', 'Y-MMDD');
2777-
to_timestamp_12 | to_timestamp
2778-
-----------------+------------------------------
2779-
|Mon Nov 16 00:00:00 2009 PST
2776+
SELECT to_timestamp('9-1116', 'Y-MMDD');
2777+
to_timestamp
2778+
------------------------------
2779+
Mon Nov 16 00:00:00 2009 PST
27802780
(1 row)
27812781

2782-
SELECT'' AS to_timestamp_13,to_timestamp('95-1116', 'YY-MMDD');
2783-
to_timestamp_13 | to_timestamp
2784-
-----------------+------------------------------
2785-
|Thu Nov 16 00:00:00 1995 PST
2782+
SELECT to_timestamp('95-1116', 'YY-MMDD');
2783+
to_timestamp
2784+
------------------------------
2785+
Thu Nov 16 00:00:00 1995 PST
27862786
(1 row)
27872787

2788-
SELECT'' AS to_timestamp_14,to_timestamp('995-1116', 'YYY-MMDD');
2789-
to_timestamp_14 | to_timestamp
2790-
-----------------+------------------------------
2791-
|Thu Nov 16 00:00:00 1995 PST
2788+
SELECT to_timestamp('995-1116', 'YYY-MMDD');
2789+
to_timestamp
2790+
------------------------------
2791+
Thu Nov 16 00:00:00 1995 PST
27922792
(1 row)
27932793

2794-
SELECT'' AS to_timestamp_15,to_timestamp('2005426', 'YYYYWWD');
2795-
to_timestamp_15 | to_timestamp
2796-
-----------------+------------------------------
2797-
|Sat Oct 15 00:00:00 2005 PDT
2794+
SELECT to_timestamp('2005426', 'YYYYWWD');
2795+
to_timestamp
2796+
------------------------------
2797+
Sat Oct 15 00:00:00 2005 PDT
27982798
(1 row)
27992799

2800-
SELECT'' AS to_timestamp_16,to_timestamp('2005300', 'YYYYDDD');
2801-
to_timestamp_16 | to_timestamp
2802-
-----------------+------------------------------
2803-
|Thu Oct 27 00:00:00 2005 PDT
2800+
SELECT to_timestamp('2005300', 'YYYYDDD');
2801+
to_timestamp
2802+
------------------------------
2803+
Thu Oct 27 00:00:00 2005 PDT
28042804
(1 row)
28052805

2806-
SELECT'' AS to_timestamp_17,to_timestamp('2005527', 'IYYYIWID');
2807-
to_timestamp_17 | to_timestamp
2808-
-----------------+------------------------------
2809-
|Sun Jan 01 00:00:00 2006 PST
2806+
SELECT to_timestamp('2005527', 'IYYYIWID');
2807+
to_timestamp
2808+
------------------------------
2809+
Sun Jan 01 00:00:00 2006 PST
28102810
(1 row)
28112811

2812-
SELECT'' AS to_timestamp_18,to_timestamp('005527', 'IYYIWID');
2813-
to_timestamp_18 | to_timestamp
2814-
-----------------+------------------------------
2815-
|Sun Jan 01 00:00:00 2006 PST
2812+
SELECT to_timestamp('005527', 'IYYIWID');
2813+
to_timestamp
2814+
------------------------------
2815+
Sun Jan 01 00:00:00 2006 PST
28162816
(1 row)
28172817

2818-
SELECT '' AS to_timestamp_19, to_timestamp('05527', 'IYIWID');
2819-
to_timestamp_19 | to_timestamp
2820-
-----------------+------------------------------
2821-
| Sun Jan 01 00:00:00 2006 PST
2818+
SELECT to_timestamp('05527', 'IYIWID');
2819+
to_timestamp
2820+
------------------------------
2821+
Sun Jan 01 00:00:00 2006 PST
2822+
(1 row)
2823+
2824+
SELECT to_timestamp('5527', 'IIWID');
2825+
to_timestamp
2826+
------------------------------
2827+
Sun Jan 01 00:00:00 2006 PST
28222828
(1 row)
28232829

2824-
SELECT '' AS to_timestamp_20, to_timestamp('5527', 'IIWID');
2825-
to_timestamp_20 | to_timestamp
2826-
-----------------+------------------------------
2827-
| Sun Jan 01 00:00:00 2006 PST
2830+
SELECT to_timestamp('2005364', 'IYYYIDDD');
2831+
to_timestamp
2832+
------------------------------
2833+
Sun Jan 01 00:00:00 2006 PST
2834+
(1 row)
2835+
2836+
SELECT to_timestamp('20050302', 'YYYYMMDD');
2837+
to_timestamp
2838+
------------------------------
2839+
Wed Mar 02 00:00:00 2005 PST
28282840
(1 row)
28292841

2830-
SELECT '' AS to_timestamp_21, to_timestamp('2005364', 'IYYYIDDD');
2831-
to_timestamp_21 | to_timestamp
2832-
-----------------+------------------------------
2833-
| Sun Jan 01 00:00:00 2006 PST
2842+
SELECT to_timestamp('2005 03 02', 'YYYYMMDD');
2843+
to_timestamp
2844+
------------------------------
2845+
Wed Mar 02 00:00:00 2005 PST
2846+
(1 row)
2847+
2848+
SELECT to_timestamp(' 2005 03 02', 'YYYYMMDD');
2849+
to_timestamp
2850+
------------------------------
2851+
Wed Mar 02 00:00:00 2005 PST
2852+
(1 row)
2853+
2854+
SELECT to_timestamp(' 20050302', 'YYYYMMDD');
2855+
to_timestamp
2856+
------------------------------
2857+
Wed Mar 02 00:00:00 2005 PST
28342858
(1 row)
28352859

28362860
--
28372861
-- Check errors for some incorrect usages of to_timestamp()
28382862
--
28392863
-- Mixture of date conventions (ISO week and Gregorian):
2840-
SELECT'' AS to_timestamp_22,to_timestamp('2005527', 'YYYYIWID');
2864+
SELECT to_timestamp('2005527', 'YYYYIWID');
28412865
ERROR: invalid combination of date conventions
28422866
HINT: Do not mix Gregorian and ISO week date conventions in a formatting template.
28432867
-- Insufficient characters in the source string:
2844-
SELECT'' AS to_timestamp_23,to_timestamp('19971', 'YYYYMMDD');
2868+
SELECT to_timestamp('19971', 'YYYYMMDD');
28452869
ERROR: source string too short for "MM" formatting field
28462870
DETAIL: Field requires 2 characters, but only 1 remain.
28472871
HINT: If your source string is not fixed-width, try using the "FM" modifier.
28482872
-- Insufficient digit characters for a single node:
2849-
SELECT'' AS to_timestamp_24,to_timestamp('19971)24', 'YYYYMMDD');
2873+
SELECT to_timestamp('19971)24', 'YYYYMMDD');
28502874
ERROR: invalid value "1)" for "MM"
28512875
DETAIL: Field requires 2 characters, but only 1 could be parsed.
28522876
HINT: If your source string is not fixed-width, try using the "FM" modifier.
28532877
-- Value clobbering:
2854-
SELECT'' AS to_timestamp_25,to_timestamp('1997-11-Jan-16', 'YYYY-MM-Mon-DD');
2878+
SELECT to_timestamp('1997-11-Jan-16', 'YYYY-MM-Mon-DD');
28552879
ERROR: conflicting values for "Mon" field in formatting string
28562880
DETAIL: This value contradicts a previous setting for the same field type.
28572881
-- Non-numeric input:
2858-
SELECT'' AS to_timestamp_26,to_timestamp('199711xy', 'YYYYMMDD');
2882+
SELECT to_timestamp('199711xy', 'YYYYMMDD');
28592883
ERROR: invalid value "xy" for "DD"
28602884
DETAIL: Value must be an integer.
28612885
-- Input that doesn't fit in an int:
2862-
SELECT'' AS to_timestamp_27,to_timestamp('10000000000', 'FMYYYY');
2886+
SELECT to_timestamp('10000000000', 'FMYYYY');
28632887
ERROR: value for "YYYY" in source string is out of range
28642888
DETAIL: Value must be in the range -2147483648 to 2147483647.

0 commit comments

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