The following bug has been logged online:
Bug reference: 1517
Logged by: Roy Badami
Email address: [EMAIL PROTECTED]
PostgreSQL version: 8.0.1
Operating system: Solaris 9
Description:SQL interval syntax is accepted by the parser, but the
interpretation is bogus
The following bug has been logged online:
Bug reference: 1518
Logged by: Roy Badami
Email address: [EMAIL PROTECTED]
PostgreSQL version: 8.0.1
Operating system: Solaris 9
Description:Conversions to (undocumented) SQL year-month and
day-time interval types silently
The following bug has been logged online:
Bug reference: 1516
Logged by: Roy Badami
Email address: [EMAIL PROTECTED]
PostgreSQL version: 8.0.1
Operating system: Solaris 9
Description:DOC BUG: Interval type syntax and interval literal
syntax
Details:
Interval types
> eg the following type is not accepted in CREATE TABLE
Oops, meant to write "eg the following type IS accepted in CREATE TABLE"
> interval year to month
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Tom> No, it shouldn't; read the SQL spec. AFAICS the syntax
Tom> select interval '1' month is perfectly spec-compliant. The
Tom> variant select interval '1 month' is *not* per-spec, it is a
Tom> Postgres-ism.
That is my understanding, though I don't have a copy of the spec (my
r
> "Bruce" == Bruce Momjian writes:
Bruce> First, the fundamental issue with intervals is that they
Bruce> are not tied to a particular date, meaning there is no way
Bruce> to map a specific number of days to a number of months.
Bruce> (Some days are also 23 or 25 hours but tha
Roy> I don't have a copy of the spec, but according to "A guide to
Roy> the SQL standard" conversions like this that would discard
Roy> data are supposed to raise an exception.
Just to clarify, my understanding is that in ANSI SQL it is valid to
convert from the data type INTERVAL YEAR
Tom> AFAICS the syntax
Tom> select interval '1' month
Tom> is perfectly spec-compliant.
Well, it's not _perfectly_ spec compliant, because AIUI SELECTs
without FROM clauses are a postgres-ism, too. But I'm just
nitpicking...
-roy
---(end of br
> Given the error message, this seems to be the whole plpgsql caches
> query plans but we don't invalidate those plans when there are schema
> changes. In all currently released versions you pretty much need to
> use EXECUTE on any queries where the table may go away, for example,
> any use of te
>>>>> "Roy" == Roy Badami <[EMAIL PROTECTED]> writes:
Roy> FWIW, this is documented behaviour (section 35.1 of the
Roy> manual).
That's 35.1 in the 8.0 doc, BTW. It's 37.1 in the 7.4 doc.
Roy>
Bruce> So, we have a few major problems with intervals. Let me
Bruce> think a little and I will summarize.
FWIW, AFAICT the problems I reported in bug 1517 and 1518 all relate
to undocumented features of PostgreSQL.
All the documented interval functionality works fine. The
undocumented
Tom> Feel like hacking the code?
Hmm, in principle I might take a look some time; in reality it's
unlikely I'll have time any time soon...
There are some design issues involved, though. If you have the type
modifier, do you isnist on SQL syntax in the string?
ie do you accept
interval '
> "Tom" == Tom Lane <[EMAIL PROTECTED]> writes:
>> ie do you accept interval '1 day 1 hour' day to second
Tom> I think we have to, and the reason is that this isn't
Tom> different under the hood from reading the external value '1
Tom> day 1 hour' and storing it into a column t
Similary the undocumented postgresism of interpreting
INTERVAL '1:02'
as 1 hour 2 minutes is consistent with the ANSI
INTERVAL '1:02' HOUR TO MINUTE
but not with the ANSI
INTERVAL '1:02' MINUTE TO SECOND
which of course means 1 minute 2 seconds.
The fact is that AN
Bruce> select interval day to second '1 day 1 hour'
Bruce> However, we don't support that syntax, only the one with
Bruce> the specification after.
Is that valid ANSI SQL?
-roy
---(end of broadcast)---
TIP 9: the planner will
Bruce> somehow. Right now we use the clause after the string as
Bruce> the date type specification, and I see you saying that the
Bruce> data value specification has to after the string. Is that
Bruce> correct?
Well, that's what 'A guide to the SQL standard' gives as the syntax
f
Bruce> test=> select timestamp with time zone '2004-01-01';
Also, FWIW, according to the postgres doc this is a postgresism. The
'with time zone' clause never occurs in an ANSI timestamp literal;
whether it is a timestamp or a timestamp with time zone depends on
whether a time zone spe
Bruce> I guess my point is that we should allow:
Bruce> select interval '1' day '1' hour
Bruce> as SQL standard and equavalent to:
Ah, I think you're misunderstanding what the SQL standard interval
literal syntax looks like.
It would be
INTERVAL '1 1' DAY TO HOUR
Essential
Roy> It would be
Roy>INTERVAL '1 1' DAY TO HOUR
Actually, it would be any one of the following:
INTERVAL '1 1' DAY TO HOUR
INTERVAL '1 1:00' DAY TO MINUTE
INTERVAL '1 1:00:00' DAY TO SECOND
INTERVAL '25' HOUR
INTERVAL '25:00' HOUR TO MINUTE
INTERVAL '25:00:00' HOUR
> "Bruce" == Bruce Momjian writes:
Bruce> OK, here are the TODO items I have created:
Bruce> * Add support for ANSI time INTERVAL syntax, INTERVAL '1
Bruce> 2:03:04' DAY TO SECOND
Bruce> * Add support for ANSI date INTERVAL
Bruce> syntax, INTERVAL '1-2' YEAR TO MONT
Bruce> * Interpret INTERVAL '1:30' MINUTE TO SECOND as '1
Bruce> minute 30 seconds'
This seems redundant; it's just another example of the ANSI day-time
interval syntax...
-roy
---(end of broadcast)---
TIP 3: if posting/read
Bruce> Is this sufficient?
Also support for ANSI interval data types is incomplete in the parser,
in that it doesn't recognize the precision field.
eg CREATE TABLE foo (a INTERVAL MONTH(3));
fails to parse.
-roy
---(end of broadcast)-
Bruce> The reason I added it is that usually people think of the
Bruce> PG syntax as '1 hour 30 seconds'. The '1:30' is a kind of
Bruce> subtle because both PG and ANSI support that syntax and we
Bruce> need to handle that. The tricky part is that we can't say
Bruce> by looki
Bruce> Seems that is implentation-dependent so I added "round' or
Bruce> error" to the TODO item.
Ah, OK. "A guide to the SQL standard" claims it's an exception, but I
haven't checked the actual standard.
Bruce> Uh, I think this already works fine for PG syntax, and I
Bruce> assu
Bruce> This is going to be a backward compatibility problem, but
Bruce> to support ANSI syntax we are going to need to do this.
Given the existence of the INTERVAL '1' MONTH etc syntax is completely
undocumented, I doubt it's a major problem.
(Actually, INTERVAL '1' and INTERVAL '1:30' a
Bruce> Here is an email stating it is implementation defined:
Bruce> http://archives.postgresql.org/pgsql-bugs/2005-03/msg00162.php
Hmm, looks like I'm mistaken, then. In fact the "whether to truncate
or round" bit suggests you should do one or the other, rather than
raise an except
Tom> That would eliminate the backward-compatibility problem
Tom> pretty well (since the constraints aren't documented and
Tom> hence aren't being used now), while not posing a big problem
Tom> for ISO cases (since if there's no constraint there are no
Tom> ambiguous cases, I be
Roy> The 'constraint' (interval type descriptor or whatever it's
Roy> really called) is mandatory in standard SQL, I think, so
Roy> there's no ambiguity anyway, unless anyone is using this
Roy> undocumented syntax at the moment...
Incidentally, this was the ratinale behind my earli
Roy> I have no objection to allowing things like
Roy>'1 hour 10 minutes' DAY TO SECOND
Roy> but I'm just wondering whether the hybrid syntax is an
Roy> unnecessary complication.
And furthermore, if you really want to allow constrained postgres
syntax interval literals (and I
Tom> We should do that *only* when an ISO (not ANSI)
Just to clarify, is that a distinction or just a clarification? (ie
are ANSI and ISO SQL different?)
-roy
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL
Bruce> o Interpret INTERVAL '1 year' MONTH as
I'm still not convinced that allowing this doesn't just create
unnecessary confusion. How about:
o Decide whether constructs such as INTERVAL '1 year' MONTH should
be allowed, and if so what they should mean, eg perhaps CAST
(INTERVAL
Tom> In order to support the spec *exactly*, we would have to.
Tom> For instance we cannot presently tell the difference between
Tom> '13 months' and '1 year 1 month' ... they both end up stored
Tom> as '13 months'. I can't say that I find this very important,
Tom> but it does
Bruce> What happens if you store '13 months' into an interval
Bruce> column that is YEAR TO MONTH? Does extract MONTH return 1
Bruce> or 13?
In standard SQL the MONTH field of INTERVAL YEAR TO MONTH can't
contain a value greater than 11. Though I don't immediately see how
yo
Tom> In the context of interval literals it's probably
Tom> unnecessary, but that's not the only thing to worry about.
Tom> In particular we have to consider the behavior of the input
Tom> and output routines for cases like COPY. I think it would be
Tom> really bad to reject '
Tom> It's not different from
Tom> INSERT INTO foo VALUES('1 year 1 month');
Tom> Nothing nonstandard about that that I can see.
Oh, does ISO allow a cast from a string to an interval? Yes, I think
it probably does. And with the interpretation of the string is
dependent on the ISO
Tom> Yes. The ISO design for the datatype is pretty brain-dead if
Tom> you ask me --- the basic meaning of a data literal shouldn't
Tom> be so dependent on context.
Arguably it's not, because the interval qualifier is an intrinsic (and
mandatory) part of the literal syntax, so the fu
> "Bruce" == Bruce Momjian writes:
>> You also need to make EXTRACT do the right thing.
>>
>> eg EXTRACT (MONTH FROM INTERVAL '1-1' YEAR TO MONTH) => 1
>>
>> but
>>
>> EXTRACT (MONTH FROM INTERVAL '13' MONTH) => 13
>>
>> Ditto for day-time intervals, o
Tom> As you point out, the ISO spec doesn't actually say that that
Tom> string literal has to be the external textual representation
Tom> ... but it's surely pretty bizarre that it's not a good
Tom> candidate to be the textual representation.
Yes, I'm convicned. Particularly after
test=> select interval '1 hour 1 minute';
interval
--
01:01:00
(1 row)
Hmm, I don't think I really like having a seconds field in the output,
given that the column is by definition only storing data to a
precision of a minute.
39 matches
Mail list logo
