MySql concat string needs to be used instead of ||

[Ghada El-Zoghbi]

Mahara 15.10
OS: Ubuntu 14.04
DB: Mysql 5.5
Browser: any

I've noticed that in htdocs/search/internal/lib.php, the SQL used to concatenate strings is '||'.

For example, line 275:
$sql = $alias . '.' . $field . ' ' . db_ilike() . " '%' || ? || '%'";

Unfortunately, this doesn't always work with Mysql. In order for this to work we would need to set PIPES_AS_CONCAT

Please refer to the documentation:
http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_pipes_as_concat

Otherwise, strings need to be concatenated using: 'CONCAT'.

This function is also available in Postgres.

So, perhaps we should be using CONCAT instead of '||'.

So, the above line 275 would be:
$sql = $alias . '.' . $field . ' ' . db_ilike() . " concat('%', ? , '%')";

[Kristina Hoeppner (kris-hoeppner)]

Ghada wants to take another look before deciding on whether anything needs to be fixed.

[Robert Lyon (robertl-9)]

As there is a fine alternative, namely CONCAT(), for both MySQL and Postgres, which I personally find easier to understand what is going on, we should use that.

[Robert Lyon (robertl-9)]

Back in commit 1a4c340b6959f7d1bd5bf2b6147686a0d627af8a (circa 2007)
the line
  $db->_Execute("SET SQL_MODE='POSTGRESQL'");

was added to make mysql being used by Mahara act like postgres so that it can do the pipes (||)

So this shouldn't be an issue.

I did not when I copied sql commands from code to try directly in mysql I'd need to do SET SQL_MODE='POSTGRESQL'; first

[Aaron Wells (u-aaronw)]

As Robert mentioned, we do "SET SQL_MODE='POSTGRESQL'" when using MySQL. This is equivalent to the "PIPES_AS_CONCAT" directive Ghada mentioned, as well as several other options:

    Equivalent to PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS.

http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_postgresql

[Aaron Wells (u-aaronw)]

The main gotcha to this, is that if you're debugging a Mahara instance that's running in MySQL, and you copy out one of the SQL queries generated by Mahara and try to run it manually in a separate MySQL client.

If you do that, and the query uses ||, it'll error out unless you have first manually run

    SET SQL_MODE='POSTGRESQL';

The full list of things we do when setting up a connection to a MySQL DB, is in the "configure_dbconnection()" function in htdocs/lib/dml.php. It's actually:

    SET NAMES 'utf8';
    SET SQL_MODE='POSTGRESQL';
    SET CHARACTER SET utf8;
    SET SQL_BIG_SELECTS=1;

And if you're using $CFG->dbtimezone, we also do

    SET time_zone='{$CFG->dbtimezone}';

So if you notice discrepancies when running Mahara-generated SQL queries in a MySQL client, one thing to try is to run all of those in the client and see if it makes a difference.