Skip to content

Command builder - wrong SQL for delete with joins in MySQL #2159

Closed
serebrov opened this Issue Mar 1, 2013 · 5 comments

3 participants

@serebrov
serebrov commented Mar 1, 2013

When use criteria with joins in createDeleteCommand the generated SQL is wrong.
Example:

$builder->createDeleteCommand($table,new CDbCriteria(array(
    'condition'=>'u.`username`=:username and `posts`.`title`=:title',
    'join'=>'JOIN `users` u ON `author_id`=u.`id`',
    'params'=>array(':username'=>'user1', ':title'=>'new post delete'))));

Generated SQL:

DELETE FROM `posts` JOIN `users` u ON `author_id`=u.`id` 
WHERE u.`username`=:username and `posts`.`title`=:title

Correct SQL:

DELETE `posts` FROM `posts` JOIN `users` u ON `author_id`=u.`id` 
WHERE u.`username`=:username and `posts`.`title`=:title

Related links:

@cebe cebe was assigned Mar 6, 2013
@cebe cebe closed this in 4fdf2d0 Mar 12, 2013
@cebe cebe added a commit that referenced this issue Mar 12, 2013
@cebe cebe some adjustments to pull request #2159 307a190
@lamarck001

Same think happens when using mssql

Example:

$criteria = new CDbCriteria();
$criteria->select = 'param_data';   
$criteria->join ='JOIN ticket_data ON param_data.ticket_data_id = ticket_data.ticket_data_id';      
$criteria->condition ='ticket_data.ticket_data_id is null';     
$command = Yii::app()->db->schema->commandBuilder->createDeleteCommand('param_data', $criteria);

Generated SQL:

DELETE FROM [dbo].[param_data] 
JOIN ticket_data ON param_data.ticket_data_id = ticket_data.ticket_data_id 
WHERE ticket_data.ticket_data_id is null

Correct SQL:

DELETE [dbo].[param_data] FROM [dbo].[param_data] 
JOIN ticket_data ON param_data.ticket_data_id = ticket_data.ticket_data_id 
WHERE ticket_data.ticket_data_id is null

Fix: in db\schema\CDbCommandBuilder.php line: 19

$sql="DELETE {$table->rawName} FROM {$table->rawName}";
@serebrov

@lamarck001 - I fixed this for MySQL only, the fix is not complex, see the changes here - https://github.com/yiisoft/yii/pull/2160/files

@lamarck001

Okay i see it now.
I'm no expert but wouldn't be easier to add {$table->rawName} to CDbCommandBuilder than changing applyJoin($sql,$join) for both MySQL and MSSQL?

@serebrov

It was almost two years ago, so I don't remember the details, but it can be related to slightly different SQL syntax in different DBs.
It may be the same for MySQL and MSSQL, but is it the same for all supported databases?

@lamarck001

Guess you are right. I'm just reading about Sqlite (https://www.sqlite.org/lang_delete.html).

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.