Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0011749Openbravo ERPY. DBSourceManagerpublic2009-12-11 17:532010-06-29 13:37
Reportermarvintm 
Assigned Tomarvintm 
PriorityimmediateSeveritymajorReproducibilityhave not tried
StatusclosedResolutionfixed 
PlatformOS5OS Version
Product Version 
Target Version2.50MP10Fixed 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
Summary0011749: Remove invalid rows process uses incorrect NOT IN syntax, which could lead to data loss
DescriptionIn update.database, there is a process which removes rows of tables with an on delete cascade foreign key, which point to a non-existing row of another table.

This process creates sql statements which have the form:

DELETE FROM table1 WHERE column1 NOT IN (SELECT column2 FROM table2);

This sql statement is correct except in the case that the column can be nullable. If this happens (that is, if there is at least one row in table1 whose value in column1 is null), and the table2 is empty, then all the rows in table1 will be deleted.
Steps To Reproduce- Make sure you have at least one M_PRODUCT entry with M_ATTRIBUTESET_ID null.
- Delete all entries in M_ATTRIBUTESET table
- Run update.database. This will delete all M_PRODUCT entries.
Proposed SolutionDBSourceManager needs to be changed. The syntax should probably use NOT EXISTS instead of NOT IN.
Additional Information
TagsNo tags attached.
Relationships
related to design defect 0011750 scheduled Triage Platform Base Check for incorrect NOT IN syntax, which could lead to data loss 
depends on backport 00117702.50MP10 closed marvintm Remove invalid rows process uses incorrect NOT IN syntax, which could lead to data loss 
Attached Files
Issue History
Date ModifiedUsernameFieldChange
2009-12-11 17:53marvintmNew Issue
2009-12-11 17:53marvintmAssigned To => marvintm
2009-12-11 17:57shuehnerIssue Monitored: shuehner
2009-12-11 17:59shuehnerRelationship addedrelated to 0011750
2009-12-14 18:42marvintmStatusnew => scheduled
2009-12-14 18:42marvintmfix_in_branch => pi
2009-12-14 20:06hgbotCheckin
2009-12-14 20:06hgbotNote Added: 0022775
2009-12-14 20:06hgbotStatusscheduled => resolved
2009-12-14 20:06hgbotResolutionopen => fixed
2009-12-14 20:06hgbotFixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/acc8c3be67a637e2802c5647b7d62885dc5803df [^]
2009-12-16 16:46hudsonbotCheckin
2009-12-16 16:46hudsonbotNote Added: 0022866
2010-01-22 18:25shuehnerNote Added: 0023767
2010-01-22 18:25shuehnerStatusresolved => closed
2010-01-23 00:00anonymoussf_bug_id0 => 2937522
2010-06-29 13:37rafarodaIssue Monitored: rafaroda

Notes
(0022775)
hgbot   
2009-12-14 20:06   
Repository: erp/devel/pi
Changeset: acc8c3be67a637e2802c5647b7d62885dc5803df
Author: Antonio Moreno <antonio.moreno <at> openbravo.com>
Date: Mon Dec 14 20:05:33 2009 +0100
URL: http://code.openbravo.com/erp/devel/pi/rev/acc8c3be67a637e2802c5647b7d62885dc5803df [^]

Fixed issue 11749. Replaced NOT IN syntax with NOT EXISTS

---
M src-db/database/lib/dbsourcemanager.jar
---
(0022866)
hudsonbot   
2009-12-16 16:46   
---
A changeset related to this issue has been promoted to main after passing a series of tests and an OBX has been generated:

Changeset: http://code.openbravo.com/erp/devel/main/rev/acc8c3be67a6 [^]
Merge Changeset: http://code.openbravo.com/erp/devel/main/rev/1767de909fe7 [^]
Tests: http://builds.openbravo.com/view/devel-int/ [^]
OBX: http://builds.openbravo.com/erp/obx/core/OpenbravoERP-2.50CI.15751.obx [^]
---
(0023767)
shuehner   
2010-01-22 18:25   
test in pi rev 6d0399c00eaa+, with steps provided, failure does no longer happen.