[2.0 b4] Error: db function instr

[Kakao]

2.0b4 Centos 5.1 Postgresql 8.3.3

After installing and selecting a new language i get this error when i click on a board link:

Code Select Expand

ERROR: function instr(text, unknown) does not exist at character 361
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
Arquivo: /var/www/html/example.com/Sources/MessageIndex.php
Linha: 248

The code is this:

Code Select Expand

        // Nosey, nosey - who's viewing this topic?
        if (!empty($settings['display_who_viewing']))
        {
                $context['view_members'] = array();
                $context['view_members_list'] = array();
                $context['view_num_hidden'] = 0;

                $request = $smcFunc['db_query']('', '
                        SELECT
                                lo.id_member, lo.log_time, mem.real_name, mem.member_name, mem.show_online,
                                mg.online_color, mg.id_group, mg.group_name
                        FROM {db_prefix}log_online AS lo
                                LEFT JOIN {db_prefix}members AS mem ON (mem.id_member = lo.id_member)
                                LEFT JOIN {db_prefix}membergroups AS mg ON (mg.id_group = CASE WHEN mem.id_group = {int:reg_member_group\
} THEN mem.id_post_group ELSE mem.id_group END)
                        WHERE INSTR(lo.url, {string:in_url_string}) OR lo.session = {string:session}',
                        array(
                                'reg_member_group' => 0,
                                'in_url_string' => 's:5:"board";i:' . $board . ';',
                                'session' => $user_info['is_guest'] ? 'ip' . $user_info['ip'] : session_id(),
                        )
                );


Selecting the default english language does not get rid of the error.

[Kakao]

As i saw this:

Code Select Expand

if (!empty($settings['display_who_viewing']))

I disabled "Show who is viewing the board index and posts" in the Theme Settings and obviously the error is gone since that chunk of code is not executed.

[SleePy]

Thanks, Bug #2657: instr does not exist in postgresql

I think there is two bugs here. I am not really sure on that though.

[Oldiesmann]

I'm not positive that the "unknown" type is a bug, but anyway, run this in phpPgAdmin and let me know if it fixes the issue:

Code Select Expand

CREATE OR REPLACE FUNCTION instr(text, text)
RETURNS integer AS '
  SELECT POSITION($2 IN $1)
' IMMUTABLE STRICT LANGUAGE 'sql';

(Source: MySQL Compatibility Functions package at http://pgfoundry.org/projects/mysqlcompat/)

[Kakao]

After creating the function i got this error:

Code Select Expand

ERROR: argument of OR must be type boolean, not type integer
Arquivo: /var/www/html/example.com/Sources/MessageIndex.php
Linha: 248

Then i changed from:

Code Select Expand

WHERE INSTR(lo.url, {string:in_url_string}) OR lo.session = {string:session}',

To:

Code Select Expand

WHERE INSTR(lo.url, {string:in_url_string}) > 0 OR lo.session = {string:session}',

And it worked. Then when viewing the post:

Code Select Expand

ERROR: argument of OR must be type boolean, not type integer
Arquivo: /var/www/html/example.com/Sources/Display.php
Linha: 497

The same fix as above worked.

[Oldiesmann]

Thanks for the feedback. With some minor tweaking, we can make the function return a boolean value

Code Select Expand

CREATE OR REPLACE FUNCTION instr(text, text)
RETURNS boolean AS '
  SELECT (POSITION($2 IN $1) != 0)
' IMMUTABLE STRICT LANGUAGE 'sql';

That's the version I've committed as the fix

! PostgreSQL doesn't have instr() (install_2-0_postgresql.sql, upgrade_2-0_postgresql.sql) [Bug 2657]

Revision 7598

[Kakao]

In my proposed fix i changed the where clause and not the function because i though it would be possible that the function was used somewhere else with the original meaning, that is, return the substring start position (int) in a string.

Thanks for the fast fix.

[SleePy]

It should be returning as the position. That is the way the MySQL manual says it should. So we need to follow that incase there is another use for instr such as returning the actual position in the string.