Woes_0: Retrieving the CORRECT File Name in Drupal

I was working on a module that uses a query with a few joins to select an appropriate node and then obtain the name of a file attached to it. The file name is then built into a link. Occasionally, clicking on such a link would result in a 404 error. I detest intermittent errors as much as any developer or support tech. It seemed that the common factor with this one was that the files in question had suffixed names, _0, _1 and so on. And therein lies the answer!

Where do the suffixes come from, and why?

When a file is uploaded to attach to a Drupal node, the function file_save_upload() is called. This function does a number of validation checks, one of which is determining whether the file name already exists. If so, the default action is that the file name, such as myfile.jpg receives a suffix, and becomes myfile_0.jpg. If that file name already exists, the new file is instead named myfile_1.jpg, and so on.

So, why would that lead to 404 errors?

Information about the upload is stored in a file object, $files, which is subsequently stored in the files database table. One of the columns in this table is filename, and it from this column that I was retrieving the file name.

files.filename contains the original, unsuffixed file name

The modified file name is stored in files.filepath, along with the path from the Drupal root. So, when I changed the retrieval and assignment from

SELECT files.filename ...
$filename = file_directory_path() . '/' . $db_fileobj->filename;

to the corrected

SELECT files.filepath ...
$filename = $db_fileobj->filepath;

all was well!