path queries not returning all results

[vredesbyyrd]

beets version 1.6.1
Python version 3.11.5

Note: I originally posted this as a discussion, as I was not confident the problem is a bug, now I am leaning more towards bug.

Hello,

Some background on the problem. My music collection is separated into 3 categories: Artists, Composers & Scores. The Directory structure reflects the categories. Years ago I accomplished this by using 3 separate beets configs, one for each category. I always thought this was convoluted so I dug into the doc looking for a more sane approach - Using flexible attributes appears to be what I was looking for.

The relevant part of my config now looks like:

library: /mnt/TARS/Media/library.db
directory: /mnt/TARS/Media/Music
paths:
    default: Artists/%the{$albumartist}/$year - $album%aunique{}/%if{$multidisc,Disc $disc/}$track - $title
    category:artists: Artists/%the{$albumartist}/$year - $album%aunique{}/%if{$multidisc,Disc $disc/}$track - $title
    category:composers: Composers/%asciify{$albumartist}/$year - %asciify{$album%aunique{}}/%if{$multidisc,Disc $disc/}%asciify{$track - $title}
    category:scores: Scores/%the{$albumartist}/$year - $album%aunique{}/%if{$multidisc,Disc $disc/}$track - $title

I first tried to tag all the files in each category by doing:

beet modify category="artists" path:/mnt/TARS/Media/Music/Artists
beet modify category="composers" path:/mnt/TARS/Media/Music/Composers
beet modify category="scores" path:/mnt/TARS/Media/Music/Scores

But this was not finding any files. To be sure:

beet ls path:/mnt/TARS/Media/Music/Artists

would not return anything.

By a happy accident typo I noticed prepending path:: to the filepath instead of path: would return all the files. I know the double :: is for regex, but not sure why this finds the files when the single : does not.

Perhaps wrecklessly, I ran the beet modify category="foobar" path::/foo/bar for each category and everything looks correct, e.g, beet ls category:artists | wc -l return the expected count of files, 24878

So that's the background, this is the issue I cannot figure out - Running:

beet ls path:/mnt/TARS/Media/Music/Artists | wc -l

returns 5871, the correct count being 24878. I expected it to find either none, like the above issue or hopefully 24878 after having run the modify command

Using the path::/foo/bar regex returns the correct count. The same issue happens on each category, eg, scores, composers.

Do any of have an idea what could be going on here? It's possible there is something wrong with the database, but querying the db directly with sql works fine. Any ideas would be very appreciated!

EDIT:

So if I run beet info -l on a file that is not returned from beet ls path:/foo/bar - the command also returns nothing. So it's apparent beets thinks most files are not in the library. This I find confusing because when examining the database directly or querying the database directly, everything is there. Am I overlooking something obvious?

Originally posted by @vredesbyyrd in https://github.com/beetbox/beets/discussions/4954

[sampsyo]

So if I run beet info -l on a file that is not returned from beet ls path:/foo/bar - the command also returns nothing. So it's apparent beets thinks most files are not in the library. This I find confusing because when examining the database directly or querying the database directly, everything is there. Am I overlooking something obvious?

Sounds tricky. Can you show the output of beet ls -p (i.e., print the paths) for some of the items that you think this should match?

[vredesbyyrd]

Agreed, tricky.

Here is what beet ls -p "citizen of glass" returns.

/mnt/TARS/Media/Music/Artists/Agnes Obel/2016 - Citizen of Glass/01 - Stretch Your Eyes.flac
/mnt/TARS/Media/Music/Artists/Agnes Obel/2016 - Citizen of Glass/02 - Familiar.flac
/mnt/TARS/Media/Music/Artists/Agnes Obel/2016 - Citizen of Glass/03 - Red Virgin Soil.flac
/mnt/TARS/Media/Music/Artists/Agnes Obel/2016 - Citizen of Glass/04 - It’s Happening Again.flac
/mnt/TARS/Media/Music/Artists/Agnes Obel/2016 - Citizen of Glass/05 - Stone.flac
/mnt/TARS/Media/Music/Artists/Agnes Obel/2016 - Citizen of Glass/06 - Trojan Horses.flac
/mnt/TARS/Media/Music/Artists/Agnes Obel/2016 - Citizen of Glass/07 - Citizen of Glass.flac
/mnt/TARS/Media/Music/Artists/Agnes Obel/2016 - Citizen of Glass/08 - Golden Green.flac
/mnt/TARS/Media/Music/Artists/Agnes Obel/2016 - Citizen of Glass/09 - Grasshopper.flac
/mnt/TARS/Media/Music/Artists/Agnes Obel/2016 - Citizen of Glass/10 - Mary.flac

Those are not returned with beet ls path:"/mnt/TARS/Media/Music/Artists/Agnes Obel"

[sampsyo]

Interesting! Do any prefixes of the path suffice? Such as beet ls path:"/mnt" or similar?

[vredesbyyrd]

Sorry for the delay - had to go touch grass for a bit.

beet ls path:"/mnt" | wc -l returns 6638, so again, only some of the files. Yeah, it's really weird.

I am tempted to start from scratch, just re-import everything, but I have a lot of "custom" album titles, e.g, beet modify foobar album="Some nice album title (vinyl, mfsl)". Although that might be a good candidate for flexible attributes @ import time, which would be much quicker than modifying each vinyl rip independently. I need to think on it.

Not sure this is helpful, but here is a link to my database. I put it up on my onedrive. The album_attributes table may have some stuff that does not need to be there, but I do not see anything obviously wrong.

[sampsyo]

One thing I might consider doing to help narrow things down is to try different prefixes of your path query. That is, are there obvious differences when moving "down" from beet ls /mnt to beet ls /mnt/TARS, and so on? That could provide a clue about where things are going wrong.

[vredesbyyrd]

Unfortunately there is no difference from /mnt and each step down the path. Good idea though.

The only other thing I can think of is to look for a pattern in the database. For example, all items returned have a common row filled where items not returned that column is null.

[wisp3rwind]

One more thing to test: Could you update to the latest version from Github (which has some additional debug logging), and run in verbose mode, i.e.

beet -vv ls -p path:"/mnt/TARS/Media/Music/Artists" 2> "beets.log" | wc -l

and share the log file beets.log that this creates? Hopefully, this could provide some hint as to where things go wrong.

[vredesbyyrd]

Hey, thanks for tip. Here is the output:

user configuration: /home/clu/.config/beets/config.yaml
data directory: /home/clu/.config/beets
plugin paths: /home/clu/.python/lib/python3.11/site-packages/beetsplug
lastgenre: Loading canonicalization tree /home/clu/scripts/beets/tree.txt
inline: adding item field multidisc
Sending event: pluginload
library database: /mnt/TARS/Media/library.db
library directory: /mnt/TARS/Media/Music
Sending event: library_opened
Parsed query: AndQuery([PathQuery('path', '/mnt/TARS/Media/Music/Artists', fast=True, case_sensitive=True)])
Parsed sort: NullSort()
Sending event: cli_exit
5903

[vredesbyyrd]

Don't know if this is helpful, but as stated in the initial issue a regex query returns all the files. Here's that output of beet -vv ls -p path::"/mnt/TARS/Media/Music/Artists" | wc -l

user configuration: /home/clu/.config/beets/config.yaml
data directory: /home/clu/.config/beets
plugin paths: /home/clu/.python/lib/python3.11/site-packages/beetsplug
inline: adding item field multidisc
lastgenre: Loading canonicalization tree /home/clu/scripts/beets/tree.txt
Sending event: pluginload
library database: /mnt/TARS/Media/library.db
library directory: /mnt/TARS/Media/Music
Sending event: library_opened
Parsed query: AndQuery([RegexpQuery('path', re.compile('/mnt/TARS/Media/Music/Artists'), fast=True)])
Parsed sort: NullSort()
Sending event: cli_exit
24910

[wisp3rwind]

Thanks for the logs! Turns out they're not giving any hints, but I found something else looking at your database:

❯ sqlite3 library.db "select typeof(path) from items" | sort | uniq
blob
text

❯ sqlite3 library.db "select typeof(path) from items" | grep text | wc -l
23013

❯ sqlite3 library.db "select typeof(path) from items" | grep blob | wc -l
6660

i.e. somehow, the database ended up with different types for the path column. That shouldn't happen, I fear that this might turn out to be a nasty bug in beets.

[vredesbyyrd]

Welp - that has to be it. Very good find.

This may not be a bug in beets, and was more likely my fault. When I set up my new server quite awhile ago, I must have manually replaced the root of paths:

update items set path = replace(path, '/home/clu/', '/mnt/TARS/Media/');

I absolutely would have mentioned it, but I do not remember doing it! After your finding I went through my .sqlite_history and found the above command. But I am a bit confused because I don't think replace should work on the blob datatype, so the update command should have failed? Now I wonder if it's possible to force the appropriate datatype on that column or if it can only be done when the db is created.

I'll read into it. And I very much appreciate your help!

[vredesbyyrd]

So I was able to fix my database with the following:

alter table items add column path_temp blob;
update items set path_temp = cast(path as blob);
alter table items drop column path;

With the sqlite browser program I moved the path_temp column to the proper position and renamed it to path.

I did the same process with the albums table and artpath column.

So far I do not see any ill side effects, everything seems good.

sqlite3 library.db "select typeof(path) from items" | grep blob | wc -l
29651

beet ls path:/mnt/TARS/Media/Music | wc -l
29651

Thanks everyone for the help, and especially @wisp3rwind for recognizing the issue. And sorry for the potential noise. I cannot be certain the issue stemmed from modifying the paths way back when, but that is the most obvious explanation. I'll close this since it appears to be a rookie mistake, not a beets issue.

[sampsyo]

Awesome! Adding further applause for @wisp3rwind; I'm glad this had a reasonable explanation in the end. :smiley: