Banshee, the secure PHP framework

Forum

MySQL >= v5.7.5 and queries with distinct/order by

Joe Schmoe
9 February 2018, 05:28
I am getting a SQL error when using the search module.

Looks like they changed the default SQL mode to include ONLY_FULL_GROUP_BY in v5.7.5 of MySQL.

This means that a query with fields in the ORDER BY clause also need to have those fields in the SELECT statement when they are grouped by DISTINCT.

From what I can see, there are a couple of options:

1) Change my.cnf and remove ONLY_FULL_GROUP_BY from "sql-mode" variable. Probably not an option for people on shared hosting.

2) Fix queries.


SQL query: select distinct concat('/forum/topic/', t.id, '/#', m.id) as url, concat(f.title, ' :: ', t.subject) as text, m.content from forums f, forum_topics t, forum_messages m where f.id=t.forum_id and t.id=m.topic_id and ((t.subject like '%test%') or (m.content like '%test%') or (m.username like '%test%')) order by m.timestamp desc
Error message: Expression #1 of ORDER BY clause is not in SELECT list, references column 'banshee61.m.timestamp' which is not in SELECT list; this is incompatible with DISTINCT
SQL query: select distinct concat('/poll/', p.id) as url, p.question as text from polls p, poll_answers a where p.id=a.poll_id and now()>=end and ((p.question like '%test%') or (a.answer like '%test%')) order by begin desc
Error message: Expression #1 of ORDER BY clause is not in SELECT list, references column 'banshee61.p.begin' which is not in SELECT list; this is incompatible with DISTINCT
SQL query: select distinct concat('/weblog/', w.id) as url, w.title as text, w.content from weblogs w left join weblog_comments c on w.id=c.weblog_id where ((w.title like '%test%') or (w.content like '%test%') or (c.content like '%test%') or (c.author like '%test%')) order by w.timestamp desc
Error message: Expression #1 of ORDER BY clause is not in SELECT list, references column 'banshee61.w.timestamp' which is not in SELECT list; this is incompatible with DISTINCT
Joe Schmoe
9 February 2018, 05:36
Queries are in models/search.php...

Line 56: search_forum()

Line 152: search_polls()

Line 196: search_weblog()

Hugo Leisink
13 February 2018, 00:55
Please, try this new search model and let me know if it works.
Joe Schmoe
13 February 2018, 07:30
It works and is exactly the same as the code I changed to fix the problem.

Thanks!