MySQL Bugs: #99310: When a function is called from inside of a procedure it returns different result

Bug #99310	When a function is called from inside of a procedure it returns different result
Submitted:	20 Apr 2020 14:46	Modified:	5 Aug 2021 13:33
Reporter:	Valentin Gjorgjioski	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.7.15	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	FUNCTION, implicit cast, PROCEDURE, where

Description:
Something is strange with the implicit casting when function is called within a procedure. The MySQL Server behaves differently when the function is called standalone and the function is called from procedure. This makes process of debugging much harder and can lead to unexpected results. 

Although the code bellow is not very logical to create in everyday environment, the behaviour of MySQL Server is strange. I was not able to find it documented. Closest bug-report I found was this one #51705, but that didn't help to me to explain this behaviour. Please look at the code bellow. 

How to repeat:
create database test;
drop table test.test;
create table test.test(a varchar(45) PRIMARY KEY, b int);
insert into test.test values('test',1);
insert into test.test values(2,2);

DELIMITER $$
drop function if exists `test`.`test_function`
$$
CREATE FUNCTION `test`.`test_function`(`in_a` INT) RETURNS int(11)
BEGIN

DECLARE b INT;

SELECT	`a`
FROM	`test`.`test`
WHERE	`a` = in_a INTO b;

RETURN b;

END

$$
drop procedure if exists `test`.`test_procedure`
$$
CREATE  PROCEDURE `test`.`test_procedure`(IN `a` INT)
BEGIN
	declare b int default null;
	set b = test.test_function(a);
END
$$
DELIMITER ;

call `test`.`test_procedure`(2);
select `test`.`test_function`(2);

I would like to add the error here, maybe will be helpful for further search

Error Code: 1292. Truncated incorrect DOUBLE value: 'test'

Hi Mr. Gjorgjioski,

Thank you for your bug report.

First of all, you are using a very old release of our 5.7 version. Please, try our latest release 5.7.29 and let us know if you are still seeing the same problem.

Next, if you wish to store a value into a variable, use the following syntax:

SELECT	`a` INTO b  FROM	`test`.`test` WHERE	`a` = in_a;

Next, you have several more errors that you should correct in your SQL. First of all, you can not insert any integer into VARCHAR column, not even the value of 2. Next, you can not store a string, like 'a' into an integer variable.

Please, try correcting all these errors, with 5.7.29, and then let us know what you get.

Hi Sinisa,

1. Yes, same behaviour on 5.7.29. 

2. I've changed the syntax of INTO, still getting the same results, although documentation states both are valid/working: https://dev.mysql.com/doc/refman/5.7/en/select-into.html

3. I don't understand what you mean when: 
3.1. you say you couldn't insert integer into varchar. Mysql doesn't complain over this. Even if I make them '2' when inserting, I am still getting the same behaviour. 
3.2. You can not store a string, like 'a' into an integer variable - Where am I doing this? I am storing the value of column  `a` from table `test`. 

Have you tried the code I've provided at all? Have you take some time to see the error message and give a thought over it? 

There are some intentional mistakes in my code, but this is the whole point to show that MySQL handles them differently - and maybe some bug is laying down there the will get eventually now uncovered. 

Btw, I would expect MySQL server to correct if someone is not correct in SQL statements and not anyone on this board. If something is not correct and MySQL server does not give an error - then it is also a problem, I would say.

Hi Mr. Gjorgjioski,

I have removed your intentional errors in the SQL code , added a return from procedure and it all run without any problems:

b
2
`test`.`test_function`('2')
2

MySQL was designed from the start with non-strict type checking. There are plans to make MySQL strong type checking database. However, this change would break millions of applications.

Hence, this is not a bug, but if you wish, I can make it a feature request, which (as I mentioned) will take lots of time to change. Or I can make it a documentation bug. The choice is yours.

I would go to document this behaviour, I don't see this to be feature reuquest. 

Furthermore, I don't understand why the error is like this: 

Error Code: 1292. Truncated incorrect DOUBLE value: 'test'
 

Where does this DOUBLE is coming from if everything defined as Integer or String - it is hardly misleading.

Furthermore, obviously the error occurs on this line: 
SELECT	`a` INTO b  FROM	`test`.`test` WHERE	`a` = in_a;

when in_a is an int. Then the column `a` gets converted into a INT?! (or DOUBLE?!) and this is where 'test' string makes the troubles. 

But this only a case when it is called from a procedure, and no problem when called as a function. So, I am wondering how (and where) this implicit type casting is being done? If I understand this better, then I will understand better the error and we can document the behaviour as it should be. 

I've made some changes in the script - so I am sending again now, which is leading also to interesting results: 

drop database test;
create database test;
drop table test.test;
create table test.test(a varchar(45) PRIMARY KEY, b int);
insert into test.test values('test',1);
insert into test.test values('2',2);

DELIMITER $$
drop function if exists `test`.`test_function`
$$
CREATE FUNCTION `test`.`test_function`(`in_a` INT) RETURNS int(11)
BEGIN

DECLARE b INT;

SELECT	`a` INTO b  FROM	`test`.`test` WHERE	`a` = in_a;

RETURN b;

END

$$
drop procedure if exists `test`.`test_procedure`
$$
CREATE  PROCEDURE `test`.`test_procedure`(IN `a` VARCHAR(45))
BEGIN
	declare b int default null;
	set b = test.test_function(a);
END
$$
DELIMITER ;

call `test`.`test_procedure`('2'); -- Error Code: 1292. Truncated incorrect DOUBLE value: 'test'
call `test`.`test_procedure`('test'); -- Error Code: 1366. Incorrect integer value: 'test' for column 'in_a' at row 1

select `test`.`test_function`('2'); -- 2
select `test`.`test_function`('test'); -- OK 

For me:

Error Code: 1292 and OK are strange. 

I would expect Error Code 1292 to be more informative (or to work, I have no idea why would mysql want to CAST 'test' to DOUBLE). 

I would expect function call with STRING to fail as it fails when called from procedure, or I would expect it not to fail inside of the procedure. Otherwise, I will call this inconsistent behaviour.

Hi Mr. Gjorgjioski,

This is now a verified documentation bug regarding strict type checking in MySQL server.

Regarding the appearance of the type double, this is how expressions are resolved. When you have to resolve between a string and integer, you convert both to double. This is explained in our Reference Manual, chapter on expressions.

Verified as a documentation bug.

Can you explain why this conversion occurs only in case when function is called from a procedure and not every other case - when call by query itself of function? 

This query actually works fine: 
SELECT	`a`  FROM	`test`.`test` WHERE	`a` = 2;

Why it doesn't convert column `a` and 2 to double? (but rather I guess it converts the 2 to string instead).  

Is it something specific for "procedure call" ?

Hi Mr. Gjorgjioski,

Your last question is also good, so I will be adding that question to be properly answered and documented within the same bug.

Thank you for that as well.

Posted by developer:
 
The information about type conversion to which Sinisa refers is at this page:
https://dev.mysql.com/doc/refman/5.7/en/type-conversion.html

Specifically, it's this part:
In all other cases, the arguments are compared as floating-point (real) numbers. For example, a comparison of string and numeric operands takes places as a comparison of floating-point numbers.

"floating-point" there is the DOUBLE you're seeing in diagnostic messages.

I see @Paul. However, can you explain why it holds when the function is called within procedure, but this doesn't hold when it is called as a standalone function?

Hi Mr. Gjorjioski,

It seems that it is because of how stored procedure passes VARCHAR value into the function which expects and INT value. However, this remains to be documented.

Hi Sinisa, I was thinking about the same, but then I doubted it. 

If this was the case then we have the following 4 possibilities: 
1. varchar sent as varchar
2. varchar sent as int
3. int sent as varchar
4. int sent as int

1 and 4 are same when it is called from outside. But 2 and 3 are also are the same as it is called from outside because the error is thrown in the function. Except - if the procedure calls "unpacks" the function and put in its body where it executes and therefore changes it's behaviour. In any case, for me very strange and inconsistent behaviour. 

For consistency I would like to see either function to throw an error, or when called from procedure to work. 

If we go even deeper, when function is called as standalone, the query must execute the where condition and must find whether 2 as int equals test as string. I have no idea - why this does not result in error - if 'test' should be converted to double to see whether these two are equal as Paul was suggesting in his post. 

Valentin

Hi Valentin,

Most important question is why does not the query returns an error. That is because MySQL has been designed like that since the very start. Returning the error is the right thing to do, but then who will go and change millions of applications that rely on that behaviour ???

It yet has to be decided on when will that change.