Debian Codegraph @

mysql-test/suite/optimizer_unfixed_bugs/t/bug45219.test

raw
1 # BUG#45219 "Azalea crash on query containing a JOIN in subquery"
2
3 # Both SELECT DISTINCT cause similar crashes.
4 # With ./mtr --mysqld=--optimizer_switch="semijoin=off" optimizer_unfixed_bugs.bug45219
5 # there is no crash, the result file has been created with this
6 # command, but it has *not* been checked for correctness of returned
7 # data (at least 5.1 gives the same result).
8
9
10 # Begin test case for query 0
11
12 --disable_warnings
13 DROP TABLE IF EXISTS CC, C, BB;
14 --enable_warnings
15
16 CREATE TABLE `CC` (
17 `pk` int(11) NOT NULL AUTO_INCREMENT,
18 `int_nokey` int(11) NOT NULL,
19 `datetime_key` datetime NOT NULL,
20 `varchar_key` varchar(1) NOT NULL,
21 PRIMARY KEY (`pk`),
22 KEY `datetime_key` (`datetime_key`),
23 KEY `varchar_key` (`varchar_key`)
24 ) ENGINE=MyISAM AUTO_INCREMENT=30 DEFAULT CHARSET=latin1;
25 INSERT INTO `CC` VALUES
26 (10,0,'2006-07-07 07:26:28','q'),(11,5,'2002-09-23 00:00:00','m'),
27 (12,7,'0000-00-00 00:00:00','j'),(13,1,'2006-06-07 00:00:00','z'),
28 (14,8,'2000-09-16 12:15:34','a'),(15,2,'2007-08-05 15:47:52',''),
29 (16,1,'0000-00-00 00:00:00','e'),(17,8,'2005-12-02 19:34:26','t'),
30 (18,5,'0000-00-00 00:00:00','q'),(19,4,'0000-00-00 00:00:00','b'),
31 (20,5,'2007-12-28 00:00:00','w'),(21,3,'2004-08-02 11:48:43','m'),
32 (22,0,'0000-00-00 00:00:00','x'),(23,8,'2004-04-19 12:18:43',''),
33 (24,0,'2009-04-27 00:00:00','w'),(25,4,'2006-10-20 14:52:15','x'),
34 (26,0,'0000-00-00 00:00:00','e'),(27,0,'2002-03-22 11:48:37','e'),
35 (28,2,'0000-00-00 00:00:00','p'),(29,0,'2001-01-04 03:55:07','x');
36 CREATE TABLE `C` (
37 `pk` int(11) NOT NULL AUTO_INCREMENT,
38 `int_nokey` int(11) NOT NULL,
39 `datetime_key` datetime NOT NULL,
40 `varchar_key` varchar(1) NOT NULL,
41 PRIMARY KEY (`pk`),
42 KEY `datetime_key` (`datetime_key`),
43 KEY `varchar_key` (`varchar_key`)
44 ) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=latin1;
45 INSERT INTO `C` VALUES
46 (1,9,'0000-00-00 00:00:00','p'),(2,0,'2002-02-09 07:38:13','v'),
47 (3,8,'2001-05-03 12:08:14','t'),(4,3,'0000-00-00 00:00:00','u'),
48 (5,7,'2009-07-28 03:43:30','n'),(6,0,'2009-08-04 00:00:00','l'),
49 (7,1,'0000-00-00 00:00:00','h'),(8,9,'0000-00-00 00:00:00','u'),
50 (9,0,'2005-08-02 17:16:54','n'),(10,9,'2002-12-21 00:00:00','j'),
51 (11,0,'2005-08-15 12:37:35','k'),(12,5,'0000-00-00 00:00:00','e'),
52 (13,0,'2006-03-10 00:00:00','i'),(14,8,'2005-05-16 11:02:36','u'),
53 (15,8,'2008-11-02 00:00:00','n'),(16,5,'2006-03-15 00:00:00','b'),
54 (17,1,'0000-00-00 00:00:00','x'),(18,7,'0000-00-00 00:00:00',''),
55 (19,0,'2008-12-17 20:15:40','q'),(20,9,'0000-00-00 00:00:00','u');
56 CREATE TABLE `BB` (
57 `pk` int(11) NOT NULL AUTO_INCREMENT,
58 `int_nokey` int(11) NOT NULL,
59 `datetime_key` datetime NOT NULL,
60 `varchar_key` varchar(1) NOT NULL,
61 PRIMARY KEY (`pk`),
62 KEY `datetime_key` (`datetime_key`),
63 KEY `varchar_key` (`varchar_key`)
64 ) ENGINE=MyISAM AUTO_INCREMENT=12 DEFAULT CHARSET=latin1;
65 INSERT INTO `BB` VALUES
66 (10,8,'2007-08-19 08:08:38','i'),(11,0,'2000-05-21 03:51:51','');
67
68 SELECT DISTINCT `datetime_key`
69 FROM C
70 WHERE ( `int_nokey` , `pk` ) IN (
71 SELECT INNR .`pk` , INNR .`pk`
72 FROM CC LEFT JOIN BB INNR ON INNR .`varchar_key` ) AND `pk` = 9 ;
73
74 DROP TABLE CC, C, BB;
75
76 # End of test case for query 0
77
78 # Begin test case for query 1
79
80 --disable_warnings
81 DROP TABLE IF EXISTS CC, C, BB;
82 --enable_warnings
83
84 CREATE TABLE `CC` (
85 `pk` int(11) NOT NULL AUTO_INCREMENT,
86 `int_nokey` int(11) NOT NULL,
87 `int_key` int(11) NOT NULL,
88 `date_key` date NOT NULL,
89 `datetime_key` datetime NOT NULL,
90 `varchar_key` varchar(1) NOT NULL,
91 `varchar_nokey` varchar(1) NOT NULL,
92 PRIMARY KEY (`pk`),
93 KEY `int_key` (`int_key`),
94 KEY `date_key` (`date_key`),
95 KEY `datetime_key` (`datetime_key`),
96 KEY `varchar_key` (`varchar_key`)
97 ) ENGINE=MyISAM AUTO_INCREMENT=30 DEFAULT CHARSET=latin1;
98 INSERT INTO `CC` VALUES
99 (10,0,8,'2007-02-14','2006-07-07 07:26:28','q','q'),
100 (11,5,8,'2002-10-03','2002-09-23 00:00:00','m','m'),
101 (12,7,3,'2006-12-02','0000-00-00 00:00:00','j','j'),
102 (13,1,2,'2007-05-02','2006-06-07 00:00:00','z','z'),
103 (14,8,2,'2001-11-18','2000-09-16 12:15:34','a','a'),
104 (15,2,6,'2006-09-09','2007-08-05 15:47:52','',''),
105 (16,1,8,'0000-00-00','0000-00-00 00:00:00','e','e'),
106 (17,8,9,'2003-07-22','2005-12-02 19:34:26','t','t'),
107 (18,5,2,'2001-12-22','0000-00-00 00:00:00','q','q'),
108 (19,4,6,'0000-00-00','0000-00-00 00:00:00','b','b'),
109 (20,5,5,'2006-09-02','2007-12-28 00:00:00','w','w'),
110 (21,3,2,'0000-00-00','2004-08-02 11:48:43','m','m'),
111 (22,0,4,'0000-00-00','0000-00-00 00:00:00','x','x'),
112 (23,8,9,'2001-02-28','2004-04-19 12:18:43','',''),
113 (24,0,6,'0000-00-00','2009-04-27 00:00:00','w','w'),
114 (25,4,5,'2007-05-19','2006-10-20 14:52:15','x','x'),
115 (26,0,0,'2005-02-15','0000-00-00 00:00:00','e','e'),
116 (27,0,0,'2000-10-19','2002-03-22 11:48:37','e','e'),
117 (28,2,8,'2005-07-07','0000-00-00 00:00:00','p','p'),
118 (29,0,0,'2008-10-18','2001-01-04 03:55:07','x','x');
119 CREATE TABLE `C` (
120 `pk` int(11) NOT NULL AUTO_INCREMENT,
121 `int_nokey` int(11) NOT NULL,
122 `int_key` int(11) NOT NULL,
123 `date_key` date NOT NULL,
124 `datetime_key` datetime NOT NULL,
125 `varchar_key` varchar(1) NOT NULL,
126 `varchar_nokey` varchar(1) NOT NULL,
127 PRIMARY KEY (`pk`),
128 KEY `int_key` (`int_key`),
129 KEY `date_key` (`date_key`),
130 KEY `datetime_key` (`datetime_key`),
131 KEY `varchar_key` (`varchar_key`)
132 ) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=latin1;
133 INSERT INTO `C` VALUES
134 (1,9,9,'2007-12-01','0000-00-00 00:00:00','p','p'),
135 (2,0,0,'0000-00-00','2002-02-09 07:38:13','v','v'),
136 (3,8,6,'0000-00-00','2001-05-03 12:08:14','t','t'),
137 (4,3,6,'2002-05-07','0000-00-00 00:00:00','u','u'),
138 (5,7,6,'0000-00-00','2009-07-28 03:43:30','n','n'),
139 (6,0,4,'0000-00-00','2009-08-04 00:00:00','l','l'),
140 (7,1,7,'0000-00-00','0000-00-00 00:00:00','h','h'),
141 (8,9,4,'0000-00-00','0000-00-00 00:00:00','u','u'),
142 (9,0,8,'0000-00-00','2005-08-02 17:16:54','n','n'),
143 (10,9,4,'2000-12-18','2002-12-21 00:00:00','j','j'),
144 (11,0,7,'2005-11-13','2005-08-15 12:37:35','k','k'),
145 (12,5,5,'0000-00-00','0000-00-00 00:00:00','e','e'),
146 (13,0,0,'2003-11-12','2006-03-10 00:00:00','i','i'),
147 (14,8,5,'2006-02-20','2005-05-16 11:02:36','u','u'),
148 (15,8,7,'2005-02-12','2008-11-02 00:00:00','n','n'),
149 (16,5,2,'2009-07-20','2006-03-15 00:00:00','b','b'),
150 (17,1,8,'2005-02-24','0000-00-00 00:00:00','x','x'),
151 (18,7,0,'0000-00-00','0000-00-00 00:00:00','',''),
152 (19,0,9,'0000-00-00','2008-12-17 20:15:40','q','q'),
153 (20,9,5,'0000-00-00','0000-00-00 00:00:00','u','u');
154 CREATE TABLE `BB` (
155 `pk` int(11) NOT NULL AUTO_INCREMENT,
156 `int_nokey` int(11) NOT NULL,
157 `int_key` int(11) NOT NULL,
158 `date_key` date NOT NULL,
159 `datetime_key` datetime NOT NULL,
160 `varchar_key` varchar(1) NOT NULL,
161 `varchar_nokey` varchar(1) NOT NULL,
162 PRIMARY KEY (`pk`),
163 KEY `int_key` (`int_key`),
164 KEY `date_key` (`date_key`),
165 KEY `datetime_key` (`datetime_key`),
166 KEY `varchar_key` (`varchar_key`)
167 ) ENGINE=MyISAM AUTO_INCREMENT=12 DEFAULT CHARSET=latin1;
168 INSERT INTO `BB` VALUES (10,8,5,'0000-00-00','2007-08-19
169 08:08:38','i','i'),(11,0,8,'2005-08-18','2000-05-21 03:51:51','','');
170
171 SELECT DISTINCT BIT_AND( OUTR . `datetime_key` ) AS X
172 FROM C AS OUTR
173 WHERE ( OUTR . `int_nokey` , OUTR . `pk` ) IN (
174 SELECT DISTINCT INNR . `pk` AS X , INNR . `pk` AS Y
175 FROM CC AS INNR2 LEFT JOIN BB AS INNR ON
176 ( INNR2 . `varchar_nokey` = INNR . `varchar_key` )
177 WHERE INNR . `date_key` BETWEEN '2009-04-26' AND '2004-08-21' )
178 AND OUTR . `pk` = 9
179 ORDER BY OUTR . `int_key` , OUTR . `pk`;
180
181 DROP TABLE CC, C, BB;
182
183 # End of test case for query 1