Command Builder produces faulty MySQL UPDATEs with JOINs #124

Closed
qiangxue opened this Issue Feb 15, 2012 · 0 comments

2 participants

@qiangxue
Yii Software LLC member

Due to a specialty in MySQL's UPDATE syntax (JOINs must be placed before SET. cf. http://dev.mysql.com/doc/refman/5.1/en/update.html and http://dev.mysql.com/doc/refman/5.1/en/join.html), CDbCommandBuilder produces SQL broken in MySQL's eyes if JOINs are involved. Attached patch has proven to correct this but seems to be a bit of an overkill.

Migrated from http://code.google.com/p/yii/issues/detail?id=2788


earlier comments

dasourcerer said, at 2011-09-05T14:29:42.000Z:

Updated patch: The CDbCommandBuilder.createUpdateCounterCommand() method has been affected as well.

dasourcerer said, at 2011-09-05T14:32:15.000Z:

Sorry, the previous patch contained a small error.

dasourcerer said, at 2011-11-07T15:03:04.000Z:

The problem still persists. The following piece of code will throw an exception in the blog demo: Post::model()->updateAll(array( 'content'=>'m00', ), array( 'join'=>'JOIN {{user}} u ON `author_id`=u.`id`', 'condition'=>'u.`username`="demo"', )); The generated query will be: UPDATE `tbl_post` SET `content`=:yp0 JOIN tbl_user u ON `author_id`=u.`id` WHERE u.`username`="demo"; While it should really be: UPDATE `tbl_post` JOIN tbl_user u ON `author_id` = u.`id` SET `content`=:yp0 WHERE u.`username`="demo";

dasourcerer said, at 2011-11-07T15:43:08.000Z:

Uh, I forgot: The error thrown by MySQL is error #1064 / sqlstate 42000, which is a parse error.

qiang.xue said, at 2012-01-01T03:36:54.000Z:

set for 1.1.10 milestone

@DaSourcerer DaSourcerer added a commit that referenced this issue Feb 16, 2012
@DaSourcerer DaSourcerer Issue #124 (#2788) 59d2560
@DaSourcerer DaSourcerer added a commit to DaSourcerer/yii that referenced this issue Feb 17, 2012
@DaSourcerer DaSourcerer Fixes issue #124 (aka ticket #2788) - CDbCommandBuilder produces faulty
mysql update queries when joins are involved
f6c31e0
@cebe cebe referenced this issue Mar 19, 2012
Closed

Issue #124 #354

@cebe cebe added a commit to cebe/yii that referenced this issue Mar 19, 2012
@DaSourcerer DaSourcerer Add CMysqlCommandBuilder to handle joins on update
Fixes issue #124
CDbCommandBuilder produced faulty mysql update queries
when joins are involved. JOIN has to come before SET in mysql.
4ac67ea
@cebe cebe added a commit to cebe/yii that referenced this issue Mar 19, 2012
@DaSourcerer DaSourcerer added some unit tests mysql
testing mysql update commands with new CMysqlCommandBuilder
issue #124
5100f24
@cebe cebe added a commit to cebe/yii that referenced this issue Aug 28, 2012
@cebe cebe better fix for issues #124
changed position of JOIN in UPDATE by overwriting applyJoin()
266df50
@cebe cebe was assigned Aug 28, 2012
@cebe cebe closed this Aug 28, 2012
@cebe cebe added a commit to cebe/yii that referenced this issue Sep 3, 2012
@cebe cebe Merge branch 'master' into 93-relational-beforefind-criteria-modifica…
…tion

* master: (180 commits)
  Updated changelog with last-modified date change.
  Changed the CHttpCacheFilter to use RFC 1123 complaint dates when returning the last-modified header.
  tabs...
  gii: better default validation "length" rule for fixed-point / floating-point field type (MYSQL)
  fixes #1319
  updated comment in CHttpSession
  Update framework/YiiBase.php
  Update docs/guide/database.arr.txt
  New unit test for enhanced $gCProbability s/getters in CHttpSession #486. Fixed those methods to process corner cases properly. Initial $gCProbability is now double(1.0), was > 1.0 before.
  Bug #112: MSSQL: database abstraction layer now uses native transaction support of the SQLSRV driver.
  Forgot about spaces in comments creating procedure call. [ci skip]
  Better comments testing method name.
  Forgot about local MSSQL database settings.
  MSSQL unit testing SQL file reformatted and decorated. MSSQL testing class improved. Added unit tests for column comments retrieving.
  Removed old message.
  Update docs/guide/topics.i18n.txt
  Update docs/guide/topics.i18n.txt
  Requirements checker: russian (ru_RU) messages.
  Minor fixes and refinements to the MSSQL unit tests.
  better fix for issues #124
  ...

Conflicts:
	UPGRADE
be1fbf1
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment