Bug #10418 LOAD_FILE does not behave like in manual if file does not exist
Submitted: 6 May 2005 16:05 Modified: 15 May 2006 19:31
Reporter: Guilhem Bichot Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1.11 OS:Linux (linux)
Assigned to: Tatiana Azundris Nuernberg

[6 May 2005 16:05] Guilhem Bichot
Description:
Manual says:
"If the file doesn't exist or cannot be read because one of the preceding conditions is not satisfied, the function returns NULL."
This is half-true:
1)SLAVE> select load_file("lkjlkj");
ERROR 13 (HY000): Can't get stat of '/m/data/5/2/lkjlkj' (Errcode: 2)
I rather expected "NULL" and no error; the above is not what I call "returns NULL".
2)SLAVE> set @a=1;select @a:=load_file("lkjlkj");select @a;
Query OK, 0 rows affected (0.01 sec)

ERROR 13 (HY000): Can't get stat of '/m/data/5/2/lkjlkj' (Errcode: 2)
+------+
| @a   |
+------+
| NULL |
+------+
Ok, in this case it returned NULL.
3)SLAVE> select ifnull(load_file("lkjlkj"),"it's null");
ERROR 13 (HY000): Can't get stat of '/m/data/5/2/lkjlkj' (Errcode: 2)
I expected "it's null" to be printed.
Please fix this and add a testcase. Thanks!

How to repeat:
select load_file("lkjlkj");
should print NULL
select ifnull(load_file("lkjlkj"),"it's null");
should print "it's null"
[6 May 2005 16:06] Guilhem Bichot
When you merge the fix into 5.0, it's possible that rpl_sp.result (which uses LOAD_FILE) has to be updated; because for now it contains the error message, which will probably go away when you fix the bug.
Note that it's really a bug: some months ago it really returned NULL and no error, like said in the manual.
[6 May 2005 16:19] Guilhem Bichot
I removed dependencies on LOAD_FILE() in rpl_sp; don't bother about rpl_sp.
[27 Jun 2005 4:04] Duane Hitz
FYI, in addition, the error message will fail an insert or update altogether if the file does not exist.

For example:

@set path = '<directory path>';

insert into <table> (code,mytext)
values ('some_code',load_file(concat(@path,code,'.txt')));

Errors and returns:

ERROR 13 (HY000): Can't get stat of '<filename>' (Errcode: 2)

... failing the insert... tested update w/ same results.
[8 May 2006 2:38] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/6076
[12 May 2006 10:31] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/6295
[15 May 2006 12:37] Tatiana Azundris Nuernberg
fixed in 4.1.20, 5.0.22
[15 May 2006 19:31] Paul Dubois
Noted in 4.1.20, 5.0.22 changelogs.

<literal>LOAD_FILE()</literal> returned an error if the file
did not exist, rather than <literal>NULL</literal> as it
should according to the manual.