Bug #15600 SELECT on ill FUNCTION gets warning instead of error
Submitted: 8 Dec 2005 19:06 Modified: 9 Dec 2005 11:27
Reporter: Matthias Leich Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S2 (Serious)
Version:5.0 OS:
Assigned to: CPU Architecture:Any

[8 Dec 2005 19:06] Matthias Leich
Description:
Please have a look on the content of my protocol:

CREATE TABLE t1 (f1 SMALLINT, PRIMARY KEY(f1));
INSERT INTO t1 SET f1 = 1;

INSERT INTO t2 SET f1 = 1;
ERROR 42S02: Table 'test.t2' doesn't exist
    <-- absolute correct
CREATE FUNCTION func0() RETURNS BIGINT
BEGIN
INSERT INTO t2 SET f1 = 1;
RETURN 1;
END//
SET @my_val = -1;
SELECT func0() INTO @my_val FROM t_select2;
ERROR 42S02: Table 'test.t_select2' doesn't exist
    <-- absolute correct
SELECT @my_val;
@my_val
-1
    <-- also correct, because the statement with the
          assignment of the value 1 failed

DROP FUNCTION func0;

INSERT INTO t1 SET f1 = 1;
ERROR 23000: Duplicate entry '1' for key 1
    <-- absolute correct
CREATE FUNCTION func0() RETURNS BIGINT
BEGIN
INSERT INTO t1 SET f1 = 1;
RETURN 1;
END//
SET @my_val = -1;
SELECT func0() INTO @my_val;
Warnings:
Error   1062    Duplicate entry '1' for key 1
   <--  !!! I expect to get error 1062 here and no warning !!!
I expect to have -1 within the variable
SELECT @my_val;
@my_val
NULL
   <--  !!! I expect to have -1 within the variable, because
               the statement with the assignment has to fail.
               Even if we would invent an ugly SQL mode
               where such a SELECT with function is successful,
               we would expect a "1" instead of NULL.

This bug is similar to 
Bug#12383 SELECT of nonexisting function in proc called from other proc returns NULL .
But there are some maybe important differences
1. My function is used within a simple SELECT and not within a procedure.
2. There is no handler involved.

My environment:
   - Intel PC with Linux(SuSE 9.3)
   - MySQL compiled from source
        Version 5.0 ChangeSet@1.1975.3.1, 2005-12-06

How to repeat:
Please use my attached testscript ml003.test
copy it to mysql-test/t
echo "Dummy" > r/ml003.result   # Produce a dummy file with 
                                                 # expected results
./mysql-test-run ml003
[8 Dec 2005 19:06] Matthias Leich
testcase

Attachment: ml003.test (application/test, text), 815 bytes.

[9 Dec 2005 11:27] Sergei Golubchik
Same cause as in the BUG#15301