I'm wondering: what is playing Left 4 Dead in single-player mode like? Will it work with the AI? Does it quickly become tedious?
In short: it quickly becomes tedious.
The AI is only suitable for Easy or Normal single player. Beating Advanced and Expert usually comes from team tactics as opposed to FPS skill, and tactics requires human cooperation. You'll find yourself constantly losing if you're using AI on those difficulties.
The AI has some other shortcomings. They are incapable of throwing grenades, meaning your team's stock of weapons is less than that of a human team. Also, they will heal you if you're hurt, no matter the situation. If this happens to you when it's crucial that you run as opposed to stop and heal, then keep a medkit on you and pull it out to prevent the AI from attempting to heal you.
Left-4-Dead has pretty decent A.I. Better than most games. Generally, they'll stay with you, and provide decent support. They rarely get stuck somewhere in the level, and will run away when you run away. They will also almost always come to your aid if you need it.
That being said, they certainly aren't perfect, but nonetheless, I find that they're decent enough to warrant playing the game single-player.
One thing not mentioned is that the game director makes the game unpredictable. You will sort of know what is coming in certain situations, but this game is different than all other SP games in that you are never really sure exactly when something bad is about to happen.
I know you asked about SP, but if you have access to internet, this game is well worth playing coop mode. There are always griefers, but you can make friends-only games and add people to your friends list who play nice.
Versus is a whole other game.