Re: [HACKERS] Patch for SQL-standard negative valued year-month literals
Tom Lane wrote: >"Stephen R. van den Berg" <[EMAIL PROTECTED]> writes: >> Intervals are a scalar, not an addition of assorted values, alternating signs >> between fields would be wrong. >Sorry, you're the one who's wrong on that. We've treated intervals as >three independent fields for years now (and before that it was two >independent fields). Ok, didn't know that. Let's put it differently then: I can understand that the standard considers it a scalar and not an addition, but apparently the addition characteristic is being used in Postgres code already; that makes it undesirable to change it indeed. -- Sincerely, Stephen R. van den Berg. He did a quarter of the work in *half* the time! -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch for SQL-standard negative valued year-month literals
Tom Lane wrote: "Stephen R. van den Berg" <[EMAIL PROTECTED]> writes: Intervals are a scalar, not an addition of assorted values, alternating signs between fields would be wrong. Sorry, you're the one who's wrong on that. We've treated intervals as three independent fields for years now (and before that it was two independent fields). We're not going to throw away that capability. +1 It's very useful. Currently our terse input format that's similar to the SQL standard rejects more mixed-sign intervals than I'd like. I'd be quite happy if: '1 2:03:-04' gave me '1 day 2 hours 3 minutes -4 seconds' but currently we reject that mixed-sign-literal. I'd just like to find a way to have SQL-standard input produce SQL-standard output in the cases where the input happened to match the standard. If we had a blank slate, my vote would be that '-1 2:03:04' should mean what the SQL standard says it should. '-1 +2:03:04' should mean negative 1 days, plus 2 hours 3 minutes 4 sec '1 2:03:-04' should mean 1 day 2 hours 3 minutes minus 4 seconds '-1 2:03:+04' should mean negative 1 day 2 hours 3 minutes plus 4 seconds but I'm aware that there are backward compatibility issues. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch for SQL-standard negative valued year-month literals
"Stephen R. van den Berg" <[EMAIL PROTECTED]> writes: > Intervals are a scalar, not an addition of assorted values, alternating signs > between fields would be wrong. Sorry, you're the one who's wrong on that. We've treated intervals as three independent fields for years now (and before that it was two independent fields). We're not going to throw away that capability. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch for SQL-standard negative valued year-month literals
Tom Lane wrote: >Ron Mayer <[EMAIL PROTECTED]> writes: >> Tom Lane wrote: >> If I read SQL 200N's spec correctly >> select interval '-1 1:00:00'; >> should mean"-1 days -1 hours", >> yet 8.3 sees it as "-1 days +1 hours". >I think we are kind of stuck on this one. If we change it, then how >would one represent -1 days +1 hours? The spec's format is only sane >if you assume all the fields must have the same sign, which is not >the case for PG. -1 days +1 hours = interval '-0 23:00:00' Intervals are a scalar, not an addition of assorted values, alternating signs between fields would be wrong. -- Sincerely, Stephen R. van den Berg. He did a quarter of the work in *half* the time! -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch for SQL-standard negative valued year-month literals
Tom Lane wrote:
If I read SQL 200N's spec correctly
select interval '-1 1:00:00';
should mean"-1 days -1 hours",
yet 8.3 sees it as "-1 days +1 hours".
I think we are kind of stuck on this one. If we change it, then how
would one represent -1 days +1 hours? The spec's format is only sane
I'm not proposing this, but I could imagine making
"-1 -1:00:00" and/or "-1 +1:00:00" mean -1 days +1 hours.
I think if it weren't for backward compatibility issues I'd even
support such an idea - since now we're oh-so-very-close to accepting
to-spec literals. Unfortunately I fear old users might assume the
opposite meaning.
Scary to touch that one, but since a standard's a standard, I think
eventually we should get there.
The SQL spec's date/time handling is, and always has been, broken enough
that I feel no great compulsion to follow every last detail. Especially
details that make it impossible to support our extensions...
In this case we're so very close to meeting the spec, though. And
it's ashame where we accept the to-spec syntax ("-1 1:00:00") but
return a different answer.
And since it's not quite impossible to support our extensions (force
putting the sign on the h:m:s part for mixed sign cases) I feels nice
to me to try.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch for SQL-standard negative valued year-month literals
Ron Mayer <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> Hmm. I'm a bit concerned about possible side-effects on other cases: >> what had been seen as two separate tokens will now become one token >> for *all* datetime types, not just interval. However, I can't > If it's a concern, I could make interval_in first look for the > SQL-standard patterns before even parsing the string into fields. I don't think it's worth the trouble unless someone points out a real-world format that would be broken by the change. We certainly don't document anything that would be. I've applied a patch along these lines and we'll see if anyone complains. > If I read SQL 200N's spec correctly > select interval '-1 1:00:00'; > should mean"-1 days -1 hours", > yet 8.3 sees it as "-1 days +1 hours". I think we are kind of stuck on this one. If we change it, then how would one represent -1 days +1 hours? The spec's format is only sane if you assume all the fields must have the same sign, which is not the case for PG. > Scary to touch that one, but since a standard's a standard, I think > eventually we should get there. The SQL spec's date/time handling is, and always has been, broken enough that I feel no great compulsion to follow every last detail. Especially details that make it impossible to support our extensions... regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch for SQL-standard negative valued year-month literals
Tom Lane wrote:
Ron Mayer <[EMAIL PROTECTED]> writes:
Short summary:
I think this patch fixes a bug with sql-spec negative interval literals.
Hmm. I'm a bit concerned about possible side-effects on other cases:
what had been seen as two separate tokens will now become one token
for *all* datetime types, not just interval. However, I can't
If it's a concern, I could make interval_in first look for the
SQL-standard patterns before even parsing the string into fields.
If we want to handle the SQL standard negative datetime intervals
(see below) the way the spec looks to me,
immediately think of any likely input formats where this would be a
problem.
Something else I noticed while poking at it is this inconsistency:...
Yes. I saw some of those too (and '-1 1:00:00'); but didn't have a
patch ready (yet). I'm happy to work on it.
As soon as you throw in a sign, it gets wacky :-(.
Oh. And looking more closely; there's a potential bad incompatibility.
If I read SQL 200N's spec correctly
select interval '-1 1:00:00';
should mean"-1 days -1 hours",
yet 8.3 sees it as "-1 days +1 hours".
Scary to touch that one, but since a standard's a standard, I think
eventually we should get there. Perhaps we need a GUC to choose
standards or backward compatible behavior for that one? Or perhaps
keep parsing it the old way but with a WARNING for 8.4 and switch in 8.5?
The reason for this bizarreness is this chunk of code at the end of
DecodeInterval's DTK_TZ case:
else if (type == IGNORE_DTF)
{...} }
which means that a signed integer gets forced to be "hours" if there
isn't an explicit unit spec in the literal, while a signed float gets
forced to be "seconds". I can't see any reason why that's a good idea,
and propose that while we're making incompatible changes in corner cases,
we should just drop the code quoted above.
+1. Shall I try for a patch that handles those and possibly the (more
frightening) SQL 200N signed day-time interval mentioned above.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch for SQL-standard negative valued year-month literals
Ron Mayer <[EMAIL PROTECTED]> writes:
> Short summary:
> I think this patch fixes a bug with sql-spec negative interval literals.
Hmm. I'm a bit concerned about possible side-effects on other cases:
what had been seen as two separate tokens will now become one token
for *all* datetime types, not just interval. However, I can't
immediately think of any likely input formats where this would be a
problem.
Something else I noticed while poking at it is this inconsistency:
regression=# select interval '1';
interval
--
00:00:01
(1 row)
regression=# select interval '-1';
interval
---
-01:00:00
(1 row)
regression=# select interval '+1';
interval
--
01:00:00
(1 row)
regression=# select interval '1' day;
interval
--
1 day
(1 row)
regression=# select interval '+1' day;
interval
--
00:00:00
(1 row)
regression=# select interval '-1' day;
interval
--
00:00:00
(1 row)
regression=# select interval '1' hour to minute;
interval
--
00:01:00
(1 row)
regression=# select interval '-1' hour to minute;
interval
---
-01:00:00
(1 row)
regression=# select interval '+1' hour to minute;
interval
--
01:00:00
(1 row)
As soon as you throw in a sign, it gets wacky :-(.
The reason for this bizarreness is this chunk of code at the end of
DecodeInterval's DTK_TZ case:
else if (type == IGNORE_DTF)
{
if (*cp == '.')
{
/*
* Got a decimal point? Then assume some sort of
* seconds specification
*/
type = DTK_SECOND;
}
else if (*cp == '\0')
{
/*
* Only a signed integer? Then must assume a
* timezone-like usage
*/
type = DTK_HOUR;
}
}
which means that a signed integer gets forced to be "hours" if there
isn't an explicit unit spec in the literal, while a signed float gets
forced to be "seconds". I can't see any reason why that's a good idea,
and propose that while we're making incompatible changes in corner cases,
we should just drop the code quoted above.
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
