Unpredictable text -> date implicit casting behaviour/to_date bug

"Aasmund Midttun Godal" <postgresql@envisity.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

On Mon, Dec 31, 2001 at 11:02:47AM -0500, Tom Lane wrote:

"Aasmund Midttun Godal" <postgresql@envisity.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@zf.jcu.cz>
http://home.zf.jcu.cz/~zakkr/

C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz

Thomas added datetime stuff over the weekend, so I assume this patch
should be applied in the next 24 hours, unless someone objects.

---------------------------------------------------------------------------

Karel Zak wrote:

On Mon, Dec 31, 2001 at 11:02:47AM -0500, Tom Lane wrote:

"Aasmund Midttun Godal" <postgresql@envisity.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@zf.jcu.cz>
http://home.zf.jcu.cz/~zakkr/

C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz

[ Attachment, skipping... ]

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Karel Zak <zakkr@zf.jcu.cz> writes:

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

Patch applied to CVS. Thanks!

regards, tom lane