1# 2# Test to ensure that we don't get stack overflows 3# 4 5drop table if exists t1,t2; 6 7# 8# MDEV-5724 9# Server crashes on SQL select containing more group by and left join 10# statements 11# This was because record_buffer was 300,000 bytes and caused stack overflow 12# 13 14CREATE TABLE t1 ( 15 `sspo_id` int(11) NOT NULL AUTO_INCREMENT, 16 `sspo_uid` int(11) NOT NULL DEFAULT '0', 17 `sspo_type` varchar(1) NOT NULL DEFAULT 'P', 18 `sspo_text` longtext NOT NULL, 19 `sspo_image` varchar(255) NOT NULL, 20 `sspo_source` int(11) NOT NULL DEFAULT '0', 21 `sspo_event_name` varchar(255) NOT NULL DEFAULT '', 22 `sspo_event_location` varchar(255) NOT NULL DEFAULT '', 23 `sspo_event_date` datetime DEFAULT NULL, 24 `sspo_remote_title` varchar(255) NOT NULL, 25 `sspo_remote_url` varchar(255) NOT NULL, 26 `sspo_remote_desc` text NOT NULL, 27 `sspo_remote_image` varchar(255) NOT NULL, 28 `sspo_obj_status` varchar(1) NOT NULL DEFAULT 'A', 29 `sspo_cr_date` datetime NOT NULL DEFAULT '1970-01-01 00:00:00', 30 `sspo_cr_uid` int(11) NOT NULL DEFAULT '0', 31 `sspo_lu_date` datetime NOT NULL DEFAULT '1970-01-01 00:00:00', 32 `sspo_lu_uid` int(11) NOT NULL DEFAULT '0', 33 PRIMARY KEY (`sspo_id`), 34 KEY `post_uid` (`sspo_uid`,`sspo_cr_date`) 35) AUTO_INCREMENT=30 DEFAULT CHARSET=utf8; 36INSERT INTO t1 VALUES (1,2,'P','test1','',0,'','',NULL,'','','','','A','2013-09-30 00:19:32',2,'2013-09-30 00:19:32',2),(2,2,'P','bbb','',0,'','',NULL,'','','','','A','2013-10-02 15:06:35',2,'2013-10-02 15:06:35',2); 37 38CREATE TABLE `t2` ( 39 `spoo_id` int(11) NOT NULL AUTO_INCREMENT, 40 `spoo_user_type_id` int(11) NOT NULL DEFAULT '0', 41 `spoo_uid` int(11) NOT NULL DEFAULT '0', 42 `spoo_option_id` int(11) NOT NULL DEFAULT '0', 43 `spoo_value` varchar(10000) NOT NULL, 44 `spoo_obj_status` varchar(1) NOT NULL DEFAULT 'A', 45 `spoo_cr_date` datetime NOT NULL DEFAULT '1970-01-01 00:00:00', 46 `spoo_cr_uid` int(11) NOT NULL DEFAULT '0', 47 `spoo_lu_date` datetime NOT NULL DEFAULT '1970-01-01 00:00:00', 48 `spoo_lu_uid` int(11) NOT NULL DEFAULT '0', 49 PRIMARY KEY (`spoo_id`), 50 KEY `object_option_main_idx` (`spoo_user_type_id`,`spoo_uid`,`spoo_option_id`,`spoo_value`(255)) 51) AUTO_INCREMENT=107 DEFAULT CHARSET=utf8; 52INSERT INTO `t2` VALUES (19,1,2,6,'Dortmund','A','2013-09-26 01:36:51',2,'2013-09-26 01:36:51',2),(20,1,2,8,'49','A','2013-09-26 01:36:51',2,'2013-09-26 01:36:51',2); 53 54SELECT Count(*) 55FROM t1 AS tbl 56 LEFT JOIN t2 a 57 ON a.spoo_uid = sspo_uid 58 AND a.spoo_option_id = 1 59 LEFT JOIN t2 b 60 ON b.spoo_uid = sspo_uid 61 AND b.spoo_option_id = 2 62 LEFT JOIN t2 c 63 ON c.spoo_uid = sspo_uid 64 AND c.spoo_option_id = 3 65 LEFT JOIN t2 d 66 ON d.spoo_uid = sspo_uid 67 AND d.spoo_option_id = 5 68 LEFT JOIN t2 e 69 ON e.spoo_uid = sspo_uid 70 AND e.spoo_option_id = 4 71 LEFT JOIN t2 f 72 ON f.spoo_uid = sspo_uid 73 AND f.spoo_option_id = 11 74 LEFT JOIN t2 g 75 ON g.spoo_uid = sspo_uid 76 AND g.spoo_option_id = 7 77 LEFT JOIN t2 h 78 ON h.spoo_uid = sspo_uid 79 AND h.spoo_option_id = 10 80 LEFT JOIN t2 i 81 ON i.spoo_uid = sspo_uid 82 AND i.spoo_option_id = 18 83 LEFT JOIN t2 j 84 ON j.spoo_uid = sspo_uid 85 AND j.spoo_option_id = 6 86GROUP BY a.spoo_value, 87 b.spoo_value, 88 c.spoo_value, 89 d.spoo_value, 90 e.spoo_value, 91 f.spoo_value, 92 g.spoo_value, 93 h.spoo_value, 94 i.spoo_value, 95 j.spoo_value; 96drop table t1,t2; 97