randgen team mailing list archive

["John H. Embretsen" <798134@xxxxxxxxxxxxxxxxxx>]

You cannot "verify" RQG behavior with respect to ROW_COUNT() by using a
different mysql client than what the RQG is using (DBD::mysql). This is
becacuse DBD::mysql sets the client flag mysql_client_found_rows
(CLIENT_FOUND_ROWS) by default (it has been doing so since 2003, version
2.9002).  This flag tells MySQL to report "matched rows" as ROW_COUNT(),
whereas the default without this flag is to report "changed rows" as
ROW_COUNT().

http://dev.mysql.com/doc/refman/5.6/en/information-functions.html
#function_row-count

As far as I can tell the logic in ExecuteAsUpdateDelete.pm is correct
when considering the Perl driver's settings, so this is likely not a
bug. However, sometimes the transformer does seem to report issues that
seem related to this when running the generated test cases, so there may
still be something fishy going on in this area.

-- 
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/798134

Title:
  Fault in GenTest::Transform::ExecuteAsUpdateDelete

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

Bug description:
  The following code in ExecuteAsUpdateDelete.pm:

  =====
  ( $original_result->rows() == 1 ?
          "UPDATE $table_name SET `$col_name` = ( $original_query ) WHERE `$col_name` IN ( $original_query ) " :
          "UPDATE $table_name SET `$col_name` = $col_name WHERE `$col_name` IN ( $original_query ) "
  ),

  # The queries above should have updated all rows
  "SELECT IF(ROW_COUNT() = ".$original_result->rows()." OR ROW_COUNT() = -1, 1, 0) /* TRANSFORM_OUTCOME_SINGLE_INTEGER_ONE */",
  =====

  Is incorrect. Example:

  =====
  mysql> show create table id4\G
  *************************** 1. row ***************************
         Table: id4
  Create Table: CREATE TABLE `id4` (
    `id` int(11) DEFAULT NULL
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1
  1 row in set (0.00 sec)

  mysql> select * from id4\G
  *************************** 1. row ***************************
  id: 1
  1 row in set (0.01 sec)

  mysql> update id4 set id=id where id=1\G
  Query OK, 0 rows affected (0.00 sec)
  Rows matched: 1  Changed: 0  Warnings: 0

  mysql> select row_count();
  +-------------+
  | row_count() |
  +-------------+
  |           0 |
  +-------------+
  1 row in set (0.00 sec)
  =====

  Reason (** highlight added):

  ROW_COUNT()
  [...]
  DML statements other than SELECT: The number of **affected rows**. This applies to statements such as UPDATE, INSERT, or DELETE (as before), but now also to statements such as ALTER TABLE and LOAD DATA INFILE. 
  Source: http://dev.mysql.com/doc/refman/5.5/en/information-functions.html#function_row-count

  So the IF line should be:

  "SELECT IF(ROW_COUNT() = 0 OR ROW_COUNT() = -1, 1, 0) /*
  TRANSFORM_OUTCOME_SINGLE_INTEGER_ONE */",

  Instead.

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