tests/Zend/Db/Select/TestCommon.php

   1 <?php
   2 /**
   3  * Zend Framework
   4  *
   5  * LICENSE
   6  *
   7  * This source file is subject to the new BSD license that is bundled
   8  * with this package in the file LICENSE.txt.
   9  * It is also available through the world-wide-web at this URL:
  10  * http://framework.zend.com/license/new-bsd
  11  * If you did not receive a copy of the license and are unable to
  12  * obtain it through the world-wide-web, please send an email
  13  * to license@zend.com so we can send you a copy immediately.
  14  *
  15  * @category   Zend
  16  * @package    Zend_Db
  17  * @subpackage UnitTests
  18  * @copyright  Copyright (c) 2005-2010 Zend Technologies USA Inc. (http://www.zend.com)
  19  * @license    http://framework.zend.com/license/new-bsd     New BSD License
  20  * @version    $Id$
  21  */
  22
  23
  24 /**
  25  * @see Zend_Db_TestSetup
  26  */
  27 require_once 'Zend/Db/TestSetup.php';
  28
  29
  30 PHPUnit_Util_Filter::addFileToFilter(__FILE__);
  31
  32
  33 /**
  34  * @category   Zend
  35  * @package    Zend_Db
  36  * @subpackage UnitTests
  37  * @copyright  Copyright (c) 2005-2010 Zend Technologies USA Inc. (http://www.zend.com)
  38  * @license    http://framework.zend.com/license/new-bsd     New BSD License
  39  */
  40 abstract class Zend_Db_Select_TestCommon extends Zend_Db_TestSetup
  41 {
  42     /**
  43      * Test basic use of the Zend_Db_Select class.
  44      *
  45      * @return Zend_Db_Select
  46      */
  47     protected function _select()
  48     {
  49         $select = $this->_db->select();
  50         $select->from('zfproducts');
  51         return $select;
  52     }
  53
  54     public function testSelect()
  55     {
  56         $select = $this->_select();
  57         $this->assertType('Zend_Db_Select', $select,
  58             'Expecting object of type Zend_Db_Select, got '.get_class($select));
  59         $stmt = $this->_db->query($select);
  60         $row = $stmt->fetch();
  61         $stmt->closeCursor();
  62         $this->assertEquals(2, count($row)); // correct number of fields
  63         $this->assertEquals(1, $row['product_id']); // correct data
  64     }
  65
  66     public function testSelectToString()
  67     {
  68         $select = $this->_select();
  69         $this->assertEquals($select->__toString(), $select->assemble()); // correct data
  70     }
  71
  72     /**
  73      * Test basic use of the Zend_Db_Select class.
  74      */
  75     public function testSelectQuery()
  76     {
  77         $select = $this->_select();
  78         $this->assertType('Zend_Db_Select', $select,
  79             'Expecting object of type Zend_Db_Select, got '.get_class($select));
  80         $stmt = $select->query();
  81         $row = $stmt->fetch();
  82         $stmt->closeCursor();
  83         $this->assertEquals(2, count($row)); // correct number of fields
  84         $this->assertEquals(1, $row['product_id']); // correct data
  85     }
  86
  87     /**
  88      * ZF-2017: Test bind use of the Zend_Db_Select class.
  89      * @group ZF-2017
  90      */
  91     public function testSelectQueryWithBinds()
  92     {
  93         $product_id = $this->_db->quoteIdentifier('product_id');
  94
  95         $select = $this->_select()->where("$product_id = :product_id")
  96                                   ->bind(array(':product_id' => 1));
  97
  98         $this->assertType('Zend_Db_Select', $select,
  99             'Expecting object of type Zend_Db_Select, got '.get_class($select));
 100         $stmt = $select->query();
 101         $row = $stmt->fetch();
 102         $stmt->closeCursor();
 103         $this->assertEquals(2, count($row)); // correct number of fields
 104         $this->assertEquals(1, $row['product_id']); // correct data
 105     }
 106
 107     /**
 108      * Test Zend_Db_Select specifying columns
 109      */
 110     protected function _selectColumnsScalar()
 111     {
 112         $select = $this->_db->select()
 113             ->from('zfproducts', 'product_name'); // scalar
 114         return $select;
 115     }
 116
 117     public function testSelectColumnsScalar()
 118     {
 119         $select = $this->_selectColumnsScalar();
 120         $stmt = $this->_db->query($select);
 121         $result = $stmt->fetchAll();
 122         $this->assertEquals(3, count($result), 'Expected count of result set to be 2');
 123         $this->assertEquals(1, count($result[0]), 'Expected column count of result set to be 1');
 124         $this->assertThat($result[0], $this->arrayHasKey('product_name'));
 125     }
 126
 127     protected function _selectColumnsArray()
 128     {
 129         $select = $this->_db->select()
 130             ->from('zfproducts', array('product_id', 'product_name')); // array
 131         return $select;
 132     }
 133
 134     public function testSelectColumnsArray()
 135     {
 136         $select = $this->_selectColumnsArray();
 137         $stmt = $this->_db->query($select);
 138         $result = $stmt->fetchAll();
 139         $this->assertEquals(3, count($result), 'Expected count of result set to be 2');
 140         $this->assertEquals(2, count($result[0]), 'Expected column count of result set to be 2');
 141         $this->assertThat($result[0], $this->arrayHasKey('product_id'));
 142         $this->assertThat($result[0], $this->arrayHasKey('product_name'));
 143     }
 144
 145     /**
 146      * Test support for column aliases.
 147      * e.g. from('table', array('alias' => 'col1')).
 148      */
 149     protected function _selectColumnsAliases()
 150     {
 151         $select = $this->_db->select()
 152             ->from('zfproducts', array('alias' => 'product_name'));
 153         return $select;
 154     }
 155
 156     public function testSelectColumnsAliases()
 157     {
 158         $select = $this->_selectColumnsAliases();
 159         $stmt = $this->_db->query($select);
 160         $result = $stmt->fetchAll();
 161         $this->assertEquals(3, count($result), 'Expected count of result set to be 2');
 162         $this->assertThat($result[0], $this->arrayHasKey('alias'));
 163         $this->assertThat($result[0], $this->logicalNot($this->arrayHasKey('product_name')));
 164     }
 165
 166     /**
 167      * Test syntax to support qualified column names,
 168      * e.g. from('table', array('table.col1', 'table.col2')).
 169      */
 170     protected function _selectColumnsQualified()
 171     {
 172         $select = $this->_db->select()
 173             ->from('zfproducts', "zfproducts.product_name");
 174         return $select;
 175     }
 176
 177     public function testSelectColumnsQualified()
 178     {
 179         $select = $this->_selectColumnsQualified();
 180         $stmt = $this->_db->query($select);
 181         $result = $stmt->fetchAll();
 182         $this->assertThat($result[0], $this->arrayHasKey('product_name'));
 183     }
 184
 185     /**
 186      * Test support for columns defined by Zend_Db_Expr.
 187      */
 188     protected function _selectColumnsExpr()
 189     {
 190         $products = $this->_db->quoteIdentifier('zfproducts');
 191         $product_name = $this->_db->quoteIdentifier('product_name');
 192
 193         $select = $this->_db->select()
 194             ->from('zfproducts', new Zend_Db_Expr($products.'.'.$product_name));
 195         return $select;
 196     }
 197
 198     public function testSelectColumnsExpr()
 199     {
 200         $select = $this->_selectColumnsExpr();
 201         $stmt = $this->_db->query($select);
 202         $result = $stmt->fetchAll();
 203         $this->assertThat($result[0], $this->arrayHasKey('product_name'));
 204     }
 205
 206     /**
 207      * Test support for automatic conversion of SQL functions to
 208      * Zend_Db_Expr, e.g. from('table', array('COUNT(*)'))
 209      * should generate the same result as
 210      * from('table', array(new Zend_Db_Expr('COUNT(*)')))
 211      */
 212     protected function _selectColumnsAutoExpr()
 213     {
 214         $select = $this->_db->select()
 215             ->from('zfproducts', array('count' => 'COUNT(*)'));
 216         return $select;
 217     }
 218
 219     public function testSelectColumnsAutoExpr()
 220     {
 221         $select = $this->_selectColumnsAutoExpr();
 222         $stmt = $this->_db->query($select);
 223         $result = $stmt->fetchAll();
 224         $this->assertThat($result[0], $this->arrayHasKey('count'));
 225         $this->assertEquals(3, $result[0]['count']);
 226     }
 227
 228     /**
 229      * Test adding the DISTINCT query modifier to a Zend_Db_Select object.
 230      */
 231     protected function _selectDistinctModifier()
 232     {
 233         $select = $this->_db->select()
 234             ->distinct()
 235             ->from('zfproducts', new Zend_Db_Expr(327));
 236         return $select;
 237     }
 238
 239     public function testSelectDistinctModifier()
 240     {
 241         $select = $this->_selectDistinctModifier();
 242         $stmt = $this->_db->query($select);
 243         $result = $stmt->fetchAll();
 244         $this->assertEquals(1, count($result));
 245     }
 246
 247     /**
 248      * Test adding the FOR UPDATE query modifier to a Zend_Db_Select object.
 249      *
 250     public function testSelectForUpdateModifier()
 251     {
 252     }
 253      */
 254
 255     /**
 256      * Test support for schema-qualified table names in from()
 257      * e.g. from('schema.table').
 258      */
 259     protected function _selectFromQualified()
 260     {
 261         $schema = $this->_util->getSchema();
 262         $select = $this->_db->select()
 263             ->from("$schema.zfproducts");
 264         return $select;
 265     }
 266
 267     public function testSelectFromQualified()
 268     {
 269         $select = $this->_selectFromQualified();
 270         $stmt = $this->_db->query($select);
 271         $result = $stmt->fetchAll();
 272         $this->assertEquals(3, count($result));
 273     }
 274
 275     /**
 276      * Test support for nested select in from()
 277      */
 278     protected function _selectFromSelectObject()
 279     {
 280         $subquery = $this->_db->select()
 281             ->from('subqueryTable');
 282
 283         $select = $this->_db->select()
 284             ->from($subquery);
 285         return $select;
 286     }
 287
 288     public function testSelectFromSelectObject()
 289     {
 290         $select = $this->_selectFromSelectObject();
 291         $query = $select->assemble();
 292         $cmp = 'SELECT ' . $this->_db->quoteIdentifier('t') . '.* FROM (SELECT '
 293                          . $this->_db->quoteIdentifier('subqueryTable') . '.* FROM '
 294                          . $this->_db->quoteIdentifier('subqueryTable') . ') AS '
 295                          . $this->_db->quoteIdentifier('t');
 296         $this->assertEquals($query, $cmp);
 297     }
 298
 299     /**
 300      * Test support for nested select in from()
 301      */
 302     protected function _selectColumnsReset()
 303     {
 304         $select = $this->_db->select()
 305             ->from(array('p' => 'zfproducts'), array('product_id', 'product_name'));
 306         return $select;
 307     }
 308
 309     public function testSelectColumnsReset()
 310     {
 311         $select = $this->_selectColumnsReset()
 312             ->reset(Zend_Db_Select::COLUMNS)
 313             ->columns('product_name');
 314         $stmt = $this->_db->query($select);
 315         $result = $stmt->fetchAll();
 316         $this->assertContains('product_name', array_keys($result[0]));
 317         $this->assertNotContains('product_id', array_keys($result[0]));
 318
 319         $select = $this->_selectColumnsReset()
 320             ->reset(Zend_Db_Select::COLUMNS)
 321             ->columns('p.product_name');
 322         $stmt = $this->_db->query($select);
 323         $result = $stmt->fetchAll();
 324         $this->assertContains('product_name', array_keys($result[0]));
 325         $this->assertNotContains('product_id', array_keys($result[0]));
 326
 327         $select = $this->_selectColumnsReset()
 328             ->reset(Zend_Db_Select::COLUMNS)
 329             ->columns('product_name', 'p');
 330         $stmt = $this->_db->query($select);
 331         $result = $stmt->fetchAll();
 332         $this->assertContains('product_name', array_keys($result[0]));
 333         $this->assertNotContains('product_id', array_keys($result[0]));
 334     }
 335
 336     public function testSelectColumnsResetBeforeFrom()
 337     {
 338         $select = $this->_selectColumnsReset();
 339         try {
 340             $select->reset(Zend_Db_Select::COLUMNS)
 341                    ->reset(Zend_Db_Select::FROM)
 342                    ->columns('product_id');
 343             $this->fail('Expected exception of type "Zend_Db_Select_Exception"');
 344         } catch (Zend_Exception $e) {
 345             $this->assertType('Zend_Db_Select_Exception', $e,
 346                               'Expected exception of type "Zend_Db_Select_Exception", got ' . get_class($e));
 347             $this->assertEquals("No table has been specified for the FROM clause", $e->getMessage());
 348         }
 349     }
 350
 351     protected function _selectColumnWithColonQuotedParameter()
 352     {
 353         $product_id = $this->_db->quoteIdentifier('product_id');
 354
 355         $select = $this->_db->select()
 356             ->from('zfproducts')
 357             ->where($product_id . ' = ?', "as'as:x");
 358         return $select;
 359     }
 360
 361     public function testSelectColumnWithColonQuotedParameter()
 362     {
 363         $stmt = $select = $this->_selectColumnWithColonQuotedParameter()
 364             ->query();
 365         $result = $stmt->fetchAll();
 366         $this->assertEquals(0, count($result));
 367     }
 368
 369     /**
 370      * Test support for FOR UPDATE
 371      * e.g. from('schema.table').
 372      */
 373     public function testSelectFromForUpdate()
 374     {
 375         $select = $this->_db->select()
 376             ->from("zfproducts")
 377             ->forUpdate();
 378         $stmt = $this->_db->query($select);
 379         $result = $stmt->fetchAll();
 380         $this->assertEquals(3, count($result));
 381     }
 382
 383     /**
 384      * Test adding a JOIN to a Zend_Db_Select object.
 385      */
 386     protected function _selectJoin()
 387     {
 388         $products = $this->_db->quoteIdentifier('zfproducts');
 389         $product_id = $this->_db->quoteIdentifier('product_id');
 390         $bugs_products = $this->_db->quoteIdentifier('zfbugs_products');
 391
 392         $select = $this->_db->select()
 393             ->from('zfproducts')
 394             ->join('zfbugs_products', "$products.$product_id = $bugs_products.$product_id");
 395         return $select;
 396     }
 397
 398     public function testSelectJoin()
 399     {
 400         $select = $this->_selectJoin();
 401         $stmt = $this->_db->query($select);
 402         $result = $stmt->fetchAll();
 403         $this->assertEquals(6, count($result));
 404         $this->assertEquals(3, count($result[0]));
 405     }
 406
 407     /**
 408      * Test adding an INNER JOIN to a Zend_Db_Select object.
 409      * This should be exactly the same as the plain JOIN clause.
 410      */
 411     protected function _selectJoinWithCorrelationName()
 412     {
 413         $product_id = $this->_db->quoteIdentifier('product_id');
 414         $xyz1 = $this->_db->quoteIdentifier('xyz1');
 415         $xyz2 = $this->_db->quoteIdentifier('xyz2');
 416
 417         $select = $this->_db->select()
 418             ->from( array('xyz1' => 'zfproducts') )
 419             ->join( array('xyz2' => 'zfbugs_products'), "$xyz1.$product_id = $xyz2.$product_id")
 420             ->where("$xyz1.$product_id = 1");
 421         return $select;
 422     }
 423
 424     public function testSelectJoinWithCorrelationName()
 425     {
 426         $select = $this->_selectJoinWithCorrelationName();
 427         $stmt = $this->_db->query($select);
 428         $result = $stmt->fetchAll();
 429         $this->assertEquals(1, count($result));
 430         $this->assertEquals(3, count($result[0]));
 431     }
 432
 433     /**
 434      * Test adding an INNER JOIN to a Zend_Db_Select object.
 435      * This should be exactly the same as the plain JOIN clause.
 436      */
 437     protected function _selectJoinInner()
 438     {
 439         $products = $this->_db->quoteIdentifier('zfproducts');
 440         $product_id = $this->_db->quoteIdentifier('product_id');
 441         $bugs_products = $this->_db->quoteIdentifier('zfbugs_products');
 442
 443         $select = $this->_db->select()
 444             ->from('zfproducts')
 445             ->joinInner('zfbugs_products', "$products.$product_id = $bugs_products.$product_id");
 446         return $select;
 447     }
 448
 449     public function testSelectJoinInner()
 450     {
 451         $select = $this->_selectJoinInner();
 452         $stmt = $this->_db->query($select);
 453         $result = $stmt->fetchAll();
 454         $this->assertEquals(6, count($result));
 455         $this->assertEquals(3, count($result[0]));
 456     }
 457
 458     /**
 459      * Test adding a JOIN to a Zend_Db_Select object.
 460      */
 461     protected function _selectJoinWithNocolumns()
 462     {
 463         $products = $this->_db->quoteIdentifier('zfproducts');
 464         $bug_id = $this->_db->quoteIdentifier('bug_id');
 465         $product_id = $this->_db->quoteIdentifier('product_id');
 466         $bugs_products = $this->_db->quoteIdentifier('zfbugs_products');
 467         $bugs = $this->_db->quoteIdentifier('zfbugs');
 468
 469         $select = $this->_db->select()
 470             ->from('zfproducts')
 471             ->join('zfbugs', "$bugs.$bug_id = 1", array())
 472             ->join('zfbugs_products', "$products.$product_id = $bugs_products.$product_id AND $bugs_products.$bug_id = $bugs.$bug_id", null);
 473         return $select;
 474     }
 475
 476     public function testSelectJoinWithNocolumns()
 477     {
 478         $select = $this->_selectJoinWithNocolumns();
 479         $stmt = $this->_db->query($select);
 480         $result = $stmt->fetchAll();
 481         $this->assertEquals(3, count($result));
 482         $this->assertEquals(2, count($result[0]));
 483     }
 484
 485     /**
 486      * Test adding an outer join to a Zend_Db_Select object.
 487      */
 488     protected function _selectJoinLeft()
 489     {
 490         $bugs = $this->_db->quoteIdentifier('zfbugs');
 491         $bugs_products = $this->_db->quoteIdentifier('zfbugs_products');
 492         $bug_id = $this->_db->quoteIdentifier('bug_id');
 493
 494         $select = $this->_db->select()
 495             ->from('zfbugs')
 496             ->joinLeft('zfbugs_products', "$bugs.$bug_id = $bugs_products.$bug_id");
 497         return $select;
 498     }
 499
 500     public function testSelectJoinLeft()
 501     {
 502         $select = $this->_selectJoinLeft();
 503         $stmt = $this->_db->query($select);
 504         $result = $stmt->fetchAll();
 505         $this->assertEquals(7, count($result));
 506         $this->assertEquals(9, count($result[0]));
 507         $this->assertEquals(3, $result[3]['product_id']);
 508         $this->assertNull($result[6]['product_id']);
 509     }
 510
 511     /**
 512      * Returns a select object that uses table aliases and specifies a mixed ordering of columns,
 513      * for testing whether the user-specified ordering is preserved.
 514      *
 515      * @return Zend_Db_Select
 516      */
 517     protected function _selectJoinLeftTableAliasesColumnOrderPreserve()
 518     {
 519         $bugsBugId        = $this->_db->quoteIdentifier('b.bug_id');
 520         $bugsProductBugId = $this->_db->quoteIdentifier('bp.bug_id');
 521
 522         $select = $this->_db->select()
 523             ->from(array('b' => 'zfbugs'), array('b.bug_id', 'bp.product_id', 'b.bug_description'))
 524             ->joinLeft(array('bp' => 'zfbugs_products'), "$bugsBugId = $bugsProductBugId", array());
 525
 526         return $select;
 527     }
 528
 529     /**
 530      * Ensures that when table aliases are used with a mixed ordering of columns, the user-specified
 531      * column ordering is preserved.
 532      *
 533      * @return void
 534      */
 535     public function testJoinLeftTableAliasesColumnOrderPreserve()
 536     {
 537         $select = $this->_selectJoinLeftTableAliasesColumnOrderPreserve();
 538         $this->assertRegExp('/^.*b.*bug_id.*,.*bp.*product_id.*,.*b.*bug_description.*$/s', $select->assemble());
 539     }
 540
 541     /**
 542      * Test adding an outer join to a Zend_Db_Select object.
 543      */
 544     protected function _selectJoinRight()
 545     {
 546         $bugs = $this->_db->quoteIdentifier('zfbugs');
 547         $bugs_products = $this->_db->quoteIdentifier('zfbugs_products');
 548         $bug_id = $this->_db->quoteIdentifier('bug_id');
 549
 550         $select = $this->_db->select()
 551             ->from('zfbugs_products')
 552             ->joinRight('zfbugs', "$bugs_products.$bug_id = $bugs.$bug_id");
 553         return $select;
 554     }
 555
 556     public function testSelectJoinRight()
 557     {
 558         $select = $this->_selectJoinRight();
 559         $stmt = $this->_db->query($select);
 560         $result = $stmt->fetchAll();
 561         $this->assertEquals(7, count($result));
 562         $this->assertEquals(9, count($result[0]));
 563         $this->assertEquals(3, $result[3]['product_id']);
 564         $this->assertNull($result[6]['product_id']);
 565     }
 566
 567     /**
 568      * Test adding a cross join to a Zend_Db_Select object.
 569      */
 570     protected function _selectJoinCross()
 571     {
 572         $select = $this->_db->select()
 573             ->from('zfproducts')
 574             ->joinCross('zfbugs_products');
 575         return $select;
 576     }
 577
 578     public function testSelectJoinCross()
 579     {
 580         $select = $this->_selectJoinCross();
 581         $stmt = $this->_db->query($select);
 582         $result = $stmt->fetchAll();
 583         $this->assertEquals(18, count($result));
 584         $this->assertEquals(3, count($result[0]));
 585     }
 586
 587     /**
 588      * Test support for schema-qualified table names in join(),
 589      * e.g. join('schema.table', 'condition')
 590      */
 591     protected function _selectJoinQualified()
 592     {
 593         $products = $this->_db->quoteIdentifier('zfproducts');
 594         $bugs_products = $this->_db->quoteIdentifier('zfbugs_products');
 595         $product_id = $this->_db->quoteIdentifier('product_id');
 596
 597         $schema = $this->_util->getSchema();
 598         $select = $this->_db->select()
 599             ->from('zfproducts')
 600             ->join("$schema.zfbugs_products", "$products.$product_id = $bugs_products.$product_id");
 601         return $select;
 602     }
 603
 604     public function testSelectJoinQualified()
 605     {
 606         $select = $this->_selectJoinQualified();
 607         $stmt = $this->_db->query($select);
 608         $result = $stmt->fetchAll();
 609         $this->assertEquals(6, count($result));
 610         $this->assertEquals(3, count($result[0]));
 611     }
 612
 613     protected function _selectJoinUsing()
 614     {
 615         $products = $this->_db->quoteIdentifier('zfproducts');
 616         $bugs_products = $this->_db->quoteIdentifier('zfbugs_products');
 617         $product_id = $this->_db->quoteIdentifier('product_id');
 618
 619         $select = $this->_db->select()
 620             ->from('zfproducts')
 621             ->joinUsing("zfbugs_products", "$product_id")
 622             ->where("$bugs_products.$product_id < ?", 3);
 623         return $select;
 624     }
 625
 626     public function testSelectMagicMethod()
 627     {
 628         $select = $this->_selectJoinUsing();
 629         try {
 630             $select->foo();
 631             $this->fail('Expected exception of type "Zend_Db_Select_Exception"');
 632         } catch (Zend_Exception $e) {
 633             $this->assertType('Zend_Db_Select_Exception', $e,
 634                               'Expected exception of type "Zend_Db_Select_Exception", got ' . get_class($e));
 635             $this->assertEquals("Unrecognized method 'foo()'", $e->getMessage());
 636         }
 637     }
 638
 639     public function testSelectJoinUsing()
 640     {
 641         $select = $this->_selectJoinUsing();
 642         $sql = preg_replace('/\\s+/', ' ', $select->assemble());
 643         $stmt = $this->_db->query($select);
 644         $result = $stmt->fetchAll();
 645         $this->assertEquals(3, count($result));
 646         $this->assertEquals(1, $result[0]['product_id']);
 647     }
 648
 649     protected function _selectJoinInnerUsing()
 650     {
 651         $products = $this->_db->quoteIdentifier('zfproducts');
 652         $bugs_products = $this->_db->quoteIdentifier('zfbugs_products');
 653         $product_id = $this->_db->quoteIdentifier('product_id');
 654
 655         $select = $this->_db->select()
 656             ->from('zfproducts')
 657             ->joinInnerUsing("zfbugs_products", "$product_id")
 658             ->where("$bugs_products.$product_id < ?", 3);
 659         return $select;
 660     }
 661
 662     public function testSelectJoinInnerUsing()
 663     {
 664         $select = $this->_selectJoinInnerUsing();
 665         $sql = preg_replace('/\\s+/', ' ', $select->assemble());
 666         $stmt = $this->_db->query($select);
 667         $result = $stmt->fetchAll();
 668         $this->assertEquals(3, count($result));
 669         $this->assertEquals(1, $result[0]['product_id']);
 670     }
 671
 672     public function testSelectJoinInnerUsingException()
 673     {
 674         $select = $this->_selectJoinInnerUsing();
 675         try {
 676             $select->joinFooUsing();
 677             $this->fail('Expected exception of type "Zend_Db_Select_Exception"');
 678         } catch (Zend_Exception $e) {
 679             $this->assertType('Zend_Db_Select_Exception', $e,
 680                               'Expected exception of type "Zend_Db_Select_Exception", got ' . get_class($e));
 681             $this->assertEquals("Unrecognized method 'joinFooUsing()'", $e->getMessage());
 682         }
 683     }
 684
 685     protected function _selectJoinCrossUsing()
 686     {
 687         $products = $this->_db->quoteIdentifier('zfproducts');
 688         $bugs_products = $this->_db->quoteIdentifier('zfbugs_products');
 689         $product_id = $this->_db->quoteIdentifier('product_id');
 690
 691         $select = $this->_db->select()
 692             ->from('zfproducts')
 693             ->where("$bugs_products.$product_id < ?", 3);
 694         return $select;
 695     }
 696
 697     public function testSelectJoinCrossUsing()
 698     {
 699         $product_id = $this->_db->quoteIdentifier('product_id');
 700         $select = $this->_selectJoinCrossUsing();
 701         try {
 702             $select->joinCrossUsing("zfbugs_products", "$product_id");
 703             $this->fail('Expected exception of type "Zend_Db_Select_Exception"');
 704         } catch (Zend_Exception $e) {
 705             $this->assertType('Zend_Db_Select_Exception', $e,
 706                               'Expected exception of type "Zend_Db_Select_Exception", got ' . get_class($e));
 707             $this->assertEquals("Cannot perform a joinUsing with method 'joinCrossUsing()'", $e->getMessage());
 708         }
 709     }
 710
 711     /**
 712      * Test adding a WHERE clause to a Zend_Db_Select object.
 713      */
 714     protected function _selectWhere()
 715     {
 716         $product_id = $this->_db->quoteIdentifier('product_id');
 717
 718         $select = $this->_db->select()
 719             ->from('zfproducts')
 720             ->where("$product_id = 2");
 721         return $select;
 722     }
 723
 724     public function testSelectWhere()
 725     {
 726         $select = $this->_selectWhere();
 727         $stmt = $this->_db->query($select);
 728         $result = $stmt->fetchAll();
 729         $this->assertEquals(1, count($result));
 730         $this->assertEquals(2, $result[0]['product_id']);
 731     }
 732
 733     /**
 734      * Test support for nested select in from()
 735      */
 736     protected function _selectWhereSelectObject()
 737     {
 738         $subquery = $this->_db->select()
 739             ->from('subqueryTable');
 740
 741         $select = $this->_db->select()
 742             ->from('table')
 743             ->where('foo IN ?', $subquery);
 744         return $select;
 745     }
 746
 747     public function testSelectWhereSelectObject()
 748     {
 749         $select = $this->_selectWhereSelectObject();
 750         $query = $select->assemble();
 751         $cmp = 'SELECT ' . $this->_db->quoteIdentifier('table') . '.* FROM '
 752                          . $this->_db->quoteIdentifier('table') . ' WHERE (foo IN (SELECT '
 753                          . $this->_db->quoteIdentifier('subqueryTable') . '.* FROM '
 754                          . $this->_db->quoteIdentifier('subqueryTable') . '))';
 755         $this->assertEquals($query, $cmp);
 756     }
 757
 758     protected function _selectWhereArray()
 759     {
 760         $product_id = $this->_db->quoteIdentifier('product_id');
 761
 762         $select = $this->_db->select()
 763             ->from('zfproducts')
 764             ->where("$product_id IN (?)", array(1, 2, 3));
 765         return $select;
 766     }
 767
 768     public function testSelectWhereArray()
 769     {
 770         $select = $this->_selectWhereArray();
 771         $stmt = $this->_db->query($select);
 772         $result = $stmt->fetchAll();
 773         $this->assertEquals(3, count($result));
 774     }
 775
 776     /**
 777      * test adding more WHERE conditions,
 778      * which should be combined with AND by default.
 779      */
 780     protected function _selectWhereAnd()
 781     {
 782         $product_id = $this->_db->quoteIdentifier('product_id');
 783
 784         $select = $this->_db->select()
 785             ->from('zfproducts')
 786             ->where("$product_id = 2")
 787             ->where("$product_id = 1");
 788         return $select;
 789     }
 790
 791     public function testSelectWhereAnd()
 792     {
 793         $select = $this->_selectWhereAnd();
 794         $stmt = $this->_db->query($select);
 795         $result = $stmt->fetchAll();
 796         $this->assertEquals(0, count($result));
 797     }
 798
 799     /**
 800      * Test support for where() with a parameter,
 801      * e.g. where('id = ?', 1).
 802      */
 803     protected function _selectWhereWithParameter()
 804     {
 805         $product_id = $this->_db->quoteIdentifier('product_id');
 806
 807         $select = $this->_db->select()
 808             ->from('zfproducts')
 809             ->where("$product_id = ?", 2);
 810         return $select;
 811     }
 812
 813     public function testSelectWhereWithParameter()
 814     {
 815         $select = $this->_selectWhereWithParameter();
 816         $stmt = $this->_db->query($select);
 817         $result = $stmt->fetchAll();
 818         $this->assertEquals(1, count($result));
 819         $this->assertEquals(2, $result[0]['product_id']);
 820     }
 821
 822     /**
 823      * Test support for where() with a specified type,
 824      * e.g. where('id = ?', 1, 'int').
 825      */
 826     protected function _selectWhereWithType()
 827     {
 828         $product_id = $this->_db->quoteIdentifier('product_id');
 829
 830         $select = $this->_db->select()
 831             ->from('zfproducts')
 832             ->where("$product_id = ?", 2, 'int');
 833         return $select;
 834     }
 835
 836     public function testSelectWhereWithType()
 837     {
 838         $select = $this->_selectWhereWithType();
 839         $stmt = $this->_db->query($select);
 840         $result = $stmt->fetchAll();
 841         $this->assertEquals(1, count($result));
 842         $this->assertEquals(2, $result[0]['product_id']);
 843     }
 844
 845     /**
 846      * Test support for where() with a specified type,
 847      * e.g. where('id = ?', 1, 'int').
 848      */
 849     protected function _selectWhereWithTypeFloat()
 850     {
 851         $price_total = $this->_db->quoteIdentifier('price_total');
 852
 853         $select = $this->_db->select()
 854             ->from('zfprice')
 855             ->where("$price_total = ?", 200.45, Zend_Db::FLOAT_TYPE);
 856         return $select;
 857     }
 858
 859     public function testSelectWhereWithTypeFloat()
 860     {
 861         $locale = setlocale(LC_ALL, null);
 862
 863         $select = $this->_selectWhereWithTypeFloat();
 864         $stmt = $this->_db->query($select);
 865         $result = $stmt->fetchAll();
 866         $this->assertEquals(1, count($result));
 867         $this->assertEquals(200.45, $result[0]['price_total']);
 868
 869         try {
 870             setlocale(LC_ALL, 'fr_BE.UTF-8');
 871             $select = $this->_selectWhereWithTypeFloat();
 872             $stmt = $this->_db->query($select);
 873             $result = $stmt->fetchAll();
 874             $this->assertEquals(1, count($result));
 875             $this->assertEquals(200.45, $result[0]['price_total']);
 876         } catch (Zend_Exception $e) {
 877             setlocale(LC_ALL, $locale);
 878             throw $e;
 879         }
 880
 881         setlocale(LC_ALL, $locale);
 882     }
 883
 884     /**
 885      * Test adding an OR WHERE clause to a Zend_Db_Select object.
 886      */
 887     protected function _selectWhereOr()
 888     {
 889         $product_id = $this->_db->quoteIdentifier('product_id');
 890
 891         $select = $this->_db->select()
 892             ->from('zfproducts')
 893             ->orWhere("$product_id = 1")
 894             ->orWhere("$product_id = 2");
 895         return $select;
 896     }
 897
 898     public function testSelectWhereOr()
 899     {
 900         $select = $this->_selectWhereOr();
 901         $stmt = $this->_db->query($select);
 902         $result = $stmt->fetchAll();
 903         $this->assertEquals(2, count($result));
 904         $this->assertEquals(1, $result[0]['product_id']);
 905         $this->assertEquals(2, $result[1]['product_id']);
 906     }
 907
 908     /**
 909      * Test support for where() with a parameter,
 910      * e.g. orWhere('id = ?', 2).
 911      */
 912     protected function _selectWhereOrWithParameter()
 913     {
 914         $product_id = $this->_db->quoteIdentifier('product_id');
 915
 916         $select = $this->_db->select()
 917             ->from('zfproducts')
 918             ->orWhere("$product_id = ?", 1)
 919             ->orWhere("$product_id = ?", 2);
 920         return $select;
 921     }
 922
 923     public function testSelectWhereOrWithParameter()
 924     {
 925         $select = $this->_selectWhereOrWithParameter();
 926         $stmt = $this->_db->query($select);
 927         $result = $stmt->fetchAll();
 928         $this->assertEquals(2, count($result));
 929         $this->assertEquals(1, $result[0]['product_id']);
 930         $this->assertEquals(2, $result[1]['product_id']);
 931     }
 932
 933     /**
 934      * Test adding a GROUP BY clause to a Zend_Db_Select object.
 935      */
 936     protected function _selectGroupBy()
 937     {
 938         $thecount = $this->_db->quoteIdentifier('thecount');
 939
 940         $select = $this->_db->select()
 941             ->from('zfbugs_products', array('bug_id', new Zend_Db_Expr("COUNT(*) AS $thecount")))
 942             ->group('bug_id')
 943             ->order('bug_id');
 944         return $select;
 945     }
 946
 947     public function testSelectGroupBy()
 948     {
 949         $select = $this->_selectGroupBy();
 950         $stmt = $this->_db->query($select);
 951         $result = $stmt->fetchAll();
 952         $this->assertEquals(3, count($result),
 953             'Expected count of first result set to be 2');
 954         $this->assertEquals(1, $result[0]['bug_id']);
 955         $this->assertEquals(3, $result[0]['thecount'],
 956             'Expected count(*) of first result set to be 2');
 957         $this->assertEquals(2, $result[1]['bug_id']);
 958         $this->assertEquals(1, $result[1]['thecount']);
 959     }
 960
 961     /**
 962      * Test support for qualified table in group(),
 963      * e.g. group('schema.table').
 964      */
 965     protected function _selectGroupByQualified()
 966     {
 967         $thecount = $this->_db->quoteIdentifier('thecount');
 968
 969         $select = $this->_db->select()
 970             ->from('zfbugs_products', array('bug_id', new Zend_Db_Expr("COUNT(*) AS $thecount")))
 971             ->group("zfbugs_products.bug_id")
 972             ->order('bug_id');
 973         return $select;
 974     }
 975
 976     public function testSelectGroupByQualified()
 977     {
 978         $select = $this->_selectGroupByQualified();
 979         $stmt = $this->_db->query($select);
 980         $result = $stmt->fetchAll();
 981         $this->assertEquals(3, count($result),
 982             'Expected count of first result set to be 2');
 983         $this->assertEquals(1, $result[0]['bug_id']);
 984         $this->assertEquals(3, $result[0]['thecount'],
 985             'Expected count(*) of first result set to be 2');
 986         $this->assertEquals(2, $result[1]['bug_id']);
 987         $this->assertEquals(1, $result[1]['thecount']);
 988     }
 989
 990     /**
 991      * Test support for Zend_Db_Expr in group(),
 992      * e.g. group(new Zend_Db_Expr('id+1'))
 993      */
 994     protected function _selectGroupByExpr()
 995     {
 996         $thecount = $this->_db->quoteIdentifier('thecount');
 997         $bug_id = $this->_db->quoteIdentifier('bug_id');
 998
 999         $select = $this->_db->select()
1000             ->from('zfbugs_products', array('bug_id'=>new Zend_Db_Expr("$bug_id+1"), new Zend_Db_Expr("COUNT(*) AS $thecount")))
1001             ->group(new Zend_Db_Expr("$bug_id+1"))
1002             ->order(new Zend_Db_Expr("$bug_id+1"));
1003         return $select;
1004     }
1005
1006     public function testSelectGroupByExpr()
1007     {
1008         $select = $this->_selectGroupByExpr();
1009         $stmt = $this->_db->query($select);
1010         $result = $stmt->fetchAll();
1011         $this->assertEquals(3, count($result),
1012             'Expected count of first result set to be 2');
1013         $this->assertEquals(2, $result[0]['bug_id'],
1014             'Expected first bug_id to be 2');
1015         $this->assertEquals(3, $result[0]['thecount'],
1016             'Expected count(*) of first group to be 2');
1017         $this->assertEquals(3, $result[1]['bug_id'],
1018             'Expected second bug_id to be 3');
1019         $this->assertEquals(1, $result[1]['thecount'],
1020             'Expected count(*) of second group to be 1');
1021     }
1022
1023     /**
1024      * Test support for automatic conversion of a SQL
1025      * function to a Zend_Db_Expr in group(),
1026      * e.g.  group('LOWER(title)') should give the same
1027      * result as group(new Zend_Db_Expr('LOWER(title)')).
1028      */
1029
1030     protected function _selectGroupByAutoExpr()
1031     {
1032         $thecount = $this->_db->quoteIdentifier('thecount');
1033         $bugs_products = $this->_db->quoteIdentifier('zfbugs_products');
1034         $bug_id = $this->_db->quoteIdentifier('bug_id');
1035
1036         $select = $this->_db->select()
1037             ->from('zfbugs_products', array('bug_id'=>"ABS($bugs_products.$bug_id)", new Zend_Db_Expr("COUNT(*) AS $thecount")))
1038             ->group("ABS($bugs_products.$bug_id)")
1039             ->order("ABS($bugs_products.$bug_id)");
1040         return $select;
1041     }
1042
1043     public function testSelectGroupByAutoExpr()
1044     {
1045         $select = $this->_selectGroupByAutoExpr();
1046         $stmt = $this->_db->query($select);
1047         $result = $stmt->fetchAll();
1048         $this->assertEquals(3, count($result), 'Expected count of first result set to be 2');
1049         $this->assertEquals(1, $result[0]['bug_id']);
1050         $this->assertEquals(3, $result[0]['thecount'], 'Expected count(*) of first result set to be 2');
1051         $this->assertEquals(2, $result[1]['bug_id']);
1052         $this->assertEquals(1, $result[1]['thecount']);
1053     }
1054
1055     /**
1056      * Test adding a HAVING clause to a Zend_Db_Select object.
1057      */
1058     protected function _selectHaving()
1059     {
1060         $select = $this->_db->select()
1061             ->from('zfbugs_products', array('bug_id', 'COUNT(*) AS thecount'))
1062             ->group('bug_id')
1063             ->having('COUNT(*) > 1')
1064             ->order('bug_id');
1065         return $select;
1066     }
1067
1068     public function testSelectHaving()
1069     {
1070         $select = $this->_selectHaving();
1071         $stmt = $this->_db->query($select);
1072         $result = $stmt->fetchAll();
1073         $this->assertEquals(2, count($result));
1074         $this->assertEquals(1, $result[0]['bug_id']);
1075         $this->assertEquals(3, $result[0]['thecount']);
1076     }
1077
1078     protected function _selectHavingAnd()
1079     {
1080         $select = $this->_db->select()
1081             ->from('zfbugs_products', array('bug_id', 'COUNT(*) AS thecount'))
1082             ->group('bug_id')
1083             ->having('COUNT(*) > 1')
1084             ->having('COUNT(*) = 1')
1085             ->order('bug_id');
1086         return $select;
1087     }
1088
1089     public function testSelectHavingAnd()
1090     {
1091         $select = $this->_selectHavingAnd();
1092         $stmt = $this->_db->query($select);
1093         $result = $stmt->fetchAll();
1094         $this->assertEquals(0, count($result));
1095     }
1096
1097     /**
1098      * Test support for parameter in having(),
1099      * e.g. having('count(*) > ?', 1).
1100      */
1101
1102     protected function _selectHavingWithParameter()
1103     {
1104         $select = $this->_db->select()
1105             ->from('zfbugs_products', array('bug_id', 'COUNT(*) AS thecount'))
1106             ->group('bug_id')
1107             ->having('COUNT(*) > ?', 1)
1108             ->order('bug_id');
1109         return $select;
1110     }
1111
1112     public function testSelectHavingWithParameter()
1113     {
1114         $select = $this->_selectHavingWithParameter();
1115         $stmt = $this->_db->query($select);
1116         $result = $stmt->fetchAll();
1117         $this->assertEquals(2, count($result));
1118         $this->assertEquals(1, $result[0]['bug_id']);
1119         $this->assertEquals(3, $result[0]['thecount']);
1120     }
1121
1122     /**
1123      * Test adding a HAVING clause to a Zend_Db_Select object.
1124      */
1125
1126     protected function _selectHavingOr()
1127     {
1128         $select = $this->_db->select()
1129             ->from('zfbugs_products', array('bug_id', 'COUNT(*) AS thecount'))
1130             ->group('bug_id')
1131             ->orHaving('COUNT(*) > 1')
1132             ->orHaving('COUNT(*) = 1')
1133             ->order('bug_id');
1134         return $select;
1135     }
1136
1137     public function testSelectHavingOr()
1138     {
1139         $select = $this->_selectHavingOr();
1140         $stmt = $this->_db->query($select);
1141         $result = $stmt->fetchAll();
1142         $this->assertEquals(3, count($result));
1143         $this->assertEquals(1, $result[0]['bug_id']);
1144         $this->assertEquals(3, $result[0]['thecount']);
1145         $this->assertEquals(2, $result[1]['bug_id']);
1146         $this->assertEquals(1, $result[1]['thecount']);
1147     }
1148
1149     /**
1150      * Test support for parameter in orHaving(),
1151      * e.g. orHaving('count(*) > ?', 1).
1152      */
1153     protected function _selectHavingOrWithParameter()
1154     {
1155         $select = $this->_db->select()
1156             ->from('zfbugs_products', array('bug_id', 'COUNT(*) AS thecount'))
1157             ->group('bug_id')
1158             ->orHaving('COUNT(*) > ?', 1)
1159             ->orHaving('COUNT(*) = ?', 1)
1160             ->order('bug_id');
1161         return $select;
1162     }
1163
1164     public function testSelectHavingOrWithParameter()
1165     {
1166         $select = $this->_selectHavingOrWithParameter();
1167         $stmt = $this->_db->query($select);
1168         $result = $stmt->fetchAll();
1169         $this->assertEquals(3, count($result));
1170         $this->assertEquals(1, $result[0]['bug_id']);
1171         $this->assertEquals(3, $result[0]['thecount']);
1172         $this->assertEquals(2, $result[1]['bug_id']);
1173         $this->assertEquals(1, $result[1]['thecount']);
1174     }
1175
1176     /**
1177      * Test adding an ORDER BY clause to a Zend_Db_Select object.
1178      */
1179     protected function _selectOrderBy()
1180     {
1181         $select = $this->_db->select()
1182             ->from('zfproducts')
1183             ->order('product_id');
1184         return $select;
1185     }
1186
1187     public function testSelectOrderBy()
1188     {
1189         $select = $this->_selectOrderBy();
1190         $stmt = $this->_db->query($select);
1191         $result = $stmt->fetchAll();
1192         $this->assertEquals(1, $result[0]['product_id']);
1193     }
1194
1195     protected function _selectOrderByArray()
1196     {
1197         $select = $this->_db->select()
1198             ->from('zfproducts')
1199             ->order(array('product_name', 'product_id'));
1200         return $select;
1201     }
1202
1203     public function testSelectOrderByArray()
1204     {
1205         $select = $this->_selectOrderByArray();
1206         $stmt = $this->_db->query($select);
1207         $result = $stmt->fetchAll();
1208         $this->assertEquals(3, count($result),
1209             'Expected count of result set to be 3');
1210         $this->assertEquals('Linux', $result[0]['product_name']);
1211         $this->assertEquals(2, $result[0]['product_id']);
1212     }
1213
1214     protected function _selectOrderByAsc()
1215     {
1216         $select = $this->_db->select()
1217             ->from('zfproducts')
1218             ->order("product_id ASC");
1219         return $select;
1220     }
1221
1222     public function testSelectOrderByAsc()
1223     {
1224         $select = $this->_selectOrderByAsc();
1225         $stmt = $this->_db->query($select);
1226         $result = $stmt->fetchAll();
1227         $this->assertEquals(3, count($result),
1228             'Expected count of result set to be 2');
1229         $this->assertEquals(1, $result[0]['product_id']);
1230     }
1231
1232     protected function _selectOrderByPosition()
1233     {
1234         $select = $this->_db->select()
1235             ->from('zfproducts')
1236             ->order('2');
1237         return $select;
1238     }
1239
1240     public function testSelectOrderByPosition()
1241     {
1242         $select = $this->_selectOrderByPosition();
1243
1244         $stmt = $this->_db->query($select);
1245         $result = $stmt->fetchAll();
1246
1247         $this->assertEquals(2, $result[0]['product_id']);
1248         $this->assertEquals(3, $result[1]['product_id']);
1249         $this->assertEquals(1, $result[2]['product_id']);
1250     }
1251
1252     protected function _selectOrderByPositionAsc()
1253     {
1254         $select = $this->_db->select()
1255             ->from('zfproducts')
1256             ->order('2 ASC');
1257         return $select;
1258     }
1259
1260     public function testSelectOrderByPositionAsc()
1261     {
1262         $select = $this->_selectOrderByPositionAsc();
1263
1264         $stmt = $this->_db->query($select);
1265         $result = $stmt->fetchAll();
1266
1267         $this->assertEquals(2, $result[0]['product_id']);
1268         $this->assertEquals(3, $result[1]['product_id']);
1269         $this->assertEquals(1, $result[2]['product_id']);
1270     }
1271
1272     protected function _selectOrderByPositionDesc()
1273     {
1274         $select = $this->_db->select()
1275             ->from('zfproducts')
1276             ->order('2 DESC');
1277         return $select;
1278     }
1279
1280     public function testSelectOrderByPositionDesc()
1281     {
1282         $select = $this->_selectOrderByPositionDesc();
1283
1284         $stmt = $this->_db->query($select);
1285         $result = $stmt->fetchAll();
1286
1287         $this->assertEquals(1, $result[0]['product_id']);
1288         $this->assertEquals(3, $result[1]['product_id']);
1289         $this->assertEquals(2, $result[2]['product_id']);
1290     }
1291
1292     protected function _selectOrderByMultiplePositions()
1293     {
1294         $select = $this->_db->select()
1295             ->from('zfproducts')
1296             ->order(array('2 DESC', '1 DESC'));
1297         return $select;
1298     }
1299
1300     public function testSelectOrderByMultiplePositions()
1301     {
1302         $select = $this->_selectOrderByMultiplePositions();
1303
1304         $stmt = $this->_db->query($select);
1305         $result = $stmt->fetchAll();
1306
1307         $this->assertEquals(1, $result[0]['product_id']);
1308         $this->assertEquals(3, $result[1]['product_id']);
1309         $this->assertEquals(2, $result[2]['product_id']);
1310     }
1311
1312     protected function _selectOrderByDesc()
1313     {
1314         $select = $this->_db->select()
1315             ->from('zfproducts')
1316             ->order("product_id DESC");
1317         return $select;
1318     }
1319
1320     public function testSelectOrderByDesc()
1321     {
1322         $select = $this->_selectOrderByDesc();
1323         $stmt = $this->_db->query($select);
1324         $result = $stmt->fetchAll();
1325         $this->assertEquals(3, count($result),
1326             'Expected count of result set to be 2');
1327         $this->assertEquals(3, $result[0]['product_id']);
1328     }
1329
1330     /**
1331      * Test support for qualified table in order(),
1332      * e.g. order('schema.table').
1333      */
1334     protected function _selectOrderByQualified()
1335     {
1336         $select = $this->_db->select()
1337             ->from('zfproducts')
1338             ->order("zfproducts.product_id");
1339         return $select;
1340     }
1341
1342     public function testSelectOrderByQualified()
1343     {
1344         $select = $this->_selectOrderByQualified();
1345         $stmt = $this->_db->query($select);
1346         $result = $stmt->fetchAll();
1347         $this->assertEquals(1, $result[0]['product_id']);
1348     }
1349
1350     /**
1351      * Test support for Zend_Db_Expr in order(),
1352      * e.g. order(new Zend_Db_Expr('id+1')).
1353      */
1354     protected function _selectOrderByExpr()
1355     {
1356         $select = $this->_db->select()
1357             ->from('zfproducts')
1358             ->order(new Zend_Db_Expr("1"));
1359         return $select;
1360     }
1361
1362     public function testSelectOrderByExpr()
1363     {
1364         $select = $this->_selectOrderByExpr();
1365         $stmt = $this->_db->query($select);
1366         $result = $stmt->fetchAll();
1367         $this->assertEquals(1, $result[0]['product_id']);
1368     }
1369
1370     /**
1371      * Test automatic conversion of SQL functions to
1372      * Zend_Db_Expr, e.g. order('LOWER(title)')
1373      * should give the same result as
1374      * order(new Zend_Db_Expr('LOWER(title)')).
1375      */
1376     protected function _selectOrderByAutoExpr()
1377     {
1378         $products = $this->_db->quoteIdentifier('zfproducts');
1379         $product_id = $this->_db->quoteIdentifier('product_id');
1380
1381         $select = $this->_db->select()
1382             ->from('zfproducts')
1383             ->order("ABS($products.$product_id)");
1384         return $select;
1385     }
1386
1387     public function testSelectOrderByAutoExpr()
1388     {
1389         $select = $this->_selectOrderByAutoExpr();
1390         $stmt = $this->_db->query($select);
1391         $result = $stmt->fetchAll();
1392         $this->assertEquals(1, $result[0]['product_id']);
1393     }
1394
1395     /**
1396      * Test ORDER BY clause that contains multiple lines.
1397      * See ZF-1822, which says that the regexp matching
1398      * ASC|DESC fails when string is multi-line.
1399      */
1400     protected function _selectOrderByMultiLine()
1401     {
1402         $select = $this->_db->select()
1403             ->from('zfproducts')
1404             ->order("product_id\nDESC");
1405         return $select;
1406     }
1407
1408     public function testSelectOrderByMultiLine()
1409     {
1410         $select = $this->_selectOrderByMultiLine();
1411         $stmt = $this->_db->query($select);
1412         $result = $stmt->fetchAll();
1413         $this->assertEquals(3, $result[0]['product_id']);
1414     }
1415
1416     /**
1417      * @group ZF-4246
1418      */
1419     protected function _checkExtraField($result)
1420     {
1421         // Check that extra field ZEND_DB_ROWNUM isn't present
1422         // (particulary with Db2 & Oracle)
1423         $this->assertArrayNotHasKey('zend_db_rownum', $result);
1424         $this->assertArrayNotHasKey('ZEND_DB_ROWNUM', $result);
1425     }
1426
1427     /**
1428      * Test adding a LIMIT clause to a Zend_Db_Select object.
1429      */
1430     protected function _selectLimit()
1431     {
1432         $select = $this->_db->select()
1433             ->from('zfproducts')
1434             ->order('product_id')
1435             ->limit(1);
1436         return $select;
1437     }
1438
1439     /**
1440      * @group ZF-4246
1441      */
1442     public function testSelectLimit()
1443     {
1444         $select = $this->_selectLimit();
1445         $stmt = $this->_db->query($select);
1446         $result = $stmt->fetchAll();
1447         $this->assertEquals(1, count($result));
1448         $this->assertEquals(1, $result[0]['product_id']);
1449         $this->_checkExtraField($result[0]);
1450     }
1451
1452     /**
1453      * @group ZF-5263
1454      * @group ZF-4246
1455      */
1456     public function testSelectLimitFetchCol()
1457     {
1458         $product_id = $this->_db->quoteIdentifier('product_id');
1459
1460         $select = $this->_db->select()
1461             ->from('zfproducts', 'product_name')
1462             ->where($product_id . ' = ?', 3)
1463             ->limit(1);
1464
1465         $result = $this->_db->fetchCol($select);
1466         $this->assertEquals(1, count($result));
1467         $this->assertEquals('OS X', $result[0]);
1468         $this->_checkExtraField($result);
1469     }
1470
1471     protected function _selectLimitNone()
1472     {
1473         $select = $this->_db->select()
1474             ->from('zfproducts')
1475             ->order('product_id')
1476             ->limit(); // no limit
1477         return $select;
1478     }
1479
1480     /**
1481      * @group ZF-4246
1482      */
1483     public function testSelectLimitNone()
1484     {
1485         $select = $this->_selectLimitNone();
1486         $stmt = $this->_db->query($select);
1487         $result = $stmt->fetchAll();
1488         $this->assertEquals(3, count($result));
1489         $this->_checkExtraField($result[0]);
1490     }
1491
1492     protected function _selectLimitOffset()
1493     {
1494         $select = $this->_db->select()
1495             ->from('zfproducts')
1496             ->order('product_id')
1497             ->limit(1, 1);
1498         return $select;
1499     }
1500
1501     /**
1502      * @group ZF-4246
1503      */
1504     public function testSelectLimitOffset()
1505     {
1506         $select = $this->_selectLimitOffset();
1507         $stmt = $this->_db->query($select);
1508         $result = $stmt->fetchAll();
1509         $this->assertEquals(1, count($result));
1510         $this->assertEquals(2, $result[0]['product_id']);
1511         $this->_checkExtraField($result[0]);
1512     }
1513
1514     /**
1515      * Test the limitPage() method of a Zend_Db_Select object.
1516      */
1517     protected function _selectLimitPageOne()
1518     {
1519         $select = $this->_db->select()
1520             ->from('zfproducts')
1521             ->order('product_id')
1522             ->limitPage(1, 1); // first page, length 1
1523         return $select;
1524     }
1525
1526     /**
1527      * @group ZF-4246
1528      */
1529     public function testSelectLimitPageOne()
1530     {
1531         $select = $this->_selectLimitPageOne();
1532         $stmt = $this->_db->query($select);
1533         $result = $stmt->fetchAll();
1534         $this->assertEquals(1, count($result));
1535         $this->assertEquals(1, $result[0]['product_id']);
1536         $this->_checkExtraField($result[0]);
1537     }
1538
1539     protected function _selectLimitPageTwo()
1540     {
1541         $select = $this->_db->select()
1542             ->from('zfproducts')
1543             ->order('product_id')
1544             ->limitPage(2, 1); // second page, length 1
1545         return $select;
1546     }
1547
1548     /**
1549      * @group ZF-4246
1550      */
1551     public function testSelectLimitPageTwo()
1552     {
1553         $select = $this->_selectLimitPageTwo();
1554         $stmt = $this->_db->query($select);
1555         $result = $stmt->fetchAll();
1556         $this->assertEquals(1, count($result));
1557         $this->assertEquals(2, $result[0]['product_id']);
1558         $this->_checkExtraField($result[0]);
1559     }
1560
1561     /**
1562      * Test the getPart() and reset() methods of a Zend_Db_Select object.
1563      */
1564     public function testSelectGetPartAndReset()
1565     {
1566         $select = $this->_db->select()
1567             ->from('zfproducts')
1568             ->limit(1);
1569         $count = $select->getPart(Zend_Db_Select::LIMIT_COUNT);
1570         $this->assertEquals(1, $count);
1571
1572         $select->reset(Zend_Db_Select::LIMIT_COUNT);
1573         $count = $select->getPart(Zend_Db_Select::LIMIT_COUNT);
1574         $this->assertNull($count);
1575
1576         $select->reset(); // reset the whole object
1577         $from = $select->getPart(Zend_Db_Select::FROM);
1578         $this->assertTrue(empty($from));
1579     }
1580
1581     /**
1582      * Test the UNION statement for a Zend_Db_Select object.
1583      */
1584     protected function _selectUnionString()
1585     {
1586         $bugs = $this->_db->quoteIdentifier('zfbugs');
1587         $bug_id = $this->_db->quoteIdentifier('bug_id');
1588         $bug_status = $this->_db->quoteIdentifier('bug_status');
1589         $products = $this->_db->quoteIdentifier('zfproducts');
1590         $product_id = $this->_db->quoteIdentifier('product_id');
1591         $product_name = $this->_db->quoteIdentifier('product_name');
1592         $id = $this->_db->quoteIdentifier('id');
1593         $name = $this->_db->quoteIdentifier('name');
1594         $sql1 = "SELECT $bug_id AS $id, $bug_status AS $name FROM $bugs";
1595         $sql2 = "SELECT $product_id AS $id, $product_name AS $name FROM $products";
1596
1597         $select = $this->_db->select()
1598             ->union(array($sql1, $sql2))
1599             ->order('id');
1600         return $select;
1601     }
1602
1603     public function testSelectUnionString()
1604     {
1605         $select = $this->_selectUnionString();
1606         $stmt = $this->_db->query($select);
1607         $result = $stmt->fetchAll();
1608         $this->assertEquals(7, count($result));
1609         $this->assertEquals(1, $result[0]['id']);
1610     }
1611
1612     /**
1613      * @group ZF-4772
1614      * @expectedException Zend_Db_Select_Exception
1615      */
1616     public function testSelectUnionNoArrayThrowsException()
1617     {
1618         $this->_db->select()->union('string');
1619     }
1620
1621     /**
1622      * @group ZF-4772
1623      * @expectedException Zend_Db_Select_Exception
1624      */
1625     public function testSelectUnionInvalidUnionTypeThrowsException()
1626     {
1627         $this->_db->select()->union(array(), 'foo');
1628     }
1629
1630     /**
1631      * @group ZF-6653
1632      */
1633     public function testSelectIsTheSameWhenCallingFromAndJoinInDifferentOrders()
1634     {
1635         $selectFromThenJoin = $this->_db->select();
1636         $selectFromThenJoin->from(array('f' => 'foo'), array('columnfoo'))
1637             ->joinLeft(array('b' => 'bar'), 'f.columnfoo2 = b.barcolumn2', array('baralias' => 'barcolumn'));
1638
1639         $selectJoinThenFrom = $this->_db->select();
1640         $selectJoinThenFrom->joinLeft(array('b' => 'bar'), 'f.columnfoo2 = b.barcolumn2', array('baralias' => 'barcolumn'))
1641             ->from(array('f' => 'foo'), array('columnfoo'));
1642
1643         $sqlSelectFromThenJoin = $selectFromThenJoin->assemble();
1644         $sqlSelectJoinThenFrom = $selectJoinThenFrom->assemble();
1645         $this->assertEquals($sqlSelectFromThenJoin, $sqlSelectJoinThenFrom);
1646     }
1647
1648     /**
1649      * @group ZF-6653
1650      */
1651     public function testSelectIsTheSameWhenCallingMultipleFromsAfterJoin()
1652     {
1653         $selectFromThenJoin = $this->_db->select();
1654         $selectFromThenJoin->from(array('f' => 'foo'), array('columnfoo'))
1655             ->from(array('d' => 'doo'), array('columndoo'))
1656             ->joinLeft(array('b' => 'bar'), 'f.columnfoo2 = b.barcolumn2', array('baralias' => 'barcolumn'));
1657
1658         $selectJoinThenFrom = $this->_db->select();
1659         $selectJoinThenFrom->joinLeft(array('b' => 'bar'), 'f.columnfoo2 = b.barcolumn2', array('baralias' => 'barcolumn'))
1660             ->from(array('f' => 'foo'), array('columnfoo'))
1661             ->from(array('d' => 'doo'), array('columndoo'));
1662
1663         $sqlSelectFromThenJoin = $selectFromThenJoin->assemble();
1664         $sqlSelectJoinThenFrom = $selectJoinThenFrom->assemble();
1665         $this->assertEquals($sqlSelectFromThenJoin, $sqlSelectJoinThenFrom);
1666     }
1667
1668     /**
1669      * @group ZF-6653
1670      */
1671     public function testSelectWithMultipleFromsAfterAJoinWillProperlyOrderColumns()
1672     {
1673         $select = $this->_selectWithMultipleFromsAfterAJoinWillProperlyOrderColumns();
1674         $quote = $this->_db->getQuoteIdentifierSymbol();
1675         $target = 'SELECT `f`.`columnfoo`, `d`.`columndoo`, `b`.`barcolumn` AS `baralias` FROM ' . $this->_db->quoteTableAs('foo', 'f')
1676             . "\n" . ' INNER JOIN ' . $this->_db->quoteTableAs('doo', 'd')
1677             . "\n" . ' LEFT JOIN ' . $this->_db->quoteTableAs('bar', 'b') . ' ON f.columnfoo2 = b.barcolumn2';
1678         if ($quote != '`') {
1679             $target = str_replace('`', $quote, $target);
1680         }
1681         $this->assertEquals($target, $select->assemble());
1682     }
1683
1684     protected function _selectWithMultipleFromsAfterAJoinWillProperlyOrderColumns()
1685     {
1686         $selectJoinThenFrom = $this->_db->select();
1687         $selectJoinThenFrom->joinLeft(array('b' => 'bar'), 'f.columnfoo2 = b.barcolumn2', array('baralias' => 'barcolumn'))
1688             ->from(array('f' => 'foo'), array('columnfoo'))
1689             ->from(array('d' => 'doo'), array('columndoo'));
1690         return $selectJoinThenFrom;
1691     }
1692
1693     public function testSerializeSelect()
1694     {
1695         /* checks if the adapter has effectively gotten serialized,
1696            no exceptions are thrown here, so it's all right */
1697         $serialize = serialize($this->_select());
1698         $this->assertType('string',$serialize);
1699     }
1700
1701 }