Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Already on GitHub? Sign in to your account

Limit replaces select clause when used with having #8103

Open
wb-lifebooker opened this Issue Nov 2, 2012 · 14 comments

Comments

Projects
None yet
8 participants

Practical Problem:
Thing.group("things.id, other_things.id").having("other_things.id = 1"). includes(:other_things).count
vs
Thing.group("things.id, other_things.id").having("other_things.id = 1"). includes(:other_things).limit(1)

Easy way to reproduce it:

Thing.select("b").group("things.id").having("other_things.id = 1").includes(:other_things).limit(1)

SELECT DISTINCTthings.id FROMthingsLEFT OUTER JOINother_thingsONother_things.thing_id=things.id...

the select clause is completely replaced

Thing.select("b").group("things.id").having("other_things.id = 1").includes(:other_things).count

SELECT COUNT(DISTINCT b) AS count_b, b, things.id AS things_id FROMthingsLEFT OUTER JOINother_thingsONother_things.thing_id=things.idGROUP BY things.id HAVING other_things.id = 1


.count has the correct behavior, and leaves the extra columns in the select clause. .limit() replaces the select clause and breaks the query.

I'm not sure the culprit is limit, seems like includes will create a join to eager load the association since it's required for the condition, and thus replacing the select to be able to detect all the correct columns.

Can you try using joins instead of includes, to see if it works? That should avoid the select override I guess.

Also, please let us know which Rails version are you using 😄

Rails 3.2.8

Joins doesn't have the same problem. That said, I need an outer join.

It seems to be the combination of .includes.group.having.limit that breaks it. If I change includes to joins, remove the having clause, or the limit clause the query isn't broken.

So to confirm, without removing anything, but changing includes to joins, it works? Or just if you remove having/limit?

Calling includes and using the "included" association with anything would force Rails to use joins, but since you're trying to eager load with includes, the select clause is overriden so that Rails can build the models properly. That's what I'd expect to happen at least.

If joins works, and you really need to eager load, you can use preload instead of includes + the working joins, which will enforce the eager loading with a separate query.

Yes, changing the includes to joins without changing anything else fixes the select clause problem.

Removing having or limit also fixes the select clause problem.

It's only when I use all of them in combination that the select clause is replaced with a bad one. In my first example, the limit query will fail with a sql error. Running it without limit, or running a count is fine. It fails because something is overwriting the select clause, causing only one column to be selected (distinct things.id)

I was using includes before because I need an outer join. I can, however, manually specify it with joins to workaround the issue.

Thanks for clarifying the issue, I believe it's easier to understand now what might be wrong.

Just as a side note, remember that includes is not meant to be used only for doing an outer join, it's an eager loading strategy, so if you don't need the related records, you might be generating more objects than necessary - but if you do, that's just fine.

In my specific case eager loading is a nice bonus, but the response time is low enough without that it's a viable workaround.

Sorry I didn't mean you can't use eager loading, it's just that you can use joins and eager load in a separate query with preload instead of using includes, I believe it should work just fine (I did something similar a week ago).

We will check the issue whenever possible, thanks.

Contributor

parndt commented Nov 26, 2012

Is this perhaps related to issues like #5990?

Member

neerajdotname commented Jun 23, 2013

@wb-lifebooker can you create an executable test case following instructions mentioned at section 1.2 .

http://edgeguides.rubyonrails.org/contributing_to_ruby_on_rails.html#create-a-self-contained-gist-for-active-record-issues

Member

senny commented Dec 2, 2013

@wb-lifebooker it's been 5 months, if you still experience this problem please post the test-case as requested by @neerajdotname . If the problem was solved or is no longer relevant please report back so we can close the issue.

Member

senny commented Jan 21, 2014

I'm closing this as we didn't receive any more feedback. If this is still an issue feel free to report back so we can reopen.

@senny senny closed this Jan 21, 2014

yuthura commented Jul 14, 2016 edited

I'm not sure if I should mention this here or if I should create a new issue, but I seem to have this (or a very similar) problem. When using mysql2 and combining limit, having and pluck, somewhere Rails seems to first want to fetch SELECT DISTINCT id FROM ... instead of the actual pluck (which breaks the having conditions). When I remove the limit, it works as I expect. Don't know if this is a Rails issue, MySQL adapter issue (within Rails) or MySQL driver issue (within the mysql2 gem), but I wrote a simple failing test to prove my scenario:

https://gist.github.com/yuthura/ac8e738af75db22918972e60248d9a5d

Problem does not seem to occur when using sqlite3, but I don't know if that's because the having condition is not a problem in sqlite, or because the "prefetch" of distinct ID's is not performed with sqlite3.

Worked around the problem by using connection.select_all("...") with a custom query for now, but still would like to see this fixed (or at the very least justified with an explanation). 😄

Member

senny commented Jul 25, 2016

@kamipo Any thoughts on the findings of @yuthura ?

@senny senny removed the needs feedback label Jul 25, 2016

@senny senny reopened this Jul 25, 2016

@kamipo kamipo added a commit to kamipo/rails that referenced this issue Jul 30, 2016

@kamipo kamipo Fix `pluck` with `limit` and `having` query for MySQL
MySQL have the limitation of columns that can be used in having clause.
Therefore It should not replace the select list when having clause exists.

Fixes #8103.
89ef2e9
Member

kamipo commented Jul 30, 2016

Looks like that MySQL have the limitation of columns that can be used in having clause.
#25997 solves this issue.

@kamipo kamipo added a commit to kamipo/rails that referenced this issue Aug 6, 2016

@kamipo kamipo Fix `pluck` with `limit` and `having` query for MySQL
MySQL have the limitation of columns that can be used in having clause.
Therefore It should not replace the select list when having clause exists.

Fixes #8103.
784fd0d
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment