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
