8.4.2 Adding Document Library breaks empty site

Permalink
I have raised this here http://www.concrete5.org/developers/bugs/8-4-2/document-library-bre... but now I have a second client that is asking about this and if I can add a document Library to a new site he wants built, given this I need to raise the profile on this to try to get an answer.
To summarise, I had an issue when upgrading a site from 8.2.1 to 8.4.2, everything worked perfectly, but when I added the inbuilt document library I get the error
An exception occurred while executing 'SELECT distinct n.treeNodeID, if(nt.treeNodeTypeHandle='file', fv.fvTitle, n.treeNodeName) as folderItemName, if(nt.treeNodeTypeHandle='file', fv.fvDateAdded, n.dateModified) as folderItemModified, case when nt.treeNodeTypeHandle='search_preset' then 1 when nt.treeNodeTypeHandle='file_folder' then 2 else (10 + fvType) end as folderItemType, fv.fvSize as folderItemSize FROM TreeNodes n INNER JOIN TreeNodeTypes nt ON nt.treeNodeTypeID = n.treeNodeTypeID LEFT JOIN TreeFileNodes tf ON tf.treeNodeID = n.treeNodeID LEFT JOIN FileVersions fv ON tf.fID = fv.fID and fv.fvIsApproved = 1 LEFT JOIN FileSearchIndexAttributes fis ON fv.fID = fis.fID WHERE n.treeNodeParentID = ? ORDER BY fv.fvTitle asc LIMIT 20 OFFSET 0' with params ["7"]: SQLSTATE[HY000]: General error: 3065 Expression #1 of ORDER BY clause is not in SELECT list, references column 'iigi.fv.fvTitle' which is not in SELECT list; this is incompatible with DISTINCT

So I tried this in a brand new 8.4.2 install with the same issue
An exception occurred while executing 'SELECT distinct n.treeNodeID, if(nt.treeNodeTypeHandle='file', fv.fvTitle, n.treeNodeName) as folderItemName, if(nt.treeNodeTypeHandle='file', fv.fvDateAdded, n.dateModified) as folderItemModified, case when nt.treeNodeTypeHandle='search_preset' then 1 when nt.treeNodeTypeHandle='file_folder' then 2 else (10 + fvType) end as folderItemType, fv.fvSize as folderItemSize FROM TreeNodes n INNER JOIN TreeNodeTypes nt ON nt.treeNodeTypeID = n.treeNodeTypeID LEFT JOIN TreeFileNodes tf ON tf.treeNodeID = n.treeNodeID LEFT JOIN FileVersions fv ON tf.fID = fv.fID and fv.fvIsApproved = 1 LEFT JOIN FileSearchIndexAttributes fis ON fv.fID = fis.fID WHERE n.treeNodeParentID = ? ORDER BY fv.fvTitle asc LIMIT 20 OFFSET 0' with params ["7"]: SQLSTATE[HY000]: General error: 3065 Expression #1 of ORDER BY clause is not in SELECT list, references column 'iigi2.fv.fvTitle' which is not in SELECT list; this is incompatible with DISTINCT

There is a bit more detail in the Bug report, has anyone experienced this and how was it fixed?

Thanks
Martyn

FaganSystems
 
JohntheFish replied on at Permalink Reply
JohntheFish
If this happens on the latest core, you should copy that into an issue on GitHub
FaganSystems replied on at Permalink Reply
FaganSystems
As you suggested reported as github issue #7311
FaganSystems replied on at Permalink Reply
FaganSystems
having thought overnight I have retested with the latest development branch and get
An exception occurred while executing 'SELECT distinct n.treeNodeID, if(nt.treeNodeTypeHandle='file', fv.fvTitle, n.treeNodeName) as folderItemName, if(nt.treeNodeTypeHandle='file', fv.fvDateAdded, n.dateModified) as folderItemModified, case when nt.treeNodeTypeHandle='search_preset' then 1 when nt.treeNodeTypeHandle='file_folder' then 2 else (10 + fvType) end as folderItemType, fv.fvSize as folderItemSize FROM TreeNodes n INNER JOIN TreeNodeTypes nt ON nt.treeNodeTypeID = n.treeNodeTypeID LEFT JOIN TreeFileNodes tf ON tf.treeNodeID = n.treeNodeID LEFT JOIN FileVersions fv ON tf.fID = fv.fID and fv.fvIsApproved = 1 LEFT JOIN FileSetFiles fsf ON tf.fID = fsf.fID LEFT JOIN FileSearchIndexAttributes fis ON fv.fID = fis.fID WHERE ((nt.treeNodeTypeHandle = "file_folder") OR (fsf.fsID = 1)) AND (n.treeNodeParentID = ?) ORDER BY fv.fvTitle asc LIMIT 20 OFFSET 0' with params ["7"]: SQLSTATE[HY000]: General error: 3065 Expression #1 of ORDER BY clause is not in SELECT list, references column 'c5dev.fv.fvTitle' which is not in SELECT list; this is incompatible with DISTINCT
FaganSystems replied on at Permalink Best Answer Reply
FaganSystems
Comment from Andrew Embler on github answering this issue
Yeah, this is due to stricter mysql configurations on some newer systems and some newer versions of MySQL. It's the only_full_group_by setting in MySQL. To get around this for the time being you can turn that setting off – but this is something we should fix.

I have tested this and can confirm this workaround works.