92
75
UINT64_MAX Error: Could not calculate number of rows
93
76
# Multiplication of number of rows in all tables
95
79
static uint64_t get_exact_record_count(TableList *tables)
98
82
for (TableList *tl= tables; tl; tl= tl->next_leaf)
100
ha_rows tmp= tl->table->cursor->records();
84
ha_rows tmp= tl->table->file->records();
101
85
if ((tmp == HA_POS_ERROR))
103
86
return UINT64_MAX;
111
int optimizer::sum_query(TableList *tables, List<Item> &all_fields, COND *conds)
94
Substitutes constants for some COUNT(), MIN() and MAX() functions.
96
@param tables list of leaves of join table tree
97
@param all_fields All fields to be returned
98
@param conds WHERE clause
101
This function is only called for queries with sum functions and no
107
1 if all items were resolved
109
HA_ERR_KEY_NOT_FOUND on impossible conditions
111
HA_ERR_... if a deadlock or a lock wait timeout happens, for example
114
int opt_sum_query(TableList *tables, List<Item> &all_fields,COND *conds)
113
116
List_iterator_fast<Item> it(all_fields);
114
117
int const_result= 1;
115
bool recalc_const_item= false;
118
bool recalc_const_item= 0;
116
119
uint64_t count= 1;
117
bool is_exact_count= true;
118
bool maybe_exact_count= true;
119
table_map removed_tables= 0;
120
table_map outer_tables= 0;
121
table_map used_tables= 0;
120
bool is_exact_count= true, maybe_exact_count= true;
121
table_map removed_tables= 0, outer_tables= 0, used_tables= 0;
122
122
table_map where_tables= 0;
128
127
where_tables= conds->used_tables();
132
Analyze outer join dependencies, and, if possible, compute the number
130
Analyze outer join dependencies, and, if possible, compute the number
135
133
for (TableList *tl= tables; tl; tl= tl->next_leaf)
137
TableList *embedded= NULL;
138
for (embedded= tl; embedded; embedded= embedded->embedding)
136
for (embedded= tl ; embedded; embedded= embedded->embedding)
140
138
if (embedded->on_expr)
144
/* Don't replace expression on a table that is part of an outer join */
142
/* Don't replace expression on a table that is part of an outer join */
146
144
outer_tables|= tl->table->map;
149
We can't optimise LEFT JOIN in cases where the WHERE condition
150
restricts the table that is used, like in:
151
SELECT MAX(t1.a) FROM t1 LEFT JOIN t2 join-condition
152
WHERE t2.field IS NULL;
147
We can't optimise LEFT JOIN in cases where the WHERE condition
148
restricts the table that is used, like in:
149
SELECT MAX(t1.a) FROM t1 LEFT JOIN t2 join-condition
150
WHERE t2.field IS NULL;
154
152
if (tl->table->map & where_tables)
159
156
used_tables|= tl->table->map;
163
If the storage manager of 'tl' gives exact row count as part of
164
statistics (cheap), compute the total number of rows. If there are
165
no outer table dependencies, this count may be used as the real count.
166
Schema tables are filled after this function is invoked, so we can't
169
if (! (tl->table->cursor->getEngine()->check_flag(HTON_BIT_STATS_RECORDS_IS_EXACT)))
159
If the storage manager of 'tl' gives exact row count as part of
160
statistics (cheap), compute the total number of rows. If there are
161
no outer table dependencies, this count may be used as the real count.
162
Schema tables are filled after this function is invoked, so we can't
165
if (!(tl->table->file->ha_table_flags() & HA_STATS_RECORDS_IS_EXACT) ||
171
maybe_exact_count&= test(tl->table->cursor->getEngine()->check_flag(HTON_BIT_HAS_RECORDS));
168
maybe_exact_count&= test(!tl->schema_table &&
169
(tl->table->file->ha_table_flags() &
172
171
is_exact_count= false;
173
count= 1; // ensure count != 0
172
count= 1; // ensure count != 0
177
error= tl->table->cursor->info(HA_STATUS_VARIABLE | HA_STATUS_NO_LOCK);
176
error= tl->table->file->info(HA_STATUS_VARIABLE | HA_STATUS_NO_LOCK);
180
tl->table->print_error(error, MYF(ME_FATALERROR));
179
tl->table->file->print_error(error, MYF(ME_FATALERROR));
183
count*= tl->table->cursor->stats.records;
182
count*= tl->table->file->stats.records;
188
Iterate through all items in the SELECT clause and replace
189
COUNT(), MIN() and MAX() with constants (if possible).
187
Iterate through all items in the SELECT clause and replace
188
COUNT(), MIN() and MAX() with constants (if possible).
192
191
while ((item= it++))
194
193
if (item->type() == Item::SUM_FUNC_ITEM)
196
195
Item_sum *item_sum= (((Item_sum*) item));
197
switch (item_sum->sum_func())
196
switch (item_sum->sum_func()) {
197
case Item_sum::COUNT_FUNC:
199
If the expr in COUNT(expr) can never be null we can change this
200
to the number of rows in the tables if this number is exact and
201
there are no outer joins.
203
if (!conds && !((Item_sum_count*) item)->args[0]->maybe_null &&
204
!outer_tables && maybe_exact_count)
208
if ((count= get_exact_record_count(tables)) == UINT64_MAX)
210
/* Error from handler in counting rows. Don't optimize count() */
214
is_exact_count= 1; // count is now exact
216
((Item_sum_count*) item)->make_const((int64_t) count);
217
recalc_const_item= 1;
222
case Item_sum::MIN_FUNC:
199
case Item_sum::COUNT_FUNC:
201
If the expr in COUNT(expr) can never be null we can change this
202
to the number of rows in the tables if this number is exact and
203
there are no outer joins.
205
if (! conds && ! ((Item_sum_count*) item)->args[0]->maybe_null &&
206
! outer_tables && maybe_exact_count)
208
if (! is_exact_count)
225
If MIN(expr) is the first part of a key or if all previous
226
parts of the key is found in the COND, then we can use
227
indexes to find the key.
229
Item *expr=item_sum->args[0];
230
if (expr->real_item()->type() == Item::FIELD_ITEM)
232
unsigned char key_buff[MAX_KEY_LENGTH];
234
uint32_t range_fl, prefix_len;
236
ref.key_buff= key_buff;
237
Item_field *item_field= (Item_field*) (expr->real_item());
238
Table *table= item_field->field->table;
241
Look for a partial key that can be used for optimization.
242
If we succeed, ref.key_length will contain the length of
243
this key, while prefix_len will contain the length of
244
the beginning of this key without field used in MIN().
245
Type of range for the key part for this field will be
246
returned in range_fl.
248
if (table->file->inited || (outer_tables & table->map) ||
249
!find_key_for_maxmin(0, &ref, item_field->field, conds,
250
&range_fl, &prefix_len))
255
error= table->file->ha_index_init((uint) ref.key, 1);
258
error= table->file->index_first(table->record[0]);
262
Use index to replace MIN/MAX functions with their values
263
according to the following rules:
265
1) Insert the minimum non-null values where the WHERE clause still
267
2) a NULL value if there are only NULL values for key_part_k.
268
3) Fail, producing a row of nulls
270
Implementation: Read the smallest value using the search key. If
271
the interval is open, read the next value after the search
272
key. If read fails, and we're looking for a MIN() value for a
273
nullable column, test if there is an exact match for the key.
275
if (!(range_fl & NEAR_MIN))
277
Closed interval: Either The MIN argument is non-nullable, or
278
we have a >= predicate for the MIN argument.
280
error= table->file->index_read_map(table->record[0],
282
make_prev_keypart_map(ref.key_parts),
283
HA_READ_KEY_OR_NEXT);
210
if ((count= get_exact_record_count(tables)) == UINT64_MAX)
287
Open interval: There are two cases:
288
1) We have only MIN() and the argument column is nullable, or
289
2) there is a > predicate on it, nullability is irrelevant.
290
We need to scan the next bigger record first.
292
error= table->file->index_read_map(table->record[0],
294
make_prev_keypart_map(ref.key_parts),
297
If the found record is outside the group formed by the search
298
prefix, or there is no such record at all, check if all
299
records in that group have NULL in the MIN argument
300
column. If that is the case return that NULL.
302
Check if case 1 from above holds. If it does, we should read
305
if (item_field->field->real_maybe_null() &&
306
ref.key_buff[prefix_len] == 1 &&
308
Last keypart (i.e. the argument to MIN) is set to NULL by
309
find_key_for_maxmin only if all other keyparts are bound
310
to constants in a conjunction of equalities. Hence, we
311
can detect this by checking only if the last keypart is
314
(error == HA_ERR_KEY_NOT_FOUND ||
315
key_cmp_if_same(table, ref.key_buff, ref.key, prefix_len)))
212
/* Error from handler in counting rows. Don't optimize count() */
317
assert(item_field->field->real_maybe_null());
318
error= table->file->index_read_map(table->record[0],
320
make_prev_keypart_map(ref.key_parts),
216
is_exact_count= 1; // count is now exact
218
((Item_sum_count*) item)->make_const_count((int64_t) count);
219
recalc_const_item= 1;
325
/* Verify that the read tuple indeed matches the search key */
326
if (!error && reckey_in_range(0, &ref, item_field->field,
327
conds, range_fl, prefix_len))
328
error= HA_ERR_KEY_NOT_FOUND;
332
table->file->extra(HA_EXTRA_NO_KEYREAD);
334
table->file->ha_index_end();
337
if (error == HA_ERR_KEY_NOT_FOUND || error == HA_ERR_END_OF_FILE)
338
return HA_ERR_KEY_NOT_FOUND; // No rows matching WHERE
339
/* HA_ERR_LOCK_DEADLOCK or some other error */
340
table->file->print_error(error, MYF(0));
343
removed_tables|= table->map;
345
else if (!expr->const_item() || !is_exact_count)
348
The optimization is not applicable in both cases:
349
(a) 'expr' is a non-constant expression. Then we can't
350
replace 'expr' by a constant.
351
(b) 'expr' is a costant. According to ANSI, MIN/MAX must return
352
NULL if the query does not return any rows. Thus, if we are not
353
able to determine if the query returns any rows, we can't apply
354
the optimization and replace MIN/MAX with a constant.
361
/* If count == 0, then we know that is_exact_count == true. */
362
((Item_sum_min*) item_sum)->clear(); /* Set to NULL. */
365
((Item_sum_min*) item_sum)->reset(); /* Set to the constant value. */
366
((Item_sum_min*) item_sum)->make_const();
367
recalc_const_item= 1;
370
case Item_sum::MAX_FUNC:
373
If MAX(expr) is the first part of a key or if all previous
374
parts of the key is found in the COND, then we can use
375
indexes to find the key.
377
Item *expr=item_sum->args[0];
378
if (expr->real_item()->type() == Item::FIELD_ITEM)
380
unsigned char key_buff[MAX_KEY_LENGTH];
382
uint32_t range_fl, prefix_len;
384
ref.key_buff= key_buff;
385
Item_field *item_field= (Item_field*) (expr->real_item());
386
Table *table= item_field->field->table;
389
Look for a partial key that can be used for optimization.
390
If we succeed, ref.key_length will contain the length of
391
this key, while prefix_len will contain the length of
392
the beginning of this key without field used in MAX().
393
Type of range for the key part for this field will be
394
returned in range_fl.
396
if (table->file->inited || (outer_tables & table->map) ||
397
!find_key_for_maxmin(1, &ref, item_field->field, conds,
398
&range_fl, &prefix_len))
403
error= table->file->ha_index_init((uint) ref.key, 1);
406
error= table->file->index_last(table->record[0]);
226
case Item_sum::MIN_FUNC:
229
If MIN(expr) is the first part of a key or if all previous
230
parts of the key is found in the COND, then we can use
231
indexes to find the key.
233
Item *expr=item_sum->args[0];
234
if (expr->real_item()->type() == Item::FIELD_ITEM)
236
unsigned char key_buff[MAX_KEY_LENGTH];
237
table_reference_st ref;
238
uint32_t range_fl, prefix_len;
240
ref.key_buff= key_buff;
241
Item_field *item_field= (Item_field*) (expr->real_item());
242
Table *table= item_field->field->table;
245
Look for a partial key that can be used for optimization.
246
If we succeed, ref.key_length will contain the length of
247
this key, while prefix_len will contain the length of
248
the beginning of this key without field used in MIN().
249
Type of range for the key part for this field will be
250
returned in range_fl.
252
if (table->cursor->inited ||
253
(outer_tables & table->map) ||
254
! find_key_for_maxmin(0,
264
error= table->cursor->ha_index_init(static_cast<uint32_t>(ref.key), 1);
266
if (! ref.key_length)
268
error= table->cursor->index_first(table->record[0]);
273
Use index to replace MIN/MAX functions with their values
274
according to the following rules:
276
1) Insert the minimum non-null values where the WHERE clause still
278
2) a NULL value if there are only NULL values for key_part_k.
279
3) Fail, producing a row of nulls
281
Implementation: Read the smallest value using the search key. If
282
the interval is open, read the next value after the search
283
key. If read fails, and we're looking for a MIN() value for a
284
nullable column, test if there is an exact match for the key.
286
if (! (range_fl & NEAR_MIN))
289
Closed interval: Either The MIN argument is non-nullable, or
290
we have a >= predicate for the MIN argument.
292
error= table->cursor->index_read_map(table->record[0],
294
make_prev_keypart_map(ref.key_parts),
295
HA_READ_KEY_OR_NEXT);
300
Open interval: There are two cases:
301
1) We have only MIN() and the argument column is nullable, or
302
2) there is a > predicate on it, nullability is irrelevant.
303
We need to scan the next bigger record first.
305
error= table->cursor->index_read_map(table->record[0],
307
make_prev_keypart_map(ref.key_parts),
310
If the found record is outside the group formed by the search
311
prefix, or there is no such record at all, check if all
312
records in that group have NULL in the MIN argument
313
column. If that is the case return that NULL.
315
Check if case 1 from above holds. If it does, we should read
318
if (item_field->field->real_maybe_null() &&
319
ref.key_buff[prefix_len] == 1 &&
321
Last keypart (i.e. the argument to MIN) is set to NULL by
322
find_key_for_maxmin only if all other keyparts are bound
323
to constants in a conjunction of equalities. Hence, we
324
can detect this by checking only if the last keypart is
327
(error == HA_ERR_KEY_NOT_FOUND ||
328
key_cmp_if_same(table, ref.key_buff, ref.key, prefix_len)))
330
assert(item_field->field->real_maybe_null());
331
error= table->cursor->index_read_map(table->record[0],
333
make_prev_keypart_map(ref.key_parts),
338
/* Verify that the read tuple indeed matches the search key */
347
error= HA_ERR_KEY_NOT_FOUND;
352
table->cursor->extra(HA_EXTRA_NO_KEYREAD);
354
table->cursor->ha_index_end();
357
if (error == HA_ERR_KEY_NOT_FOUND || error == HA_ERR_END_OF_FILE)
359
return HA_ERR_KEY_NOT_FOUND; // No rows matching WHERE
361
/* HA_ERR_LOCK_DEADLOCK or some other error */
362
table->print_error(error, MYF(0));
365
removed_tables|= table->map;
367
else if (! expr->const_item() || ! is_exact_count)
370
The optimization is not applicable in both cases:
371
(a) 'expr' is a non-constant expression. Then we can't
372
replace 'expr' by a constant.
373
(b) 'expr' is a costant. According to ANSI, MIN/MAX must return
374
NULL if the query does not return any rows. Thus, if we are not
375
able to determine if the query returns any rows, we can't apply
376
the optimization and replace MIN/MAX with a constant.
383
/* If count == 0, then we know that is_exact_count == true. */
384
((Item_sum_min*) item_sum)->clear(); /* Set to NULL. */
388
((Item_sum_min*) item_sum)->reset(); /* Set to the constant value. */
390
((Item_sum_min*) item_sum)->make_const();
391
recalc_const_item= 1;
394
case Item_sum::MAX_FUNC:
397
If MAX(expr) is the first part of a key or if all previous
398
parts of the key is found in the COND, then we can use
399
indexes to find the key.
401
Item *expr= item_sum->args[0];
402
if (expr->real_item()->type() == Item::FIELD_ITEM)
404
unsigned char key_buff[MAX_KEY_LENGTH];
405
table_reference_st ref;
406
uint32_t range_fl, prefix_len;
408
ref.key_buff= key_buff;
409
Item_field *item_field= (Item_field*) (expr->real_item());
410
Table *table= item_field->field->table;
413
Look for a partial key that can be used for optimization.
414
If we succeed, ref.key_length will contain the length of
415
this key, while prefix_len will contain the length of
416
the beginning of this key without field used in MAX().
417
Type of range for the key part for this field will be
418
returned in range_fl.
420
if (table->cursor->inited ||
421
(outer_tables & table->map) ||
422
! find_key_for_maxmin(1,
432
error= table->cursor->ha_index_init(static_cast<uint32_t>(ref.key), 1);
434
if (! ref.key_length)
436
error= table->cursor->index_last(table->record[0]);
440
error= table->cursor->index_read_map(table->record[0],
442
make_prev_keypart_map(ref.key_parts),
443
range_fl & NEAR_MAX ?
445
HA_READ_PREFIX_LAST_OR_PREV);
455
error= HA_ERR_KEY_NOT_FOUND;
460
table->cursor->extra(HA_EXTRA_NO_KEYREAD);
462
table->cursor->ha_index_end();
465
if (error == HA_ERR_KEY_NOT_FOUND || error == HA_ERR_END_OF_FILE)
467
return HA_ERR_KEY_NOT_FOUND; // No rows matching WHERE
469
/* HA_ERR_LOCK_DEADLOCK or some other error */
470
table->print_error(error, MYF(ME_FATALERROR));
473
removed_tables|= table->map;
475
else if (! expr->const_item() || ! is_exact_count)
478
The optimization is not applicable in both cases:
479
(a) 'expr' is a non-constant expression. Then we can't
480
replace 'expr' by a constant.
481
(b) 'expr' is a costant. According to ANSI, MIN/MAX must return
482
NULL if the query does not return any rows. Thus, if we are not
483
able to determine if the query returns any rows, we can't apply
484
the optimization and replace MIN/MAX with a constant.
491
/* If count != 1, then we know that is_exact_count == true. */
492
((Item_sum_max*) item_sum)->clear(); /* Set to NULL. */
496
((Item_sum_max*) item_sum)->reset(); /* Set to the constant value. */
498
((Item_sum_max*) item_sum)->make_const();
499
recalc_const_item= 1;
408
error= table->file->index_read_map(table->record[0], key_buff,
409
make_prev_keypart_map(ref.key_parts),
410
range_fl & NEAR_MAX ?
412
HA_READ_PREFIX_LAST_OR_PREV);
413
if (!error && reckey_in_range(1, &ref, item_field->field,
414
conds, range_fl, prefix_len))
415
error= HA_ERR_KEY_NOT_FOUND;
419
table->file->extra(HA_EXTRA_NO_KEYREAD);
421
table->file->ha_index_end();
424
if (error == HA_ERR_KEY_NOT_FOUND || error == HA_ERR_END_OF_FILE)
425
return HA_ERR_KEY_NOT_FOUND; // No rows matching WHERE
426
/* HA_ERR_LOCK_DEADLOCK or some other error */
427
table->file->print_error(error, MYF(ME_FATALERROR));
430
removed_tables|= table->map;
432
else if (!expr->const_item() || !is_exact_count)
435
The optimization is not applicable in both cases:
436
(a) 'expr' is a non-constant expression. Then we can't
437
replace 'expr' by a constant.
438
(b) 'expr' is a costant. According to ANSI, MIN/MAX must return
439
NULL if the query does not return any rows. Thus, if we are not
440
able to determine if the query returns any rows, we can't apply
441
the optimization and replace MIN/MAX with a constant.
448
/* If count != 1, then we know that is_exact_count == true. */
449
((Item_sum_max*) item_sum)->clear(); /* Set to NULL. */
452
((Item_sum_max*) item_sum)->reset(); /* Set to the constant value. */
453
((Item_sum_max*) item_sum)->make_const();
454
recalc_const_item= 1;
507
462
else if (const_result)
509
464
if (recalc_const_item)
511
465
item->update_used_tables();
513
if (! item->const_item())
466
if (!item->const_item())
520
If we have a where clause, we can only ignore searching in the
521
tables if MIN/MAX optimisation replaced all used tables
522
We do not use replaced values in case of:
523
SELECT MIN(key) FROM table_1, empty_table
524
removed_tables is != 0 if we have used MIN() or MAX().
471
If we have a where clause, we can only ignore searching in the
472
tables if MIN/MAX optimisation replaced all used tables
473
We do not use replaced values in case of:
474
SELECT MIN(key) FROM table_1, empty_table
475
removed_tables is != 0 if we have used MIN() or MAX().
526
477
if (removed_tables && used_tables != removed_tables)
528
478
const_result= 0; // We didn't remove all tables
530
479
return const_result;
534
bool optimizer::simple_pred(Item_func *func_item, Item **args, bool &inv_order)
484
Test if the predicate compares a field with constants.
486
@param func_item Predicate item
487
@param[out] args Here we store the field followed by constants
488
@param[out] inv_order Is set to 1 if the predicate is of the form
492
0 func_item is a simple predicate: a field is compared with
498
bool simple_pred(Item_func *func_item, Item **args, bool *inv_order)
538
switch (func_item->argument_count())
502
switch (func_item->argument_count()) {
541
504
/* MULT_EQUAL_FUNC */