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

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

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.