Merge lp:~roccoblues/drizzle/port-constants-evalutation-fixes into lp:~drizzle-trunk/drizzle/development
- port-constants-evalutation-fixes
- Merge into development
Proposed by
Dennis Schön
Status: | Merged |
---|---|
Merged at revision: | not available |
Proposed branch: | lp:~roccoblues/drizzle/port-constants-evalutation-fixes |
Merge into: | lp:~drizzle-trunk/drizzle/development |
Diff against target: |
408 lines (+181/-11) 18 files modified
drizzled/item.cc (+59/-0) drizzled/item.h (+3/-0) drizzled/item/cache.h (+4/-0) drizzled/item/ref.h (+4/-1) drizzled/join.cc (+38/-0) drizzled/join.h (+2/-0) tests/r/errors.result (+4/-1) tests/r/func_in.result (+0/-1) tests/r/func_like.result (+1/-1) tests/r/func_str.result (+1/-1) tests/r/func_time.result (+0/-1) tests/r/select.result (+43/-0) tests/r/subselect.result (+1/-1) tests/r/subselect_no_mat.result (+1/-1) tests/r/subselect_no_mat_and_semi_join.result (+1/-1) tests/r/subselect_no_opts.result (+1/-1) tests/r/subselect_no_semijoin.result (+1/-1) tests/t/select.test (+17/-0) |
To merge this branch: | bzr merge lp:~roccoblues/drizzle/port-constants-evalutation-fixes |
Related bugs: | |
Related blueprints: |
Reviewer | Review Type | Date Requested | Status |
---|---|---|---|
Jay Pipes (community) | Approve | ||
Review via email: mp+16242@code.launchpad.net |
Commit message
Description of the change
To post a comment you must log in.
Revision history for this message
Dennis Schön (roccoblues) wrote : | # |
Revision history for this message
Jay Pipes (jaypipes) wrote : | # |
Looks good. I downloaded and all tests pass. Approved from me.
review:
Approve
Preview Diff
[H/L] Next/Prev Comment, [J/K] Next/Prev File, [N/P] Next/Prev Hunk
1 | === modified file 'drizzled/item.cc' |
2 | --- drizzled/item.cc 2009-12-05 23:29:59 +0000 |
3 | +++ drizzled/item.cc 2009-12-16 14:56:20 +0000 |
4 | @@ -1272,6 +1272,65 @@ |
5 | return error; |
6 | } |
7 | |
8 | +/** |
9 | + Check if an item is a constant one and can be cached. |
10 | + |
11 | + @param arg [out] TRUE <=> Cache this item. |
12 | + |
13 | + @return TRUE Go deeper in item tree. |
14 | + @return FALSE Don't go deeper in item tree. |
15 | +*/ |
16 | + |
17 | +bool Item::cache_const_expr_analyzer(unsigned char **arg) |
18 | +{ |
19 | + bool *cache_flag= (bool*)*arg; |
20 | + if (!*cache_flag) |
21 | + { |
22 | + Item *item= real_item(); |
23 | + /* |
24 | + Cache constant items unless it's a basic constant, constant field or |
25 | + a subselect (they use their own cache). |
26 | + */ |
27 | + if (const_item() && |
28 | + !(item->basic_const_item() || item->type() == Item::FIELD_ITEM || |
29 | + item->type() == SUBSELECT_ITEM || |
30 | + /* |
31 | + Do not cache GET_USER_VAR() function as its const_item() may |
32 | + return TRUE for the current thread but it still may change |
33 | + during the execution. |
34 | + */ |
35 | + (item->type() == Item::FUNC_ITEM && |
36 | + ((Item_func*)item)->functype() == Item_func::GUSERVAR_FUNC))) |
37 | + *cache_flag= true; |
38 | + return true; |
39 | + } |
40 | + return false; |
41 | +} |
42 | + |
43 | +/** |
44 | + Cache item if needed. |
45 | + |
46 | + @param arg TRUE <=> Cache this item. |
47 | + |
48 | + @return cache if cache needed. |
49 | + @return this otherwise. |
50 | +*/ |
51 | + |
52 | +Item* Item::cache_const_expr_transformer(unsigned char *arg) |
53 | +{ |
54 | + if (*(bool*)arg) |
55 | + { |
56 | + *((bool*)arg)= false; |
57 | + Item_cache *cache= Item_cache::get_cache(this); |
58 | + if (!cache) |
59 | + return NULL; |
60 | + cache->setup(this); |
61 | + cache->store(this); |
62 | + return cache; |
63 | + } |
64 | + return this; |
65 | +} |
66 | + |
67 | bool Item::send(plugin::Client *client, String *buffer) |
68 | { |
69 | bool result= false; |
70 | |
71 | === modified file 'drizzled/item.h' |
72 | --- drizzled/item.h 2009-12-05 20:06:30 +0000 |
73 | +++ drizzled/item.h 2009-12-16 14:56:20 +0000 |
74 | @@ -684,6 +684,9 @@ |
75 | virtual bool register_field_in_read_map(unsigned char *arg); |
76 | virtual bool subst_argument_checker(unsigned char **arg); |
77 | |
78 | + virtual bool cache_const_expr_analyzer(unsigned char **arg); |
79 | + virtual Item* cache_const_expr_transformer(unsigned char *arg); |
80 | + |
81 | virtual Item *equal_fields_propagator(unsigned char * arg); |
82 | virtual bool set_no_const_sub(unsigned char *arg); |
83 | virtual Item *replace_equal_field(unsigned char * arg); |
84 | |
85 | === modified file 'drizzled/item/cache.h' |
86 | --- drizzled/item/cache.h 2009-12-04 23:47:14 +0000 |
87 | +++ drizzled/item/cache.h 2009-12-16 14:56:20 +0000 |
88 | @@ -78,6 +78,10 @@ |
89 | { |
90 | return this == item; |
91 | } |
92 | + bool basic_const_item() const |
93 | + { |
94 | + return test(example && example->basic_const_item()); |
95 | + } |
96 | }; |
97 | |
98 | #endif /* DRIZZLED_ITEM_CACHE_H */ |
99 | |
100 | === modified file 'drizzled/item/ref.h' |
101 | --- drizzled/item/ref.h 2009-12-05 23:13:49 +0000 |
102 | +++ drizzled/item/ref.h 2009-12-16 14:56:20 +0000 |
103 | @@ -146,7 +146,10 @@ |
104 | if (ref && result_type() == ROW_RESULT) |
105 | (*ref)->bring_value(); |
106 | } |
107 | - |
108 | + bool basic_const_item() const |
109 | + { |
110 | + return (*ref)->basic_const_item(); |
111 | + } |
112 | }; |
113 | |
114 | #endif /* DRIZZLED_ITEM_REF_H */ |
115 | |
116 | === modified file 'drizzled/join.cc' |
117 | --- drizzled/join.cc 2009-12-07 19:18:46 +0000 |
118 | +++ drizzled/join.cc 2009-12-16 14:56:20 +0000 |
119 | @@ -621,6 +621,7 @@ |
120 | { |
121 | conds=new Item_int((int64_t) 0,1); // Always false |
122 | } |
123 | + |
124 | if (make_join_select(this, select, conds)) |
125 | { |
126 | zero_result_cause= |
127 | @@ -828,6 +829,9 @@ |
128 | if (setup_subquery_materialization()) |
129 | return 1; |
130 | |
131 | + /* Cache constant expressions in WHERE, HAVING, ON clauses. */ |
132 | + cache_const_exprs(); |
133 | + |
134 | /* |
135 | is this simple IN subquery? |
136 | */ |
137 | @@ -2379,6 +2383,40 @@ |
138 | } |
139 | |
140 | /** |
141 | + Cache constant expressions in WHERE, HAVING, ON conditions. |
142 | +*/ |
143 | + |
144 | +void JOIN::cache_const_exprs() |
145 | +{ |
146 | + bool cache_flag= false; |
147 | + bool *analyzer_arg= &cache_flag; |
148 | + |
149 | + /* No need in cache if all tables are constant. */ |
150 | + if (const_tables == tables) |
151 | + return; |
152 | + |
153 | + if (conds) |
154 | + conds->compile(&Item::cache_const_expr_analyzer, (unsigned char **)&analyzer_arg, |
155 | + &Item::cache_const_expr_transformer, (unsigned char *)&cache_flag); |
156 | + cache_flag= false; |
157 | + if (having) |
158 | + having->compile(&Item::cache_const_expr_analyzer, (unsigned char **)&analyzer_arg, |
159 | + &Item::cache_const_expr_transformer, (unsigned char *)&cache_flag); |
160 | + |
161 | + for (JoinTable *tab= join_tab + const_tables; tab < join_tab + tables ; tab++) |
162 | + { |
163 | + if (*tab->on_expr_ref) |
164 | + { |
165 | + cache_flag= false; |
166 | + (*tab->on_expr_ref)->compile(&Item::cache_const_expr_analyzer, |
167 | + (unsigned char **)&analyzer_arg, |
168 | + &Item::cache_const_expr_transformer, |
169 | + (unsigned char *)&cache_flag); |
170 | + } |
171 | + } |
172 | +} |
173 | + |
174 | +/** |
175 | @brief |
176 | |
177 | Process one record of the nested loop join. |
178 | |
179 | === modified file 'drizzled/join.h' |
180 | --- drizzled/join.h 2009-12-07 19:18:46 +0000 |
181 | +++ drizzled/join.h 2009-12-16 14:56:20 +0000 |
182 | @@ -472,6 +472,8 @@ |
183 | sizeof(drizzled::optimizer::Position) * size); |
184 | } |
185 | |
186 | + void cache_const_exprs(); |
187 | + |
188 | /** |
189 | * @param[in] index the index of the position to retrieve |
190 | * @return a reference to the specified position in the optimal |
191 | |
192 | === modified file 'tests/r/errors.result' |
193 | --- tests/r/errors.result 2008-12-16 07:07:50 +0000 |
194 | +++ tests/r/errors.result 2009-12-16 14:56:20 +0000 |
195 | @@ -30,17 +30,20 @@ |
196 | CREATE TABLE t1 (a INT); |
197 | SELECT a FROM t1 WHERE a IN(1, (SELECT IF(1=0,1,2/0))); |
198 | a |
199 | +Warnings: |
200 | +Error 1365 Division by 0 |
201 | INSERT INTO t1 VALUES(1); |
202 | SELECT a FROM t1 WHERE a IN(1, (SELECT IF(1=0,1,2/0))); |
203 | a |
204 | 1 |
205 | +Warnings: |
206 | +Error 1365 Division by 0 |
207 | INSERT INTO t1 VALUES(2),(3); |
208 | SELECT a FROM t1 WHERE a IN(1, (SELECT IF(1=0,1,2/0))); |
209 | a |
210 | 1 |
211 | Warnings: |
212 | Error 1365 Division by 0 |
213 | -Error 1365 Division by 0 |
214 | DROP TABLE t1; |
215 | CREATE TABLE t1( a INT ); |
216 | SELECT b FROM t1; |
217 | |
218 | === modified file 'tests/r/func_in.result' |
219 | --- tests/r/func_in.result 2009-07-27 23:00:57 +0000 |
220 | +++ tests/r/func_in.result 2009-12-16 14:56:20 +0000 |
221 | @@ -460,7 +460,6 @@ |
222 | id |
223 | Warnings: |
224 | Error 1365 Division by 0 |
225 | -Error 1365 Division by 0 |
226 | DROP TABLE t1; |
227 | End of 5.0 tests |
228 | create TEMPORARY table t1(f1 char(1)) ENGINE=MYISAM; |
229 | |
230 | === modified file 'tests/r/func_like.result' |
231 | --- tests/r/func_like.result 2008-12-06 22:41:03 +0000 |
232 | +++ tests/r/func_like.result 2009-12-16 14:56:20 +0000 |
233 | @@ -10,7 +10,7 @@ |
234 | id select_type table type possible_keys key key_len ref rows filtered Extra |
235 | 1 SIMPLE t1 index a a 43 NULL 5 40.00 Using where; Using index |
236 | Warnings: |
237 | -Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` like concat('abc','%')) |
238 | +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` like <cache>(concat('abc','%'))) |
239 | select * from t1 where a like "abc%"; |
240 | a |
241 | abc |
242 | |
243 | === modified file 'tests/r/func_str.result' |
244 | --- tests/r/func_str.result 2009-10-31 18:12:20 +0000 |
245 | +++ tests/r/func_str.result 2009-12-16 14:56:20 +0000 |
246 | @@ -847,7 +847,7 @@ |
247 | 1 SIMPLE t2 const PRIMARY PRIMARY 42 const 1 100.00 Using index |
248 | 1 SIMPLE t1 ref code code 43 const 2 100.00 Using where; Using index |
249 | Warnings: |
250 | -Note 1003 select `test`.`t1`.`code` AS `code`,'a12' AS `id` from `test`.`t1` join `test`.`t2` where ((`test`.`t1`.`code` = 'a12') and ('a12' = 'a12') and ((length(`test`.`t1`.`code`) = 5) or ('a12' < 'a00'))) |
251 | +Note 1003 select `test`.`t1`.`code` AS `code`,'a12' AS `id` from `test`.`t1` join `test`.`t2` where ((`test`.`t1`.`code` = 'a12') and <cache>(('a12' = 'a12')) and ((length(`test`.`t1`.`code`) = 5) or <cache>(('a12' < 'a00')))) |
252 | DROP TABLE t1,t2; |
253 | select benchmark(-1, 1); |
254 | benchmark(-1, 1) |
255 | |
256 | === modified file 'tests/r/func_time.result' |
257 | --- tests/r/func_time.result 2009-06-16 00:53:22 +0000 |
258 | +++ tests/r/func_time.result 2009-12-16 14:56:20 +0000 |
259 | @@ -714,7 +714,6 @@ |
260 | f1 |
261 | Warnings: |
262 | Warning 1292 Incorrect datetime value: 'zzz' |
263 | -Warning 1292 Incorrect datetime value: 'zzz' |
264 | select f1 from t1 where makedate(2006,1) between date(f1) and date(f3); |
265 | f1 |
266 | 2006-01-01 |
267 | |
268 | === modified file 'tests/r/select.result' |
269 | --- tests/r/select.result 2009-11-13 00:56:59 +0000 |
270 | +++ tests/r/select.result 2009-12-16 14:56:20 +0000 |
271 | @@ -3661,3 +3661,46 @@ |
272 | 4 |
273 | 5 |
274 | DROP TABLE t1; |
275 | +CREATE TABLE t1 (a INT); |
276 | +INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10); |
277 | +CREATE TABLE t2 (b INT); |
278 | +INSERT INTO t2 VALUES (2); |
279 | +SELECT * FROM t1 WHERE a = 1 + 1; |
280 | +a |
281 | +2 |
282 | +EXPLAIN EXTENDED SELECT * FROM t1 WHERE a = 1 + 1; |
283 | +id select_type table type possible_keys key key_len ref rows filtered Extra |
284 | +1 SIMPLE t1 ALL NULL NULL NULL NULL 10 100.00 Using where |
285 | +Warnings: |
286 | +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` = <cache>((1 + 1))) |
287 | +SELECT * FROM t1 HAVING a = 1 + 1; |
288 | +a |
289 | +2 |
290 | +EXPLAIN EXTENDED SELECT * FROM t1 HAVING a = 1 + 1; |
291 | +id select_type table type possible_keys key key_len ref rows filtered Extra |
292 | +1 SIMPLE t1 ALL NULL NULL NULL NULL 10 100.00 |
293 | +Warnings: |
294 | +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` having (`test`.`t1`.`a` = <cache>((1 + 1))) |
295 | +SELECT * FROM t1, t2 WHERE a = b + (1 + 1); |
296 | +a b |
297 | +4 2 |
298 | +EXPLAIN EXTENDED SELECT * FROM t1, t2 WHERE a = b + (1 + 1); |
299 | +id select_type table type possible_keys key key_len ref rows filtered Extra |
300 | +1 SIMPLE t2 ALL NULL NULL NULL NULL 1 100.00 |
301 | +1 SIMPLE t1 ALL NULL NULL NULL NULL 10 100.00 Using where; Using join buffer |
302 | +Warnings: |
303 | +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` join `test`.`t2` where (`test`.`t1`.`a` = (`test`.`t2`.`b` + <cache>((1 + 1)))) |
304 | +SELECT * FROM t2 LEFT JOIN t1 ON a = b + 1; |
305 | +b a |
306 | +2 3 |
307 | +EXPLAIN EXTENDED SELECT * FROM t2 LEFT JOIN t1 ON a = b + 1; |
308 | +id select_type table type possible_keys key key_len ref rows filtered Extra |
309 | +1 SIMPLE t2 ALL NULL NULL NULL NULL 1 100.00 |
310 | +1 SIMPLE t1 ALL NULL NULL NULL NULL 10 100.00 |
311 | +Warnings: |
312 | +Note 1003 select `test`.`t2`.`b` AS `b`,`test`.`t1`.`a` AS `a` from `test`.`t2` left join `test`.`t1` on((`test`.`t1`.`a` = (`test`.`t2`.`b` + 1))) where 1 |
313 | +EXPLAIN EXTENDED SELECT * FROM t1 WHERE a > UNIX_TIMESTAMP('2009-03-10 00:00:00'); |
314 | +id select_type table type possible_keys key key_len ref rows filtered Extra |
315 | +1 SIMPLE t1 ALL NULL NULL NULL NULL 10 100.00 Using where |
316 | +Warnings: |
317 | +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` > <cache>(unix_timestamp('2009-03-10 00:00:00'))) |
318 | |
319 | === modified file 'tests/r/subselect.result' |
320 | --- tests/r/subselect.result 2009-11-19 18:26:26 +0000 |
321 | +++ tests/r/subselect.result 2009-12-16 14:56:20 +0000 |
322 | @@ -720,7 +720,7 @@ |
323 | Warnings: |
324 | Note 1249 Select 3 was reduced during optimization |
325 | Note 1249 Select 2 was reduced during optimization |
326 | -Note 1003 select `test`.`t2`.`id` AS `id` from `test`.`t2` where (`test`.`t2`.`id` = (1 + 1)) |
327 | +Note 1003 select `test`.`t2`.`id` AS `id` from `test`.`t2` where (`test`.`t2`.`id` = <cache>((1 + 1))) |
328 | EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1 UNION SELECT 3); |
329 | id select_type table type possible_keys key key_len ref rows filtered Extra |
330 | 1 PRIMARY t2 index NULL id 5 NULL 2 100.00 Using where; Using index |
331 | |
332 | === modified file 'tests/r/subselect_no_mat.result' |
333 | --- tests/r/subselect_no_mat.result 2009-11-19 18:26:26 +0000 |
334 | +++ tests/r/subselect_no_mat.result 2009-12-16 14:56:20 +0000 |
335 | @@ -724,7 +724,7 @@ |
336 | Warnings: |
337 | Note 1249 Select 3 was reduced during optimization |
338 | Note 1249 Select 2 was reduced during optimization |
339 | -Note 1003 select `test`.`t2`.`id` AS `id` from `test`.`t2` where (`test`.`t2`.`id` = (1 + 1)) |
340 | +Note 1003 select `test`.`t2`.`id` AS `id` from `test`.`t2` where (`test`.`t2`.`id` = <cache>((1 + 1))) |
341 | EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1 UNION SELECT 3); |
342 | id select_type table type possible_keys key key_len ref rows filtered Extra |
343 | 1 PRIMARY t2 index NULL id 5 NULL 2 100.00 Using where; Using index |
344 | |
345 | === modified file 'tests/r/subselect_no_mat_and_semi_join.result' |
346 | --- tests/r/subselect_no_mat_and_semi_join.result 2009-11-19 18:26:26 +0000 |
347 | +++ tests/r/subselect_no_mat_and_semi_join.result 2009-12-16 14:56:20 +0000 |
348 | @@ -724,7 +724,7 @@ |
349 | Warnings: |
350 | Note 1249 Select 3 was reduced during optimization |
351 | Note 1249 Select 2 was reduced during optimization |
352 | -Note 1003 select `test`.`t2`.`id` AS `id` from `test`.`t2` where (`test`.`t2`.`id` = (1 + 1)) |
353 | +Note 1003 select `test`.`t2`.`id` AS `id` from `test`.`t2` where (`test`.`t2`.`id` = <cache>((1 + 1))) |
354 | EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1 UNION SELECT 3); |
355 | id select_type table type possible_keys key key_len ref rows filtered Extra |
356 | 1 PRIMARY t2 index NULL id 5 NULL 2 100.00 Using where; Using index |
357 | |
358 | === modified file 'tests/r/subselect_no_opts.result' |
359 | --- tests/r/subselect_no_opts.result 2009-11-19 18:26:26 +0000 |
360 | +++ tests/r/subselect_no_opts.result 2009-12-16 14:56:20 +0000 |
361 | @@ -724,7 +724,7 @@ |
362 | Warnings: |
363 | Note 1249 Select 3 was reduced during optimization |
364 | Note 1249 Select 2 was reduced during optimization |
365 | -Note 1003 select `test`.`t2`.`id` AS `id` from `test`.`t2` where (`test`.`t2`.`id` = (1 + 1)) |
366 | +Note 1003 select `test`.`t2`.`id` AS `id` from `test`.`t2` where (`test`.`t2`.`id` = <cache>((1 + 1))) |
367 | EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1 UNION SELECT 3); |
368 | id select_type table type possible_keys key key_len ref rows filtered Extra |
369 | 1 PRIMARY t2 index NULL id 5 NULL 2 100.00 Using where; Using index |
370 | |
371 | === modified file 'tests/r/subselect_no_semijoin.result' |
372 | --- tests/r/subselect_no_semijoin.result 2009-11-19 18:26:26 +0000 |
373 | +++ tests/r/subselect_no_semijoin.result 2009-12-16 14:56:20 +0000 |
374 | @@ -724,7 +724,7 @@ |
375 | Warnings: |
376 | Note 1249 Select 3 was reduced during optimization |
377 | Note 1249 Select 2 was reduced during optimization |
378 | -Note 1003 select `test`.`t2`.`id` AS `id` from `test`.`t2` where (`test`.`t2`.`id` = (1 + 1)) |
379 | +Note 1003 select `test`.`t2`.`id` AS `id` from `test`.`t2` where (`test`.`t2`.`id` = <cache>((1 + 1))) |
380 | EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1 UNION SELECT 3); |
381 | id select_type table type possible_keys key key_len ref rows filtered Extra |
382 | 1 PRIMARY t2 index NULL id 5 NULL 2 100.00 Using where; Using index |
383 | |
384 | === modified file 'tests/t/select.test' |
385 | --- tests/t/select.test 2009-11-13 00:56:59 +0000 |
386 | +++ tests/t/select.test 2009-12-16 14:56:20 +0000 |
387 | @@ -3454,3 +3454,20 @@ |
388 | SELECT a FROM t1 ORDER BY a LIMIT 2,4294967296; |
389 | SELECT a FROM t1 ORDER BY a LIMIT 2,4294967297; |
390 | DROP TABLE t1; |
391 | + |
392 | +# |
393 | +# MySQL Bug#33546: Slowdown on re-evaluation of constant expressions. |
394 | +# |
395 | +CREATE TABLE t1 (a INT); |
396 | +INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10); |
397 | +CREATE TABLE t2 (b INT); |
398 | +INSERT INTO t2 VALUES (2); |
399 | +SELECT * FROM t1 WHERE a = 1 + 1; |
400 | +EXPLAIN EXTENDED SELECT * FROM t1 WHERE a = 1 + 1; |
401 | +SELECT * FROM t1 HAVING a = 1 + 1; |
402 | +EXPLAIN EXTENDED SELECT * FROM t1 HAVING a = 1 + 1; |
403 | +SELECT * FROM t1, t2 WHERE a = b + (1 + 1); |
404 | +EXPLAIN EXTENDED SELECT * FROM t1, t2 WHERE a = b + (1 + 1); |
405 | +SELECT * FROM t2 LEFT JOIN t1 ON a = b + 1; |
406 | +EXPLAIN EXTENDED SELECT * FROM t2 LEFT JOIN t1 ON a = b + 1; |
407 | +EXPLAIN EXTENDED SELECT * FROM t1 WHERE a > UNIX_TIMESTAMP('2009-03-10 00:00:00'); |
408 | \ No newline at end of file |
This ports the fixes for MySQL Bug#33546 to drizzle. Test-suite passes.
One small thing I've noticed is that in the test result file this query
EXPLAIN EXTENDED SELECT * FROM t2 LEFT JOIN t1 ON a = b + 1;
leads to different results in drizzle and mysql:
MySQL:
EXPLAIN EXTENDED SELECT * FROM t2 LEFT JOIN t1 ON a = b + 1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t2 system NULL NULL NULL NULL 1 100.00
1 SIMPLE t1 ALL NULL NULL NULL NULL 10 100.00 Using where
Warnings:
Note 1003 select '2' AS `b`,`test`.`t1`.`a` AS `a` from `test`.`t1` where 1
Drizzle:
EXPLAIN EXTENDED SELECT * FROM t2 LEFT JOIN t1 ON a = b + 1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t2 ALL NULL NULL NULL NULL 1 100.00
1 SIMPLE t1 ALL NULL NULL NULL NULL 10 100.00
Warnings:
Note 1003 select `test`.`t2`.`b` AS `b`,`test`.`t1`.`a` AS `a` from `test`.`t2` left join `test`.`t1` on((`test`.`t1`.`a` = (`test`.`t2`.`b` + 1))) where 1
I don't think that's because of my changes but please double check. Jay said on IRC: "might be some constant propogation in the optimizer that is not in Drizzle. I wouldn't worry about it too much. The case that is being optimized is pretty minimal."