Wrong position on medias with MariaDB >=10.6 about csvimport HOT 9 CLOSED

ROW_NUMBER isn't as available in MySQL... I think it requires 8.0+, so that's not an ideal answer.

What's the behavior you're seeing, just no sorting at all?

from csvimport.

We get a wrong sorting. The problem seems be that during the assignment of the calculated ranks (@rank := IF(@item_id = item_id, @rank + 1, 1)) the order given by item_id ASC, -position DESC, id ASC is partially ignored. Instead the default order for column position is used, so the null values come first, not last as intended... Let me give you an example, expecting we want to import 4 medias with a job batch size of 2:

First 2 scans:

• Create medias:
  ID Position
  100 null
  101 null
• Reorder medias:
  ID Position
  100 1
  101 2

Next 2 scans:

• Create medias:
  ID Position
  100 1
  101 2
  102 null
  103 null

• Reorder medias:
  ID Position
  100 3
  101 4
  102 1
  103 2

from csvimport.

Hmmm.

Can you tell if the ORDER BY is just not being respected at all, or just the -position construction? That would also be odd, but I guess possible. What I mean here is, does using item_id, ISNULL(position), position, id as the ORDER BY in the original query make a difference?

You specifically mention MariaDB 10.6: is that just because that's the version you're using that exhibits the problem, or do you have information, a bug report, release notes, something like that, that indicates a responsible change in 10.6?

I don't think we'd be able to do ROW_NUMBER as discussed but if necessary we might be able to use some other solution. We'd first have to reproduce the problem; I don't think we have any environments with MariaDB 10.6 but I'll see about that.

from csvimport.

I already tried to replace the -position with ISNULL(position), position in the original query, but it made no difference. It's a very strange behavior because it orders by item_id and by position, but it completely ignores the NULL value handling and behaves as an order by position asc. :-(

On our test system we use openSuse Leap 15. Last week we upgraded from 15.3 to 15.4 and now openSuse uses MariaDB 10.6, before it was 10.5. The problem definitely showed up with MariaDB 10.6. Forcing downgrade to 10.5 solved the issue, but isn't really a solution. I could not find any bug report or release note that would explain the problem. But, to be honest, the behavior is so curious, i really don't now what to search for :-)

The problem can easily be reproduced on MariaDB 10.6 with a media table containing a few rows. Set the position of some medias back to NULL and execute the query:

SET @item_id = 0;
SET @rank = 1;
SELECT id, @rank := IF(@item_id = item_id, @rank + 1, 1) AS `rank`, @item_id := item_id AS item FROM media WHERE item_id IN (<ID_XXXXXX>) ORDER BY item_id ASC, ISNULL(`position`), `position`, id ASC;

from csvimport.

So I haven't looked deeply into this, but so far I don't think I'm reproducing the problem.

Here's a dbfiddle of an attempted extraction of the problematic query: these are the exact results I'd expect. Unless I'm missing something about the situation.

Does your server give you the same results for these testing queries?

from csvimport.

In your dbfiddle is something important missing, the index over the columns item_id and position as it exists in the 'real' media table. I created a new fiddle, adding the index creation stuff, et voila... :-)

from csvimport.

Ah, very good! Interesting.

An oddity that the index would affect the order... what must be happening is that the order of operations for the variables isn't actually defined, and 10.6 is probably "optimizing" to our detriment here when the index is present.

This should be very helpful. One option is to just get the order we want and then do the conversion to positions on the PHP side, which should avoid this oddness. Or maybe we could just get the max position for each item and count up from there.

from csvimport.

If you take a look at the media-order branch (the relevant commit is also linked on the issue here), you'll see a change that should resolve this for MariaDB 10.6+, without causing issues for other DBs.

from csvimport.

I've gone ahead and merged the fix for this.

from csvimport.