Get random album returns 500

[sprnza]

DB backend: Postgres 10.4, Supysonic- current master Jamstash: Auto Albums -> Random Ultrasonic: Ultrasonic Main -> Albums -> Random

Supysonic logs an error like this:

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/usr/lib/python3.8/site-packages/flask/app.py", line 2447, in wsgi_app
    response = self.full_dispatch_request()
  File "/usr/lib/python3.8/site-packages/flask/app.py", line 1952, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "/usr/lib/python3.8/site-packages/flask/app.py", line 1821, in handle_user_exception
    reraise(exc_type, exc_value, tb)
  File "/usr/lib/python3.8/site-packages/flask/_compat.py", line 39, in reraise
    raise value
  File "/usr/lib/python3.8/site-packages/flask/app.py", line 1950, in full_dispatch_request
    rv = self.dispatch_request()
  File "/usr/lib/python3.8/site-packages/flask/app.py", line 1936, in dispatch_request
    return self.view_functions[rule.endpoint](**req.view_args)
  File "/usr/lib/python3.8/site-packages/supysonic/api/albums_songs.py", line 84, in album_list
    for a in query.distinct().random(size)
  File "/usr/lib/python3.8/site-packages/pony/orm/core.py", line 6272, in random
    return query.order_by('random()')[:limit]
  File "/usr/lib/python3.8/site-packages/pony/orm/core.py", line 6206, in __getitem__
    return query._fetch(limit=stop-start, offset=start)
  File "/usr/lib/python3.8/site-packages/pony/orm/core.py", line 6208, in _fetch
    return QueryResult(query, limit, offset, lazy=lazy)
  File "/usr/lib/python3.8/site-packages/pony/orm/core.py", line 6315, in __init__
    self._items = None if lazy else self._query._actual_fetch(limit, offset)
  File "/usr/lib/python3.8/site-packages/pony/orm/core.py", line 5848, in _actual_fetch
    cursor = database._exec_sql(sql, arguments)
  File "/usr/lib/python3.8/site-packages/pony/orm/core.py", line 954, in _exec_sql
    connection = cache.reconnect(e)
  File "/usr/lib/python3.8/site-packages/pony/orm/core.py", line 1789, in reconnect
    if not provider.should_reconnect(exc): reraise(*sys.exc_info())
  File "/usr/lib/python3.8/site-packages/pony/utils/utils.py", line 95, in reraise
    try: raise exc.with_traceback(tb)
  File "/usr/lib/python3.8/site-packages/pony/orm/core.py", line 952, in _exec_sql
    try: new_id = provider.execute(cursor, sql, arguments, returning_id)
  File "<string>", line 2, in execute
  File "/usr/lib/python3.8/site-packages/pony/orm/dbapiprovider.py", line 64, in wrap_dbapi_exceptions
    raise ProgrammingError(e)
pony.orm.dbapiprovider.ProgrammingError: for SELECT DISTINCT, ORDER BY expressions must appear in select list
LINE 4: ORDER BY random()

Random tracks works fine though.

[vincentDcmps]

Hi could you execute album_songs test with python -m unittest tests/api/test_album_songs.py -vv to isolate an issue with your database

[sprnza]

Hi! Actually just before posting the issue I've dropped the DB and rescanned my collection (3186 albums currently). Here is the unit test results:

/tmp/supysonic # python3 -m unittest tests/api/test_album_songs.py -vv
test_get_album_list (tests.api.test_album_songs.AlbumSongsTestCase) ... ok
test_get_album_list2 (tests.api.test_album_songs.AlbumSongsTestCase) ... ok
test_get_random_songs (tests.api.test_album_songs.AlbumSongsTestCase) ... ok
test_get_starred (tests.api.test_album_songs.AlbumSongsTestCase) ... ok
test_get_starred2 (tests.api.test_album_songs.AlbumSongsTestCase) ... ok
test_now_playing (tests.api.test_album_songs.AlbumSongsTestCase) ... ok

----------------------------------------------------------------------
Ran 6 tests in 2.584s

OK

[vincentDcmps]

do you have check postgresql log?

[sprnza]

2021-01-07 17:26:25.280 GMT [125819] ERROR:  for SELECT DISTINCT, ORDER BY expressions must appear in select list at character 277
2021-01-07 17:26:25.280 GMT [125819] STATEMENT:  SELECT DISTINCT "folder"."id", "folder"."root", "folder"."name", "folder"."path", "folder"."path_hash", "folder"."created", "folder"."cover_art", "folder"."last_scan", "folder"."parent_id"
        FROM "track" "t-1", "folder" "folder"
        WHERE "t-1"."folder_id" = "folder"."id"
        ORDER BY random()
        LIMIT 15

[vincentDcmps]

I just test with Jamstash and I have same error with my instance it seem that jamstash use this end point to get random song getAlbumList.view?c=Jamstash&callback=angular.callbacks._6&f=jsonp&offset=0&p=enc:pass&size=15&type=random&u=vincent&v=1.9.0

on dsub no issues but he use rest/getRandomSongs.view endpoint

[vincentDcmps]

seem link to postgress no issue on my dev environment on SQLITE

[sprnza]

ponyorm's bug then? As I can see supysonic doesn't compose queries ponyorm does it instead.

[sprnza]

Thanks! The issue is solved for me with these changes!

[spl0k]

Thank you @vincentDcmps for the help on the issue 👍