mplayer fails miserably when called from at because STDIN is not defined/opened.
This causes the open for the audio file to assign fd/0 and mplayer ends up
trying to read keyboard commands from this input.

All at jobs run with no stdin defined - this is the way it has always been.
This should be made clearer in the documentation - I will make it so in the
next release.
In fact, when you run an at job, the shell is executing stdin, so when the
commands execute, stdin is an invalid file descriptor, as shown by the output
of the following at job:
# echo 'ls -l /proc/$$/fd' | at now
mails the output:
total 4
lr-x------ 1 jvdias jvdias 64 Oct 10 09:20 0 -> /tmp/sh-thd-1128930366 (deleted)
l-wx------ 1 jvdias jvdias 64 Oct 10 09:20 1 -> /var/spool/at/spool/a00042011f1b60
l-wx------ 1 jvdias jvdias 64 Oct 10 09:20 2 -> /var/spool/at/spool/a00042011f1b60
So stdin comes from the shell, which directs it to the invalid (deleted)
temporary file, reinforcing the fact that stdin cannot be used by this job.
I don't have any 'mplayer' program on my FC-4 or FC-5 system - what
package does it come from, and what is the command you are trying to
run in an at job ?
This program should not be trying to read an invalid stdin, nor can I
understand why it would attempt to open an invalid fd 0 as an audio file.
Perhaps a workaround would be to run the mplayer command with
'command < /dev/null' .

Just documenting a problem does not make it go away ;-)
Looking at your `ls` output I further investigated the problem and found that
any program (including mplayer) started from `at` gets the shell script on
STDIN. While I cannot imagine any valid use for this behaviour it is causing
very strange problems when a program tries to read something from STDIN. Not
only that this program is reading unexpected shell script, it also causes
execution to skip some characters. It is like every line executed via `at` has a
here-document (<<) appended. Try for example
echo -e "cat\nls -l" | at now
Is this really what you would expect? What a user would expect?
I would strongly suggest that at should redirect STDIN from /dev/null.

Yes, this would appear to be an issue with the way all versions of at(1) have
executed at jobs, by directing the shell's stdin to the at job spool file.
I've investigated all current open source versions of at, including those
used by openbsd, open solaris, and mac osx, and they all do it this way.
This demonstrates the default behaviour of the shell when reading commands
from stdin :
$ bash << end
> cat
> ls -l
> end
ls -l
$ bash << end
> cat
> echo 'I RAN!'
> end
echo 'I RAN!'
Commands read from a "here document" or on stdin, are run with the same
stdin as the shell, so if a command reads stdin, commands are removed and
are not executed.
Indeed, whenever bash executes commands from stdin (as with a here document), or
with 'bash < afile', the file becomes the stdin for all commands; this is no
different to when bash runs with stdin directed to a terminal device ( the
standard interactive bash session ) .
I'm currently working on a rewrite of atd, to be integrated with crond, and
will investigate providing options to make the new crond not direct the
shell's stdin to at job spool file - but this would be a major departure
from the way all at versions have worked, and should not be the default.
There may well be at(1) scripts out there which depend on sending input to
commands in the lines following the command, ie:
'read_input_line
line of input
more commands ...
'
So maybe there should be an 'at -F' option to tell atd to give the at job
as a shell '-f' script file option and not direct the shell's stdin to it.
I will consider this as an enhancement for the new crond/at version.

Thanks for your efforts. I had not imagines that problem would be so widespread
and yet go unnoticed for so long. I have asked several people what they would
expect from the 'echo -e "cat\nls -l" | at now' job and noone got it right.

This report targets the FC3 or FC4 products, which have now been EOL'd.
Could you please check that it still applies to a current Fedora release, and
either update the target product or close it ?
Thanks.