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

Select max + row_number giving incorrect result

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.2, 10.3, 10.4, 10.3.11, 10.3.12
    • Fix Version/s: 10.2.23
    • Labels:
      None
    • Environment:
      Server version: 10.3.12-MariaDB-1:10.3.12+maria~bionic mariadb.org binary distribution
      Running docker image mariadb:10.3.12 in Docker version 18.09.1, build 4c52b90, on macOS 10.14.2, MacBook Pro (Retina, 15-inch, Mid 2015).

      Description

      I have a table like this:

      MariaDB [test]> CREATE TABLE `event` (
        `entity_id` int(10) NOT NULL,
        `entity_version` int(10) NOT NULL,
        `something` varchar(10) DEFAULT NULL,
        PRIMARY KEY (`entity_id`,`entity_version`)
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1
      

      With data:

      MariaDB [test]> insert into event values (1, 1, 'foo'), (1,2,'bar'), (1,3,'bar'), (2, 1, 'baz'), (2, 2, 'grunt');
      

      It is a simplified version of a table used for storing events of entities, identified by an entity id and an increasing entity version number, used for event sourcing.

      Some background for the use case: I wanted to insert some new events for the entities using insert...select, with possibly more than one event to add for each entity. So, in the select part I need to get the next available entity version number for each new event to insert. I figured I would get max(entity_version) and row_number() and add them together to get the entity_version to insert for each event. I found an issue in the select part of the insert...select, and the SQL statements below are simplified to concentrate on issue. Now to the actual issue:

      If I select the max entity_version and row_number in separate columns, the results are what I expected:

      MariaDB [test]> select e.entity_id, (select max(e2.entity_version) from event e2 where e2.entity_id=e.entity_id) as maxver, row_number() over (partition by e.entity_id order by e.entity_id, e.entity_version) as rownum from event e order by e.entity_id, e.entity_version;
      +-----------+--------+--------+
      | entity_id | maxver | rownum |
      +-----------+--------+--------+
      |         1 |      3 |      1 |
      |         1 |      3 |      2 |
      |         1 |      3 |      3 |
      |         2 |      2 |      1 |
      |         2 |      2 |      2 |
      +-----------+--------+--------+
      

      But for insert...select I need to add them together, and then I get something I didn't expect:

      MariaDB [test]> select e.entity_id, (select max(e2.entity_version) from event e2 where e2.entity_id=e.entity_id) + (row_number() over (partition by e.entity_id order by e.entity_id, e.entity_version)) as maxverplusrownum from event e order by e.entity_id, e.entity_version;
      +-----------+------------------+
      | entity_id | maxverplusrownum |
      +-----------+------------------+
      |         1 |                3 |
      |         1 |                4 |
      |         1 |                5 |
      |         2 |                3 |
      |         2 |                4 |
      +-----------+------------------+
      

      I tried the same with MySQL 8.0.14, and that gives the expected results:

      mysql> select e.entity_id, (select max(e2.entity_version) from event e2 where e2.entity_id=e.entity_id) + (row_number() over (partition by e.entity_id order by e.entity_id, e.entity_version)) as maxverplusrownum from event e order by e.entity_id, e.entity_version;
      +-----------+------------------+
      | entity_id | maxverplusrownum |
      +-----------+------------------+
      |         1 |                4 |
      |         1 |                5 |
      |         1 |                6 |
      |         2 |                3 |
      |         2 |                4 |
      +-----------+------------------+
      

      I might miss something as this is the first time I need to use row_number, and I think I can work around this with a temporary table, but at this point I suspect this might be a bug. I have only tried the versions mentioned above.

        Attachments

          Activity

            People

            • Assignee:
              varun Varun Gupta
              Reporter:
              Antikainen Jarno Antikainen
            • Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: