Skip to content

HTTPS clone URL

Subversion checkout URL

You can clone with
or
.
Download ZIP
Browse files

Fixes #957.

  • Loading branch information...
commit 9cf2824050cea2e4a522a74064bbb7c51ea9599d 1 parent 6c8a5bb
@resurtm authored
View
1  CHANGELOG
@@ -11,6 +11,7 @@ Version 1.1.13 work in progress
- Bug #276: Tweaked CGridView stylesheet to include a hover style for the selected row (acorncom)
- Bug #810: Gii now adds a number to the end of relation name if same named relation already exists instead of not generating relation (n30kill, samdark)
- Bug #837: Fixed method CDbCriteria::__wakeup(), allowing to keep custom names for params and update all string parts for automatic params (klimov-paul)
+- Bug #957: CDbCommandBuilder::createUpdateCommand() used to create illogical SQL when JOIN and LIMIT/OFFSET parts were used at once (resurtm)
- Bug #962: Fixed handling of negative timestamps in CDateFormatter::format() (johnmendonca)
- Bug #1095: Added missing retry_interval parameter of addServer function call in CMemCache (Lisio)
- Bug #1181: Fixed can read but not save binary data e.g. BYTEA on PostgreSQL (karmakaze)
View
10 framework/db/schema/CDbCommandBuilder.php
@@ -271,7 +271,7 @@ public function createUpdateCommand($table,$data,$criteria)
$sql=$this->applyJoin($sql,$criteria->join);
$sql=$this->applyCondition($sql,$criteria->condition);
$sql=$this->applyOrder($sql,$criteria->order);
- $sql=$this->applyLimit($sql,$criteria->limit,$criteria->offset);
+ $sql=$this->applyLimit($sql,$criteria->limit,$criteria->offset,!empty($criteria->join));
$command=$this->_connection->createCommand($sql);
$this->bindValues($command,array_merge($values,$criteria->params));
@@ -379,10 +379,16 @@ public function applyOrder($sql,$orderBy)
* @param string $sql SQL query string without LIMIT and OFFSET.
* @param integer $limit maximum number of rows, -1 to ignore limit.
* @param integer $offset row offset, -1 to ignore offset.
+ * @param boolean $sqlHasJoin whether provided SQL has the JOIN part. This parameter available since 1.1.13.
* @return string SQL with LIMIT and OFFSET
*/
- public function applyLimit($sql,$limit,$offset)
+ public function applyLimit($sql,$limit,$offset,$sqlHasJoin=false)
{
+ // LIMIT and OFFSET should be ignored when UPDATE query performed with applied JOIN part. This is illogical
+ // in any case. Please refer to the issue #957 for more details. Note that searching $sql for substring
+ // 'JOIN' is not a robust solution—assume situation where we're dealing with the table column called 'JOIN'.
+ if(strpos($sql,'UPDATE')===0 && $sqlHasJoin)
+ return $sql;
if($limit>=0)
$sql.=' LIMIT '.(int)$limit;
if($offset>0)
View
81 tests/framework/db/CDbCommandBuilderTest.php
@@ -0,0 +1,81 @@
+<?php
+
+Yii::import('system.db.CDbConnection');
+Yii::import('system.db.schema.mysql.CMysqlSchema');
+
+class CDbCommandBuilderTest extends CTestCase
+{
+ /**
+ * @var CDbConnection
+ */
+ private $db;
+ /**
+ * @var CDbCommandBuilder
+ */
+ private $builder;
+
+ public function setUp()
+ {
+ if(!extension_loaded('pdo') || !extension_loaded('pdo_mysql'))
+ $this->markTestSkipped('PDO and MySQL extensions are required.');
+
+ $this->db=new CDbConnection('mysql:host=127.0.0.1;dbname=yii','test','test');
+ $this->db->charset='UTF8';
+ $this->db->enableParamLogging=true;
+ try
+ {
+ $this->db->active=true;
+ }
+ catch(Exception $e)
+ {
+ $schemaFile=realpath(dirname(__FILE__).'/data/mysql.sql');
+ $this->markTestSkipped("Please read $schemaFile for details on setting up the test environment for MySQL test case.");
+ }
+
+ $tables=array('comments','post_category','posts','categories','profiles','users','items','orders','types');
+ foreach($tables as $table)
+ $this->db->createCommand("DROP TABLE IF EXISTS $table CASCADE")->execute();
+
+ $sqls=file_get_contents(dirname(__FILE__).'/data/mysql.sql');
+ foreach(explode(';',$sqls) as $sql)
+ {
+ if(trim($sql)!=='')
+ $this->db->createCommand($sql)->execute();
+ }
+
+ $this->builder=$this->db->schema->commandBuilder;
+ }
+
+ public function tearDown()
+ {
+ $this->db->active=false;
+ }
+
+ public function testIssue957()
+ {
+ // changing origin data
+ $criteria=new CDbCriteria();
+ $criteria->join='INNER JOIN profiles ON profiles.user_id=users.id';
+ $criteria->condition='user_id=:uid';
+ $criteria->params[':uid']=2;
+ $criteria->limit=1;
+ $criteria->offset=1;
+ $updateCommand=$this->builder->createUpdateCommand('users',array('password'=>'123','first_name'=>'321'),$criteria);
+ $this->assertNotSame(false,strpos($updateCommand->text,'UPDATE'));
+ $this->assertNotSame(false,strpos($updateCommand->text,'INNER JOIN'));
+ $this->assertNotSame(false,strpos($updateCommand->text,'ON'));
+ $this->assertSame(false,strpos($updateCommand->text,'LIMIT'));
+ $updateCommand->execute();
+
+ // asserting changed data
+ $criteria=new CDbCriteria();
+ $criteria->select='password';
+ $users=$this->builder->createFindCommand('users',$criteria)->queryColumn();
+ $this->assertEquals(array('pass1','123','pass3'),$users);
+
+ $criteria=new CDbCriteria();
+ $criteria->select='first_name';
+ $profiles=$this->builder->createFindCommand('profiles',$criteria)->queryColumn();
+ $this->assertEquals(array('first 1','first 2'),$profiles);
+ }
+}
Please sign in to comment.
Something went wrong with that request. Please try again.