| Bug #27328 | SELECT with WHERE IN () inside procedure | ||
|---|---|---|---|
| Submitted: | 21 Mar 2007 13:38 | Modified: | 21 Mar 2007 17:12 |
| Reporter: | Anca Dogaru | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: Stored Routines | Severity: | S3 (Non-critical) |
| Version: | 5.0.18 or 5.0.20 | OS: | Linux (centos) |
| Assigned to: | CPU Architecture: | Any | |
[21 Mar 2007 14:03]
MySQL Verification Team
We're sorry, but the bug system is not the appropriate forum for asking help on using MySQL products. Your problem is not the result of a bug. Support on using our products is available both free in our forums at http://forums.mysql.com/ and for a reasonable fee direct from our skilled support engineers at http://www.mysql.com/support/ Thank you for your interest in MySQL.
[21 Mar 2007 17:12]
Anca Dogaru
I didn't ask for help i just though that mysql should have the same behavior when running a statement in the mysql> prompt or inside a stored procedure if i issue mysql> select id from table where id in (1, 2, 3) returns 3 rows if i do the same inside a procedure it returns one row. If my method is wrong when using a string param instead of (1, 2, 3), maybe it should not return any row (but not a single row)
[21 Mar 2007 17:35]
MySQL Verification Team
How are you doing in the SP, it is tbe below within console:
mysql> SELECT * FROM test_table WHERE id IN ("1,2");
+----+--------+
| id | name |
+----+--------+
| 1 | value1 |
+----+--------+
1 row in set (0.00 sec)
mysql> SELECT * FROM test_table WHERE id IN ("2,1");
+----+--------+
| id | name |
+----+--------+
| 2 | value2 |
+----+--------+
1 row in set (0.00 sec)
Description: The statement SELECT * FROM test_table WHERE id IN (ids); returns only one row if it is called inside a stored procedure and the ids value is an input param for the procedure. How to repeat: CREATE TABLE `test_table` ( `id` INT NOT NULL AUTO_INCREMENT , `name` VARCHAR( 255 ) NOT NULL , PRIMARY KEY ( `id` ) ) TYPE = innodb; INSERT INTO `test_table` ( `id` , `name` ) VALUES ('', 'value1'), ('', 'value2'); DELIMITER // DROP PROCEDURE IF EXISTS _test_ // CREATE PROCEDURE _test_(ids varchar(255)) BEGIN SELECT * FROM test_table WHERE id IN (ids); END// mysql> call _test_('1,2'); +----+--------+ | id | name | +----+--------+ | 1 | value1 | +----+--------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec) Suggested fix: the procedure should return all columns matching the IN condition (in my example 2)