Lightroom: Issue with Custom Sort Order

  • 6
  • Problem
  • Updated 1 month ago
  • (Edited)
After a while of custom sorting the order of photos and stacks within a collection (using Grid View), Lightroom starts to unpredictably refuse to sort photos. Some will get positioned where I drop them, others won't move at all and yet others will get positioned somewhere close by (eg. 4 or 5 photos before or after).

After digging into the catalog file I've come across what I think is the problem, but don't know how to fix it. In the attached file you'll see a screenshot of the database table for the collection, you'll see I've hilited the images that are part of the same collection, but their positionId is identical (which should never happen I'm assuming), probably due to the field size reaching it's maximum length. This is what I believe is causing the problem. Tested this on both Lightroom 5.7 and CC 2015.8.

This is a major bug and effectively stops user sorting from being functional, as well as now having potentially lost weeks of work. Any suggestions?



Thanks,

Adrian
Photo of Adrian Cleave

Adrian Cleave

  • 3 Posts
  • 1 Reply Like

Posted 2 years ago

  • 6
Photo of John R. Ellis

John R. Ellis, Champion

  • 3694 Posts
  • 966 Reply Likes
A small amount of investigation shows that LR is using an inappropriate algorithm for representing custom order.  This algorithm allows for at most 52 insertions just after a given photo in the custom order. To reproduce the bug:

1. Create a collection with 3 photos.
2. Do View > Sort > Custom Order.
3. In the Collections panel, select the collection.
4. Drag the the third photo between the first and second photo.
5. Repeat the previous step 51 more times.
6. Now repeat step 4 one more time, and observe that the custom order doesn't change.

Thus, you can do 52 insertions after the first photo, but not 53.

A workaround:

1. After no more than 30 insertions after any one photo in a collection, select all of the photos in the collection.
2. Do Library > Create Collection.
3. Select "Include selected photos".
4. Delete the original collection.
5. Rename the new collection to have the original's name.

The new collection should have the same custom order as the original, and it will allow at least 30 insertions after any one photo before triggering the bug again.

ANALYSIS OF THE BUG

LR represents the custom-order position of a photo within a collection using a 64-bit floating point number. Initially, the photos have position values 1.0, 2.0, 3.0, etc.  Suppose there are two adjacent photos A and B in the collection's custom order, and their positions are A.position and B.position.   If you insert photo C between A and B, then:

    C.position = (A.position + B.position) / 2

This might seem like a clever hack, allowing a photo to be inserted without reassigning the positions of the other photos in the collection.  However, as seen above, it fails after a fixed number of insertions.

A 64-bit floating-point number uses 53 bits for representing the mantissa. If A.position is originally 1, and B.position is originally 2, then there are at most 52 bits available for representing the position between A and B.   After 52 insertions, there are no more bits available for representing C.position.   Note that if A.position is a larger number, say 1024 (2^10), then there will be only 43 bits available for representing the position between A and B (43 insertions).

The fix is straightforward: If A.postion = (A.position + B.position) / 2, then A.position has to be decreased a little or B.position increased a little to make room for C.position. In the extreme case, that might involve changing the .position of the photo preceding A or the photo following B, possibly rippling through the entire collection in the worst (but highly unlikely case).   

What's the likelihood this will ever be fixed?  Vanishingly small, in my opinion.
(Edited)
Photo of John R. Ellis

John R. Ellis, Champion

  • 3694 Posts
  • 966 Reply Likes
"This algorithm allows for at most 52 insertions just after a given photo in the custom order."

A clarification: If you are inserting more than one photo at a time, it will take far fewer than 52 insertions to trigger the bug.  For example, if you are inserting 128 photos at a time, the bug will be triggered on the 8th insertion.
Photo of Adrian Cleave

Adrian Cleave

  • 3 Posts
  • 1 Reply Like
John, thank you so much for this detailed reply!

I tried your workaround and it "almost" solves the problem. While it does keep the custom sort order and resets the "positionInCollection" number...the new collection doesn't unfortunately maintain Stack relationships.  So my two scenarios are I either:

a)  Select all images with Stacks Collapsed and then get a new collection with only the top image from the stack 

b) Expand all Stacks, select all and create new collection, which ends up with the correct order but all stacks have been broken.

I'm using stacks to group duplicates/variations of a single photo and have >10,000 photos that I'm trying to sequence and group into stacks. So far it's proving very difficulty to actually achieve this due to this bug.

Any thoughts on how to tackle Stacks using the workaround would be greatly appreciated.

Thanks!

Adrian
Photo of John R. Ellis

John R. Ellis, Champion

  • 3676 Posts
  • 961 Reply Likes
Oh, I forgot about stacks. (LR stacking is "odd", and I only use stacking in folders.)

I can't think of a way of how to correctly handle stacks without using a low-level utility to change the SQL database.  (Plugins can't change stacking once photos are imported.)
Photo of John R. Ellis

John R. Ellis, Champion

  • 3694 Posts
  • 966 Reply Likes
In the hope that this bug might be looked at soon, here's sample Lua code illustrating a correct algorithm for inserting photos into a collection and maintaining the custom order.

The array "position" contains the custom-order positions that will be stored in the database column AgLibraryCollectionImage.positionInCollection. position[i] is the value of "positionInCollection" for the ith image in the collection (in custom order).

This code assumes that "nInserted" photos have been inserted at ordinal position "i + 1" though "i + nInserted" and there are a total of "nPhotos" in the collection.

The algorithm tries to minimize the number of entries in "position" that are changed (since those entries will be written back to the catalog database).  It tests whether the inserted photos can be correctly assigned positions between position [i] and position [j] (without exceeding the precision of floating point numbers); if they can't, then i is repeatedly decreased by 1 or j increased by 1, alternatingly, until there is "room".

As special cases, a range of photos at the beginning or end of the collection order will be assigned integer-valued positions.
local delta, first
local backwards = false
local j = i + nInserted + 1
while true do
    if nPhotos == nInserted then
        delta, first = 1, 0
        break
    elseif i == 0 then 
        delta, first = 1, math.ceil (position [j]) - j
        break
    elseif j == nPhotos + 1 then
        delta, first = 1, math.floor (position [i])
        break
        end
    local delta = (position [j] - position [i]) / (nInserted + 1)
    if delta > 0 then break end
    if backwards then i = i - 1 else j = j + 1 end
    backwards = not backwards
    end
for k = 1, j - i - 1 do 
    position [i + k] = first + k * delta
    end
Photo of Adrian Cleave

Adrian Cleave

  • 3 Posts
  • 1 Reply Like
Update: As a stop-gap solution, I'm resetting the position IDs using SQL queries directly on the SQLite catalog file. It's not pretty but it seems to work for now. Code below in case it's useful to anyone:

STEPS:
1. Create tmp table with a unique, auto-increment col for order_id
2. Copy into tmp table from source with order by collection and position
3. Update the position back into source from tmp using order_id*100 for position (to buy extra re-orders)

SQLITE CODE:

CREATE TABLE IF NOT EXISTS "tmpTable" (
  "order_id" integer PRIMARY KEY autoincrement,
  "id_local" integer NOT NULL DEFAULT(0),
  "collection" integer NOT NULL DEFAULT(0),
  "positionInCollection"
);

INSERT INTO tmpTable(id_local, collection, positionInCollection)
SELECT id_local, collection, positionInCollection FROM AgLibraryCollectionImage ORDER BY collection DESC, positionInCollection ASC;

UPDATE AgLibraryCollectionImage
SET positionInCollection = (SELECT tmpTable.order_id 
                            FROM tmpTable
                            WHERE tmpTable.id_local = AgLibraryCollectionImage.id_local)*100
WHERE
    EXISTS (
        SELECT *
        FROM tmpTable
        WHERE tmpTable.id_local = AgLibraryCollectionImage.id_local
    );

DROP TABLE tmpTable;
Photo of Carol Peterson

Carol Peterson

  • 10 Posts
  • 0 Reply Likes
This reply was created from a merged topic originally titled Custom order in folders fail.

I'm now having a second drag and drop issue. I don't know if it's related, but here's what happens:

I begin by dragging individual photos, one by one, further up in the order on the grid. I'm doing this because when I scanned the photos, they were scanned in reverse order so I need to reorder them. 

To do this, I select an image upwards on the grid to mark my insertion point. I then drag photos from below in the grid, and drop them in front of the selected photo. This works fine for a while, but after a number of drags and drops, the selected photo "freezes."

What I mean by this is, I am no longer able to drop photos in front of the selected photo--when I let go of the dragged photo, it actually lands behind the selected photo, even though I dropped it in front. At first I didn't realize this was happening and it really messed up the order of my photos!

Now that I see it happening, I can fix it: I drag the selected photo, and drop it behind the photos I recently moved up (that landed in the wrong spot). After I do that, everything is fixed, at least for a while. Then it randomly happens again.

It's clear to me there are some bugs in the drag and drop functionality of the Grid view. Perhaps this is not a typical use case for Lightroom. I'm a photo organizer, not a photographer. I take my clients' photos and organize them and add metadata. The order matters because often clients have a legend, at least for trip photos, that identify each image. I need them in the right order so they can be tagged correctly.

Many of my clients' photos are paper and must be scanned. And since the scanning process often gets them out of order, I have to reorder them manually--hence all the dragging and dropping.

Unfortunately there isn't anything I can screen grab of here, but hopefully I have captured the steps that cause the bug. Please let me know if I should move this to a new thread.

Note: This conversation was created from a reply on: Lightroom: After an unsuccessful drag of an image, dragging is no longer possible....
Photo of John R. Ellis

John R. Ellis, Champion

  • 3676 Posts
  • 961 Reply Likes
You've stumbled across a known bug -- LR uses an inappropriate algorithm for maintaining custom order in folders and collections, allowing you to do only a relatively small number of insertions between any two pics.  

I think should be able to use a workaround similar to that for collections: Create a temporary folder. Select all the photos in the problem photo and drag them to the temporary folder -- LR will move the photos.  Then select them all again and drag them back to the original folder.   

This should reset LR's internal numbering and allow you to do more insertions.  (But eventually, you'll have to reset the numbering again.)
Photo of Carol Peterson

Carol Peterson

  • 10 Posts
  • 0 Reply Likes
Thank you John. Since I have to do a lot of reordering, I may just delete the I tagged folders from the catalog, reorder them in Windows explorer, and reimport them into the catalog. Then I shouldn't hit the bug. What a pain!!
Photo of Carol Peterson

Carol Peterson

  • 10 Posts
  • 0 Reply Likes
Wow this just made things worse! I moved all the files to a temp folder and moved them back. I was still having the insertion bug and almost immediately. So I created a fresh temp folder, moved the files to it, deleted the original folder, created a fresh folder with the same name, and copied the files to it. When I started the files were in the correct order. When I finished the entire group of files was completely scrambled! Not in any kind of discernible order.

This is serious for me--an extremely bad bug that is killing my workflow. I guess I'll have to try exporting these pics, maybe to Bridge, and see if I can sort them there. I hope I don't lose the metadata I've already applied!

Going forward I guess I'll work in another app to reorganize the files and rename them to keep them in order. Then maybe I'll try Lightroom for just the metadata. Very discouraging.
Photo of John R. Ellis

John R. Ellis, Champion

  • 3676 Posts
  • 961 Reply Likes
"When I finished the entire group of files was completely scrambled!"

Ensure you're sorting the folder by custom order by giving the menu command View > Sort > Custom Order.
Photo of Carol Peterson

Carol Peterson

  • 10 Posts
  • 0 Reply Likes
As an addendum to my post above, I'm not working in a collection--I haven't created any collections. I'm working on the images in folders. However it seems likely that it is a similar if not identical issue causing the problem.

My work around may be to reorder the photos in the file system folders before importing into LR and avoid the bug altogether.

I'm curious--if you are interested, follow the link to my other reordering issue in my post above. I'm wondering if the bogus error message I get is also related to this problem.

It is disheartening to hear Adobe is unlikely to fix this. It breaks work flow, and it sounds like it is potentially losing data if the db entries are messed up. That seems serious enough to deserve a fix.
Photo of John R. Ellis

John R. Ellis, Champion

  • 3676 Posts
  • 961 Reply Likes
It was just my surmise that the bug wouldn't be fixed soon, based on other similar bugs.  I don't work for Adobe and don't have any inside information about it.
Photo of John R. Ellis

John R. Ellis, Champion

  • 3676 Posts
  • 961 Reply Likes
"I'm wondering if the bogus error message I get is also related to this problem."

I don't think so.  With this multiple-insertion bug, you can drag a pic and drop it, but LR just fails to reorder the photos and doesn't issue any error message.  I just verified that's the case by dragging the last photo in a folder right after the first photo.  After the 53rd drag, LR stopped reordering but didn't issue any error message.
Photo of Sunil Bhaskaran

Sunil Bhaskaran, Official Rep

  • 321 Posts
  • 111 Reply Likes
We have a fix in Lightroom Classic 7.1.
Could you please have a look and let us know?

Thanks,
Sunil
Photo of John R. Ellis

John R. Ellis, Champion

  • 3694 Posts
  • 966 Reply Likes
LR 7.1 appears better but still has a bug.  When testing with my recipe above:

https://feedback.photoshop.com/photoshop_family/topics/lightroom-major-bug-with-custom-sort-order?to...

the first 52 insertions (drag of the third photo between the first and second) work correctly, but the 53rd insertion fails.  But then subsequent insertions work correctly.  
Photo of Sunil Bhaskaran

Sunil Bhaskaran, Official Rep

  • 321 Posts
  • 111 Reply Likes
Thanks, John.
Let us check again.

Thanks,
Sunil
Photo of Matt Audette

Matt Audette

  • 1 Post
  • 0 Reply Likes
I'm running into this issue in Lightroom Classic 7.4
Photo of Sunil Bhaskaran

Sunil Bhaskaran, Official Rep

  • 321 Posts
  • 111 Reply Likes
Matt,
Could you please let us know what exactly the issue you are facing?

Thanks,
Sunil