Details

    • Type: Bug Bug
    • Status: Open
    • Priority: Major Major
    • Resolution: Unresolved
    • Affects Version/s: 1.2.1
    • Fix Version/s: None
    • Component/s: Query
    • Labels:
      None
    • Environment:
      php 5.3.0, mysql 5.1.36, symfony 1.4.3

      Description

      1)Code
      Doctrine_Query::create ()
      ->select ( "u.user_id,mr.message_id,mr.message_senddate,mr.message_title,mr.message_content,mr.message_alias,mr.message_categoryid," )
      ->from ( "user u" )
      ->leftJoin ( "u.messages mr ON u.user_id = mr.message_sender" )
      ->where ( "u.user_id = ?", 2 )
      ->limit(1)
      ->fetchArray ();

      This code must return only one result but it returns all results in which user_id is 2 in the database

      If i looked it in Symfony Debug bar, i saw that;

      2)Symfony debug toolbar values:

      #Query A
      SELECT DISTINCT u2.user_id FROM users u2 LEFT JOIN messages m2 ON (u2.user_id = m2.message_sender) WHERE u2.user_id = 2 LIMIT 1

      #Query B
      SELECT u.user_id AS u_user_id, m.message_id AS mmessage_id, m.message_senddate AS mmessage_senddate, m.message_title AS mmessage_title, m.message_content AS mmessage_content, m.message_alias AS mmessage_alias, m.message_categoryid AS m_message_categoryid FROM users u LEFT JOIN messages m ON (u.user_id = m.message_sender) WHERE u.user_id IN ('2') AND (u.user_id = 2)

      And if i test this code in MYSQL query browser i got a result like that;

      SELECT u.user_id AS u_user_id, m.message_id AS mmessage_id, m.message_senddate AS mmessage_senddate, m.message_title AS mmessage_title, m.message_content AS mmessage_content, m.message_alias AS mmessage_alias, m.message_categoryid AS m_message_categoryid
      FROM users u
      LEFT JOIN messages m ON ( u.user_id = m.message_sender )
      WHERE u.user_id
      IN (
      '2'
      )
      AND (
      u.user_id =2
      )
      LIMIT 0 , 30

      If i changed SQL code like below and put the limit at end, the problem solved:

      #Query A
      SELECT DISTINCT u2.user_id FROM users u2 LEFT JOIN messages m2 ON (u2.user_id = m2.message_sender) WHERE u2.user_id = 2

      #Query B
      SELECT u.user_id AS u_user_id, m.message_id AS mmessage_id, m.message_senddate AS mmessage_senddate, m.message_title AS mmessage_title, m.message_content AS mmessage_content, m.message_alias AS mmessage_alias, m.message_categoryid AS m_message_categoryid FROM users u LEFT JOIN messages m ON (u.user_id = m.message_sender) WHERE u.user_id IN ('2') AND (u.user_id = 2) LIMIT 1

      But doctrine's limit(1) metod is Select Distinct

      The query which starts with ... is at query A but it needs to be at the end of query B

        Activity

        Hide
        O. added a comment -

        My schema file.

        Show
        O. added a comment - My schema file.
        Hide
        Jonathan H. Wage added a comment -

        It is hard to understand your issue description. Can you make a clear failing Doctrine test case for us to look at? Thanks, Jon

        Show
        Jonathan H. Wage added a comment - It is hard to understand your issue description. Can you make a clear failing Doctrine test case for us to look at? Thanks, Jon
        Hide
        O. added a comment -
        Show
        O. added a comment - Adding problem screencast: http://www.screencast-o-matic.com/watch/c6ebiN1qS
        Hide
        O. added a comment -

        I found the source of the problem:
        lib\vendor\doctrine\Doctrine\Query.php in getSqlQuery() function.

        $limitSubquery values are default TRUE, but FALSE have to be here.. (to me)
        How can we define the value of the DQL $limitSubquery?

        Show
        O. added a comment - I found the source of the problem: lib\vendor\doctrine\Doctrine\Query.php in getSqlQuery() function. $limitSubquery values are default TRUE, but FALSE have to be here.. (to me) How can we define the value of the DQL $limitSubquery?
        Hide
        will ferrer added a comment -

        Hi O

        I made a patch which will allow you to turn off the limit subquery. If you would like to check it out here is the bug it is posted on:

        http://www.doctrine-project.org/jira/browse/DC-701

        Hope that helps.

        Will

        Show
        will ferrer added a comment - Hi O I made a patch which will allow you to turn off the limit subquery. If you would like to check it out here is the bug it is posted on: http://www.doctrine-project.org/jira/browse/DC-701 Hope that helps. Will

          People

          • Assignee:
            Guilherme Blanco
            Reporter:
            O.
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated: