Re: Bug in date arithmetic - Mailing list pgsql-hackers

David Fetter <david@fetter.org> wrote: 
> On Mon, Aug 24, 2009 at 07:48:06PM -0400, Tom Lane wrote:
>> "Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
>> > I realize I'm in a minority on this, but I would also prefer an
>> > error.  I expect things like
>> >
>> > SELECT "date" + (INTERVAL '1' YEAR)
>> >
>> > to just work.
>> That certainly works, and I didn't hear anyone proposing to change
>> it.
Agreed.  I never meant to suggest otherwise.
>> The issue is about undecorated literals.
At least for my part, I was talking about literals decorated with
apostrophes -- what the spec calls "character string literals".
>> If we start throwing errors for those, the fallout will
>> make the 8.3 implicit-cast changes look like a day at the beach.
Yeah.  I'm sort of resigned to that behavior.  I'd be pushing a GUC to
make it optional if I thought there was any chance of that being
acceptable to the community.  I wouldn't consider suggesting a change
to the default behavior because of the backwards compatibility issues.
>> I believe that it would also violate the SQL spec in numerous
>> places --- whether you like it or not, the concept of
>> context-dependent type resolution is built into the standard.
> 
> As far as you can tell, does the standard speak to adding an untyped
> literal to a time format?  The draft standard I have here lists,
> "Valid operators involving datetimes and intervals" as,
> 
> Operand 1 Operator Operand 2 Result Type
> Datetime     *      Datetime  Interval
> Datetime   + or *   Interval  Datetime
> Interval     +      Datetime  Datetime
> Interval   + or *   Interval  Interval
> Interval   * or /   Numeric   Interval
> Numeric      *      Interval  Interval
> 
> It's not crystal clear to me whether any type coercion behavior is
> mandated here, or which kind, if there is some.
Unless there's been some change in recent versions of the spec which I
haven't picked up on, the PostgreSQL treatment of character string
literals is novel.  I don't remember any concept of an "undecorated"
character string literal being of type "unknown" and therefore easily
taken to be non-character types, outside of the explicit literal
declarations for other types where the character string literal is
preceded by a keyword to cause special treatment.
B'00101010'  -- bit string literal
X'01FE'  -- hexadecimal literal
DATE '2009-12-31'  -- date literal
etc.
If there's something I've missed, I'd love for someone to tell me what
section of which version of the spec to read.
This one is not something I lose any sleep over, though.  Our main
body of production software uses a framework where we parse the SQL
and emit query classes.  Our parser is more strict.  It's just the ad
hoc queries where we see people getting surprised by such issues as
the post which started this thread.  When they do something like that,
once I've figured out their problem, I just point out that I've always
recommended the ANSI form for literals.
-Kevin