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

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

            rucha174 Rucha Deodhar added a comment - Patch: https://github.com/MariaDB/server/commit/76af9e1d56053e0fc5519104ab86e100ffb980be

            let's split this MDEV is two. The first is without ORDER BY and without any filesort changes, only DELETE part:

            CREATE TABLE t (a VARCHAR(8));
            INSERT INTO t VALUES ('val1'),('val2'),('100'),('val4');
            DELETE FROM t WHERE a = 100;
            GET DIAGNOSTICS CONDITION 3 @n = ROW_NUMBER;
            SELECT @n;
            DROP TABLE t;
            

            This test and fix are ok to push, please, push them.

            serg Sergei Golubchik added a comment - let's split this MDEV is two. The first is without ORDER BY and without any filesort changes, only DELETE part: CREATE TABLE t (a VARCHAR (8)); INSERT INTO t VALUES ( 'val1' ),( 'val2' ),( '100' ),( 'val4' ); DELETE FROM t WHERE a = 100; GET DIAGNOSTICS CONDITION 3 @n = ROW_NUMBER; SELECT @n; DROP TABLE t; This test and fix are ok to push, please, push them.
            rucha174 Rucha Deodhar added a comment -

            Fixed as two separate commits and pushed to bb-10.7-row_number

            rucha174 Rucha Deodhar added a comment - Fixed as two separate commits and pushed to bb-10.7-row_number
            serg Sergei Golubchik added a comment - - edited

            sorry, I wasn't clear enough. Only the first part — without ORDER BY — was ok to push.

            but np problem, the fix isn't in the main tree yet, so nothing irreversible has happened yet, let's continue.

            Please, try this test case:

            CREATE TABLE t (a VARCHAR(8), b tinyint);
            INSERT INTO t(a) VALUES ('val1'),('val2'),('100'),('val4'),('100');
            SELECT * FROM t;
            set sql_mode='';
            UPDATE t SET b=1234 WHERE a = 100 ORDER BY a;
            GET DIAGNOSTICS CONDITION 2 @n = ROW_NUMBER;
            SELECT @n;
            GET DIAGNOSTICS CONDITION 5 @n = ROW_NUMBER;
            SELECT @n;
            

            serg Sergei Golubchik added a comment - - edited sorry, I wasn't clear enough. Only the first part — without ORDER BY — was ok to push. but np problem, the fix isn't in the main tree yet, so nothing irreversible has happened yet, let's continue. Please, try this test case: CREATE TABLE t (a VARCHAR (8), b tinyint); INSERT INTO t(a) VALUES ( 'val1' ),( 'val2' ),( '100' ),( 'val4' ),( '100' ); SELECT * FROM t; set sql_mode= '' ; UPDATE t SET b=1234 WHERE a = 100 ORDER BY a; GET DIAGNOSTICS CONDITION 2 @n = ROW_NUMBER; SELECT @n; GET DIAGNOSTICS CONDITION 5 @n = ROW_NUMBER; SELECT @n;
            rucha174 Rucha Deodhar added a comment - Patch (fix for UPDATE and DELETE with ORDER BY) : https://github.com/MariaDB/server/commit/0b01c7cb7bc594061f689f13fec8c40449996d62

            the ORDER BY issue is moved to MDEV-26909

            serg Sergei Golubchik added a comment - the ORDER BY issue is moved to MDEV-26909

            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.