Issues

ZF-5699: Zend_Db_Table_Abstract::findDependentRowset throws an error when called on unsaved Zend_Db_Table_Row (with PDO_MYSQL)

Description

When I create a new row from a table object and I use findDependentRowset to get stuff linked with the row, I obtain this output :

PHP Fatal error:  Uncaught exception 'Zend_Db_Statement_Exception' with message 'SQLSTATE[HY093]: Invalid parameter number: no parameters were bound' in /home/sylvain/Documents/ZendFramework-1.7.4/library/Zend/Db/Statement/Pdo.php:238
Stack trace:
#0 /home/sylvain/Documents/ZendFramework-1.7.4/library/Zend/Db/Statement.php(283): Zend_Db_Statement_Pdo->_execute(Array)
#1 /home/sylvain/Documents/ZendFramework-1.7.4/library/Zend/Db/Adapter/Abstract.php(433): Zend_Db_Statement->execute(Array)
#2 /home/sylvain/Documents/ZendFramework-1.7.4/library/Zend/Db/Adapter/Pdo/Abstract.php(230): Zend_Db_Adapter_Abstract->query(Object(Zend_Db_Table_Select), Array)
#3 /home/sylvain/Documents/ZendFramework-1.7.4/library/Zend/Db/Table/Abstract.php(1330): Zend_Db_Adapter_Pdo_Abstract->query(Object(Zend_Db_Table_Select))
#4 /home/sylvain/Documents/ZendFramework-1.7.4/library/Zend/Db/Table/Abstract.php(1158): Zend_Db_Table_Abstract->_fetch(Object(Zend_Db_Table_Select))
#5 /home/sylvain/Documents/ZendFramework-1.7.4/library/Zend/Db/Table/Row/Abstrac in /home/sylvain/Documents/ZendFramework-1.7.4/library/Zend/Db/Statement/Pdo.php on line 238

Instead, I should have an empty Zend_Db_Table_Rowset.

You should note that with the pdo_sqlite adapter and 'dbname' => ':memory:', it works as expected.

Here is the code to reproduce the problem :


<?php

set_include_path('/home/sylvain/Documents/ZendFramework-1.7.4/library');

require_once('Zend/Loader.php');

Zend_Loader::registerAutoload();

class Application {

        public static function setUp() {
                $db = Zend_Db::factory('pdo_mysql', array(
                        'dbname' => 'test',
                        'host' => 'localhost',
                        'username' => 'root',
                        'password' => ''
                ));
                Zend_Db_Table_Abstract::setDefaultAdapter($db);

                $db->query('DROP TABLE IF EXISTS projects');
                $db->query('DROP TABLE IF EXISTS components');

                $db->query('CREATE TABLE projects (id INTEGER AUTO_INCREMENT, name VARCHAR(15), PRIMARY KEY (id));');
                $db->query('CREATE TABLE components (id INTEGER AUTO_INCREMENT, name VARCHAR(15), fk_parent INTEGER, PRIMARY KEY (id));');
        }

}

class Projects extends Zend_Db_Table_Abstract {

        protected $_name = 'projects';

        protected $_dependentTables = array('Components');

}

class Components extends Zend_Db_Table_Abstract {

        protected $_name = 'components';

        protected $_referenceMap = array(
                'Parent' => array(
                        'columns' => 'fk_parent',
                        'refTableClass' => 'Projects',
                        'refColumns' => 'id'
                )
        );

}

Application::setUp();

$tbl_projects = new Projects();
$new_project = $tbl_projects->createRow();

$components = $new_project->findDependentRowset('Components');
echo count($components) . "\n";

Comments

Add format to the description

I think the problem happen in the function Zend_Db_Select::_where. When you have a null value, the function doesn't ask the adapter to quote the value :


if ($value !== null) {
    $condition = $this->_adapter->quoteInto($condition, $value, $type);
}

I think it's a duplicate.

I think it's a duplicate.

I am not quite following the use case above.

You are attempting to call findDependentRowset() on a row that is not yet saved into the database. This poses a major problem. Your $new_project variable doesn't represent an actual row in the database, it represents a potential row in the database. Also, since you are using surrogate keys (primary key id autoincrement), this means that this row wont have a primary key until it is actually saved.

In short, you cannot query the database for relationships that do not exist yet... which is what you are attempting to do here. To make this work (and it will always return 0 when you take the count), you could do this:


$tbl_projects = new Projects();
$new_project = $tbl_projects->createRow();
$new_project->save(); // this will ensure the row is saved to the database, and a primary key is assigned to it to do relationship queryies later

$components = $new_project->findDependentRowset('Components');
echo count($components) . "\n";

This too i am inclined to close as "Not an Issue"

-ralph

In my opinion, it should work as is, or at least have the same behavior across the database adapters.

Actually, if I run my code with an sqlite adapter, it just works and return 0. Also, the same code work on ZF 1.0.x. I know this version is an old story but...

Maybe the method should just return an empty Rowset when the row was never saved ?

You make a good point, and for that i'll have to turn to the community to see what people think. It should either return an exception, or an empty result set inside findDependentRowset and/or the magic find query

Another point: I am guessing that (the effective dependent rowset query)


select * from mytable where something = ?

is working on SQLite (as you mentioned, but i have yet to test), but that query still is nonsensical, and technically most RDMBS will throw an error on such a query. In this case, the ? should probably be quoted for the query to pass, but like i said, i'd have to check SQLite on this one.

It should throw an exception across all adapters. Returning an empty rowset is confusing in this scenario. It's not hard to do a try/catch.

I think that when you use methods like findDependentRowset, findParentRow and find, you don't really want to know the what is the query under the hood.

{quote} I think that when you use methods like findDependentRowset, findParentRow and find, you don't really want to know the what is the query under the hood. {quote}

I agree with that point Sylvain, but trying to find dependents of a row that doesn't exist in the database is misuse and/or unintended use case IMO. In which case should throw an exception since it actually is Exceptional behavior.

I agree with Matthew, it should throw an exception.

The rationale is simple: Until a row object is saved, there IS NO row and therefore there CAN'T be any dependent rows (at least if referential integrity is maintained).

Indeed, there AREN'T any rows, so one could argue that an empty rowset is the correct response, but that would be the same as if reading from a non-existent file returned 0 bytes instead of an error. Sure, there aren't any bytes, but there can't be any either.

Throwing an exception is not something that will help me with my existing code base. But I must agree that this is the correct solution.

I'm curious to see what propel or doctrine do with this use case.

For the method findParentRow, it should return null because it's completly valid to have a row with a foreignkey NULL.

Imagine that in my use case of the projects and components, a components can have no project linked with fk_parent (the field is nullable) :


$tbl_components = new Components();
$comp = $tbl_components->createRow();
$comp->name = "Something";
$comp->save();

$project = $comp->findParentRow();

Actually, this throws an exception but it should return null, like older version of ZF.

Makes sense to me.

A parent may exist before a row does, but children can't. So findParentRow() should return null and findDependentRowset() should throw.

I agree with the community here. I will add the exceptional behavior (throwing) to both find dependentRowset as well as the magic findByRule method.

-ralph

Any news on this issue?

Bulk change of all issues last updated before 1st January 2010 as "Won't Fix".

Feel free to re-open and provide a patch if you want to fix this issue.