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