Difference between count and find

peterw2000 · Post by **peterw2000** » Thu Dec 24, 2009 12:25 am

Hi, I'm seeing a difference between Find and Count. I have exactly the same logic in both rules.

Is there a way I can see the SQL being sent to the database that comes with Aware?

Here are the two rules. I know the context and variables will mean nothing without an understanding of the whole application, but what is concerning is that though the logic is the same, the count is returning 6, while the find is returning 2 objects. (as seen from the trace)

BTW - I'm expecting a count of 2.

Any ideas?

When I turn on the trace see the following:

Code: Select all

COUNT
---------
Executing action ResultSetRequirement.CountRSCompleteStepLink=
COUNT ExecutionTestStep WHERE (ExecutionTestStep.TestProcedure IN ResultSetRequirement.ParentResultSetReq.ExecutionProcList AND ExecutionTestStep.StepResult IN 'Pass', 'Fail' AND ResultSetRequirement.LinkedRequirement IN ExecutionTestStep.LinkedTestStep.LinkedRequirements) 

from rules of object ResultSetRequirement
Value was not changed  because it was equal to the old value CountRSCompleteStepLink=6



FIND
---------
Executing action 
FIND  ExecutionTestStep WHERE  ExecutionTestStep.TestProcedure IN ResultSetRequirement.ParentResultSetReq.ExecutionProcList AND ExecutionTestStep.StepResult IN 'Pass', 'Fail' AND ResultSetRequirement.LinkedRequirement IN ExecutionTestStep.LinkedTestStep.LinkedRequirements 

from rules of object ResultSetRequirement
Found 2 objects

tford · Post by **tford** » Thu Dec 24, 2009 1:10 am

I wonder if the difference in parentheses between the COUNT and the FIND makes a difference in the result?

peterw2000 · Post by **peterw2000** » Thu Dec 24, 2009 1:42 am

I just tried taking the brackets off the count and it wouldn't let me, so I added brackets to the find. Still the different results of 2 and 6.

Funny thing is that the find in the trace didn't have the brackets. Made me think there was something not updating, so I rebooted the servers, but alas still strangely different and without brackets in the find on the trace.

Thanks for your reply though.

Post by **aware_admin** » Thu Dec 24, 2009 6:16 am

They should be exactly the same. Are you sure they operate in the same database environment? Maybe when COUNT is operating the number of instances is indeed 6?

peterw2000 · Post by **peterw2000** » Fri Dec 25, 2009 9:17 pm

Ok I moved the two rules into a process by itself and liked the process to the object's primary form.

I then opened the form and ran the process. I believe this should guarantee the same context for both the find and the count.

Same result, count=6, find = 2.

Here is the file log:

Code: Select all

2009-12-26 08:02:15,578 GENERIC PT#peter -17  
2009-12-26 08:02:15,578 GENERIC PT#peter -17  
2009-12-26 08:02:15,578 GENERIC PT#peter -20 Executing process tester with parameters: ResultSetRequirement:118
2009-12-26 08:02:15,578 RULE_EVALUATION PT#peter    -29 Started evaluation of rules
2009-12-26 08:02:15,578 RULE_EVALUATION PT#peter       -22 Evaluating rule 'counter' ResultSetRequirement.CountRSCompleteStepLink=COUNT ExecutionTestStep WHERE (ExecutionTestStep.TestProcedure IN ResultSetRequirement.ParentResultSetReq.ExecutionProcList AND ExecutionTestStep.StepResult IN 'Pass', 'Fail' AND ResultSetRequirement.LinkedRequirement IN ExecutionTestStep.LinkedTestStep.LinkedRequirements)
2009-12-26 08:02:15,578 RULE_EVALUATION PT#peter          -2 Adding action ResultSetRequirement.CountRSCompleteStepLink=COUNT ExecutionTestStep WHERE (ExecutionTestStep.TestProcedure IN ResultSetRequirement.ParentResultSetReq.ExecutionProcList AND ExecutionTestStep.StepResult IN 'Pass', 'Fail' AND ResultSetRequirement.LinkedRequirement IN ExecutionTestStep.LinkedTestStep.LinkedRequirements) to the agenda
2009-12-26 08:02:15,578 RULE_EVALUATION PT#peter       -16 Finished rule evaluation
2009-12-26 08:02:15,578 RULE_EVALUATION PT#peter    -16 Finished evaluation of rules
2009-12-26 08:02:15,578 ACTION_EXECUTION PT#peter    -15 Executing action ResultSetRequirement.CountRSCompleteStepLink=COUNT ExecutionTestStep WHERE (ExecutionTestStep.TestProcedure IN ResultSetRequirement.ParentResultSetReq.ExecutionProcList AND ExecutionTestStep.StepResult IN 'Pass', 'Fail' AND ResultSetRequirement.LinkedRequirement IN ExecutionTestStep.LinkedTestStep.LinkedRequirements)
2009-12-26 08:02:15,609 ACTION_EXECUTION PT#peter       -5 Value was not changed  because it was equal to the old value CountRSCompleteStepLink=6
2009-12-26 08:02:15,609 ACTION_EXECUTION PT#peter    -16 Finished executing action ResultSetRequirement.CountRSCompleteStepLink=COUNT ExecutionTestStep WHERE (ExecutionTestStep.TestProcedure IN ResultSetRequirement.ParentResultSetReq.ExecutionProcList AND ExecutionTestStep.StepResult IN 'Pass', 'Fail' AND ResultSetRequirement.LinkedRequirement IN ExecutionTestStep.LinkedTestStep.LinkedRequirements)
2009-12-26 08:02:15,609 RULE_EVALUATION PT#peter    -29 Started evaluation of rules
2009-12-26 08:02:15,609 RULE_EVALUATION PT#peter       -22 Evaluating rule 'finder' FIND ExecutionTestStep WHERE (ExecutionTestStep.TestProcedure IN ResultSetRequirement.ParentResultSetReq.ExecutionProcList AND ExecutionTestStep.StepResult IN 'Pass', 'Fail' AND ResultSetRequirement.LinkedRequirement IN ExecutionTestStep.LinkedTestStep.LinkedRequirements)
2009-12-26 08:02:15,609 RULE_EVALUATION PT#peter          -2 Adding action FIND ExecutionTestStep WHERE ExecutionTestStep.TestProcedure IN ResultSetRequirement.ParentResultSetReq.ExecutionProcList AND ExecutionTestStep.StepResult IN 'Pass', 'Fail' AND ResultSetRequirement.LinkedRequirement IN ExecutionTestStep.LinkedTestStep.LinkedRequirements to the agenda
2009-12-26 08:02:15,609 RULE_EVALUATION PT#peter       -16 Finished rule evaluation
2009-12-26 08:02:15,609 RULE_EVALUATION PT#peter    -16 Finished evaluation of rules
2009-12-26 08:02:15,609 ACTION_EXECUTION PT#peter    -15 Executing action FIND ExecutionTestStep WHERE ExecutionTestStep.TestProcedure IN ResultSetRequirement.ParentResultSetReq.ExecutionProcList AND ExecutionTestStep.StepResult IN 'Pass', 'Fail' AND ResultSetRequirement.LinkedRequirement IN ExecutionTestStep.LinkedTestStep.LinkedRequirements
2009-12-26 08:02:15,671 ACTION_EXECUTION PT#peter       -33 Found 2 objects
2009-12-26 08:02:15,671 ACTION_EXECUTION PT#peter    -16 Finished executing action FIND ExecutionTestStep WHERE ExecutionTestStep.TestProcedure IN ResultSetRequirement.ParentResultSetReq.ExecutionProcList AND ExecutionTestStep.StepResult IN 'Pass', 'Fail' AND ResultSetRequirement.LinkedRequirement IN ExecutionTestStep.LinkedTestStep.LinkedRequirements
2009-12-26 08:02:15,671 GENERIC PT#peter -16 Finished executing process tester
2009-12-26 08:02:15,671 GENERIC PT#peter -17  
2009-12-26 08:02:15,671 GENERIC PT#peter -17

Further to this, I migrated to MySQL because I couldn't work out how to trace the SQL in Derby. I didn't set up all of my data in MySQL, so I didn't replicate the problem, just got a trace of the SQL.

Here are the two queries being executed:

Code: Select all

SELECT COUNT(*) FROM BASTESTDOMAINPT_EXECUTIONTESTSTEP AS ExecutionTestStep  LEFT JOIN BASTESTDOMAINPT_EXECUTIONTESTPROCEDURE AS ExecutionTestStep_TestProcedure ON ExecutionTestStep.TestProcedure_RID=ExecutionTestStep_TestProcedure.ID LEFT JOIN BASTESTDOMAINPT_TESTSTEP AS ExecutionTestStep_LinkedTestStep ON ExecutionTestStep.LinkedTestStep_RID=ExecutionTestStep_LinkedTestStep.ID LEFT JOIN BASTESTDOMAINPT_TESTSTEP_REF AS ER_TestStep_ExecutionTestStep_LinkedTestStep_LinkedRequirements ON ExecutionTestStep_LinkedTestStep.ID=ER_TestStep_ExecutionTestStep_LinkedTestStep_LinkedRequirements.ID LEFT JOIN BASTESTDOMAINPT_REQUIREMENT AS ExecutionTestStep_LinkedTestStep_LinkedRequirements ON ER_TestStep_ExecutionTestStep_LinkedTestStep_LinkedRequirements.RID=ExecutionTestStep_LinkedTestStep_LinkedRequirements.ID AND ER_TestStep_ExecutionTestStep_LinkedTestStep_LinkedRequirements.FIELD_NAME='LinkedRequirements' LEFT JOIN BASTESTDOMAINPT_RESULTSET_REF AS ResultSet_ExecutionProcList_RO ON ResultSet_ExecutionProcList_RO.RID=ExecutionTestStep_TestProcedure.ID AND ResultSet_ExecutionProcList_RO.ID=13 AND ResultSet_ExecutionProcList_RO.FIELD_NAME='ExecutionProcList' WHERE ((((ExecutionTestStep_TestProcedure.ID IS NOT NULL AND ResultSet_ExecutionProcList_RO.ID=13)) AND ((ExecutionTestStep.StepResult='Pass' OR ExecutionTestStep.StepResult='Fail'))) AND ((4=ExecutionTestStep_LinkedTestStep_LinkedRequirements.ID AND ExecutionTestStep_LinkedTestStep_LinkedRequirements.ID IS NOT NULL)))
		  


SELECT ExecutionTestStep.* FROM BASTESTDOMAINPT_EXECUTIONTESTSTEP AS ExecutionTestStep  LEFT JOIN BASTESTDOMAINPT_EXECUTIONTESTPROCEDURE AS ExecutionTestStep_TestProcedure ON ExecutionTestStep.TestProcedure_RID=ExecutionTestStep_TestProcedure.ID LEFT JOIN BASTESTDOMAINPT_TESTSTEP AS ExecutionTestStep_LinkedTestStep ON ExecutionTestStep.LinkedTestStep_RID=ExecutionTestStep_LinkedTestStep.ID LEFT JOIN BASTESTDOMAINPT_TESTSTEP_REF AS ER_TestStep_ExecutionTestStep_LinkedTestStep_LinkedRequirements ON ExecutionTestStep_LinkedTestStep.ID=ER_TestStep_ExecutionTestStep_LinkedTestStep_LinkedRequirements.ID LEFT JOIN BASTESTDOMAINPT_REQUIREMENT AS ExecutionTestStep_LinkedTestStep_LinkedRequirements ON ER_TestStep_ExecutionTestStep_LinkedTestStep_LinkedRequirements.RID=ExecutionTestStep_LinkedTestStep_LinkedRequirements.ID AND ER_TestStep_ExecutionTestStep_LinkedTestStep_LinkedRequirements.FIELD_NAME='LinkedRequirements' LEFT JOIN BASTESTDOMAINPT_RESULTSET_REF AS ResultSet_ExecutionProcList_RO ON ResultSet_ExecutionProcList_RO.RID=ExecutionTestStep_TestProcedure.ID AND ResultSet_ExecutionProcList_RO.ID=13 AND ResultSet_ExecutionProcList_RO.FIELD_NAME='ExecutionProcList' WHERE ((((ExecutionTestStep_TestProcedure.ID IS NOT NULL AND ResultSet_ExecutionProcList_RO.ID=13)) AND ((ExecutionTestStep.StepResult='Pass' OR ExecutionTestStep.StepResult='Fail'))) AND ((4=ExecutionTestStep_LinkedTestStep_LinkedRequirements.ID AND ExecutionTestStep_LinkedTestStep_LinkedRequirements.ID IS NOT NULL))) FOR UPDATE

They are almost identical, but I think the problem is that you are counting all returned rows which could be problematic when the cartesian joins expands the rows beyond the number of primary elements.

I believe the answer may be to perform a SELECT COUNT(DISTINCT ExecutionTestStep.ID) FROM ....

I think I came across this issue previously but was too lazy to dig into it at the time.

I could be completely wrong of course. I'd know for sure if you could tell me how to see and execute SQL in Derby.

Cheers and Merry Christmas.

Peter

aware_support · Post by **aware_support** » Sat Dec 26, 2009 9:29 pm

Merry Christmas to you too!

Would it be possible for you to send us your BSV file as well as test data, so that we could reproduce this problem ourselves?

peterw2000 · Post by **peterw2000** » Sat Dec 26, 2009 10:23 pm

Sure.

How do I send the test data and where do I send it?

Thanks - you should really be on holiday you know.

Post by **aware_admin** » Sun Dec 27, 2009 10:27 am

You can send everything to [email protected]

With the test data you can just explain how to create it manually, or export it as CSV and send it to the same e-mail address

peterw2000 · Post by **peterw2000** » Mon Jan 04, 2010 10:20 pm

Just wanted to confirm that you have received my email and to see if you have had any luck.

Thanks

Peter

aware_support · Post by **aware_support** » Mon Jan 04, 2010 10:43 pm

We have received your e-mail, thank you. But we haven't had time to look at the problem yet.

peterw2000 · Post by **peterw2000** » Thu Jan 07, 2010 11:45 pm

No worries.

Can you give me a rough time when you'll get to have a look at it? Understand it is the holiday season and all.

Thanks
Peter