Skip site navigation (1) Skip section navigation (2)

Re: [SQL] Unpredictable text -> date implicit casting behaviour/to_date bug

From: Karel Zak <zakkr(at)zf(dot)jcu(dot)cz>
To: pgsql-patches <pgsql-patches(at)postgreSQL(dot)org>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Aasmund Midttun Godal <postgresql(at)envisity(dot)com>
Subject: Re: [SQL] Unpredictable text -> date implicit casting behaviour/to_date bug
Date: 2002-01-02 13:04:07
Message-ID: 20020102140407.C11749@zf.jcu.cz (view raw or flat)
Thread:
Lists: pgsql-patchespgsql-sql
On Mon, Dec 31, 2001 at 11:02:47AM -0500, Tom Lane wrote:
> "Aasmund Midttun Godal" <postgresql(at)envisity(dot)com> writes:
> > The implicit casting of text to date is normally quite good, however i
> > think it should only use one method during a given query (or maybe
> > transaction?)
> 
> Try setting DateStyle to match the date style you are using.  Postgres
> is doing the best it can with ambiguous input.
> 
> > Added to the fact that to_date has a bug:
> 
> > SELECT ordredato, ordredato::date, to_date(ordredato, 'DD.MM.YY') from old_faktura where status = 'ANN';
> >  ordredato |  ?column?  |    to_date    
> > -----------+------------+---------------
> >  18.04.00  | 2000-04-18 | 0001-04-18 BC
> >  30.08.01  | 2001-08-30 | 2001-08-30
> >  18.04.00  | 2000-04-18 | 0001-04-18 BC
> 
> I agree, this is not desired behavior.  The problem seems to be that the
> logic in to_timestamp() tries to use "field is not zero" as a substitute
> test for "field was provided".  At least in the case of the year fields,
> this is *not* workable.  There must be a separate flag bit.
> 
> 			regards, tom lane

 Nice bug :-(

 It's fixed and the patch is attached, please apply it to 7.2.

 Thanks for report.

        Karel

-- 
 Karel Zak  <zakkr(at)zf(dot)jcu(dot)cz>
 http://home.zf.jcu.cz/~zakkr/
 
 C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz

Attachment: formatting-01022002.patch.gz
Description: application/x-gzip (1.1 KB)

In response to

Responses

pgsql-patches by date

Next:From: Bruce MomjianDate: 2002-01-02 18:26:31
Subject: Re: [SQL] Unpredictable text -> date implicit casting behaviour/to_date
Previous:From: Brent VernerDate: 2002-01-01 23:01:52
Subject: Re: [PATCHES] system catalog relation of a table and a serial sequence

pgsql-sql by date

Next:From: Alex GliksonDate: 2002-01-02 13:31:00
Subject: OUTER JOIN doesn't work on pgSQL 7.1.2
Previous:From: Christopher Kings-LynneDate: 2002-01-02 03:36:53
Subject: Re: [SQL] An easy question about creating a primary key

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group