I filed a bug report the other day and the problem turned out to be with my setup and not Magento so this time I thought I better post this to the forum first and see if anyone else has the same problem…
To create a new product I go to:
Catalog -> Manage Products -> Add Product
When I browse the store and select the appropriate category then I see the product. It has a URL in the form:
Selecting the product takes me to this URL and the product page is displayed as expected.
I enter a SEF URL Identifier and this is stored as part of the product record but there are no records added to the core_url_rewrite database table.
I then create a second product by ”duplicating” the first.
For the duplicate, I change the product name, the SKU and the SEF URL and save it. Two records appear in the core_url_rewrite database table.
The first record in the core_url_rewrite table has an id_path of “product/141”, a request_path of “product-five.html” and a target path of “catalog/product/view/id/141”
The second record in the core_url_rewrite table has an id_path of “product/141/4”, a request_path of “/category-sef-url/product-five.html” and a target path of “catalog/product/view/id/141/category/4”
When I browse the store and select the appropriate category then I see the new duplicated product. It has a URL in the form:
Selecting the product takes me to this URL but instead of displaying the product page I get the “Whoops, our bad… The page you requested was not found...” page.
If I manually browse to http://domain.com/product-four.html (the original product) then I get the “Whoops, our bad… The page you requested was not found...” page. This is because Magento did not create any records in the core_url_rewrite table for this product.
If I manually browse to http://domain.com/product-five.html (the duplicate product) then I get the product page for that product.
So, there appears to be three issues:
 Creating a product does not create any records in the core_url_rewrite database table but duplicating a product does create records in the core_url_rewrite database table.
 When I browse to the appropriate category, I see both products however I can only select the “created” products and can not select the “duplicate” products. This is because the created products do not have core_url_rewrite records and I therefore get the none-SEF URL however the duplicate products have SEF URLs and as I am browsing to them from within a category I get the “Whoops, our bad… The page you requested was not found...” page.
If I clear the core_url_rewrite table then the catalogue uses the none-SEF-URL for both products and I can browse to the product page for both products from within a category.
 As noted in point , when browsing the catalogue and selecting a link to a SEF URL that contains a category (e.g. http://domain.com//category-sef-url/product-five.html) this form of URL does not work and I get the “Whoops, our bad… The page you requested was not found...”.
Sorry this is so long but I wanted to make sure I thoroughly documented this as best I could.