← Back to team overview

randgen team mailing list archive

Re: Question about Comparison vs. Stress


Hi Joel,

On 08/26/2013 08:49 PM, Joel Epstein wrote:

I am currently trying to run some of the RQG tests for correctness (e.g.
comparison) as opposed to using the suite to test for stress failures.
  Is there anything I should look for within the grammar files or, in
general, that might indicate what tests are not really set up to be run
for correctness (e.g. comparison)?   On one of the tests, I had to an
ORDER BY clause to get the Update, Delete, Insert statements to validate
the test results.  Otherwise, divergence would quickly occur.

There is so far no systematic separation of grammars that are suitable for comparison testing from other grammars (as far as I know). On the other hand, I have used the RQG for correctness testing myself for a long time, and it works pretty well, with some caveats.

Sometimes the grammar author has added some comments to the top of the grammar file, such as "This grammar is not suitable for comparison because..." or "In order to use this for result comparison, you need to..." or something to that effect. So I recommend to look for this first.

In most grammars there are no such comments. So it is not easy to tell which grammars are suitable or not. I can tell from experience that most grammars in the optimizer category are somewhat suitable for such testing, but not without some effort with post-processing of the results, in order to separate real issues from false positives. The same is likely true for several other grammars.

Then it is the question how you do the actual testing. There are several ways to test for correctness, for example:

 - Use a 2-way or 3-way result comparator validator such as
   ResultsetComparatorSimplify, to compare results from two
   (or three) servers directly.


 - Use the Transformer validator to compare results from a single
   server using equivalent alternative queries that are "transformed"
   from the original queries produced by the grammar.


I am not sure what you mean by using "the Update, Delete, Insert statements" to validate results. If the Transformer validator is used, the order of the rows does not matter in most cases, as long as the same set of rows is returned for both queries, due to hints like "TRANSFORM_OUTCOME_UNORDERED_MATCH", which makes the Transformer validator ignore the order when comparing. In other contexts the ordering may pose a challenge.

In any case, lack of ORDER BY can be an issue in some special cases regardless of the comparison method used. For example, lack of full ORDER BY in combination with LIMIT can result in non-deterministic results, making result comparison difficult. The same is often the case with "hidden GROUP BY/ORDER BY", or combining aggregates and non-aggregates in the SELECT list without including all non-aggregates in GROUP BY, or using HAVING without GROUP BY, or non-deterministic statistical functions, or non-sensical aggregates (e.g. SUM(VARCHAR)), etc.

Some of these issues can be avoided by including sql_mode=ONLY_FULL_GROUP_BY in the server settings for MySQL. Other database vendors may have something similar, or simply reject more of the non-deterministic query types, so your mileage may vary.

So, there are some traps and pitfalls, but with some patience and some experience I think the RQG is useful also for correctness testing.

I hope this helps,