[DC-81] Using WITH in combinations with LIMIT returns unexpected results Created: 06/Oct/09  Updated: 15/Jun/10

Status: Reopened
Project: Doctrine 1
Component/s: None
Affects Version/s: 1.2.0-ALPHA1
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Gerry Vandermaesen Assignee: Jonathan H. Wage
Resolution: Unresolved Votes: 0
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.



 Comments   
Comment by Jonathan H. Wage [ 02/Nov/09 ]

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.

Comment by Gerry Vandermaesen [ 16/Apr/10 ]

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' !

Comment by Jonathan H. Wage [ 08/Jun/10 ]

Can you provide a test case we can run?

Comment by Gerry Vandermaesen [ 15/Jun/10 ]

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.

Generated at Tue Jul 22 13:38:21 UTC 2014 using JIRA 6.2.3#6260-sha1:63ef1d6dac3f4f4d7db4c1effd405ba38ccdc558.