Why does the code ‘ORDER BY rating DESC’ cause an error in my code?

In the cs50 class I was assigned to list all of the movies in 2012 and their ratings, in descending order by rating in sql. When I made this code the error

‘line 12: 2111 File size limit exceeded/usr/bin/sqlite3 -header -separator ‘ | ‘ “$@”‘

and I would like to know why.

SELECT title, rating FROM movies, ratings
WHERE year = 2012 AND movie_id IN (SELECT id FROM movies
WHERE year = 2012)
ORDER BY rating DESC

The error does go away after I remove ‘ORDER BY rating DESC’, but I cannot do that because check50 wouldn’t accept it.

Answer

This is an accidental cross-join, or Cartesian Product, which will cause the result set to be large: one row for each (movie,rating) combination instead of one row for each rating with the title pulled from the related movie.

The large result size is apparently causing a resource exhaustion error when it’s sorted. When you don’t have an ORDER BY the results don’t have to be buffered and sorted, so it requires less resources.

Should be something like

SELECT m.title, r.rating 
FROM movies m
JOIN ratings r
  ON m.id = r.movie_id
WHERE r.year = 2012 
AND m.year = 2012
ORDER BY r.rating DESC