diff options
Diffstat (limited to 'mysql-test/main/join_crash.result')
| -rw-r--r-- | mysql-test/main/join_crash.result | 103 |
1 files changed, 103 insertions, 0 deletions
diff --git a/mysql-test/main/join_crash.result b/mysql-test/main/join_crash.result new file mode 100644 index 00000000000..f1a3b4956a8 --- /dev/null +++ b/mysql-test/main/join_crash.result @@ -0,0 +1,103 @@ +DROP TABLE IF EXISTS t1,t2,t3,t4; +CREATE TABLE t1 ( +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) +) ENGINE=MyISAM PACK_KEYS=1; +INSERT INTO t1 VALUES (1,0,'Rejected Time',1,NULL,NULL,NULL,NULL); +INSERT INTO t1 VALUES (209,0,'MDGRAD Proposal/Investigation',97,NULL,NULL,NULL,''); +INSERT INTO t1 VALUES (208,0,'Font 9 Design',84,NULL,NULL,NULL,''); +INSERT INTO t1 VALUES (207,0,'Web Based Order Processing',95,NULL,NULL,NULL,''); +INSERT INTO t1 VALUES (205,0,'Mac Screen Saver',95,NULL,NULL,NULL,''); +INSERT INTO t1 VALUES (206,0,'Web Site',96,NULL,NULL,NULL,''); +INSERT INTO t1 VALUES (204,0,'Magnafire Glue',94,NULL,NULL,NULL,''); +INSERT INTO t1 VALUES (203,0,'Print Bid',93,NULL,NULL,NULL,''); +INSERT INTO t1 VALUES (202,0,'EPOC Port',92,NULL,NULL,NULL,''); +INSERT INTO t1 VALUES (201,0,'TravelMate',88,NULL,NULL,NULL,''); +CREATE TABLE t2 ( +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) +) ENGINE=MyISAM PACK_KEYS=1; +INSERT INTO t2 VALUES (1,'user_table',98,'2000-01-01 00:00:00',NULL,NULL); +INSERT INTO t2 VALUES (2,'user_table',99,'2000-01-01 00:00:00',NULL,NULL); +INSERT INTO t2 VALUES (3,'user_table',100,'2000-01-01 00:00:00',NULL,NULL); +INSERT INTO t2 VALUES (49,'project_table',148,'2000-01-01 00:00:00',NULL,NULL); +INSERT INTO t2 VALUES (50,'client_table',68,'2000-01-01 00:00:00',NULL,NULL); +INSERT INTO t2 VALUES (51,'project_table',149,'2000-01-01 00:00:00',NULL,NULL); +INSERT INTO t2 VALUES (52,'project_table',150,'2000-01-01 00:00:00',NULL,NULL); +INSERT INTO t2 VALUES (53,'client_table',69,'2000-01-01 00:00:00',NULL,NULL); +INSERT INTO t2 VALUES (54,'project_table',151,'2000-01-01 00:00:00',NULL,NULL); +INSERT INTO t2 VALUES (55,'client_table',70,'2000-01-01 00:00:00',NULL,NULL); +INSERT INTO t2 VALUES (155,'role_table',1,'2000-01-01 00:00:00',NULL,NULL); +INSERT INTO t2 VALUES (156,'role_table',2,'2000-01-01 00:00:00',NULL,NULL); +INSERT INTO t2 VALUES (160,'member_table',1,'2000-01-01 00:00:00',NULL,1); +INSERT INTO t2 VALUES (161,'member_table',2,'2000-01-01 00:00:00',NULL,1); +INSERT INTO t2 VALUES (162,'member_table',3,'2000-01-01 00:00:00',NULL,1); +CREATE TABLE t3 ( +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) +) ENGINE=MyISAM PACK_KEYS=1; +CREATE TABLE t4 ( +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) +) ENGINE=MyISAM PACK_KEYS=1; +INSERT INTO t4 VALUES (1,0,'CPS',NULL,NULL); +select distinct +t1.project_id as project_id, +t1.project_name as project_name, +t1.client_ptr as client_ptr, +t1.comments as comments, +sum( t3.amount_received ) + sum( t3.adjustment ) as total_budget +from +t2 as client_period , +t2 as project_period, +t3 left join t1 on (t3.project_ptr = t1.project_id and +t3.date_received <= '2001-03-22 14:15:09') +left join t4 on t4.client_id = t1.client_ptr +where +1 +and ( client_period.period_type = 'client_table' + and client_period.period_key = t4.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 = t1.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; +project_id project_name client_ptr comments total_budget +DROP TABLE t1,t2,t3,t4; |