Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0027261Openbravo ERP05. Production managementpublic2014-08-01 17:172014-10-02 14:26
Reportershuehner 
Assigned Toreinaldoguerra 
PriorityurgentSeverityminorReproducibilityhave not tried
StatusclosedResolutionfixed 
PlatformOS5OS Version
Product Version 
Target Version3.0PR15Q1Fixed in Version3.0PR14Q4 
Merge Request Status
Review Assigned Tovmromanos
OBNetwork customerOBPS
Web browser
ModulesCore
Support ticket
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary0027261: Wrong usage of to_char in two xsql files
DescriptionThe following two files:

src/org/openbravo/erpCommon/ad_actionButton/EditCCPMeasureValues_Values_data.xsql
src/org/openbravo/erpCommon/ad_reports/ReportGuaranteeDate_data.xsql

Have code similar to

select to_date('31/12/9999') in them. Which is wrong.

As checking to_date function (to_date(text) in prescript-postgresql) shows that this call boils down to:

select to_timestamp('31/12/9999', dateFormat());

With that function dateFormat being created/updated from Openbravo.properties.

But in short it means that dateformat can be configured and be anything so it cannot be compatible (at least not always) with a fixed format date-value.

Most code instead does the following which is garanteed to work always:

select to_date('31-12-9999', 'DD-MM-YYYY')

The 2 files doing that wrong should be changed as well.

NOTE:
That wrong syntax to_timestamp is accepted by standard postgres.
However it is not accepted by ppas 9.1 (enterprise db commercial version of postgres).

Steps To ReproduceCompare running that call:
select pg_catalog.to_timestamp('31/12/9999', 'DD-MM-YYYY');

and check output on
1.) standard postgres
 9999-12-31 00:00:00+01
So accepted

2.) ppas9.1
ERROR: invalid value "/1" for "MM"
DETAIL: Value must be an integer.

Rejected
Proposed SolutionApply attached patch to fix sql
Additional Information
TagsNo tags attached.
Relationships
Attached Filesdiff 27261.diff (2,289) 2014-08-01 17:17
https://issues.openbravo.com/file_download.php?file_id=7119&type=bug
Issue History
Date ModifiedUsernameFieldChange
2014-08-01 17:17shuehnerNew Issue
2014-08-01 17:17shuehnerAssigned To => dmiguelez
2014-08-01 17:17shuehnerOBNetwork customer => No
2014-08-01 17:17shuehnerModules => Core
2014-08-01 17:17shuehnerTriggers an Emergency Pack => No
2014-08-01 17:17shuehnerFile Added: 27261.diff
2014-08-01 17:18shuehnerNote Added: 0069039
2014-08-26 17:28shuehnerOBNetwork customerNo => Yes
2014-08-26 17:28shuehnerNote Added: 0069769
2014-09-01 16:29jonalegriaesarteResolution time => 1412114400
2014-09-03 14:05eduardo_ArgalAssigned Todmiguelez => eduardo_Argal
2014-09-22 16:34jonalegriaesarteTarget Version3.0PR14Q4 => 3.0PR15Q1
2014-09-25 05:49reinaldoguerraAssigned Toeduardo_Argal => reinaldoguerra
2014-09-25 05:49reinaldoguerraStatusnew => scheduled
2014-09-30 17:25hgbotCheckin
2014-09-30 17:25hgbotNote Added: 0070572
2014-09-30 17:25hgbotStatusscheduled => resolved
2014-09-30 17:25hgbotResolutionopen => fixed
2014-09-30 17:25hgbotFixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/a80c97c10d9804d517eb3324b57b403f611c5183 [^]
2014-09-30 17:26vmromanosReview Assigned To => vmromanos
2014-09-30 17:26vmromanosStatusresolved => closed
2014-10-02 14:26shuehnerFixed in Version => 3.0PR14Q4

Notes
(0069039)
shuehner   
2014-08-01 17:18   
NOTE: Patch only compile tested on postgres + ppas9.1

No functional testing done yet on neither postgres nor oracle.
Has to be done by committer.
(0069769)
shuehner   
2014-08-26 17:28   
Setting obps after talking to Dmitry to give bug more priority to get fixes soon. As issue very interesting for internal project from me.
(0070572)
hgbot   
2014-09-30 17:25   
Repository: erp/devel/pi
Changeset: a80c97c10d9804d517eb3324b57b403f611c5183
Author: Reinaldo Guerra <reinaldo.guerra <at> peoplewalking.com>
Date: Thu Sep 25 12:32:08 2014 -0500
URL: http://code.openbravo.com/erp/devel/pi/rev/a80c97c10d9804d517eb3324b57b403f611c5183 [^]

Fixed bug 27261: Wrong usage of to_date in two xsql files.

EditCCPMeasureValues_Values_data.xsql and ReportGuaranteeDate_data.xsql were modified by adding specified format on function to_date.
Now when executed these process, all dates will be compared using the correct date format.

---
M src/org/openbravo/erpCommon/ad_actionButton/EditCCPMeasureValues_Values_data.xsql
M src/org/openbravo/erpCommon/ad_reports/ReportGuaranteeDate_data.xsql
---