Doctrine 2 - ORM
  1. Doctrine 2 - ORM
  2. DDC-980

SQL alias behavior inconsistent in UPDATE ... WHERE (subselect)

    Details

    • Type: Bug Bug
    • Status: Resolved
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 2.0
    • Fix Version/s: 2.0.1, 2.1
    • Component/s: None
    • Security Level: All
    • Labels:
      None

      Description

      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...')
      

        Activity

        Hide
        Benjamin Eberlei added a comment -

        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
        
        Show
        Benjamin Eberlei added a comment - 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
        Hide
        Benjamin Eberlei added a comment -

        It does work. Fixed!

        Show
        Benjamin Eberlei added a comment - It does work. Fixed!

          People

          • Assignee:
            Benjamin Eberlei
            Reporter:
            Lee Feistel
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: