Description:
If you create a View which uses a function, and you qualify the function with the full
databasename (<databasename>.<functionname>) the View is created without an error. If you
try to select from the View or "show create view" you will get an error message, as if you
had tried to create the view without the function at all.
I run into this problem when looking into some older views we have created a while ago,
those views are working without any issues but when you try to show the create statement,
the functions are missing. But those views are working without any problems. If you check
the VIEW_DEFINITION in the Information_schema Meta-Database their Syntax is shown with
the functions. I can't repeat creating a view like this, so i think this is an issue with
5.0.41 those views were created before 5.0.41.
Another issue, when you create a Databasedump with mysqldump, those views are dumped with
the wrong syntax without the function calls.
How to repeat:
For Example, create 2 tables:
CREATE TABLE `yocTest1` (
`id` int(11) NOT NULL,
`status` int(11) NOT NULL,
PRIMARY KEY (`id`)
);
CREATE TABLE `yocTest2` (
`id` int(11) NOT NULL,
`status` int(11) NOT NULL,
PRIMARY KEY (`id`)
);
A function:
DELIMITER $$
CREATE FUNCTION `yocMinInt`(a int,b int) RETURNS int(11)
DETERMINISTIC
RETURN CASE WHEN a >= b THEN b ELSE a END$$
DELIMITER ;
The following Select works without any problems:
SELECT yocTest.yocMinInt(t1.status,t2.status) as status FROM yocTest1 t1 cross join
yocTest2 t2;
If you Create a View from this View:
CREATE VIEW `yocTest`.`yocTestView` AS ( SELECT yocTest.yocMinInt(t1.status,t2.status)
as status FROM yocTest1 t1 cross join yocTest2 t2 );
The View is Created. If you now try to select from that view, your will get:
ERROR 1241 (21000): Operand should contain 1 column(s)
If you try to show the View Create Syntax with:
show create view `yocTest`.`yocTestView`;
you will get the same eror:
ERROR 1241 (21000): Operand should contain 1 column(s)
If you look in the "information_schema.VIEWS" Meta-Table you will find the following
create view :
/* ALGORITHM=UNDEFINED */
(select (`t1`.`status`,`t2`.`status`) AS `status` from (`yocTest`.`yocTest1` `t1` join
`yocTest`.`yocTest2` `t2`))
The function is missing, once in a while "information_schema.VIEWS.VIEW_DEFINITION" is
even empty.
If you create the view like this:
CREATE VIEW `yocTest`.`yocTestView` AS ( SELECT yocMinInt(t1.status,t2.status) as status
FROM yocTest1 t1 cross join yocTest2 t2 );
Everything works as expected.
Suggested fix:
Avoid qualifying the database when using functions inside views.
Description: If you create a View which uses a function, and you qualify the function with the full databasename (<databasename>.<functionname>) the View is created without an error. If you try to select from the View or "show create view" you will get an error message, as if you had tried to create the view without the function at all. I run into this problem when looking into some older views we have created a while ago, those views are working without any issues but when you try to show the create statement, the functions are missing. But those views are working without any problems. If you check the VIEW_DEFINITION in the Information_schema Meta-Database their Syntax is shown with the functions. I can't repeat creating a view like this, so i think this is an issue with 5.0.41 those views were created before 5.0.41. Another issue, when you create a Databasedump with mysqldump, those views are dumped with the wrong syntax without the function calls. How to repeat: For Example, create 2 tables: CREATE TABLE `yocTest1` ( `id` int(11) NOT NULL, `status` int(11) NOT NULL, PRIMARY KEY (`id`) ); CREATE TABLE `yocTest2` ( `id` int(11) NOT NULL, `status` int(11) NOT NULL, PRIMARY KEY (`id`) ); A function: DELIMITER $$ CREATE FUNCTION `yocMinInt`(a int,b int) RETURNS int(11) DETERMINISTIC RETURN CASE WHEN a >= b THEN b ELSE a END$$ DELIMITER ; The following Select works without any problems: SELECT yocTest.yocMinInt(t1.status,t2.status) as status FROM yocTest1 t1 cross join yocTest2 t2; If you Create a View from this View: CREATE VIEW `yocTest`.`yocTestView` AS ( SELECT yocTest.yocMinInt(t1.status,t2.status) as status FROM yocTest1 t1 cross join yocTest2 t2 ); The View is Created. If you now try to select from that view, your will get: ERROR 1241 (21000): Operand should contain 1 column(s) If you try to show the View Create Syntax with: show create view `yocTest`.`yocTestView`; you will get the same eror: ERROR 1241 (21000): Operand should contain 1 column(s) If you look in the "information_schema.VIEWS" Meta-Table you will find the following create view : /* ALGORITHM=UNDEFINED */ (select (`t1`.`status`,`t2`.`status`) AS `status` from (`yocTest`.`yocTest1` `t1` join `yocTest`.`yocTest2` `t2`)) The function is missing, once in a while "information_schema.VIEWS.VIEW_DEFINITION" is even empty. If you create the view like this: CREATE VIEW `yocTest`.`yocTestView` AS ( SELECT yocMinInt(t1.status,t2.status) as status FROM yocTest1 t1 cross join yocTest2 t2 ); Everything works as expected. Suggested fix: Avoid qualifying the database when using functions inside views.