Bug #47336 Have to use "Like" instead of "=" when querying info_schema in Stored Procedure
Submitted: 15 Sep 2009 18:31 Modified: 15 Oct 2009 19:55
Reporter: Bing Wu Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S2 (Serious)
Version:5.1.13-0ubuntu10.2 OS:Linux (Linux cheapnloud 2.6.28-15-server #49-Ubuntu SMP Tue Aug 18 19:30:06 UTC 2009 i686 GNU/Linux)
Assigned to: CPU Architecture:Any
Tags: =, equals, information_schema, like, stored procedure

[15 Sep 2009 18:31] Bing Wu 
Description:
I am trying to count number of columns (whose names match certain pattern) inside a stored procedure. The same SQL query (against the information_schema) worked fine in CLI but once I put it inside a stored procedure, it returns NULL.

I do NOT notice the same problem when querying against non-system schema.

How to repeat:
Code -
create table test_tmp_0 (
id int(8),
name varchar(200) not null,
attr_0 int(16) default 0,
attr_1 int(16) default 0,
attr_2 int(16) default 0,
attr_3 int(16) default 0,
attr_4 int(16) default 0,
attr_5 int(16) default 0,
attr_6 int(16) default 0,
primary key (`id`)
) engine=MyISAM;

To find out how many attr_% columns in this table from CLI, do -
mysql> select count(column_name) from information_schema.columns where table_name='test_tmp_0' and column_name like 'attr_%';

It should return 7.

However, if you created a stored proc:
delimiter $$
create procedure test_tmp_proc()
begin
    select count(column_name) into @val from information_schema.columns where table_name='test_tmp_0' and column_name like 'attr_%';
    select @val;
end $$

Then run the procedure -
mysql> call test_tmp_proc()

You should get empty set.
[15 Sep 2009 19:28] Peter Laursen 
I get this with your example:  

@val  
------
     7

But why use the @val user variable at all?  This

CREATE PROCEDURE test_tmp_proc()
BEGIN
    SELECT COUNT(column_name)FROM information_schema.columns WHERE
table_name='test_tmp_0' AND column_name LIKE 'attr_%';
END $$
DELIMITER ;

call test_tmp_proc();

returns 

count(column_name)
------------------
                 7
(use an alias if you want - just add "AS val"!)

Could I guess you are using a PHP-based client? If so you should not use user variables as PHP will create a new connection for every statement due to the non-persistent nature on PHP<>MySQL connections.  User variables live on a 'per connection basis'. 

Peter
(not a MySQL person)
[15 Sep 2009 19:45] Peter Laursen 
A very simple example: 

Try in command line or another decent client;

SET @peter = 'nice guy';
SELECT @peter; -- returns 'nice guy';

Try in phpMyAdmin or a similar PHP-based client

SET @peter = 'nice guy';
SELECT @peter; -- returns NULL
(because the SET and SELECT statements were executed in different connections)

(.. and (at least in this case) PHP-based clients are not trustworthy!  :-) )
[15 Sep 2009 19:55] Sveta Smirnova 
Thank you for the report.

But version 5.1.13 is very old. Please try current version 5.1.38 and inform us if problem still exists in your environment.
[15 Oct 2009 23:00] Bugs System 
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".