Dunno if this counts, but BoF: Dragon Quarter. It's not exactly panned by critics, but the fanbase seems to denounce it as the harbinger of the apocalypse or something.

Dragon Quest VI is another interesting example since it seems to be considered an OK game in its own right, but most DQ fans have either not played it or consider it one of the worst entries in the series. But I love it.

Can't think of any actual trainwrecks I've played and enjoyed though