randgen team mailing list archive

[Roel Van de Paar <798134@xxxxxxxxxxxxxxxxxx>]

Public bug reported:

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.

** Affects: randgen
     Importance: Undecided
         Status: New

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