Perhaps this will help clarify. There are actually three modes:
Sport mode - This is activated using the rocker switch to the left of the shift lever. In Sport mode, the transmission will upshift later and hold a lower gear. The throttle will become more sensitive. The transmission will still shift automatically. The downside is more engine noise and lower fuel economy.
Manual mode - This is activated by moving the shift lever to the left while in D. You can then manually select a gear using either the shift lever or paddle shifters. Invalid gear requests will be ignored. The transmission will downshift automatically as you come to a stop. The transmission will also downshift automatically if you press the accelerator past the detent position. Otherwise, the engine will hold the selected gear and will not upshift automatically, but the engine's rev limiter will keep it from going past redline by closing the throttle.
Manual mode (temporary) - This is selected in Sport or "normal" by pulling a paddle shifter to temporarily choose a lower gear such as when engine braking is desired. "M" appears on the instrument cluster beside the shift indicator just as it does in manual mode, but unless the shift lever is in the manual mode, the vehicle will override the chose gear.
Manual and manual (temporary) mode can used in either "normal" or "sport" model.
This can get really confusing for drivers who don't fully understand each mode. My 2019 RDX took a while to figure out. It had a pushbutton shifter that had PRND and S (sequential mode) positions, paddle shifters, and four drive modes (Snow, Comfort, Sport, and Sport+) resulting in many combinations of shift behavior. Sequential ("manual") mode only became manual when you pulled a paddle shifter and would still upshift automatically near redline unlike the CX-5. Otherwise, S mode was more like Mazda's "sport" mode, but only for the transmission - the rest of the car (dampers, steering, throttle, sound) didn't change. Those parameters only changed when you chose a different drive mode (i.e. Comfort or Sport+).