| Bug #110765 | Function returns incorrect value inside a session | ||
|---|---|---|---|
| Submitted: | 21 Apr 2023 18:26 | Modified: | 2 Jul 19:20 |
| Reporter: | Eric Vanier | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server: Stored Routines | Severity: | S3 (Non-critical) |
| Version: | 8.0.32 | OS: | Linux (EC2) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | RDS, Stored Functions | ||
[24 Apr 2023 12:25]
MySQL Verification Team
Hi Mr. Vanier, Thank you for your bug report. First of all, your category is wrongly chosen. You are not using UDF's , but stored routines, more precisely, stored functions. Much more important then that, we can not repeat the behaviour that you are reporting. We used some of our own tables , but we get the consistent results. In order that you get consistent results, you need that several factors are met, of which the most important are the following ones. * There are no DML's running in-between read-only statements * Both joins return only one result row We also recommend that you use DECLARE and store the result of the query into it and return the value of the local variable. Can't repeat.
[24 Apr 2023 12:30]
MySQL Verification Team
Hi, We would like to note that there is one more problem, BIG problem with your queries. You have limited the result set to one row. However, according to SQL standard, if it is not specified explicitly, result sets are not ordered by any attribute.. So for example, if result set has two rows, like: 2 1000 in one run you can get 2 as a first row and in another run you can get 1000 as the first row. That is how SQL works and that is 100 % according to all SQL standards.
[2 Jul 19:20]
Eric Vanier
I believe this is the same behavior we're discussing, and it looks like a fix is coming with version 8.0.38. https://bugs.mysql.com/bug.php?id=114235
[3 Jul 9:20]
MySQL Verification Team
Hi Mr. Vanier, We can not confirm nor deny your statements, for the simple reason that you never provided us with a fully repeatable test case.
Description: Team, We have an issue with a function which is returning incorrect results inside a same session. It returns good value on the first run ,but if we wait in the same session for sometime and call the same query ,we get wrong data. details in the how to repeat step. Any suggestions will be helpful . Here is the create function statement . 1. First_Name CREATE DEFINER=`yyy`@`%` FUNCTION `fx_profile_first_name`(idprospect_p int(11)) RETURNS varchar(55) CHARSET latin1 begin return (select value from table1 a inner join table2 b on a.attribute_id=b.id where a.contact_id=idprospect_p and b.tag='first_name' limit 1 ); end; 2. Last_Name CREATE DEFINER=`yyy`@`%` FUNCTION `fx_profile_last_name`(idprospect_p int(11)) RETURNS varchar(55) CHARSET utf8mb4 COLLATE utf8mb4_bin begin return (select value from table1 a inner join table2 b on a.attribute_id=b.id where a.contact_id=idprospect_p and b.tag='last_name' limit 1 ); end; How to repeat: step 1 - connect to the schema and callout the function in the SELECT step 2 - Wait for sometime (around 6 mins ) inside the same session step 3 - Again call the same SELECT ,You would see a different Value Details: mysql> use zzz Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> select fx_profile_first_name(79), fx_profile_last_name(79); +---------------------------+--------------------------+ | fx_profile_first_name(79) | fx_profile_last_name(79) | +---------------------------+--------------------------+ | John | Amun-Ra | +---------------------------+--------------------------+ 1 row in set (0.00 sec) mysql> select now(); +---------------------+ | now() | +---------------------+ | 2023-04-21 16:24:18 | +---------------------+ 1 row in set (0.00 sec) mysql> select now(); +---------------------+ | now() | +---------------------+ | 2023-04-21 16:30:29 | +---------------------+ 1 row in set (0.00 sec) mysql> select fx_profile_first_name(79), fx_profile_last_name(79); +---------------------------+--------------------------+ | fx_profile_first_name(79) | fx_profile_last_name(79) | +---------------------------+--------------------------+ | John | 1037 | +---------------------------+--------------------------+ 1 row in set (0.01 sec) mysql> exit Bye Suggested fix: