MySQL Bugs: #95303: RIGHT ... IN evaluating to 1 within user function, 0 outside of user function

Bug #95303	RIGHT ... IN evaluating to 1 within user function, 0 outside of user function
Submitted:	8 May 2019 16:17	Modified:	9 May 2019 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

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

Hello Bob Dankert,

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

thanks,
Umesh

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>

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>

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!