Description:
Hello!
I have problems with certain sql statments (using "case", "union", ansii join syntax) that prevent me from developing elegant sql solutions.
Such are wrong results and server crashdowns after 1st execution, while the 1st execution works perfectly (!).
To fastly build a testing environment I'm sending you the necessary ddl statements (3 tables, 1 stored procedure).
The details how to reproduce this behaviour are given as comments in the "create procedure" statement at the end of the ddl statements.
I'm using MySQL 5 alpha version on Windows 98 SE.
How to repeat:
drop table if exists x_tree_component;
create table x_tree_component
(component_id smallint unsigned not null auto_increment,
component char(30) not null default '',
primary key (component_id))
engine=myisam
default charset=latin1;
-- system data --
insert into x_tree_component(component)
values('root node');
insert into x_tree_component(component)
values('þuffer');
-- test data --
insert into x_tree_component(component)
values('1 node');
insert into x_tree_component(component)
values('11 node');
insert into x_tree_component(component)
values('111 node');
insert into x_tree_component(component)
values('2 node');
insert into x_tree_component(component)
values('21 node');
insert into x_tree_component(component)
values('a leaf');
insert into x_tree_component(component)
values('22 node');
insert into x_tree_component(component)
values('c node');
insert into x_tree_component(component)
values('b leaf');
insert into x_tree_component(component)
values('3 node');
-- **********
drop table if exists x_tree_node;
create table x_tree_node
(node_id smallint unsigned not null default 0,
primary key (node_id))
engine=myisam
default charset=latin1;
-- system data --
insert into x_tree_node(node_id)
values(1);
insert into x_tree_node(node_id)
values(2);
-- test data --
insert into x_tree_node(node_id)
values(3);
insert into x_tree_node(node_id)
values(4);
insert into x_tree_node(node_id)
values(5);
insert into x_tree_node(node_id)
values(6);
insert into x_tree_node(node_id)
values(7);
insert into x_tree_node(node_id)
values(9);
insert into x_tree_node(node_id)
values(10);
insert into x_tree_node(node_id)
values(12);
-- **********
drop table if exists x_tree;
create table x_tree
(parent_node_id smallint unsigned not null,
child_component_id smallint unsigned not null,
primary key (parent_node_id, child_component_id))
engine=myisam
default charset=latin1;
-- system data --
insert into x_tree
values('1', '2');
-- test data --
insert into x_tree
values('1', '3');
insert into x_tree
values('1', '6');
insert into x_tree
values('1', '12');
insert into x_tree
values('3', '4');
insert into x_tree
values('4', '5');
insert into x_tree
values('6', '7');
insert into x_tree
values('7', '8');
insert into x_tree
values('6', '9');
insert into x_tree
values('9', '10');
insert into x_tree
values('9', '11');
-- **********
drop procedure if exists x_tree;
delimiter // ;
create procedure x_tree ()
begin
select xt.parent_node_id, xtcp.component as parent_node,
xt.child_component_id, xtcc.component as child_component,
case
when isnull(xtn.node_id) then 'false'
else 'true'
end as is_node
/* the following statements cause a server crashdown at 2nd execution
not shown in this procedure but: sql statements with "union" result in the same behaviour
,
case
when exists(select * from x_tree where parent_node_id = xt.child_component_id) then 'true'
else 'false'
end as has_children
*/
from x_tree xt, x_tree_component xtcp, x_tree_component xtcc
left join x_tree_node xtn on
xt.child_component_id = xtn.node_id
where xt.parent_node_id = xtcp.component_id and
xt.child_component_id = xtcc.component_id
/* using the ansii join syntax for inner joins instead returns a cartesian product after 1st execution
inner join x_tree_component xtcp on
xt.parent_node_id = xtcp.component_id
inner join x_tree_component xtcc on
xt.child_component_id = xtcc.component_id
*/
order by xtcp.component, xtcc.component;
end
//
delimiter ; //
Description: Hello! I have problems with certain sql statments (using "case", "union", ansii join syntax) that prevent me from developing elegant sql solutions. Such are wrong results and server crashdowns after 1st execution, while the 1st execution works perfectly (!). To fastly build a testing environment I'm sending you the necessary ddl statements (3 tables, 1 stored procedure). The details how to reproduce this behaviour are given as comments in the "create procedure" statement at the end of the ddl statements. I'm using MySQL 5 alpha version on Windows 98 SE. How to repeat: drop table if exists x_tree_component; create table x_tree_component (component_id smallint unsigned not null auto_increment, component char(30) not null default '', primary key (component_id)) engine=myisam default charset=latin1; -- system data -- insert into x_tree_component(component) values('root node'); insert into x_tree_component(component) values('þuffer'); -- test data -- insert into x_tree_component(component) values('1 node'); insert into x_tree_component(component) values('11 node'); insert into x_tree_component(component) values('111 node'); insert into x_tree_component(component) values('2 node'); insert into x_tree_component(component) values('21 node'); insert into x_tree_component(component) values('a leaf'); insert into x_tree_component(component) values('22 node'); insert into x_tree_component(component) values('c node'); insert into x_tree_component(component) values('b leaf'); insert into x_tree_component(component) values('3 node'); -- ********** drop table if exists x_tree_node; create table x_tree_node (node_id smallint unsigned not null default 0, primary key (node_id)) engine=myisam default charset=latin1; -- system data -- insert into x_tree_node(node_id) values(1); insert into x_tree_node(node_id) values(2); -- test data -- insert into x_tree_node(node_id) values(3); insert into x_tree_node(node_id) values(4); insert into x_tree_node(node_id) values(5); insert into x_tree_node(node_id) values(6); insert into x_tree_node(node_id) values(7); insert into x_tree_node(node_id) values(9); insert into x_tree_node(node_id) values(10); insert into x_tree_node(node_id) values(12); -- ********** drop table if exists x_tree; create table x_tree (parent_node_id smallint unsigned not null, child_component_id smallint unsigned not null, primary key (parent_node_id, child_component_id)) engine=myisam default charset=latin1; -- system data -- insert into x_tree values('1', '2'); -- test data -- insert into x_tree values('1', '3'); insert into x_tree values('1', '6'); insert into x_tree values('1', '12'); insert into x_tree values('3', '4'); insert into x_tree values('4', '5'); insert into x_tree values('6', '7'); insert into x_tree values('7', '8'); insert into x_tree values('6', '9'); insert into x_tree values('9', '10'); insert into x_tree values('9', '11'); -- ********** drop procedure if exists x_tree; delimiter // ; create procedure x_tree () begin select xt.parent_node_id, xtcp.component as parent_node, xt.child_component_id, xtcc.component as child_component, case when isnull(xtn.node_id) then 'false' else 'true' end as is_node /* the following statements cause a server crashdown at 2nd execution not shown in this procedure but: sql statements with "union" result in the same behaviour , case when exists(select * from x_tree where parent_node_id = xt.child_component_id) then 'true' else 'false' end as has_children */ from x_tree xt, x_tree_component xtcp, x_tree_component xtcc left join x_tree_node xtn on xt.child_component_id = xtn.node_id where xt.parent_node_id = xtcp.component_id and xt.child_component_id = xtcc.component_id /* using the ansii join syntax for inner joins instead returns a cartesian product after 1st execution inner join x_tree_component xtcp on xt.parent_node_id = xtcp.component_id inner join x_tree_component xtcc on xt.child_component_id = xtcc.component_id */ order by xtcp.component, xtcc.component; end // delimiter ; //