added test case for the nasty join query crash

sql/sql_table.cc:
  fixed typo in comment

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 
+;