Bug #8268

selectCount() gives pear error, if as-names are used for where, group, order

Details

Submitted	2006-07-23 11:57 UTC
From	gramlich at eosc dot de
Assigned	wiesemann
Status	Closed
Package	DB_Table
PHP Version	4.3.8
Roadmaps	(Not assigned)

Comments

[2006-07-23 11:57 UTC] gramlich at eosc dot de

Description:
------------
In DB_Table::selectCount(), the $count_sql array includes order, join, group, having and where entries that can include named fields (SELECT id AS x FROM table). These as-names are removed for the select-entry.
At the end, $this->select(..) tries to build a query like
SELECT COUNT(*) FROM table WHERE x = 1
and there is no x-field in zzTT.

For the order, there is no problem, to unset($count_sql['order']), but doing something like this for where, join, group or having is not a good idea, as it changes the result.

Test script:
---------------
<?php
require_once 'DB.php';
require_once 'DB/Table.php';
class TT extends DB_Table {
var $col = array(

// unique row ID
'id' => array(
'type' => 'integer'
),

'tf' => array(
'type' => 'integer'
)
);

var $sql = array(
'query' => array(
'select' => 'id as x, tf as y',
'where' => 'x = 1',
//'group' => 'y',
//'order' => 'x'
)
);
}

$dsn = "mysql://user:pass@localhost/db";
$db = DB::connect($dsn);
$TT =& new TT($db, 'zzTT', 'drop');
$TT->insert(array('id' => 1, 'tf' => 2));
$TT->insert(array('id' => 2, 'tf' => 2));
$TT->insert(array('id' => 3, 'tf' => 1));
print_r($TT->selectCount('query'));
?>

Expected result:
----------------
1

Actual result:
--------------
db_error Object
(
[error_message_prefix] =>
[mode] => 1
[level] => 1024
[code] => -19
[message] => DB Error: no such field
[userinfo] => SELECT COUNT(*) FROM zzTT WHERE x = 1
[nativecode=1054 ** Unknown column 'x' in 'where clause']
........