Anonymous | Login
Project:
RSS
  
News | My View | View Issues | Roadmap | Summary

View Issue Details[ Jump to Notes ] [ Issue History ] [ Print ]
ID
0011770
TypeCategorySeverityReproducibilityDate SubmittedLast Update
backport[Openbravo ERP] Y. DBSourceManagermajorhave not tried2009-12-11 17:532010-02-15 11:00
ReportermarvintmView Statuspublic 
Assigned Tomarvintm 
PriorityimmediateResolutionfixedFixed in Version2.40MP12
StatusclosedFix in branch2.40Fixed in SCM revisionba7d8f535917
ProjectionnoneETAnoneTarget Version2.50MP10
OSAnyDatabaseOracleJava version
OS VersionDatabase versionAnt version
Product VersionSCM revision 
Review Assigned To
Web browser
ModulesCore
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary

0011770: 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.
TagsNo tags attached.
Attached Files

- Relationships [ Relation Graph ] [ Dependency Graph ]
blocks defect 00117492.50MP10 closedmarvintm Remove invalid rows process uses incorrect NOT IN syntax, which could lead to data loss 

-  Notes
(0022774)
hgbot (developer)
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 (developer)
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
---

- Issue History
Date Modified Username Field Change
2009-12-14 18:42 marvintm Type defect => backport
2009-12-14 18:42 marvintm fix_in_branch => 2.40
2009-12-14 20:04 hgbot Checkin
2009-12-14 20:04 hgbot Note Added: 0022774
2009-12-14 20:04 hgbot Status scheduled => resolved
2009-12-14 20:04 hgbot Resolution open => fixed
2009-12-14 20:04 hgbot Fixed in SCM revision => http://code.openbravo.com/erp/stable/2.40/rev/08e2964c295b9973fcefe988e4996904104c4c95 [^]
2009-12-15 09:12 hgbot Checkin
2009-12-15 09:12 hgbot Note Added: 0022807
2009-12-15 09:12 hgbot Fixed in SCM revision http://code.openbravo.com/erp/stable/2.40/rev/08e2964c295b9973fcefe988e4996904104c4c95 [^] => http://code.openbravo.com/erp/stable/2.40/rev/ba7d8f535917b597322261438a38e458b76b2a71 [^]
2010-02-15 11:00 psarobe Status resolved => closed
2010-02-15 11:00 psarobe Fixed in Version => 2.40MP12

Copyright © 2000 - 2009 MantisBT Group
Powered by Mantis Bugtracker