randgen team mailing list archive

[Roel Van de Paar <798134@xxxxxxxxxxxxxxxxxx>]

Did some more testing. 5.1 and 5.5 both react like this:

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

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

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

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

Now, what worries me a bit before changing this is that in the orginal
two queries I am not exactly sure if the query will always be in the
form of id=id instead of id=value, as per the examples above.

I'd like a second opinion here.

Is there maybe another way around this? Why do we need the == 1 check?
Maybe we can just always use SET `$col_name` = $col_name? Why no quotes
around the second $col_name?

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