Incorrect result after GenerateCommand with formula property and left outer joins

[boozerxxl]

Hello all,

I am using JetDriver 2.0.0.1001 for MsAccess on Net 4.5 and with NHibernate 3.3.3.4001
I need some help to clarify why the following SQL text is post-processed incorrectly by JetDriver's ConnectionProvider.Driver.GenerateCommand(). I think it is related to the formula property I have, without it all is ok.

<property name="CutCount" type="System.Int32" formula="(SELECT COUNT(CuttingPart.ID) FROM CuttingPart WHERE CuttingPart.PartID = ID)"/>

---

NHibernate generated SQL passed to AbstractBatcher.Generate method (correct)

SELECT 
	this_.ID as ID42_4_, 
	this_.Title as Title42_4_, 
	this_.Length as Length42_4_, 
        (SELECT COUNT(CuttingPart.ID) FROM CuttingPart WHERE CuttingPart.PartID = this_.ID) as formula1_4_, 
        o2_.ID as ID40_0_, 
        o2_.Title as Title40_0_, 
        m1_.ID as ID16_1_, 
        m1_.Title as Title16_1_, 
        pc3_.PartID as PartID6_, 
        pc3_.ID as ID6_, 
        pcv4_.ContourID as ContourID7_, 
        pcv4_.ID as ID7_, 
        pcv4_.ID as ID50_3_, 
        pcv4_.[Type] as column2_50_3_, 
        pcv4_.XM as XM50_3_, 
        pcv4_.YM as YM50_3_, 
        pcv4_.Radius as Radius50_3_, 
FROM Part this_ 
        left outer join AppOrder o2_ on this_.AppOrderID=o2_.ID 
        left outer join Material m1_ on this_.MaterialID=m1_.ID 
        left outer join Contour pc3_ on this_.ID=pc3_.PartID 
        left outer join ContourValue pcv4_ on pc3_.ID=pcv4_.ContourID 
WHERE 
        this_.IsArchived = 0 
ORDER BY pcv4_.PointIndex asc

---

Jet Driver result after its _factory.ConnectionProvider.Driver.GenerateCommand

SELECT 
	this_.ID as ID42_4_, 
	this_.Title as Title42_4_, 
	this_.Length as Length42_4_, 
	(SELECT COUNT(CuttingPart.ID) from CuttingPart WHERE CuttingPart.PartID = this_.ID) as formula1_4_,
	o2_.ID as ID40_0_, 
	o2_.Title as Title40_0_, 
	m1_.ID as ID16_1_, 
	m1_.Title as Title16_1_, 
	pc3_.PartID as PartID6_, 
	pc3_.ID as ID6_, 
	pcv4_.ContourID as ContourID7_, 
	pcv4_.ID as ID7_, 
	pcv4_.ID as ID50_3_, 
	pcv4_.[Type] as column2_50_3_, 
	pcv4_.XM as XM50_3_, 
	pcv4_.YM as YM50_3_, 
	pcv4_.Radius as Radius50_3_,
        -- from here I have incorrect SQL text
        (select * from  
	((( pcv4_.ContourID as ContourID50_3_ FROM Part this_
	left outer join AppOrder o2_ on this_.AppOrderID=o2_.ID) 
	left outer join Material m1_ on this_.MaterialID=m1_.ID) 
	left outer join Contour pc3_ on this_.ID=pc3_.PartID) 
	left outer join ContourValue pcv4_ on pc3_.ID=pcv4_.ContourID 

WHERE 
	this_.IsArchived = 0) as jetJoinAlias29 

ORDER BY pcv4_.PointIndex asc

---

Is it a known problem ? Could you please give me a hint about a possible workaround if any? Thanks a lot.

[boozerxxl]

I looked at the sources. I suppose FinalizeJoins() works incorrectly if there are nested SELECT FROM in the query (as the formula does) because FinalizeJoins() doesn't properly determine From-Where clauses indices in the sql string with IndexOfCaseInsensitive() in this case.

A simple fix is to use LastIndexOfCaseInsensitive in this case to skip the nested queries. It resolves my issue but I am not sure if it is correct in all possible cases.

[fredericDelaporte]

Your issue wording is a mix of bug report and support request. Better write it only as a bug report, if you intend it to be a bug report. For support request, better go on nh user group or Stack Overflow.