Обсуждение: Two bugs found (and patch included)
I just discovered two bugs and attached a patch to correct them :
- the jdbc escaped scalar function dayofweek should return values
between 1 and 7 (1 for sunday) unlike the backend "extract dow" function
- the literal escaped values for date/time/timestamp were not explicitly
casted and causing some problems : for example
extract( dow from '2005-01-17 12:00:00')
generate an error, but the following is accepted
extract( dow from TIMESTAMP '2005-01-17 12:00:00')
Xavier Poinsard.
Index: jdbc2/AbstractJdbc2Statement.java
===================================================================
RCS file: /usr/local/cvsroot/pgjdbc/pgjdbc/org/postgresql/jdbc2/AbstractJdbc2Statement.java,v
retrieving revision 1.63
diff -u -r1.63 AbstractJdbc2Statement.java
--- jdbc2/AbstractJdbc2Statement.java 15 Jan 2005 07:53:02 -0000 1.63
+++ jdbc2/AbstractJdbc2Statement.java 17 Jan 2005 15:12:04 -0000
@@ -756,12 +756,21 @@
{
state = ESC_TIMEDATE;
i++;
+ newsql.append("DATE ");
break;
}
else if (next == 't' || next == 'T')
{
state = ESC_TIMEDATE;
- i += (nextnext == 's' || nextnext == 'S') ? 2 : 1;
+ if (nextnext == 's' || nextnext == 'S'){
+ // timestamp constant
+ i+=2;
+ newsql.append("TIMESTAMP ");
+ }else{
+ // time constant
+ i++;
+ newsql.append("TIME ");
+ }
break;
}
else if ( next == 'f' || next == 'F' )
Index: jdbc2/EscapedFunctions.java
===================================================================
RCS file: /usr/local/cvsroot/pgjdbc/pgjdbc/org/postgresql/jdbc2/EscapedFunctions.java,v
retrieving revision 1.3
diff -u -r1.3 EscapedFunctions.java
--- jdbc2/EscapedFunctions.java 14 Jan 2005 01:20:20 -0000 1.3
+++ jdbc2/EscapedFunctions.java 17 Jan 2005 15:12:06 -0000
@@ -399,13 +399,14 @@
return "extract(day from "+parsedArgs.get(0)+")";
}
- /** dayofweek translation */
+ /** dayofweek translation
+ * adding 1 to postgresql function since we expect values from 1 to 7 */
public static String sqldayofweek(List parsedArgs) throws SQLException{
if (parsedArgs.size()!=1){
throw new PSQLException(GT.tr("{0} function takes one and only one argument.","dayofweek"),
PSQLState.SYNTAX_ERROR);
}
- return "extract(dow from "+parsedArgs.get(0)+")";
+ return "extract(dow from "+parsedArgs.get(0)+")+1";
}
/** dayofyear translation */
Index: test/jdbc2/StatementTest.java
===================================================================
RCS file: /usr/local/cvsroot/pgjdbc/pgjdbc/org/postgresql/test/jdbc2/StatementTest.java,v
retrieving revision 1.13
diff -u -r1.13 StatementTest.java
--- test/jdbc2/StatementTest.java 11 Jan 2005 08:25:48 -0000 1.13
+++ test/jdbc2/StatementTest.java 17 Jan 2005 15:12:07 -0000
@@ -267,13 +267,15 @@
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("select {fn curdate()},{fn curtime()}" +
",{fn dayname({fn now()})}, {fn dayofmonth({fn now()})}" +
- ",{fn dayofweek({fn now()})},{fn dayofyear({fn now()})}" +
+ ",{fn dayofweek({ts '2005-01-17 12:00:00'})},{fn dayofyear({fn now()})}" +
",{fn hour({fn now()})},{fn minute({fn now()})}" +
",{fn month({fn now()})}" +
",{fn monthname({fn now()})},{fn quarter({fn now()})}" +
",{fn second({fn now()})},{fn week({fn now()})}" +
",{fn year({fn now()})} ");
assertTrue(rs.next());
+ // ensure sunday =>1 and monday =>2
+ assertEquals(2,rs.getInt(5));
}
public void testSystemFunctions() throws SQLException
Xavier Poinsard wrote: > I just discovered two bugs and attached a patch to correct them : > > - the jdbc escaped scalar function dayofweek should return values > between 1 and 7 (1 for sunday) unlike the backend "extract dow" function > > - the literal escaped values for date/time/timestamp were not explicitly > casted and causing some problems : for example > extract( dow from '2005-01-17 12:00:00') > generate an error, but the following is accepted > extract( dow from TIMESTAMP '2005-01-17 12:00:00') Applied. Thanks for the report & patch. While testing this patch I noticed we have existing date-related regression failures (not related to the patch) against 7.2.x: > [junit] Testcase: testSetTimestampWTZ(org.postgresql.test.jdbc2.TimestampTest): Caused an ERROR > [junit] ERROR: Bad timestamp external representation '2000-02-08 04:00:00.120000+1300' > [junit] org.postgresql.util.PSQLException: ERROR: Bad timestamp external representation '2000-02-08 04:00:00.120000+1300' ... > [junit] at org.postgresql.test.jdbc2.TimestampTest.testSetTimestampWTZ(TimestampTest.java:100) > [junit] Testcase: testSetTimestampWOTZ(org.postgresql.test.jdbc2.TimestampTest): Caused an ERROR > [junit] ERROR: Bad timestamp external representation '2000-02-07 15:00:00.120000+1300' > [junit] org.postgresql.util.PSQLException: ERROR: Bad timestamp external representation '2000-02-07 15:00:00.120000+1300' ... > [junit] at org.postgresql.test.jdbc2.TimestampTest.testSetTimestampWOTZ(TimestampTest.java:210) Is it worth fixing this? -O
On Wed, 19 Jan 2005, Oliver Jowett wrote: > While testing this patch I noticed we have existing date-related > regression failures (not related to the patch) against 7.2.x: > > Bad timestamp external representation '2000-02-08 04:00:00.120000+1300' This appears to be an oddity of the +1300 timezone. Both of the following work: select '2000-02-07 15:00:00.120000+13'::timestamptz; select '2000-02-07 15:00:00.120000+1200'::timestamptz; We potentially could hack around this on the driver, but it looks to me like a server problem, so I'd leave it alone based on the number of people it affects (7.2 & +1300). Kris Jurka
Oliver Jowett <oliver@opencloud.com> writes:
> While testing this patch I noticed we have existing date-related
> regression failures (not related to the patch) against 7.2.x:
>> [junit] Testcase: testSetTimestampWTZ(org.postgresql.test.jdbc2.TimestampTest): Caused an ERROR
>> [junit] ERROR: Bad timestamp external representation '2000-02-08 04:00:00.120000+1300'
I believe this is a server bug, not JDBC's fault. The timestamp code
rejected +1300 as an invalid timezone until not too long ago. It is
valid though...
regards, tom lane