[GH-ISSUE #1787] Incomplete fix for postgresql #986

Closed
opened 2026-02-26 02:34:50 +03:00 by kerem · 2 comments
Owner

Originally created by @MyPod on GitHub (Jul 14, 2024).
Original GitHub issue: https://github.com/koel/koel/issues/1787

I believe that the changes in 475de94 are incomplete. I am unable to log in entirely after the upgrade.

The issue is reproducible on a stock v7.0.3 setup relying on PostgreSQL. I'm using PostgreSQL 15 but I don't believe this is relevant.

Simply attempting to log in via the browser after the setup will fail due to a server-side error.

laravel.log contains the following:

[previous exception] [object] (PDOException(code: 42P10): SQLSTATE[42P10]: Invalid column reference: 7 ERROR:  SELECT DISTINCT ON expressions must match initial ORDER BY expressions
LINE 1: select distinct on (\"songs\".\"id\") \"songs\".*, \"albums\".\"name\"...
                            ^ at /var/www/koel/vendor/laravel/framework/src/Illuminate/Database/Connection.php:428)
[stacktrace]
#0 /var/www/koel/vendor/laravel/framework/src/Illuminate/Database/Connection.php(428): PDOStatement->execute()
#1 /var/www/koel/vendor/laravel/framework/src/Illuminate/Database/Connection.php(816): Illuminate\\Database\\Connection->Illuminate\\Database\\{closure}()
#2 /var/www/koel/vendor/laravel/framework/src/Illuminate/Database/Connection.php(783): Illuminate\\Database\\Connection->runQueryCallback()
#3 /var/www/koel/vendor/laravel/framework/src/Illuminate/Database/Connection.php(414): Illuminate\\Database\\Connection->run()
#4 /var/www/koel/vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php(2871): Illuminate\\Database\\Connection->select()
#5 /var/www/koel/vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php(2860): Illuminate\\Database\\Query\\Builder->runSelect()
#6 /var/www/koel/vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php(3414): Illuminate\\Database\\Query\\Builder->Illuminate\\Database\\Query\\{closure}()
#7 /var/www/koel/vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php(2859): Illuminate\\Database\\Query\\Builder->onceWithColumns()
#8 /var/www/koel/vendor/laravel/framework/src/Illuminate/Database/Eloquent/Builder.php(738): Illuminate\\Database\\Query\\Builder->get()
#9 /var/www/koel/vendor/laravel/framework/src/Illuminate/Database/Eloquent/Builder.php(722): Illuminate\\Database\\Eloquent\\Builder->getModels()
#10 /var/www/koel/app/Repositories/SongRepository.php(54): Illuminate\\Database\\Eloquent\\Builder->get()
#11 /var/www/koel/app/Http/Controllers/API/FetchOverviewController.php(21): App\\Repositories\\SongRepository->getMostPlayed()
#12 /var/www/koel/vendor/laravel/framework/src/Illuminate/Routing/Controller.php(54): App\\Http\\Controllers\\API\\FetchOverviewController->__invoke()
#13 /var/www/koel/vendor/laravel/framework/src/Illuminate/Routing/ControllerDispatcher.php(43): Illuminate\\Routing\\Controller->callAction()
#14 /var/www/koel/vendor/laravel/framework/src/Illuminate/Routing/Route.php(259): Illuminate\\Routing\\ControllerDispatcher->dispatch()
#15 /var/www/koel/vendor/laravel/framework/src/Illuminate/Routing/Route.php(205): Illuminate\\Routing\\Route->runController()
#16 /var/www/koel/vendor/laravel/framework/src/Illuminate/Routing/Router.php(806): Illuminate\\Routing\\Route->run()
#17 /var/www/koel/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(144): Illuminate\\Routing\\Router->Illuminate\\Routing\\{closure}()
#18 /var/www/koel/app/Http/Middleware/Authenticate.php(14): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}()
#19 /var/www/koel/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(183): App\\Http\\Middleware\\Authenticate->handle()
#20 /var/www/koel/vendor/laravel/framework/src/Illuminate/Routing/Middleware/SubstituteBindings.php(50): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}()
#21 /var/www/koel/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(183): Illuminate\\Routing\\Middleware\\SubstituteBindings->handle()
#22 /var/www/koel/vendor/laravel/framework/src/Illuminate/Routing/Middleware/ThrottleRequests.php(159): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}()
#23 /var/www/koel/vendor/laravel/framework/src/Illuminate/Routing/Middleware/ThrottleRequests.php(90): Illuminate\\Routing\\Middleware\\ThrottleRequests->handleRequest()
#24 /var/www/koel/app/Http/Middleware/ThrottleRequests.php(14): Illuminate\\Routing\\Middleware\\ThrottleRequests->handle()
#25 /var/www/koel/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(183): App\\Http\\Middleware\\ThrottleRequests->handle()
#26 /var/www/koel/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(119): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}()
#27 /var/www/koel/vendor/laravel/framework/src/Illuminate/Routing/Router.php(805): Illuminate\\Pipeline\\Pipeline->then()
#28 /var/www/koel/vendor/laravel/framework/src/Illuminate/Routing/Router.php(784): Illuminate\\Routing\\Router->runRouteWithinStack()
#29 /var/www/koel/vendor/laravel/framework/src/Illuminate/Routing/Router.php(748): Illuminate\\Routing\\Router->runRoute()
#30 /var/www/koel/vendor/laravel/framework/src/Illuminate/Routing/Router.php(737): Illuminate\\Routing\\Router->dispatchToRoute()
#31 /var/www/koel/vendor/laravel/framework/src/Illuminate/Foundation/Http/Kernel.php(200): Illuminate\\Routing\\Router->dispatch()
#32 /var/www/koel/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(144): Illuminate\\Foundation\\Http\\Kernel->Illuminate\\Foundation\\Http\\{closure}()
#33 /var/www/koel/vendor/laravel/framework/src/Illuminate/Http/Middleware/TrustHosts.php(48): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}()
#34 /var/www/koel/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(183): Illuminate\\Http\\Middleware\\TrustHosts->handle()
#35 /var/www/koel/app/Http/Middleware/ForceHttps.php(28): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}()
#36 /var/www/koel/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(183): App\\Http\\Middleware\\ForceHttps->handle()
#37 /var/www/koel/vendor/laravel/framework/src/Illuminate/Foundation/Http/Middleware/TransformsRequest.php(21): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}()
#38 /var/www/koel/vendor/laravel/framework/src/Illuminate/Foundation/Http/Middleware/TrimStrings.php(40): Illuminate\\Foundation\\Http\\Middleware\\TransformsRequest->handle()
#39 /var/www/koel/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(183): Illuminate\\Foundation\\Http\\Middleware\\TrimStrings->handle()
#40 /var/www/koel/vendor/laravel/framework/src/Illuminate/Foundation/Http/Middleware/ValidatePostSize.php(27): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}()
#41 /var/www/koel/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(183): Illuminate\\Foundation\\Http\\Middleware\\ValidatePostSize->handle()
#42 /var/www/koel/vendor/laravel/framework/src/Illuminate/Foundation/Http/Middleware/PreventRequestsDuringMaintenance.php(99): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}()
#43 /var/www/koel/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(183): Illuminate\\Foundation\\Http\\Middleware\\PreventRequestsDuringMaintenance->handle()
#44 /var/www/koel/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(119): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}()
#45 /var/www/koel/vendor/laravel/framework/src/Illuminate/Foundation/Http/Kernel.php(175): Illuminate\\Pipeline\\Pipeline->then()
#46 /var/www/koel/vendor/laravel/framework/src/Illuminate/Foundation/Http/Kernel.php(144): Illuminate\\Foundation\\Http\\Kernel->sendRequestThroughRouter()
#47 /var/www/koel/public/index.php(56): Illuminate\\Foundation\\Http\\Kernel->handle()
#48 {main}
"}

Is the distinct() required on line 64 of app/Builders/SongBuilder.php? I've tried commenting it out and it works around my problem.

Environment

  • Koel version 7.0.3
  • OS: Debian Bookworm 12.6
  • Browser Firefox 128
  • PHP version 8.2.20-1~deb12u1
  • Node version 18.19.0+dfsg-6~deb12u2
Originally created by @MyPod on GitHub (Jul 14, 2024). Original GitHub issue: https://github.com/koel/koel/issues/1787 I believe that the changes in 475de94 are incomplete. I am unable to log in entirely after the upgrade. The issue is reproducible on a stock v7.0.3 setup relying on PostgreSQL. I'm using PostgreSQL 15 but I don't believe this is relevant. Simply attempting to log in via the browser after the setup will fail due to a server-side error. laravel.log contains the following: ``` [previous exception] [object] (PDOException(code: 42P10): SQLSTATE[42P10]: Invalid column reference: 7 ERROR: SELECT DISTINCT ON expressions must match initial ORDER BY expressions LINE 1: select distinct on (\"songs\".\"id\") \"songs\".*, \"albums\".\"name\"... ^ at /var/www/koel/vendor/laravel/framework/src/Illuminate/Database/Connection.php:428) [stacktrace] #0 /var/www/koel/vendor/laravel/framework/src/Illuminate/Database/Connection.php(428): PDOStatement->execute() #1 /var/www/koel/vendor/laravel/framework/src/Illuminate/Database/Connection.php(816): Illuminate\\Database\\Connection->Illuminate\\Database\\{closure}() #2 /var/www/koel/vendor/laravel/framework/src/Illuminate/Database/Connection.php(783): Illuminate\\Database\\Connection->runQueryCallback() #3 /var/www/koel/vendor/laravel/framework/src/Illuminate/Database/Connection.php(414): Illuminate\\Database\\Connection->run() #4 /var/www/koel/vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php(2871): Illuminate\\Database\\Connection->select() #5 /var/www/koel/vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php(2860): Illuminate\\Database\\Query\\Builder->runSelect() #6 /var/www/koel/vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php(3414): Illuminate\\Database\\Query\\Builder->Illuminate\\Database\\Query\\{closure}() #7 /var/www/koel/vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php(2859): Illuminate\\Database\\Query\\Builder->onceWithColumns() #8 /var/www/koel/vendor/laravel/framework/src/Illuminate/Database/Eloquent/Builder.php(738): Illuminate\\Database\\Query\\Builder->get() #9 /var/www/koel/vendor/laravel/framework/src/Illuminate/Database/Eloquent/Builder.php(722): Illuminate\\Database\\Eloquent\\Builder->getModels() #10 /var/www/koel/app/Repositories/SongRepository.php(54): Illuminate\\Database\\Eloquent\\Builder->get() #11 /var/www/koel/app/Http/Controllers/API/FetchOverviewController.php(21): App\\Repositories\\SongRepository->getMostPlayed() #12 /var/www/koel/vendor/laravel/framework/src/Illuminate/Routing/Controller.php(54): App\\Http\\Controllers\\API\\FetchOverviewController->__invoke() #13 /var/www/koel/vendor/laravel/framework/src/Illuminate/Routing/ControllerDispatcher.php(43): Illuminate\\Routing\\Controller->callAction() #14 /var/www/koel/vendor/laravel/framework/src/Illuminate/Routing/Route.php(259): Illuminate\\Routing\\ControllerDispatcher->dispatch() #15 /var/www/koel/vendor/laravel/framework/src/Illuminate/Routing/Route.php(205): Illuminate\\Routing\\Route->runController() #16 /var/www/koel/vendor/laravel/framework/src/Illuminate/Routing/Router.php(806): Illuminate\\Routing\\Route->run() #17 /var/www/koel/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(144): Illuminate\\Routing\\Router->Illuminate\\Routing\\{closure}() #18 /var/www/koel/app/Http/Middleware/Authenticate.php(14): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}() #19 /var/www/koel/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(183): App\\Http\\Middleware\\Authenticate->handle() #20 /var/www/koel/vendor/laravel/framework/src/Illuminate/Routing/Middleware/SubstituteBindings.php(50): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}() #21 /var/www/koel/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(183): Illuminate\\Routing\\Middleware\\SubstituteBindings->handle() #22 /var/www/koel/vendor/laravel/framework/src/Illuminate/Routing/Middleware/ThrottleRequests.php(159): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}() #23 /var/www/koel/vendor/laravel/framework/src/Illuminate/Routing/Middleware/ThrottleRequests.php(90): Illuminate\\Routing\\Middleware\\ThrottleRequests->handleRequest() #24 /var/www/koel/app/Http/Middleware/ThrottleRequests.php(14): Illuminate\\Routing\\Middleware\\ThrottleRequests->handle() #25 /var/www/koel/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(183): App\\Http\\Middleware\\ThrottleRequests->handle() #26 /var/www/koel/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(119): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}() #27 /var/www/koel/vendor/laravel/framework/src/Illuminate/Routing/Router.php(805): Illuminate\\Pipeline\\Pipeline->then() #28 /var/www/koel/vendor/laravel/framework/src/Illuminate/Routing/Router.php(784): Illuminate\\Routing\\Router->runRouteWithinStack() #29 /var/www/koel/vendor/laravel/framework/src/Illuminate/Routing/Router.php(748): Illuminate\\Routing\\Router->runRoute() #30 /var/www/koel/vendor/laravel/framework/src/Illuminate/Routing/Router.php(737): Illuminate\\Routing\\Router->dispatchToRoute() #31 /var/www/koel/vendor/laravel/framework/src/Illuminate/Foundation/Http/Kernel.php(200): Illuminate\\Routing\\Router->dispatch() #32 /var/www/koel/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(144): Illuminate\\Foundation\\Http\\Kernel->Illuminate\\Foundation\\Http\\{closure}() #33 /var/www/koel/vendor/laravel/framework/src/Illuminate/Http/Middleware/TrustHosts.php(48): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}() #34 /var/www/koel/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(183): Illuminate\\Http\\Middleware\\TrustHosts->handle() #35 /var/www/koel/app/Http/Middleware/ForceHttps.php(28): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}() #36 /var/www/koel/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(183): App\\Http\\Middleware\\ForceHttps->handle() #37 /var/www/koel/vendor/laravel/framework/src/Illuminate/Foundation/Http/Middleware/TransformsRequest.php(21): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}() #38 /var/www/koel/vendor/laravel/framework/src/Illuminate/Foundation/Http/Middleware/TrimStrings.php(40): Illuminate\\Foundation\\Http\\Middleware\\TransformsRequest->handle() #39 /var/www/koel/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(183): Illuminate\\Foundation\\Http\\Middleware\\TrimStrings->handle() #40 /var/www/koel/vendor/laravel/framework/src/Illuminate/Foundation/Http/Middleware/ValidatePostSize.php(27): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}() #41 /var/www/koel/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(183): Illuminate\\Foundation\\Http\\Middleware\\ValidatePostSize->handle() #42 /var/www/koel/vendor/laravel/framework/src/Illuminate/Foundation/Http/Middleware/PreventRequestsDuringMaintenance.php(99): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}() #43 /var/www/koel/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(183): Illuminate\\Foundation\\Http\\Middleware\\PreventRequestsDuringMaintenance->handle() #44 /var/www/koel/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(119): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}() #45 /var/www/koel/vendor/laravel/framework/src/Illuminate/Foundation/Http/Kernel.php(175): Illuminate\\Pipeline\\Pipeline->then() #46 /var/www/koel/vendor/laravel/framework/src/Illuminate/Foundation/Http/Kernel.php(144): Illuminate\\Foundation\\Http\\Kernel->sendRequestThroughRouter() #47 /var/www/koel/public/index.php(56): Illuminate\\Foundation\\Http\\Kernel->handle() #48 {main} "} ``` Is the _distinct()_ required on line 64 of app/Builders/SongBuilder.php? I've tried commenting it out and it works around my problem. **Environment** - Koel version 7.0.3 - OS: Debian Bookworm 12.6 - Browser Firefox 128 - PHP version 8.2.20-1~deb12u1 - Node version 18.19.0+dfsg-6~deb12u2
kerem closed this issue 2026-02-26 02:34:50 +03:00
Author
Owner

@phanan commented on GitHub (Jul 14, 2024):

Let's consolidate at #1785.

<!-- gh-comment-id:2227378272 --> @phanan commented on GitHub (Jul 14, 2024): Let's consolidate at #1785.
Author
Owner

@MyPod commented on GitHub (Jul 14, 2024):

Not a problem. There wasn't yet #1785 when I started typing this one up

<!-- gh-comment-id:2227430267 --> @MyPod commented on GitHub (Jul 14, 2024): Not a problem. There wasn't yet #1785 when I started typing this one up
Sign in to join this conversation.
No milestone
No project
No assignees
1 participant
Notifications
Due date
The due date is invalid or out of range. Please use the format "yyyy-mm-dd".

No due date set.

Dependencies

No dependencies set.

Reference
starred/koel-koel#986
No description provided.