You cannot replace the main query with the output of $wpdb. The main query is a WP_Query object. $wpdb returns a simple array or object, but not a WP_Query object.
Even if you could shove raw SQL into a query (something in the back of my head makes me think you can but I can’t swear to it) it is not advisable. Try var_dump($wp_query); and you will see that there is a lot going on besides just a SQL query. For the object to work–including the pagination that you want– all of that other stuff has to be set correctly.
You do need to use pre_get_posts and perhaps other query filters like posts_where to alter the WP_Query object, not replace it. Without knowing the details as @Milo and @ChipBennett have requested but you are hesitant to provide, it is not going to be possible to give you much more of an answer.