Home » Database » DB_DataObject » Bug #9834

[2007-01-15 10:19 UTC] andries dot seutens at skynet dot be

Description:
------------
We have recently upgraded our servers from MySql 4.x to MySql 5.0.26 and we discovered some problems with DB_DataObjects. After some research we figured out that this was due to the fact that DB_DataObjects doesn't adhere the SQL 2003 standards, and apparently the MySQL team changed the MySQL5 syntax to be more complient with these standards.

I haven't read those standards fully, but by wrapping the joins in brackets I was able to resolve the problem and migrate the sites.

I did not see an option in this bug tracker to provide a patch, so I have copy/pasted a solution (which is pretty simple) in the test script section.

Test script:
---------------
LEFT JOIN foo ON foo.foo_id = bar.foo_id

should be:

LEFT JOIN foo ON (foo.foo_id = bar.foo_id)

Line 3186 until 3196

switch ($joinType) {
case 'INNER':
case 'LEFT':
case 'RIGHT': // others??? .. cross, left outer, right outer, natural..?
$this->_join .= "\n {$joinType} JOIN {$objTable} {$fullJoinAs}".
" ON ({$joinAs}.{$ofield}={$table}.{$tfield} {$appendJoin}) ";
break;
case '': // this is just a standard multitable select..
$this->_join .= "\n , {$objTable} {$fullJoinAs} {$appendJoin}";
$this->whereAdd("{$joinAs}.{$ofield}={$table}.{$tfield}");
}

[2007-01-15 10:28 UTC] andries dot seutens at skynet dot be

Sorry, the patch I have posted before wasn't complete, and not from the last version.

Here's the good one:

line 3221 until 3247

switch ($joinType) {
case 'INNER':
case 'LEFT':
case 'RIGHT': // others??? .. cross, left outer, right outer, natural..?

// Feature Request #4266 - Allow joins with multiple keys
$this->_join .= "\n {$joinType} JOIN {$objTable} {$fullJoinAs}";
if (is_array($ofield)) {
$key_count = count($ofield);
for($i = 0; $i < $key_count; $i++) {
if ($i == 0) {
$this->_join .= " ON ({$joinAs}.{$ofield[$i]}={$table}.{$tfield[$i]}) {$appendJoin} ";
}
else {
$this->_join .= " AND {$joinAs}.{$ofield[$i]}={$table}.{$tfield[$i]} {$appendJoin} ";
}
}
} else {
$this->_join .= " ON ({$joinAs}.{$ofield}={$table}.{$tfield}) {$appendJoin} ";
}

break;

case '': // this is just a standard multitable select..
$this->_join .= "\n , {$objTable} {$fullJoinAs} {$appendJoin}";
$this->whereAdd("{$joinAs}.{$ofield}={$table}.{$tfield}");
}