Bug #53538 Tables/views missing from generated tests where views are used in a query

• Submitted: 10 May 2010 11:43
• Reporter: John Embretsen
• Status: Verified
• Category: Tools: Random Query Generator
• Severity: S3 (Non-critical)
• OS: Any
• Assigned to: Bernt Marius Johnsen
• CPU Architecture: Any

[10 May 2010 11:43] John Embretsen

Description:
Using an RQG feature (such as the Transformer validator) which generates MTR test cases on the fly is very useful for analyzing and reproducing issues. 

However, sometimes such test files are not directly usable because they are missing database objects referenced in the offending queries. Such a case seems to be views.

An example of a generated test file follows as an attachment.
As you will see, one of the queries in the test is:

SELECT table1 .`col_int_key`  
FROM C table1  LEFT  JOIN view_D table2  ON table1 .`col_int_key`  = table2 .`col_int_key`  
WHERE table2 .`pk`  <=  3   ;

However, the test file does not include a definition of the view 'view_D' (which from the server's query log seems to be "CREATE VIEW view_D AS SELECT * FROM D"), nor the table D which view_D depends upon.

This report may be viewed as a feature request instead of a bug (I don't know the full background of the RQG->MTR test generator or the --views option).

How to repeat:
The following RQG command generated such a test when run against mysql-6.0-codebase-bugfixing (referred to as $CODE) as of 2010-05-05:

perl ./runall.pl \
--threads=1 \
--grammar=conf/optimizer/outer_join.yy \
--queries=100000 \
--basedir=$CODE \
--vardir=$PWD/var-transformer \
--validator=Transformer \
--views \
--mysqld1=--init-file=$PWD/init-file.sql

where init-file.sql was:

SET GLOBAL optimizer_join_cache_level=8;
SET GLOBAL optimizer_switch="engine_condition_pushdown=on,firstmatch=on,index_condition_pushdown=on,index_merge=on,index_merge_intersection=on,index_merge_sort_union=on,index_merge_union=on,loosescan=on,materialization=on,mrr=on,mrr_cost_based=off,semijoin=off";

The simplified transformed offending query was:

SELECT STRAIGHT_JOIN table1 .`col_int_key`  FROM C table1  LEFT  JOIN view_D table2  ON table1 .`col_int_key`  = table2 .`col_int_key`  WHERE table2 .`pk`  <=  3     /* TRANSFORM_OUTCOME_UNORDERED_MATCH */;

Suggested fix:
Include views and tables used by views in generated tests if necessary in order to reproduce the issue that is supposed to be exercised using the test file.

[10 May 2010 11:50] John Embretsen

Incomplete MTR test file generated by the RQG

Attachment: 1273480779.test (application/octet-stream, text), 3.48 KiB.

[10 May 2010 11:52] Philip Stoev

It seems the reason for this is that the Simplifier is not set up to handle views at all. In the absence of a full-blown parser, it relies on a regexp in order to extract table names from the query. Hopefully if the regexp is fixed to allow for view names, a perl one-liner can be used to add both the underlying table and the view to the list of objects to be dumped. Column-level simplification may not work as expected, but at least the final test should be complete and runnable.