Bug 24030

Summary: GetItemsForInventory failing with "ORDER BY clause is not in SELECT list"
Product: Koha Reporter: Jonathan Druart <jonathan.druart+koha>
Component: Test SuiteAssignee: Jonathan Druart <jonathan.druart+koha>
Status: CLOSED FIXED QA Contact: Testopia <testopia>
Severity: normal    
Priority: P5 - low CC: martin.renvoize
Version: unspecified   
Hardware: All   
OS: All   
Change sponsored?: --- Patch complexity: ---
Documentation contact: Documentation submission:
Text to go in the release notes:
Version(s) released in:
19.11.00
Bug Depends on:    
Bug Blocks: 24002    
Attachments: Bug 24030: Remove OldWay comparaison from GetItemsForInventory.t
Bug 24030: Fix GetItemsForInventory under MySQL 8
Bug 24030: Remove OldWay comparaison from GetItemsForInventory.t
Bug 24030: Fix GetItemsForInventory under MySQL 8

Description Jonathan Druart 2019-11-13 10:07:22 UTC
t/db_dependent/Items/GetItemsForInventory.t .. 1/9 DBD::mysql::st execute failed: Expression #1 of ORDER BY clause is not in SELECT list, references column 'koha_kohadev.items.cn_sort' which is not in SELECT list; this is incompatible with DISTINCT [for Statement "
        SELECT DISTINCT(items.itemnumber), barcode, itemcallnumber, title, author, biblio.biblionumber, biblio.frameworkcode, datelastseen, homebranch, location, notforloan, damaged, itemlost, withdrawn, stocknumber

        FROM items
        LEFT JOIN biblio ON items.biblionumber = biblio.biblionumber
        LEFT JOIN biblioitems on items.biblionumber = biblioitems.biblionumber
     ORDER BY items.cn_sort, itemcallnumber, title"] at /kohadevbox/koha/C4/Items.pm line 838.
Comment 1 Jonathan Druart 2019-11-13 10:12:45 UTC
Created attachment 95356 [details] [review]
Bug 24030: Remove OldWay comparaison from GetItemsForInventory.t

This has been introduced to make sure there were no regression at a
given point. But now we are fixing the "old way" so it does not make
sense to keep it any longer
Comment 2 Jonathan Druart 2019-11-13 10:12:50 UTC
Created attachment 95357 [details] [review]
Bug 24030: Fix GetItemsForInventory under MySQL 8

t/db_dependent/Items/GetItemsForInventory.t .. 1/9 DBD::mysql::st execute failed: Expression #1 of ORDER BY clause is not in SELECT list, references column 'koha_kohadev.items.cn_sort' which is not in SELECT list; this is incompatible with DISTINCT [for Statement "
        SELECT DISTINCT(items.itemnumber), barcode, itemcallnumber, title, author, biblio.biblionumber, biblio.frameworkcode, datelastseen, homebranch, location, notforloan, damaged, itemlost, withdrawn, stocknumber

        FROM items
        LEFT JOIN biblio ON items.biblionumber = biblio.biblionumber
        LEFT JOIN biblioitems on items.biblionumber = biblioitems.biblionumber
     ORDER BY items.cn_sort, itemcallnumber, title"] at /kohadevbox/koha/C4/Items.pm line 838.

We simply follow what the error says, and add items.cn_sort to the SELECT list
Comment 3 Martin Renvoize 2019-11-13 13:24:57 UTC
Good catch, obvious corrections. Passing QA and Pushing.
Comment 4 Martin Renvoize 2019-11-13 13:25:51 UTC
Created attachment 95377 [details] [review]
Bug 24030: Remove OldWay comparaison from GetItemsForInventory.t

This has been introduced to make sure there were no regression at a
given point. But now we are fixing the "old way" so it does not make
sense to keep it any longer

Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com>
Comment 5 Martin Renvoize 2019-11-13 13:25:54 UTC
Created attachment 95378 [details] [review]
Bug 24030: Fix GetItemsForInventory under MySQL 8

t/db_dependent/Items/GetItemsForInventory.t .. 1/9 DBD::mysql::st execute failed: Expression #1 of ORDER BY clause is not in SELECT list, references column 'koha_kohadev.items.cn_sort' which is not in SELECT list; this is incompatible with DISTINCT [for Statement "
        SELECT DISTINCT(items.itemnumber), barcode, itemcallnumber, title, author, biblio.biblionumber, biblio.frameworkcode, datelastseen, homebranch, location, notforloan, damaged, itemlost, withdrawn, stocknumber

        FROM items
        LEFT JOIN biblio ON items.biblionumber = biblio.biblionumber
        LEFT JOIN biblioitems on items.biblionumber = biblioitems.biblionumber
     ORDER BY items.cn_sort, itemcallnumber, title"] at /kohadevbox/koha/C4/Items.pm line 838.

We simply follow what the error says, and add items.cn_sort to the SELECT list

Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com>
Comment 6 Martin Renvoize 2019-11-13 13:26:25 UTC
Nice work!

Pushed to master for 19.11.00