Michael Meskes wrote:
> On Wed, May 09, 2007 at 12:46:52PM +0100, Dave Page wrote:
>>> Unfortunately I do not have access to a Vista system I could use to test
>>> and track this one down.
>> I'm happy to run any tests you like.
>
> Dave, could you please apply this small patch to pgtypeslib/datetime.c.
> I still have no clue where the error code is comming from and I'm trying
> to narrow it down a bit.
Hi Michael,
The results are attached. Please let me know if I can do anything more.
Regards, Dave
[NO_PID]: ECPGdebug: set to 1
[NO_PID]: sqlca: code: 0, state: 00000
[NO_PID]: ECPGconnect: opening database regress1 on <DEFAULT> port <DEFAULT>
[NO_PID]: sqlca: code: 0, state: 00000
[NO_PID]: ECPGexecute line 29: QUERY: create table date_test ( d date , ts
timestamp ) on connection regress1
[NO_PID]: sqlca: code: 0, state: 00000
[NO_PID]: ECPGexecute line 29 Ok: CREATE TABLE
[NO_PID]: sqlca: code: 0, state: 00000
[NO_PID]: ECPGexecute line 30: QUERY: set datestyle to iso on connection
regress1
[NO_PID]: sqlca: code: 0, state: 00000
[NO_PID]: ECPGexecute line 30 Ok: SET
[NO_PID]: sqlca: code: 0, state: 00000
[NO_PID]: ECPGexecute line 35: QUERY: insert into date_test ( d , ts ) values
( date '1966-01-17' , timestamp '2000-07-12 17:34:29' ) on connection
regress1
[NO_PID]: sqlca: code: 0, state: 00000
[NO_PID]: ECPGexecute line 35 Ok: INSERT 0 1
[NO_PID]: sqlca: code: 0, state: 00000
[NO_PID]: ECPGexecute line 37: QUERY: select * from date_test where d = date
'1966-01-17' on connection regress1
[NO_PID]: sqlca: code: 0, state: 00000
[NO_PID]: ECPGexecute line 37: Correctly got 1 tuples with 2 fields
[NO_PID]: sqlca: code: 0, state: 00000
[NO_PID]: ECPGget_data line 37: RESULT: 1966-01-17 offset: -1 array: Yes
[NO_PID]: sqlca: code: 0, state: 00000
[NO_PID]: ECPGget_data line 37: RESULT: 1966-01-17 errno 22
[NO_PID]: sqlca: code: 0, state: 00000
[NO_PID]: raising sqlcode -209 in line 37, 'SQL error #-209 in line 37.'.
[NO_PID]: sqlca: code: -209, state: 42804
sql error SQL error #-209 in line 37.
[NO_PID]: ECPGtrans line 350 action = rollback connection = regress1
[NO_PID]: sqlca: code: 0, state: 00000
[NO_PID]: ecpg_finish: Connection regress1 closed.
[NO_PID]: sqlca: code: 0, state: 00000
1: errno = 0
2: errno = 22
3: errno = 22
4: errno = 22
1: errno = 0
2: errno = 22
3: errno = 22
4: errno = 22
Date: 1966-01-17
timestamp: 2000-07-12 17:34:29
interval: @ 13556 days 12 hours 34 mins 14 secs
m: 4, d: 19, y: 1998
date seems to get encoded to julian -622
m: 4, d: 19, y: 1998
date_day of 2003-12-04 17:34:29 is 4
Above date in format "(ddd), mmm. dd, yyyy, repeat: (ddd), mmm. dd, yyyy. end"
is "(Thu), Dec. 04, 2003, repeat: (Thu), Dec. 04, 2003. end"
date_defmt_asc1: 1995-12-25
date_defmt_asc2: 0095-12-25
date_defmt_asc3: 0095-12-25
date_defmt_asc4: 1995-12-25
date_defmt_asc5: 1995-12-25
date_defmt_asc6: 1995-12-25
date_defmt_asc7: 1995-12-25
date_defmt_asc8: 1995-12-25
date_defmt_asc9: 1995-12-25
date_defmt_asc10: 1995-12-25
date_defmt_asc12: 0095-12-25
timestamp_to_asc1: 1996-02-29 00:00:00
timestamp_to_asc2: 1994-02-11 03:10:35
timestamp_to_asc3: 2000-01-01 00:00:00
timestamp_fmt_asc: 0: abc-00:00:00-def-01/01/00-ghi%
timestamp_defmt_asc(This is a 4/12/80 3-39l12test, This is a %m/%d/%y
%H-%Ml%Stest) = 1980-04-12 03:39:12, error: 0
timestamp_defmt_asc(Tue Jul 22 17:28:44 +0200 2003, %a %b %d %H:%M:%S %z %Y) =
2003-07-22 15:28:44, error: 0
timestamp_defmt_asc(Tue Feb 29 17:28:44 +0200 2000, %a %b %d %H:%M:%S %z %Y) =
2000-02-29 15:28:44, error: 0
timestamp_defmt_asc(Tue Feb 29 17:28:44 +0200 1900, %a %b %d %H:%M:%S %z %Y) =
1900-02-28 15:28:44, error (should be error!): 1
timestamp_defmt_asc(Tue Feb 29 17:28:44 +0200 1996, %a %b %d %H:%M:%S %z %Y) =
1996-02-29 15:28:44, error: 0
timestamp_defmt_asc( Jul 31 17:28:44 +0200 1996, %b %d %H:%M:%S %z %Y) =
1996-07-31 15:28:44, error: 0
timestamp_defmt_asc( Jul 32 17:28:44 +0200 1996, %b %d %H:%M:%S %z %Y) =
1996-07-31 15:28:44, error (should be error!): 1
timestamp_defmt_asc(Tue Feb 29 17:28:44 +0200 1997, %a %b %d %H:%M:%S %z %Y) =
1997-02-28 15:28:44, error (should be error!): 1
timestamp_defmt_asc(Tue Jul 22 17:28:44 +0200 2003, %) = 1997-02-28 15:28:44,
error (should be error!): 1
timestamp_defmt_asc(Tue Jul 22 17:28:44 +0200 2003, a %) = 1997-02-28 15:28:44,
error (should be error!): 1
timestamp_defmt_asc( Jul, 22 17_28 `44 +0200 2003 , %b, %d %H_%M`%S %z %Y)
= 2003-07-22 15:28:44, error: 0
timestamp_defmt_asc(Tue Jul %22 17:28:44 CEST 2003, %a %b %%%d %H:%M:%S %Z %Y)
= 2003-07-22 15:28:44, error: 0
timestamp_defmt_asc(Tue Jul %22 17:28:44 CEST 2003, %a %b %%%d %H:%M:%S %Z %Y)
= 2003-07-22 15:28:44, error: 0
timestamp_defmt_asc(abc
19 October %22 17:28:44 CEST 2003, abc%n %C %B %%%d %H:%M:%S %Z %Y) =
2003-10-22 15:28:44, error: 0
timestamp_defmt_asc(abc
18 October %34 17:28:44 CEST 80, abc%n %C %B %%%d %H:%M:%S %Z %y) =
1880-10-31 15:28:44, error (should be error!): 1
timestamp_defmt_asc(abc
18 October %34 17:28:44 CEST 80, ) = 1880-10-31 15:28:44, error (should be
error!): 1
timestamp_defmt_asc(1980-04-12 3:49:44 , NULL) = 1980-04-12 03:49:44,
error: 0
timestamp_defmt_asc(July 14, 1988. Time: 9:15am, %B %d, %Y. Time: %I:%M%p) =
1988-07-14 09:15:00, error: 0
timestamp_defmt_asc(September 6 at 01:30 pm in the year 1983, %B %d at %I:%M %p
in the year %Y) = 1983-09-06 13:30:00, error: 0
timestamp_defmt_asc( 1976, July 14. Time: 9:15am, %Y, %B %d. Time: %I:%M %p)
= 1976-07-14 09:15:00, error: 0
timestamp_defmt_asc( 1976, July 14. Time: 9:15 am, %Y, %B %d. Time: %I:%M%p)
= 1976-07-14 09:15:00, error: 0
timestamp_defmt_asc( 1976, P.M. July 14. Time: 9:15, %Y, %P %B %d. Time:
%I:%M) = 1976-07-14 21:15:00, error: 0
*** expected/pgtypeslib-dt_test.stderr Sat Mar 17 19:25:23 2007
--- results/pgtypeslib-dt_test.stderr Wed May 16 09:14:04 2007
***************
*** 20,27 ****
[NO_PID]: sqlca: code: 0, state: 00000
[NO_PID]: ECPGget_data line 37: RESULT: 1966-01-17 offset: -1 array: Yes
[NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ECPGget_data line 37: RESULT: 2000-07-12 17:34:29 offset: -1 array:
Yes
[NO_PID]: sqlca: code: 0, state: 00000
[NO_PID]: ECPGtrans line 350 action = rollback connection = regress1
[NO_PID]: sqlca: code: 0, state: 00000
[NO_PID]: ecpg_finish: Connection regress1 closed.
--- 20,30 ----
[NO_PID]: sqlca: code: 0, state: 00000
[NO_PID]: ECPGget_data line 37: RESULT: 1966-01-17 offset: -1 array: Yes
[NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ECPGget_data line 37: RESULT: 1966-01-17 errno 22
[NO_PID]: sqlca: code: 0, state: 00000
+ [NO_PID]: raising sqlcode -209 in line 37, 'SQL error #-209 in line 37.'.
+ [NO_PID]: sqlca: code: -209, state: 42804
+ sql error SQL error #-209 in line 37.
[NO_PID]: ECPGtrans line 350 action = rollback connection = regress1
[NO_PID]: sqlca: code: 0, state: 00000
[NO_PID]: ecpg_finish: Connection regress1 closed.
*** expected/pgtypeslib-dt_test.stdout Mon Aug 7 14:17:02 2006
--- results/pgtypeslib-dt_test.stdout Wed May 16 09:14:04 2007
***************
*** 1,3 ****
--- 1,11 ----
+ 1: errno = 0
+ 2: errno = 22
+ 3: errno = 22
+ 4: errno = 22
+ 1: errno = 0
+ 2: errno = 22
+ 3: errno = 22
+ 4: errno = 22
Date: 1966-01-17
timestamp: 2000-07-12 17:34:29
interval: @ 13556 days 12 hours 34 mins 14 secs
*** expected/pgtypeslib-dt_test2.stdout Thu Sep 14 09:02:38 2006
--- results/pgtypeslib-dt_test2.stdout Wed May 16 09:14:06 2007
***************
*** 1,88 ****
--- 1,159 ----
timestamp: 2003-12-04 17:34:29
Date of timestamp: 2003-12-04
+ 1: errno = 0
Date[0]: - (N - T)
+ 1: errno = 0
Date[1]: - (N - T)
+ 1: errno = 0
Date[2]: - (N - T)
+ 1: errno = 0
+ 2: errno = 0
+ 3: errno = 0
+ 4: errno = 0
Date[3]: 1999-01-08 (N - F)
TS[3,0]: 1999-01-08 00:04:00
TS[3,1]: 1999-01-08 01:59:00
TS[3,2]: 1999-01-08 13:24:40
TS[3,3]: 1999-01-08 13:24:40.495
+ 1: errno = 0
+ 2: errno = 0
+ 3: errno = 0
+ 4: errno = 0
Date[4]: 1999-01-08 (N - F)
TS[4,0]: 1999-01-08 00:04:00
TS[4,1]: 1999-01-08 01:59:00
TS[4,2]: 1999-01-08 13:24:40
TS[4,3]: 1999-01-08 13:24:40.495
+ 1: errno = 0
+ 2: errno = 0
+ 3: errno = 0
+ 4: errno = 0
Date[5]: 1999-01-08 (N - F)
TS[5,0]: 1999-01-08 00:04:00
TS[5,1]: 1999-01-08 01:59:00
TS[5,2]: 1999-01-08 13:24:40
TS[5,3]: 1999-01-08 13:24:40.495
+ 1: errno = 0
+ 2: errno = 0
+ 3: errno = 0
+ 4: errno = 0
Date[6]: 1999-01-18 (N - F)
TS[6,0]: 1999-01-18 00:04:00
TS[6,1]: 1999-01-18 01:59:00
TS[6,2]: 1999-01-18 13:24:40
TS[6,3]: 1999-01-18 13:24:40.495
+ 1: errno = 0
+ 2: errno = 0
+ 3: errno = 0
+ 4: errno = 0
Date[7]: 2003-01-02 (N - F)
TS[7,0]: 2003-01-02 00:04:00
TS[7,1]: 2003-01-02 01:59:00
TS[7,2]: 2003-01-02 13:24:40
TS[7,3]: 2003-01-02 13:24:40.495
+ 1: errno = 0
+ 2: errno = 0
+ 3: errno = 0
+ 4: errno = 0
Date[8]: 1999-01-08 (N - F)
TS[8,0]: 1999-01-08 00:04:00
TS[8,1]: 1999-01-08 01:59:00
TS[8,2]: 1999-01-08 13:24:40
TS[8,3]: 1999-01-08 13:24:40.495
+ 1: errno = 0
+ 2: errno = 0
+ 3: errno = 0
+ 4: errno = 0
Date[9]: 1999-01-08 (N - F)
TS[9,0]: 1999-01-08 00:04:00
TS[9,1]: 1999-01-08 01:59:00
TS[9,2]: 1999-01-08 13:24:40
TS[9,3]: 1999-01-08 13:24:40.495
+ 1: errno = 0
+ 2: errno = 0
+ 3: errno = 0
+ 4: errno = 0
Date[10]: 1999-01-08 (N - F)
TS[10,0]: 1999-01-08 00:04:00
TS[10,1]: 1999-01-08 01:59:00
TS[10,2]: 1999-01-08 13:24:40
TS[10,3]: 1999-01-08 13:24:40.495
+ 1: errno = 0
+ 2: errno = 0
+ 3: errno = 0
+ 4: errno = 0
Date[11]: 1999-01-08 (N - F)
TS[11,0]: 1999-01-08 00:04:00
TS[11,1]: 1999-01-08 01:59:00
TS[11,2]: 1999-01-08 13:24:40
TS[11,3]: 1999-01-08 13:24:40.495
+ 1: errno = 0
+ 2: errno = 0
+ 3: errno = 0
+ 4: errno = 0
Date[12]: 1999-01-08 (N - F)
TS[12,0]: 1999-01-08 00:04:00
TS[12,1]: 1999-01-08 01:59:00
TS[12,2]: 1999-01-08 13:24:40
TS[12,3]: 1999-01-08 13:24:40.495
+ 1: errno = 0
+ 2: errno = 0
+ 3: errno = 0
+ 4: errno = 0
Date[13]: 2006-01-08 (N - F)
TS[13,0]: 2006-01-08 00:04:00
TS[13,1]: 2006-01-08 01:59:00
TS[13,2]: 2006-01-08 13:24:40
TS[13,3]: 2006-01-08 13:24:40.495
+ 1: errno = 0
+ 2: errno = 0
+ 3: errno = 0
+ 4: errno = 0
Date[14]: 1999-01-08 (N - F)
TS[14,0]: 1999-01-08 00:04:00
TS[14,1]: 1999-01-08 01:59:00
TS[14,2]: 1999-01-08 13:24:40
TS[14,3]: 1999-01-08 13:24:40.495
+ 1: errno = 0
+ 2: errno = 0
+ 3: errno = 0
+ 4: errno = 0
Date[15]: 1999-01-08 (N - F)
TS[15,0]: 1999-01-08 00:04:00
TS[15,1]: 1999-01-08 01:59:00
TS[15,2]: 1999-01-08 13:24:40
TS[15,3]: 1999-01-08 13:24:40.495
+ 1: errno = 0
+ 2: errno = 0
+ 3: errno = 0
+ 4: errno = 0
Date[16]: 1999-01-08 (N - F)
TS[16,0]: 1999-01-08 00:04:00
TS[16,1]: 1999-01-08 01:59:00
TS[16,2]: 1999-01-08 13:24:40
TS[16,3]: 1999-01-08 13:24:40.495
+ 1: errno = 0
+ 2: errno = 0
+ 3: errno = 0
+ 4: errno = 0
Date[17]: 1999-01-08 (N - F)
TS[17,0]: 1999-01-08 00:04:00
TS[17,1]: 1999-01-08 01:59:00
TS[17,2]: 1999-01-08 13:24:40
TS[17,3]: 1999-01-08 13:24:40.495
+ 1: errno = 0
+ 2: errno = 0
+ 3: errno = 0
+ 4: errno = 0
Date[18]: 1999-01-08 (N - F)
TS[18,0]: 1999-01-08 00:04:00
TS[18,1]: 1999-01-08 01:59:00
TS[18,2]: 1999-01-08 13:24:40
TS[18,3]: 1999-01-08 13:24:40.495
+ 1: errno = 0
+ 2: errno = 0
+ 3: errno = 0
+ 4: errno = 0
Date[19]: 0099-01-08 BC (N - F)
TS[19,0]: 0099-01-08 00:04:00 BC
TS[19,1]: 0099-01-08 01:59:00 BC
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
