[F] Missing PostgreSQL concat function

[DoctorCasino]

MyBB Version: 1.4.2
PHP Version: 4.3.10
DB Version: PostgreSQL 8.3.3

I often got an SQL error, which says that the function concat does not exist.

e.g. in:
/admin/index.php?module=user/users&action=search&results=1&conditions=a:1:{s:9"usergroup";s:1:"5";}

when I wanted to watch users that are not activated.

And in PostgreSQL there is no concat function. Since there is no way for varying parameters in PostgreSQL function (until now), I created my own concat functions. Since I'm new to MyBB, I don't know how many arguments could be used for concat by MyBB. I've got functions for a maximum of four parameters.

After I created theses functions everthing worked fine

Maybe one could create these functions when installing MyBB:

Code:

CREATE OR REPLACE FUNCTION concat(text, text) RETURNS text AS $$
    SELECT $1 || $2;
$$ LANGUAGE 'sql';


CREATE OR REPLACE FUNCTION concat(text, text, text) RETURNS text AS $$
    SELECT $1 || $2 || $3;
$$ LANGUAGE 'sql';


CREATE OR REPLACE FUNCTION concat(text, text, text, text) RETURNS text AS $$
    SELECT $1 || $2 || $3 || $4;
$$ LANGUAGE 'sql';

[Ryan Gordon]

We use switch statements on the db engine that is currently being used to use the right query

[DoctorCasino]

(10-21-2008 04:20 PM)Ryan Gordon Wrote:  We use switch statements on the db engine that is currently being used to use the right query

I see, so I dropped my functions to get back to the errors:

This happens when I am in the admin dashbord and I click on the users awaiting activation:

Quote:SQL Error:
0 - ERROR: function concat(unknown, character varying, unknown) does not exist at character 144 HINT: No function matches the given name and argument types. You might need to add explicit type casts.
Query:
SELECT COUNT(u.uid) AS num_results FROM mybb_users u WHERE 1=1 AND (u.usergroup IN (5) OR ','||additionalgroups||',' LIKE '%,5,%'OR CONCAT(',',additionalgroups,',') LIKE '%,5,%')

[Ryan Gordon]

PHP Code:

switch($db->type)
            {
                case "pgsql":
                case "sqlite3":
                case "sqlite2":
                    $additional_sql .= " OR ','||additionalgroups||',' LIKE '%,{$usergroup},%'";
                default:
                    $additional_sql .= "OR CONCAT(',',additionalgroups,',') LIKE '%,{$usergroup},%'";
            } 


Not sure what's wrong. Looks like you've made some modifications

[DoctorCasino]

(10-22-2008 01:56 AM)Ryan Gordon Wrote:  

PHP Code:

switch($db->type)
            {
                case "pgsql":
                case "sqlite3":
                case "sqlite2":
                    $additional_sql .= " OR ','||additionalgroups||',' LIKE '%,{$usergroup},%'";
                default:
                    $additional_sql .= "OR CONCAT(',',additionalgroups,',') LIKE '%,{$usergroup},%'";
            } 


Not sure what's wrong. Looks like you've made some modifications

I can't test it right now, but I think there should be a

PHP Code:

break; 


after the first three cases!?

---

Ok, I added a

PHP Code:

break; 


at line 1926 and it works properly now.

[Ryan Gordon]

Ah, woops - Must have missed that

[Dennis Tsang]

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

[Michael S.]

This fix seems to be missing in SVN.

[Ryan Gordon]

The fix should be in the SVN now