[BUGS] BUG #1517: SQL interval syntax is accepted by the parser, but the interpretation is bogus
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 Details: The parser accepts SQL interval syntax, but then silently ignores it, treating it as a zero interval. radius=# select date '2005-01-01' + interval '1' month; ?column? - 2005-01-01 00:00:00 (1 row) radius=# select timestamp '2005-01-1 00:00:00' + interval '1' minute; ?column? - 2005-01-01 00:00:00 (1 row) radius=# ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[BUGS] BUG #1518: Conversions to (undocumented) SQL year-month and day-time interval types silently discard data
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 discard data
Details:
Conversions to the (undocumented) SQL year-month and day-time intervals
silently discard data, instead of raising an exception.
Note, the following examples intentinally use non-standard interval syntax,
since SQL standard interval syntax appears to be broken...
radius=# create table foo (year_month interval year to month);
CREATE TABLE
radius=# insert into foo values ('1 year 1 month');
INSERT 19963 1
radius=# select * from foo;
year_month
--
1 year 1 mon
(1 row)
-- correct
radius=# insert into foo values ('1 hour 1 minute');
INSERT 19964 1
-- should be an error, I think?
radius=# select * from foo;
year_month
--
1 year 1 mon
00:00:00
(2 rows)
-- but instead the interval has been replaced by a zero interval
radius=# create table bar (day_time interval day to second);
CREATE TABLE
radius=# insert into bar values ('1 hour 1 minute');
INSERT 19968 1
radius=# select * from bar;
day_time
--
01:01:00
(1 row)
-- correct
radius=# insert into bar values ('1 year 1 month');
INSERT 19969 1
-- should be an error, I think?
radius=# select * from bar;
day_time
--
01:01:00
00:00:00
(2 rows)
-- but instead has been converted to a zero interval
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[BUGS] BUG #1516: DOC BUG: Interval type syntax and interval literal syntax
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 -- 8.5 of the manual gives the syntax of the interval type as interval [ (p) ] However PostgreSQL seems to support more of the SQL standard syntax than this... eg the following type is not accepted in CREATE TABLE, but is clearly stored as distinct from an unadorned interval type, since it is echoed back unchanged by \d interval year to month The complete SQL syntax isn't supported, however, eg the following is rejected by the parser: internal month(3) Interval literals - 8.5.1.4 of the manual gives the syntax for interval literals as [EMAIL PROTECTED] quantity unit [quantity unit...] [direction] and then comments: The optional precision p should be between 0 and 6, and defaults to the precision of the input literal. But the specified syntax doesn't include a precision p. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [BUGS] BUG #1516: DOC BUG: Interval type syntax and interval literal
> 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]
Re: [BUGS] BUG #1517: SQL interval syntax is accepted by the parser,
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 reference is Date & Darwen's "A guide to the SQL standard") However, it may be better if the PostgreSQL parser rejected the syntax. The current behaviour would seem to be a smoking gun for people porting ANSI-compliant SQL applications (assuming such things exist :) to PostgreSQL. -roy ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [BUGS] BUG #1518: Conversions to (undocumented) SQL year-month and
> "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 that variability Bruce> seems to be considered acceptable.) Bruce> This is why the interval data type store both seconds and Bruce> months. Indeed. ANSI SQL regards these as two distinct kinds of interval data types. There are day-time intervals, which store some combination of days, hours, minutes and seconds, and are broadly equivalent to the seconds field in PostgreSQL intervals, and there are year-month intervals, which store some combination of years and months, and are broadly equivalent to the months field in PostgreSQL intervals. PostgreSQL instead implements a single hybrid interval data type, that stores both. This isn't problematic in itself. However, in ANSI SQL no casts exist between day-time intervals and year-month intervals. PostgreSQL effectively implements a cast that always returns a zero interval; it should raise an exception. Actually, it looks like the underlying problem is more basic than all this. Here's an example entirely with year-month intervals. radius=# create table foo (a interval year); CREATE TABLE radius=# insert into foo values (interval '1 year 1 month'); INSERT 20947 1 radius=# select * from foo; a 1 year (1 row) I don't have a copy of the spec, but according to "A guide to the SQL standard" conversions like this that would discard data are supposed to raise an exception. Ok, and how about this one, which is far worse: radius=# create table bar (a interval month); CREATE TABLE radius=# insert into bar values (interval '1 year 1 month'); INSERT 20956 1 radius=# select * from bar; a --- 1 mon (1 row) The ANSI-compliant answer is 13 months, but PostgreSQL returns 1 month! It seems to me that the ANSI compatible interval stuff should be disabled by default, since it clearly doesn't work yet :-/ Like the ANSI interval literal issue (bug 1517) this is another smoking gun for porters... -roy ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [BUGS] BUG #1518: Conversions to (undocumented) SQL year-month and
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 TO MONTH to the data type INTERVAL YEAR, but the conversion should raise an exception if the value is not an integral number of years... -roy ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [BUGS] BUG #1517: SQL interval syntax is accepted by the parser,
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 broadcast)--- TIP 8: explain analyze is your friend
Re: [BUGS] BUG #1546: Temp table isn't deleted at the end of a
> 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 temp tables. FWIW, this is documented behaviour (section 35.1 of the manual). -roy ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [BUGS] BUG #1546: Temp table isn't deleted at the end of a
>>>>> "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> -roy ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [BUGS] BUG #1517: SQL interval syntax is accepted by the parser,
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 support for ANSI SQL interval data types and litereals doesn't :-/ -roy ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [BUGS] BUG #1517: SQL interval syntax is accepted by the parser,
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 '1 day 1 hour' day to second Personally I think it would be a bad idea to allow hybrid SQL/postgres syntax like this. IMHO, you should either write interval '1 day 1 hour' (postgres style), or interval '1 1:00:00' day to second (SQL style.) Hmm, except writing the above has just raised another question. Is that what the postgres-ism really means (I think it does) or does it mean interval '1 1' day to hour Once you start distinguishing your interval types, does this become important? Actually, I can't immediately see a case where it would matter, but that doesn't mean there isn't one... -roy ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [BUGS] BUG #1517: SQL interval syntax is accepted by the parser,
> "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 that has the DAY TO Tom> SECOND typmod. I don't know anything about the postgres internals, but I don't see it has to be this way. INTERVAL '1 day 1 hour' DAY TO SECOND won't occur in any existing dump file. But if it's important to treat this the same as casting the string '1 day 1 hour' to type INTERVAL DAY TO SECOND then yes, you'll have to accept it. But this is just syntax; I don't see why you have to interpret it that way... But on refelction if you want to treat INTERVAL 'postgres-interval' ansi-interval-type as equivalent to CAST (INTERVAL 'postgres-interval' AS INTERVAL ansi-interval-type) that's probably not unreasonable. Though it creates an inconsistency with the current (undocumented) postgresism of treating INTERVAL '1' as INTERVAL '1 second' since clearly you can't treat the ANSI interval INTERVAL '1' HOUR as CAST (INTERVAL '1 second' AS INTERVAL HOUR) -roy ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] BUG #1517: SQL interval syntax is accepted by the parser,
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 ANSI interval syntax is very different from postgres interval syntax. In ANSI interval syntax the literal string can only be interpreted in the context of the interval type; in postgres interval syntax the literal string has a well defined meaning in and of itself, and no interval type is explicitly declared. So I think I'm back to where I started. Attempting to define semantics for a hybrid format, where you have an ANSI interval type but the literal string formatted in postgres interval format is unnecessarity confusing and complicated. -roy ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [BUGS] BUG #1517: SQL interval syntax is accepted by the parser,
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 ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [BUGS] BUG #1517: SQL interval syntax is accepted by the parser,
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 for interval literals. Note too that this isn't just some optional qualifier, it's presence is mandatory in ANSI SQL, since without it you can't interpret the string. I had a brief look at the standard, but I don't know my way around it and couldn't immeidately find where this is specified... -roy ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [BUGS] BUG #1517: SQL interval syntax is accepted by the parser,
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 specification is included in the literal string. -roy ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] BUG #1517: SQL interval syntax is accepted by the parser,
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 Essentially the full syntax for a day-time interval is INTERVAL '1 2:03:04' DAY TO SECOND and the full syntax of a year-month interval is INTERVA: '1-2' YEAR TO MONTH but if you use a more restricted interval type you omit the fields that aren't present in your interval type. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [BUGS] BUG #1517: SQL interval syntax is accepted by the parser,
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 TO SECOND INTERVAL '1500' MINUTE INTERVAL '1500:00' MINUTE TO SECOND INTERVAL '9' SECOND all of which are equivalent to the postgres interval INTERVAL '1 day 1 hour' but all of them have (in ANSI SQL) disitinct data types. At this point you may decide that ANSI intervals are (to put it politely) somewhat overcomplex... :-) -roy ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [BUGS] BUG #1518: Conversions to (undocumented) SQL year-month and
> "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 MONTH You may as well use the correct ANSI terminology: * Add support for ANSI day-time INTERVAL syntax, INTERVAL '1 2:03:04' DAY TO SECOND * Add support for ANSI year-month INTERVAL syntax, INTERVAL '1-2' YEAR TO MONTH Bruce> * Process mixed ANSI/PG INTERVAL syntax, and round value Bruce> to requested precision Never round, I think. Throwing away precision should be an exception, unless the discarded fields were zero. Bruce> Interpret INTERVAL '1 year' MONTH as CAST (INTERVAL '1 Bruce> year' AS INTERVAL MONTH), and this should return '12 Bruce> months' Bruce> Is this sufficient? 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, of course. I'll have a think about if there's anything else... -roy ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] BUG #1517: SQL interval syntax is accepted by the parser,
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/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [BUGS] BUG #1518: Conversions to (undocumented) SQL year-month and
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)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] BUG #1517: SQL interval syntax is accepted by the parser,
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 looking at '1:30' whether it is PG or ANSI, and that Bruce> will affect how we deal with the clause after it. It could be either in ANSI: INTERVAL '1:30' MINUTE TO SECOND INTERVAL '1:30' HOUR TO MINUTE Similarly, pg interprets INTERVAL '1' as INTERVAL '1 second' In ANSI, it could be any one of INTERVAL '1' SECOND INTERVAL '1' MINUTE INTERVAL '1' HOUR INTERVAL '1' DAY INTERVAL '1' MONTH INTERVAL '1' YEAR In ANSI you can only parse the string by reference to the interval type. -roy ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [BUGS] BUG #1518: Conversions to (undocumented) SQL year-month and
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> assume once we support ANSI syntax it will work fine too: I guess. It's just that it's something that EXTRACT doesn't currently have to worry about, so it would be easy to overlook. -roy ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [BUGS] BUG #1517: SQL interval syntax is accepted by the parser,
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' are undocumented, too, but it's probably more likely that people are relying on that). -roy ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] BUG #1518: Conversions to (undocumented) SQL year-month and
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 exception. Will have to take a look at the spec when I get a moment... -roy ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [BUGS] BUG #1517: SQL interval syntax is accepted by the parser,
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 believe --- the ISO syntax would require Tom> all fields to be present). The 'constraint' (interval type descriptor or whatever it's really called) is mandatory in standard SQL, I think, so there's no ambiguity anyway, unless anyone is using this undocumented syntax at the moment... ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [BUGS] BUG #1517: SQL interval syntax is accepted by the parser,
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 earlier suggestion, that: * if the interval type descriptor is absent, parse the interval as a traditional postgres interval * if the interval type descriptor is present, parse the interval according to the standard syntax I have no objection to allowing things like '1 hour 10 minutes' DAY TO SECOND but I'm just wondering whether the hybrid syntax is an unnecessary complication. -roy ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] BUG #1517: SQL interval syntax is accepted by the parser,
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 can't at the moment see how constraining a literal is useful) then why *not* use the syntax Brian suggested: INTERVAL DAY TO SECOND '1 hour 10 minutes' This keeps the non-standard postgres syntax and the SQL standard syntax nicely separate... -roy ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] BUG #1517: SQL interval syntax is accepted by the parser,
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 PROTECTED]
Re: [BUGS] BUG #1518: Conversions to (undocumented) SQL year-month and
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 '1 year' AS INTERVAL MONTH) ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [BUGS] BUG #1518: Conversions to (undocumented) SQL year-month
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 put limits on how exactly we can emulate the Tom> spec. Which is where my comment about EXTRACT comes in. They can both be stored as 13 months, but EXTRACT (MONTH FROM ...) should return 1 month or 13 months as appropriate. Surely this isn't a problem, you know the type of the interval? So you _can_ emulate the spec, you just don't use the same internal representation that a naive implementation of the spec would... -roy ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] BUG #1518: Conversions to (undocumented) SQL year-month
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 you'd go about storing 13 in the month field. I don't think there's an analogue of EXTRACT that allows you to set fields, is there? Bruce> The lack of complaints all these years perhaps means people Bruce> either don't care or accept the PG behavior. To be honest, I don't really care :-) I try to write my SQL in as standard a way as possible, in case I later want to port to another database... I would be perfectly happy for INTERVAL '1' MONTH to be a syntax error. I just don't like the fact that it gives me a zero interval. Taking out the ISO support from the parser is a valid fix as far as I'm concerned (though actually making it do the ISO thing would obviously be nicer) -roy ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] BUG #1517: SQL interval syntax is accepted by the parser,
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 '1 hour 10 minutes' as data input into Tom> an interval field just because it has an ISO qualifier. Hmm, but COPY is non-standard, so I'd be happy that it insisted on postgres interval syntax. ANSI interval syntax is confusing in this context, precisely because there is nowhere to actually put an 'interval qualifier' in the literals. Otherwise the fact that ALTERing a table to add a constraint will completely change the semantices of the COPYing data into the table worries me. I don't think that conceptually the qualifier on an interval type is really the same thing as the qualifier on an interval literal. Tom> Also, I would personally prefer to see the output from an Tom> interval field remain in the Postgres format Seconded. But that's fine, too, I think. Pretty much everything about an interactive SQL session is implementation defined. If people are using embedded SQL, or one of the libraries, than isn't it a non-issue? -roy ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [BUGS] BUG #1517: SQL interval syntax is accepted by the parser,
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 interval type, just like for literals? Hmm, yes,
I think it probably does...
That convinces me that you have to figure out what to do with
CAST ('1 year 1 month' AS INTERVAL MONTH)
It _still_ doesn't completely convince me that you need to (or even
want to) support hybrid interval syntax.
Though given you have to support both pg and ISO syntax in the cast, I
guess it becomes more consistent (and less implementation work) to
support both in the literal, too...
I'm concinced, I think. (For the moment :)
-roy
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [BUGS] BUG #1517: SQL interval syntax is accepted by the parser,
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 full ISO interval is completely unambigous. Where you run into problems is where you are casting strings to intervals, in which case the way the string is parsed is (rather unncessarily) dependent on the exact type of the interval. This area certainly seems (over) complex, although you can see the logic behind the design... -roy ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [BUGS] BUG #1518: Conversions to (undocumented) SQL year-month and
> "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, of course. Bruce> Uh, I think this already works fine for PG syntax, and I Bruce> assume once we support ANSI syntax it will work fine too: Ok, so based on Tom's comments it sounds like this isn't a non-issue; in fact it sounds like it will be very hard to make this work (at least without changing the internal represnation of an interval). So perhaps there should be something on the TODO list, even if only: o figure out whether it is feasible to make EXTRACT obey ISO standard semantics. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] BUG #1517: SQL interval syntax is accepted by the parser,
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 considering casts of strings to intervals... -roy ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [BUGS] BUG #1517: SQL interval syntax is accepted by the parser,
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. -roy ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
