Received: from ara.zf.jcu.cz (ara.zf.jcu.cz [160.217.161.4]) by postgresql.org (8.11.3/8.11.4) with ESMTP id g02D4Am27378 for ; Wed, 2 Jan 2002 08:04:10 -0500 (EST) (envelope-from zakkr@zf.jcu.cz) Received: from ara.zf.jcu.cz (zakkr@LOCALHOST [127.0.0.1]) by LOCALHOST (8.12.0.Beta7/8.12.0.Beta7/Debian 8.12.0.Beta7-1) with ESMTP id g02D488r004869; Wed, 2 Jan 2002 14:04:08 +0100 Received: (from zakkr@localhost) by ara.zf.jcu.cz (8.12.0.Beta7/8.12.0.Beta7/Debian 8.12.0.Beta7-1) id g02D478b004867; Wed, 2 Jan 2002 14:04:07 +0100 Date: Wed, 2 Jan 2002 14:04:07 +0100 From: Karel Zak To: pgsql-patches Cc: Tom Lane , Aasmund Midttun Godal Subject: Re: [SQL] Unpredictable text -> date implicit casting behaviour/to_date bug Message-ID: <20020102140407.C11749@zf.jcu.cz> References: <20011231143815.8427.qmail@213-145-170-138.dd.nextgentel.com> <12926.1009814567@sss.pgh.pa.us> Mime-Version: 1.0 Content-Type: multipart/mixed; boundary="tKW2IUtsqtDRztdT" Content-Disposition: inline User-Agent: Mutt/1.2.5i In-Reply-To: <12926.1009814567@sss.pgh.pa.us>; from tgl@sss.pgh.pa.us on Mon, Dec 31, 2001 at 11:02:47AM -0500 X-Archive-Number: 200201/1 X-Sequence-Number: 2851 --tKW2IUtsqtDRztdT Content-Type: text/plain; charset=us-ascii Content-Disposition: inline On Mon, Dec 31, 2001 at 11:02:47AM -0500, Tom Lane wrote: > "Aasmund Midttun Godal" 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 http://home.zf.jcu.cz/~zakkr/ C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz --tKW2IUtsqtDRztdT Content-Type: application/x-gzip Content-Disposition: attachment; filename="formatting-01022002.patch.gz" Content-Transfer-Encoding: base64 H4sICA0CMzwAA2Zvcm1hdHRpbmctMDEwMjIwMDIucGF0Y2gAxVZrb9NYEP2c /IpRV6VJ7Zv4EZImbFB5bGFBFStgtXyoZDl+NIbYLr62slXhv+/clx03dmME 0laq5+jemXtnzpmx40dhCIQkwZaE0SYAUgB5DiSDdOOPaeaNV673JUj8cZFH Gzp2/Xwcplns5nmUXI88wMAObn1CSMcTe5dpAi8DD0wDzOnCniyMx2AZhtnX NK3jdb1/Ah/euAmAhccsbGNhTtkZVv/8HIg9M3U8XGN2Bufnfejhn+/7ukBx rADV+4SBW/yrYIkU0Psat4GbyciVJ0G0lWCLgF8+t8Xlc4NZdjucjvvwWxT6 QQgv/3j+9yvn4zvn4v27S+fF62fvcQs3oiRQe5cXLwbgfIIhXLH7g016PRBb DDppkd8UuQ5HzHFxlazXcOxfJW7MzY0wccQNpdJI4IunMsLGMoQuuGVccLDy uIm23Gyl4U9PbH3lz8/8WfDwIx2T1v6HpFGan0uaiThwPg3J0/VaB4HcWKGb EsWRQpTW41jCas8vwQ4qYVydxs8g5Rm8EeUe9phE2GQSbSskmVaBrDe7B1ZZ e6XzVwU+K1DQ4ZP99iybEx7UGVWmgVdXJ44SIYjvih5bp4XYuVUrsQJJRH2a i7VUhB3pV3zGLHs+1c9As9iwnakJv5MTmeT49DzMXIwvpZ6bhIMoSXlmx75+ bNhM9Ees9kd5HHqCeCyWB1QroC1hgH1zim8Zw8B9rfuBWLMKqFb2DgSRdBbk RZYAzTM/uo5y6myCRFwwBA1s/P/w9s+/nI+v8/WAFiELxKjvgo2JOdNNC+mY mBMG7vERwuCDc3HJ4+DbN4iokwT/5g4NbtzMzdNskKR+MBwK9zvBwX6RR/e5 0g76CQr4sQ9WSMBsqRBL5CbY0ODh/IxJ1wzrng05ttEtk1GsT2z8uiDpE3zR l00oXFi6wIgPN+41LJdQvu2HUprGzpRdVNbQ1nB1v51GE8/xqbDYZTbhbANz WqjV3gk23wmMRiM4mc/nJ7AEE1f4gokLlZ9R+hnCD7+xws8yKr+xAKzenU5/ umQ9PpQi1EeANb9MmSvb5GMJn9p8WK2DwJ6rLHC/PJHqTKU67LfAL1LHqqnT Ko7VIM6eMFaTMEiz4nuKdNf5ns4rt1klH3cz57O6fNCuy+8wa1HF6qCKOd9X pW14G1Q5E6o8NizdtH+JKuYu222amF3nxdyVBdzN1r2lmC8Sc6/ZD/eq0ZUV G3+36hP8nNmGNas+Z9IDWACWrlQc8Xcb4yKPR3ns8BSWUG7hNww3SzLFOvPn /GGp5FChRJTZfAMWJYolmNfePeoicRVRf/LOes+TLh1PuvQ7d7p3WVv+kh9J Z7kthoL0ajGlsEQOxP6+GIdmLh4mw94n4+B7mXR6K/8QHS18qJd3Q8UHGKm6 Q9vpWTZzbHAaUkAsfgaV3iuPTfxd/z/rV9Nruw4AAA== --tKW2IUtsqtDRztdT--