title: Use of a mysql function in the 'ON' clause of a mysql JOIN query
body: <p>I need to join two tables where a substring of the base table's join column's value is used to join with the other table. </p>
  
  <p>I'm using zend framework 2 and I've written the query as follows.</p>
  
  <pre><code>$select = new Select($this-&gt;table);
  
  $select-&gt;columns(array('match_code'))
             -&gt;join('countries','countries.cid = SUBSTR(matches.teams,1,3)', 'name')
             -&gt;where(array('round'=&gt;1));
  </code></pre>
  
  <p>This gives an error.</p>
  
  <pre><code>SQLSTATE[42000]: Syntax error or access violation: 1630 FUNCTION mydatabase.SUBSTR does not exist. 
  Check the 'Function Name Parsing and Resolution' section in the Reference Manual
  </code></pre>
  
  <p>I've run the mysql query corresponds to this and it works perfectly. </p>
  
  <pre><code>SELECT `matches`.`match_code`, `countries`.`name` 
  FROM `matches` INNER JOIN `countries` 
  ON `countries`.`cid`=SUBSTR(`matches`.`teams`,1,3) 
  WHERE `round` = 1
  </code></pre>
  
  <p>I cannot use <code>Zend\Db\Sql\Expression</code> in here as the <code>join()</code> expects only a string. </p>
  
  <p>Any suggestions??</p>