Merge lp:~roccoblues/drizzle/port-constants-evalutation-fixes into lp:~drizzle-trunk/drizzle/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
Reviewer Review Type Date Requested Status
Jay Pipes (community) Approve
Review via email: mp+16242@code.launchpad.net
To post a comment you must log in.
Revision history for this message
Dennis Schön (roccoblues) wrote :

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."

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