As you mentioned, the implementation of ResultSet is vendor specific, but I don't believe there's a simgle implementation that will retrieve all million rows and try to store them in memory. The ResultSet will fetch a number of rows at a time, and as the client code iterates through the rows, it will keep fetching more rows as needed.
That's why it's so important to remember that a ResultSet object is only valid while the connection to the database is still live. The ResultSet itself may not contain all the data. It's merely a proxy that communicates with the database to retrieve data as needed.