limit is broken

[BurntSushi]

This code

import nfldb

db = nfldb.connect()

q = nfldb.Query(db)
q.game(season_year=2012, season_type='Regular')
q.player(full_name='Tom Brady')
q.play(passing_yds__ge=40)

for p in q.sort('passing_yds').limit(10).as_plays():
    print p

produces this output:

(NE, OWN 17, Q2, 1 and 10) (9:56) (Shotgun) T.Brady pass short left to S.Vereen for 83 yards, TOUCHDOWN.

But it should be:

(NE, OWN 17, Q2, 1 and 10) (9:56) (Shotgun) T.Brady pass short left to S.Vereen for 83 yards, TOUCHDOWN.
(NE, OWN 37, Q3, 3 and 10) (10:00) (Shotgun) T.Brady pass deep middle to D.Stallworth for 63 yards, TOUCHDOWN. NE 12-Brady 18th career game with 4+ TD passes, passing Johnny Unitas for 4th most all-time.
(NE, OWN 21, Q1, 1 and 10) (9:32) (No Huddle) T.Brady pass deep left to W.Welker to BAL 20 for 59 yards (B.Pollard).
(NE, OWN 44, Q2, 3 and 5) (3:17) (Shotgun) T.Brady pass deep left to J.Edelman for 56 yards, TOUCHDOWN.
(NE, OWN 18, Q4, 1 and 10) (9:18) (No Huddle, Shotgun) T.Brady pass deep right to B.Lloyd to SF 29 for 53 yards (C.Culliver).
(NE, OPP 46, Q1, 1 and 10) (5:59) (No Huddle, Shotgun) T.Brady pass deep left to W.Welker for 46 yards, TOUCHDOWN.
(NE, OWN 33, Q1, 2 and 5) (8:57) (Shotgun) T.Brady pass deep right to R.Gronkowski to BUF 26 for 41 yards (J.Rogers). Caught at BUF 32, slanting to sideline.
(NE, OPP 44, Q3, 2 and 1) (2:09) (No Huddle, Shotgun) T.Brady pass deep right to M.Hoomanawanui to SF 3 for 41 yards (D.Whitner).

[ochawkeye]

Just a few more examples to highlight the unexpected results from this limit function.

No limit produces 8 results:

for p in q.sort('passing_yds').as_plays():
    print p
> (NE, OWN 17, Q2, 1 and 10) (9:56) (Shotgun) T.Brady pass short left to S.Vereen for 83 yards, TOUCHDOWN.
> (NE, OWN 37, Q3, 3 and 10) (10:00) (Shotgun) T.Brady pass deep middle to D.Stallworth for 63 yards, TOUCHDOWN. NE 12-Brady 18th career game with 4+ TD passes, passing Johnny Unitas for 4th most all-time.
> (NE, OWN 21, Q1, 1 and 10) (9:32) (No Huddle) T.Brady pass deep left to W.Welker to BAL 20 for 59 yards (B.Pollard).
> (NE, OWN 44, Q2, 3 and 5) (3:17) (Shotgun) T.Brady pass deep left to J.Edelman for 56 yards, TOUCHDOWN.
> (NE, OWN 18, Q4, 1 and 10) (9:18) (No Huddle, Shotgun) T.Brady pass deep right to B.Lloyd to SF 29 for 53 yards (C.Culliver).
> (NE, OPP 46, Q1, 1 and 10) (5:59) (No Huddle, Shotgun) T.Brady pass deep left to W.Welker for 46 yards, TOUCHDOWN.
> (NE, OWN 33, Q1, 2 and 5) (8:57) (Shotgun) T.Brady pass deep right to R.Gronkowski to BUF 26 for 41 yards (J.Rogers). Caught at BUF 32, slanting to sideline.
> (NE, OPP 44, Q3, 2 and 1) (2:09) (No Huddle, Shotgun) T.Brady pass deep right to M.Hoomanawanui to SF 3 for 41 yards (D.Whitner).
[Finished in 0.7s]

limit(1) produces 0 results.

for p in q.sort('passing_yds').limit(1).as_plays():
    print p
[Finished in 0.5s]

limit(10) produces 1 result:

for p in q.sort('passing_yds').limit(10).as_plays():
    print p
> (NE, OWN 17, Q2, 1 and 10) (9:56) (Shotgun) T.Brady pass short left to S.Vereen for 83 yards, TOUCHDOWN.
[Finished in 0.5s]

limit(200) produces 6 results:

for p in q.sort('passing_yds').limit(200).as_plays():
    print p
> (NE, OWN 17, Q2, 1 and 10) (9:56) (Shotgun) T.Brady pass short left to S.Vereen for 83 yards, TOUCHDOWN.
> (NE, OWN 37, Q3, 3 and 10) (10:00) (Shotgun) T.Brady pass deep middle to D.Stallworth for 63 yards, TOUCHDOWN. NE 12-Brady 18th career game with 4+ TD passes, passing Johnny Unitas for 4th most all-time.
> (NE, OWN 21, Q1, 1 and 10) (9:32) (No Huddle) T.Brady pass deep left to W.Welker to BAL 20 for 59 yards (B.Pollard).
> (NE, OWN 44, Q2, 3 and 5) (3:17) (Shotgun) T.Brady pass deep left to J.Edelman for 56 yards, TOUCHDOWN.
> (NE, OWN 18, Q4, 1 and 10) (9:18) (No Huddle, Shotgun) T.Brady pass deep right to B.Lloyd to SF 29 for 53 yards (C.Culliver).
> (NE, OPP 46, Q1, 1 and 10) (5:59) (No Huddle, Shotgun) T.Brady pass deep left to W.Welker for 46 yards, TOUCHDOWN.
[Finished in 0.6s]

[BurntSushi]

Thanks for the extra examples. This isn't going to be a simple fix, unfortunately. sort and limit can be used for performance reasons to make retrieving results quicker. The problem is that its application is across several different SQL queries inside the Query class, so applying it to each query has to be done carefully in only the correct circumstances. (The complexity results from splitting what seems like a single huge JOIN query into smaller more performant filtering queries. All of the boolean logic expressed with Query needs to translate seamlessly...)

[BurntSushi]

I think I fixed this unknowingly. If I had to guess, I think the commit is 51468a6 in nfldb/query.py.