Bug #15683 crash, Function on nested VIEWs, Prepared statement
Submitted: 12 Dec 2005 13:56 Modified: 31 Mar 2006 6:28
Reporter: Matthias Leich Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Prepared statements Severity:S2 (Serious)
Version:5.0 OS:
Assigned to: Konstantin Osipov CPU Architecture:Any

[12 Dec 2005 13:56] Matthias Leich
Description:
Extreme simplified testcase, which crashes when executed
with --ps-protocol:
CREATE VIEW v1 AS SELECT 1 FROM t_select3 UNION ALL SELECT 1 ;
CREATE VIEW v2 AS SELECT 1 FROM v1;
CREATE VIEW v3 AS SELECT 1 AS f1 FROM v2;

delimiter //;
CREATE FUNCTION func1() RETURNS BIGINT
BEGIN
RETURN (SELECT COUNT(*) FROM v3);
END//
delimiter ;//

# This gives a crash
SELECT func1();

Any action like:
- removal of the UNION in VIEW v1
- expand the SELECT within v2 so that v1 is not used
- expand ....                        v3 ...........v2 is not used
removes the crash.

Please have a look in my testscript. It starts with the extreme
simplified testcase followed by testcases with increased
complexity till the original testcase is reached.
You will have to switch the "if (0)" to "if (1)" if you want
to execute the more complicated testcases.

BTW: It looks a bit like this bug is similar to
     Bug#15516  Selecting from the view causes server to crash on 
                        2nd, 4th,6th, etc executions.
         But the current bug seems to be not a duplicate because
         - a function is needed
         - #15516 should be fixed with 5.0.17 and indeed it does not
            crash with my fresh release
         - the option --ps-protocol is needed

My environment:
   - Intel PC with Linux(SuSE 9.3)
   - MySQL compiled from source
        Version 5.0 ChangeSet@1.1968, 2005-12-11

How to repeat:
Please use my attached testscript ml072.test
copy it to mysql-test/t
echo "Dummy" > r/ml072.result   # Produce a dummy file with 
                                                 # expected results
./mysql-test-run ml072
[12 Dec 2005 13:57] Matthias Leich
testscript

Attachment: ml072.test (application/test, text), 12.31 KiB.

[12 Dec 2005 13:57] Matthias Leich
stack trace

Attachment: ml072.txt (text/plain), 4.41 KiB.

[24 Mar 2006 20:13] Konstantin Osipov
I was able to reproduce the bug.
Below is a full test case for the problem:

drop table if exists t1, v1, v2, v3;
create table t1 (f1 bigint, f2 varchar(20), f3 bigint);
insert into t1 set f1 = 1, f2 = 'schoenenbourg', f3 = 1;
create view v1 as select 1 from t_union all select 1 ;
create view v2 as select 1 from v1;
create view v3 as select 1 as f1 from v2;

delimiter |
create function func1() returns bigint
begin
return (select count(*) from v3);
end|
delimiter ;

prepare stmt from "select func1()";
execute stmt;
[25 Mar 2006 11:28] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/4153
[28 Mar 2006 11:25] Konstantin Osipov
Pushed into the runtime tree.
[29 Mar 2006 18:11] Konstantin Osipov
Pushed into 5.0.20, merged into 5.1.8
[31 Mar 2006 6:28] Jon Stephens
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Documented bugfix in 5.0.20 and 5.1.8 changelogs. Closed.