In such a world, rotating a pixel sprite by 45 degrees should just look like there being multiple invisible “grids” to the world, each rendered at infinite resolution and downsampled to your display, where the sprite isn’t rotating per se, but rather the pixel grid “layer” the sprite is on (i.e. the hi-res texture the sprite layer is being rendered onto for compositing) is itself rotating.
Of course, if you’re going to use things like rotation/scaling, sub-pixel particle effects, 3D, etc. then you then either have to embrace it in your game setting’s art design everywhere so particular uses of it don’t stick out; or you have to acknowledge that any uses of it are basically “non-Euclidean geometry” from the viewpoint of in-setting observers, and thus slide your game’s mood toward something approaching either science-fantasy (e.g. Fez) or horror (has anyone done a Flatland Cthulhu game yet?)
Also note that “multiple distinct pixel grids” isn’t even unprecedented in retro games, either; there were plenty of consoles where you got one resolution for your tile map and a different (usually higher) resolution for your sprites; where sprites could sit on top of tiles in non-aligned positions. A few consoles and arcade cabinets even combined pixels with vectors!
(Although I would consider a low-res limited-palette game which uses transparency, lighting, rotation, scaling and other transforms as retro since that was already possible decades ago.)
I (and many others) think mismatched pixel sizes look bad, but that there's nothing inherently wrong with those other things.
I surveyed the indie-game landscape a couple of years back, specifically looking for games in your first category, but struggled to find any recent examples. 2D transforms, alpha-blending and lighting effects seem to be the standard now.
Loop Hero is strict about its pixel art and uses a fixed 16-colour palette that is reminiscent of the Commodore 64.
Baba Is You has visuals that you could basically render on a ZX Spectrum.
And in addition to doing pixel art well, these are all pretty great games.
In one category it’s about retro authenticity and these games are defined by the fact that their final buffer is the same low resolution as their art. They also often feature limited palettes, especially ones that were actually used on retro consoles.
There’s also a second category of pixel art, where the style is more modern. The art is still low res, but the final screen buffer is high res. This is where you’re more likely to see stuff like smooth rotating sprites and lighting effects.
You may have a preference for one style or the other, but neither style is bad or “wrong”. Most people who use the modern style are doing it on purpose. There are fantastic looking games in both categories.