Comments (9)
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.
Related Issues (20)
- Invalid settings: basic-settings: Array HOT 7
- User import doesn't send emails HOT 1
- Styling issue with Language input (with core on develop)
- items and media imported, but not linked together
- Appends lose value annotation data from existing values
- CSV Import says status “completed,” but no results HOT 9
- Add user manual link & help text HOT 2
- Switch to openspout
- PHP error on "Append data to the resource" and "Revise data of the resource" csv import HOT 5
- Testing for CSV Import 2.5.0 HOT 11
- "Automap" when unchecked still automaps HOT 2
- "Result" column improvements HOT 1
- CSV throws an exception and skip item import if latitude is empty HOT 3
- Automap expansion
- Testing/docs: resource-identifier-property branch HOT 9
- Setting languages during import HOT 2
- Feature Suggestion: Config Persistence
- resource:item saved as item in the db
- Request: add event to query builder
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from csvimport.