SQL does not garantee the ordering of rows (tuples) unless you specifc an ORDER BY Clause. Therefore, it is posssible that my result set in my test XML would be in different order from the current execution. SQLUnit seems to treat that as a failure.
The above can happen when the query optimizer decides to evaluate the query using a different plan. For example, a new index was created that improve the execution of the query.
Is there a matcher to indicate containment? That is same number of tuples as Result set in XM file and every tuple in the execution is part of the Resulkt set. From my point of view, this should be the normal behaviour since SQL does not guarantee ordering of results.
Then, if a have a test that have an ORDER BY clause, may be I should have a matcher for that.
Is there a current way to do that?
If I am not mistaken, the current matchers compare one tuple at a time...instead of containment.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Yes, I have faced that problem before, and I have usually gotten around it by either by imposing an ORDER BY on my source, or by diluting the test to test only the number of rows returned. You can do the latter in SQLUnit by specifying the rowcount attribute in an empty result tag.
And you are right, the design of the Matcher was not done to compare two resultsets, it was more to specify a user-defined "match" between two column values, actual and expected.
However, as I think more about your question, I think there may be a way to do this. If we added a sorted-by attribute to the (specified) resultset element, then we could sort the actual resultset using the same column values that are specified in the sorted-by attribute. This is /not/ currently available but I can build this in the next release. It will require some refactoring, but I was planning to do that anyway, and now I have a reason to do it :-).
Let me know if you have any thoughts on this, or some other approach you can think of.
-sujit
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Anonymous
-
2004-07-08
Hi Sujit:
I think we can create a new tag that indicates if the results must be compared as they are. That is, if we are expecting the specified order for the rows. This tag should be used when there is an ORDER BY in the SQL Statement or the stored procedure guarantees a specific order for the results. For anything else, I feel that we should SORT the results for both the results indicated in the XML file and the actual results obtained by the new execution. It seems to me that an ASCII sort of results would be enough for this purpose since we are just comparing the results (we are not displaying them). The trick would be to display a meaningful error when row of ResultSet in XML file is different. We willlikely need to remember the actual row-id of the orginal result set in the XML file when we do the sorting, so we can print what row-id we are talkign about.
--
I uderstand your suggestions for the current release. Unfortunately, I would not modify the stored procedures or the SQL statement to add the ORDER BY since it would add overhead to the code. That is, I should not fix source code to allow the test framework to work. For your second suggestion, if I test against number of rows retuned, it is like I am not testing the stored procedure or the SQL statement as it is in the code. It is like if I went to the code and added a count(*) instead. I understand that your suggestions would help to do some testings instead of nothing.
Thanks
Jorge
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I would rather create a new tag for the new behavior or modify the existing tag with optional attributes because I dont want existing tests to have to be changed as a result. As you said, it may be non-trivial to make this change, so I may create a new tag for this, although now that I have a reasonably comprehensive mock test suite to test against, I am less worried about breaking existing code than I was before.
-sujit
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Anonymous
-
2004-07-19
I guess I failed to explain myself, let'me try again.
If the default behaviour is changed to mean, result does not need to be ordered. The current tests would not fail since they actually do not care if the results are ordered or not. Thus, if the test pass today, they would pass with new behaviour.
Then, if someone needs the results to be ordered (e.g., stored procedure returns results ordered by)they can indicate that with a new tag. Note that the current tests do not need to indicate it since they do not have any problems by checking results unordered. The new tag is needed only when we need to check for ORDER
Hope this help..
Jorge
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Thanks for the explanation, I guess we are talking at cross-purposes since what you suggest was what I was planning on doing. Sorry for the confusion.
I am taking a little time because I am trying to xdoclet-ise the SLQUnit code since I find that with the addition of new features, the documentation is getting out of date, since I sometimes forget to update the documentation. I am almost done, I have already checked in the xdoclet templates into CVS, and should have the xdoclet docs done on each of the Handlet files in a couple of days, then I can start on the features you requested.
-sujit
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
The sorting feature is in CVS now, and will go out in the next release. Specifically, this is what happens now.
By default, both the result returned from the sql or call, as well as the result specified in the result is sorted by its "natural" order, ie by ascending order of columns specified in the result set.
If the caller knows that the stored procedure will return things in a different order because he put in a ORDER BY for example, he can override the sorting order by specifying an order-by attribute in the resultset element in the result.
The order-by attribute can contain a column separated list of column ids (1 based). So if you wanted to specify a sorting of col1, col2, you would specify it thus:
order-by="1,2"
or if you wanted to specify col1 asc, col2 desc, you would specify it thus:
order-by="1,-2"
Of course, you can also order by single columns.
See /cvs/test/mock/sortingtests.xml for some examples.
Sujit
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
SQL does not garantee the ordering of rows (tuples) unless you specifc an ORDER BY Clause. Therefore, it is posssible that my result set in my test XML would be in different order from the current execution. SQLUnit seems to treat that as a failure.
The above can happen when the query optimizer decides to evaluate the query using a different plan. For example, a new index was created that improve the execution of the query.
Is there a matcher to indicate containment? That is same number of tuples as Result set in XM file and every tuple in the execution is part of the Resulkt set. From my point of view, this should be the normal behaviour since SQL does not guarantee ordering of results.
Then, if a have a test that have an ORDER BY clause, may be I should have a matcher for that.
Is there a current way to do that?
If I am not mistaken, the current matchers compare one tuple at a time...instead of containment.
Hi Jorge,
Yes, I have faced that problem before, and I have usually gotten around it by either by imposing an ORDER BY on my source, or by diluting the test to test only the number of rows returned. You can do the latter in SQLUnit by specifying the rowcount attribute in an empty result tag.
And you are right, the design of the Matcher was not done to compare two resultsets, it was more to specify a user-defined "match" between two column values, actual and expected.
However, as I think more about your question, I think there may be a way to do this. If we added a sorted-by attribute to the (specified) resultset element, then we could sort the actual resultset using the same column values that are specified in the sorted-by attribute. This is /not/ currently available but I can build this in the next release. It will require some refactoring, but I was planning to do that anyway, and now I have a reason to do it :-).
Let me know if you have any thoughts on this, or some other approach you can think of.
-sujit
Hi Sujit:
I think we can create a new tag that indicates if the results must be compared as they are. That is, if we are expecting the specified order for the rows. This tag should be used when there is an ORDER BY in the SQL Statement or the stored procedure guarantees a specific order for the results. For anything else, I feel that we should SORT the results for both the results indicated in the XML file and the actual results obtained by the new execution. It seems to me that an ASCII sort of results would be enough for this purpose since we are just comparing the results (we are not displaying them). The trick would be to display a meaningful error when row of ResultSet in XML file is different. We willlikely need to remember the actual row-id of the orginal result set in the XML file when we do the sorting, so we can print what row-id we are talkign about.
--
I uderstand your suggestions for the current release. Unfortunately, I would not modify the stored procedures or the SQL statement to add the ORDER BY since it would add overhead to the code. That is, I should not fix source code to allow the test framework to work. For your second suggestion, if I test against number of rows retuned, it is like I am not testing the stored procedure or the SQL statement as it is in the code. It is like if I went to the code and added a count(*) instead. I understand that your suggestions would help to do some testings instead of nothing.
Thanks
Jorge
Hi Jorge,
I would rather create a new tag for the new behavior or modify the existing tag with optional attributes because I dont want existing tests to have to be changed as a result. As you said, it may be non-trivial to make this change, so I may create a new tag for this, although now that I have a reasonably comprehensive mock test suite to test against, I am less worried about breaking existing code than I was before.
-sujit
I guess I failed to explain myself, let'me try again.
If the default behaviour is changed to mean, result does not need to be ordered. The current tests would not fail since they actually do not care if the results are ordered or not. Thus, if the test pass today, they would pass with new behaviour.
Then, if someone needs the results to be ordered (e.g., stored procedure returns results ordered by)they can indicate that with a new tag. Note that the current tests do not need to indicate it since they do not have any problems by checking results unordered. The new tag is needed only when we need to check for ORDER
Hope this help..
Jorge
Hi Jorge,
Thanks for the explanation, I guess we are talking at cross-purposes since what you suggest was what I was planning on doing. Sorry for the confusion.
I am taking a little time because I am trying to xdoclet-ise the SLQUnit code since I find that with the addition of new features, the documentation is getting out of date, since I sometimes forget to update the documentation. I am almost done, I have already checked in the xdoclet templates into CVS, and should have the xdoclet docs done on each of the Handlet files in a couple of days, then I can start on the features you requested.
-sujit
Hi Jorge,
The sorting feature is in CVS now, and will go out in the next release. Specifically, this is what happens now.
By default, both the result returned from the sql or call, as well as the result specified in the result is sorted by its "natural" order, ie by ascending order of columns specified in the result set.
If the caller knows that the stored procedure will return things in a different order because he put in a ORDER BY for example, he can override the sorting order by specifying an order-by attribute in the resultset element in the result.
The order-by attribute can contain a column separated list of column ids (1 based). So if you wanted to specify a sorting of col1, col2, you would specify it thus:
order-by="1,2"
or if you wanted to specify col1 asc, col2 desc, you would specify it thus:
order-by="1,-2"
Of course, you can also order by single columns.
See /cvs/test/mock/sortingtests.xml for some examples.
Sujit
Jorge, the 4.0 release of SQLUnit has this feature now.
-sujit