Re: INTERVAL type: SQL92 implementation

(back on list)

> As far as I can see, it is the same. My examples come from Cannan and Otten
> on SQL92, but I read the spec for SQL99 and I can't see any obvious
> change, except that INTERVAL YEAR TO YEAR (and any other X TO X) is no
> longer allowed. (I take it you have a copy of SQL99?)

We have a copy of an SQL99 draft which seems to be reasonably complete.
afaik we haven't come across an actual released version. Let me know if
you want me to forward it; perhaps it is on the ftp or web site?

> >o We need to figure out how to parse it in gram.y. I looked at it a
> >little bit (a couple of hours?) and it was not obvious how to get rid of
> >shift/reduce problems.
> I don't have any deep knowledge of yacc/bison...yet.

Oh, you will... ;)

> I feel unhappy about multiplying interval types like that. I would rather
> restrict it to interval (as now), intervalym (YEAR TO MONTH) and intervalds
> (DAY TO SECOND), with the parameters determining the interval range.

But that means (perhaps?) that you can't define a column INTERVAL DAY,
since internally everything would accept all values DAY TO SECOND. I
know you proposed setting an internal mask, but that would be per-value,
not per-column, so it doesn't help. The attribute system may not be much
help here either, unless we somehow generalize it (to allow types to
keep their own impure storage?).

> otherwise we would have 13 new types and would need to make conversion
> functions for all of them. SQL99 says that YEAR TO MONTH and DAY TO SECOND
> are incompatible; the results of other combinations give the combined
> maximum range: DAY TO HOUR + HOUR TO SECOND = DAY TO SECOND, but I don't
> see this as being outside the capabilities of the 2 new types I propose.
> Is there some reason in the internals why it would be necessary to create all
> 13 new types?

3 for YEAR/MONTH, and 10 for DAY/HOUR/MIN/SEC to get all the
combinations. If you convert to a "super interval" for internal
operations, then you may only need the I/O and conversion functions,
which would be easy.

My example still holds as a test case to evaluate an implementation
afaik:

create table t (id interval day);
insert into t(id) select interval '2' day + interval '05' minute;

will need to be stored with only the day field non-zero. Certainly that
column can not be allowed to end up holding quantities other than
integral days, right?

Also, the column defined above has no ability to enforce the "day only"
character of the column if we are using only a single type and without
help from the type or attribute system already in place.

> As I said above, I feel that this is to over-complicate things...

Hmm, but it may be a required minimum level of complication to meet the
spec. Given the arcane syntax and limited functionality (note the
gratuitous editorializing ;) it probably isn't worth doing unless it
gets us on an obvious path to SQL99-compliant functionality.

Also, it is one of the edge cases for SQL99, so even if it is a pain to
do we are only doing it once. They couldn't possibly come up with
anything uglier for SQL0x, could they? Please say no...

...
> the distinction between YEAR TO MONTH and DAY TO SECOND is one that is
> present in the existing interval type, so perhaps we could even get away with
> only one new type?

Not sure what you mean here. The existing type does keep years/months
stored separately from the days/hours/minutes/seconds (a total of two
internal fields) but SQL99 asks that these be kept completely away from
each other from what you've said. Does it define any arithmetic between
the two kinds of intervals?

- Thomas

Good day,

Sorry to post to this list about a patch, but I seem to be having some
difficult getting on the pgsql-patches list; keep getting an "illegal
command" when I send it "subscribe", for some reason. At any rate:

In documenting the to_char() function for transformation of numbers to
text, I noticed that the "RN" template character sequence was displaying
some unusual behavior; specifically, unless in fill mode (with the "FM"
sequence), it would either return the result of the last query executed
derived from a to_char() result, or what appears to be a garbage pointer
if there was no such last query.

Example output from PostgreSQL 7.1.3:
-------------------------------------------------------
lx=# SELECT to_char(485, 'RN');
to_char
-----------------
UERY :command 1
(1 row)

lx=# SELECT to_char(485, 'FMRN');
to_char
---------
CDLXXXV
(1 row)

lx=# SELECT to_char(485, 'RN');
to_char
---------
CDLXXXV
(1 row)

lx=# SELECT to_char(1000, 'RN');
to_char
---------
CDLXXXV
(1 row)

lx=# SELECT 1, 2, to_char(900, '999');
?column? | ?column? | to_char
----------+----------+---------
1 | 2 | 900
(1 row)

lx=# SELECT to_char(485, 'RN');
to_char
---------
900
(1 row)
-------------------------------------------------------

Upon looking into src/backend/utils/adt/formatting.c, I noticed that for
RN transforms:

strcpy(Np->inout_p, Np->number_p);

was only being called within the IS_FILLMODE if block. Moving it out, and
above that check seems to correct this behavior, and I've attached Patches
for both today's pgsql CVS snapshot and postgresql-7.1.3. Both compile,
but I've only tested the latter since my data path is not setup for
pre-7.2, and it seems like a fairly small change.

I consider myself a competent programmer, but never having hacked on
Postgres, I'm not 100% sure that this modification is totally correct
(e.g., if there are any strange side-effects from doing this?), since I'm
not even sure what the Np pointers are achieving in this instance. ;) I'm
guessing its copying the actual output result into the output value's
char* pointer, as that would explain the garbage pointer if it was never
copied.

Any explanation would be greatly appreciated, as I'd like to document this
apparent bug correctly.

Regards,
Jw.
--
jlx(at)commandprompt(dot)com - John Worsley @ Command Prompt, Inc.
by way of pgsql-hackers(at)commandprompt(dot)com

Thomas Lockhart <lockhart(at)fourpalms(dot)org> wrote:
> We have a copy of an SQL99 draft which seems to be reasonably complete.
> afaik we haven't come across an actual released version. Let me know if
> you want me to forward it; perhaps it is on the ftp or web site?

ftp://ftp.postgresql.org/pub/doc/sql/sql1998.tar.gz

Mostly the same files are at
http://gatekeeper.research.compaq.com/pub/standards/sql/
(or ftp).

I didn't know until recently that the ANSI standard was available in PDF
form for an almost reasonable price ($18/part) compared to the outrageous
ISO price ($98 to $275 per part).

See http://webstore.ansi.org/ansidocstore/find.asp?find_spec=sql

[...]
> Not sure what you mean here. The existing type does keep years/months
> stored separately from the days/hours/minutes/seconds (a total of two
> internal fields) but SQL99 asks that these be kept completely away from
> each other from what you've said. Does it define any arithmetic between
> the two kinds of intervals?

No. Days/hours/minutes/seconds are exact quantities whereas years and
months are not, so they don't mix.

I seem to have the complete released (I think) SQL99 docs. If anyone wants
them - just reply to me personally. Should they be put on the postgres
site? Is that legal?

Chris

> -----Original Message-----
> From: pgsql-hackers-owner(at)postgresql(dot)org
> [mailto:pgsql-hackers-owner(at)postgresql(dot)org]On Behalf Of Ken Hirsch
> Sent: Sunday, 2 September 2001 5:18 AM
> To: Hackers List
> Subject: Re: [HACKERS] INTERVAL type: SQL92 implementation
>
>
>
> Thomas Lockhart <lockhart(at)fourpalms(dot)org> wrote:
> > We have a copy of an SQL99 draft which seems to be reasonably complete.
> > afaik we haven't come across an actual released version. Let me know if
> > you want me to forward it; perhaps it is on the ftp or web site?
>
> ftp://ftp.postgresql.org/pub/doc/sql/sql1998.tar.gz
>
> Mostly the same files are at
> http://gatekeeper.research.compaq.com/pub/standards/sql/
> (or ftp).
>
> I didn't know until recently that the ANSI standard was available in PDF
> form for an almost reasonable price ($18/part) compared to the outrageous
> ISO price ($98 to $275 per part).
>
> See http://webstore.ansi.org/ansidocstore/find.asp?find_spec=sql
>
> [...]
> > Not sure what you mean here. The existing type does keep years/months
> > stored separately from the days/hours/minutes/seconds (a total of two
> > internal fields) but SQL99 asks that these be kept completely away from
> > each other from what you've said. Does it define any arithmetic between
> > the two kinds of intervals?
>
> No. Days/hours/minutes/seconds are exact quantities whereas years and
> months are not, so they don't mix.
>
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>

On Fri, Aug 31, 2001 at 07:28:50PM -0700, Command Prompt, Inc. wrote:

> In documenting the to_char() function for transformation of numbers to
> text, I noticed that the "RN" template character sequence was displaying
> some unusual behavior; specifically, unless in fill mode (with the "FM"
> sequence), it would either return the result of the last query executed
> derived from a to_char() result, or what appears to be a garbage pointer
> if there was no such last query.

You are right it's bug. For the 'RM' in non-fillmode is to_char() quiet.
I will fix it for 7.2.

> I consider myself a competent programmer, but never having hacked on
> Postgres, I'm not 100% sure that this modification is totally correct

I check it and if it's good solution we use it.

Thanks!

Karel

PS. Bruce, please, can you apply my previous (31 Aug) patch with to_char()
stuff? I want fix this bug in really actual CVS code. Thanks.

--
Karel Zak <zakkr(at)zf(dot)jcu(dot)cz>
http://home.zf.jcu.cz/~zakkr/

C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz

> On Fri, Aug 31, 2001 at 07:28:50PM -0700, Command Prompt, Inc. wrote:
>
> > In documenting the to_char() function for transformation of numbers to
> > text, I noticed that the "RN" template character sequence was displaying
> > some unusual behavior; specifically, unless in fill mode (with the "FM"
> > sequence), it would either return the result of the last query executed
> > derived from a to_char() result, or what appears to be a garbage pointer
> > if there was no such last query.
>
> You are right it's bug. For the 'RM' in non-fillmode is to_char() quiet.
> I will fix it for 7.2.

Karel, I assume you will send in a patch yourself, right?

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026

On Tue, Sep 04, 2001 at 11:37:48AM -0400, Bruce Momjian wrote:
> > On Fri, Aug 31, 2001 at 07:28:50PM -0700, Command Prompt, Inc. wrote:
> >
> > > In documenting the to_char() function for transformation of numbers to
> > > text, I noticed that the "RN" template character sequence was displaying
> > > some unusual behavior; specifically, unless in fill mode (with the "FM"
> > > sequence), it would either return the result of the last query executed
> > > derived from a to_char() result, or what appears to be a garbage pointer
> > > if there was no such last query.
> >
> > You are right it's bug. For the 'RM' in non-fillmode is to_char() quiet.
> > I will fix it for 7.2.
>
> Karel, I assume you will send in a patch yourself, right?

Right. It needs check.

Karel

--
Karel Zak <zakkr(at)zf(dot)jcu(dot)cz>
http://home.zf.jcu.cz/~zakkr/

C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz

I have checked this in CVS and it is working fine. Karel, have you
fixed this? I can't find a place where I have applied a fix for this.

> Good day,
>
> Sorry to post to this list about a patch, but I seem to be having some
> difficult getting on the pgsql-patches list; keep getting an "illegal
> command" when I send it "subscribe", for some reason. At any rate:
>
> In documenting the to_char() function for transformation of numbers to
> text, I noticed that the "RN" template character sequence was displaying
> some unusual behavior; specifically, unless in fill mode (with the "FM"
> sequence), it would either return the result of the last query executed
> derived from a to_char() result, or what appears to be a garbage pointer
> if there was no such last query.
>
> Example output from PostgreSQL 7.1.3:
> -------------------------------------------------------
> lx=# SELECT to_char(485, 'RN');
> to_char
> -----------------
> UERY :command 1
> (1 row)
>
> lx=# SELECT to_char(485, 'FMRN');
> to_char
> ---------
> CDLXXXV
> (1 row)
>
> lx=# SELECT to_char(485, 'RN');
> to_char
> ---------
> CDLXXXV
> (1 row)
>
> lx=# SELECT to_char(1000, 'RN');
> to_char
> ---------
> CDLXXXV
> (1 row)
>
> lx=# SELECT 1, 2, to_char(900, '999');
> ?column? | ?column? | to_char
> ----------+----------+---------
> 1 | 2 | 900
> (1 row)
>
> lx=# SELECT to_char(485, 'RN');
> to_char
> ---------
> 900
> (1 row)
> -------------------------------------------------------
>
> Upon looking into src/backend/utils/adt/formatting.c, I noticed that for
> RN transforms:
>
> strcpy(Np->inout_p, Np->number_p);
>
> was only being called within the IS_FILLMODE if block. Moving it out, and
> above that check seems to correct this behavior, and I've attached Patches
> for both today's pgsql CVS snapshot and postgresql-7.1.3. Both compile,
> but I've only tested the latter since my data path is not setup for
> pre-7.2, and it seems like a fairly small change.
>
> I consider myself a competent programmer, but never having hacked on
> Postgres, I'm not 100% sure that this modification is totally correct
> (e.g., if there are any strange side-effects from doing this?), since I'm
> not even sure what the Np pointers are achieving in this instance. ;) I'm
> guessing its copying the actual output result into the output value's
> char* pointer, as that would explain the garbage pointer if it was never
> copied.
>
> Any explanation would be greatly appreciated, as I'd like to document this
> apparent bug correctly.
>
>
> Regards,
> Jw.
> --
> jlx(at)commandprompt(dot)com - John Worsley @ Command Prompt, Inc.
> by way of pgsql-hackers(at)commandprompt(dot)com

Content-Description:

[ Attachment, skipping... ]

Content-Description:

[ Attachment, skipping... ]

>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026

On Fri, Sep 07, 2001 at 04:23:08PM -0400, Bruce Momjian wrote:
>
> I have checked this in CVS and it is working fine. Karel, have you
> fixed this? I can't find a place where I have applied a fix for this.

It is not fixed and I doubt that it is working fine in current CVS. The
bugfix is in the attached patch. Please apply it. Thanks.

Output must be:

test=# SELECT to_char(485, 'RN');
to_char
-----------------
CDLXXXV
(1 row)

test=# SELECT to_char(485, 'FMRN');
to_char
---------
CDLXXXV
(1 row)

test=# SELECT to_char(1000, 'RN');
to_char
-----------------
M
(1 row)

test=# SELECT to_char(7.2, '"Welcome to"9.9 "release! :-)"');
to_char
-----------------------------
Welcome to 7.2 release! :-)
(1 row)

Thanks to jlx(at)commandprompt(dot)com for this bug report!

Karel

--
Karel Zak <zakkr(at)zf(dot)jcu(dot)cz>
http://home.zf.jcu.cz/~zakkr/

C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz

Your patch has been added to the PostgreSQL unapplied patches list at:

http://candle.pha.pa.us/cgi-bin/pgpatches

I will try to apply it within the next 48 hours.

> On Fri, Sep 07, 2001 at 04:23:08PM -0400, Bruce Momjian wrote:
> >
> > I have checked this in CVS and it is working fine. Karel, have you
> > fixed this? I can't find a place where I have applied a fix for this.
>
> It is not fixed and I doubt that it is working fine in current CVS. The
> bugfix is in the attached patch. Please apply it. Thanks.
>
> Output must be:
>
> test=# SELECT to_char(485, 'RN');
> to_char
> -----------------
> CDLXXXV
> (1 row)
>
> test=# SELECT to_char(485, 'FMRN');
> to_char
> ---------
> CDLXXXV
> (1 row)
>
> test=# SELECT to_char(1000, 'RN');
> to_char
> -----------------
> M
> (1 row)
>
>
> test=# SELECT to_char(7.2, '"Welcome to"9.9 "release! :-)"');
> to_char
> -----------------------------
> Welcome to 7.2 release! :-)
> (1 row)
>
>
> Thanks to jlx(at)commandprompt(dot)com for this bug report!
>
> Karel
>
> --
> Karel Zak <zakkr(at)zf(dot)jcu(dot)cz>
> http://home.zf.jcu.cz/~zakkr/
>
> C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz

[ Attachment, skipping... ]

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026

Patch applied. Thanks.

> On Fri, Sep 07, 2001 at 04:23:08PM -0400, Bruce Momjian wrote:
> >
> > I have checked this in CVS and it is working fine. Karel, have you
> > fixed this? I can't find a place where I have applied a fix for this.
>
> It is not fixed and I doubt that it is working fine in current CVS. The
> bugfix is in the attached patch. Please apply it. Thanks.
>
> Output must be:
>
> test=# SELECT to_char(485, 'RN');
> to_char
> -----------------
> CDLXXXV
> (1 row)
>
> test=# SELECT to_char(485, 'FMRN');
> to_char
> ---------
> CDLXXXV
> (1 row)
>
> test=# SELECT to_char(1000, 'RN');
> to_char
> -----------------
> M
> (1 row)
>
>
> test=# SELECT to_char(7.2, '"Welcome to"9.9 "release! :-)"');
> to_char
> -----------------------------
> Welcome to 7.2 release! :-)
> (1 row)
>
>
> Thanks to jlx(at)commandprompt(dot)com for this bug report!
>
> Karel
>
> --
> Karel Zak <zakkr(at)zf(dot)jcu(dot)cz>
> http://home.zf.jcu.cz/~zakkr/
>
> C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz

[ Attachment, skipping... ]

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026