Under what conditions exactly does SIGPIPE happen?

Your example is using a fifo not a pipe, so is subject to fifo(7). pipe(7) also tells:

A FIFO (short for First In First Out) has a name within the filesystem (created using mkfifo(3)), and is opened using open(2). Any process may open a FIFO, assuming the file permissions allow it. The read end is opened using the O_RDONLY flag; the write end is opened using the O_WRONLY flag. See fifo(7) for further details. Note: although FIFOs have a pathname in the filesystem, I/O on FIFOs does not involve operations on the underlying device (if there is one).

I/O on pipes and FIFOs
The only difference between pipes and FIFOs is the manner in which they are created and opened. Once these tasks have been accomplished, I/O on pipes and FIFOs has exactly the same semantics.

So now from fifo(7):

The kernel maintains exactly one pipe object for each FIFO special file that is opened by at least one process. The FIFO must be opened on both ends (reading and writing) before data can be passed. Normally, opening the FIFO blocks until the other end is opened also.

So before both ends (here meaning there is at least a reader and a writer) are opened, write blocks as per fifo(7). After both ends have been opened, and then (the) reading end(s) closed, write generates SIGPIPE as per pipe(7).

For an example of pipe usage (not fifo) look at the example section of pipe(2) : involves pipe() (no open(), since pipe() actually created the pipe pair opened), close(), read() write() and fork() (there's almost always a fork() around when using a pipe).

The simpliest way to handle SIGPIPE from your own C code if you don't want it to die when writing to a fifo, would be to call signal(SIGPIPE, SIG_IGN); and handle it by checking for errno EPIPE after each write() instead.