If your second store views are purely for language, and nothing else. You would almost definitely fair better in terms of scalability and performance if you extended the core functionality for translation and assigned different CSV's based on either URL or IP.
That way, you can avoid having dozens of redundant stores - taking up resources that only exist for a simple language translation.
The problem is a bug in the model Mage_Core_Model_Url_Rewrite_Request (Magento 1.8) and Mage_Core_Model_Url_Rewrite (earlier versions).
The section of core code in 1.8 looks like this:
// Section from Mage_Core_Model_Url_Rewrite_Request::_rewriteDb()
$fromStore = $this->_request->getQuery('___from_store');
if (!$this->_rewrite->getId() && $fromStore) {
$stores = $this->_app->getStores();
if (!empty($stores[$fromStore])) {
$store = $stores[$fromStore];
$fromStoreId = $store->getId();
} else {
return false;
}
The Bug: the value of the query parameter is the store code, (in my case de, en or fr).
The keys of the array returned by app->getStores() are the numeric store IDs.
Thats why if (!empty($stores[$fromStore])) { always fails.
Once that bug is fixed, another bug becomes apparent later in the same method (I think only in 1.8):
$targetUrl = $this->_request->getBaseUrl() . '/' . $this->_rewrite->getRequestPath();
The request objects base url always is the Magento base_url, without the store code.
Using $currentStore->getBaseUrl() instead there fixes that bug, too.
Once those two issues are fixed the language switcher works fine. Here is an extension which does exactly that for Magento 1.8 (CE): https://github.com/Vinai/VinaiKopp_StoreUrlRewrites
In Magento 1.7 the issue might be something different. I still thought I'd add this answer, just in case google brings somebody else here who is running 1.8 or newer.
Best Answer
This is a Magento 2 issue.
See: https://github.com/magento/magento2/issues/10908
What is you Magento version? It's maybe resolved in latest version 2.2.2.