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

DELETE returns ROW_NUMBER=1 for every row upon ER_TRUNCATED_WRONG_VALUE

    XMLWordPrintable

    Details

      Description

      create or replace table t (a varchar(8));
      insert into t values ('val1'),('val2'),('100'),('val4');
      delete from t where a = 100;
       
      show warnings;
      get diagnostics condition 3 @n = row_number;
      select @n;
      

      bb-10.7-row_number cb9002bee36

      +---------+------+------------------------------------------+
      | Level   | Code | Message                                  |
      +---------+------+------------------------------------------+
      | Warning | 1292 | Truncated incorrect DOUBLE value: 'val1' |
      | Warning | 1292 | Truncated incorrect DOUBLE value: 'val2' |
      | Warning | 1292 | Truncated incorrect DOUBLE value: 'val4' |
      +---------+------+------------------------------------------+
      3 rows in set (0.000 sec)
       
      MariaDB [test]> get diagnostics condition 3 @n = row_number;
      Query OK, 0 rows affected (0.000 sec)
       
      MariaDB [test]> select @n;
      +------+
      | @n   |
      +------+
      |    1 |
      +------+
      1 row in set (0.000 sec)
      

      Same for every condition.
      It should be either 0 or different for each row.

      If I add ORDER BY to DELETE, it starts returning ROW_NUMBER=0 instead:

      MariaDB [test]> delete from t where a = 100 order by a;
      Query OK, 0 rows affected, 3 warnings (0.001 sec)
       
      MariaDB [test]> get diagnostics condition 3 @n = row_number;
      Query OK, 0 rows affected (0.000 sec)
       
      MariaDB [test]> select @n;
      +------+
      | @n   |
      +------+
      |    0 |
      +------+
      1 row in set (0.001 sec)
      

      This is also counter-intuitive. If there supposed to be any difference in behavior, I would expect DELETE with ORDER BY to be supported and without ORDER BY unsupported, but not vice versa as it appears now.

        Attachments

          Issue Links

            Activity

              People

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

                Dates

                Created:
                Updated:
                Resolved:

                  Git Integration