I know it’s late in the 4.1.0 release cycle, but I was hoping somebody could review this for that release:
rails:master ← al2o3cr:issue12770
opened 12:33AM - 22 Feb 14 UTC
This PR fixes the problem noted in #12770 where the clause added by the `default… _scope` did not correctly pick up the aliased table name (causing the condition to be on `"categorizations"."special"` instead of `"special_categorizations_categories"."special"`). This is apparently an error on some versions of Postgres, but produces incorrect results even when it can be successfully executed.
Before this patch, the SQL generated in the added test case is:
```
SELECT "categories"."id" AS t0_r0, "categories"."name" AS t0_r1, "categories"."type" AS t0_r2, "categories"."categorizations_count" AS t0_r3, "categorizations"."id" AS t1_r0, "categorizations"."category_id" AS t1_r1, "categorizations"."named_category_name" AS t1_r2, "categorizations"."post_id" AS t1_r3, "categorizations"."author_id" AS t1_r4, "categorizations"."special" AS t1_r5 FROM "categories"
LEFT OUTER JOIN "categorizations" "special_categorizations_categories"
ON "special_categorizations_categories"."category_id" = "categories"."id"
AND "categorizations"."special" = 't'
INNER JOIN "categorizations" ON "categories"."id" = "categorizations"."category_id"
WHERE "categorizations"."author_id" = ?
```
After the patch, the SQL is (mostly) correct:
```
SELECT "categories"."id" AS t0_r0, "categories"."name" AS t0_r1, "categories"."type" AS t0_r2, "categories"."categorizations_count" AS t0_r3, "categorizations"."id" AS t1_r0, "categorizations"."category_id" AS t1_r1, "categorizations"."named_category_name" AS t1_r2, "categorizations"."post_id" AS t1_r3, "categorizations"."author_id" AS t1_r4, "categorizations"."special" AS t1_r5 FROM "categories"
LEFT OUTER JOIN "categorizations" "special_categorizations_categories"
ON "special_categorizations_categories"."category_id" = "categories"."id"
AND "special_categorizations_categories"."special" = 't'
INNER JOIN "categorizations" ON "categories"."id" = "categorizations"."category_id" WHERE "categorizations"."author_id" = ?
```
The SQL is only "mostly correct" because the `SELECT` isn't picking up the table alias and results in incorrect data loaded into `special_characterizations` (both before and after the patch). I'll open a separate issue for that (#14155).
/cc @tenderlove
It fixes an issue where default_scopes in ON clauses don’t pick up the table’s aliased name. Without the patch, the resulting queries are generally valid SQL but return incorrect results.
I’d also appreciate any insight people can put on #14155 , but that’s lower priority since it’s been broken in the same fashion since 3.x.
Thanks,
—Matt Jones
