AREL probelm with postgress

rubyonrails-talk
1

Hi all,

I am testing locally with MySQL and deploying to heroku ( postgress ) The following query seems to

generate a PGError.

SELECT COUNT(*) FROM “events” INNER JOIN “events” “events_2” WHERE (“events”.“schedule_id” = 1 AND “events”.“start_at” = “events_2”.“start_at” AND “events_2”.“schedule_id” IN (3))

It is generated via an AREL builder by calling

coliding_events.count

where coliding_events is defined as

2

Hi all,

I am testing locally with MySQL and deploying to heroku ( postgress ) The following query seems to

generate a PGError.

SELECT COUNT(*) FROM “events” INNER JOIN “events” “events_2” WHERE (“events”.“schedule_id” = 1 AND “events”.“start_at” = “events_2”.“start_at” AND “events_2”.“schedule_id” IN (3))

Brad, I would recommend trying the following if you haven’t already done so:

  1. Writing the code without accessing the underlying ARel structure.

  2. Running the raw query within a PG client.

I haven’t played enough with ARel but I would suggest trying (1) and (2) above. Futhermore, by doing (1), you can see what SQL is being generated by using the

method to_sql and compare it against (2).

Good luck,

-Conrad

3

I’ve installed the postgres db under rails osx and I can reproduce the bug locally.

Makes sense to develop locally with postgres if deploying on heroku :slight_smile:

4

Seems that

SELECT COUNT(*) FROM “events” INNER JOIN “events” “events_2” WHERE (“events”.“schedule_id” = 1 AND “events”.“start_at” = “events_2”.“start_at” AND “events_2”.“schedule_id” IN (3));

is bad but this is ok

SELECT COUNT(*) FROM “events” INNER JOIN “events” “events_2” ON (“events”.“schedule_id” = 1 AND “events”.“start_at” = “events_2”.“start_at” AND “events_2”.“schedule_id” IN (3));

Perhaps in postgres using a JOIN always requires an ON clause though in MySQL this is not always the case.

Any ideas?

B

5

It looks like MySQL is forgiving the missing ON in your first expression thus executing your WHERE clause as a normal WHERE condition of the JOIN. PostgreSQL is not forgiving you the missing ON statement in the join. It’s looking for that ON because it knows you’re running a JOIN and the SQL syntax states you need an ON. Not all databases follow or enforce the same SQL rules so SQL code written for one will not port nicely over to the other all the time.

B.

6

I fixed the problem eventually but it was not obvious how.