1 /* Copyright (c) 2002, 2020, Oracle and/or its affiliates. All rights reserved.
2
3 This program is free software; you can redistribute it and/or modify
4 it under the terms of the GNU General Public License, version 2.0,
5 as published by the Free Software Foundation.
6
7 This program is also distributed with certain software (including
8 but not limited to OpenSSL) that is licensed under separate terms,
9 as designated in a particular file or component or in included license
10 documentation. The authors of MySQL hereby grant you an additional
11 permission to link the program and your derivative works with the
12 separately licensed software that they have included with MySQL.
13
14 This program is distributed in the hope that it will be useful,
15 but WITHOUT ANY WARRANTY; without even the implied warranty of
16 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
17 GNU General Public License, version 2.0, for more details.
18
19 You should have received a copy of the GNU General Public License
20 along with this program; if not, write to the Free Software Foundation,
21 51 Franklin Street, Suite 500, Boston, MA 02110-1335 USA */
22
23
24 /*
25 Derived tables
26 These were introduced by Sinisa <sinisa@mysql.com>
27 */
28
29
30 #include "my_global.h" /* NO_EMBEDDED_ACCESS_CHECKS */
31 #include "sql_derived.h"
32 #include "sql_select.h"
33 #include "sql_resolver.h"
34 #include "sql_optimizer.h" // JOIN
35 #include "sql_view.h" // check_duplicate_names
36 #include "auth_common.h" // SELECT_ACL
37 #include "sql_tmp_table.h" // Tmp tables
38 #include "sql_union.h" // Query_result_union
39 #include "opt_trace.h" // opt_trace_disable_etc
40
41
42 /**
43 Resolve a derived table or view reference, including recursively resolving
44 contained subqueries.
45
46 @param thd thread handle
47 @param apply_semijoin Apply possible semi-join transforms if this is true
48
49 @returns false if success, true if error
50 */
51
resolve_derived(THD * thd,bool apply_semijoin)52 bool TABLE_LIST::resolve_derived(THD *thd, bool apply_semijoin)
53 {
54 DBUG_ENTER("TABLE_LIST::resolve_derived");
55
56 if (!is_view_or_derived() || is_merged())
57 DBUG_RETURN(false);
58
59 const bool derived_tables_saved= thd->derived_tables_processing;
60
61 thd->derived_tables_processing= true;
62
63 #ifndef DBUG_OFF
64 for (SELECT_LEX *sl= derived->first_select(); sl; sl= sl->next_select())
65 {
66 // Make sure there are no outer references
67 DBUG_ASSERT(sl->context.outer_context == NULL);
68 }
69 #endif
70 if (!(derived_result= new (thd->mem_root) Query_result_union))
71 DBUG_RETURN(true);
72
73 /*
74 Prepare the underlying query expression of the derived table.
75 The SELECT_STRAIGHT_JOIN option prevents semi-join transformation.
76 */
77 if (derived->prepare(thd, derived_result,
78 !apply_semijoin ? SELECT_NO_SEMI_JOIN : 0, 0))
79 DBUG_RETURN(true);
80
81 if (check_duplicate_names(derived->types, 0))
82 DBUG_RETURN(true);
83
84 #ifndef NO_EMBEDDED_ACCESS_CHECKS
85 /*
86 A derived table is transparent with respect to privilege checking.
87 This setting means that privilege checks ignore the derived table
88 and are done properly in underlying base tables and views.
89 SELECT_ACL is used because derived tables cannot be used for update,
90 delete or insert.
91 */
92 if (is_derived())
93 set_privileges(SELECT_ACL);
94 #endif
95
96 thd->derived_tables_processing= derived_tables_saved;
97
98 DBUG_RETURN(false);
99 }
100
101
102 /**
103 Prepare a derived table or view for materialization.
104
105 @param thd THD pointer
106
107 @return false if successful, true if error
108 */
setup_materialized_derived(THD * thd)109 bool TABLE_LIST::setup_materialized_derived(THD *thd)
110
111 {
112 DBUG_ENTER("TABLE_LIST::setup_materialized_derived");
113
114 DBUG_ASSERT(is_view_or_derived() && !is_merged() && table == NULL);
115
116 DBUG_PRINT("info", ("algorithm: TEMPORARY TABLE"));
117
118 Opt_trace_context *const trace= &thd->opt_trace;
119 Opt_trace_object trace_wrapper(trace);
120 Opt_trace_object trace_derived(trace, is_view() ? "view" : "derived");
121 trace_derived.add_utf8_table(this).
122 add("select#", derived->first_select()->select_number).
123 add("materialized", true);
124
125 set_uses_materialization();
126
127 // Create the result table for the materialization
128 const ulonglong create_options= derived->first_select()->active_options() |
129 TMP_TABLE_ALL_COLUMNS;
130 if (derived_result->create_result_table(thd, &derived->types, false,
131 create_options,
132 alias, false, false))
133 DBUG_RETURN(true); /* purecov: inspected */
134
135 table= derived_result->table;
136 table->pos_in_table_list= this;
137
138 // Make table's name same as the underlying materialized table
139 set_name_temporary();
140
141 table->s->tmp_table= NON_TRANSACTIONAL_TMP_TABLE;
142 #ifndef NO_EMBEDDED_ACCESS_CHECKS
143 if (referencing_view)
144 table->grant= grant;
145 else
146 table->grant.privilege= SELECT_ACL;
147 #endif
148
149 // Table is "nullable" if inner table of an outer_join
150 if (is_inner_table_of_outer_join())
151 table->set_nullable();
152
153 // Add new temporary table to list of open derived tables
154 table->next= thd->derived_tables;
155 thd->derived_tables= table;
156
157 for (SELECT_LEX *sl= derived->first_select(); sl; sl= sl->next_select())
158 {
159 /*
160 Derived tables/view are materialized prior to UPDATE, thus we can skip
161 them from table uniqueness check
162 */
163 sl->propagate_unique_test_exclusion();
164
165 /*
166 SELECT privilege is needed for all materialized derived tables and views,
167 and columns must be marked for read, unless command is SHOW FIELDS.
168 */
169 if (thd->lex->sql_command == SQLCOM_SHOW_FIELDS)
170 continue;
171
172 if (sl->check_view_privileges(thd, SELECT_ACL, SELECT_ACL))
173 DBUG_RETURN(true);
174
175 // Set all selected fields to be read:
176 // @todo Do not set fields that are not referenced from outer query
177 DBUG_ASSERT(thd->mark_used_columns == MARK_COLUMNS_READ);
178 List_iterator<Item> it(sl->all_fields);
179 Item *item;
180 Column_privilege_tracker tracker(thd, SELECT_ACL);
181 Mark_field mf(thd->mark_used_columns);
182 while ((item= it++))
183 {
184 if (item->walk(&Item::check_column_privileges, Item::WALK_PREFIX,
185 (uchar *)thd))
186 DBUG_RETURN(true);
187 item->walk(&Item::mark_field_in_map, Item::WALK_POSTFIX, (uchar *)&mf);
188 }
189 }
190
191 DBUG_RETURN(false);
192 }
193
194
195 /**
196 Optimize the query expression representing a derived table/view.
197
198 @note
199 If optimizer finds out that the derived table/view is of the type
200 "SELECT a_constant" this functions also materializes it.
201
202 @param thd thread handle
203
204 @returns false if success, true if error.
205 */
206
optimize_derived(THD * thd)207 bool TABLE_LIST::optimize_derived(THD *thd)
208 {
209 DBUG_ENTER("TABLE_LIST::optimize_derived");
210
211 SELECT_LEX_UNIT *const unit= derived_unit();
212
213 DBUG_ASSERT(unit && !unit->is_optimized());
214
215 if (unit->optimize(thd) || thd->is_error())
216 DBUG_RETURN(true);
217
218 if (materializable_is_const() &&
219 (create_derived(thd) || materialize_derived(thd)))
220 DBUG_RETURN(true);
221
222 DBUG_RETURN(false);
223 }
224
225
226 /**
227 Create result table for a materialized derived table/view.
228
229 @param thd thread handle
230
231 This function actually creates the result table for given 'derived'
232 table/view, but it doesn't fill it.
233
234 @returns false if success, true if error.
235 */
236
create_derived(THD * thd)237 bool TABLE_LIST::create_derived(THD *thd)
238 {
239 DBUG_ENTER("TABLE_LIST::create_derived");
240
241 SELECT_LEX_UNIT *const unit= derived_unit();
242
243 // @todo: Be able to assert !table->is_created() as well
244 DBUG_ASSERT(unit && uses_materialization() && table);
245
246 /*
247 Don't create result table if:
248 1) Table is already created, or
249 2) Table is a constant one with all NULL values.
250 */
251 if (table->is_created() || // 1
252 (select_lex->join != NULL && // 2
253 (select_lex->join->const_table_map & map()))) // 2
254 {
255 /*
256 At this point, JT_CONST derived tables should be null rows. Otherwise
257 they would have been materialized already.
258 */
259 #ifndef DBUG_OFF
260 if (table != NULL)
261 {
262 QEP_TAB *tab= table->reginfo.qep_tab;
263 DBUG_ASSERT(tab == NULL ||
264 tab->type() != JT_CONST ||
265 table->has_null_row());
266 }
267 #endif
268 DBUG_RETURN(false);
269 }
270 /* create tmp table */
271 Query_result_union *result= (Query_result_union*)unit->query_result();
272
273 if (instantiate_tmp_table(table, table->key_info,
274 result->tmp_table_param.start_recinfo,
275 &result->tmp_table_param.recinfo,
276 unit->first_select()->active_options() |
277 thd->lex->select_lex->active_options() |
278 TMP_TABLE_ALL_COLUMNS,
279 thd->variables.big_tables, &thd->opt_trace))
280 DBUG_RETURN(true); /* purecov: inspected */
281
282 table->file->extra(HA_EXTRA_WRITE_CACHE);
283 table->file->extra(HA_EXTRA_IGNORE_DUP_KEY);
284
285 table->set_created();
286
287 DBUG_RETURN(false);
288 }
289
290
291 /**
292 Materialize derived table
293
294 @param thd Thread handle
295
296 Derived table is resolved with temporary table. It is created based on the
297 queries defined. After temporary table is materialized, if this is not
298 EXPLAIN, then the entire unit / node is deleted. unit is deleted if UNION is
299 used for derived table and node is deleted is it is a simple SELECT.
300 If you use this function, make sure it's not called at prepare.
301 Due to evaluation of LIMIT clause it can not be used at prepared stage.
302
303 @returns false if success, true if error.
304 */
305
materialize_derived(THD * thd)306 bool TABLE_LIST::materialize_derived(THD *thd)
307 {
308 DBUG_ENTER("TABLE_LIST::materialize_derived");
309
310 DBUG_ASSERT(is_view_or_derived() && uses_materialization());
311
312 SELECT_LEX_UNIT *const unit= derived_unit();
313 bool res= false;
314
315 DBUG_ASSERT(table && table->is_created());
316
317 if (unit->is_union())
318 {
319 // execute union without clean up
320 res= unit->execute(thd);
321 }
322 else
323 {
324 SELECT_LEX *first_select= unit->first_select();
325 JOIN *join= first_select->join;
326 SELECT_LEX *save_current_select= thd->lex->current_select();
327 thd->lex->set_current_select(first_select);
328
329 DBUG_ASSERT(join && join->is_optimized());
330
331 unit->set_limit(first_select);
332
333 join->exec();
334 res= join->error;
335 thd->lex->set_current_select(save_current_select);
336 }
337
338 if (!res)
339 {
340 /*
341 Here we entirely fix both TABLE_LIST and list of SELECT's as
342 there were no derived tables
343 */
344 if (derived_result->flush())
345 res= true; /* purecov: inspected */
346 }
347
348 DBUG_RETURN(res);
349 }
350
351
352 /**
353 Clean up the query expression for a materialized derived table
354 */
355
cleanup_derived()356 bool TABLE_LIST::cleanup_derived()
357 {
358 DBUG_ASSERT(is_view_or_derived() && uses_materialization());
359
360 return derived_unit()->cleanup(false);
361 }
362