randgen team mailing list archive

[Roel Van de Paar <798134@xxxxxxxxxxxxxxxxxx>]

How about if we just remove the (rather confusing) IF((ROW_COUNT()...
queries? We should see a much more clearer (and already sufficient)
output from the immediately following 2x
TRANSFORM_OUTCOME_UNORDERED_MATCH and 1x TRANSFORM_OUTCOME_EMPTY_RESULT
compares. This way the analysis of issues would be much clearer/simpler,
and we would not loose any functionality.

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