Different contact points
So, I am not entirely sure what other answers you have gotten, but I will say that in my own case, when I was trying to learn about friction on wheels, one of the most important ideas was that this phrase “the contact point” hides a lot of dynamics that then become confusing.
When a wheel is rolling, the contact points of the ground and the wheel are constantly changing. When we are rolling without slipping, the new contact point is lowered purely vertically onto the ground, as it makes contact it pushes the ground backwards without exceeding the maximum force $\mu_\text{s}F_\text{N}$ allowed by static friction before it transitions to dynamic friction, and then it lifts straight-vertically off again.
Understanding the words of this explanation, doesn't quite do it justice. It is helpful to sit on an office chair with rollers, put your foot vertically down on the ground, confirm that without sliding your foot you can exert forces on the ground that push the chair in some way or another, then take your foot vertically off the ground. You can then alternate feet to “creep the chair around.” In other words these are normal everyday unremarkable things, but we sometimes accidentally mystify them when we start writing equations. (And then someone goes and reverses the forces while keeping the rest of the system pointed forwards and we call it “the moonwalk.”)
Now when you do this the wheels on the office chair roll, of course they do, even though they do not provide the motive force. Why? Because otherwise the wheel would be stuck and it would slide and that friction would push it. So the wheels of the office chair are kind of just doing the easiest thing that they can, they are just minimizing energy for themselves in some way.
What makes a wheel slightly special, then, is that it's like putting your two feet together one in front of the other, at an angle so that when the one is flat on the ground the next is up by an angle $\theta$. This is what I mean by “the contact point is changing,” literally the foot that you put down changes as you try to push the ground until you can swap to the next foot.
Original questions
the contact point will experience no net [horizontal] acceleration, which means that the contact point will remain stationary with respect to the ground
Correct, the first foot does not move horizontally, rather it has to be picked up off the ground as the next foot takes its place.
Wouldn't the torque induced by the static friction exactly cancel the torque caused by the leftward force?
So, “the force caused by the torque” is something you defined as a force acting on the ground. Remember there are three steps here: engine torques the wheel (≈ my leg pushes my foot), wheel pushes the ground (≈ my foot pushes the ground), ground pushes the wheel (or my foot) statically because the force is less than the limit for static friction. Now, the last two of these are a Newton’s 3rd-law force pair, equal and opposite. But, they are forces on different objects! With one of them the floor torques my foot, with the other I torque the planet about its center. And those torques are not equal (because the effort arm on the planet is so much larger than the size of my ankle) but nobody cares (the Earth is so massive that I'm not going to spin it anyway this way, even with the extra leverage).
But what you are instead picking up is, a distinction between the first and the third, which do both affect the same thing, the wheel. And then the answer is easy, it's that indeed a little bit of the torque from the engine does not become a force exerted on the road. It instead goes into the angular momentum of the wheel. Or if you prefer it all goes into angular momentum of the wheel, but then the ground really doesn't want the wheel to just be spinning freely and so it sucks out almost all of this angular momentum at the cost of having to absorb some force, but that's less than the maximum $\mu_\text{s}F_\text{N}$ so it succeeds. But it does not suck out all of the angular momentum, it leaves just enough so that the wheel can have this surface that drops perfectly vertically onto it, which requires the wheel to be rotating forwards a little bit.
Let's say that ... the wheel is no longer accelerating, so there is no external torque applied to the wheel anymore... is there still a static friction in this case?
In the ideal case no, the wheel would just maintain its angular momentum and that would be that. In practice there is always “rolling resistance,” the bearings of the wheels aren't perfectly frictionless and are sapping some angular momentum from the wheel, the wheel is full of compressed air so that you don't feel every single pebble underneath you and that means that the wheel has to be constantly deforming which is an energy loss (touch a wheel after driving, they get super hot from this!), etc. But another way to ask this question is, can you roll without slipping on a frictionless surface, a perfect lake of ice or so. And sure, throw a rock behind you so that you start moving forward, and then if you spin up your wheels just a little bit, they will perfectly match the velocity of the ice underneath you, and someone looking from the shore would say that it looks like you're driving on the ice. Of course you know that you're on a frictionless surface because you could dump as much or as little angular momentum into your wheels and nothing would change. But if you put just the right amount in, yeah you're randomly rolling without slipping.
Follow-ups
How can you explain that the torque on the axle exceeds the torque exerted by the static friction causing the wheel to spin if the forces are supposed to be equal?
Well first, as the example of the Earth shows, usually, equal forces just don't come with equal torques. A torque is a force times a perpendicular length, you have to conspire to put the forces at exactly the same distance from the center of rotation to get them to have the same torque, and even then, torque is a vector and you have to put some thought into the exact angles used to cancel them out. A good example might be the hinges on a door. A door is held up by two hinges, they are off center, they must be in torque balance because the door isn't rotating into or out of the frame. But if this came from equal and opposite forces, there would actually be no force balancing out gravity and the door would be in freefall (or massless). So to get equal torques on the door actually requires unequal forces on the hinges, which requires them to have different vectors of action, so the top hinge is trying to rip out the screws from the doorframe, the bottom hinge is not.
But I addressed the main confusion above, the reason that you think the forces are equal and opposite is that you are looking at a third-law force-pair, these are only relevant to calculating an equal and opposite torque because they imply that internal forces on a rigid body never create torque.
How can you explain that the contact point remains at 0 velocity w.r.t. the ground if there is supposed to be a net rightward force on the contact point from the ground?
Because the friction is static. Literally this is the most normal thing that has ever happened to you. This happens to you all day, every day, you have been blinded by equations into forgetting the most common stuff. Find a good cylindrical coffee mug, ideally without a handle but if it has a handle just don't use it, cup your hand around it, and pick it up vertically. If it stays in your hand, that is purely because you gave it enough normal force so that you could generate the friction that keeps it in your hand.
Forces are dispositions to accelerate, so when you put a force on something, you dispose it to move. Now whether it actually accelerates depends on all of the other forces acting on it, the net disposition of the object. It is absolutely routine for there to be forces on something and for it to not move. This is happening to your butt in the chair as we speak, gravity is pulling you down, the chair is pushing you up, lots of forces on you but you are not moving. And it is normal for this to also happen with shear forces that lie tangent to the plane, rather than normal to it, and we call these forces friction.
If you want to understand what's microscopically going on, it's that when these two surfaces are in motion, the atoms between them kind of grab at each other (hydrogen bonds, van der Waals forces, etc.) and then stretch out to try to hold on but macroscopic motion is just so much bigger than atomic distances that they just get torn apart and vibrate with the energy that they had when stretched, dissipating this energy as heat. (You literally cannot see atomic distances, visible light is bigger than that.) When two surfaces are allowed to come to rest relative to each other, the molecules have more time to grab on to each other, and when these molecules pull the surface together the neighboring molecules are better able to see and grab onto each other and so on, they reinforce each other. The more surface area, the more atoms see each other of course; but also the more pressure pushes the two surfaces together, the closer they approach and the more the atoms see each other and grip each other. So you multiply pressure times area and you just get the normal force. These little intermolecular bonds then need to be broken for the surfaces to move more than those atomic distances (that you literally cannot see) relative to each other. And this generates a maximum force that the surfaces can sustain between each other without (perceptibly) moving.
But understanding the microscopic basis is not really needed, the fact is whenever you pick something up by its sides, like a tissue box or a pencil, you use this aspect of static friction. Don't mystify it, it's really normal, it's why your desk doesn't have to be concave to hold objects on it and it's why the screws in your desk hold it together and all that good stuff. Things that are at rest relative to each other can just take a certain shear force without moving. If they weren't, they would be liquids, which can't sustain shear without moving.
