| Bug #9700 | crashes when calling a stored procedure reading data from a view | ||
|---|---|---|---|
| Submitted: | 7 Apr 2005 1:17 | Modified: | 15 May 2005 23:32 |
| Reporter: | lei wang | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | MySQL Server: User-defined functions ( UDF ) | Severity: | S1 (Critical) |
| Version: | 5.0.3 Beta | OS: | Windows (Windows XP Pro) |
| Assigned to: | Assigned Account | CPU Architecture: | Any |
[7 Apr 2005 1:42]
MySQL Verification Team
c:\mysql\bin>mysql -uroot
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.0.4-beta-debug
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> create database test_view;
Query OK, 1 row affected (0.01 sec)
mysql> use test_view;
Database changed
mysql> create table t_t (id int, reserve_code varchar(32));
Query OK, 0 rows affected (0.09 sec)
mysql> insert into t_t values (1,"12345");
Query OK, 1 row affected (0.02 sec)
mysql> insert into t_t values (2,"67890");
Query OK, 1 row affected (0.00 sec)
mysql> create view t_v as select * from t_t;
Query OK, 0 rows affected (0.02 sec)
mysql> delimiter //
mysql> create procedure sp_t (re_code VARCHAR(32))
-> BEGIN
-> select t_v.* from test_view.t_v
-> WHERE t_v.reserve_code=re_code;
-> END;//
Query OK, 0 rows affected (0.00 sec)
mysql> call sp_t ("1");
-> //
Empty set (0.01 sec)
Query OK, 0 rows affected (0.01 sec)
mysql> call sp_t ("12345");
-> //
+------+--------------+
| id | reserve_code |
+------+--------------+
| 1 | 12345 |
+------+--------------+
1 row in set (0.01 sec)
Query OK, 0 rows affected (0.01 sec)
mysql>
[7 Apr 2005 6:13]
lei wang
I'm using InnoDB as the storage engine.
[7 Apr 2005 13:38]
MySQL Verification Team
Also with InnoDB tables I wasn't able to repeat:
mysql> show create table t_t\G
*************************** 1. row ***************************
Table: t_t
Create Table: CREATE TABLE `t_t` (
`id` int(11) default NULL,
`reserve_code` varchar(32) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> call sp_t ("67890");
+------+--------------+
| id | reserve_code |
+------+--------------+
| 2 | 67890 |
| 2 | 67890 |
+------+--------------+
2 rows in set (0.01 sec)
Query OK, 0 rows affected (0.01 sec)
[7 Apr 2005 14:40]
lei wang
my database schema and related data
Attachment: showerror.sql (text/plain), 66.54 KiB.
[7 Apr 2005 14:42]
lei wang
please create the schema by executing the script i uploaded,after that,execute the following statement,then u will see the bug i submitted.
there is a drop database in my script file , u may comment it.
DELIMITER //
CREATE PROCEDURE sp_getnightaudit (hotelcode VARCHAR(32))
BEGIN
SELECT v.* FROM v_normalnightaudit v
WHERE v.ReserveHotel=hotelcode;
END;//
DELIMITER ;
CALL sp_getnightaudit('');
CALL sp_getnightaudit('0215004');
[7 Apr 2005 15:15]
MySQL Verification Team
Thank you for the feedback.
Description: call a stored procedure including select statement from a view,when the result is an empty set it's ok,while results are there, mysql clients show 'can not find file'.. storage engine is InnoDB How to repeat: CREATE PROCEDURE sp_getnightaudit (hotelcode VARCHAR(32)) BEGIN SELECT v.* FROM v_normalnightaudit v <--it's a view WHERE v.ReserveHotel=hotelcode; END; mysql> call sp_getnightaudit('01'); Empty set (0.98 sec) mysql> call sp_getnightaudit('0215004'); ERROR 1017 (HY000): Can't find file: '.\\C:\WINDOWS\TEMP\#sql_bc_0.frm' (errno: 22)