randgen team mailing list archive

[John Embretsen <johnemb@xxxxxxxxx>]

Hi Joel,

On 08/26/2013 08:49 PM, Joel Epstein wrote:
> Hello,
>
> 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.

https://github.com/RQG/RQG-Documentation/wiki/RandomQueryGeneratorValidators#wiki-Optimizer_Comparison_Testing_and_Data_Validation

 - 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.

https://github.com/RQG/RQG-Documentation/wiki/RandomQueryGeneratorTransforms

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,


--
John