randgen team mailing list archive

[Roel Van de Paar <806919@xxxxxxxxxxxxxxxxxx>]

I checked all other optimizer .yy grammars, and there are no other " AS
{few letters table name in uppercase}" occurrences. I did notice these:

AS { "SUBQUERY".$subquery_idx."_field1" }
AS { "CHILD_SUBQUERY".$child_subquery_idx."_field1" }
AS { "SQ".$subquery_idx."_field1" }
AS { "C_SQ".$child_subquery_idx."_alias".++$child_subquery_tables }

But that should be fine given the "_field1" addition and the fact that
it is so long.

Even if we were to change/fix the table matching regex (see bug
#792138), it is unlikely to match the above.

The only other one which may cause issues is virtual_columns.yy:

v1 field_type AS ( virtual_expr ) /*executor1 PERSISTENT */ /*executor2 VIRTUAL */ ,
v2 field_type AS ( virtual_expr ) /*executor1 PERSISTENT */ /*executor2 VIRTUAL */ ,
v3 field_type AS ( virtual_expr ) /*executor1 PERSISTENT */ /*executor2 VIRTUAL */ ,
v4 field_type AS ( virtual_expr ) /*executor1 PERSISTENT */ /*executor2 VIRTUAL */

virtual_expr leads to nested_expr which may lead to comparison_operator
which may leads to things like

       expr IS not boolean_value |
       expr IS not NULL |
       expr not BETWEEN ( expr ) AND ( expr ) |
       COALESCE( expr_list ) |
       GREATEST( expr , expr_list ) |
       expr not IN ( expr_list ) |
       ISNULL( expr ) |
       INTERVAL( expr , expr_list ) |
       LEAST( expr, expr_list ) ;

Not likely to be a problem, but if we're ever researching this... :)

Bug fixed.

** Changed in: randgen
       Status: In Progress => Fix Released

-- 
You received this bug notification because you are a member of Random
Query Generator Team, which is subscribed to Random Query Generator.
https://bugs.launchpad.net/bugs/806919

Title:
  ConvertTablesToDerived transformer produces some queries with wrong
  syntax

Status in SQL Generator for testing SQL servers (MySQL, JavaDB, PostgreSQL):
  Fix Released

Bug description:
  The ConvertTablesToDerived transformer has the following purpose:
   - Replace AA with ( SELECT * FROM AA ) AS derived1; Add "AS derivedN" if there was no alias originally;

  This works most of the time, however, when the original query is like
  this:

  SELECT  VARIANCE( OUTR . `pk` ) AS X FROM B AS OUTR WHERE OUTR .
  `col_int_nokey` IN ( SELECT  INNR . `col_int_key` AS Y FROM BB AS
  INNR2 LEFT JOIN CC AS INNR ON ( INNR2 . `col_date_nokey` <= INNR .
  `col_datetime_nokey` ) WHERE OUTR . `col_int_key` >= 3  ) AND OUTR .
  `col_date_key` >= '2003-06-16' AND OUTR . `col_varchar_key` > 'n'
  HAVING X <= NULL ORDER BY OUTR . `col_int_nokey` , OUTR . `pk`;

  the tranformer produces

  /* GenTest::Transform::ConvertTablesToDerived */ SELECT  VARIANCE(
  OUTR . `pk` ) AS ( SELECT * FROM  X  ) AS derived1 FROM ( SELECT *
  FROM  B  ) AS  OUTR WHERE OUTR . `col_int_nokey` IN ( SELECT  INNR .
  `col_int_key` AS ( SELECT * FROM  Y  ) AS derived2 FROM ( SELECT *
  FROM  BB  ) AS  INNR2 LEFT JOIN ( SELECT * FROM  CC  ) AS  INNR ON (
  INNR2 . `col_date_nokey` <= INNR . `col_datetime_nokey` ) WHERE OUTR .
  `col_int_key` >= 3  ) AND OUTR . `col_date_key` >= '2003-06-16' AND
  OUTR . `col_varchar_key` > 'n' HAVING ( SELECT * FROM  X  ) AS
  derived3 <= NULL ORDER BY OUTR . `col_int_nokey` , OUTR . `pk` /*
  TRANSFORM_OUTCOME_UNORDERED_MATCH */;

  which fails with:

  1064 You have an error in your SQL syntax; check the manual that
  corresponds to your MySQL server version for the right syntax to use
  near '( SELECT * FROM  X  ) AS derived1 FROM ( SELECT * FROM  B  ) AS
  OUTR WHERE OUTR' at line 1; RQG Status: STATUS_SYNTAX_ERROR (21)

  It may be that the regexes in ConvertTablesToDerived.pm do not account
  for all possible combinations of tables, functions and aliases.

  RQG command line to reproduce (lp:randgen revid
  philips@eve-20110704134324-c0stltqrvpufugdm):

  perl runall.pl --queries=100000000 --seed=20275 --threads=1
  --duration=1800 --querytimeout=120
  --reporter=Shutdown,Backtrace,QueryTimeout,ErrorLog,ErrorLogAlarm
  --Validator=Transformer --basedir=$CODE --engine=MYISAM
  --grammar=conf/optimizer/archive/subquery_materialization.yy

To manage notifications about this bug go to:
https://bugs.launchpad.net/randgen/+bug/806919/+subscriptions