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

ROW_NUMBER and DELETE/UPDATE with ORDER BY

    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 order by a;
      get diagnostics condition 3 @n = row_number;
      select @n;
      

      returns 0.

      It gets even more confusing with UPDATE

      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 @n2 = ROW_NUMBER;
      GET DIAGNOSTICS CONDITION 5 @n5 = ROW_NUMBER;
      SELECT @n2, @n5;
      

      shows 2, 2. That is, both second and fifth warnings are claimed to have happened on the second row. But they actually have happened on different "second rows".

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              rucha174 Rucha Deodhar
              Reporter:
              serg Sergei Golubchik
              Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

                Dates

                Created:
                Updated:

                  Git Integration

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