Skip to content

Loading…

DDC-980: SQL alias behavior inconsistent in UPDATE ... WHERE (subselect) #5515

Closed
doctrinebot opened this Issue · 3 comments

2 participants

@doctrinebot

Jira issue originally created by user lfeistel:

When using a DQL UPDATE with a subselect in the WHERE clause (such as what the size() function generates), the subselect is trying to reference an alias that is not defined in the main UPDATE clause. Using the size() function similarly in a SELECT statement works fine, the problem is only occurring for me when I try an UPDATE. I have not tried it with INSERT or DELETE, but I would suspect a similar inconsistency with anything other than the more common usage with SELECT.

$q = $em->createQuery('UPDATE Event e SET e.no*speakers = 1, e.no*credits = 1 WHERE size(e.Instances) = 0');
print $q->getSQL();

Output:

UPDATE Event SET no*speakers = 1, no_credits = 1 WHERE (SELECT COUNT(*) FROM Instance c0_ WHERE c0_.Event_id = c1*.id) = 0

And, $q->execute() results in:

PHP Fatal error:  Uncaught exception 'PDOException' with message 'SQLSTATE[42S22]: Column not found: 1054 Unknown column 'c1_.id' in 'where clause'' in /var/www/v3-lf/library/Doctrine/DBAL/Connection.php:657
Stack trace: #0 /var/www/v3-lf/library/Doctrine/DBAL/Connection.php(657): PDO->exec('UPDATE Event...')
@doctrinebot

Comment created by @beberlei:

Would something like the following work on ALL database vendors?

UPDATE Event SET no*speakers = 1, no_credits = 1 WHERE (SELECT COUNT(*) FROM Instance c0_ WHERE c0_.Event*id = Event.id) = 0
@doctrinebot

Comment created by @beberlei:

It does work. Fixed!

@doctrinebot

Issue was closed with resolution "Fixed"

@beberlei beberlei was assigned by doctrinebot
@doctrinebot doctrinebot added this to the 2.0.1 milestone
@doctrinebot doctrinebot closed this
@doctrinebot doctrinebot added the Bug label
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.