Doctrine 1
  1. Doctrine 1
  2. DC-81

Using WITH in combinations with LIMIT returns unexpected results

    Details

    • Type: Bug Bug
    • Status: Reopened
    • Priority: Major Major
    • Resolution: Unresolved
    • Affects Version/s: 1.2.0-ALPHA1
    • Fix Version/s: None
    • Component/s: None
    • Labels:
      None
    • Environment:
      Mac OS X 10.6.1

      Description

      When I try to limit left joined records with an additional WITH clause, it seems to be bugged when also specifying a LIMIT.

      My DQL:

      FROM Transporter t LEFT JOIN t.Profile p LEFT JOIN t.Requests r WITH (r.distributor_id = ?) WHERE t.is_active = ? ORDER BY p.company_country, p.company_name LIMIT 20

      This returns me 0 results, while removing the LIMIT (or the WITH) will return me 2 results.

        Activity

        Gerry Vandermaesen created issue -
        Hide
        Jonathan H. Wage added a comment -

        Sorry this is just not enough information to produce the problem. I ran some basic tests looking for what you pointed out but I didn't see any issues. Plus our tests cover this functionality so I imagine that if it were broke we'd get some failures. However, I could be wrong so if you could re-open and provide a failing test case for us that would help with getting it fixed.

        Show
        Jonathan H. Wage added a comment - Sorry this is just not enough information to produce the problem. I ran some basic tests looking for what you pointed out but I didn't see any issues. Plus our tests cover this functionality so I imagine that if it were broke we'd get some failures. However, I could be wrong so if you could re-open and provide a failing test case for us that would help with getting it fixed.
        Jonathan H. Wage made changes -
        Field Original Value New Value
        Status Open [ 1 ] Resolved [ 5 ]
        Resolution Cannot Reproduce [ 5 ]
        Hide
        Gerry Vandermaesen added a comment -

        I actually ran into this problem once again in another problem.

        Schema:

        Story:
        columns:
        id:
        type: integer(4)
        unsigned: true
        primary: true
        autoincrement: true

        Picture:
        columns:
        id:
        type: integer(4)
        unsigned: true
        primary: true
        autoincrement: true
        story_id:
        type: integer(4)
        unsigned: true
        notnull: true
        is_selected:
        type: boolean
        notnull: true
        default: false
        relations:
        Story:
        foreignAlias: Pictures
        local: story_id
        foreign: id
        type: one
        foreignType: many
        onDelete: CASCADE

        Query:

        Doctrine_Query::create()
        ->from('Story s')
        ->leftJoin('s.Pictures p WITH p.is_selected = ?', true)
        ->where('s.id = ?', 5)
        ->limit(10);

        Outputted SQL:

        SELECT "s"."id" AS "s_id", "s"."first_name" AS "sfirst_name", "s"."last_name" AS "slast_name", "s"."country" AS "scountry", "s"."email" AS "semail", "s"."content" AS "scontent", "s"."title" AS "stitle", "s"."summary" AS "ssummary", "s"."is_published" AS "sis_published", "s"."is_rejected" AS "sis_rejected", "s"."is_promoted" AS "sis_promoted", "s"."published_at" AS "spublished_at", "s"."created_at" AS "screated_at", "s"."updated_at" AS "supdated_at", "p"."id" AS "pid", "p"."story_id" AS "pstory_id", "p"."filename_original" AS "pfilename_original", "p"."filename_large" AS "pfilename_large", "p"."filename_thumb" AS "pfilename_thumb", "p"."mime_type" AS "pmime_type", "p"."is_selected" AS "pis_selected", "p"."created_at" AS "pcreated_at", "p"."updated_at" AS "p_updated_at" FROM "story" "s" LEFT JOIN "picture" "p" ON "s"."id" = "p"."story_id" AND ("p"."is_selected" = '1') WHERE "s"."id" IN (SELECT DISTINCT "s2"."id" FROM "story" "s2" LEFT JOIN "picture" "p2" ON "s2"."id" = "p2"."story_id" AND ("p2"."is_selected" = '5') WHERE "s2"."id" = '1' ORDER BY "s2"."published_at" DESC LIMIT 10) AND ("s"."id" = '5') ORDER BY "s"."published_at" DESC

        Notice the WHERE "s2"."id" = '1' !

        Show
        Gerry Vandermaesen added a comment - I actually ran into this problem once again in another problem. Schema: Story: columns: id: type: integer(4) unsigned: true primary: true autoincrement: true Picture: columns: id: type: integer(4) unsigned: true primary: true autoincrement: true story_id: type: integer(4) unsigned: true notnull: true is_selected: type: boolean notnull: true default: false relations: Story: foreignAlias: Pictures local: story_id foreign: id type: one foreignType: many onDelete: CASCADE Query: Doctrine_Query::create() ->from('Story s') ->leftJoin('s.Pictures p WITH p.is_selected = ?', true) ->where('s.id = ?', 5) ->limit(10); Outputted SQL: SELECT "s"."id" AS "s_ id", "s"."first_name" AS "s first_name", "s"."last_name" AS "s last_name", "s"."country" AS "s country", "s"."email" AS "s email", "s"."content" AS "s content", "s"."title" AS "s title", "s"."summary" AS "s summary", "s"."is_published" AS "s is_published", "s"."is_rejected" AS "s is_rejected", "s"."is_promoted" AS "s is_promoted", "s"."published_at" AS "s published_at", "s"."created_at" AS "s created_at", "s"."updated_at" AS "s updated_at", "p"."id" AS "p id", "p"."story_id" AS "p story_id", "p"."filename_original" AS "p filename_original", "p"."filename_large" AS "p filename_large", "p"."filename_thumb" AS "p filename_thumb", "p"."mime_type" AS "p mime_type", "p"."is_selected" AS "p is_selected", "p"."created_at" AS "p created_at", "p"."updated_at" AS "p _updated_at" FROM "story" "s" LEFT JOIN "picture" "p" ON "s"."id" = "p"."story_id" AND ("p"."is_selected" = '1') WHERE "s"."id" IN (SELECT DISTINCT "s2"."id" FROM "story" "s2" LEFT JOIN "picture" "p2" ON "s2"."id" = "p2"."story_id" AND ("p2"."is_selected" = '5') WHERE "s2"."id" = '1' ORDER BY "s2"."published_at" DESC LIMIT 10) AND ("s"."id" = '5') ORDER BY "s"."published_at" DESC Notice the WHERE "s2"."id" = '1' !
        Gerry Vandermaesen made changes -
        Resolution Cannot Reproduce [ 5 ]
        Status Resolved [ 5 ] Reopened [ 4 ]
        Hide
        Jonathan H. Wage added a comment -

        Can you provide a test case we can run?

        Show
        Jonathan H. Wage added a comment - Can you provide a test case we can run?
        Hide
        Gerry Vandermaesen added a comment -

        I could not figure out what test to write but I did narrow the problem down.

        It seems that the order of the generated SQL clauses does not match the order of the passed parameters, so in the final SQL query the values are mixed up. You can actually see that happening in the SQL query above. I also noticed there a special cases hardcoded in the code for MySQL and PgSQL as far as these "limit subqueries" go, so that might be another reason why you havent bumped into problem before. I was using an SQLite driver.

        I suppose something goes wrong in the SQL generation, but I don't know the core good enough to find out where exactly it does go wrong, so I'm afraid I can't attach a test case, but it should be easy to reproduce now anyway.

        Show
        Gerry Vandermaesen added a comment - I could not figure out what test to write but I did narrow the problem down. It seems that the order of the generated SQL clauses does not match the order of the passed parameters, so in the final SQL query the values are mixed up. You can actually see that happening in the SQL query above. I also noticed there a special cases hardcoded in the code for MySQL and PgSQL as far as these "limit subqueries" go, so that might be another reason why you havent bumped into problem before. I was using an SQLite driver. I suppose something goes wrong in the SQL generation, but I don't know the core good enough to find out where exactly it does go wrong, so I'm afraid I can't attach a test case, but it should be easy to reproduce now anyway.

        This list may be incomplete, as errors occurred whilst retrieving source from linked applications:

        • Request to http://www.doctrine-project.org/fisheye/ failed: Error in remote call to 'FishEye 0 (http://www.doctrine-project.org/fisheye/)' (http://www.doctrine-project.org/fisheye) [AbstractRestCommand{path='/rest-service-fe/search-v1/crossRepositoryQuery', params={query=DC-81, expand=changesets[0:20].revisions[0:29],reviews}, methodType=GET}] : Received status code 503 (Service Temporarily Unavailable)

          People

          • Assignee:
            Jonathan H. Wage
            Reporter:
            Gerry Vandermaesen
          • Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

            • Created:
              Updated: