Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-26854

Wrong row number reported upon multi-table update

    XMLWordPrintable

    Details

      Description

      create or replace table t (a tinyint);
      insert into t values (1),(2),(127);
      update t t1 join t t2 set t1.a = t1.a + 100 order by t1.a;
       
      # Cleanup
      drop table t;
      

      10.3 b378ddb3

      MariaDB [test]> update t t1 join t t2 set t1.a = t1.a + 100 order by t1.a;
      ERROR 1264 (22003): Out of range value for column 'a' at row 1
      

      ROW_NUMBER from MDEV-10075 returns the same value.

      It is set to 1 even when the update cannot be performed due to safe mode. In this case it is not reported in the error message, but ROW_NUMBER demonstrates it:

      bb-10.7-row_number c27f04ed

      MariaDB [test]> update t t1 join t t2 set t1.a = t1.a + 100 order by t1.a;
      ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column
      MariaDB [test]> get diagnostics condition 1 @n = row_number; select @n;
      Query OK, 0 rows affected (0.000 sec)
       
      +------+
      | @n   |
      +------+
      |    1 |
      +------+
      1 row in set (0.000 sec)
      

      (For single-table update it would be 0).

        Attachments

          Activity

            People

            Assignee:
            rucha174 Rucha Deodhar
            Reporter:
            elenst Elena Stepanova
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Dates

              Created:
              Updated:

                Git Integration