MySQL Bugs: #11061: view to call function

Bug #11061	view to call function
Submitted:	3 Jun 2005 8:03	Modified:	24 Jun 2005 21:02
Reporter:	Ka Pong Chen	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	beta 5.0.6	OS:	Windows (Windows 2003 Server)
Assigned to:	Oleksandr Byelkin	CPU Architecture:	Any

Description:
I have created a function and then 
create a view to call the function,
after select from view, 
then system will crash

How to repeat:
I have created below function 
----------------------------------------------------------
CREATE FUNCTION format_int ( inparam int) RETURNS int
return (case when inparam is null then 0 else inparam end);
----------------------------------------------------------
And when I create a view  to call this function,
----------------------------------------------------------
CREATE VIEW testview
AS
select (format_int(a)+ format_int(b)) as field
from tableA
left outer join TableB
on tableA.key = tableB.key
----------------------------------------------------------
select * from testview  (will be crash system)

I tested on 5.0.7:

mysql> create table tableA (`key` int, a int, b int);
Query OK, 0 rows affected (0.03 sec)

mysql> create table tableB (`key` int);
Query OK, 0 rows affected (0.08 sec)

mysql> CREATE VIEW testview
    -> AS
    -> select (format_int(a)+ format_int(b)) as field
    -> from tableA
    -> left outer join TableB
    -> on tableA.key = tableB.key
    -> ;
Query OK, 0 rows affected (0.03 sec)

mysql> select * from testview;
Empty set (0.00 sec)

mysql> insert into tableA values (1,2,3);
Query OK, 1 row affected (0.00 sec)

mysql> insert into tableB values (1);
Query OK, 1 row affected (0.00 sec)

mysql>  select * from testview;
+-------+
| field |
+-------+
|     5 |
+-------+
1 row in set (0.00 sec)

All worked fine. Could you please check on 5.0.7?

I am expiriencing similar problem, using beta 5.0.6 on Linux FC3.

It happens to me also with a regular select. Database seems to crash whenever the select returns MORE THAN ONE record from an InnoDB table.

Steps to reproduce using a new/clean database:

mysql> CREATE FUNCTION myfunc() RETURNS CHAR(10) RETURN 'myfunc';
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE mytable (`key` int auto_increment, primary key (`key`)) ENGINE=INNODB;
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO mytable () VALUES();
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO mytable () VALUES();
Query OK, 1 row affected (0.00 sec)

mysql> SELECT *, myfunc() FROM mytable;
ERROR 2013 (HY000): Lost connection to MySQL server during query

Yes, I use Anze Zagar test case and the
system crash again on windows 2003 server OS.

The system will crash when input parameter  is null value.

Thanks. I can reproduce. 
Tested on Win 2000 Sp4, MySQL server 5.0.7 beta

XP2, mysqld 5.0.7 same bug:
delimiter //
//
use test
//
drop function if exists ctok
//
create function ctok(c char,i int) returns char
begin
  declare z char(1) default ";";
  set z=";";
  return substring_index(substring_index(concat(c,z),z,i),z,-1);
end
//
drop table if exists a
//
create table a
(
  a char(1) comment "a;b;c;d"
)
//
create or replace view my_tables as select 
 table_name 
,ctok(table_comment,1) as table_comment
,table_comment real_table_comment
from information_schema.tables 
where 1=1
and table_schema="test" 
//
select ctok("a;b;c;d",2)
//
select ctok("a;b;c;d",4)
//
select table_name from my_tables;
CRASH 
=> view calculate all columns from view ???
//
#select * from my_tables
//

Thank you for bugreport, but I can't reproduce crashes any more on current 5.0 (if something still wrong with mentioned tests, feel free reopen this bug):

+ CREATE FUNCTION myfunc() RETURNS CHAR(10) RETURN 'myfunc';
+ CREATE TABLE mytable (`key` int auto_increment, primary key (`key`))
+ ENGINE=INNODB;
+ INSERT INTO mytable () VALUES();
+ INSERT INTO mytable () VALUES();
+ SELECT *, myfunc() FROM mytable;
+ key   myfunc()
+ 1     myfunc
+ 2     myfunc

and

+ use test//
+ drop function if exists ctok//
+ Warnings:
+ Note  1305    FUNCTION ctok does not exist
+ create function ctok(c char,i int) returns char
+ begin
+ declare z char(1) default ";";
+ set z=";";
+ return substring_index(substring_index(concat(c,z),z,i),z,-1);
+ end//
+ drop table if exists a//
+ Warnings:
+ Note  1051    Unknown table 'a'
+ create table a
+ (
+ a char(1) comment "a;b;c;d"
+ )//
+ create or replace view my_tables as select
+ table_name
+ ,ctok(table_comment,1) as table_comment
+ ,table_comment real_table_comment
+ from information_schema.tables
+ where 1=1
+ and table_schema="test" //
+ select ctok("a;b;c;d",2)//
+ ctok("a;b;c;d",2)
+ b
+ select ctok("a;b;c;d",4)//
+ ctok("a;b;c;d",4)
+ d
+ select table_name from my_tables//
+ table_name
+ a
+ my_tables
+ select * from my_tables//
+ table_name    table_comment   real_table_comment
+ a     T       Table 'a' was not locked with LOCK TABLES
+ my_tables     v       view

I can confirm that my test case (myfunc) now works fine on my system since I updated to 5.0.7-beta-standard.

The other test case still doesn't seem to work:
for lines: select table_name from my_tables//
and: select * from my_tables//
I'm getting:
ERROR 2013 (HY000): Lost connection to MySQL server during query

Anze Zagar: I tested on current bk source repository, which marked as 5.0.9 (not yet released version)