MyBB Community Forums
[F] Concat not defined in PostgreSQL [C-Michael83] - Printable Version

+- MyBB Community Forums (https://community.mybb.com)
+-- Forum: Community Archive (https://community.mybb.com/forum-106.html)
+--- Forum: Archived Forums (https://community.mybb.com/forum-143.html)
+---- Forum: Archived Development and Support (https://community.mybb.com/forum-155.html)
+----- Forum: Archived Bug Reports (https://community.mybb.com/forum-74.html)
+------ Forum: MyBB 1.4.4 (https://community.mybb.com/forum-110.html)
+------ Thread: [F] Concat not defined in PostgreSQL [C-Michael83] (/thread-41616.html)



[F] Concat not defined in PostgreSQL [C-Michael83] - Peter de Sowaro - 12-07-2008

It occurs in many places, for example when sending mass mail to users.

MyBB tries to use CONCAT in SQL queries, but it is not defined in PostgreSQL. My suggestion is to define it while installing/upgrading with:

CREATE FUNCTION CONCAT(text,text)
RETURNS text
LANGUAGE SQL
AS $$ SELECT 'a'||'b' $$;

CREATE FUNCTION CONCAT(text,text,text)
RETURNS text
LANGUAGE SQL
AS $$ SELECT 'a'||'b'||'c' $$;



RE: Concat not defined in PostgreSQL - Ryan Gordon - 12-07-2008

That's not how we do it - We use a php switch statement based on the dbms. Where are these errors happening?


RE: Concat not defined in PostgreSQL - Peter de Sowaro - 12-07-2008

But that's what has been done wrong - there are a lot of SQL queries called independently of db_pgsql.php definitions. For example concat is called in so many places outside of db_*.php files! So you can search for 'CONCAT' in all MyBB files and replace the queries with something database-dependent, or create the functions above.

The occurences of CONCAT:
[email protected] ~/.homepage/forum $ grep -R 'CONCAT' *
forumdisplay.php:                               $query = $db->simple_select("modtools", 'tid, name', "(CONCAT(',',forums,',') LIKE '%,$fid,%' OR CONCAT(',',forums,',') LIKE '%,-1,%' OR forums='') AND type = 't'");
inc/functions_massmail.php:                                     $additional_sql .= " OR CONCAT(',',additionalgroups,',') LIKE '%,{$usergroup},%'";
inc/functions_search.php:                                                       WHERE INSTR(CONCAT(',',parentlist,','),',$forum,') > 0 AND active!=0 AND (ISNULL(p.fid) OR p.cansearch=1)
inc/functions_search.php:                                                       WHERE INSTR(CONCAT(',',parentlist,','),',$forum,') > 0 AND active!=0 AND (ISNULL(p.fid) OR p.cansearch=1)
inc/class_moderation.php:                                               $additional_groups .= " OR CONCAT(',',u.additionalgroups,',') LIKE ',{$group['gid']},'";
lalal/modules/forum/management.php:                                             $query = $db->simple_select("forums", "fid", "CONCAT(',',parentlist,',') LIKE '%,$fid,%'");
lalal/modules/forum/management.php:                             $query = $db->simple_select("forums", "*", "CONCAT(',', parentlist, ',') LIKE '%,$fid,%'");
lalal/modules/forum/management.php:                             $db->delete_query("forums", "CONCAT(',',parentlist,',') LIKE '%,$fid,%'");
lalal/modules/user/users.php:                                   $additional_sql .= "OR CONCAT(',',additionalgroups,',') LIKE '%,{$usergroup},%'";
lalal/modules/user/admin_permissions.php:                               $secondary_group_list .= " OR CONCAT(',', u.additionalgroups,',') LIKE '%,{$gid},%'";
lalal/modules/user/groups.php:                          $query = $db->simple_select("users", "uid", "CONCAT(',',additionalgroups,',') LIKE '%,{$usergroup['gid']},%'");
lalal/modules/user/groups.php:                          LEFT JOIN ".TABLE_PREFIX."usergroups g ON (CONCAT(',', u.additionalgroups, ',') LIKE CONCAT('%,', g.gid, ',%'))
lalal/error.log:LINE 1: ...2)  OR ','||additionalgroups||',' LIKE '%,2,%' OR CONCAT(','...
lalal/error.log:Query: SELECT COUNT(uid) AS num FROM mybb_users u WHERE u.allownotices=1 AND (u.usergroup IN (2)  OR ','||additionalgroups||',' LIKE '%,2,%'OR CONCAT(',',additionalgroups,',') LIKE '%,2,%')</message>
managegroup.php:                        $query = $db->simple_select("users", "*", "CONCAT(',',additionalgroups,',') LIKE '%,".$mybb->input['gid'].",%' OR usergroup='".$mybb->input['gid']."'", array('order_by' => 'username'));
member.php:                                     $query = $db->simple_select("forums", "fid", "INSTR(CONCAT(',',parentlist,','),',".intval($mybb->input['fid']).",') > 0");
report.php:                                             LEFT JOIN ".TABLE_PREFIX."usergroups g ON (((CONCAT(','|| u.additionalgroups|| ',') LIKE CONCAT('%,'|| g.gid|| ',%')) OR u.usergroup = g.gid))
report.php:                                             LEFT JOIN ".TABLE_PREFIX."usergroups g ON (((CONCAT(',', u.additionalgroups, ',') LIKE CONCAT('%,', g.gid, ',%')) OR u.usergroup = g.gid))
search.php:                                     $query = $db->simple_select("modtools", "tid, name", "type='t' AND (CONCAT(',',forums,',') LIKE '%,-1,%' OR forums='')");
search.php:                                     $query = $db->simple_select("modtools", "tid, name, type", "type='p' AND (CONCAT(',',forums,',') LIKE '%,-1,%' OR forums='')");
showthread.php:                         $query = $db->simple_select("modtools", "tid, name, type", "CONCAT(',',forums,',') LIKE '%,$fid,%' OR CONCAT(',',forums,',') LIKE '%,-1,%' OR forums=''");
usercp.php:                             LEFT JOIN ".TABLE_PREFIX."users u ON(((CONCAT(',', u.additionalgroups, ',') LIKE CONCAT('%,', g.gid, ',%')) OR u.usergroup = g.gid))



RE: Concat not defined in PostgreSQL - Ryan Gordon - 12-07-2008

If you looked most of those do have the proper switches. It's only in a few rare cases where there may be a programming mistake or the like


RE: Concat not defined in PostgreSQL - Peter de Sowaro - 12-08-2008

Ok, I understand.

My bug comes from inc/functions_massmail.php:
switch($db->type)
{
  case "pgsql":
  case "sqlite3":
  case "sqlite2":
    $additional_sql .= " OR ','||additionalgroups||',' LIKE '%,{$usergroup},%'";
  default:
    $additional_sql .= " OR CONCAT(',',additionalgroups,',') LIKE '%,{$usergroup},%'";
}
I suppose there should be:
switch($db->type)
{
  case "pgsql":
  case "sqlite3":
  case "sqlite2":
    $additional_sql .= " OR ','||additionalgroups||',' LIKE '%,{$usergroup},%'";
    break;
  default:
    $additional_sql .= " OR CONCAT(',',additionalgroups,',') LIKE '%,{$usergroup},%'";
}
It's common to forget a break in switch Smile
No other problems with concat.


[F] Concat not defined in PostgreSQL - Ryan Gordon - 12-08-2008

Thank you for your bug report.

This bug has been fixed in our internal code repository. Please note that the problem will not be fixed here until these forums are updated.

With regards,
MyBB Group