System.LimitException: Too many query rows: 50001 even though SOQL has LIMIT 10,000 specified

Question

I realize that this is a very basic exception but I don't understand why it is being thrown although I am limiting the number of results to 10,000. My question is similar to this post but nothing there helps. I also looked at this. Here is my query/code:

Map<Id, LoginGeo> location_dump = new Map<Id, LoginGeo>();
                for(LoginGeo lg : [SELECT Id, City, Country, CountryIso, latitude, longitude, PostalCode, LoginTime, Subdivision 
                                    FROM LoginGeo 
                                    WHERE Id IN :geo_ids 
                                    ORDER BY LoginTime DESC LIMIT 10000])
                    location_dump.put(lg.Id, lg);

where geo_ids is a Set<Id> containing ids of type LoginGeo.

Why is it pulling 50,000+ records?

I get System.LimitException: Too many query rows: 50001 exception for that query.

Might be helpful:

1. This will work only on API version 35.0 and above.
2. The query alone returns exactly 10000 records, like it should, in Developer Console.
3. The exception is thrown in a test method while deploying it to Production org. This works super fine in my sandbox, because the data there is limited.
4. Fortunately for me these are standard objects, so you can try it in your orgs if at all.

Answer 1

Be aware that the 50k limit is an overall per-transaction limit, and not a per-query limit. Do you have other code in your test method that makes SOQL queries? Are there at least 4 other queries with LIMIT 10000 in them? If so, that would do it. That things fail only in production, where you have more data, hints that this could be the case.

Also, since you mentioned that this is a test method: if any of the queries contributing to this are in logic to set up your test you may be able to use Test.startTest and Test.stopTest to help. Code that is run between these two methods gets a new set of limits, which means that in a scenario where exactly half your work is setup/teardown you can query 100k records instead.

For the sake of debugging note that you can check how many query rows have been used via Limits.getQueryRows();.

Answer 2

To get the number of remaining query rows you would do:

Integer remainingRows = Limits.getLimitQueryRows() - Limits.getQueryRows();

So you could do a dynamic query limit with that if you want to get as many rows as possible up to 10k.

Integer upperBound = Math.min(remainingRows, 10000);
List<LoginGeo> logins = Database.query('SELECT Id FROM LoginGeo LIMIT ' + upperBound');