Thread: Issue in Behavior of Interval Datatype
<div class="WordSection1"><p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Arial","sans-serif"">select (interval'56:48' minute to second<b>);</b><br /><b><span style="color:#004080">result</span></b></span><b><span style="font-size:10.0pt">：</span></b><spanstyle="font-size:10.0pt;font-family:"Arial","sans-serif"">00:56:48 <br />select(interval '-56:48' minute to second); <br /><b><span style="color:#004080">result</span></b></span><span style="font-size:10.0pt">：</span><spanstyle="font-size:10.0pt;font-family:"Arial","sans-serif"">-56:48:00 <br />select (interval'+56:48' minute to second); <br /><b><span style="color:#004080">result</span></b></span><span style="font-size:10.0pt">：</span><spanstyle="font-size:10.0pt;font-family:"Arial","sans-serif"">56:48:00</span><p class="MsoNormal"><spanstyle="font-size:10.0pt;font-family:"Arial","sans-serif""> </span><p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Arial","sans-serif"">Whenuser uses ‘+’ or ‘–‘ symbol, then minute to second range isgetting ignored.</span><p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Arial","sans-serif""> </span><pclass="MsoNormal"><span style="font-size:10.0pt;font-family:"Arial","sans-serif"">Ihave checked the code and found that in function DecodeInterval(),for timezone case (DTK_TZ) it uses INTERVAL_FULL_RANGE irrespective of range passed by user.</span><p class="MsoNormal"><spanstyle="font-size:10.0pt;font-family:"Arial","sans-serif"">However if use the range passed as argumentin function DecodeInterval(), the result of using ‘+’ or ‘-‘ is same as without using it.</span><p class="MsoNormal"><spanstyle="font-size:10.0pt;font-family:"Arial","sans-serif"">Is there any particular reason for ignoringthe range for DTK_TZ case in DecodeInterval() function?</span><p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Arial","sans-serif""> </span><pclass="MsoNormal"><span style="font-size:10.0pt;font-family:"Arial","sans-serif""> </span><pclass="MsoNormal"><span style="font-size:10.0pt;font-family:"Arial","sans-serif"">WithRegards,</span><p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Arial","sans-serif"">AmitKapila</span><p class="MsoNormal"> </div>
Amit Kapila <amit.kapila@huawei.com> writes:
> select (interval '56:48' minute to second);
> result$B!'(B00:56:48
> select (interval '-56:48' minute to second);
> result$B!'(B-56:48:00
> select (interval '+56:48' minute to second);
> result$B!'(B56:48:00
> I have checked the code and found that in function DecodeInterval(), for
> timezone case (DTK_TZ) it uses INTERVAL_FULL_RANGE irrespective of range
> passed by user.
> However if use the range passed as argument in function DecodeInterval(),
> the result of using $B!F(B+$B!G(B or $B!F(B-$B!F(B is same as without using it.
> Is there any particular reason for ignoring the range for DTK_TZ case in
> DecodeInterval() function?
I think you are right; this if-block should be exactly like the DTK_TIME
case except for handling the prepended sign. That also raises the
question why it is changing the tmask value returned by DecodeTime.
It seems to be doing exactly the wrong thing there. Test case:
regression=# select (interval '56:48 56:48' );
ERROR: invalid input syntax for type interval: "56:48 56:48"
LINE 1: select (interval '56:48 56:48' ); ^
regression=# select (interval '56:48 +56:48' );interval
----------56:48:00
(1 row)
The second one fails to fail because an inappropriate tmask value got
included into fmask.
Will fix.
regards, tom lane
From: Tom Lane [mailto:tgl@sss.pgh.pa.us] Sent: Saturday, August 04, 2012 1:48 AM Amit Kapila <amit.kapila@huawei.com> writes: > select (interval '56:48' minute to second); > result$B!'(B00:56:48 > select (interval '-56:48' minute to second); > result$B!'(B-56:48:00 > select (interval '+56:48' minute to second); > result$B!'(B56:48:00 > I have checked the code and found that in function DecodeInterval(), for > timezone case (DTK_TZ) it uses INTERVAL_FULL_RANGE irrespective of range > passed by user. > However if use the range passed as argument in function DecodeInterval(), > the result of using $B!F(B+$B!G(B or $B!F(B-$B!F(B is same as without using it. > Is there any particular reason for ignoring the range for DTK_TZ case in > DecodeInterval() function? > I think you are right; this if-block should be exactly like the DTK_TIME > case except for handling the prepended sign. That also raises the > question why it is changing the tmask value returned by DecodeTime. > It seems to be doing exactly the wrong thing there. Test case: > regression=# select (interval '56:48 56:48' ); > ERROR: invalid input syntax for type interval: "56:48 56:48" > LINE 1: select (interval '56:48 56:48' ); ^ > regression=# select (interval '56:48 +56:48' ); > interval > ---------- > 56:48:00 > (1 row) > The second one fails to fail because an inappropriate tmask value got >included into fmask. Yes, this is right that tmask need not be changed, also one more thing I have noticed that in file Interval.c also there is a function DecodeInterval() which is currently little different from DecodeInterval() in datetime.c for DTK_TZ case. For example Assert check is commented. Why the Assert check is commented out there? May be due to some defect, but I am not able to think the reason for same. Shouldn't we make this change (don't change tmask in case of DTK_TZ) in Interval.c? >Will fix. Thank you very much. With Regards, Amit Kapila.
Amit Kapila <amit.kapila@huawei.com> writes:
> Yes, this is right that tmask need not be changed, also one more thing I
> have noticed that
> in file Interval.c also there is a function DecodeInterval() which is
> currently little different
> from DecodeInterval() in datetime.c for DTK_TZ case.
> For example Assert check is commented.
> Why the Assert check is commented out there?
Assert doesn't work in client-side code.
More generally, nobody is maintaining ecpg's copy of the datetime code,
and that's been true for a very long time. I'm not personally
interested in trying to re-sync that copy. It would be more useful to
figure out a way to get rid of it. There have been past discussions of
how we could make a single copy of the code work in both backend and
frontend contexts, but the frontend environment is so impoverished by
comparison (no Assert, no elog, no palloc) that it hasn't looked like an
attractive idea. It's also fairly unclear whether anyone is actually
using ecpg's client-side datetime support, which means there's little
motivation to put a lot of work into it.
regards, tom lane
On Sat, Aug 04, 2012 at 12:11:56PM -0400, Tom Lane wrote: > More generally, nobody is maintaining ecpg's copy of the datetime code, > and that's been true for a very long time. I'm not personally Well, maintaining as in fixing bugs I do as I find time. Maintaining as in keeping in sync with the backend nobody does, you're right. However, as long as the code works and the internal data representation isn't changed or used, that's not really a problem. > interested in trying to re-sync that copy. It would be more useful to > figure out a way to get rid of it. There have been past discussions of +1 > how we could make a single copy of the code work in both backend and > frontend contexts, but the frontend environment is so impoverished by > comparison (no Assert, no elog, no palloc) that it hasn't looked like an > attractive idea. It's also fairly unclear whether anyone is actually > using ecpg's client-side datetime support, which means there's little > motivation to put a lot of work into it. This is an interesting question. I used to think that even ecpg is not used very often anymore but I keep running into large scale usages from time to time. And at least some do use pgtypeslib, too. Michael -- Michael Meskes Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org) Michael at BorussiaFan dot De, Meskes at (Debian|Postgresql) dot Org Jabber: michael.meskes at googlemail dot com VfL Borussia! Força Barça! Go SF 49ers! Use Debian GNU/Linux, PostgreSQL