Re: insert throw error when year field len > 4 for timestamptz datatype

On Mon, Sep 16, 2013 at 7:22 PM, Haribabu kommi
<haribabu(dot)kommi(at)huawei(dot)com>wrote:

> *On *14 August 2013 Rushabh Lathia wrote:**
>
> ** **
>
> >postgres=# create table test ( a timestamptz);****
>
> >CREATE TABLE****
>
> ** **
>
> >-- Date with year 1000****
>
> >postgres=# insert into test values ( 'Sat Mar 11 23:58:48 1000 IST');***
> *
>
> >INSERT 0 1****
>
> ** **
>
> >-- Now try with year 10000 it will return error****
>
> >postgres=# insert into test values ( 'Sat Mar 11 23:58:48 10000 IST');**
> **
>
> >ERROR: invalid input syntax for type timestamp with time zone: "Sat Mar
> 11 23:58:48 10000 IST" ****
>
> >LINE 1: insert into test values ( 'Sat Mar 11 23:58:48 10000 IST');****
>
> ** **
>
> >here error coming from timestamptz_in() -> datefields_to_timestamp() ->**
> **
>
> >DecodeDateTime() stack.****
>
> ** **
>
> >Looking more at the DecodeDateTime() function, here error coming while
> trying****
>
> >to Decode year field which is 10000 in the our test. For year field
> ftype is****
>
> >DTK_NUMBER, and under DTK_NUMBER for this case if drop in to following
> condition:****
>
> ** **
>
> >else if (flen > 4)****
>
> >{****
>
> >dterr = DecodeNumberField(flen, field[i], fmask,****
>
> > &tmask, tm,****
>
> > fsec, &is2digits);****
>
> >if (dterr < 0)****
>
> >return dterr;****
>
> >}****
>
> ** **
>
> >because flen in out case flen is 5 (10000).****
>
> ** **
>
> >As per the comment above DecodeNumberField(), it interpret numeric
> string as a****
>
> >concatenated date or time field. So ideally we should be into
> DecodeNumberField****
>
> >function only with (fmask & DTK_DATE_M) == 0 or (fmask & DTK_TIME_M) ==
> 0,****
>
> >right ??****
>
> ** **
>
> >So, I tried the same and after that test working fine.****
>
> ** **
>
> >PFA patch and share your input/suggestions.****
>
> ** **
>
> Patch applies cleanly to HEAD. As this patch tries to improve in inserting
> the date of the year value to be more than 4 in length.****
>
> But it didn’t solve all the ways to insert the year field more than 4 in
> length. Please check the following test.****
>
> ** **
>
> ** **
>
> postgres=# insert into test values ('10001010 10:10:10 IST');****
>
> INSERT 0 1****
>
> postgres=# insert into test values ('100011010 10:10:10 IST');****
>
> ERROR: invalid input syntax for type timestamp with time zone: "100011010
> 10:10:10 IST" at character 26****
>
> STATEMENT: insert into test values ('100011010 10:10:10 IST');****
>
> ERROR: invalid input syntax for type timestamp with time zone: "100011010
> 10:10:10 IST"****
>
> LINE 1: insert into test values ('100011010 10:10:10 IST');****
>
> ^****
>
> ** **
>
> I feel it is better to provide the functionality of inserting year field
> more than 4 in length in all flows.
>

+1. Nice catch.

Here is the latest version of patch which handles the functionality in all
flows.
Could you test it and share you comments.

Thanks,
Rushabh Lathia
www.EnterpriseDB.com