[SOLVED] Zend Db Illegal mix of collations and sytnax error using COLLATE

Zend Db Illegal mix of collations and sytnax error using COLLATE

I was trying to join two columns in tables that used different collation using Zend Db and I got this error:

Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '=')

After googling and finding a few questions on Stackoverflow I added COLLATE utf8_unicode_ci onto my join like so:

        $select = $this->getSql()->select();
        $select->columns(['*', new Expression('count(*) as count')]);
        $select->join(
            ['tbl2' => 'table2'],
            'table1.name COLLATE utf8_unicode_ci = tbl2.name',
            ['person_id' => 'id'],
            $select::JOIN_LEFT
        );

But now I get a different error:

...
could not be executed (42000 - 1064 - You have an error in your SQL syntax;
check the manual that corresponds to your MySQL server version for 
the right syntax to use near '`COLLATE` `utf8_unicode_ci`

This is because Zend automatically backquotes the keyword COLLATE for me but I want to use a reserved word in the join condition.

Solution

Ideally you should change your collation so that both match, then you can join those tables using that condition without the COLLATE keyword. I'm not sure but I would guess using COLLATE in a join will probably impact performance.

If you cannot change the schema then alternatively, you could use Predicate\Expression instead on a string join which seems to bypass the auto quoting:

        $select = $this->getSql()->select();
        $select->columns(['*', new Expression('count(*) as count')]);
        $select->join(
            ['tbl2' => 'table2'],
            new \Zend\Db\Sql\Predicate\Expression('table1.name COLLATE utf8_unicode_ci = table2.name'),
            ['person_id' => 'id'],
            $select::JOIN_LEFT
        );

Or I guess you can bypass using Zend and handwrite the SQL statement yourself..