Bug #95303 RIGHT ... IN evaluating to 1 within user function, 0 outside of user function
Submitted: 8 May 16:17 Modified: 9 May 14:53
Reporter: Bob Dankert Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:8.0.15, 5.6.36, 5.6.44, 5.7.26 OS:Linux
Assigned to: CPU Architecture:x86

[8 May 16:17] Bob Dankert
Description:
I am having an issue where a statement evaluates to 1 inside a user function, but is 0 outside of the user function. The function has been simplified to:

CREATE FUNCTION `test`(`input_string` VARCHAR(1000)) RETURNS int(11) NO SQL DETERMINISTIC
BEGIN
  RETURN RIGHT(`input_string`, 1) IN (' ', ',');
END

SELECT `test`(''); // Returns 1, should be 0

Whereas if I run this statement without it being in a user function, this returns 0:

SELECT RIGHT('', 1) IN (' ', ','); // Returns 0

I've gone through the documentation and cannot come up with any reason why the behavior would differ in these two cases, executing what should be the same code on the same blank string. 

How to repeat:
CREATE FUNCTION `test`(`input_string` VARCHAR(1000)) RETURNS int(11) NO SQL DETERMINISTIC
BEGIN
  RETURN RIGHT(`input_string`, 1) IN (' ', ',');
END;

SELECT `test`(''); // Returns 1, should be 0
[9 May 5:40] Umesh Shastry
Hello Bob Dankert,

Thank you for the report.
Observed that 5.6.44 and 5.7.26 are affected.

thanks,
Umesh
[9 May 5:43] Umesh Shastry
Issue is not there in 8.0 since at least 8.0.11+

- 8.0.16

 bin/mysql -uroot -S /tmp/mysql_ushastry.sock                          
 Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.16 MySQL Community Server - GPL

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database if not exists test;
Query OK, 1 row affected (0.01 sec)

mysql> use test
Database changed
mysql> delimiter //
mysql> CREATE FUNCTION `test`(`input_string` VARCHAR(1000)) RETURNS int(11) NO SQL DETERMINISTIC
    -> BEGIN
    ->   RETURN RIGHT(`input_string`, 1) IN (' ', ',');
    -> END//
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> delimiter ;
mysql>
mysql> SELECT `test`('');
+------------+
| `test`('') |
+------------+
|          0 |
+------------+
1 row in set (0.00 sec)

mysql>

 bin/mysql -uroot -S /tmp/mysql_ushastry.sock   Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.15 MySQL Community Server - GPL

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database if not exists test;
Query OK, 1 row affected (0.00 sec)

mysql> use test;
Database changed
mysql> delimiter //
mysql> CREATE FUNCTION `test`(`input_string` VARCHAR(1000)) RETURNS int(11) NO SQL DETERMINISTIC
    -> BEGIN
    ->   RETURN RIGHT(`input_string`, 1) IN (' ', ',');
    -> END//
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> delimiter ;
mysql>
mysql> SELECT `test`('');
+------------+
| `test`('') |
+------------+
|          0 |
+------------+
1 row in set (0.00 sec)

mysql>

- 8.0.11

 bin/mysql -uroot -S /tmp/mysql_ushastry.sock                          Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.11 MySQL Community Server - GPL

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>
mysql> create database if not exists test;
Query OK, 1 row affected (0.05 sec)

mysql> use test;
Database changed
mysql> delimiter //
mysql> CREATE FUNCTION `test`(`input_string` VARCHAR(1000)) RETURNS int(11) NO SQL DETERMINISTIC
    -> BEGIN
    ->   RETURN RIGHT(`input_string`, 1) IN (' ', ',');
    -> END//
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> delimiter ;
mysql>
mysql> SELECT `test`('');
+------------+
| `test`('') |
+------------+
|          0 |
+------------+
1 row in set (0.00 sec)

mysql>
[9 May 13:54] Bob Dankert
It is happening with me using MySQL 8.0.15 sourced from two different places: AWS RDS MySQL 8.0.15, as well as the mysql:8.0.15 Docker image. Here is the log of running this on the 8.0.15 Docker image:

Your MySQL connection id is 398
Server version: 8.0.15 MySQL Community Server - GPL

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> delimiter //
mysql> CREATE FUNCTION `test`(`input_string` VARCHAR(1000)) RETURNS int(11) NO SQL DETERMINISTIC
    -> BEGIN
    ->   RETURN RIGHT(`input_string`, 1) IN (' ', ',');
    -> END//
Query OK, 0 rows affected (0.07 sec)

mysql> delimiter ;
mysql> SELECT `test`('');
+------------+
| `test`('') |
+------------+
|          1 |
+------------+
1 row in set (0.00 sec)

mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 8.0.15    |
+-----------+
1 row in set (0.00 sec)

mysql>
[9 May 14:53] Bob Dankert
I also tried creating a fresh database in 8.0.15, if that mattered, but it also returned the wrong result.

Just snippets:
mysql> create database if not exists test;
mysql> SELECT * FROM `information_schema`.`schemata` WHERE `schema_name` = 'test'\G
*************************** 1. row ***************************
              CATALOG_NAME: def
               SCHEMA_NAME: test
DEFAULT_CHARACTER_SET_NAME: utf8mb4
    DEFAULT_COLLATION_NAME: utf8mb4_unicode_ci
                  SQL_PATH: NULL
1 row in set (0.00 sec)

mysql> SELECT `test`.`test`('');
+-------------------+
| `test`.`test`('') |
+-------------------+
|                 1 |
+-------------------+
1 row in set (0.00 sec)

Again, this is using the docker MySQL 8.0.15 image, though it also occurs in an AWS RDS MySQL instance (with and without Multi-AZ, if that matters). 

If we are seeing a difference in results testing on 8.0.15 (which it seems we are), it's likely a difference in system variables affecting the behavior? If so, I am happy to upload my system variables on either the docker or AWS instances. Thanks!