Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0020371Openbravo ERPA. Platformpublic2012-04-27 12:312012-04-27 14:10
Reportermirurita 
Assigned Toalostale 
PriorityhighSeveritymajorReproducibilityalways
StatusclosedResolutionno change required 
PlatformOS5OS Version
Product Versionpi 
Target VersionFixed in Version 
Merge Request Status
Review Assigned To
OBNetwork customer
Web browser
ModulesCore
Support ticket
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary0020371: Unique constraint in PostgreSQL not working with nullable columns
Descriptionunique constraints containing nullable columns are not working as expected in PostgreSQL.

http://www.postgresql.org/docs/8.1/static/ddl-constraints.html [^]

"In general, a unique constraint is violated when there are two or more rows in the table where the values of all of the columns included in the constraint are equal. However, null values are not considered equal in this comparison. That means even in the presence of a unique constraint it is possible to store duplicate rows that contain a null value in at least one of the constrained columns. This behavior conforms to the SQL standard, but we have heard that other SQL databases may not follow this rule. So be careful when developing applications that are intended to be portable."
Steps To Reproduce[PostgreSQL]

c_conversion_rate_document table has the following unique constraint:
constraint c_conversionratedoc_once UNIQUE (c_currency_id, c_currency_id_to, c_invoice_id, fin_payment_id, aprm_finacc_transaction_v_id)

1) Create a new Sales Invoice
2) Go to Exchange Rates tab
3) Create new Exchange Rate (EUR --> EUR)
    - Currency: EUR
    - To Currency: EUR
4) Create again the same exchange rate for EUR --> EUR
    - Currency: EUR
    - To Currency: EUR
5) The unique constraint defined should not allow to create the record in step 4
6) The system allows to create a duplicate exchange rate.
Proposed SolutionSupport more complex constraints.
Additional Information
TagsNo tags attached.
Relationships
related to defect 0020142 closed mirurita In Exchange Rate tab, in Sales Invoice Window, it allows to enter more than one exchange rate for the same pair of currencies. 
Attached Files
Issue History
Date ModifiedUsernameFieldChange
2012-04-27 12:31miruritaNew Issue
2012-04-27 12:31miruritaAssigned To => alostale
2012-04-27 12:31miruritaModules => Core
2012-04-27 12:33miruritaRelationship addedrelated to 0020142
2012-04-27 14:10alostaleNote Added: 0048148
2012-04-27 14:10alostaleStatusnew => closed
2012-04-27 14:10alostaleResolutionopen => no change required

Notes
(0048148)
alostale   
2012-04-27 14:10   
Unique constraints in PostgreSQL do behave as they are expected to behave in PG (which might be differently than in Oracle).