You’re thinking of the Bonjour sleep proxy. Normally if you tried to ssh to `mymac.local`, but your Mac was asleep, nothing would respond to the mDNS broadcast for `mymac.local`. If it had been long enough that your local mDNSResponder cache had expired, you’re out of luck.

The AirPort would take over for your Mac and respond to mDNS queries on behalf of its hostname. (I believe it would also repeat the service records.) So your lookup of `mymac.local` would resolve to your Mac’s last IP address, and the AirPort would send the WoL packet to your Mac’s MAC, hopefully in time for your TCP connection to succeed.

Wake-on-LAN works fine, but only wakes the machine from sleep. It won't boot a machine that's fully powered off.