Using WITH in combinations with LIMIT returns unexpected results

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.

Can you provide a test case we can run?

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

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.