diff options
Diffstat (limited to 'mysql-test/t/join_crash.test')
| -rw-r--r-- | mysql-test/t/join_crash.test | 163 |
1 files changed, 163 insertions, 0 deletions
diff --git a/mysql-test/t/join_crash.test b/mysql-test/t/join_crash.test new file mode 100644 index 00000000000..da3778c1aae --- /dev/null +++ b/mysql-test/t/join_crash.test @@ -0,0 +1,163 @@ +# MySQL dump 8.13 +# +# Host: localhost Database: timeprep +#-------------------------------------------------------- +# Server version 3.23.35-log + +# +# Table structure for table 'project' +# + +DROP TABLE IF EXISTS project; +CREATE TABLE project ( + project_id int(11) NOT NULL auto_increment, + project_row_lock int(11) NOT NULL default '0', + project_name varchar(80) NOT NULL default '', + client_ptr int(11) NOT NULL default '0', + project_contact_ptr int(11) default NULL, + client_contact_ptr int(11) default NULL, + billing_contact_ptr int(11) default NULL, + comments mediumtext, + PRIMARY KEY (project_id), + UNIQUE KEY project (client_ptr,project_name) +) TYPE=MyISAM PACK_KEYS=1; + +# +# Dumping data for table 'project' +# + +INSERT INTO project VALUES (1,0,'Rejected Time',1,NULL,NULL,NULL,NULL); +INSERT INTO project VALUES (209,0,'MDGRAD Proposal/Investigation',97,NULL,NULL,NULL,''); +INSERT INTO project VALUES (208,0,'Font 9 Design',84,NULL,NULL,NULL,''); +INSERT INTO project VALUES (207,0,'Web Based Order Processing',95,NULL,NULL,NULL,''); +INSERT INTO project VALUES (205,0,'Mac Screen Saver',95,NULL,NULL,NULL,''); +INSERT INTO project VALUES (206,0,'Web Site',96,NULL,NULL,NULL,''); +INSERT INTO project VALUES (204,0,'Magnafire Glue',94,NULL,NULL,NULL,''); +INSERT INTO project VALUES (203,0,'Print Bid',93,NULL,NULL,NULL,''); +INSERT INTO project VALUES (202,0,'EPOC Port',92,NULL,NULL,NULL,''); +INSERT INTO project VALUES (201,0,'TravelMate',88,NULL,NULL,NULL,''); + +# +# Table structure for table 'period' +# + +DROP TABLE IF EXISTS period; +CREATE TABLE period ( + period_id int(11) NOT NULL auto_increment, + period_type enum('user_table','client_table','role_table','member_table','project_table') default NULL, + period_key int(11) default NULL, + start_date datetime default NULL, + end_date datetime default NULL, + work_load int(11) default NULL, + PRIMARY KEY (period_id), + KEY period_index (period_type,period_key), + KEY date_index (start_date,end_date) +) TYPE=MyISAM PACK_KEYS=1; + +# +# Dumping data for table 'period' +# + +INSERT INTO period VALUES (1,'user_table',98,'2000-01-01 00:00:00',NULL,NULL); +INSERT INTO period VALUES (2,'user_table',99,'2000-01-01 00:00:00',NULL,NULL); +INSERT INTO period VALUES (3,'user_table',100,'2000-01-01 00:00:00',NULL,NULL); +INSERT INTO period VALUES (49,'project_table',148,'2000-01-01 00:00:00',NULL,NULL); +INSERT INTO period VALUES (50,'client_table',68,'2000-01-01 00:00:00',NULL,NULL); +INSERT INTO period VALUES (51,'project_table',149,'2000-01-01 00:00:00',NULL,NULL); +INSERT INTO period VALUES (52,'project_table',150,'2000-01-01 00:00:00',NULL,NULL); +INSERT INTO period VALUES (53,'client_table',69,'2000-01-01 00:00:00',NULL,NULL); +INSERT INTO period VALUES (54,'project_table',151,'2000-01-01 00:00:00',NULL,NULL); +INSERT INTO period VALUES (55,'client_table',70,'2000-01-01 00:00:00',NULL,NULL); +INSERT INTO period VALUES (155,'role_table',1,'2000-01-01 00:00:00',NULL,NULL); +INSERT INTO period VALUES (156,'role_table',2,'2000-01-01 00:00:00',NULL,NULL); +INSERT INTO period VALUES (160,'member_table',1,'2000-01-01 00:00:00',NULL,1); +INSERT INTO period VALUES (161,'member_table',2,'2000-01-01 00:00:00',NULL,1); +INSERT INTO period VALUES (162,'member_table',3,'2000-01-01 00:00:00',NULL,1); + +# +# Table structure for table 'budget' +# + +DROP TABLE IF EXISTS budget; +CREATE TABLE budget ( + budget_id int(11) NOT NULL auto_increment, + project_ptr int(11) NOT NULL default '0', + po_number varchar(20) NOT NULL default '', + status enum('open','closed') default NULL, + date_received datetime default NULL, + amount_received float(10,2) default NULL, + adjustment float(10,2) default NULL, + PRIMARY KEY (budget_id), + UNIQUE KEY po (project_ptr,po_number) +) TYPE=MyISAM PACK_KEYS=1; + +# +# Dumping data for table 'budget' +# + + +# +# Table structure for table 'client' +# + +DROP TABLE IF EXISTS client; +CREATE TABLE client ( + client_id int(11) NOT NULL auto_increment, + client_row_lock int(11) NOT NULL default '0', + client_name varchar(80) NOT NULL default '', + contact_ptr int(11) default NULL, + comments mediumtext, + PRIMARY KEY (client_id), + UNIQUE KEY client_name (client_name) +) TYPE=MyISAM PACK_KEYS=1; + +# +# Dumping data for table 'client' +# + +INSERT INTO client VALUES (1,0,'CPS',NULL,NULL); + + + +# +# The query that fails... +# +select distinct + project.project_id as project_id, + project.project_name as project_name, + project.client_ptr as client_ptr, + project.comments as comments, + sum( budget.amount_received ) + sum( budget.adjustment ) as total_budget +from + project , + period as client_period , + period as project_period + left join + budget + on + budget.project_ptr = project.project_id + and budget.date_received <= '2001-03-22 14:15:09' + left join + client + on + client.client_id = project.client_ptr + where + 1 + and ( client_period.period_type = 'client_table' + and client_period.period_key = client.client_id + and ( client_period.start_date <= '2001-03-22 14:15:09' or isnull( client_period.start_date )) + and ( client_period.end_date > '2001-03-21 14:15:09' or isnull( client_period.end_date )) + ) + and ( project_period.period_type = 'project_table' + and project_period.period_key = project.project_id + and ( project_period.start_date <= '2001-03-22 14:15:09' or isnull( project_period.start_date )) + and ( project_period.end_date > '2001-03-21 14:15:09' or isnull( project_period.end_date )) ) + group by + client_id, + project_id , + client_period.period_id , + project_period.period_id + order by + client_name asc, + project_name asc +; |