Home » Database » DB_DataObject » Bug #8209

[2006-07-13 14:29 UTC] leonard at locomo dot nl

Description:
------------
Upgrading DB_DataObject form 1.8.2 to 1.8.4 breaks the few joins that I use in my app.

Certain queries return no results. Downgrading to 1.8.2 restores functionality.

[2006-07-17 10:35 UTC] leonard at locomo dot nl

$ cat Country.php
<?php
/**
* Table Definition for country
*/
require_once 'DB/DataObject.php';

class DataObject_Country extends DB_DataObject
{
###START_AUTOCODE
/* the code below is auto generated do not remove the above tag */

var $__table = 'country'; // table name
var $ID; // int(11) not_null primary_key
var $name; // string(20) not_null unique_key

/* ZE2 compatibility trick*/
function __clone() { return $this;}

/* Static get */
function staticGet($k,$v=NULL) { return DB_DataObject::staticGet('DataObject_Country',$k,$v); }

/* the code above is auto generated do not remove the tag below */
###END_AUTOCODE
}

$ cat User.php
<?php
/**
* Table Definition for user
*/
require_once 'DB/DataObject.php';

class DataObject_User extends DB_DataObject
{
###START_AUTOCODE
/* the code below is auto generated do not remove the above tag */

var $__table = 'user'; // table name
var $ID; // int(11) not_null primary_key auto_increment
var $name; // string(10) not_null
var $countryID; // int(11) not_null

/* ZE2 compatibility trick*/
function __clone() { return $this;}

/* Static get */
function staticGet($k,$v=NULL) { return DB_DataObject::staticGet('DataObject_User',$k,$v); }

/* the code above is auto generated do not remove the tag below */
###END_AUTOCODE
}

SELECT * FROM `country`;
1 Nederland
2 Duitsland

SELECT * FROM `user`;
1 Leonard 1
2 Hans 2

$ cat leonardjo-test.links.ini
[user]
countryID = country:ID

$ cat testjoin.php
<?php
require_once('DataObjects/User.php');
require_once('DataObjects/Country.php');

$options =& PEAR::getStaticProperty('DB_DataObject', 'options');
$options = parse_ini_file('../private-config/leonardjo-test.db.ini');

$user = new DataObject_User;
$country = new DataObject_Country;

$user->find();

while ($user->fetch()) {
echo $user->name . ' ' . $user->countryID . "<br>\n";
}

$user->free();
echo "---<br>\n";

$user = new DataObject_User;
$user->selectAs();
$user->joinAdd($country);
$user->selectAs($country, 'country_%s');
$user->find();

while ($user->fetch()) {
echo $user->name . ' ' . $user->countryID . "<br>\n";
echo $user->name . ' ' . $user->country_name . "<br>\n";
}
?>

Output 1.8.2:
Leonard 1
Hans 2
---
Leonard 1
Leonard Nederland
Hans 2
Hans Duitsland

Output 1.8.4:
Leonard 1
Hans 2
---

[2006-07-17 10:57 UTC] leonard at locomo dot nl

Issue also exists with PHP-4.3.9, not just 4.3.2. Changing PHP-version.

Issue doesn't exist with DB_DataObject-1.8.3, so indeed this regression happened in 1.8.4. Changing subject.

[2006-07-18 08:01 UTC] leonard at locomo dot nl

It appears the new code stumbles over the fact that there is a hyphen in the DB name.

Pasting relevant section. Quoting these parameters fixes the query.

dataobjects_user: QUERY: SELECT user.ID as ID , user.name as name , user.countryID as countryID
, country.ID as country_ID , country.name as country_name

FROM user

INNER JOIN locomo-test.country ON locomo-test.country.ID=user.countryID

dataobjects_user: Query Error: [db_error: message="DB Error: syntax error" code=-2 mode=return level=notice prefix="" info="SELECT user.ID as ID , user.name as name , user.countryID as countryID
, country.ID as country_ID , country.name as country_name

FROM user

INNER JOIN locomo-test.country ON locomo-test.country.ID=user.countryID

[nativecode=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 '-test.country ON locomo-test.country.ID=user.countryID' at line 6]"]

[2006-07-18 08:12 UTC] leonard at locomo dot nl

I see 1.8.3 omits the DB name:

dataobject_user: QUERY: SELECT user.ID as ID , user.name as name , user.countryID as countryID
, country.ID as country_ID , country.name as country_name

FROM user

INNER JOIN country ON .country.ID=user.countryID

[2006-07-18 08:40 UTC] leonard at locomo dot nl

Sorry, but nope, I will not.

The issue is clear: In 1.8.4 the DB name was added to the join query. The query breaks because my DB names use dashes. I know I should fix this but there are quite a few of them.

If the CVS version still puts the DB name in the query and does not quote that name the issue still exists. Whether you
call this a user bug or not a solution should be clear (quoting names or making (/documenting) it illegal to .

[2006-07-18 08:42 UTC] leonard at locomo dot nl

Duh. Inadvertent click on submit.

I was saying:

Whether you call this a user bug or not a solution should be clear (quoting names or making it illegal to use databases with hyphens in their name, or at least document usage of hyphens in names will break queries).

[2006-07-18 09:02 UTC] leonard at locomo dot nl

Setting quote_identifiers to 1 fixes the issue for 1.8.4.

However, it breaks 1.8.3:

dataobject_user: QUERY: SELECT `user`.`ID` as `ID` , `user`.`name` as `name` , `user`.`countryID` as `countryID`
, `country`.`ID` as `country_ID` , `country`.`name` as `country_name`

FROM `user`
INNER JOIN `country` ON ``.`country`.`ID`=`user`.`countryID`

dataobject_user: Query Error: [db_error: message="DB Error: unknown error" code=-1 mode=return level=notice prefix="" info="SELECT `user`.`ID` as `ID` , `user`.`name` as `name` , `user`.`countryID` as `countryID`
, `country`.`ID` as `country_ID` , `country`.`name` as `country_name`

FROM `user`
INNER JOIN `country` ON ``.`country`.`ID`=`user`.`countryID`

[nativecode=1109 ** Unknown table 'country' in on clause]"]

So if the new code still adds a dot before the "ON table" name and the white space before the dot still gets quoted when quote_identifiers is set to 1 you still have an issue.

With the provided sample code you should be able to test this yourself. Make sure the DB name contains a hyphen to test all cases.

[2006-07-18 09:26 UTC] leonard at locomo dot nl

Yes, but in 1.8.3 that empty prefix gets quoted (as you can see in my previous example), which causes an error.

So if you now conditionally add an empty prefix when a single DB is being used you've reintroduced that bug from 1.8.3 that happens when quote_identifiers is set. Try it. (Doesn't matter if the DB has hyphens in this case.)

[2006-07-18 14:05 UTC] leonard at locomo dot nl

Well, you could either compare the code or run a test using my example...

I'm not going to investigate how to get CVS access, but if you can provide me a tarbal from CVS I can test this for you if you are unwilling to run the test yourself.

[2006-07-18 14:25 UTC] leonard at locomo dot nl

I think the relevant code in DataObject.php 1.8.4 is:

// prefix database (quoted if neccessary..)
$objTable = ($quoteIdentifiers
? $DB->quoteIdentifier($obj->_database)
: $obj->_database)
. '.' . $objTable;

There's no test here to not quote $obj->_database if it is an empty string. You should be able to tell whether that has changed in CVS
.

[2006-07-20 09:37 UTC] leonard at locomo dot nl

Ok. It appears the database name is not added at all, and no empty quotes either. Good. Thanks.

There probably still is an issue when using different dbs with hyphens in their names and not setting quote_identifiers to 1, but that is more of a user / documentation issue.

Maybe something like:
"Set quote_identifiers = 1 in db.ini if any of your table names uses hyphens. Or rather, don't use hyphens in identifiers."
could be added to the introduction? This keeps biting me, and probably one or two others.