randgen team mailing list archive

[Roel Van de Paar <806919@xxxxxxxxxxxxxxxxxx>]

I fixed this in oracle base, at least for the archive grammars. Need to
check others.

** Changed in: randgen
     Assignee: (unassigned) => Roel Van de Paar (roel11)

** Changed in: randgen
   Importance: Undecided => High

** Changed in: randgen
       Status: New => In Progress

-- 
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):
  In Progress

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