[IBATISNET-253] N+1 issue when using sql timestamp data type

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: DataMapper 1.6.1
• Fix Version/s: DataMapper 1.6.2
• Component/s: DataMapper
• Environment: Visual Studio 2005 ; SQL Server 2005
• Assignee: Gilles Bayon
• Reporter: Anthony Seddon
• Created: 04/Jan/08 22:37
• Updated: 30/Apr/08 04:51
• Resolved: 30/Apr/08 04:51

Description

Select N+1 produces unexpected results when using SQL timestamp data type. Created tables in SQL 2005 as follows:
Parent table with Id (int), Description (varchar) and RowVersion (timestamp) fields
Child table with Id (int), ParentId (int), Description (varchar) and RowVersion (timestamp) fields

Added 2 records to Parent table
Added 2 records to Child table with ParentId = 1

Created N+1 select statement joining the 2 tables as follows:
SELECT Parent.Id Parent_Id, Parent.RowVersion Parent_RowVersion, Parent.Description Parent_Description,
Child.Id Child_Id, Child.RowVersion Child_RowVersion, Child.ParentId Child_ParentId, Child.Description Child_Description
FROM Parent LEFT JOIN Child ON Parent.Id = Child.ParentId
Running the above statement via iBatis results in 2 Parent objects returned, the 2nd one having an unexpected Child

Created a different N+1 select statement joining the 2 tables as follows:
SELECT Parent.Id Parent_Id, Parent.RowVersion Parent_RowVersion, Parent.Description Parent_Description,
Child.Id Child_Id, NULL Child_RowVersion, Child.ParentId Child_ParentId, Child.Description Child_Description
FROM Parent LEFT JOIN Child ON Parent.Id = Child.ParentId
Running the above statement via iBatis results in 2 Parent objects returned, the 2nd one having no children, as expected

It appears that the the fact that Child_RowVersion = 0x (as when viewed in a query in SQL Management Studio) iBatis creates a Child object in error.