Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0011770Openbravo ERPY. DBSourceManagerpublic2009-12-11 17:532010-02-15 11:00
Reportermarvintm 
Assigned Tomarvintm 
PriorityimmediateSeveritymajorReproducibilityhave not tried
StatusclosedResolutionfixed 
PlatformOS5OS Version
Product Version 
Target Version2.50MP10Fixed in Version2.40MP12 
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
Summary0011770: 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
blocks defect 00117492.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-14 18:42marvintmTypedefect => backport
2009-12-14 18:42marvintmfix_in_branch => 2.40
2009-12-14 20:04hgbotCheckin
2009-12-14 20:04hgbotNote Added: 0022774
2009-12-14 20:04hgbotStatusscheduled => resolved
2009-12-14 20:04hgbotResolutionopen => fixed
2009-12-14 20:04hgbotFixed in SCM revision => http://code.openbravo.com/erp/stable/2.40/rev/08e2964c295b9973fcefe988e4996904104c4c95 [^]
2009-12-15 09:12hgbotCheckin
2009-12-15 09:12hgbotNote Added: 0022807
2009-12-15 09:12hgbotFixed in SCM revisionhttp://code.openbravo.com/erp/stable/2.40/rev/08e2964c295b9973fcefe988e4996904104c4c95 [^] => http://code.openbravo.com/erp/stable/2.40/rev/ba7d8f535917b597322261438a38e458b76b2a71 [^]
2010-02-15 11:00psarobeStatusresolved => closed
2010-02-15 11:00psarobeFixed in Version => 2.40MP12

Notes
(0022774)
hgbot   
2009-12-14 20:04   
Repository: erp/stable/2.40
Changeset: 08e2964c295b9973fcefe988e4996904104c4c95
Author: Antonio Moreno <antonio.moreno <at> openbravo.com>
Date: Mon Dec 14 20:03:55 2009 +0100
URL: http://code.openbravo.com/erp/stable/2.40/rev/08e2964c295b9973fcefe988e4996904104c4c95 [^]

Fixed issue 11770. Replaced NOT IN syntax with NOT EXISTS

---
M src-db/database/lib/dbsourcemanager.jar
---
(0022807)
hgbot   
2009-12-15 09:12   
Repository: erp/stable/2.40
Changeset: ba7d8f535917b597322261438a38e458b76b2a71
Author: Antonio Moreno <antonio.moreno <at> openbravo.com>
Date: Tue Dec 15 09:10:34 2009 +0100
URL: http://code.openbravo.com/erp/stable/2.40/rev/ba7d8f535917b597322261438a38e458b76b2a71 [^]

Fixed issue 11770. Replaced NOT IN syntax with NOT EXISTS (previous commit contained wrong dbsourcemanager)

---
M src-db/database/lib/dbsourcemanager.jar
---