0020505: Validate during export.database any unique constraint containing nullable columns

Openbravo Issue Tracking System - Openbravo ERP

View Issue Details

Project

Category

View Status

Date Submitted

Last Update

0020505

Openbravo ERP

A. Platform

public

2012-05-15 13:49

2022-02-01 08:08

Reporter

mirurita

Assigned To

Triage Platform Base

Priority

normal

Severity

major

Reproducibility

always

Status

new

Resolution

open

Platform

OS Version

Product Version

Target Version

Fixed in Version

Merge Request Status

Review Assigned To

OBNetwork customer

Web browser

Modules

Core

Support ticket

Regression level

Regression date

Regression introduced in release

Regression introduced by commit

Triggers an Emergency Pack

Summary

0020505: Validate during export.database any unique constraint containing nullable columns

Description

unique constraints containing nullable columns are not working as expected in PostgreSQL.

we should add a validation (clear warning message) during export.database ant target for checking any unique constraint defined with nullable columns.

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) Disable the trigger for c_conversion_rate_document
2) Create a new Sales Invoice
3) Go to Exchange Rates tab
4) Create new Exchange Rate (EUR --> EUR)
    - Currency: EUR
    - To Currency: EUR
5) Create again the same exchange rate for EUR --> EUR
    - Currency: EUR
    - To Currency: EUR
6) The unique constraint defined should not allow to create the record in step 4
7) The system allows to create a duplicate exchange rate.

Proposed Solution

Additional Information