Thread

1. Problems with avg on interval data type

   PostgreSQL Bugs List <pgsql-bugs@postgresql.org> — 2001-05-17T19:24:39Z

   Jeremy Blumenfeld (jeremy@horizonlive.com) reports a bug with a severity of 1
   The lower the number the more severe it is.
   
   Short Description
   Problems with avg on interval data type
   
   Long Description
   We have recently upgraded from 7.0.3 to 7.1 and a query which used to work is no longer working.
   The query does an avg on an interval column and now gets the error:
   
   ERROR:  Bad interval external representation '0'
   
   We've reproduced this problem with other tables using an interval type.  It can handle count, sum, but not avg.
   
   Sample Code
   
   
   No file was uploaded with this report
   
   
   

2. Re: Problems with avg on interval data type

   Tom Lane <tgl@sss.pgh.pa.us> — 2001-05-18T16:08:07Z

   pgsql-bugs@postgresql.org writes:
   > The query does an avg on an interval column and now gets the error:
   > ERROR:  Bad interval external representation '0'
   
   Sorry about that :-(.  A last-minute tightening of the allowed input
   formats for interval broke avg(interval), but you're the first one to
   notice.
   
   I have corrected this in the sources for 7.1.2, but that will not help
   you much unless you care to re-initdb with 7.1.2.  If you don't want to
   initdb, you can manually correct the erroneous catalog entry with
   
   update pg_aggregate set agginitval = '{0 second,0 second}' where
   aggname = 'avg' and aggbasetype = 1186;
   
   Note you will need to do this in each extant database including
   template1 (or at least all the databases where you plan to use
   avg(interval)).
   
   			regards, tom lane
   
   

3. Re: Problems with avg on interval data type

   Thomas Lockhart <lockhart@alumni.caltech.edu> — 2001-05-18T16:47:10Z

   > We have recently upgraded from 7.0.3 to 7.1 and a query which used
   > to work is no longer working.
   > The query does an avg on an interval column and now gets the error:
   > ERROR:  Bad interval external representation '0'
   
   OK, there is one case of interval constant which is not handled
   correctly in the 7.1.x release -- the simplest interval specification
   having only an unadorned integer. That is a bug, for which I have a
   patch (or patches) available.
   
   Before I post the patch (which should go into the 7.1.2 release as a bug
   fix) I need feedback on a conventions dilemma, which led to the code
   modifications which introduced the bug. Here it is:
   
   Intervals usually indicate a time span, and can be specified with either
   "# units" strings (e.g. '5 hours') or (as of 7.1) as "hh:mm:ss" (e.g.
   '05:00').
   
   A new construct, "a_expr AT TIME ZONE c_expr" is supported in 7.1, per
   SQL99 spec. One of the possible arguments is
   
     a_expr AT TIME ZONE 'PST'
   
   and
   
     a_expr AT TIME ZONE INTERVAL '-08:00'
   
   It is this last style which leads to the problem of how to interpret
   signed or unsigned integers as interval types. For example, in this
   context
   
     INTERVAL '-8'
   
   must be interpreted as having units of "hours", while our historical
   usage has
   
     INTERVAL '8'
   
   being interpreted as "seconds" (even with signed values). Currently, we
   interpret various forms as follows:
   
     Value	Units
     +8	hours
     -8	hours
     8.0	seconds
     8	?? seconds ??
   
   I would propose that the last example should be interpreted in units of
   seconds, but that could be perilously close to the conventions required
   for the signed examples. Comments?
   
                        - Thomas
   
   

4. Re: Problems with avg on interval data type

   Tom Lane <tgl@sss.pgh.pa.us> — 2001-05-18T16:55:55Z

   Thomas Lockhart <lockhart@alumni.caltech.edu> writes:
   >> We have recently upgraded from 7.0.3 to 7.1 and a query which used
   >> to work is no longer working.
   >> The query does an avg on an interval column and now gets the error:
   >> ERROR:  Bad interval external representation '0'
   
   > OK, there is one case of interval constant which is not handled
   > correctly in the 7.1.x release -- the simplest interval specification
   > having only an unadorned integer. That is a bug, for which I have a
   > patch (or patches) available.
   
   I have modified the declaration of avg(interval) to initialize its
   accumulator as '0 second' instead of just '0', so as far as the
   aggregate goes it's not necessary to consider this a bug.
   
   > Currently, we interpret various forms as follows:
   
   >   Value	Units
   >   +8		hours
   >   -8		hours
   >   8.0		seconds
   >   8		?? seconds ??
   
   > I would propose that the last example should be interpreted in units of
   > seconds, but that could be perilously close to the conventions required
   > for the signed examples. Comments?
   
   Yipes.  I do not like the idea that '+8' and '8' yield radically
   different results.  That's definitely going to create unhappiness.
   
   I suggest that the current code is more correct than you think ;-).
   ISTM it is a good idea to require a units field, or at least some
   punctuation giving a clue about units --- for example I do not object to
   '08:00' being interpreted as hours and minutes.  But I would be inclined
   to reject all four of the forms '+8', '-8', '8.0', and '8' as ambiguous.
   Is there something in the SQL spec that requires us to accept them?
   
   			regards, tom lane
   
   

5. Re: Problems with avg on interval data type

   Thomas Lockhart <lockhart@alumni.caltech.edu> — 2001-05-18T23:30:14Z

   > >   Value       Units
   > >   +8          hours
   > >   -8          hours
   > >   8.0         seconds
   > >   8           ?? seconds ??
   > > I would propose that the last example should be interpreted in units of
   > > seconds, but that could be perilously close to the conventions required
   > > for the signed examples. Comments?
   > Yipes.  I do not like the idea that '+8' and '8' yield radically
   > different results.  That's definitely going to create unhappiness.
   
   Yeah, I agree. The ugliness is that an unsigned integer has been
   accepted in the past as "seconds", and would seem to be a reasonable
   assumption.
   
   > I suggest that the current code is more correct than you think ;-).
   > ISTM it is a good idea to require a units field, or at least some
   > punctuation giving a clue about units --- for example I do not object to
   > '08:00' being interpreted as hours and minutes.  But I would be inclined
   > to reject all four of the forms '+8', '-8', '8.0', and '8' as ambiguous.
   > Is there something in the SQL spec that requires us to accept them?
   
   Single-field signed integers (and unsigned integers?) must be acceptable
   for a time zone specification (pretty sure this is covered in the SQL
   spec). Remember that SQL is woefully inadequate for date, time, and time
   zone support, but afaicr a signed integer is one of the few things they
   do specify ;)
   
                         - Thomas
   
   

6. Re: Re: Problems with avg on interval data type

   Peter Eisentraut <peter_e@gmx.net> — 2001-05-19T00:55:12Z

   Tom Lane writes:
   
   > I suggest that the current code is more correct than you think ;-).
   > ISTM it is a good idea to require a units field, or at least some
   > punctuation giving a clue about units --- for example I do not object to
   > '08:00' being interpreted as hours and minutes.  But I would be inclined
   > to reject all four of the forms '+8', '-8', '8.0', and '8' as ambiguous.
   > Is there something in the SQL spec that requires us to accept them?
   
   Our interval is quite a bit different from the SQL version.  In SQL, an
   interval value looks like this:
   
   INTERVAL -'5 12:30:15.3' DAY TO SECOND
   
   The unit qualifier is required.  Consequentially, I would reject anything
   without units, except '0' maybe.
   
   -- 
   Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter
   
   
   

7. Re: Problems with avg on interval data type

   Tom Lane <tgl@sss.pgh.pa.us> — 2001-05-19T01:14:21Z

   Thomas Lockhart <lockhart@alumni.caltech.edu> writes:
   >> I suggest that the current code is more correct than you think ;-).
   >> ISTM it is a good idea to require a units field, or at least some
   >> punctuation giving a clue about units --- for example I do not object to
   >> '08:00' being interpreted as hours and minutes.  But I would be inclined
   >> to reject all four of the forms '+8', '-8', '8.0', and '8' as ambiguous.
   >> Is there something in the SQL spec that requires us to accept them?
   
   > Single-field signed integers (and unsigned integers?) must be acceptable
   > for a time zone specification (pretty sure this is covered in the SQL
   > spec).
   
   But surely there is other context cuing you that the number is a
   timezone?  In any case, you weren't proposing that interval_in
   should accept '8' as a timezone ...
   
   			regards, tom lane
   
   

8. Re: Problems with avg on interval data type

   Thomas Lockhart <lockhart@alumni.caltech.edu> — 2001-05-19T06:21:18Z

   > > Single-field signed integers (and unsigned integers?) must be acceptable
   > > for a time zone specification (pretty sure this is covered in the SQL
   > > spec).
   > But surely there is other context cuing you that the number is a
   > timezone?  In any case, you weren't proposing that interval_in
   > should accept '8' as a timezone ...
   
   In the particular case I mentioned, any context is gone by the time the
   parser gets beyond gram.y (and that is well before the constant is
   evaluated). The general point is that in this case (and perhaps some
   other cases) there is a need to convert an interval into a time zone, so
   the specification of either or both had better be self consistant.
   
   We do not have an explicit timezone type which could make different
   assumptions about units on unadorned integers (and I'd like to avoid
   defining a new type for this purpose since SQL9x seems to think that
   "interval" should be enough). I'd also like to avoid *requiring* the
   full brain damage of SQL9x interval specifications such as Peter
   mentioned; we may support it, but should not require it since it is
   truely horrid.
   
                          - Thomas