Status: Closed (View Workflow)
Affects Version/s: 10.2, 10.3, 10.4, 10.3.11, 10.3.12
Fix Version/s: 10.2.23
Component/s: Optimizer - Window functions
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).
I have a table like this:
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:
But for insert...select I need to add them together, and then I get something I didn't expect:
I tried the same with MySQL 8.0.14, and that gives the expected results:
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.