Description:
When ISNULL is used together with LOAD_FILE as the condition on WHILE loop, it continuously returns the value caught on the first try. If the file is created/delete, ISNULL still returns the captured value.
How to repeat:
mysql> delimiter ;
mysql> show variables like 'secure_file_priv';
+------------------+------------------------------------------+
| Variable_name | Value |
+------------------+------------------------------------------+
| secure_file_priv | /home/kamil/repo/8.0/bld/mysql-test/var/ |
+------------------+------------------------------------------+
1 row in set (0.01 sec)
mysql> delimiter //
mysql> CREATE PROCEDURE p1() BEGIN WHILE ISNULL(LOAD_FILE('/home/kamil/repo/8.0/bld/mysql-test/var/tmp/trigger')) DO SELECT SLEEP(10); SELECT 'WAITING'; END WHILE; END//
Query OK, 0 rows affected (0.01 sec)
mysql> delimiter ;
mysql> call p1();
+-----------+
| SLEEP(10) |
+-----------+
| 0 |
+-----------+
1 row in set (10.00 sec)
+---------+
| WAITING |
+---------+
| WAITING |
+---------+
1 row in set (10.00 sec)
+-----------+
| SLEEP(10) |
+-----------+
| 0 |
+-----------+
1 row in set (20.00 sec)
+---------+
| WAITING |
+---------+
| WAITING |
+---------+
1 row in set (20.00 sec)
Now from the command line:
touch /home/kamil/repo/8.0/bld/mysql-test/var/trigger
Result: stored procedure loop still goes on.
Expected result: Loop should finish, as the file appeared and 'while' condition is not met anymore.
Suggested fix:
item_strfunc.h Item_Load_File class
Add method
table_map used_tables() const override { return (table_map)1L; }
to prevent caching in Item_func_isnull.
Description: When ISNULL is used together with LOAD_FILE as the condition on WHILE loop, it continuously returns the value caught on the first try. If the file is created/delete, ISNULL still returns the captured value. How to repeat: mysql> delimiter ; mysql> show variables like 'secure_file_priv'; +------------------+------------------------------------------+ | Variable_name | Value | +------------------+------------------------------------------+ | secure_file_priv | /home/kamil/repo/8.0/bld/mysql-test/var/ | +------------------+------------------------------------------+ 1 row in set (0.01 sec) mysql> delimiter // mysql> CREATE PROCEDURE p1() BEGIN WHILE ISNULL(LOAD_FILE('/home/kamil/repo/8.0/bld/mysql-test/var/tmp/trigger')) DO SELECT SLEEP(10); SELECT 'WAITING'; END WHILE; END// Query OK, 0 rows affected (0.01 sec) mysql> delimiter ; mysql> call p1(); +-----------+ | SLEEP(10) | +-----------+ | 0 | +-----------+ 1 row in set (10.00 sec) +---------+ | WAITING | +---------+ | WAITING | +---------+ 1 row in set (10.00 sec) +-----------+ | SLEEP(10) | +-----------+ | 0 | +-----------+ 1 row in set (20.00 sec) +---------+ | WAITING | +---------+ | WAITING | +---------+ 1 row in set (20.00 sec) Now from the command line: touch /home/kamil/repo/8.0/bld/mysql-test/var/trigger Result: stored procedure loop still goes on. Expected result: Loop should finish, as the file appeared and 'while' condition is not met anymore. Suggested fix: item_strfunc.h Item_Load_File class Add method table_map used_tables() const override { return (table_map)1L; } to prevent caching in Item_func_isnull.