~drizzle-trunk/drizzle/development

if ((curr_join->group_list && (!test_if_subpart(curr_join->group_list, curr_join->order) || curr_join->select_distinct)) || (curr_join->select_distinct && curr_join->tmp_table_param.using_indirect_summary_function))

2222

if ((curr_join->group_list && (!test_if_subpart(curr_join->group_list, curr_join->order) || curr_join->select_distinct))

2223

|| (curr_join->select_distinct && curr_join->tmp_table_param.using_indirect_summary_function))

2215

2224

{ /* Must copy to another table */

2216

2225

/* Free first data from old join */

2217

2226

curr_join->join_free();

2218

2227

if (make_simple_join(curr_join, curr_tmp_table))

2219

return;

2228

return;

2220

2229

calc_group_buffer(curr_join, group_list);

2221

2230

count_field_types(select_lex, &curr_join->tmp_table_param,

2222

2231

curr_join->tmp_all_fields1,

2223

2232

curr_join->select_distinct && !curr_join->group_list);

2224

curr_join->tmp_table_param.hidden_field_count=

2225

(curr_join->tmp_all_fields1.elements-

2226

curr_join->tmp_fields_list1.elements);

2227

2228

2233

curr_join->tmp_table_param.hidden_field_count= curr_join->tmp_all_fields1.elements

2234

- curr_join->tmp_fields_list1.elements;

2235

2229

2236

if (exec_tmp_table2)

2230

curr_tmp_table= exec_tmp_table2;

2237

curr_tmp_table= exec_tmp_table2;

2231

2238

else

2232

2239

{

2233

/* group data to new table */

2240

/* group data to new table */

2234

2241

2235

2242

2236

2243

If the access method is loose index scan then all MIN/MAX

2240

2247

if (curr_join->join_tab->is_using_loose_index_scan())

2241

2248

curr_join->tmp_table_param.precomputed_group_by= true;

2242

2249

2243

if (!(curr_tmp_table=

2244

exec_tmp_table2= create_tmp_table(thd,

2245

&curr_join->tmp_table_param,

2246

*curr_all_fields,

2247

(order_st*) 0,

2248

curr_join->select_distinct &&

2249

!curr_join->group_list,

2250

1, curr_join->select_options,

2251

HA_POS_ERROR,

2252

(char *) "")))

2253

return;

2254

curr_join->exec_tmp_table2= exec_tmp_table2;

2250

if (!(curr_tmp_table=

2251

exec_tmp_table2= create_tmp_table(session,

2252

&curr_join->tmp_table_param,

2253

*curr_all_fields,

2254

(order_st*) 0,

2255

curr_join->select_distinct &&

2256

!curr_join->group_list,

2257

1, curr_join->select_options,

2258

HA_POS_ERROR,

2259

(char *) "")))

2260

return;

2261

curr_join->exec_tmp_table2= exec_tmp_table2;

2255

2262

}

2256

2263

if (curr_join->group_list)

2257

2264

{

2258

thd_proc_info(thd, "Creating sort index");

2259

if (curr_join->join_tab == join_tab && save_join_tab())

2260

{

2261

return;

2262

}

2263

if (create_sort_index(thd, curr_join, curr_join->group_list,

2264

HA_POS_ERROR, HA_POS_ERROR, false) ||

2265

make_group_fields(this, curr_join))

2266

{

2267

return;

2268

}

2265

session->set_proc_info("Creating sort index");

2266

if (curr_join->join_tab == join_tab && save_join_tab())

2267

{

2268

return;

2269

}

2270

if (create_sort_index(session, curr_join, curr_join->group_list,

2271

HA_POS_ERROR, HA_POS_ERROR, false) ||

2272

make_group_fields(this, curr_join))

2273

{

2274

return;

2275

}

2269

2276

sortorder= curr_join->sortorder;

2270

2277

}

2271

2272

thd_proc_info(thd, "Copying to group table");

2278

2279

session->set_proc_info("Copying to group table");

2273

2280

tmp_error= -1;

2274

2281

if (curr_join != this)

2275

2282

{

2276

if (sum_funcs2)

2277

{

2278

curr_join->sum_funcs= sum_funcs2;

2279

curr_join->sum_funcs_end= sum_funcs_end2;

2280

}

2281

else

2282

{

2283

curr_join->alloc_func_list();

2284

sum_funcs2= curr_join->sum_funcs;

2285

sum_funcs_end2= curr_join->sum_funcs_end;

2286

}

2283

if (sum_funcs2)

2284

{

2285

curr_join->sum_funcs= sum_funcs2;

2286

curr_join->sum_funcs_end= sum_funcs_end2;

2287

}

2288

else

2289

{

2290

curr_join->alloc_func_list();

2291

sum_funcs2= curr_join->sum_funcs;

2292

sum_funcs_end2= curr_join->sum_funcs_end;

2293

}

2287

2294

}

2288

if (curr_join->make_sum_func_list(*curr_all_fields, *curr_fields_list,

2289

1, true))

2295

if (curr_join->make_sum_func_list(*curr_all_fields, *curr_fields_list, 1, true))

2290

2296

return;

2291

2297

curr_join->group_list= 0;

2292

if (!curr_join->sort_and_group &&

2293

curr_join->const_tables != curr_join->tables)

2298

2299

if (!curr_join->sort_and_group && (curr_join->const_tables != curr_join->tables))

2294

2300

curr_join->join_tab[curr_join->const_tables].sorted= 0;

2295

if (setup_sum_funcs(curr_join->thd, curr_join->sum_funcs) ||

2296

(tmp_error= do_select(curr_join, (List<Item> *) 0, curr_tmp_table)))

2301

2302

if (setup_sum_funcs(curr_join->session, curr_join->sum_funcs)

2303

|| (tmp_error= do_select(curr_join, (List<Item> *) 0, curr_tmp_table)))

2297

2304

{

2298

error= tmp_error;

2299

return;

2305

error= tmp_error;

2306

return;

2300

2307

}

2301

2308

end_read_record(&curr_join->join_tab->read_record);

2302

2309

curr_join->const_tables= curr_join->tables; // Mark free for cleanup()

2303

2310

curr_join->join_tab[0].table= 0; // Table is freed

2304

2311

2305

2312

// No sum funcs anymore

2306

2313

if (!items2)

2307

2314

{

2308

items2= items1 + all_fields.elements;

2309

if (change_to_use_tmp_fields(thd, items2,

2310

tmp_fields_list2, tmp_all_fields2,

2311

fields_list.elements, tmp_all_fields1))

2312

return;

2313

curr_join->tmp_fields_list2= tmp_fields_list2;

2314

curr_join->tmp_all_fields2= tmp_all_fields2;

2315

items2= items1 + all_fields.elements;

2316

if (change_to_use_tmp_fields(session, items2,

2317

tmp_fields_list2, tmp_all_fields2,

2318

fields_list.elements, tmp_all_fields1))

2319

return;

2320

curr_join->tmp_fields_list2= tmp_fields_list2;

2321

curr_join->tmp_all_fields2= tmp_all_fields2;

2315

2322

}

2316

2323

curr_fields_list= &curr_join->tmp_fields_list2;

2317

2324

curr_all_fields= &curr_join->tmp_all_fields2;

2318

2325

curr_join->set_items_ref_array(items2);

2319

curr_join->tmp_table_param.field_count+=

2320

curr_join->tmp_table_param.sum_func_count;

2326

curr_join->tmp_table_param.field_count+= curr_join->tmp_table_param.sum_func_count;

2321

2327

curr_join->tmp_table_param.sum_func_count= 0;

2322

2328

}

2323

2329

if (curr_tmp_table->distinct)

2324

2330

curr_join->select_distinct=0; /* Each row is unique */

2325

2331

2326

2332

curr_join->join_free(); /* Free quick selects */

2327

2333

if (curr_join->select_distinct && ! curr_join->group_list)

2328

2334

{

2329

thd_proc_info(thd, "Removing duplicates");

2335

session->set_proc_info("Removing duplicates");

2330

2336

if (curr_join->tmp_having)

2331

curr_join->tmp_having->update_used_tables();

2337

curr_join->tmp_having->update_used_tables();

2338

2332

2339

if (remove_duplicates(curr_join, curr_tmp_table,

2333

2340

*curr_fields_list, curr_join->tmp_having))

2334

return;

2341

return;

2342

2335

2343

curr_join->tmp_having=0;

2336

2344

curr_join->select_distinct=0;

2337

2345

}

2339

2347

if (make_simple_join(curr_join, curr_tmp_table))

2340

2348

return;

2341

2349

calc_group_buffer(curr_join, curr_join->group_list);

2342

count_field_types(select_lex, &curr_join->tmp_table_param,

2343

*curr_all_fields, 0);

2344

2350

count_field_types(select_lex, &curr_join->tmp_table_param, *curr_all_fields, 0);

2351

2345

2352

}

2346

2353

2347

2354

if (curr_join->group || curr_join->tmp_table_param.sum_func_count)

2348

2355

{

2349

2356

if (make_group_fields(this, curr_join))

2350

{

2351

2357

return;

2352

}

2353

if (!items3)

2358

2359

if (! items3)

2354

2360

{

2355

if (!items0)

2356

init_items_ref_array();

2361

if (! items0)

2362

init_items_ref_array();

2357

2363

items3= ref_pointer_array + (all_fields.elements*4);

2358

setup_copy_fields(thd, &curr_join->tmp_table_param,

2364

setup_copy_fields(session, &curr_join->tmp_table_param,

2359

2365

items3, tmp_fields_list3, tmp_all_fields3,

2360

2366

curr_fields_list->elements, *curr_all_fields);

2361

2367

tmp_table_param.save_copy_funcs= curr_join->tmp_table_param.copy_funcs;

2362

2368

tmp_table_param.save_copy_field= curr_join->tmp_table_param.copy_field;

2363

tmp_table_param.save_copy_field_end=

2364

curr_join->tmp_table_param.copy_field_end;

2369

tmp_table_param.save_copy_field_end= curr_join->tmp_table_param.copy_field_end;

2365

2370

curr_join->tmp_all_fields3= tmp_all_fields3;

2366

2371

curr_join->tmp_fields_list3= tmp_fields_list3;

2367

2372

}

2369

2374

{

2370

2375

curr_join->tmp_table_param.copy_funcs= tmp_table_param.save_copy_funcs;

2371

2376

curr_join->tmp_table_param.copy_field= tmp_table_param.save_copy_field;

2372

curr_join->tmp_table_param.copy_field_end=

2373

tmp_table_param.save_copy_field_end;

2377

curr_join->tmp_table_param.copy_field_end= tmp_table_param.save_copy_field_end;

2374

2378

}

2375

2379

curr_fields_list= &tmp_fields_list3;

2376

2380

curr_all_fields= &tmp_all_fields3;

2377

2381

curr_join->set_items_ref_array(items3);

2378

2382

2379

2383

if (curr_join->make_sum_func_list(*curr_all_fields, *curr_fields_list,

2380

1, true) ||

2381

setup_sum_funcs(curr_join->thd, curr_join->sum_funcs) ||

2382

thd->is_fatal_error)

2384

1, true) ||

2385

setup_sum_funcs(curr_join->session, curr_join->sum_funcs) ||

2386

session->is_fatal_error)

2383

2387

return;

2384

2388

}

2385

2389

if (curr_join->group_list || curr_join->order)

2386

2390

{

2387

thd_proc_info(thd, "Sorting result");

2391

session->set_proc_info("Sorting result");

2388

2392

/* If we have already done the group, add HAVING to sorted table */

2389

if (curr_join->tmp_having && ! curr_join->group_list &&

2390

! curr_join->sort_and_group)

2393

if (curr_join->tmp_having && ! curr_join->group_list && ! curr_join->sort_and_group)

2391

2394

{

2392

2395

// Some tables may have been const

2393

2396

curr_join->tmp_having->update_used_tables();

2395

2398

table_map used_tables= (curr_join->const_table_map |

2396

2399

curr_table->table->map);

2397

2400

2398

Item* sort_table_cond= make_cond_for_table(curr_join->tmp_having,

2399

used_tables,

2400

used_tables, 0);

2401

Item* sort_table_cond= make_cond_for_table(curr_join->tmp_having, used_tables, used_tables, 0);

2401

2402

if (sort_table_cond)

2402

2403

{

2403

if (!curr_table->select)

2404

if (!(curr_table->select= new SQL_SELECT))

2405

return;

2406

if (!curr_table->select->cond)

2407

curr_table->select->cond= sort_table_cond;

2408

else // This should never happen

2409

{

2410

if (!(curr_table->select->cond=

2411

new Item_cond_and(curr_table->select->cond,

2412

sort_table_cond)))

2413

return;

2414

2415

Item_cond_and do not need fix_fields for execution, its parameters

2416

are fixed or do not need fix_fields, too

2417

2418

curr_table->select->cond->quick_fix_field();

2419

}

2420

curr_table->select_cond= curr_table->select->cond;

2421

curr_table->select_cond->top_level_item();

2422

curr_join->tmp_having= make_cond_for_table(curr_join->tmp_having,

2423

~ (table_map) 0,

2424

~used_tables, 0);

2404

if (!curr_table->select)

2405

if (!(curr_table->select= new SQL_SELECT))

2406

return;

2407

if (!curr_table->select->cond)

2408

curr_table->select->cond= sort_table_cond;

2409

else // This should never happen

2410

{

2411

if (!(curr_table->select->cond=

2412

new Item_cond_and(curr_table->select->cond,

2413

sort_table_cond)))

2414

return;

2415

2416

Item_cond_and do not need fix_fields for execution, its parameters

2417

are fixed or do not need fix_fields, too

2418

2419

curr_table->select->cond->quick_fix_field();

2420

}

2421

curr_table->select_cond= curr_table->select->cond;

2422

curr_table->select_cond->top_level_item();

2423

curr_join->tmp_having= make_cond_for_table(curr_join->tmp_having,

2424

~ (table_map) 0,

2425

~used_tables, 0);

2425

2426

}

2426

2427

}

2427

2428

{

2428

2429

if (group)

2429

curr_join->select_limit= HA_POS_ERROR;

2430

curr_join->select_limit= HA_POS_ERROR;

2430

2431

else

2431

2432

{

2432

2433

We can abort sorting after thd->select_limit rows if we there is no

2434

WHERE clause for any tables after the sorted one.

2435

2436

JOIN_TAB *curr_table= &curr_join->join_tab[curr_join->const_tables+1];

2437

JOIN_TAB *end_table= &curr_join->join_tab[curr_join->tables];

2438

for (; curr_table < end_table ; curr_table++)

2439

{

2440

2441

table->keyuse is set in the case there was an original WHERE clause

2442

on the table that was optimized away.

2443

2444

if (curr_table->select_cond ||

2445

(curr_table->keyuse && !curr_table->first_inner))

2446

{

2447

/* We have to sort all rows */

2448

curr_join->select_limit= HA_POS_ERROR;

2449

break;

2450

}

2451

}

2433

2434

We can abort sorting after session->select_limit rows if we there is no

2435

WHERE clause for any tables after the sorted one.

2436

2437

JOIN_TAB *curr_table= &curr_join->join_tab[curr_join->const_tables+1];

2438

JOIN_TAB *end_table= &curr_join->join_tab[curr_join->tables];

2439

for (; curr_table < end_table ; curr_table++)

2440

{

2441

2442

table->keyuse is set in the case there was an original WHERE clause

2443

on the table that was optimized away.

2444

2445

if (curr_table->select_cond ||

2446

(curr_table->keyuse && !curr_table->first_inner))

2447

{

2448

/* We have to sort all rows */

2449

curr_join->select_limit= HA_POS_ERROR;

2450

break;

2451

}

2452

}

2452

2453

}

2453

2454

if (curr_join->join_tab == join_tab && save_join_tab())

2454

{

2455

return;

2456

}

2455

return;

2457

2456

2458

Here we sort rows for order_st BY/GROUP BY clause, if the optimiser

2459

chose FILESORT to be faster than INDEX SCAN or there is no

2460

suitable index present.

2461

Note, that create_sort_index calls test_if_skip_sort_order and may

2462

finally replace sorting with index scan if there is a LIMIT clause in

2463

the query. XXX: it's never shown in EXPLAIN!

2464

OPTION_FOUND_ROWS supersedes LIMIT and is taken into account.

2457

Here we sort rows for order_st BY/GROUP BY clause, if the optimiser

2458

chose FILESORT to be faster than INDEX SCAN or there is no

2459

suitable index present.

2460

Note, that create_sort_index calls test_if_skip_sort_order and may

2461

finally replace sorting with index scan if there is a LIMIT clause in

2462

the query. XXX: it's never shown in EXPLAIN!

2463

OPTION_FOUND_ROWS supersedes LIMIT and is taken into account.

2465

2464

2466

if (create_sort_index(thd, curr_join,

2467

curr_join->group_list ?

2465

if (create_sort_index(session, curr_join,

2466

curr_join->group_list ?

2468

2467

curr_join->group_list : curr_join->order,

2469

2468

curr_join->select_limit,

2470

2469

(select_options & OPTION_FOUND_ROWS ?

2471

2470

HA_POS_ERROR : unit->select_limit_cnt),

2472

2471

curr_join->group_list ? true : false))

2473

return;

2472

return;

2473

2474

sortorder= curr_join->sortorder;

2475

if (curr_join->const_tables != curr_join->tables &&

2476

!curr_join->join_tab[curr_join->const_tables].table->sort.io_cache)

2484

}

2485

}

2486

}

2487

/* XXX: When can we have here thd->is_error() not zero? */

2488

if (thd->is_error())

2487

/* XXX: When can we have here session->is_error() not zero? */

2488

if (session->is_error())

2489

{

2490

error= thd->is_error();

2490

error= session->is_error();

2491

return;

2492

}

2493

curr_join->having= curr_join->tmp_having;

2494

curr_join->fields= curr_fields_list;

2495

2496

{

2497

thd_proc_info(thd, "Sending data");

2498

result->send_fields(*curr_fields_list,

2499

Protocol::SEND_NUM_ROWS | Protocol::SEND_EOF);

2500

error= do_select(curr_join, curr_fields_list, NULL);

2501

thd->limit_found_rows= curr_join->send_records;

2502

}

2496

session->set_proc_info("Sending data");

2497

result->send_fields(*curr_fields_list,

2498

Protocol::SEND_NUM_ROWS | Protocol::SEND_EOF);

2499

error= do_select(curr_join, curr_fields_list, NULL);

2500

session->limit_found_rows= curr_join->send_records;

2503

2501

2504

2502

/* Accumulate the counts from all join iterations of all join parts. */

2505

thd->examined_row_count+= curr_join->examined_rows;

2503

session->examined_row_count+= curr_join->examined_rows;

2506

2504

2507

2505

2508

2506

With EXPLAIN EXTENDED we have to restore original ref_array

2509

2507

for a derived table which is always materialized.

2510

2508

Otherwise we would not be able to print the query correctly.

2511

2512

if (items0 &&

2513

(thd->lex->describe & DESCRIBE_EXTENDED) &&

2514

select_lex->linkage == DERIVED_TABLE_TYPE)

2509

2510

if (items0 && (session->lex->describe & DESCRIBE_EXTENDED) && select_lex->linkage == DERIVED_TABLE_TYPE)

2515

2511

set_items_ref_array(items0);

2516

2512

2517

2513

return;

2518

2514

}

2519

2515

2520

2521

2516

/**

2522

2517

Clean up join.

2523

2518

2524

2519

@return

2525

2520

Return error that hold JOIN.

2526

2521

2527

2528

int

2529

JOIN::destroy()

2522

int JOIN::destroy()

2530

2523

{

2531

2524

select_lex->join= 0;

2532

2525

2536

2529

{

2537

2530

JOIN_TAB *tab, *end;

2538

2531

for (tab= join_tab, end= tab+tables ; tab != end ; tab++)

2539

tab->cleanup();

2532

tab->cleanup();

2540

2533

}

2541

2534

tmp_join->tmp_join= 0;

2542

2535

tmp_table_param.copy_field=0;

2546

2539

2547

2540

cleanup(1);

2548

2541

if (exec_tmp_table1)

2549

exec_tmp_table1->free_tmp_table(thd);

2542

exec_tmp_table1->free_tmp_table(session);

2550

2543

if (exec_tmp_table2)

2551

exec_tmp_table2->free_tmp_table(thd);

2544

exec_tmp_table2->free_tmp_table(session);

2552

2545

delete select;

2553

2546

delete_dynamic(&keyuse);

2554

2547

return(error);

2555

2548

}

2556

2549

2557

2558

2559

2550

/**

2560

2551

An entry point to single-unit select (a select without UNION).

2561

2552

2562

@param thd thread handler

2553

@param session thread handler

2563

2554

@param rref_pointer_array a reference to ref_pointer_array of

2564

2555

the top-level select_lex for this query

2565

2556

@param tables list of all tables used in this query.

2566

2557

The tables have been pre-opened.

2567

@param wild_num number of wildcards used in the top level

2558

@param wild_num number of wildcards used in the top level

2568

2559

select of this query.

2569

2560

For example statement

2570

2561

SELECT *, t1.*, catalog.t2.* FROM t0, t1, t2;

2580

2571

@param order linked list of order_st BY agruments

2581

2572

@param group linked list of GROUP BY arguments

2582

2573

@param having top level item of HAVING expression

2583

@param proc_param list of PROCEDUREs

2584

2574

@param select_options select options (BIG_RESULT, etc)

2585

2575

@param result an instance of result set handling class.

2586

2576

This object is responsible for send result

2587

2577

set rows to the client or inserting them

2588

2578

into a table.

2589

@param select_lex the only SELECT_LEX of this query

2579

@param select_lex the only Select_Lex of this query

2590

2580

@param unit top-level UNIT of this query

2591

2581

UNIT is an artificial object created by the

2592

2582

parser for every SELECT clause.

2601

2591

2602

2592

2603

2593

bool

2604

mysql_select(THD *thd, Item ***rref_pointer_array,

2594

mysql_select(Session *session, Item ***rref_pointer_array,

2605

2595

TableList *tables, uint32_t wild_num, List<Item> &fields,

2606

2596

COND *conds, uint32_t og_num, order_st *order, order_st *group,

2607

Item *having, order_st *proc_param, uint64_t select_options,

2608

select_result *result, SELECT_LEX_UNIT *unit,

2609

SELECT_LEX *select_lex)

2597

Item *having, uint64_t select_options,

2598

select_result *result, Select_Lex_Unit *unit,

2599

Select_Lex *select_lex)

2610

2600

{

2611

2601

bool err;

2612

2602

bool free_join= 1;

2634

2624

else

2635

2625

{

2636

2626

if ((err= join->prepare(rref_pointer_array, tables, wild_num,

2637

conds, og_num, order, group, having, proc_param,

2638

select_lex, unit)))

2627

conds, og_num, order, group, having, select_lex, unit)))

2639

2628

{

2640

2629

goto err;

2641

2630

}

2646

2635

}

2647

2636

else

2648

2637

{

2649

if (!(join= new JOIN(thd, fields, select_options, result)))

2638

if (!(join= new JOIN(session, fields, select_options, result)))

2650

2639

return(true);

2651

thd_proc_info(thd, "init");

2652

thd->used_tables=0; // Updated by setup_fields

2640

session->set_proc_info("init");

2641

session->used_tables=0; // Updated by setup_fields

2653

2642

if ((err= join->prepare(rref_pointer_array, tables, wild_num,

2654

conds, og_num, order, group, having, proc_param,

2643

conds, og_num, order, group, having,

2655

2644

select_lex, unit)) == true)

2656

2645

{

2657

2646

goto err;

2658

2647

}

2659

2648

}

2660

2649

2661

/* dump_TableList_graph(select_lex, select_lex->leaf_tables); */

2662

2650

if (join->flatten_subqueries())

2663

2651

{

2664

2652

err= 1;

2665

2653

goto err;

2666

2654

}

2667

/* dump_TableList_struct(select_lex, select_lex->leaf_tables); */

2668

2655

2669

2656

if ((err= join->optimize()))

2670

2657

{

2671

2658

goto err; // 1

2672

2659

}

2673

2660

2674

if (thd->lex->describe & DESCRIBE_EXTENDED)

2661

if (session->lex->describe & DESCRIBE_EXTENDED)

2675

2662

{

2676

2663

join->conds_history= join->conds;

2677

2664

join->having_history= (join->having?join->having:join->tmp_having);

2678

2665

}

2679

2666

2680

if (thd->is_error())

2667

if (session->is_error())

2681

2668

goto err;

2682

2669

2683

2670

join->exec();

2684

2671

2685

if (thd->lex->describe & DESCRIBE_EXTENDED)

2672

if (session->lex->describe & DESCRIBE_EXTENDED)

2686

2673

{

2687

2674

select_lex->where= join->conds_history;

2688

2675

select_lex->having= join->having_history;

2691

2678

err:

2692

2679

if (free_join)

2693

2680

{

2694

thd_proc_info(thd, "end");

2681

session->set_proc_info("end");

2695

2682

err|= select_lex->cleanup();

2696

return(err || thd->is_error());

2683

return(err || session->is_error());

2697

2684

}

2698

2685

return(join->error);

2699

2686

}

2700

2687

2701

2688

2702

int subq_sj_candidate_cmp(Item_in_subselect* const *el1,

2689

int subq_sj_candidate_cmp(Item_in_subselect* const *el1,

2703

2690

Item_in_subselect* const *el2)

2704

2691

{

2705

return ((*el1)->sj_convert_priority < (*el2)->sj_convert_priority) ? 1 :

2692

return ((*el1)->sj_convert_priority < (*el2)->sj_convert_priority) ? 1 :

2706

2693

( ((*el1)->sj_convert_priority == (*el2)->sj_convert_priority)? 0 : -1);

2707

2694

}

2708

2695

2713

2700

}

2714

2701

2715

2702

2716

static TableList *alloc_join_nest(THD *thd)

2703

static TableList *alloc_join_nest(Session *session)

2717

2704

{

2718

2705

TableList *tbl;

2719

if (!(tbl= (TableList*) thd->calloc(ALIGN_SIZE(sizeof(TableList))+

2706

if (!(tbl= (TableList*) session->calloc(ALIGN_SIZE(sizeof(TableList))+

2720

2707

sizeof(nested_join_st))))

2721

2708

return NULL;

2722

tbl->nested_join= (nested_join_st*) ((unsigned char*)tbl +

2709

tbl->nested_join= (nested_join_st*) ((unsigned char*)tbl +

2723

2710

ALIGN_SIZE(sizeof(TableList)));

2724

2711

return tbl;

2725

2712

}

2726

2713

2727

2714

2728

void fix_list_after_tbl_changes(SELECT_LEX *new_parent, List<TableList> *tlist)

2715

void fix_list_after_tbl_changes(Select_Lex *new_parent, List<TableList> *tlist)

2729

2716

{

2730

2717

List_iterator<TableList> it(*tlist);

2731

2718

TableList *table;

2744

2731

2745

2732

SYNOPSIS

2746

2733

convert_subq_to_sj()

2747

parent_join Parent join, the one that has subq_pred in its WHERE/ON

2734

parent_join Parent join, the one that has subq_pred in its WHERE/ON

2748

2735

clause

2749

2736

subq_pred Subquery predicate to be converted

2750

2737

2751

2738

DESCRIPTION

2752

Convert a subquery predicate into a TableList semi-join nest. All the

2739

Convert a subquery predicate into a TableList semi-join nest. All the

2753

2740

prerequisites are already checked, so the conversion is always successfull.

2754

2741

2755

2742

Prepared Statements: the transformation is permanent:

2756

2743

- Changes in TableList structures are naturally permanent

2757

2744

- Item tree changes are performed on statement MEM_ROOT:

2758

= we activate statement MEM_ROOT

2745

= we activate statement MEM_ROOT

2759

2746

= this function is called before the first fix_prepare_information

2760

2747

call.

2761

2748

2769

2756

2770

2757

bool convert_subq_to_sj(JOIN *parent_join, Item_in_subselect *subq_pred)

2771

2758

{

2772

SELECT_LEX *parent_lex= parent_join->select_lex;

2759

Select_Lex *parent_lex= parent_join->select_lex;

2773

2760

TableList *emb_tbl_nest= NULL;

2774

2761

List<TableList> *emb_join_list= &parent_lex->top_join_list;

2775

THD *thd= parent_join->thd;

2762

Session *session= parent_join->session;

2776

2763

2777

2764

2778

2765

1. Find out where to put the predicate into.

2808

2795

}

2809

2796

else if (!subq_pred->expr_join_nest->nested_join)

2810

2797

{

2811

TableList *outer_tbl= subq_pred->expr_join_nest;

2798

TableList *outer_tbl= subq_pred->expr_join_nest;

2812

2799

TableList *wrap_nest;

2813

2800

2814

2801

We're dealing with

2820

2807

... LEFT JOIN ( tbl SJ (subq_tables) ) ON (on_expr AND subq_pred) ...

2821

2808

| |

2822

2809

|<----- wrap_nest ---->|

2823

2810

2824

2811

Q: other subqueries may be pointing to this element. What to do?

2825

2812

A1: simple solution: copy *subq_pred->expr_join_nest= *parent_nest.

2826

2813

But we'll need to fix other pointers.

2829

2816

A3: changes in the TableList::outer_join will make everything work

2830

2817

automatically.

2831

2818

2832

if (!(wrap_nest= alloc_join_nest(parent_join->thd)))

2819

if (!(wrap_nest= alloc_join_nest(parent_join->session)))

2833

2820

{

2834

2821

return(true);

2835

2822

}

2864

2851

}

2865

2852

}

2866

2853

2867

Ok now wrap_nest 'contains' outer_tbl and we're ready to add the

2854

Ok now wrap_nest 'contains' outer_tbl and we're ready to add the

2868

2855

semi-join nest into it

2869

2856

2870

2857

emb_join_list= &wrap_nest->nested_join->join_list;

2874

2861

2875

2862

TableList *sj_nest;

2876

2863

nested_join_st *nested_join;

2877

if (!(sj_nest= alloc_join_nest(parent_join->thd)))

2864

if (!(sj_nest= alloc_join_nest(parent_join->session)))

2878

2865

{

2879

2866

return(true);

2880

2867

}

2887

2874

/* sj_nest->next_leaf= sj_nest->next_local= sj_nest->next_global == NULL*/

2888

2875

emb_join_list->push_back(sj_nest);

2889

2876

2890

2877

2891

2878

nested_join->used_tables and nested_join->not_null_tables are

2892

2879

initialized in simplify_joins().

2893

2880

2894

2895

2881

2882

2896

2883

2. Walk through subquery's top list and set 'embedding' to point to the

2897

2884

sj-nest.

2898

2885

2899

st_select_lex *subq_lex= subq_pred->unit->first_select();

2886

Select_Lex *subq_lex= subq_pred->unit->first_select();

2900

2887

nested_join->join_list.empty();

2901

2888

List_iterator_fast<TableList> li(subq_lex->top_join_list);

2902

2889

TableList *tl, *last_leaf;

2906

2893

tl->join_list= &nested_join->join_list;

2907

2894

nested_join->join_list.push_back(tl);

2908

2895

}

2909

2896

2910

2897

2911

2898

Reconnect the next_leaf chain.

2912

2899

TODO: Do we have to put subquery's tables at the end of the chain?

2941

2928

{

2942

2929

tl->table->tablenr= table_no;

2943

2930

tl->table->map= ((table_map)1) << table_no;

2944

SELECT_LEX *old_sl= tl->select_lex;

2945

tl->select_lex= parent_join->select_lex;

2931

Select_Lex *old_sl= tl->select_lex;

2932

tl->select_lex= parent_join->select_lex;

2946

2933

for(TableList *emb= tl->embedding; emb && emb->select_lex == old_sl; emb= emb->embedding)

2947

2934

emb->select_lex= parent_join->select_lex;

2948

2935

}

2949

2936

parent_join->tables += subq_lex->join->tables;

2950

2937

2951

2938

2952

2939

Put the subquery's WHERE into semi-join's sj_on_expr

2953

2940

Add the subquery-induced equalities too.

2954

2941

2955

SELECT_LEX *save_lex= thd->lex->current_select;

2956

thd->lex->current_select=subq_lex;

2942

Select_Lex *save_lex= session->lex->current_select;

2943

session->lex->current_select=subq_lex;

2957

2944

if (!subq_pred->left_expr->fixed &&

2958

subq_pred->left_expr->fix_fields(thd, &subq_pred->left_expr))

2945

subq_pred->left_expr->fix_fields(session, &subq_pred->left_expr))

2959

2946

return(true);

2960

thd->lex->current_select=save_lex;

2947

session->lex->current_select=save_lex;

2961

2948

2962

2949

sj_nest->nested_join->sj_corr_tables= subq_pred->used_tables();

2963

2950

sj_nest->nested_join->sj_depends_on= subq_pred->used_tables() |

2975

2962

(TODO: can we just create a list of pointers and hope the expressions

2976

2963

will not substitute themselves on fix_fields()? or we need to wrap

2977

2964

them into Item_direct_view_refs and store pointers to those. The

2978

pointers to Item_direct_view_refs are guaranteed to be stable as

2979

Item_direct_view_refs doesn't substitute itself with anything in

2965

pointers to Item_direct_view_refs are guaranteed to be stable as

2966

Item_direct_view_refs doesn't substitute itself with anything in

2980

2967

Item_direct_view_ref::fix_fields.

2981

2968

2982

2969

sj_nest->sj_in_exprs= subq_pred->left_expr->cols();

2986

2973

{

2987

2974

nested_join->sj_outer_expr_list.push_back(subq_pred->left_expr);

2988

2975

2989

Item *item_eq= new Item_func_eq(subq_pred->left_expr,

2976

Item *item_eq= new Item_func_eq(subq_pred->left_expr,

2990

2977

subq_lex->ref_pointer_array[0]);

2991

2978

item_eq->name= (char*)subq_sj_cond_name;

2992

2979

sj_nest->sj_on_expr= and_items(sj_nest->sj_on_expr, item_eq);

2997

2984

{

2998

2985

nested_join->sj_outer_expr_list.push_back(subq_pred->left_expr->

2999

2986

element_index(i));

3000

Item *item_eq=

3001

new Item_func_eq(subq_pred->left_expr->element_index(i),

2987

Item *item_eq=

2988

new Item_func_eq(subq_pred->left_expr->element_index(i),

3002

2989

subq_lex->ref_pointer_array[i]);

3003

2990

item_eq->name= (char*)subq_sj_cond_name + (i % 64);

3004

2991

sj_nest->sj_on_expr= and_items(sj_nest->sj_on_expr, item_eq);

3005

2992

}

3006

2993

}

3007

2994

/* Fix the created equality and AND */

3008

sj_nest->sj_on_expr->fix_fields(parent_join->thd, &sj_nest->sj_on_expr);

2995

sj_nest->sj_on_expr->fix_fields(parent_join->session, &sj_nest->sj_on_expr);

3009

2996

3010

2997

3011

2998

Walk through sj nest's WHERE and ON expressions and call

3021

3008

/* Inject sj_on_expr into the parent's WHERE or ON */

3022

3009

if (emb_tbl_nest)

3023

3010

{

3024

emb_tbl_nest->on_expr= and_items(emb_tbl_nest->on_expr,

3011

emb_tbl_nest->on_expr= and_items(emb_tbl_nest->on_expr,

3025

3012

sj_nest->sj_on_expr);

3026

emb_tbl_nest->on_expr->fix_fields(parent_join->thd, &emb_tbl_nest->on_expr);

3013

emb_tbl_nest->on_expr->fix_fields(parent_join->session, &emb_tbl_nest->on_expr);

3027

3014

}

3028

3015

else

3029

3016

{

3030

3017

/* Inject into the WHERE */

3031

3018

parent_join->conds= and_items(parent_join->conds, sj_nest->sj_on_expr);

3032

parent_join->conds->fix_fields(parent_join->thd, &parent_join->conds);

3019

parent_join->conds->fix_fields(parent_join->session, &parent_join->conds);

3033

3020

parent_join->select_lex->where= parent_join->conds;

3034

3021

}

3035

3022

3042

3029

3043

3030

SYNOPSIS

3044

3031

JOIN::flatten_subqueries()

3045

3032

3046

3033

DESCRIPTION

3047

3034

Convert candidate subquery predicates to semi-joins.

3048

3035

3049

RETURN

3036

RETURN

3050

3037

false OK

3051

3038

true Error

3052

3039

3060

3047

return(false);

3061

3048

3062

3049

/* 1. Fix children subqueries */

3063

for (in_subq= sj_subselects.front(), in_subq_end= sj_subselects.back();

3050

for (in_subq= sj_subselects.front(), in_subq_end= sj_subselects.back();

3064

3051

in_subq != in_subq_end; in_subq++)

3065

3052

{

3066

3053

JOIN *child_join= (*in_subq)->unit->first_select()->join;

3067

3054

child_join->outer_tables = child_join->tables;

3068

3055

if (child_join->flatten_subqueries())

3069

3056

return(true);

3070

(*in_subq)->sj_convert_priority=

3057

(*in_subq)->sj_convert_priority=

3071

3058

(*in_subq)->is_correlated * MAX_TABLES + child_join->outer_tables;

3072

3059

}

3073

3074

//dump_TableList_struct(select_lex, select_lex->leaf_tables);

3075

3076

2. Pick which subqueries to convert:

3077

sort the subquery array

3078

- prefer correlated subqueries over uncorrelated;

3079

- prefer subqueries that have greater number of outer tables;

3080

3081

sj_subselects.sort(subq_sj_candidate_cmp);

3082

// #tables-in-parent-query + #tables-in-subquery < MAX_TABLES

3083

/* Replace all subqueries to be flattened with Item_int(1) */

3084

for (in_subq= sj_subselects.front();

3085

in_subq != in_subq_end &&

3086

tables + ((*in_subq)->sj_convert_priority % MAX_TABLES) < MAX_TABLES;

3087

in_subq++)

3060

3061

bool outer_join_disable_semi_join= false;

3062

3063

* Temporary measure: disable semi-joins when they are together with outer

3064

* joins.

3065

3066

* @see LP Bug #314911

3067

3068

for (TableList *tbl= select_lex->leaf_tables; tbl; tbl=tbl->next_leaf)

3088

3069

{

3089

if (replace_where_subcondition(this, *in_subq, new Item_int(1), false))

3090

return(true);

3070

TableList *embedding= tbl->embedding;

3071

if (tbl->on_expr || (tbl->embedding && !(embedding->sj_on_expr &&

3072

!embedding->embedding)))

3073

{

3074

in_subq= sj_subselects.front();

3075

outer_join_disable_semi_join= true;

3076

}

3091

3077

}

3092

3093

for (in_subq= sj_subselects.front();

3094

in_subq != in_subq_end &&

3095

tables + ((*in_subq)->sj_convert_priority % MAX_TABLES) < MAX_TABLES;

3096

in_subq++)

3078

3079

if (! outer_join_disable_semi_join)

3097

3080

{

3098

if (convert_subq_to_sj(this, *in_subq))

3099

return(true);

3081

3082

2. Pick which subqueries to convert:

3083

sort the subquery array

3084

- prefer correlated subqueries over uncorrelated;

3085

- prefer subqueries that have greater number of outer tables;

3086

3087

sj_subselects.sort(subq_sj_candidate_cmp);

3088

// #tables-in-parent-query + #tables-in-subquery < MAX_TABLES

3089

/* Replace all subqueries to be flattened with Item_int(1) */

3090

for (in_subq= sj_subselects.front();

3091

in_subq != in_subq_end &&

3092

tables + ((*in_subq)->sj_convert_priority % MAX_TABLES) < MAX_TABLES;

3093

in_subq++)

3094

{

3095

if (replace_where_subcondition(this, *in_subq, new Item_int(1), false))

3096

return(true);

3097

}

3098

3099

for (in_subq= sj_subselects.front();

3100

in_subq != in_subq_end &&

3101

tables + ((*in_subq)->sj_convert_priority % MAX_TABLES) < MAX_TABLES;

3102

in_subq++)

3103

{

3104

if (convert_subq_to_sj(this, *in_subq))

3105

return(true);

3106

}

3100

3107

}

3101

3108

3102

3109

/* 3. Finalize those we didn't convert */

3118

3125

if (replace_where_subcondition(this, *in_subq, substitute, do_fix_fields))

3119

3126

return(true);

3120

3127

3121

//if ((*in_subq)->fix_fields(thd, (*in_subq)->ref_ptr))

3128

//if ((*in_subq)->fix_fields(session, (*in_subq)->ref_ptr))

3122

3129

// return(true);

3123

3130

}

3124

3131

sj_subselects.clear();

3125

3132

return(false);

3126

3133

}

3127

3134

3128

3129

3135

/**

3130

3136

Setup for execution all subqueries of a query, for which the optimizer

3131

3137

chose hash semi-join.

3148

3154

@retval false success.

3149

3155

@retval true error occurred.

3150

3156

3151

3152

3157

bool JOIN::setup_subquery_materialization()

3153

3158

{

3154

for (SELECT_LEX_UNIT *un= select_lex->first_inner_unit(); un;

3159

for (Select_Lex_Unit *un= select_lex->first_inner_unit(); un;

3155

3160

un= un->next_unit())

3156

3161

{

3157

for (SELECT_LEX *sl= un->first_select(); sl; sl= sl->next_select())

3162

for (Select_Lex *sl= un->first_select(); sl; sl= sl->next_select())

3158

3163

{

3159

3164

Item_subselect *subquery_predicate= sl->master_unit()->item;

3160

3165

if (subquery_predicate &&

3247

3252

3248

3253

DESCRIPTION

3249

3254

Try to pull tables out of semi-join nests.

3250

3255

3251

3256

PRECONDITIONS

3252

3257

When this function is called, the join may have several semi-join nests

3253

3258

(possibly within different semi-join nests), but it is guaranteed that

3254

3259

one semi-join nest does not contain another.

3255

3260

3256

3261

ACTION

3257

3262

A table can be pulled out of the semi-join nest if

3258

3263

- It is a constant table

3259

- It is accessed

3264

- It is accessed

3260

3265

3261

3266

POSTCONDITIONS

3262

3267

* Pulled out tables have JOIN_TAB::emb_sj_nest == NULL (like the outer

3267

3272

This operation is (and should be) performed at each PS execution since

3268

3273

tables may become/cease to be constant across PS reexecutions.

3269

3274

3270

RETURN

3275

RETURN

3271

3276

0 - OK

3272

3277

1 - Out of memory error

3273

3278

3276

3281

{

3277

3282

TableList *sj_nest;

3278

3283

List_iterator<TableList> sj_list_it(join->select_lex->sj_nests);

3279

3284

3280

3285

/* Try pulling out of the each of the semi-joins */

3281

3286

while ((sj_nest= sj_list_it++))

3282

3287

{

3283

3288

/* Action #1: Mark the constant tables to be pulled out */

3284

3289

table_map pulled_tables= 0;

3285

3290

3286

3291

List_iterator<TableList> child_li(sj_nest->nested_join->join_list);

3287

3292

TableList *tbl;

3288

3293

while ((tbl= child_li++))

3296

3301

}

3297

3302

}

3298

3303

}

3299

3304

3300

3305

3301

3306

Action #2: Find which tables we can pull out based on

3302

3307

update_ref_and_keys() data. Note that pulling one table out can allow

3303

3308

us to pull out some other tables too.

3304

3309

3305

3310

bool pulled_a_table;

3306

3311

3307

3312

{

3308

3313

pulled_a_table= false;

3309

3314

child_li.rewind();

3311

3316

{

3312

3317

if (tbl->table && !(pulled_tables & tbl->table->map))

3313

3318

{

3314

if (find_eq_ref_candidate(tbl->table,

3315

sj_nest->nested_join->used_tables &

3319

if (find_eq_ref_candidate(tbl->table,

3320

sj_nest->nested_join->used_tables &

3316

3321

~pulled_tables))

3317

3322

{

3318

3323

pulled_a_table= true;

3321

3326

}

3322

3327

}

3323

3328

} while (pulled_a_table);

3324

3329

3325

3330

child_li.rewind();

3326

3331

if ((sj_nest)->nested_join->used_tables == pulled_tables)

3327

3332

{

3335

3340

else

3336

3341

{

3337

3342

/* Record the bitmap of inner tables, mark the inner tables */

3338

table_map inner_tables=(sj_nest)->nested_join->used_tables &

3343

table_map inner_tables=(sj_nest)->nested_join->used_tables &

3339

3344

~pulled_tables;

3340

3345

(sj_nest)->sj_inner_tables= inner_tables;

3341

3346

while ((tbl= child_li++))

3359

3364

*****************************************************************************/

3360

3365

3361

3366

3362

static ha_rows get_quick_record_count(THD *thd, SQL_SELECT *select,

3367

static ha_rows get_quick_record_count(Session *session, SQL_SELECT *select,

3363

3368

Table *table,

3364

3369

const key_map *keys,ha_rows limit)

3365

3370

{

3366

3371

int error;

3367

if (check_stack_overrun(thd, STACK_MIN_SIZE, NULL))

3372

if (check_stack_overrun(session, STACK_MIN_SIZE, NULL))

3368

3373

return(0); // Fatal error flag is set

3369

3374

if (select)

3370

3375

{

3371

3376

select->head=table;

3372

3377

table->reginfo.impossible_range=0;

3373

if ((error= select->test_quick_select(thd, *(key_map *)keys,(table_map) 0,

3378

if ((error= select->test_quick_select(session, *(key_map *)keys,(table_map) 0,

3374

3379

limit, 0, false)) == 1)

3375

3380

return(select->quick->records);

3376

3381

if (error == -1)

3394

3399

Field *field; /* field against which to check sargability */

3395

3400

Item **arg_value; /* values of potential keys for lookups */

3396

3401

uint32_t num_values; /* number of values in the above array */

3397

} SARGABLE_PARAM;

3402

} SARGABLE_PARAM;

3398

3403

3399

3404

/**

3400

3405

Calculate the best possible join and initialize the join structure.

3422

3427

JOIN_TAB *stat_vector[MAX_TABLES+1];

3423

3428

3424

3429

table_count=join->tables;

3425

stat=(JOIN_TAB*) join->thd->calloc(sizeof(JOIN_TAB)*table_count);

3426

stat_ref=(JOIN_TAB**) join->thd->alloc(sizeof(JOIN_TAB*)*MAX_TABLES);

3427

table_vector=(Table**) join->thd->alloc(sizeof(Table*)*(table_count*2));

3430

stat=(JOIN_TAB*) join->session->calloc(sizeof(JOIN_TAB)*table_count);

3431

stat_ref=(JOIN_TAB**) join->session->alloc(sizeof(JOIN_TAB*)*MAX_TABLES);

3432

table_vector=(Table**) join->session->alloc(sizeof(Table*)*(table_count*2));

3428

3433

if (!stat || !stat_ref || !table_vector)

3429

3434

return(1); // Eom /* purecov: inspected */

3430

3435

3440

3445

{

3441

3446

TableList *embedding= tables->embedding;

3442

3447

stat_vector[i]=s;

3443

s->keys.init();

3444

s->const_keys.init();

3445

s->checked_keys.init();

3446

s->needed_reg.init();

3448

s->keys.reset();

3449

s->const_keys.reset();

3450

s->checked_keys.reset();

3451

s->needed_reg.reset();

3447

3452

table_vector[i]=s->table=table=tables->table;

3448

3453

table->pos_in_table_list= tables;

3449

3454

error= table->file->info(HA_STATUS_VARIABLE | HA_STATUS_NO_LOCK);

3452

3457

table->file->print_error(error, MYF(0));

3453

3458

return(1);

3454

3459

}

3455

table->quick_keys.clear_all();

3460

table->quick_keys.reset();

3456

3461

table->reginfo.join_tab=s;

3457

3462

table->reginfo.not_exists_optimize=0;

3458

3463

memset(table->const_key_parts, 0,

3510

3515

3511

3516

if (join->outer_join)

3512

3517

{

3513

3518

3514

3519

Build transitive closure for relation 'to be dependent on'.

3515

3520

This will speed up the plan search for many cases with outer joins,

3516

3521

as well as allow us to catch illegal cross references/

3517

3522

Warshall's algorithm is used to build the transitive closure.

3518

3523

As we use bitmaps to represent the relation the complexity

3519

of the algorithm is O((number of tables)^2).

3524

of the algorithm is O((number of tables)^2).

3520

3525

3521

3526

for (i= 0, s= stat ; i < table_count ; i++, s++)

3522

3527

{

3543

3548

}

3544

3549

3545

3550

if (conds || outer_join)

3546

if (update_ref_and_keys(join->thd, keyuse_array, stat, join->tables,

3551

if (update_ref_and_keys(join->session, keyuse_array, stat, join->tables,

3547

3552

conds, join->cond_equal,

3548

3553

~outer_join, join->select_lex, &sargables))

3549

3554

return(1);

3585

3590

{

3586

3591

table=s->table;

3587

3592

3588

3593

3589

3594

If equi-join condition by a key is null rejecting and after a

3590

3595

substitution of a const table the key value happens to be null

3591

3596

then we can state that there are no matches for this equi-join.

3592

3597

3593

3598

if ((keyuse= s->keyuse) && *s->on_expr_ref && !s->embedding_map)

3594

3599

{

3595

3600

3596

3601

When performing an outer join operation if there are no matching rows

3597

3602

for the single row of the outer table all the inner tables are to be

3598

3603

null complemented and thus considered as constant tables.

3599

Here we apply this consideration to the case of outer join operations

3604

Here we apply this consideration to the case of outer join operations

3600

3605

with a single inner table only because the case with nested tables

3601

3606

would require a more thorough analysis.

3602

3607

TODO. Apply single row substitution to null complemented inner tables

3603

for nested outer join operations.

3604

3608

for nested outer join operations.

3609

3605

3610

while (keyuse->table == table)

3606

3611

{

3607

3612

if (!(keyuse->val->used_tables() & ~join->const_table_map) &&

3608

3613

keyuse->val->is_null() && keyuse->null_rejecting)

3609

3614

{

3610

3615

s->type= JT_CONST;

3611

mark_as_null_row(table);

3616

table->mark_as_null_row();

3612

3617

found_const_table_map|= table->map;

3613

3618

join->const_table_map|= table->map;

3614

3619

set_position(join,const_count++,s,(KEYUSE*) 0);

3649

3654

{

3650

3655

start_keyuse=keyuse;

3651

3656

key=keyuse->key;

3652

s->keys.set_bit(key); // QQ: remove this ?

3657

s->keys.set(key); // QQ: remove this ?

3653

3658

3654

3659

refs=0;

3655

const_ref.clear_all();

3656

eq_part.clear_all();

3660

const_ref.reset();

3661

eq_part.reset();

3657

3662

3658

3663

{

3659

3664

if (keyuse->val->type() != Item::NULL_ITEM && !keyuse->optimize)

3660

3665

{

3661

3666

if (!((~found_const_table_map) & keyuse->used_tables))

3662

const_ref.set_bit(keyuse->keypart);

3667

const_ref.set(keyuse->keypart);

3663

3668

else

3664

3669

refs|=keyuse->used_tables;

3665

eq_part.set_bit(keyuse->keypart);

3670

eq_part.set(keyuse->keypart);

3666

3671

}

3667

3672

keyuse++;

3668

3673

} while (keyuse->table == table && keyuse->key == key);

3669

3674

3670

if (eq_part.is_prefix(table->key_info[key].key_parts) &&

3675

if (is_keymap_prefix(eq_part, table->key_info[key].key_parts) &&

3671

3676

!table->pos_in_table_list->embedding)

3672

3677

{

3673

3678

if ((table->key_info[key].flags & (HA_NOSAME))

3678

3683

int tmp;

3679

3684

ref_changed = 1;

3680

3685

s->type= JT_CONST;

3681

join->const_table_map|=table->map;

3686

join->const_table_map|= table->map;

3682

3687

set_position(join,const_count++,s,start_keyuse);

3683

3688

if (create_ref_for_key(join, s, start_keyuse,

3684

3689

found_const_table_map))

3697

3702

found_ref|= refs; // Table is const if all refs are const

3698

3703

}

3699

3704

else if (const_ref == eq_part)

3700

s->const_keys.set_bit(key);

3705

s->const_keys.set(key);

3701

3706

}

3702

3707

}

3703

3708

}

3704

3709

}

3705

3710

} while (join->const_table_map & found_ref && ref_changed);

3706

3707

3711

3712

3708

3713

Update info on indexes that can be used for search lookups as

3709

reading const tables may has added new sargable predicates.

3714

reading const tables may has added new sargable predicates.

3710

3715

3711

3716

if (const_count && sargables)

3712

3717

{

3715

3720

Field *field= sargables->field;

3716

3721

JOIN_TAB *join_tab= field->table->reginfo.join_tab;

3717

3722

key_map possible_keys= field->key_start;

3718

possible_keys.intersect(field->table->keys_in_use_for_query);

3723

possible_keys&= field->table->keys_in_use_for_query;

3719

3724

bool is_const= 1;

3720

3725

for (uint32_t j=0; j < sargables->num_values; j++)

3721

3726

is_const&= sargables->arg_value[j]->const_item();

3722

3727

if (is_const)

3723

join_tab[0].const_keys.merge(possible_keys);

3728

join_tab[0].const_keys|= possible_keys;

3724

3729

}

3725

3730

}

3726

3731

3757

3762

3758

3763

add_group_and_distinct_keys(join, s);

3759

3764

3760

if (!s->const_keys.is_clear_all() &&

3765

if (s->const_keys.any() &&

3761

3766

!s->table->pos_in_table_list->embedding)

3762

3767

{

3763

3768

ha_rows records;

3768

3773

1, &error);

3769

3774

if (!select)

3770

3775

return(1);

3771

records= get_quick_record_count(join->thd, select, s->table,

3776

records= get_quick_record_count(join->session, select, s->table,

3772

3777

&s->const_keys, join->row_limit);

3773

3778

s->quick=select->quick;

3774

3779

s->needed_reg=select->needed_reg;

3775

3780

select->quick=0;

3776

3781

if (records == 0 && s->table->reginfo.impossible_range)

3777

3782

{

3778

3779

Impossible WHERE or ON expression

3780

In case of ON, we mark that the we match one empty NULL row.

3781

In case of WHERE, don't set found_const_table_map to get the

3782

caller to abort with a zero row result.

3783

3784

join->const_table_map|= s->table->map;

3785

set_position(join,const_count++,s,(KEYUSE*) 0);

3786

s->type= JT_CONST;

3787

if (*s->on_expr_ref)

3788

{

3789

/* Generate empty row */

3790

s->info= "Impossible ON condition";

3791

found_const_table_map|= s->table->map;

3792

s->type= JT_CONST;

3793

mark_as_null_row(s->table); // All fields are NULL

3794

}

3783

3784

Impossible WHERE or ON expression

3785

In case of ON, we mark that the we match one empty NULL row.

3786

In case of WHERE, don't set found_const_table_map to get the

3787

caller to abort with a zero row result.

3788

3789

join->const_table_map|= s->table->map;

3790

set_position(join,const_count++,s,(KEYUSE*) 0);

3791

s->type= JT_CONST;

3792

if (*s->on_expr_ref)

3793

{

3794

/* Generate empty row */

3795

s->info= "Impossible ON condition";

3796

found_const_table_map|= s->table->map;

3797

s->type= JT_CONST;

3798

s->table->mark_as_null_row(); // All fields are NULL

3799

}

3795

3800

}

3796

3801

if (records != HA_POS_ERROR)

3797

3802

{

3798

s->found_records=records;

3799

s->read_time= (ha_rows) (s->quick ? s->quick->read_time : 0.0);

3803

s->found_records=records;

3804

s->read_time= (ha_rows) (s->quick ? s->quick->read_time : 0.0);

3800

3805

}

3801

3806

delete select;

3802

3807

}

3822

3827

join->best_read=1.0;

3823

3828

}

3824

3829

/* Generate an execution plan from the found optimal join order. */

3825

return(join->thd->killed || get_best_combination(join));

3830

return(join->session->killed || get_best_combination(join));

3826

3831

}

3827

3832

3828

3833

3845

3850

If true, the condition this struct represents will not be satisfied

3846

3851

when val IS NULL.

3847

3852

3848

bool null_rejecting;

3853

bool null_rejecting;

3849

3854

bool *cond_guard; /* See KEYUSE::cond_guard */

3850

3855

uint32_t sj_pred_no; /* See KEYUSE::sj_pred_no */

3851

3856

} KEY_FIELD;

3860

3865

that are internally transformed to something like:.

3861

3866

3862

3867

@code

3863

SELECT * FROM t1 WHERE t1.key=outer_ref_field or t1.key IS NULL

3868

SELECT * FROM t1 WHERE t1.key=outer_ref_field or t1.key IS NULL

3864

3869

@endcode

3865

3870

3866

3871

KEY_FIELD::null_rejecting is processed as follows: @n

3895

3900

3896

3901

NOTE: below const_item() call really works as "!used_tables()", i.e.

3897

3902

it can return false where it is feasible to make it return true.

3898

3903

3899

3904

The cause is as follows: Some of the tables are already known to be

3900

3905

const tables (the detection code is in make_join_statistics(),

3901

above the update_ref_and_keys() call), but we didn't propagate

3906

above the update_ref_and_keys() call), but we didn't propagate

3902

3907

information about this: Table::const_table is not set to true, and

3903

3908

Item::update_used_tables() hasn't been called for each item.

3904

3909

The result of this is that we're missing some 'ref' accesses.

3922

3927

}

3923

3928

}

3924

3929

else if (old->eq_func && new_fields->eq_func &&

3925

old->val->eq_by_collation(new_fields->val,

3930

old->val->eq_by_collation(new_fields->val,

3926

3931

old->field->binary(),

3927

3932

old->field->charset()))

3928

3933

3936

3941

new_fields->null_rejecting);

3937

3942

}

3938

3943

else if (old->eq_func && new_fields->eq_func &&

3939

((old->val->const_item() && old->val->is_null()) ||

3944

((old->val->const_item() && old->val->is_null()) ||

3940

3945

new_fields->val->is_null()))

3941

3946

{

3942

3947

/* field = expression OR field IS NULL */

3948

3953

3949

3954

if (!old->val->used_tables() && old->val->is_null())

3950

3955

old->val= new_fields->val;

3951

/* The referred expression can be NULL: */

3956

/* The referred expression can be NULL: */

3952

3957

old->null_rejecting= 0;

3953

3958

}

3954

3959

else

4039

4044

else

4040

4045

{

4041

4046

JOIN_TAB *stat=field->table->reginfo.join_tab;

4042

key_map possible_keys=field->key_start;

4043

possible_keys.intersect(field->table->keys_in_use_for_query);

4044

stat[0].keys.merge(possible_keys); // Add possible keys

4047

key_map possible_keys= field->key_start;

4048

possible_keys&= field->table->keys_in_use_for_query;

4049

stat[0].keys|= possible_keys; // Add possible keys

4045

4050

4046

4051

4047

4052

Save the following cases:

4054

4059

Field BETWEEN ...

4055

4060

Field IN ...

4056

4061

4057

stat[0].key_dependent|=used_tables;

4062

stat[0].key_dependent|= used_tables;

4058

4063

4059

4064

bool is_const=1;

4060

4065

for (uint32_t i=0; i<num_values; i++)

4063

4068

break;

4064

4069

}

4065

4070

if (is_const)

4066

stat[0].const_keys.merge(possible_keys);

4071

stat[0].const_keys|= possible_keys;

4067

4072

else if (!eq_func)

4068

4073

{

4069

4070

Save info to be able check whether this predicate can be

4074

4075

Save info to be able check whether this predicate can be

4071

4076

considered as sargable for range analisis after reading const tables.

4072

4077

We do not save info about equalities as update_const_equal_items

4073

will take care of updating info on keys from sargable equalities.

4078

will take care of updating info on keys from sargable equalities.

4074

4079

4075

4080

(*sargables)--;

4076

4081

(*sargables)->field= field;

4084

4089

4085

4090

if (!eq_func)

4086

4091

{

4087

4092

4088

4093

Additional optimization: if we're processing

4089

4094

"t.key BETWEEN c1 AND c1" then proceed as if we were processing

4090

4095

"t.key = c1".

4091

TODO: This is a very limited fix. A more generic fix is possible.

4096

TODO: This is a very limited fix. A more generic fix is possible.

4092

4097

There are 2 options:

4093

4098

A) Make equality propagation code be able to handle BETWEEN

4094

4099

(including cases like t1.key BETWEEN t2.key AND t3.key)

4135

4140

(*key_fields)->level= and_level;

4136

4141

(*key_fields)->optimize= exists_optimize;

4137

4142

4138

If the condition has form "tbl.keypart = othertbl.field" and

4139

othertbl.field can be NULL, there will be no matches if othertbl.field

4143

If the condition has form "tbl.keypart = othertbl.field" and

4144

othertbl.field can be NULL, there will be no matches if othertbl.field

4140

4145

has NULL value.

4141

4146

We use null_rejecting in add_not_null_conds() to add

4142

4147

'othertbl.field IS NOT NULL' to tab->select_cond.

4146

4151

((*value)->type() == Item::FIELD_ITEM) &&

4147

4152

((Item_field*)*value)->field->maybe_null());

4148

4153

(*key_fields)->cond_guard= NULL;

4149

(*key_fields)->sj_pred_no= (cond->name >= subq_sj_cond_name &&

4150

cond->name < subq_sj_cond_name + 64)?

4154

(*key_fields)->sj_pred_no= (cond->name >= subq_sj_cond_name &&

4155

cond->name < subq_sj_cond_name + 64)?

4151

4156

cond->name - subq_sj_cond_name: UINT_MAX;

4152

4157

(*key_fields)++;

4153

4158

}

4162

4167

@param field Field used in comparision

4163

4168

@param eq_func True if we used =, <=> or IS NULL

4164

4169

@param value Value used for comparison with field

4165

Is NULL for BETWEEN and IN

4170

Is NULL for BETWEEN and IN

4166

4171

@param usable_tables Tables which can be used for key optimization

4167

4172

@param sargables IN/OUT Array of found sargable candidates

4168

4173

4186

4191

eq_func, val, num_values, usable_tables, sargables);

4187

4192

Item_equal *item_equal= field_item->item_equal;

4188

4193

if (item_equal)

4189

{

4194

{

4190

4195

4191

4196

Add to the set of possible key values every substitution of

4192

4197

the field for an equal field included into item_equal

4243

4248

return;

4244

4249

}

4245

4250

4246

4251

4247

4252

Subquery optimization: Conditions that are pushed down into subqueries

4248

4253

are wrapped into Item_func_trig_cond. We process the wrapped condition

4249

4254

but need to set cond_guard for KEYUSE elements generated from it.

4254

4259

{

4255

4260

Item *cond_arg= ((Item_func*)cond)->arguments()[0];

4256

4261

if (!join->group_list && !join->order &&

4257

join->unit->item &&

4262

join->unit->item &&

4258

4263

join->unit->item->substype() == Item_subselect::IN_SUBS &&

4259

4264

!join->unit->is_union())

4260

4265

{

4292

4297

cond_func->argument_count() != 2);

4293

4298

add_key_equal_fields(key_fields, *and_level, cond_func,

4294

4299

(Item_field*) (cond_func->key_item()->real_item()),

4295

0, values,

4300

0, values,

4296

4301

cond_func->argument_count()-1,

4297

4302

usable_tables, sargables);

4298

4303

}

4308

4313

{

4309

4314

field_item= (Item_field *) (cond_func->arguments()[i]->real_item());

4310

4315

add_key_equal_fields(key_fields, *and_level, cond_func,

4311

field_item, 0, values, 1, usable_tables,

4316

field_item, 0, values, 1, usable_tables,

4312

4317

sargables);

4313

4318

}

4314

}

4319

}

4315

4320

}

4316

4321

break;

4317

4322

}

4333

4338

cond_func->functype() != Item_func::LIKE_FUNC &&

4334

4339

!(cond_func->arguments()[1]->used_tables() & OUTER_REF_TABLE_BIT))

4335

4340

{

4336

add_key_equal_fields(key_fields, *and_level, cond_func,

4341

add_key_equal_fields(key_fields, *and_level, cond_func,

4337

4342

(Item_field*) (cond_func->arguments()[1])->real_item(),

4338

4343

equal_func,

4339

4344

cond_func->arguments(),1,usable_tables,

4363

4368

if (const_item)

4364

4369

{

4365

4370

4366

For each field field1 from item_equal consider the equality

4371

For each field field1 from item_equal consider the equality

4367

4372

field1=const_item as a condition allowing an index access of the table

4368

4373

with field1 by the keys value of field1.

4369

4374

4370

4375

while ((item= it++))

4371

4376

{

4372

4377

add_key_field(key_fields, *and_level, cond_func, item->field,

4373

4378

true, &const_item, 1, usable_tables, sargables);

4374

4379

}

4375

4380

}

4376

else

4381

else

4377

4382

{

4378

4383

4379

4384

Consider all pairs of different fields included into item_equal.

4380

For each of them (field1, field1) consider the equality

4385

For each of them (field1, field1) consider the equality

4381

4386

field1=field2 as a condition allowing an index access of the table

4382

4387

with field1 by the keys value of field2.

4383

4388

4384

4389

Item_equal_iterator fi(*item_equal);

4385

4390

while ((item= fi++))

4386

4391

{

4426

4431

{

4427

4432

for (uint32_t key= 0 ; key < form->sizeKeys() ; key++)

4428

4433

{

4429

if (!(form->keys_in_use_for_query.is_set(key)))

4434

if (!(form->keys_in_use_for_query.test(key)))

4430

4435

continue;

4431

4436

4432

uint32_t key_parts= (uint) form->key_info[key].key_parts;

4437

uint32_t key_parts= (uint32_t) form->key_info[key].key_parts;

4433

4438

for (uint32_t part=0 ; part < key_parts ; part++)

4434

4439

{

4435

4440

if (field->eq(form->key_info[key].key_part[part].field))

4474

4479

4475

4480

Add to KEY_FIELD array all 'ref' access candidates within nested join.

4476

4481

4477

This function populates KEY_FIELD array with entries generated from the

4478

ON condition of the given nested join, and does the same for nested joins

4482

This function populates KEY_FIELD array with entries generated from the

4483

ON condition of the given nested join, and does the same for nested joins

4479

4484

contained within this nested join.

4480

4485

4481

4486

@param[in] nested_join_table Nested join pseudo-table to process

4485

4490

4486

4491

4487

4492

@note

4488

We can add accesses to the tables that are direct children of this nested

4493

We can add accesses to the tables that are direct children of this nested

4489

4494

join (1), and are not inner tables w.r.t their neighbours (2).

4490

4491

Example for #1 (outer brackets pair denotes nested join this function is

4495

4496

Example for #1 (outer brackets pair denotes nested join this function is

4492

4497

invoked for):

4493

4498

@code

4494

4499

... LEFT JOIN (t1 LEFT JOIN (t2 ... ) ) ON cond

4497

4502

@code

4498

4503

... LEFT JOIN (t1 LEFT JOIN t2 ) ON cond

4499

4504

@endcode

4500

In examples 1-2 for condition cond, we can add 'ref' access candidates to

4505

In examples 1-2 for condition cond, we can add 'ref' access candidates to

4501

4506

t1 only.

4502

4507

Example #3:

4503

4508

@code

4527

4532

/* It's a semi-join nest. Walk into it as if it wasn't a nest */

4528

4533

have_another= true;

4529

4534

li2= li;

4530

li= List_iterator<TableList>(table->nested_join->join_list);

4535

li= List_iterator<TableList>(table->nested_join->join_list);

4531

4536

}

4532

4537

else

4533

4538

add_key_fields_for_nj(join, table, end, and_level, sargables);

4544

4549

4545

4550

/**

4546

4551

Update keyuse array with all possible keys we can use to fetch rows.

4547

4548

@param thd

4552

4553

@param session

4549

4554

@param[out] keyuse Put here ordered array of KEYUSE structures

4550

4555

@param join_tab Array in tablenr_order

4551

4556

@param tables Number of tables in join

4556

4561

clause)

4557

4562

@param select_lex current SELECT

4558

4563

@param[out] sargables Array of found sargable candidates

4559

4564

4560

4565

@retval

4561

4566

0 OK

4562

4567

@retval

4564

4569

4565

4570

4566

4571

static bool

4567

update_ref_and_keys(THD *thd, DYNAMIC_ARRAY *keyuse,JOIN_TAB *join_tab,

4568

uint32_t tables, COND *cond,

4569

COND_EQUAL *cond_equal __attribute__((unused)),

4570

table_map normal_tables, SELECT_LEX *select_lex,

4572

update_ref_and_keys(Session *session, DYNAMIC_ARRAY *keyuse,JOIN_TAB *join_tab,

4573

uint32_t tables, COND *cond, COND_EQUAL *,

4574

table_map normal_tables, Select_Lex *select_lex,

4571

4575

SARGABLE_PARAM **sargables)

4572

4576

{

4573

4577

uint and_level,i,found_eq_constant;

4574

4578

KEY_FIELD *key_fields, *end, *field;

4575

4579

uint32_t sz;

4576

4580

uint32_t m= cmax(select_lex->max_equal_elems,(uint32_t)1);

4577

4578

4579

We use the same piece of memory to store both KEY_FIELD

4581

4582

4583

We use the same piece of memory to store both KEY_FIELD

4580

4584

and SARGABLE_PARAM structure.

4581

4585

KEY_FIELD values are placed at the beginning this memory

4582

4586

while SARGABLE_PARAM values are put at the end.

4583

4587

All predicates that are used to fill arrays of KEY_FIELD

4584

4588

and SARGABLE_PARAM structures have at most 2 arguments

4585

except BETWEEN predicates that have 3 arguments and

4589

except BETWEEN predicates that have 3 arguments and

4586

4590

IN predicates.

4587

This any predicate if it's not BETWEEN/IN can be used

4591

This any predicate if it's not BETWEEN/IN can be used

4588

4592

directly to fill at most 2 array elements, either of KEY_FIELD

4589

4593

or SARGABLE_PARAM type. For a BETWEEN predicate 3 elements

4590

4594

can be filled as this predicate is considered as

4593

4597

it is considered as sargable only for its first argument.

4594

4598

Multiple equality can add elements that are filled after

4595

4599

substitution of field arguments by equal fields. There

4596

can be not more than select_lex->max_equal_elems such

4600

can be not more than select_lex->max_equal_elems such

4597

4601

substitutions.

4598

4602

4599

4603

sz= cmax(sizeof(KEY_FIELD),sizeof(SARGABLE_PARAM))*

4600

(((thd->lex->current_select->cond_count+1)*2 +

4601

thd->lex->current_select->between_count)*m+1);

4602

if (!(key_fields=(KEY_FIELD*) thd->alloc(sz)))

4604

(((session->lex->current_select->cond_count+1)*2 +

4605

session->lex->current_select->between_count)*m+1);

4606

if (!(key_fields=(KEY_FIELD*) session->alloc(sz)))

4603

4607

return true; /* purecov: inspected */

4604

4608

and_level= 0;

4605

4609

field= end= key_fields;

4606

*sargables= (SARGABLE_PARAM *) key_fields +

4610

*sargables= (SARGABLE_PARAM *) key_fields +

4607

4611

(sz - sizeof((*sargables)[0].field))/sizeof(SARGABLE_PARAM);

4608

4612

/* set a barrier for the array of SARGABLE_PARAM */

4609

(*sargables)[0].field= 0;

4613

(*sargables)[0].field= 0;

4610

4614

4611

4615

if (my_init_dynamic_array(keyuse,sizeof(KEYUSE),20,64))

4612

4616

return true;

4633

4637

In the future when we introduce conditional accesses

4634

4638

for inner tables in outer joins these keys will be taken

4635

4639

into account as well.

4636

4640

4637

4641

if (*join_tab[i].on_expr_ref)

4638

add_key_fields(join_tab->join, &end, &and_level,

4642

add_key_fields(join_tab->join, &end, &and_level,

4639

4643

*join_tab[i].on_expr_ref,

4640

4644

join_tab[i].table->map, sargables);

4641

4645

}

4647

4651

while ((table= li++))

4648

4652

{

4649

4653

if (table->nested_join)

4650

add_key_fields_for_nj(join_tab->join, table, &end, &and_level,

4654

add_key_fields_for_nj(join_tab->join, table, &end, &and_level,

4651

4655

sargables);

4652

4656

}

4653

4657

}

4702

4706

/* Save ptr to first use */

4703

4707

if (!use->table->reginfo.join_tab->keyuse)

4704

4708

use->table->reginfo.join_tab->keyuse=save_pos;

4705

use->table->reginfo.join_tab->checked_keys.set_bit(use->key);

4709

use->table->reginfo.join_tab->checked_keys.set(use->key);

4706

4710

save_pos++;

4707

4711

}

4708

i=(uint) (save_pos-(KEYUSE*) keyuse->buffer);

4712

i=(uint32_t) (save_pos-(KEYUSE*) keyuse->buffer);

4709

4713

set_dynamic(keyuse,(unsigned char*) &key_end,i);

4710

4714

keyuse->elements=i;

4711

4715

}

4804

4808

4805

4809

/* Intersect the keys of all group fields. */

4806

4810

cur_item= indexed_fields_it++;

4807

possible_keys.merge(cur_item->field->part_of_key);

4811

possible_keys|= cur_item->field->part_of_key;

4808

4812

while ((cur_item= indexed_fields_it++))

4809

4813

{

4810

possible_keys.intersect(cur_item->field->part_of_key);

4814

possible_keys&= cur_item->field->part_of_key;

4811

4815

}

4812

4816

4813

if (!possible_keys.is_clear_all())

4814

join_tab->const_keys.merge(possible_keys);

4817

if (possible_keys.any())

4818

join_tab->const_keys|= possible_keys;

4815

4819

}

4816

4820

4817

4821

4860

4864

Bitmap of bound IN-equalities.

4861

4865

4862

4866

4863

uint64_t get_bound_sj_equalities(TableList *sj_nest,

4867

uint64_t get_bound_sj_equalities(TableList *sj_nest,

4864

4868

table_map remaining_tables)

4865

4869

{

4866

4870

List_iterator<Item> li(sj_nest->nested_join->sj_outer_expr_list);

4898

4902

@param join pointer to the structure providing all context info

4899

4903

for the query

4900

4904

@param s the table to be joined by the function

4901

@param thd thread for the connection that submitted the query

4905

@param session thread for the connection that submitted the query

4902

4906

@param remaining_tables set of tables not included into the partial plan yet

4903

4907

@param idx the length of the partial plan

4904

4908

@param record_count estimate for the number of records returned by the

4912

4916

static void

4913

4917

best_access_path(JOIN *join,

4914

4918

JOIN_TAB *s,

4915

THD *thd,

4919

Session *session,

4916

4920

table_map remaining_tables,

4917

4921

uint32_t idx,

4918

4922

double record_count,

4919

double read_time __attribute__((unused)))

4923

double)

4920

4924

{

4921

4925

KEYUSE *best_key= 0;

4922

4926

uint32_t best_max_key_part= 0;

4947

4951

3. All correlation references from this sj-nest are bound

4948

4952

4949

4953

if (s->emb_sj_nest && // (1)

4950

s->emb_sj_nest->sj_in_exprs < 64 &&

4954

s->emb_sj_nest->sj_in_exprs < 64 &&

4951

4955

((remaining_tables & s->emb_sj_nest->sj_inner_tables) == // (2)

4952

4956

s->emb_sj_nest->sj_inner_tables) && // (2)

4953

4957

join->cur_emb_sj_nests == s->emb_sj_nest->sj_inner_tables && // (3)

4954

4958

!(remaining_tables & s->emb_sj_nest->nested_join->sj_corr_tables)) // (4)

4955

4959

{

4956

4960

/* This table is an InsideOut scan candidate */

4957

bound_sj_equalities= get_bound_sj_equalities(s->emb_sj_nest,

4961

bound_sj_equalities= get_bound_sj_equalities(s->emb_sj_nest,

4958

4962

remaining_tables);

4959

4963

try_sj_inside_out= true;

4960

4964

}

4982

4986

uint32_t keypart= keyuse->keypart;

4983

4987

table_map best_part_found_ref= 0;

4984

4988

double best_prev_record_reads= DBL_MAX;

4985

4989

4986

4990

do /* For each way to access the keypart */

4987

4991

{

4988

4992

5047

5051

5048

5052

Check if InsideOut scan is applicable:

5049

5053

1. All IN-equalities are either "bound" or "handled"

5050

2. Index keyparts are

5054

2. Index keyparts are

5051

5055

...

5052

5056

5053

if (try_sj_inside_out &&

5054

table->covering_keys.is_set(key) &&

5057

if (try_sj_inside_out &&

5058

table->covering_keys.test(key) &&

5055

5059

(handled_sj_equalities | bound_sj_equalities) == // (1)

5056

5060

PREV_BITS(uint64_t, s->emb_sj_nest->sj_in_exprs)) // (1)

5057

5061

{

5110

5114

ReuseRangeEstimateForRef-1:

5111

5115

We get here if we've found a ref(const) (c_i are constants):

5112

5116

"(keypart1=c1) AND ... AND (keypartN=cN)" [ref_const_cond]

5113

5114

If range optimizer was able to construct a "range"

5117

5118

If range optimizer was able to construct a "range"

5115

5119

access on this index, then its condition "quick_cond" was

5116

5120

eqivalent to ref_const_cond (*), and we can re-use E(#rows)

5117

5121

from the range optimizer.

5118

5119

Proof of (*): By properties of range and ref optimizers

5120

quick_cond will be equal or tighther than ref_const_cond.

5121

ref_const_cond already covers "smallest" possible interval -

5122

a singlepoint interval over all keyparts. Therefore,

5123

quick_cond is equivalent to ref_const_cond (if it was an

5122

5123

Proof of (*): By properties of range and ref optimizers

5124

quick_cond will be equal or tighther than ref_const_cond.

5125

ref_const_cond already covers "smallest" possible interval -

5126

a singlepoint interval over all keyparts. Therefore,

5127

quick_cond is equivalent to ref_const_cond (if it was an

5124

5128

empty interval we wouldn't have got here).

5125

5129

5126

if (table->quick_keys.is_set(key))

5130

if (table->quick_keys.test(key))

5127

5131

records= (double) table->quick_rows[key];

5128

5132

else

5129

5133

{

5146

5150

5147

5151

ReuseRangeEstimateForRef-2: We get here if we could not reuse

5148

5152

E(#rows) from range optimizer. Make another try:

5149

5153

5150

5154

If range optimizer produced E(#rows) for a prefix of the ref

5151

5155

access we're considering, and that E(#rows) is lower then our

5152

5156

current estimate, make an adjustment. The criteria of when we

5153

5157

can make an adjustment is a special case of the criteria used

5154

5158

in ReuseRangeEstimateForRef-3.

5155

5159

5156

if (table->quick_keys.is_set(key) &&

5160

if (table->quick_keys.test(key) &&

5157

5161

const_part & (1 << table->quick_key_parts[key]) &&

5158

5162

table->quick_n_ranges[key] == 1 &&

5159

5163

records > (double) table->quick_rows[key])

5163

5167

}

5164

5168

/* Limit the number of matched rows */

5165

5169

tmp= records;

5166

set_if_smaller(tmp, (double) thd->variables.max_seeks_for_key);

5167

if (table->covering_keys.is_set(key))

5170

set_if_smaller(tmp, (double) session->variables.max_seeks_for_key);

5171

if (table->covering_keys.test(key))

5168

5172

{

5169

5173

/* we can use only index tree */

5170

5174

tmp= record_count * table->file->index_only_read_time(key, tmp);

5188

5192

5189

5193

ReuseRangeEstimateForRef-3:

5190

5194

We're now considering a ref[or_null] access via

5191

(t.keypart1=e1 AND ... AND t.keypartK=eK) [ OR

5192

(same-as-above but with one cond replaced

5195

(t.keypart1=e1 AND ... AND t.keypartK=eK) [ OR

5196

(same-as-above but with one cond replaced

5193

5197

with "t.keypart_i IS NULL")] (**)

5194

5198

5195

5199

Try re-using E(#rows) from "range" optimizer:

5196

5200

We can do so if "range" optimizer used the same intervals as

5197

in (**). The intervals used by range optimizer may be not

5201

in (**). The intervals used by range optimizer may be not

5198

5202

available at this point (as "range" access might have choosen to

5199

5203

create quick select over another index), so we can't compare

5200

5204

them to (**). We'll make indirect judgements instead.

5201

5205

The sufficient conditions for re-use are:

5202

5206

(C1) All e_i in (**) are constants, i.e. found_ref==false. (if

5203

5207

this is not satisfied we have no way to know which ranges

5204

will be actually scanned by 'ref' until we execute the

5208

will be actually scanned by 'ref' until we execute the

5205

5209

join)

5206

5210

(C2) max #key parts in 'range' access == K == max_key_part (this

5207

5211

is apparently a necessary requirement)

5208

5212

5209

We also have a property that "range optimizer produces equal or

5213

We also have a property that "range optimizer produces equal or

5210

5214

tighter set of scan intervals than ref(const) optimizer". Each

5211

of the intervals in (**) are "tightest possible" intervals when

5212

one limits itself to using keyparts 1..K (which we do in #2).

5215

of the intervals in (**) are "tightest possible" intervals when

5216

one limits itself to using keyparts 1..K (which we do in #2).

5213

5217

From here it follows that range access used either one, or

5214

5218

both of the (I1) and (I2) intervals:

5215

5216

(t.keypart1=c1 AND ... AND t.keypartK=eK) (I1)

5217

(same-as-above but with one cond replaced

5219

5220

(t.keypart1=c1 AND ... AND t.keypartK=eK) (I1)

5221

(same-as-above but with one cond replaced

5218

5222

with "t.keypart_i IS NULL") (I2)

5219

5223

5220

5224

The remaining part is to exclude the situation where range

5224

5228

5225

5229

(C3) "range optimizer used (have ref_or_null?2:1) intervals"

5226

5230

5227

if (table->quick_keys.is_set(key) && !found_ref && //(C1)

5231

if (table->quick_keys.test(key) && !found_ref && //(C1)

5228

5232

table->quick_key_parts[key] == max_key_part && //(C2)

5229

5233

table->quick_n_ranges[key] == 1+((ref_or_null_part)?1:0)) //(C3)

5230

5234

{

5235

5239

/* Check if we have statistic about the distribution */

5236

5240

if ((records= keyinfo->rec_per_key[max_key_part-1]))

5237

5241

{

5238

5242

5239

5243

Fix for the case where the index statistics is too

5240

optimistic: If

5244

optimistic: If

5241

5245

(1) We're considering ref(const) and there is quick select

5242

on the same index,

5246

on the same index,

5243

5247

(2) and that quick select uses more keyparts (i.e. it will

5244

5248

scan equal/smaller interval then this ref(const))

5245

5249

(3) and E(#rows) for quick select is higher then our

5246

5250

estimate,

5247

Then

5251

Then

5248

5252

We'll use E(#rows) from quick select.

5249

5253

5250

5254

Q: Why do we choose to use 'ref'? Won't quick select be

5251

5255

cheaper in some cases ?

5252

5256

TODO: figure this out and adjust the plan choice if needed.

5253

5257

5254

if (!found_ref && table->quick_keys.is_set(key) && // (1)

5258

if (!found_ref && table->quick_keys.test(key) && // (1)

5255

5259

table->quick_key_parts[key] > max_key_part && // (2)

5256

5260

records < (double)table->quick_rows[key]) // (3)

5257

5261

records= (double)table->quick_rows[key];

5294

5298

tmp= a;

5295

5299

set_if_bigger(tmp,1.0);

5296

5300

}

5297

records = (ulong) tmp;

5301

records = (uint32_t) tmp;

5298

5302

}

5299

5303

5300

5304

if (ref_or_null_part)

5307

5311

5308

5312

ReuseRangeEstimateForRef-4: We get here if we could not reuse

5309

5313

E(#rows) from range optimizer. Make another try:

5310

5311

If range optimizer produced E(#rows) for a prefix of the ref

5314

5315

If range optimizer produced E(#rows) for a prefix of the ref

5312

5316

access we're considering, and that E(#rows) is lower then our

5313

5317

current estimate, make the adjustment.

5314

5318

5316

5320

optimizer is the same as in ReuseRangeEstimateForRef-3,

5317

5321

applied to first table->quick_key_parts[key] key parts.

5318

5322

5319

if (table->quick_keys.is_set(key) &&

5323

if (table->quick_keys.test(key) &&

5320

5324

table->quick_key_parts[key] <= max_key_part &&

5321

5325

const_part & (1 << table->quick_key_parts[key]) &&

5322

5326

table->quick_n_ranges[key] == 1 + ((ref_or_null_part &

5328

5332

}

5329

5333

5330

5334

/* Limit the number of matched rows */

5331

set_if_smaller(tmp, (double) thd->variables.max_seeks_for_key);

5332

if (table->covering_keys.is_set(key))

5335

set_if_smaller(tmp, (double) session->variables.max_seeks_for_key);

5336

if (table->covering_keys.test(key))

5333

5337

{

5334

5338

/* we can use only index tree */

5335

5339

tmp= record_count * table->file->index_only_read_time(key, tmp);

5378

5382

(or index scan, or quick select), or 'ref' is more expensive than

5379

5383

any of them.

5380

5384

(2) This doesn't hold: the best way to perform table scan is to to perform

5381

'range' access using index IDX, and the best way to perform 'ref'

5385

'range' access using index IDX, and the best way to perform 'ref'

5382

5386

access is to use the same index IDX, with the same or more key parts.

5383

(note: it is not clear how this rule is/should be extended to

5387

(note: it is not clear how this rule is/should be extended to

5384

5388

index_merge quick selects)

5385

5389

(3) See above note about InnoDB.

5386

5390

(4) NOT ("FORCE INDEX(...)" is used for table and there is 'ref' access

5395

5399

!(s->quick && best_key && s->quick->index == best_key->key && // (2)

5396

5400

best_max_key_part >= s->table->quick_key_parts[best_key->key]) &&// (2)

5397

5401

!((s->table->file->ha_table_flags() & HA_TABLE_SCAN_ON_INDEX) && // (3)

5398

! s->table->covering_keys.is_clear_all() && best_key && !s->quick) &&// (3)

5402

! s->table->covering_keys.none() && best_key && !s->quick) &&// (3)

5399

5403

!(s->table->force_index && best_key && !s->quick)) // (4)

5400

5404

{ // Check full join

5401

5405

ha_rows rnd_records= s->found_records;

5402

5406

5403

5407

If there is a filtering condition on the table (i.e. ref analyzer found

5404

5408

at least one "table.keyXpartY= exprZ", where exprZ refers only to tables

5405

preceding this table in the join order we're now considering), then

5409

preceding this table in the join order we're now considering), then

5406

5410

assume that 25% of the rows will be filtered out by this condition.

5407

5411

5408

5412

This heuristic is supposed to force tables used in exprZ to be before

5429

5433

For each record we:

5430

5434

- read record range through 'quick'

5431

5435

- skip rows which does not satisfy WHERE constraints

5432

TODO:

5436

TODO:

5433

5437

We take into account possible use of join cache for ALL/index

5434

access (see first else-branch below), but we don't take it into

5438

access (see first else-branch below), but we don't take it into

5435

5439

account here for range/index_merge access. Find out why this is so.

5436

5440

5437

5441

tmp= record_count *

5446

5450

{

5447

5451

5448

5452

For each record we have to:

5449

- read the whole table record

5453

- read the whole table record

5450

5454

- skip rows which does not satisfy join condition

5451

5455

5452

5456

tmp= record_count *

5458

5462

/* We read the table as many times as join buffer becomes full. */

5459

5463

tmp*= (1.0 + floor((double) cache_record_length(join,idx) *

5460

5464

record_count /

5461

(double) thd->variables.join_buff_size));

5462

5465

(double) session->variables.join_buff_size));

5466

5463

5467

We don't make full cartesian product between rows in the scanned

5464

5468

table and existing records because we skip all rows from the

5465

scanned table, which does not satisfy join condition when

5469

scanned table, which does not satisfy join condition when

5466

5470

we read the table (see flush_cached_records for details). Here we

5467

5471

take into account cost to read and skip these records.

5468

5472

5536

5540

static bool

5537

5541

choose_plan(JOIN *join, table_map join_tables)

5538

5542

{

5539

uint32_t search_depth= join->thd->variables.optimizer_search_depth;

5540

uint32_t prune_level= join->thd->variables.optimizer_prune_level;

5543

uint32_t search_depth= join->session->variables.optimizer_search_depth;

5544

uint32_t prune_level= join->session->variables.optimizer_prune_level;

5541

5545

bool straight_join= test(join->select_options & SELECT_STRAIGHT_JOIN);

5542

5546

5543

5547

join->cur_embedding_map= 0;

5544

5548

reset_nj_counters(join->join_list);

5545

5549

5546

5550

if (SELECT_STRAIGHT_JOIN option is set)

5547

reorder tables so dependent tables come after tables they depend

5548

on, otherwise keep tables in the order they were specified in the query

5551

reorder tables so dependent tables come after tables they depend

5552

on, otherwise keep tables in the order they were specified in the query

5549

5553

else

5550

5554

Apply heuristic: pre-sort all access plans with respect to the number of

5551

5555

records accessed.

5568

5572

join->best_read= DBL_MAX;

5569

5573

if (find_best(join, join_tables, join->const_tables, 1.0, 0.0))

5570

5574

return(true);

5571

}

5575

}

5572

5576

else

5573

5577

{

5574

5578

if (search_depth == 0)

5579

5583

}

5580

5584

}

5581

5585

5582

5586

5583

5587

Store the cost of this query into a user variable

5584

5588

Don't update last_query_cost for statements that are not "flat joins" :

5585

5589

i.e. they have subqueries, unions or call stored procedures.

5586

5590

TODO: calculate a correct cost for a query with subqueries and UNIONs.

5587

5591

5588

if (join->thd->lex->is_single_level_stmt())

5589

join->thd->status_var.last_query_cost= join->best_read;

5592

if (join->session->lex->is_single_level_stmt())

5593

join->session->status_var.last_query_cost= join->best_read;

5590

5594

return(false);

5591

5595

}

5592

5596

5607

5611

a: dependent = 0x0 table->map = 0x1 found_records = 3 ptr = 0x907e6b0

5608

5612

b: dependent = 0x0 table->map = 0x2 found_records = 3 ptr = 0x907e838

5609

5613

c: dependent = 0x6 table->map = 0x10 found_records = 2 ptr = 0x907ecd0

5610

5614

5611

5615

@retval

5612

5616

1 if first is bigger

5613

5617

@retval

5616

5620

0 if equal

5617

5621

5618

5622

5619

static int

5620

join_tab_cmp(const void* ptr1, const void* ptr2)

5623

int join_tab_cmp(const void* ptr1, const void* ptr2)

5621

5624

{

5622

5625

JOIN_TAB *jt1= *(JOIN_TAB**) ptr1;

5623

5626

JOIN_TAB *jt2= *(JOIN_TAB**) ptr2;

5625

5628

if (jt1->dependent & jt2->table->map)

5626

5629

return 1;

5627

5630

if (jt2->dependent & jt1->table->map)

5628

return -1;

5631

return -1;

5629

5632

if (jt1->found_records > jt2->found_records)

5630

5633

return 1;

5631

5634

if (jt1->found_records < jt2->found_records)

5632

return -1;

5635

return -1;

5633

5636

return jt1 > jt2 ? 1 : (jt1 < jt2 ? -1 : 0);

5634

5637

}

5635

5638

5641

Same as join_tab_cmp, but for use with SELECT_STRAIGHT_JOIN.

5639

5642

5640

5643

5641

static int

5642

join_tab_cmp_straight(const void* ptr1, const void* ptr2)

5644

int join_tab_cmp_straight(const void* ptr1, const void* ptr2)

5643

5645

{

5644

5646

JOIN_TAB *jt1= *(JOIN_TAB**) ptr1;

5645

5647

JOIN_TAB *jt2= *(JOIN_TAB**) ptr2;

5734

5736

uint32_t idx= join->const_tables;

5735

5737

double record_count= 1.0;

5736

5738

double read_time= 0.0;

5737

5739

5738

5740

for (JOIN_TAB **pos= join->best_ref + idx ; (s= *pos) ; pos++)

5739

5741

{

5740

5742

/* Find the best access method from 's' to the current partial plan */

5741

5743

advance_sj_state(join_tables, s);

5742

best_access_path(join, s, join->thd, join_tables, idx,

5744

best_access_path(join, s, join->session, join_tables, idx,

5743

5745

record_count, read_time);

5744

5746

/* compute the cost of the new plan extended with 's' */

5745

5747

record_count*= join->positions[idx].records_read;

5910

5912

each table. The choice of a table order and an access path for each table

5911

5913

constitutes a query execution plan (QEP) that fully specifies how to

5912

5914

execute the query.

5913

5915

5914

5916

The maximal size of the found plan is controlled by the parameter

5915

5917

'search_depth'. When search_depth == N, the resulting plan is complete and

5916

5918

can be used directly as a QEP. If search_depth < N, the found plan consists

6027

6029

uint32_t search_depth,

6028

6030

uint32_t prune_level)

6029

6031

{

6030

THD *thd= join->thd;

6031

if (thd->killed) // Abort

6032

Session *session= join->session;

6033

if (session->killed) // Abort

6032

6034

return(true);

6033

6035

6034

6036

6035

6037

'join' is a partial plan with lower cost than the best plan so far,

6036

6038

so continue expanding it further with the tables in 'remaining_tables'.

6037

6039

6042

6044

for (JOIN_TAB **pos= join->best_ref + idx ; (s= *pos) ; pos++)

6043

6045

{

6044

6046

table_map real_table_bit= s->table->map;

6045

if ((remaining_tables & real_table_bit) &&

6046

!(remaining_tables & s->dependent) &&

6047

if ((remaining_tables & real_table_bit) &&

6048

!(remaining_tables & s->dependent) &&

6047

6049

(!idx || !check_interleaving_with_nj(join->positions[idx-1].table, s)))

6048

6050

{

6049

6051

double current_record_count, current_read_time;

6050

6052

advance_sj_state(remaining_tables, s);

6051

6053

6052

6054

6053

psergey-insideout-todo:

6054

when best_access_path() detects it could do an InsideOut scan or

6055

some other scan, have it return an insideout scan and a flag that

6056

requests to "fork" this loop iteration. (Q: how does that behave

6055

psergey-insideout-todo:

6056

when best_access_path() detects it could do an InsideOut scan or

6057

some other scan, have it return an insideout scan and a flag that

6058

requests to "fork" this loop iteration. (Q: how does that behave

6057

6059

when the depth is insufficient??)

6058

6060

6059

6061

/* Find the best access method from 's' to the current partial plan */

6060

best_access_path(join, s, thd, remaining_tables, idx,

6062

best_access_path(join, s, session, remaining_tables, idx,

6061

6063

record_count, read_time);

6062

6064

/* Compute the cost of extending the plan with 's' */

6063

6065

current_record_count= record_count * join->positions[idx].records_read;

6152

6154

find_best(JOIN *join,table_map rest_tables,uint32_t idx,double record_count,

6153

6155

double read_time)

6154

6156

{

6155

THD *thd= join->thd;

6156

if (thd->killed)

6157

Session *session= join->session;

6158

if (session->killed)

6157

6159

return(true);

6158

6160

if (!rest_tables)

6159

6161

{

6182

6184

{

6183

6185

double records, best;

6184

6186

advance_sj_state(rest_tables, s);

6185

best_access_path(join, s, thd, rest_tables, idx, record_count,

6187

best_access_path(join, s, session, rest_tables, idx, record_count,

6186

6188

read_time);

6187

6189

records= join->positions[idx].records_read;

6188

6190

best= join->positions[idx].read_time;

6223

6225

Find how much space the prevous read not const tables takes in cache.

6224

6226

6225

6227

6226

static void calc_used_field_length(THD *thd __attribute__((unused)),

6227

JOIN_TAB *join_tab)

6228

static void calc_used_field_length(Session *, JOIN_TAB *join_tab)

6228

6229

{

6229

6230

uint32_t null_fields,blobs,fields,rec_length;

6230

6231

Field **f_ptr,*field;

6231

MY_BITMAP *read_set= join_tab->table->read_set;;

6232

6233

null_fields= blobs= fields= rec_length=0;

6234

for (f_ptr=join_tab->table->field ; (field= *f_ptr) ; f_ptr++)

6235

{

6236

if (bitmap_is_set(read_set, field->field_index))

6236

if (field->isReadSet())

6237

{

6238

uint32_t flags=field->flags;

6239

fields++;

6250

rec_length+=sizeof(bool);

6251

if (blobs)

6252

{

6253

uint32_t blob_length=(uint) (join_tab->table->file->stats.mean_rec_length-

6253

uint32_t blob_length=(uint32_t) (join_tab->table->file->stats.mean_rec_length-

6254

(join_tab->table->getRecordLength()- rec_length));

6255

rec_length+=(uint) cmax((uint)4,blob_length);

6255

rec_length+=(uint32_t) cmax((uint32_t)4,blob_length);

6256

}

6257

join_tab->used_fields=fields;

6258

join_tab->used_fieldlength=rec_length;

6259

join_tab->used_blobs=blobs;

6257

join_tab->used_fields= fields;

6258

join_tab->used_fieldlength= rec_length;

6259

join_tab->used_blobs= blobs;

6260

}

6261

6262

6265

{

6266

uint32_t length=0;

6267

JOIN_TAB **pos,**end;

6268

THD *thd=join->thd;

6268

Session *session=join->session;

6269

6270

for (pos=join->best_ref+join->const_tables,end=join->best_ref+idx ;

6271

pos != end ;

6273

{

6274

JOIN_TAB *join_tab= *pos;

6275

if (!join_tab->used_fieldlength) /* Not calced yet */

6276

calc_used_field_length(thd, join_tab);

6276

calc_used_field_length(session, join_tab);

6277

length+=join_tab->used_fieldlength;

6278

}

6279

return length;

6296

tables within that join order (specified in found_ref), find out how many

6297

distinct row combinations of subset tables will be in the result of the

6298

partial join order.

6299

6300

This is used as follows: Suppose we have a table accessed with a ref-based

6301

method. The ref access depends on current rows of tables in found_ref.

6302

We want to count # of different ref accesses. We assume two ref accesses

6307

6308

and a join order:

6309

t1, ref access on t1.key=c1

6310

t2, ref access on t2.key=c2

6311

t3, ref access on t3.key=t1.field

6312

6310

t2, ref access on t2.key=c2

6311

t3, ref access on t3.key=t1.field

6312

6313

For t1: n_ref_scans = 1, n_distinct_ref_scans = 1

6314

For t2: n_ref_scans = records_read(t1), n_distinct_ref_scans=1

6315

For t3: n_ref_scans = records_read(t1)*records_read(t2)

6316

n_distinct_ref_scans = #records_read(t1)

6317

6318

The reason for having this function (at least the latest version of it)

6319

is that we need to account for buffering in join execution.

6320

6319

is that we need to account for buffering in join execution.

6320

6321

An edge-case example: if we have a non-first table in join accessed via

6322

ref(const) or ref(param) where there is a small number of different

6323

values of param, then the access will likely hit the disk cache and will

6324

not require any disk seeks.

6325

6326

The proper solution would be to assume an LRU disk cache of some size,

6327

calculate probability of cache hits, etc. For now we just count

6328

identical ref accesses as one.

6329

6330

RETURN

6330

RETURN

6331

Expected number of row combinations

6332

6333

6341

if (pos->table->table->map & found_ref)

6342

{

6343

found_ref|= pos->ref_depend_map;

6344

6345

For the case of "t1 LEFT JOIN t2 ON ..." where t2 is a const table

6346

with no matching row we will get position[t2].records_read==0.

6347

Actually the size of output is one null-complemented row, therefore

6344

6345

For the case of "t1 LEFT JOIN t2 ON ..." where t2 is a const table

6346

with no matching row we will get position[t2].records_read==0.

6347

Actually the size of output is one null-complemented row, therefore

6348

we will use value of 1 whenever we get records_read==0.

6349

6350

Note

6351

- the above case can't occur if inner part of outer join has more

6351

- the above case can't occur if inner part of outer join has more

6352

than one table: table with no matches will not be marked as const.

6353

6354

- Ideally we should add 1 to records_read for every possible null-

6377

JOIN_TAB *join_tab,*j;

6378

KEYUSE *keyuse;

6379

uint32_t table_count;

6380

THD *thd=join->thd;

6380

Session *session=join->session;

6381

6382

table_count=join->tables;

6383

if (!(join->join_tab=join_tab=

6384

(JOIN_TAB*) thd->alloc(sizeof(JOIN_TAB)*table_count)))

6384

(JOIN_TAB*) session->alloc(sizeof(JOIN_TAB)*table_count)))

6385

return(true);

6386

6387

join->full_join=0;

6404

6405

if (j->type == JT_SYSTEM)

6406

continue;

6407

if (j->keys.is_clear_all() || !(keyuse= join->best_positions[tablenr].key))

6407

if (j->keys.none() || !(keyuse= join->best_positions[tablenr].key))

6408

{

6409

j->type=JT_ALL;

6410

if (tablenr != join->const_tables)

6425

table_map used_tables)

6426

{

6427

KEYUSE *keyuse=org_keyuse;

6428

THD *thd= join->thd;

6428

Session *session= join->session;

6429

uint32_t keyparts,length,key;

6430

Table *table;

6431

KEY *keyinfo;

6464

j->ref.key_parts=keyparts;

6465

j->ref.key_length=length;

6466

j->ref.key=(int) key;

6467

if (!(j->ref.key_buff= (unsigned char*) thd->calloc(ALIGN_SIZE(length)*2)) ||

6468

!(j->ref.key_copy= (store_key**) thd->alloc((sizeof(store_key*) *

6467

if (!(j->ref.key_buff= (unsigned char*) session->calloc(ALIGN_SIZE(length)*2)) ||

6468

!(j->ref.key_copy= (store_key**) session->alloc((sizeof(store_key*) *

6469

(keyparts+1)))) ||

6470

!(j->ref.items= (Item**) thd->alloc(sizeof(Item*)*keyparts)) ||

6471

!(j->ref.cond_guards= (bool**) thd->alloc(sizeof(uint*)*keyparts)))

6470

!(j->ref.items= (Item**) session->alloc(sizeof(Item*)*keyparts)) ||

6471

!(j->ref.cond_guards= (bool**) session->alloc(sizeof(uint*)*keyparts)))

6472

{

6473

return(true);

6474

}

6492

uint32_t maybe_null= test(keyinfo->key_part[i].null_bit);

6493

j->ref.items[i]=keyuse->val; // Save for cond removal

6494

j->ref.cond_guards[i]= keyuse->cond_guard;

6495

if (keyuse->null_rejecting)

6495

if (keyuse->null_rejecting)

6496

j->ref.null_rejecting |= 1 << i;

6497

keyuse_uses_no_tables= keyuse_uses_no_tables && !keyuse->used_tables;

6498

if (!keyuse->used_tables &&

6499

!(join->select_options & SELECT_DESCRIBE))

6500

{ // Compare against constant

6501

store_key_item tmp(thd, keyinfo->key_part[i].field,

6501

store_key_item tmp(session, keyinfo->key_part[i].field,

6502

key_buff + maybe_null,

6503

maybe_null ? key_buff : 0,

6504

keyinfo->key_part[i].length, keyuse->val);

6505

if (thd->is_fatal_error)

6505

if (session->is_fatal_error)

6506

return(true);

6507

tmp.copy();

6508

}

6509

else

6510

*ref_key++= get_store_key(thd,

6510

*ref_key++= get_store_key(session,

6511

keyuse,join->const_table_map,

6512

&keyinfo->key_part[i],

6513

key_buff, maybe_null);

6550

6551

6552

static store_key *

6553

get_store_key(THD *thd, KEYUSE *keyuse, table_map used_tables,

6553

get_store_key(Session *session, KEYUSE *keyuse, table_map used_tables,

6554

KEY_PART_INFO *key_part, unsigned char *key_buff, uint32_t maybe_null)

6555

{

6556

if (!((~used_tables) & keyuse->used_tables)) // if const item

6557

{

6558

return new store_key_const_item(thd,

6558

return new store_key_const_item(session,

6559

key_part->field,

6560

key_buff + maybe_null,

6561

maybe_null ? key_buff : 0,

6566

(keyuse->val->type() == Item::REF_ITEM &&

6567

((Item_ref*)keyuse->val)->ref_type() == Item_ref::OUTER_REF &&

6568

(*(Item_ref**)((Item_ref*)keyuse->val)->ref)->ref_type() ==

6569

Item_ref::DIRECT_REF &&

6569

Item_ref::DIRECT_REF &&

6570

keyuse->val->real_item()->type() == Item::FIELD_ITEM))

6571

return new store_key_field(thd,

6571

return new store_key_field(session,

6572

key_part->field,

6573

key_buff + maybe_null,

6574

maybe_null ? key_buff : 0,

6575

key_part->length,

6576

((Item_field*) keyuse->val->real_item())->field,

6577

keyuse->val->full_name());

6578

return new store_key_item(thd,

6578

return new store_key_item(session,

6579

key_part->field,

6580

key_buff + maybe_null,

6581

maybe_null ? key_buff : 0,

6595

{

6596

bool error;

6597

Table *table= field->table;

6598

THD *thd= table->in_use;

6599

ha_rows cuted_fields=thd->cuted_fields;

6598

Session *session= table->in_use;

6599

ha_rows cuted_fields=session->cuted_fields;

6600

6601

6602

we should restore old value of count_cuted_fields because

6603

store_val_in_field can be called from mysql_insert

6603

store_val_in_field can be called from mysql_insert

6604

with select_insert, which make count_cuted_fields= 1

6605

6606

enum_check_fields old_count_cuted_fields= thd->count_cuted_fields;

6607

thd->count_cuted_fields= check_flag;

6606

enum_check_fields old_count_cuted_fields= session->count_cuted_fields;

6607

session->count_cuted_fields= check_flag;

6608

error= item->save_in_field(field, 1);

6609

thd->count_cuted_fields= old_count_cuted_fields;

6610

return error || cuted_fields != thd->cuted_fields;

6609

session->count_cuted_fields= old_count_cuted_fields;

6610

return error || cuted_fields != session->cuted_fields;

6611

}

6612

6613

6623

6624

if (!join->table_reexec)

6625

{

6626

if (!(join->table_reexec= (Table**) join->thd->alloc(sizeof(Table*))))

6626

if (!(join->table_reexec= (Table**) join->session->alloc(sizeof(Table*))))

6627

return(true); /* purecov: inspected */

6628

if (join->tmp_join)

6629

join->tmp_join->table_reexec= join->table_reexec;

6631

if (!join->join_tab_reexec)

6632

{

6633

if (!(join->join_tab_reexec=

6634

(JOIN_TAB*) join->thd->alloc(sizeof(JOIN_TAB))))

6634

(JOIN_TAB*) join->session->alloc(sizeof(JOIN_TAB))))

6635

return(true); /* purecov: inspected */

6636

if (join->tmp_join)

6637

join->tmp_join->join_tab_reexec= join->join_tab_reexec;

6659

join_tab->select_cond=0;

6660

join_tab->quick=0;

6661

join_tab->type= JT_ALL; /* Map through all records */

6662

join_tab->keys.init();

6663

join_tab->keys.set_all(); /* test everything in quick */

6662

join_tab->keys.set(); /* test everything in quick */

6664

6663

join_tab->info=0;

6665

6664

join_tab->on_expr_ref=0;

6666

6665

join_tab->last_inner= 0;

6708

6707

add "t1.field IS NOT NULL" to t1's table condition. @n

6709

6708

6710

6709

Description of the optimization:

6711

6710

6712

6711

We look through equalities choosen to perform ref/eq_ref access,

6713

6712

pick equalities that have form "tbl.part_of_key = othertbl.field"

6714

6713

(where othertbl is a non-const table and othertbl.field may be NULL)

6736

6735

This optimization doesn't affect the choices that ref, range, or join

6737

6736

optimizer make. This was intentional because this was added after 4.1

6738

6737

was GA.

6739

6738

6740

6739

Implementation overview

6741

6740

1. update_ref_and_keys() accumulates info about null-rejecting

6742

6741

predicates in in KEY_FIELD::null_rejecting

6751

6750

for (uint32_t i=join->const_tables ; i < join->tables ; i++)

6752

6751

{

6753

6752

JOIN_TAB *tab=join->join_tab+i;

6754

if ((tab->type == JT_REF || tab->type == JT_EQ_REF ||

6753

if ((tab->type == JT_REF || tab->type == JT_EQ_REF ||

6755

6754

tab->type == JT_REF_OR_NULL) &&

6756

6755

!tab->table->maybe_null)

6757

6756

{

6775

6774

return;

6776

6775

6777

6776

We need to do full fix_fields() call here in order to have correct

6778

notnull->const_item(). This is needed e.g. by test_quick_select

6779

when it is called from make_join_select after this function is

6777

notnull->const_item(). This is needed e.g. by test_quick_select

6778

when it is called from make_join_select after this function is

6780

6779

called.

6781

6780

6782

if (notnull->fix_fields(join->thd, &notnull))

6781

if (notnull->fix_fields(join->session, &notnull))

6783

6782

return;

6784

6783

add_cond_and_fix(&referred_tab->select_cond, notnull);

6785

6784

}

6840

6839

corresponding first inner table through the field t0->on_expr_ref.

6841

6840

Here ti are structures of the JOIN_TAB type.

6842

6841

6843

EXAMPLE. For the query:

6842

EXAMPLE. For the query:

6844

6843

@code

6845

6844

SELECT * FROM t1

6846

6845

LEFT JOIN

6853

6852

is selected, the following references will be set;

6854

6853

t4->last_inner=[t4], t4->first_inner=[t4], t4->first_upper=[t2]

6855

6854

t2->last_inner=[t4], t2->first_inner=t3->first_inner=[t2],

6856

on expression (t1.a=t2.a AND t1.b=t3.b) will be attached to

6855

on expression (t1.a=t2.a AND t1.b=t3.b) will be attached to

6857

6856

*t2->on_expr_ref, while t3.a=t4.a will be attached to *t4->on_expr_ref.

6858

6857

6859

6858

@param join reference to the info fully describing the query

6877

6876

6878

6877

if (tbl->outer_join)

6879

6878

{

6880

6879

6881

6880

Table tab is the only one inner table for outer join.

6882

6881

(Like table t4 for the table reference t3 LEFT JOIN t4 ON t3.a=t4.a

6883

6882

is in the query above.)

6887

6886

tab->cond_equal= tbl->cond_equal;

6888

6887

if (embedding)

6889

6888

tab->first_upper= embedding->nested_join->first_nested;

6890

}

6889

}

6891

6890

for ( ; embedding ; embedding= embedding->embedding)

6892

6891

{

6893

6892

/* Ignore sj-nests: */

6896

6895

nested_join_st *nested_join= embedding->nested_join;

6897

6896

if (!nested_join->counter_)

6898

6897

{

6899

6898

6900

6899

Table tab is the first inner table for nested_join.

6901

6900

Save reference to it in the nested join structure.

6902

6901

6903

6902

nested_join->first_nested= tab;

6904

6903

tab->on_expr_ref= &embedding->on_expr;

6905

6904

tab->cond_equal= tbl->cond_equal;

6906

6905

if (embedding->embedding)

6907

6906

tab->first_upper= embedding->embedding->nested_join->first_nested;

6908

6907

}

6909

if (!tab->first_inner)

6908

if (!tab->first_inner)

6910

6909

tab->first_inner= nested_join->first_nested;

6911

6910

if (++nested_join->counter_ < nested_join->join_list.elements)

6912

6911

break;

6921

6920

static bool

6922

6921

make_join_select(JOIN *join,SQL_SELECT *select,COND *cond)

6923

6922

{

6924

THD *thd= join->thd;

6923

Session *session= join->session;

6925

6924

if (select)

6926

6925

{

6927

6926

add_not_null_conds(join);

6928

6927

table_map used_tables;

6929

6928

if (cond) /* Because of QUICK_GROUP_MIN_MAX_SELECT */

6930

{ /* there may be a select without a cond. */

6929

{ /* there may be a select without a cond. */

6931

6930

if (join->tables > 1)

6932

6931

cond->update_used_tables(); // Tablenr may have changed

6933

6932

if (join->const_tables == join->tables &&

6934

thd->lex->current_select->master_unit() ==

6935

&thd->lex->unit) // not upper level SELECT

6933

session->lex->current_select->master_unit() ==

6934

&session->lex->unit) // not upper level SELECT

6936

6935

join->const_table_map|=RAND_TABLE_BIT;

6937

6936

{ // Check const tables

6938

6937

COND *const_cond=

6960

6959

if (!cond_tab->select_cond)

6961

6960

return(1);

6962

6961

cond_tab->select_cond->quick_fix_field();

6963

}

6962

}

6964

6963

}

6965

6964

if (const_cond && !const_cond->val_int())

6966

6965

{

6977

6976

first_inner is the X in queries like:

6978

6977

SELECT * FROM t1 LEFT OUTER JOIN (t2 JOIN t3) ON X

6979

6978

6980

JOIN_TAB *first_inner_tab= tab->first_inner;

6979

JOIN_TAB *first_inner_tab= tab->first_inner;

6981

6980

table_map current_map= tab->table->map;

6982

6981

bool use_quick_range=0;

6983

6982

COND *tmp;

6991

6990

used_tables|=current_map;

6992

6991

6993

6992

if (tab->type == JT_REF && tab->quick &&

6994

(uint) tab->ref.key == tab->quick->index &&

6993

(uint32_t) tab->ref.key == tab->quick->index &&

6995

6994

tab->ref.key_length < tab->quick->max_used_key_length)

6996

6995

{

6997

6996

/* Range uses longer key; Use this instead of ref on key */

7001

7000

tab->ref.key= -1;

7002

7001

tab->ref.key_parts=0; // Don't use ref key.

7003

7002

join->best_positions[i].records_read= rows2double(tab->quick->records);

7004

7003

7005

7004

We will use join cache here : prevent sorting of the first

7006

7005

table only and sort at the end.

7007

7006

7039

7038

tab->type == JT_EQ_REF)

7040

7039

{

7041

7040

SQL_SELECT *sel= tab->select= ((SQL_SELECT*)

7042

thd->memdup((unsigned char*) select,

7041

session->memdup((unsigned char*) select,

7043

7042

sizeof(*select)));

7044

7043

if (!sel)

7045

7044

return(1); // End of memory

7048

7047

add a match guard to the pushed down predicate.

7049

7048

The guard will turn the predicate on only after

7050

7049

the first match for outer tables is encountered.

7051

7050

7052

7051

if (cond && tmp)

7053

7052

{

7054

7053

7061

7060

/* Push condition to storage engine if this is enabled

7062

7061

and the condition is not guarded */

7063

7062

tab->table->file->pushed_cond= NULL;

7064

if (thd->variables.engine_condition_pushdown)

7063

if (session->variables.engine_condition_pushdown)

7065

7064

{

7066

COND *push_cond=

7065

COND *push_cond=

7067

7066

make_cond_for_table(tmp, current_map, current_map, 0);

7068

7067

if (push_cond)

7069

7068

{

7081

7080

{

7082

7081

/* Use quick key read if it's a constant and it's not used

7083

7082

with key reading */

7084

if (tab->needed_reg.is_clear_all() && tab->type != JT_EQ_REF

7085

&& (tab->type != JT_REF || (uint) tab->ref.key == tab->quick->index))

7083

if (tab->needed_reg.none() && tab->type != JT_EQ_REF

7084

&& (tab->type != JT_REF || (uint32_t) tab->ref.key == tab->quick->index))

7086

7085

{

7087

7086

sel->quick=tab->quick; // Use value from get_quick_...

7088

sel->quick_keys.clear_all();

7089

sel->needed_reg.clear_all();

7087

sel->quick_keys.reset();

7088

sel->needed_reg.reset();

7090

7089

}

7091

7090

else

7092

7091

{

7094

7093

}

7095

7094

tab->quick=0;

7096

7095

}

7097

uint32_t ref_key=(uint) sel->head->reginfo.join_tab->ref.key+1;

7096

uint32_t ref_key=(uint32_t) sel->head->reginfo.join_tab->ref.key+1;

7098

7097

if (i == join->const_tables && ref_key)

7099

7098

{

7100

if (!tab->const_keys.is_clear_all() &&

7099

if (tab->const_keys.any() &&

7101

7100

tab->table->reginfo.impossible_range)

7102

7101

return(1);

7103

7102

}

7104

7103

else if (tab->type == JT_ALL && ! use_quick_range)

7105

7104

{

7106

if (!tab->const_keys.is_clear_all() &&

7105

if (tab->const_keys.any() &&

7107

7106

tab->table->reginfo.impossible_range)

7108

7107

return(1); // Impossible range

7109

7108

7113

7112

the index if we are using limit and this is the first table

7114

7113

7115

7114

7116

if ((cond && (!tab->keys.is_subset(tab->const_keys) && i > 0)) ||

7117

(!tab->const_keys.is_clear_all() && (i == join->const_tables) && (join->unit->select_limit_cnt < join->best_positions[i].records_read) && ((join->select_options & OPTION_FOUND_ROWS) == false)))

7115

if ((cond && (!((tab->keys & tab->const_keys) == tab->keys) && i > 0)) ||

7116

(!tab->const_keys.none() && (i == join->const_tables) && (join->unit->select_limit_cnt < join->best_positions[i].records_read) && ((join->select_options & OPTION_FOUND_ROWS) == false)))

7118

7117

{

7119

7118

/* Join with outer join condition */

7120

7119

COND *orig_cond=sel->cond;

7130

7129

if (sel->cond && !sel->cond->fixed)

7131

7130

sel->cond->quick_fix_field();

7132

7131

7133

if (sel->test_quick_select(thd, tab->keys,

7132

if (sel->test_quick_select(session, tab->keys,

7134

7133

used_tables & ~ current_map,

7135

7134

(join->select_options &

7136

7135

OPTION_FOUND_ROWS ?

7144

7143

7145

7144

sel->cond=orig_cond;

7146

7145

if (!*tab->on_expr_ref ||

7147

sel->test_quick_select(thd, tab->keys,

7146

sel->test_quick_select(session, tab->keys,

7148

7147

used_tables & ~ current_map,

7149

7148

(join->select_options &

7150

7149

OPTION_FOUND_ROWS ?

7163

7162

else

7164

7163

{

7165

7164

sel->needed_reg=tab->needed_reg;

7166

sel->quick_keys.clear_all();

7165

sel->quick_keys.reset();

7167

7166

}

7168

if (!sel->quick_keys.is_subset(tab->checked_keys) ||

7169

!sel->needed_reg.is_subset(tab->checked_keys))

7167

if (!((tab->checked_keys & sel->quick_keys) == sel->quick_keys) ||

7168

!((tab->checked_keys & sel->needed_reg) == sel->needed_reg))

7170

7169

{

7171

tab->keys=sel->quick_keys;

7172

tab->keys.merge(sel->needed_reg);

7173

tab->use_quick= (!sel->needed_reg.is_clear_all() &&

7174

(select->quick_keys.is_clear_all() ||

7170

tab->keys= sel->quick_keys;

7171

tab->keys|= sel->needed_reg;

7172

tab->use_quick= (!sel->needed_reg.none() &&

7173

(select->quick_keys.none() ||

7175

7174

(select->quick &&

7176

7175

(select->quick->records >= 100L)))) ?

7177

7176

2 : 1;

7186

7185

current_map, 0)))

7187

7186

{

7188

7187

tab->cache.select=(SQL_SELECT*)

7189

thd->memdup((unsigned char*) sel, sizeof(SQL_SELECT));

7188

session->memdup((unsigned char*) sel, sizeof(SQL_SELECT));

7190

7189

tab->cache.select->cond=tmp;

7191

7190

tab->cache.select->read_tables=join->const_table_map;

7192

7191

}

7193

7192

}

7194

7193

}

7195

7194

}

7196

7197

7195

7196

7198

7197

Push down conditions from all on expressions.

7199

7198

Each of these conditions are guarded by a variable

7200

7199

that turns if off just before null complemented row for

7201

7200

outer joins is formed. Thus, the condition from an

7202

7201

'on expression' are guaranteed not to be checked for

7203

7202

the null complemented row.

7204

7203

7205

7204

7206

7205

/* First push down constant conditions from on expressions */

7207

7206

for (JOIN_TAB *join_tab= join->join_tab+join->const_tables;

7210

7209

if (*join_tab->on_expr_ref)

7211

7210

{

7212

7211

JOIN_TAB *cond_tab= join_tab->first_inner;

7213

COND *tmp= make_cond_for_table(*join_tab->on_expr_ref,

7214

join->const_table_map,

7215

(table_map) 0, 0);

7212

tmp= make_cond_for_table(*join_tab->on_expr_ref,

7213

join->const_table_map,

7214

(table_map) 0, 0);

7216

7215

if (!tmp)

7217

7216

continue;

7218

7217

tmp= new Item_func_trig_cond(tmp, &cond_tab->not_null_compl);

7224

7223

if (!cond_tab->select_cond)

7225

7224

return(1);

7226

7225

cond_tab->select_cond->quick_fix_field();

7227

}

7226

}

7228

7227

}

7229

7228

7230

7229

/* Push down non-constant conditions from on expressions */

7231

7230

JOIN_TAB *last_tab= tab;

7232

7231

while (first_inner_tab && first_inner_tab->last_inner == last_tab)

7233

{

7234

7232

{

7233

7235

7234

Table tab is the last inner table of an outer join.

7236

7235

An on expression is always attached to it.

7237

7236

7238

7237

COND *on_expr= *first_inner_tab->on_expr_ref;

7239

7238

7240

7239

table_map used_tables2= (join->const_table_map |

7256

7255

tmp_cond,

7257

7256

first_inner_tab)))

7258

7257

return(1);

7259

7260

Now add the guard turning the predicate off for

7258

7259

Now add the guard turning the predicate off for

7261

7260

the null complemented row.

7262

7261

7263

7262

tmp_cond= new Item_func_trig_cond(tmp_cond,

7264

7263

&first_inner_tab->

7265

7264

not_null_compl);

7272

7271

if (!cond_tab->select_cond)

7273

7272

return(1);

7274

7273

cond_tab->select_cond->quick_fix_field();

7275

}

7274

}

7276

7275

}

7277

first_inner_tab= first_inner_tab->first_upper;

7276

first_inner_tab= first_inner_tab->first_upper;

7278

7277

}

7279

7278

}

7280

7279

}

7282

7281

}

7283

7282

7284

7283

7285

7284

7286

7285

Check if given expression uses only table fields covered by the given index

7287

7286

7288

7287

SYNOPSIS

7295

7294

DESCRIPTION

7296

7295

Check if given expression only uses fields covered by index #keyno in the

7297

7296

table tbl. The expression can use any fields in any other tables.

7298

7299

The expression is guaranteed not to be AND or OR - those constructs are

7297

7298

The expression is guaranteed not to be AND or OR - those constructs are

7300

7299

handled outside of this function.

7301

7300

7302

7301

RETURN

7304

7303

false No

7305

7304

7306

7305

7307

bool uses_index_fields_only(Item *item, Table *tbl, uint32_t keyno,

7306

bool uses_index_fields_only(Item *item, Table *tbl, uint32_t keyno,

7308

7307

bool other_tbls_ok)

7309

7308

{

7310

7309

if (item->const_item())

7311

7310

return true;

7312

7311

7313

7314

Don't push down the triggered conditions. Nested outer joins execution

7312

7313

Don't push down the triggered conditions. Nested outer joins execution

7315

7314

code may need to evaluate a condition several times (both triggered and

7316

7315

untriggered), and there is no way to put thi

7317

7316

TODO: Consider cloning the triggered condition and using the copies for:

7318

7317

1. push the first copy down, to have most restrictive index condition

7319

7318

possible

7320

2. Put the second copy into tab->select_cond.

7319

2. Put the second copy into tab->select_cond.

7321

7320

7322

if (item->type() == Item::FUNC_ITEM &&

7321

if (item->type() == Item::FUNC_ITEM &&

7323

7322

((Item_func*)item)->functype() == Item_func::TRIG_COND_FUNC)

7324

7323

return false;

7325

7324

7345

7344

{

7346

7345

/* This is a function, apply condition recursively to arguments */

7347

7346

List_iterator<Item> li(*((Item_cond*)item)->argument_list());

7348

Item *item;

7349

while ((item=li++))

7347

Item *list_item;

7348

while ((list_item=li++))

7350

7349

{

7351

7350

if (!uses_index_fields_only(item, tbl, keyno, other_tbls_ok))

7352

7351

return false;

7356

7355

case Item::FIELD_ITEM:

7357

7356

{

7358

7357

Item_field *item_field= (Item_field*)item;

7359

if (item_field->field->table != tbl)

7358

if (item_field->field->table != tbl)

7360

7359

return true;

7361

return item_field->field->part_of_key.is_set(keyno);

7360

return item_field->field->part_of_key.test(keyno);

7362

7361

}

7363

7362

case Item::REF_ITEM:

7364

7363

return uses_index_fields_only(item->real_item(), tbl, keyno,

7383

7382

other_tbls_ok true <=> Fields of other non-const tables are allowed

7384

7383

7385

7384

DESCRIPTION

7386

Get a part of the condition that can be checked when for the given table

7385

Get a part of the condition that can be checked when for the given table

7387

7386

we have values only of fields covered by some index. The condition may

7388

refer to other tables, it is assumed that we have values of all of their

7387

refer to other tables, it is assumed that we have values of all of their

7389

7388

fields.

7390

7389

7391

7390

Example:

7392

7391

make_cond_for_index(

7393

7392

"cond(t1.field) AND cond(t2.key1) AND cond(t2.non_key) AND cond(t2.key2)",

7394

t2, keyno(t2.key1))

7393

t2, keyno(t2.key1))

7395

7394

will return

7396

7395

"cond(t1.field) AND cond(t2.key2)"

7397

7396

7542

7541

{

7543

7542

Item *idx_cond;

7544

7543

if (tab->table->file->index_flags(keyno, 0, 1) & HA_DO_INDEX_COND_PUSHDOWN &&

7545

tab->join->thd->variables.engine_condition_pushdown)

7544

tab->join->session->variables.engine_condition_pushdown)

7546

7545

{

7547

7546

idx_cond= make_cond_for_index(tab->select_cond, tab->table, keyno,

7548

7547

other_tbls_ok);

7550

7549

if (idx_cond)

7551

7550

{

7552

7551

tab->pre_idx_push_select_cond= tab->select_cond;

7553

Item *idx_remainder_cond=

7552

Item *idx_remainder_cond=

7554

7553

tab->table->file->idx_cond_push(keyno, idx_cond);

7555

7554

7556

7555

7557

7556

Disable eq_ref's "lookup cache" if we've pushed down an index

7558

condition.

7557

condition.

7559

7558

TODO: This check happens to work on current ICP implementations, but

7560

there may exist a compliant implementation that will not work

7559

there may exist a compliant implementation that will not work

7561

7560

correctly with it. Sort this out when we stabilize the condition

7562

7561

pushdown APIs.

7563

7562

7574

7573

{

7575

7574

tab->select_cond= new Item_cond_and(row_cond, idx_remainder_cond);

7576

7575

tab->select_cond->quick_fix_field();

7577

((Item_cond_and*)tab->select_cond)->used_tables_cache=

7576

((Item_cond_and*)tab->select_cond)->used_tables_cache=

7578

7577

row_cond->used_tables() | idx_remainder_cond->used_tables();

7579

7578

}

7580

7579

}

7592

7591

7593

7592

7594

7593

7595

Determine if the set is already ordered for order_st BY, so it can

7594

Determine if the set is already ordered for order_st BY, so it can

7596

7595

disable join cache because it will change the ordering of the results.

7597

Code handles sort table that is at any location (not only first after

7596

Code handles sort table that is at any location (not only first after

7598

7597

the const tables) despite the fact that it's currently prohibited.

7599

7598

We must disable join cache if the first non-const table alone is

7600

7599

ordered. If there is a temp table the ordering is done as a last

7610

7609

{

7611

7610

JOIN_TAB *tab=join->join_tab+i;

7612

7611

Table *table=tab->table;

7613

if ((table == join->sort_by_table &&

7612

if ((table == join->sort_by_table &&

7614

7613

(!join->order || join->skip_sort_order)) ||

7615

7614

(join->sort_by_table == (Table *) 1 && i != join->const_tables))

7616

7615

{

7627

7626

SYNOPSIS

7628

7627

make_join_readinfo()

7629

7628

join Join being processed

7630

options Join's options (checking for SELECT_DESCRIBE,

7629

options Join's options (checking for SELECT_DESCRIBE,

7631

7630

SELECT_NO_JOIN_CACHE)

7632

7631

no_jbuf_after Don't use join buffering after table with this number.

7633

7632

7638

7637

- increment counters

7639

7638

- etc

7640

7639

7641

RETURN

7640

RETURN

7642

7641

false - OK

7643

7642

true - Out of memory

7644

7643

7658

7657

tab->read_record.table= table;

7659

7658

tab->read_record.file=table->file;

7660

7659

tab->next_select=sub_select; /* normal select */

7661

7662

TODO: don't always instruct first table's ref/range access method to

7660

7661

TODO: don't always instruct first table's ref/range access method to

7663

7662

produce sorted output.

7664

7663

7665

7664

tab->sorted= sorted;

7666

7665

sorted= 0; // only first must be sorted

7667

7666

if (tab->insideout_match_tab)

7668

7667

{

7669

if (!(tab->insideout_buf= (unsigned char*)join->thd->alloc(tab->table->key_info

7668

if (!(tab->insideout_buf= (unsigned char*)join->session->alloc(tab->table->key_info

7670

7669

[tab->index].

7671

7670

key_length)))

7672

7671

return true;

7681

7680

table->status=STATUS_NO_RECORD;

7682

7681

tab->read_first_record= join_read_const;

7683

7682

tab->read_record.read_record= join_no_more_records;

7684

if (table->covering_keys.is_set(tab->ref.key) &&

7683

if (table->covering_keys.test(tab->ref.key) &&

7685

7684

!table->no_keyread)

7686

7685

{

7687

7686

table->key_read=1;

7699

7698

tab->quick=0;

7700

7699

tab->read_first_record= join_read_key;

7701

7700

tab->read_record.read_record= join_no_more_records;

7702

if (table->covering_keys.is_set(tab->ref.key) &&

7701

if (table->covering_keys.test(tab->ref.key) &&

7703

7702

!table->no_keyread)

7704

7703

{

7705

7704

table->key_read=1;

7718

7717

}

7719

7718

delete tab->quick;

7720

7719

tab->quick=0;

7721

if (table->covering_keys.is_set(tab->ref.key) &&

7720

if (table->covering_keys.test(tab->ref.key) &&

7722

7721

!table->no_keyread)

7723

7722

{

7724

7723

table->key_read=1;

7729

7728

if (tab->type == JT_REF)

7730

7729

{

7731

7730

tab->read_first_record= join_read_always_key;

7732

tab->read_record.read_record= tab->insideout_match_tab?

7731

tab->read_record.read_record= tab->insideout_match_tab?

7733

7732

join_read_next_same_diff : join_read_next_same;

7734

7733

}

7735

7734

else

7750

7749

!tab->insideout_match_tab)

7751

7750

{

7752

7751

if ((options & SELECT_DESCRIBE) ||

7753

!join_init_cache(join->thd,join->join_tab+join->const_tables,

7752

!join_init_cache(join->session,join->join_tab+join->const_tables,

7754

7753

i-join->const_tables))

7755

7754

{

7756

7755

using_join_cache= true;

7760

7759

/* These init changes read_record */

7761

7760

if (tab->use_quick == 2)

7762

7761

{

7763

join->thd->server_status|=SERVER_QUERY_NO_GOOD_INDEX_USED;

7762

join->session->server_status|=SERVER_QUERY_NO_GOOD_INDEX_USED;

7764

7763

tab->read_first_record= join_init_quick_read_record;

7765

7764

if (statistics)

7766

status_var_increment(join->thd->status_var.select_range_check_count);

7765

status_var_increment(join->session->status_var.select_range_check_count);

7767

7766

}

7768

7767

else

7769

7768

{

7773

7772

if (tab->select && tab->select->quick)

7774

7773

{

7775

7774

if (statistics)

7776

status_var_increment(join->thd->status_var.select_range_count);

7775

status_var_increment(join->session->status_var.select_range_count);

7777

7776

}

7778

7777

else

7779

7778

{

7780

join->thd->server_status|=SERVER_QUERY_NO_INDEX_USED;

7779

join->session->server_status|=SERVER_QUERY_NO_INDEX_USED;

7781

7780

if (statistics)

7782

status_var_increment(join->thd->status_var.select_scan_count);

7781

status_var_increment(join->session->status_var.select_scan_count);

7783

7782

}

7784

7783

}

7785

7784

else

7787

7786

if (tab->select && tab->select->quick)

7788

7787

{

7789

7788

if (statistics)

7790

status_var_increment(join->thd->status_var.select_full_range_join_count);

7789

status_var_increment(join->session->status_var.select_full_range_join_count);

7791

7790

}

7792

7791

else

7793

7792

{

7794

join->thd->server_status|=SERVER_QUERY_NO_INDEX_USED;

7793

join->session->server_status|=SERVER_QUERY_NO_INDEX_USED;

7795

7794

if (statistics)

7796

status_var_increment(join->thd->status_var.select_full_join_count);

7795

status_var_increment(join->session->status_var.select_full_join_count);

7797

7796

}

7798

7797

}

7799

7798

if (!table->no_keyread)

7800

7799

{

7801

7800

if (tab->select && tab->select->quick &&

7802

7801

tab->select->quick->index != MAX_KEY && //not index_merge

7803

table->covering_keys.is_set(tab->select->quick->index))

7802

table->covering_keys.test(tab->select->quick->index))

7804

7803

{

7805

7804

table->key_read=1;

7806

7805

table->file->extra(HA_EXTRA_KEYREAD);

7807

7806

}

7808

else if (!table->covering_keys.is_clear_all() &&

7807

else if (!table->covering_keys.none() &&

7809

7808

!(tab->select && tab->select->quick))

7810

7809

{ // Only read index tree

7811

7810

if (!tab->insideout_match_tab)

7949

7948

7950

7949

void JOIN::join_free()

7951

7950

{

7952

SELECT_LEX_UNIT *tmp_unit;

7953

SELECT_LEX *sl;

7951

Select_Lex_Unit *tmp_unit;

7952

Select_Lex *sl;

7954

7953

7955

7954

Optimization: if not EXPLAIN and we are done with the JOIN,

7956

7955

free all tables.

7957

7956

7958

bool full= (!select_lex->uncacheable && !thd->lex->describe);

7957

bool full= (!select_lex->uncacheable && !session->lex->describe);

7959

7958

bool can_unlock= full;

7960

7959

7961

7960

cleanup(full);

7985

7984

We are not using tables anymore

7986

7985

Unlock all tables. We may be in an INSERT .... SELECT statement.

7987

7986

7988

if (can_unlock && lock && thd->lock &&

7987

if (can_unlock && lock && session->lock &&

7989

7988

!(select_options & SELECT_NO_UNLOCK) &&

7990

7989

!select_lex->subquery_in_having &&

7991

(select_lex == (thd->lex->unit.fake_select_lex ?

7992

thd->lex->unit.fake_select_lex : &thd->lex->select_lex)))

7990

(select_lex == (session->lex->unit.fake_select_lex ?

7991

session->lex->unit.fake_select_lex : &session->lex->select_lex)))

7993

7992

{

7994

7993

7995

7994

TODO: unlock tables even if the join isn't top level select in the

7996

7995

tree.

7997

7996

7998

mysql_unlock_read_tables(thd, lock); // Don't free join->lock

7997

mysql_unlock_read_tables(session, lock); // Don't free join->lock

7999

7998

lock= 0;

8000

7999

}

8001

8000

8236

8235

8237

8236

8238

8237

static order_st *

8239

remove_const(JOIN *join,order_st *first_order, COND *cond,

8240

bool change_list, bool *simple_order)

8238

remove_constants(JOIN *join,order_st *first_order, COND *cond,

8239

bool change_list, bool *simple_order)

8241

8240

{

8242

8241

if (join->tables == join->const_tables)

8243

8242

return change_list ? 0 : first_order; // No need to sort

8314

8313

if (send_row)

8315

8314

{

8316

8315

for (TableList *table= tables; table; table= table->next_leaf)

8317

mark_as_null_row(table->table); // All fields are NULL

8316

table->table->mark_as_null_row(); // All fields are NULL

8318

8317

if (having && having->val_int() == 0)

8319

8318

send_row=0;

8320

8319

}

8332

8331

result->send_eof(); // Should be safe

8333

8332

}

8334

8333

/* Update results for FOUND_ROWS */

8335

join->thd->limit_found_rows= join->thd->examined_row_count= 0;

8334

join->session->limit_found_rows= join->session->examined_row_count= 0;

8336

8335

return(0);

8337

8336

}

8338

8337

8341

8340

8342

8341

static void clear_tables(JOIN *join)

8343

8342

{

8344

8343

8345

8344

must clear only the non-const tables, as const tables

8346

8345

are not re-calculated.

8347

8346

8348

8347

for (uint32_t i=join->const_tables ; i < join->tables ; i++)

8349

mark_as_null_row(join->table[i]); // All fields are NULL

8348

join->table[i]->mark_as_null_row(); // All fields are NULL

8350

8349

}

8351

8350

8352

8351

/*****************************************************************************

8362

8361

public:

8363

8362

static void *operator new(size_t size)

8364

8363

{

8365

return (void*) sql_alloc((uint) size);

8364

return (void*) sql_alloc((uint32_t) size);

8366

8365

}

8367

static void operator delete(void *ptr __attribute__((unused)),

8368

size_t size __attribute__((unused)))

8366

static void operator delete(void *, size_t)

8369

8367

{ TRASH(ptr, size); }

8370

8368

8371

8369

Item *and_level;

8420

8418

return item;

8421

8419

}

8422

8420

8423

8421

8424

8422

/**

8425

8423

Check whether an equality can be used to build multiple equalities.

8426

8424

8447

8445

the check_equality will be called for the following equality

8448

8446

predicates a=b, b=c, b=2 and f=e.

8449

8447

- For a=b it will be called with *cond_equal=(0,[]) and will transform

8450

*cond_equal into (0,[Item_equal(a,b)]).

8448

*cond_equal into (0,[Item_equal(a,b)]).

8451

8449

- For b=c it will be called with *cond_equal=(0,[Item_equal(a,b)])

8452

8450

and will transform *cond_equal into CE=(0,[Item_equal(a,b,c)]).

8453

8451

- For b=2 it will be called with *cond_equal=(ptr(CE),[])

8460

8458

the Field::eq_def method) are placed to the same multiple equalities.

8461

8459

Because of this some equality predicates are not eliminated and

8462

8460

can be used in the constant propagation procedure.

8463

We could weeken the equlity test as soon as at least one of the

8464

equal fields is to be equal to a constant. It would require a

8461

We could weeken the equlity test as soon as at least one of the

8462

equal fields is to be equal to a constant. It would require a

8465

8463

more complicated implementation: we would have to store, in

8466

8464

general case, its own constant for each fields from the multiple

8467

8465

equality. But at the same time it would allow us to get rid

8479

8477

containing just field1 and field2 is added to the existing

8480

8478

multiple equalities.

8481

8479

If the function processes the predicate of the form field1=const,

8482

it looks for a multiple equality containing field1. If found, the

8480

it looks for a multiple equality containing field1. If found, the

8483

8481

function checks the constant of the multiple equality. If the value

8484

8482

is unknown, it is setup to const. Otherwise the value is compared with

8485

8483

const and the evaluation of the equality predicate is performed.

8506

8504

static bool check_simple_equality(Item *left_item, Item *right_item,

8507

8505

Item *item, COND_EQUAL *cond_equal)

8508

8506

{

8509

if (left_item->type() == Item::REF_ITEM &&

8510

((Item_ref*)left_item)->ref_type() == Item_ref::VIEW_REF)

8511

{

8512

if (((Item_ref*)left_item)->depended_from)

8513

return false;

8514

left_item= left_item->real_item();

8515

}

8516

if (right_item->type() == Item::REF_ITEM &&

8517

((Item_ref*)right_item)->ref_type() == Item_ref::VIEW_REF)

8518

{

8519

if (((Item_ref*)right_item)->depended_from)

8520

return false;

8521

right_item= right_item->real_item();

8522

}

8523

8507

if (left_item->type() == Item::FIELD_ITEM &&

8524

8508

right_item->type() == Item::FIELD_ITEM &&

8525

8509

!((Item_field*)left_item)->depended_from &&

8537

8521

bool left_copyfl, right_copyfl;

8538

8522

Item_equal *left_item_equal=

8539

8523

find_item_equal(cond_equal, left_field, &left_copyfl);

8540

Item_equal *right_item_equal=

8524

Item_equal *right_item_equal=

8541

8525

find_item_equal(cond_equal, right_field, &right_copyfl);

8542

8526

8543

8527

/* As (NULL=NULL) != true we can't just remove the predicate f=f */

8544

8528

if (left_field->eq(right_field)) /* f = f */

8545

return (!(left_field->maybe_null() && !left_item_equal));

8529

return (!(left_field->maybe_null() && !left_item_equal));

8546

8530

8547

8531

if (left_item_equal && left_item_equal == right_item_equal)

8548

8532

{

8549

8533

8550

8534

The equality predicate is inference of one of the existing

8551

8535

multiple equalities, i.e the condition is already covered

8552

8536

by upper level equalities

8553

8537

8554

8538

return true;

8555

8539

}

8556

8557

bool copy_item_name= test(item && item->name >= subq_sj_cond_name &&

8540

8541

bool copy_item_name= test(item && item->name >= subq_sj_cond_name &&

8558

8542

item->name < subq_sj_cond_name + 64);

8559

8543

/* Copy the found multiple equalities at the current level if needed */

8560

8544

if (left_copyfl)

8575

8559

}

8576

8560

8577

8561

if (left_item_equal)

8578

{

8562

{

8579

8563

/* left item was found in the current or one of the upper levels */

8580

8564

if (! right_item_equal)

8581

8565

left_item_equal->add((Item_field *) right_item);

8590

8574

}

8591

8575

}

8592

8576

else

8593

{

8577

{

8594

8578

/* left item was not found neither the current nor in upper levels */

8595

8579

if (right_item_equal)

8596

8580

{

8598

8582

if (copy_item_name)

8599

8583

right_item_equal->name = item->name;

8600

8584

}

8601

else

8585

else

8602

8586

{

8603

8587

/* None of the fields was found in multiple equalities */

8604

8588

Item_equal *item_equal= new Item_equal((Item_field *) left_item,

8646

8630

eq_item->set_cmp_func();

8647

8631

eq_item->quick_fix_field();

8648

8632

item= eq_item;

8649

}

8633

}

8650

8634

if ((cs != ((Item_func *) item)->compare_collation()) ||

8651

8635

!cs->coll->propagate(cs, 0, 0))

8652

8636

return false;

8661

8645

}

8662

8646

if (item_equal)

8663

8647

{

8664

8648

8665

8649

The flag cond_false will be set to 1 after this, if item_equal

8666

8650

already contains a constant and its value is not equal to

8667

8651

the value of const_item.

8692

8676

simple equality nor a row equality the item for this predicate is added

8693

8677

to eq_list.

8694

8678

8695

@param thd thread handle

8679

@param session thread handle

8696

8680

@param left_row left term of the row equality to be processed

8697

8681

@param right_row right term of the row equality to be processed

8698

8682

@param cond_equal multiple equalities that must hold together with the

8705

8689

@retval

8706

8690

false otherwise

8707

8691

8708

8709

static bool check_row_equality(THD *thd, Item *left_row, Item_row *right_row,

8692

8693

static bool check_row_equality(Session *session, Item *left_row, Item_row *right_row,

8710

8694

COND_EQUAL *cond_equal, List<Item>* eq_list)

8711

{

8695

{

8712

8696

uint32_t n= left_row->cols();

8713

8697

for (uint32_t i= 0 ; i < n; i++)

8714

8698

{

8718

8702

if (left_item->type() == Item::ROW_ITEM &&

8719

8703

right_item->type() == Item::ROW_ITEM)

8720

8704

{

8721

is_converted= check_row_equality(thd,

8705

is_converted= check_row_equality(session,

8722

8706

(Item_row *) left_item,

8723

8707

(Item_row *) right_item,

8724

8708

cond_equal, eq_list);

8725

8709

if (!is_converted)

8726

thd->lex->current_select->cond_count++;

8710

session->lex->current_select->cond_count++;

8727

8711

}

8728

8712

else

8729

{

8713

{

8730

8714

is_converted= check_simple_equality(left_item, right_item, 0, cond_equal);

8731

thd->lex->current_select->cond_count++;

8732

}

8733

8715

session->lex->current_select->cond_count++;

8716

}

8717

8734

8718

if (!is_converted)

8735

8719

{

8736

8720

Item_func_eq *eq_item;

8759

8743

equalities which are treated in the same way as original equality

8760

8744

predicates.

8761

8745

8762

@param thd thread handle

8746

@param session thread handle

8763

8747

@param item predicate to process

8764

8748

@param cond_equal multiple equalities that must hold together with the

8765

8749

predicate

8775

8759

or, if the procedure fails by a fatal error.

8776

8760

8777

8761

8778

static bool check_equality(THD *thd, Item *item, COND_EQUAL *cond_equal,

8762

static bool check_equality(Session *session, Item *item, COND_EQUAL *cond_equal,

8779

8763

List<Item> *eq_list)

8780

8764

{

8781

8765

if (item->type() == Item::FUNC_ITEM &&

8787

8771

if (left_item->type() == Item::ROW_ITEM &&

8788

8772

right_item->type() == Item::ROW_ITEM)

8789

8773

{

8790

thd->lex->current_select->cond_count--;

8791

return check_row_equality(thd,

8774

session->lex->current_select->cond_count--;

8775

return check_row_equality(session,

8792

8776

(Item_row *) left_item,

8793

8777

(Item_row *) right_item,

8794

8778

cond_equal, eq_list);

8795

8779

}

8796

else

8780

else

8797

8781

return check_simple_equality(left_item, right_item, item, cond_equal);

8798

}

8782

}

8799

8783

return false;

8800

8784

}

8801

8785

8802

8786

8803

8787

/**

8804

8788

Replace all equality predicates in a condition by multiple equality items.

8805

8789

8806

8790

At each 'and' level the function detects items for equality predicates

8807

8791

and replaced them by a set of multiple equality items of class Item_equal,

8808

taking into account inherited equalities from upper levels.

8792

taking into account inherited equalities from upper levels.

8809

8793

If an equality predicate is used not in a conjunction it's just

8810

8794

replaced by a multiple equality predicate.

8811

8795

For each 'and' level the function set a pointer to the inherited

8812

8796

multiple equalities in the cond_equal field of the associated

8813

object of the type Item_cond_and.

8797

object of the type Item_cond_and.

8814

8798

The function also traverses the cond tree and and for each field reference

8815

8799

sets a pointer to the multiple equality item containing the field, if there

8816

8800

is any. If this multiple equality equates fields to a constant the

8817

function replaces the field reference by the constant in the cases

8801

function replaces the field reference by the constant in the cases

8818

8802

when the field is not of a string type or when the field reference is

8819

8803

just an argument of a comparison predicate.

8820

The function also determines the maximum number of members in

8804

The function also determines the maximum number of members in

8821

8805

equality lists of each Item_cond_and object assigning it to

8822

thd->lex->current_select->max_equal_elems.

8806

session->lex->current_select->max_equal_elems.

8823

8807

8824

8808

@note

8825

8809

Multiple equality predicate =(f1,..fn) is equivalent to the conjuction of

8831

8815

in a conjuction for a minimal set of multiple equality predicates.

8832

8816

This set can be considered as a canonical representation of the

8833

8817

sub-conjunction of the equality predicates.

8834

E.g. (t1.a=t2.b AND t2.b>5 AND t1.a=t3.c) is replaced by

8818

E.g. (t1.a=t2.b AND t2.b>5 AND t1.a=t3.c) is replaced by

8835

8819

(=(t1.a,t2.b,t3.c) AND t2.b>5), not by

8836

8820

(=(t1.a,t2.b) AND =(t1.a,t3.c) AND t2.b>5);

8837

8821

while (t1.a=t2.b AND t2.b>5 AND t3.c=t4.d) is replaced by

8842

8826

The function performs the substitution in a recursive descent by

8843

8827

the condtion tree, passing to the next AND level a chain of multiple

8844

8828

equality predicates which have been built at the upper levels.

8845

The Item_equal items built at the level are attached to other

8829

The Item_equal items built at the level are attached to other

8846

8830

non-equality conjucts as a sublist. The pointer to the inherited

8847

8831

multiple equalities is saved in the and condition object (Item_cond_and).

8848

This chain allows us for any field reference occurence easyly to find a

8832

This chain allows us for any field reference occurence easyly to find a

8849

8833

multiple equality that must be held for this occurence.

8850

8834

For each AND level we do the following:

8851

8835

- scan it for all equality predicate (=) items

8852

8836

- join them into disjoint Item_equal() groups

8853

- process the included OR conditions recursively to do the same for

8854

lower AND levels.

8837

- process the included OR conditions recursively to do the same for

8838

lower AND levels.

8855

8839

8856

8840

We need to do things in this order as lower AND levels need to know about

8857

8841

all possible Item_equal objects in upper levels.

8858

8842

8859

@param thd thread handle

8843

@param session thread handle

8860

8844

@param cond condition(expression) where to make replacement

8861

8845

@param inherited path to all inherited multiple equality items

8862

8846

8864

8848

pointer to the transformed condition

8865

8849

8866

8850

8867

static COND *build_equal_items_for_cond(THD *thd, COND *cond,

8851

static COND *build_equal_items_for_cond(Session *session, COND *cond,

8868

8852

COND_EQUAL *inherited)

8869

8853

{

8870

8854

Item_equal *item_equal;

8877

8861

bool and_level= ((Item_cond*) cond)->functype() ==

8878

8862

Item_func::COND_AND_FUNC;

8879

8863

List<Item> *args= ((Item_cond*) cond)->argument_list();

8880

8864

8881

8865

List_iterator<Item> li(*args);

8882

8866

Item *item;

8883

8867

8886

8870

8887

8871

Retrieve all conjucts of this level detecting the equality

8888

8872

that are subject to substitution by multiple equality items and

8889

removing each such predicate from the conjunction after having

8873

removing each such predicate from the conjunction after having

8890

8874

found/created a multiple equality whose inference the predicate is.

8891

8875

8892

8876

while ((item= li++))

8893

8877

{

8894

8878

8896

8880

structure here because it's restored before each

8897

8881

re-execution of any prepared statement/stored procedure.

8898

8882

8899

if (check_equality(thd, item, &cond_equal, &eq_list))

8883

if (check_equality(session, item, &cond_equal, &eq_list))

8900

8884

li.remove();

8901

8885

}

8902

8886

8905

8889

{

8906

8890

item_equal->fix_length_and_dec();

8907

8891

item_equal->update_used_tables();

8908

set_if_bigger(thd->lex->current_select->max_equal_elems,

8909

item_equal->members());

8892

set_if_bigger(session->lex->current_select->max_equal_elems,

8893

item_equal->members());

8910

8894

}

8911

8895

8912

8896

((Item_cond_and*)cond)->cond_equal= cond_equal;

8918

8902

8919

8903

li.rewind();

8920

8904

while ((item= li++))

8921

{

8905

{

8922

8906

Item *new_item;

8923

if ((new_item= build_equal_items_for_cond(thd, item, inherited)) != item)

8907

if ((new_item= build_equal_items_for_cond(session, item, inherited)) != item)

8924

8908

{

8925

8909

/* This replacement happens only for standalone equalities */

8926

8910

8948

8932

(b=5) and (a=c) are standalone equalities.

8949

8933

In general we can't leave alone standalone eqalities:

8950

8934

for WHERE a=b AND c=d AND (b=c OR d=5)

8951

b=c is replaced by =(a,b,c,d).

8935

b=c is replaced by =(a,b,c,d).

8952

8936

8953

if (check_equality(thd, cond, &cond_equal, &eq_list))

8937

if (check_equality(session, cond, &cond_equal, &eq_list))

8954

8938

{

8955

8939

int n= cond_equal.current_level.elements + eq_list.elements;

8956

8940

if (n == 0)

8964

8948

}

8965

8949

else

8966

8950

item_equal= (Item_equal *) eq_list.pop();

8967

set_if_bigger(thd->lex->current_select->max_equal_elems,

8968

item_equal->members());

8951

set_if_bigger(session->lex->current_select->max_equal_elems,

8952

item_equal->members());

8969

8953

return item_equal;

8970

8954

}

8971

8955

else

8972

8956

{

8973

8957

8974

8958

Here a new AND level must be created. It can happen only

8975

8959

when a row equality is processed as a standalone predicate.

8976

8960

8982

8966

{

8983

8967

item_equal->fix_length_and_dec();

8984

8968

item_equal->update_used_tables();

8985

set_if_bigger(thd->lex->current_select->max_equal_elems,

8986

item_equal->members());

8969

set_if_bigger(session->lex->current_select->max_equal_elems,

8970

item_equal->members());

8987

8971

}

8988

8972

and_cond->cond_equal= cond_equal;

8989

8973

args->concat((List<Item> *)&cond_equal.current_level);

8990

8974

8991

8975

return and_cond;

8992

8976

}

8993

8977

}

8994

8978

8995

8979

For each field reference in cond, not from equal item predicates,

8996

8980

set a pointer to the multiple equality it belongs to (if there is any)

8997

8981

as soon the field is not of a string type or the field reference is

8998

an argument of a comparison predicate.

8999

8982

an argument of a comparison predicate.

8983

9000

8984

unsigned char *is_subst_valid= (unsigned char *) 1;

9001

8985

cond= cond->compile(&Item::subst_argument_checker,

9002

&is_subst_valid,

8986

&is_subst_valid,

9003

8987

&Item::equal_fields_propagator,

9004

8988

(unsigned char *) inherited);

9005

8989

cond->update_used_tables();

9053

9037

SELECT * FROM (t1,t2) LEFT JOIN (t3,t4) ON t2.a=t4.a AND t3.a=t4.a

9054

9038

WHERE t1.a=t2.a

9055

9039

@endcode

9056

that is equivalent to:

9040

that is equivalent to:

9057

9041

@code

9058

9042

SELECT * FROM (t2 LEFT JOIN (t3,t4)ON t2.a=t4.a AND t3.a=t4.a), t1

9059

9043

WHERE t1.a=t2.a

9060

9044

@endcode

9061

9045

Thus, applying equalities from the where condition we basically

9062

9046

can get more freedom in performing join operations.

9063

Althogh we don't use this property now, it probably makes sense to use

9064

it in the future.

9065

@param thd Thread handler

9047

Althogh we don't use this property now, it probably makes sense to use

9048

it in the future.

9049

@param session Thread handler

9066

9050

@param cond condition to build the multiple equalities for

9067

9051

@param inherited path to all inherited multiple equality items

9068

9052

@param join_list list of join tables to which the condition

9073

9057

@return

9074

9058

pointer to the transformed condition containing multiple equalities

9075

9059

9076

9077

static COND *build_equal_items(THD *thd, COND *cond,

9060

9061

static COND *build_equal_items(Session *session, COND *cond,

9078

9062

COND_EQUAL *inherited,

9079

9063

List<TableList> *join_list,

9080

9064

COND_EQUAL **cond_equal_ref)

9081

9065

{

9082

9066

COND_EQUAL *cond_equal= 0;

9083

9067

9084

if (cond)

9068

if (cond)

9085

9069

{

9086

cond= build_equal_items_for_cond(thd, cond, inherited);

9070

cond= build_equal_items_for_cond(session, cond, inherited);

9087

9071

cond->update_used_tables();

9088

9072

if (cond->type() == Item::COND_ITEM &&

9089

9073

((Item_cond*) cond)->functype() == Item_func::COND_AND_FUNC)

9117

9101

We can modify table->on_expr because its old value will

9118

9102

be restored before re-execution of PS/SP.

9119

9103

9120

table->on_expr= build_equal_items(thd, table->on_expr, inherited,

9104

table->on_expr= build_equal_items(session, table->on_expr, inherited,

9121

9105

nested_join_list,

9122

9106

&table->cond_equal);

9123

9107

}

9125

9109

}

9126

9110

9127

9111

return cond;

9128

}

9112

}

9129

9113

9130

9114

9131

9115

/**

9132

9116

Compare field items by table order in the execution plan.

9133

9117

9134

9118

field1 considered as better than field2 if the table containing

9135

field1 is accessed earlier than the table containing field2.

9119

field1 is accessed earlier than the table containing field2.

9136

9120

The function finds out what of two fields is better according

9137

9121

this criteria.

9138

9122

9155

9139

int cmp= 0;

9156

9140

bool outer_ref= 0;

9157

9141

if (field2->used_tables() & OUTER_REF_TABLE_BIT)

9158

{

9142

{

9159

9143

outer_ref= 1;

9160

9144

cmp= -1;

9161

9145

}

9218

9202

List<Item> eq_list;

9219

9203

Item_func_eq *eq_item= 0;

9220

9204

if (((Item *) item_equal)->const_item() && !item_equal->val_int())

9221

return new Item_int((int64_t) 0,1);

9205

return new Item_int((int64_t) 0,1);

9222

9206

Item *item_const= item_equal->get_const();

9223

9207

Item_equal_iterator it(*item_equal);

9224

9208

Item *head;

9235

9219

Item_equal *upper= item_field->find_item_equal(upper_levels);

9236

9220

Item_field *item= item_field;

9237

9221

if (upper)

9238

{

9222

{

9239

9223

if (item_const && upper->get_const())

9240

9224

item= 0;

9241

9225

else

9279

9263

9280

9264

cond->quick_fix_field();

9281

9265

cond->update_used_tables();

9282

9266

9283

9267

return cond;

9284

9268

}

9285

9269

9292

9276

multiple equality predicate it sorts the field references in it

9293

9277

according to the order of tables specified by the table_join_idx

9294

9278

parameter. Then it eliminates the multiple equality predicate it

9295

replacing it by the conjunction of simple equality predicates

9279

replacing it by the conjunction of simple equality predicates

9296

9280

equating every field from the multiple equality to the first

9297

9281

field in it, or to the constant, if there is any.

9298

9282

After this the function retrieves all other conjuncted

9329

9313

cond_equal= &((Item_cond_and *) cond)->cond_equal;

9330

9314

cond_list->disjoin((List<Item> *) &cond_equal->current_level);

9331

9315

9332

List_iterator_fast<Item_equal> it(cond_equal->current_level);

9316

List_iterator_fast<Item_equal> it(cond_equal->current_level);

9333

9317

while ((item_equal= it++))

9334

9318

{

9335

9319

item_equal->sort(&compare_fields_by_table_order, table_join_idx);

9336

9320

}

9337

9321

}

9338

9322

9339

9323

List_iterator<Item> li(*cond_list);

9340

9324

Item *item;

9341

9325

while ((item= li++))

9368

9352

cond= new Item_int((int32_t)cond->val_bool());

9369

9353

9370

9354

}

9371

else if (cond->type() == Item::FUNC_ITEM &&

9355

else if (cond->type() == Item::FUNC_ITEM &&

9372

9356

((Item_cond*) cond)->functype() == Item_func::MULT_EQUAL_FUNC)

9373

9357

{

9374

9358

item_equal= (Item_equal *) cond;

9403

9387

9404

9388

if (cond->type() == Item::COND_ITEM)

9405

9389

{

9406

List<Item> *cond_list= ((Item_cond*) cond)->argument_list();

9390

List<Item> *cond_list= ((Item_cond*) cond)->argument_list();

9407

9391

List_iterator_fast<Item> li(*cond_list);

9408

9392

Item *item;

9409

9393

while ((item= li++))

9410

9394

update_const_equal_items(item, tab);

9411

9395

}

9412

else if (cond->type() == Item::FUNC_ITEM &&

9396

else if (cond->type() == Item::FUNC_ITEM &&

9413

9397

((Item_cond*) cond)->functype() == Item_func::MULT_EQUAL_FUNC)

9414

9398

{

9415

9399

Item_equal *item_equal= (Item_equal *) cond;

9425

9409

Field *field= item_field->field;

9426

9410

JOIN_TAB *stat= field->table->reginfo.join_tab;

9427

9411

key_map possible_keys= field->key_start;

9428

possible_keys.intersect(field->table->keys_in_use_for_query);

9429

stat[0].const_keys.merge(possible_keys);

9412

possible_keys&= field->table->keys_in_use_for_query;

9413

stat[0].const_keys|= possible_keys;

9430

9414

9431

9415

9432

For each field in the multiple equality (for which we know that it

9433

is a constant) we have to find its corresponding key part, and set

9416

For each field in the multiple equality (for which we know that it

9417

is a constant) we have to find its corresponding key part, and set

9434

9418

that key part in const_key_parts.

9435

9436

if (!possible_keys.is_clear_all())

9419

9420

if (possible_keys.any())

9437

9421

{

9438

Table *tab= field->table;

9422

Table *field_tab= field->table;

9439

9423

KEYUSE *use;

9440

for (use= stat->keyuse; use && use->table == tab; use++)

9441

if (possible_keys.is_set(use->key) &&

9442

tab->key_info[use->key].key_part[use->keypart].field ==

9424

for (use= stat->keyuse; use && use->table == field_tab; use++)

9425

if (possible_keys.test(use->key) &&

9426

field_tab->key_info[use->key].key_part[use->keypart].field ==

9443

9427

field)

9444

tab->const_key_parts[use->key]|= use->keypart_map;

9428

field_tab->const_key_parts[use->key]|= use->keypart_map;

9445

9429

}

9446

9430

}

9447

9431

}

9455

9439

9456

9440

9457

9441

static void

9458

change_cond_ref_to_const(THD *thd, I_List<COND_CMP> *save_list,

9442

change_cond_ref_to_const(Session *session, I_List<COND_CMP> *save_list,

9459

9443

Item *and_father, Item *cond,

9460

9444

Item *field, Item *value)

9461

9445

{

9466

9450

List_iterator<Item> li(*((Item_cond*) cond)->argument_list());

9467

9451

Item *item;

9468

9452

while ((item=li++))

9469

change_cond_ref_to_const(thd, save_list,and_level ? cond : item, item,

9453

change_cond_ref_to_const(session, save_list,and_level ? cond : item, item,

9470

9454

field, value);

9471

9455

return;

9472

9456

}

9487

9471

{

9488

9472

Item *tmp=value->clone_item();

9489

9473

tmp->collation.set(right_item->collation);

9490

9474

9491

9475

if (tmp)

9492

9476

{

9493

thd->change_item_tree(args + 1, tmp);

9477

session->change_item_tree(args + 1, tmp);

9494

9478

func->update_used_tables();

9495

9479

if ((functype == Item_func::EQ_FUNC || functype == Item_func::EQUAL_FUNC)

9496

9480

&& and_father != cond && !left_item->const_item())

9511

9495

{

9512

9496

Item *tmp= value->clone_item();

9513

9497

tmp->collation.set(left_item->collation);

9514

9498

9515

9499

if (tmp)

9516

9500

{

9517

thd->change_item_tree(args, tmp);

9501

session->change_item_tree(args, tmp);

9518

9502

value= tmp;

9519

9503

func->update_used_tables();

9520

9504

if ((functype == Item_func::EQ_FUNC || functype == Item_func::EQUAL_FUNC)

9521

9505

&& and_father != cond && !right_item->const_item())

9522

9506

{

9523

9507

args[0]= args[1]; // For easy check

9524

thd->change_item_tree(args + 1, value);

9508

session->change_item_tree(args + 1, value);

9525

9509

cond->marker=1;

9526

9510

COND_CMP *tmp2;

9527

9511

if ((tmp2=new COND_CMP(and_father,func)))

9565

9549

}

9566

9550

9567

9551

static void

9568

propagate_cond_constants(THD *thd, I_List<COND_CMP> *save_list,

9552

propagate_cond_constants(Session *session, I_List<COND_CMP> *save_list,

9569

9553

COND *and_father, COND *cond)

9570

9554

{

9571

9555

if (cond->type() == Item::COND_ITEM)

9577

9561

I_List<COND_CMP> save;

9578

9562

while ((item=li++))

9579

9563

{

9580

propagate_cond_constants(thd, &save,and_level ? cond : item, item);

9564

propagate_cond_constants(session, &save,and_level ? cond : item, item);

9581

9565

}

9582

9566

if (and_level)

9583

9567

{ // Handle other found items

9587

9571

{

9588

9572

Item **args= cond_cmp->cmp_func->arguments();

9589

9573

if (!args[0]->const_item())

9590

change_cond_ref_to_const(thd, &save,cond_cmp->and_level,

9574

change_cond_ref_to_const(session, &save,cond_cmp->and_level,

9591

9575

cond_cmp->and_level, args[0], args[1]);

9592

9576

}

9593

9577

}

9607

9591

{

9608

9592

if (right_const)

9609

9593

{

9610

resolve_const_item(thd, &args[1], args[0]);

9594

resolve_const_item(session, &args[1], args[0]);

9611

9595

func->update_used_tables();

9612

change_cond_ref_to_const(thd, save_list, and_father, and_father,

9596

change_cond_ref_to_const(session, save_list, and_father, and_father,

9613

9597

args[0], args[1]);

9614

9598

}

9615

9599

else if (left_const)

9616

9600

{

9617

resolve_const_item(thd, &args[0], args[1]);

9601

resolve_const_item(session, &args[0], args[1]);

9618

9602

func->update_used_tables();

9619

change_cond_ref_to_const(thd, save_list, and_father, and_father,

9603

change_cond_ref_to_const(session, save_list, and_father, and_father,

9620

9604

args[1], args[0]);

9621

9605

}

9622

9606

}

9634

9618

It also moves the on expressions for the converted outer joins

9635

9619

and from inner joins to conds.

9636

9620

The function also calculates some attributes for nested joins:

9637

- used_tables

9621

- used_tables

9638

9622

- not_null_tables

9639

9623

- dep_tables.

9640

9624

- on_expr_dep_tables

9657

9641

or the on expression for an embedding nested join contains a conjunctive

9658

9642

predicate rejecting null values for some attribute of the inner tables.

9659

9643

9660

E.g. in the query:

9644

E.g. in the query:

9661

9645

@code

9662

9646

SELECT * FROM t1 LEFT JOIN t2 ON t2.a=t1.a WHERE t2.b < 5

9663

9647

@endcode

9675

9659

Similarly the following query:

9676

9660

@code

9677

9661

SELECT * from t1 LEFT JOIN (t2, t3) ON t2.a=t1.a t3.b=t1.b

9678

WHERE t2.c < 5

9662

WHERE t2.c < 5

9679

9663

@endcode

9680

9664

is converted to:

9681

9665

@code

9682

SELECT * FROM t1, (t2, t3) WHERE t2.c < 5 AND t2.a=t1.a t3.b=t1.b

9666

SELECT * FROM t1, (t2, t3) WHERE t2.c < 5 AND t2.a=t1.a t3.b=t1.b

9683

9667

9684

9668

@endcode

9685

9669

9689

9673

LEFT JOIN t3 ON t3.b=t2.b

9690

9674

WHERE t3 IS NOT NULL =>

9691

9675

SELECT * FROM t1 LEFT JOIN t2 ON t2.a=t1.a, t3

9692

WHERE t3 IS NOT NULL AND t3.b=t2.b =>

9676

WHERE t3 IS NOT NULL AND t3.b=t2.b =>

9693

9677

SELECT * FROM t1, t2, t3

9694

9678

WHERE t3 IS NOT NULL AND t3.b=t2.b AND t2.a=t1.a

9695

9679

@endcode

9700

9684

@code

9701

9685

SELECT * FROM t1, (t2, t3) WHERE t2.c < 5 AND t2.a=t1.a AND t3.b=t1.b

9702

9686

@endcode

9703

finally is converted to:

9687

finally is converted to:

9704

9688

@code

9705

9689

SELECT * FROM t1, t2, t3 WHERE t2.c < 5 AND t2.a=t1.a AND t3.b=t1.b

9706

9690

9713

9697

SELECT * from (t1, (t2,t3)) WHERE t1.a=t2.a AND t2.b=t3.b.

9714

9698

@endcode

9715

9699

9716

The benefit of this simplification procedure is that it might return

9700

The benefit of this simplification procedure is that it might return

9717

9701

a query for which the optimizer can evaluate execution plan with more

9718

9702

join orders. With a left join operation the optimizer does not

9719

9703

consider any plan where one of the inner tables is before some of outer

9733

9717

EXAMPLES

9734

9718

Here is an example of a join query with invalid cross references:

9735

9719

@code

9736

SELECT * FROM t1 LEFT JOIN t2 ON t2.a=t3.a LEFT JOIN t3 ON t3.b=t1.b

9720

SELECT * FROM t1 LEFT JOIN t2 ON t2.a=t3.a LEFT JOIN t3 ON t3.b=t1.b

9737

9721

@endcode

9738

9722

9739

9723

@param join reference to the query info

9755

9739

TableList *prev_table= 0;

9756

9740

List_iterator<TableList> li(*join_list);

9757

9741

9758

9742

9759

9743

Try to simplify join operations from join_list.

9760

The most outer join operation is checked for conversion first.

9744

The most outer join operation is checked for conversion first.

9761

9745

9762

9746

while ((table= li++))

9763

9747

{

9766

9750

9767

9751

if ((nested_join= table->nested_join))

9768

9752

{

9769

9753

9770

9754

If the element of join_list is a nested join apply

9771

9755

the procedure to its nested join list first.

9772

9756

9773

9757

if (table->on_expr)

9774

9758

{

9775

9759

Item *expr= table->on_expr;

9776

9777

If an on expression E is attached to the table,

9760

9761

If an on expression E is attached to the table,

9778

9762

check all null rejected predicates in this expression.

9779

9763

If such a predicate over an attribute belonging to

9780

9764

an inner table of an embedded outer join is found,

9781

9765

the outer join is converted to an inner join and

9782

the corresponding on expression is added to E.

9783

9766

the corresponding on expression is added to E.

9767

9784

9768

expr= simplify_joins(join, &nested_join->join_list,

9785

9769

expr, false, in_sj || table->sj_on_expr);

9786

9770

9789

9773

assert(expr);

9790

9774

9791

9775

table->on_expr= expr;

9792

table->prep_on_expr= expr->copy_andor_structure(join->thd);

9776

table->prep_on_expr= expr->copy_andor_structure(join->session);

9793

9777

}

9794

9778

}

9795

9779

nested_join->used_tables= (table_map) 0;

9796

9780

nested_join->not_null_tables=(table_map) 0;

9797

conds= simplify_joins(join, &nested_join->join_list, conds, top,

9781

conds= simplify_joins(join, &nested_join->join_list, conds, top,

9798

9782

in_sj || table->sj_on_expr);

9799

9783

used_tables= nested_join->used_tables;

9800

not_null_tables= nested_join->not_null_tables;

9784

not_null_tables= nested_join->not_null_tables;

9801

9785

}

9802

9786

else

9803

9787

{

9807

9791

if (conds)

9808

9792

not_null_tables= conds->not_null_tables();

9809

9793

}

9810

9794

9811

9795

if (table->embedding)

9812

9796

{

9813

9797

table->embedding->nested_join->used_tables|= used_tables;

9816

9800

9817

9801

if (!table->outer_join || (used_tables & not_null_tables))

9818

9802

{

9819

9803

9820

9804

For some of the inner tables there are conjunctive predicates

9821

9805

that reject nulls => the outer join can be replaced by an inner join.

9822

9806

9830

9814

conds->top_level_item();

9831

9815

/* conds is always a new item as both cond and on_expr existed */

9832

9816

assert(!conds->fixed);

9833

conds->fix_fields(join->thd, &conds);

9817

conds->fix_fields(join->session, &conds);

9834

9818

}

9835

9819

else

9836

conds= table->on_expr;

9820

conds= table->on_expr;

9837

9821

table->prep_on_expr= table->on_expr= 0;

9838

9822

}

9839

9823

}

9840

9824

9841

9825

if (!top)

9842

9826

continue;

9843

9827

9844

9828

9845

9829

Only inner tables of non-convertible outer joins

9846

9830

remain with on_expr.

9847

9831

9848

9832

if (table->on_expr)

9849

9833

{

9850

table->dep_tables|= table->on_expr->used_tables();

9834

table->dep_tables|= table->on_expr->used_tables();

9851

9835

if (table->embedding)

9852

9836

{

9853

table->dep_tables&= ~table->embedding->nested_join->used_tables;

9837

table->dep_tables&= ~table->embedding->nested_join->used_tables;

9854

9838

9855

9839

Embedding table depends on tables used

9856

in embedded on expressions.

9840

in embedded on expressions.

9857

9841

9858

9842

table->embedding->on_expr_dep_tables|= table->on_expr->used_tables();

9859

9843

}

9872

9856

table_map prev_used_tables= prev_table->nested_join ?

9873

9857

prev_table->nested_join->used_tables :

9874

9858

prev_table->table->map;

9875

9859

9876

9860

If on expression contains only references to inner tables

9877

9861

we still make the inner tables dependent on the outer tables.

9878

9862

It would be enough to set dependency only on one outer table

9879

9863

for them. Yet this is really a rare case.

9880

9864

9881

9865

if (!(prev_table->on_expr->used_tables() & ~prev_used_tables))

9882

9866

prev_table->dep_tables|= used_tables;

9883

9867

}

9884

9868

}

9885

9869

prev_table= table;

9886

9870

}

9887

9888

9871

9872

9889

9873

Flatten nested joins that can be flattened.

9890

9874

no ON expression and not a semi-join => can be flattened.

9891

9875

9896

9880

if (table->sj_on_expr && !in_sj)

9897

9881

{

9898

9882

9899

If this is a semi-join that is not contained within another semi-join,

9883

If this is a semi-join that is not contained within another semi-join,

9900

9884

leave it intact (otherwise it is flattened)

9901

9885

9902

9886

join->select_lex->sj_nests.push_back(table);

9909

9893

{

9910

9894

tbl->embedding= table->embedding;

9911

9895

tbl->join_list= table->join_list;

9912

}

9896

}

9913

9897

li.replace(nested_join->join_list);

9914

9898

}

9915

9899

}

9916

return(conds);

9900

return(conds);

9917

9901

}

9918

9902

9919

9903

9937

9921

First unused bit in nested_join_map after the call.

9938

9922

9939

9923

9940

static uint32_t build_bitmap_for_nested_joins(List<TableList> *join_list,

9924

static uint32_t build_bitmap_for_nested_joins(List<TableList> *join_list,

9941

9925

uint32_t first_unused)

9942

9926

{

9943

9927

List_iterator<TableList> li(*join_list);

9950

9934

9951

9935

It is guaranteed by simplify_joins() function that a nested join

9952

9936

that has only one child is either

9953

- a single-table view (the child is the underlying table), or

9937

- a single-table view (the child is the underlying table), or

9954

9938

- a single-table semi-join nest

9955

9939

9956

We don't assign bits to such sj-nests because

9957

1. it is redundant (a "sequence" of one table cannot be interleaved

9940

We don't assign bits to such sj-nests because

9941

1. it is redundant (a "sequence" of one table cannot be interleaved

9958

9942

with anything)

9959

9943

2. we could run out bits in nested_join_map otherwise.

9960

9944

10003

9987

Check interleaving with an inner tables of an outer join for

10004

9988

extension table.

10005

9989

10006

Check if table next_tab can be added to current partial join order, and

9990

Check if table next_tab can be added to current partial join order, and

10007

9991

if yes, record that it has been added.

10008

9992

10009

9993

The function assumes that both current partial join order and its

10010

9994

extension with next_tab are valid wrt table dependencies.

10011

9995

10012

9996

@verbatim

10013

IMPLEMENTATION

9997

IMPLEMENTATION

10014

9998

LIMITATIONS ON JOIN order_st

10015

9999

The nested [outer] joins executioner algorithm imposes these limitations

10016

10000

on join order:

10017

1. "Outer tables first" - any "outer" table must be before any

10001

1. "Outer tables first" - any "outer" table must be before any

10018

10002

corresponding "inner" table.

10019

10003

2. "No interleaving" - tables inside a nested join must form a continuous

10020

sequence in join order (i.e. the sequence must not be interrupted by

10004

sequence in join order (i.e. the sequence must not be interrupted by

10021

10005

tables that are outside of this nested join).

10022

10006

10023

10007

#1 is checked elsewhere, this function checks #2 provided that #1 has

10024

10008

been already checked.

10025

10009

10026

10010

WHY NEED NON-INTERLEAVING

10027

Consider an example:

10011

Consider an example:

10028

10012

10029

10013

select * from t0 join t1 left join (t2 join t3) on cond1

10030

10014

10048

10032

The limitations on join order can be rephrased as follows: for valid

10049

10033

join order one must be able to:

10050

10034

1. write down the used tables in the join order on one line.

10051

2. for each nested join, put one '(' and one ')' on the said line

10035

2. for each nested join, put one '(' and one ')' on the said line

10052

10036

3. write "LEFT JOIN" and "ON (...)" where appropriate

10053

10037

4. get a query equivalent to the query we're trying to execute.

10054

10038

10055

10039

Calls to check_interleaving_with_nj() are equivalent to writing the

10056

above described line from left to right.

10057

A single check_interleaving_with_nj(A,B) call is equivalent to writing

10040

above described line from left to right.

10041

A single check_interleaving_with_nj(A,B) call is equivalent to writing

10058

10042

table B and appropriate brackets on condition that table A and

10059

10043

appropriate brackets is the last what was written. Graphically the

10060

10044

transition is as follows:

10067

10051

position.

10068

10052

10069

10053

Notes about the position:

10070

The caller guarantees that there is no more then one X-bracket by

10071

checking "!(remaining_tables & s->dependent)" before calling this

10054

The caller guarantees that there is no more then one X-bracket by

10055

checking "!(remaining_tables & s->dependent)" before calling this

10072

10056

function. X-bracket may have a pair in Y-bracket.

10073

10057

10074

10058

When "writing" we store/update this auxilary info about the current

10099

10083

10100

10084

if (join->cur_embedding_map & ~next_tab->embedding_map)

10101

10085

{

10102

10086

10103

10087

next_tab is outside of the "pair of brackets" we're currently in.

10104

10088

Cannot add it.

10105

10089

10106

10090

return true;

10107

10091

}

10108

10092

10109

10093

10110

10094

Do update counters for "pairs of brackets" that we've left (marked as

10111

10095

X,Y,Z in the above picture)

10115

10099

next_emb->nested_join->counter_++;

10116

10100

if (next_emb->nested_join->counter_ == 1)

10117

10101

{

10118

10102

10119

10103

next_emb is the first table inside a nested join we've "entered". In

10120

10104

the picture above, we're looking at the 'X' bracket. Don't exit yet as

10121

10105

X bracket might have Y pair bracket.

10122

10106

10123

10107

join->cur_embedding_map |= next_emb->nested_join->nj_map;

10124

10108

}

10125

10109

10126

10110

if (next_emb->nested_join->join_list.elements !=

10127

10111

next_emb->nested_join->counter_)

10128

10112

break;

10141

10125

Nested joins perspective: Remove the last table from the join order.

10142

10126

10143

10127

Remove the last table from the partial join order and update the nested

10144

joins counters and join->cur_embedding_map. It is ok to call this

10145

function for the first table in join order (for which

10128

joins counters and join->cur_embedding_map. It is ok to call this

10129

function for the first table in join order (for which

10146

10130

check_interleaving_with_nj has not been called)

10147

10131

10148

10132

@param last join table to remove, it is assumed to be the last in current

10160

10144

if (!(--last_emb->nested_join->counter_))

10161

10145

join->cur_embedding_map&= ~last_emb->nested_join->nj_map;

10162

10146

else if (last_emb->nested_join->join_list.elements-1 ==

10163

last_emb->nested_join->counter_)

10147

last_emb->nested_join->counter_)

10164

10148

join->cur_embedding_map|= last_emb->nested_join->nj_map;

10165

10149

else

10166

10150

break;

10171

10155

10172

10156

10173

10157

10174

static

10158

static

10175

10159

void advance_sj_state(const table_map remaining_tables, const JOIN_TAB *tab)

10176

10160

{

10177

10161

TableList *emb_sj_nest;

10189

10173

we assume remaining_tables doesnt contain @tab.

10190

10174

10191

10175

10192

static void restore_prev_sj_state(const table_map remaining_tables,

10176

static void restore_prev_sj_state(const table_map remaining_tables,

10193

10177

const JOIN_TAB *tab)

10194

10178

{

10195

10179

TableList *emb_sj_nest;

10196

10180

if ((emb_sj_nest= tab->emb_sj_nest))

10197

10181

{

10198

10182

/* If we're removing the last SJ-inner table, remove the sj-nest */

10199

if ((remaining_tables & emb_sj_nest->sj_inner_tables) ==

10183

if ((remaining_tables & emb_sj_nest->sj_inner_tables) ==

10200

10184

(emb_sj_nest->sj_inner_tables & ~tab->table->map))

10201

10185

{

10202

10186

tab->join->cur_emb_sj_nests &= ~emb_sj_nest->sj_inner_tables;

10209

10193

optimize_cond(JOIN *join, COND *conds, List<TableList> *join_list,

10210

10194

Item::cond_result *cond_value)

10211

10195

{

10212

THD *thd= join->thd;

10196

Session *session= join->session;

10213

10197

10214

10198

if (!conds)

10215

10199

*cond_value= Item::COND_TRUE;

10216

10200

else

10217

10201

{

10218

10202

10219

10203

Build all multiple equality predicates and eliminate equality

10220

10204

predicates that can be inferred from these multiple equalities.

10221

10205

For each reference of a field included into a multiple equality

10222

10206

that occurs in a function set a pointer to the multiple equality

10223

10207

predicate. Substitute a constant instead of this field if the

10224

10208

multiple equality contains a constant.

10225

10226

conds= build_equal_items(join->thd, conds, NULL, join_list,

10209

10210

conds= build_equal_items(join->session, conds, NULL, join_list,

10227

10211

&join->cond_equal);

10228

10212

10229

10213

/* change field = field to field = const for each found field = const */

10230

propagate_cond_constants(thd, (I_List<COND_CMP> *) 0, conds, conds);

10214

propagate_cond_constants(session, (I_List<COND_CMP> *) 0, conds, conds);

10231

10215

10232

10216

Remove all instances of item == item

10233

10217

Remove all and-levels where CONST item != CONST item

10234

10218

10235

conds= remove_eq_conds(thd, conds, cond_value) ;

10219

conds= remove_eq_conds(session, conds, cond_value) ;

10236

10220

}

10237

10221

return(conds);

10238

10222

}

10250

10234

10251

10235

10252

10236

COND *

10253

remove_eq_conds(THD *thd, COND *cond, Item::cond_result *cond_value)

10237

remove_eq_conds(Session *session, COND *cond, Item::cond_result *cond_value)

10254

10238

{

10255

10239

if (cond->type() == Item::COND_ITEM)

10256

10240

{

10257

bool and_level= ((Item_cond*) cond)->functype()

10258

== Item_func::COND_AND_FUNC;

10241

bool and_level= (((Item_cond*) cond)->functype() == Item_func::COND_AND_FUNC);

10242

10259

10243

List_iterator<Item> li(*((Item_cond*) cond)->argument_list());

10260

10244

Item::cond_result tmp_cond_value;

10261

bool should_fix_fields=0;

10245

bool should_fix_fields= false;

10262

10246

10263

*cond_value=Item::COND_UNDEF;

10247

*cond_value= Item::COND_UNDEF;

10264

10248

Item *item;

10265

while ((item=li++))

10249

while ((item= li++))

10266

10250

{

10267

Item *new_item=remove_eq_conds(thd, item, &tmp_cond_value);

10268

if (!new_item)

10269

li.remove();

10251

Item *new_item= remove_eq_conds(session, item, &tmp_cond_value);

10252

if (! new_item)

10253

li.remove();

10270

10254

else if (item != new_item)

10271

10255

{

10272

li.replace(new_item);

10273

should_fix_fields=1;

10256

li.replace(new_item);

10257

should_fix_fields= true;

10274

10258

}

10275

10259

if (*cond_value == Item::COND_UNDEF)

10276

*cond_value=tmp_cond_value;

10277

switch (tmp_cond_value) {

10278

case Item::COND_OK: // Not true or false

10279

if (and_level || *cond_value == Item::COND_FALSE)

10280

*cond_value=tmp_cond_value;

10281

break;

10282

case Item::COND_FALSE:

10283

if (and_level)

10284

{

10285

*cond_value=tmp_cond_value;

10286

return (COND*) 0; // Always false

10287

}

10288

break;

10289

case Item::COND_TRUE:

10290

if (!and_level)

10291

{

10292

*cond_value= tmp_cond_value;

10293

return (COND*) 0; // Always true

10294

}

10295

break;

10296

case Item::COND_UNDEF: // Impossible

10297

break; /* purecov: deadcode */

10260

*cond_value= tmp_cond_value;

10261

10262

switch (tmp_cond_value)

10263

{

10264

case Item::COND_OK: /* Not true or false */

10265

if (and_level || (*cond_value == Item::COND_FALSE))

10266

*cond_value= tmp_cond_value;

10267

break;

10268

case Item::COND_FALSE:

10269

if (and_level)

10270

{

10271

*cond_value= tmp_cond_value;

10272

return (COND *) NULL; /* Always false */

10273

}

10274

break;

10275

case Item::COND_TRUE:

10276

if (! and_level)

10277

{

10278

*cond_value= tmp_cond_value;

10279

return (COND *) NULL; /* Always true */

10280

}

10281

break;

10282

case Item::COND_UNDEF: /* Impossible */

10283

break; /* purecov: deadcode */

10298

10284

}

10299

10285

}

10286

10300

10287

if (should_fix_fields)

10301

10288

cond->update_used_tables();

10302

10289

10303

if (!((Item_cond*) cond)->argument_list()->elements ||

10304

*cond_value != Item::COND_OK)

10305

return (COND*) 0;

10290

if (! ((Item_cond*) cond)->argument_list()->elements || *cond_value != Item::COND_OK)

10291

return (COND*) NULL;

10292

10306

10293

if (((Item_cond*) cond)->argument_list()->elements == 1)

10307

{ // Remove list

10294

{

10295

/* Argument list contains only one element, so reduce it so a single item, then remove list */

10308

10296

item= ((Item_cond*) cond)->argument_list()->head();

10309

10297

((Item_cond*) cond)->argument_list()->empty();

10310

10298

return item;

10311

10299

}

10312

10300

}

10313

else if (cond->type() == Item::FUNC_ITEM &&

10314

((Item_func*) cond)->functype() == Item_func::ISNULL_FUNC)

10301

else if (cond->type() == Item::FUNC_ITEM && ((Item_func*) cond)->functype() == Item_func::ISNULL_FUNC)

10315

10302

{

10316

10303

10317

10304

Handles this special case for some ODBC applications:

10323

10310

SELECT * from table_name where auto_increment_column = LAST_INSERT_ID

10324

10311

10325

10312

10326

Item_func_isnull *func=(Item_func_isnull*) cond;

10313

Item_func_isnull *func= (Item_func_isnull*) cond;

10327

10314

Item **args= func->arguments();

10328

10315

if (args[0]->type() == Item::FIELD_ITEM)

10329

10316

{

10330

Field *field=((Item_field*) args[0])->field;

10331

if (field->flags & AUTO_INCREMENT_FLAG && !field->table->maybe_null &&

10332

(thd->options & OPTION_AUTO_IS_NULL) &&

10333

(thd->first_successful_insert_id_in_prev_stmt > 0 &&

10334

thd->substitute_null_with_insert_id))

10317

Field *field= ((Item_field*) args[0])->field;

10318

if (field->flags & AUTO_INCREMENT_FLAG

10319

&& ! field->table->maybe_null

10320

&& session->options & OPTION_AUTO_IS_NULL

10321

&& (

10322

session->first_successful_insert_id_in_prev_stmt > 0

10323

&& session->substitute_null_with_insert_id

10324

)

10325

)

10335

10326

{

10336

COND *new_cond;

10337

if ((new_cond= new Item_func_eq(args[0],

10338

new Item_int("last_insert_id()",

10339

thd->read_first_successful_insert_id_in_prev_stmt(),

10340

MY_INT64_NUM_DECIMAL_DIGITS))))

10341

{

10342

cond=new_cond;

10327

COND *new_cond;

10328

if ((new_cond= new Item_func_eq(args[0], new Item_int("last_insert_id()",

10329

session->read_first_successful_insert_id_in_prev_stmt(),

10330

MY_INT64_NUM_DECIMAL_DIGITS))))

10331

{

10332

cond= new_cond;

10343

10333

10344

10334

Item_func_eq can't be fixed after creation so we do not check

10345

10335

cond->fixed, also it do not need tables so we use 0 as second

10346

10336

argument.

10347

10337

10348

cond->fix_fields(thd, &cond);

10349

}

10338

cond->fix_fields(session, &cond);

10339

}

10350

10340

10351

10341

IS NULL should be mapped to LAST_INSERT_ID only for first row, so

10352

10342

clear for next row

10353

10343

10354

thd->substitute_null_with_insert_id= false;

10344

session->substitute_null_with_insert_id= false;

10355

10345

}

10346

#ifdef NOTDEFINED

10356

10347

/* fix to replace 'NULL' dates with '0' (shreeve@uci.edu) */

10357

else if (((field->type() == DRIZZLE_TYPE_NEWDATE) ||

10358

(field->type() == DRIZZLE_TYPE_DATETIME)) &&

10359

(field->flags & NOT_NULL_FLAG) &&

10360

!field->table->maybe_null)

10348

else if (

10349

((field->type() == DRIZZLE_TYPE_DATE) || (field->type() == DRIZZLE_TYPE_DATETIME))

10350

&& (field->flags & NOT_NULL_FLAG)

10351

&& ! field->table->maybe_null)

10361

10352

{

10362

COND *new_cond;

10363

if ((new_cond= new Item_func_eq(args[0],new Item_int("0", 0, 2))))

10364

{

10365

cond=new_cond;

10353

COND *new_cond;

10354

if ((new_cond= new Item_func_eq(args[0],new Item_int("0", 0, 2))))

10355

{

10356

cond= new_cond;

10366

10357

10367

10358

Item_func_eq can't be fixed after creation so we do not check

10368

10359

cond->fixed, also it do not need tables so we use 0 as second

10369

10360

argument.

10370

10361

10371

cond->fix_fields(thd, &cond);

10372

}

10362

cond->fix_fields(session, &cond);

10363

}

10373

10364

}

10365

#endif /* NOTDEFINED */

10374

10366

}

10375

10367

if (cond->const_item())

10376

10368

{

10377

10369

*cond_value= eval_const_cond(cond) ? Item::COND_TRUE : Item::COND_FALSE;

10378

return (COND*) 0;

10370

return (COND *) NULL;

10379

10371

}

10380

10372

}

10381

10373

else if (cond->const_item() && !cond->is_expensive())

10391

10383

10392

10384

{

10393

10385

*cond_value= eval_const_cond(cond) ? Item::COND_TRUE : Item::COND_FALSE;

10394

return (COND*) 0;

10386

return (COND *) NULL;

10395

10387

}

10396

10388

else if ((*cond_value= cond->eq_cmp_result()) != Item::COND_OK)

10397

{ // boolan compare function

10389

{

10390

/* boolan compare function */

10398

10391

Item *left_item= ((Item_func*) cond)->arguments()[0];

10399

10392

Item *right_item= ((Item_func*) cond)->arguments()[1];

10400

10393

if (left_item->eq(right_item,1))

10401

10394

{

10402

if (!left_item->maybe_null ||

10403

((Item_func*) cond)->functype() == Item_func::EQUAL_FUNC)

10404

return (COND*) 0; // Compare of identical items

10395

if (!left_item->maybe_null || ((Item_func*) cond)->functype() == Item_func::EQUAL_FUNC)

10396

return (COND*) NULL; /* Comparison of identical items */

10405

10397

}

10406

10398

}

10407

*cond_value=Item::COND_OK;

10408

return cond; // Point at next and level

10399

*cond_value= Item::COND_OK;

10400

return cond; /* Point at next and return into recursion */

10409

10401

}

10410

10402

10411

10403

10412

10404

Check if equality can be used in removing components of GROUP BY/DISTINCT

10413

10405

10414

10406

SYNOPSIS

10415

10407

test_if_equality_guarantees_uniqueness()

10416

10408

l the left comparison argument (a field if any)

10417

10409

r the right comparison argument (a const of any)

10418

10419

DESCRIPTION

10420

Checks if an equality predicate can be used to take away

10421

DISTINCT/GROUP BY because it is known to be true for exactly one

10410

10411

DESCRIPTION

10412

Checks if an equality predicate can be used to take away

10413

DISTINCT/GROUP BY because it is known to be true for exactly one

10422

10414

distinct value (e.g. <expr> == <const>).

10423

Arguments must be of the same type because e.g.

10424

<string_field> = <int_const> may match more than 1 distinct value from

10425

the column.

10426

We must take into consideration and the optimization done for various

10415

Arguments must be of the same type because e.g.

10416

<string_field> = <int_const> may match more than 1 distinct value from

10417

the column.

10418

We must take into consideration and the optimization done for various

10427

10419

string constants when compared to dates etc (see Item_int_with_ref) as

10428

10420

well as the collation of the arguments.

10429

10430

RETURN VALUE

10421

10422

RETURN VALUE

10431

10423

true can be used

10432

10424

false cannot be used

10433

10425

10518

10510

Next_select_func setup_end_select_func(JOIN *join)

10519

10511

{

10520

10512

Table *table= join->tmp_table;

10521

TMP_TABLE_PARAM *tmp_tbl= &join->tmp_table_param;

10513

Tmp_Table_Param *tmp_tbl= &join->tmp_table_param;

10522

10514

Next_select_func end_select;

10523

10515

10524

10516

/* Set up select_end */

10525

10517

if (table)

10526

10518

{

10527

if (table->group && tmp_tbl->sum_func_count &&

10519

if (table->group && tmp_tbl->sum_func_count &&

10528

10520

!tmp_tbl->precomputed_group_by)

10529

10521

{

10530

10522

if (table->s->keys)

10548

10540

10549

10541

A preceding call to create_tmp_table in the case when loose

10550

10542

index scan is used guarantees that

10551

TMP_TABLE_PARAM::items_to_copy has enough space for the group

10543

Tmp_Table_Param::items_to_copy has enough space for the group

10552

10544

by functions. It is OK here to use memcpy since we copy

10553

10545

Item_sum pointers into an array of Item pointers.

10554

10546

10588

10580

int rc= 0;

10589

10581

enum_nested_loop_state error= NESTED_LOOP_OK;

10590

10582

JOIN_TAB *join_tab= NULL;

10591

10583

10592

10584

join->tmp_table= table; /* Save for easy recursion */

10593

10585

join->fields= fields;

10594

10586

10595

10587

if (table)

10596

10588

{

10597

10589

table->file->extra(HA_EXTRA_WRITE_CACHE);

10598

empty_record(table);

10590

table->emptyRecord();

10599

10591

if (table->group && join->tmp_table_param.sum_func_count &&

10600

10592

table->s->keys && !table->file->inited)

10601

10593

table->file->ha_index_init(0, 0);

10627

10619

so we don't touch it here.

10628

10620

10629

10621

join->examined_rows++;

10630

join->thd->row_count++;

10622

join->session->row_count++;

10631

10623

assert(join->examined_rows <= 1);

10632

10624

}

10633

10625

else if (join->send_row_on_empty_set())

10681

10673

if (new_errno)

10682

10674

table->file->print_error(new_errno,MYF(0));

10683

10675

}

10684

return(join->thd->is_error() ? -1 : rc);

10676

return(join->session->is_error() ? -1 : rc);

10685

10677

}

10686

10678

10687

10679

10697

10689

rc= sub_select(join,join_tab,end_of_records);

10698

10690

return rc;

10699

10691

}

10700

if (join->thd->killed) // If aborted by user

10692

if (join->session->killed) // If aborted by user

10701

10693

{

10702

join->thd->send_kill_message();

10694

join->session->send_kill_message();

10703

10695

return NESTED_LOOP_KILLED; /* purecov: inspected */

10704

10696

}

10705

10697

if (join_tab->use_quick != 2 || test_if_quick_select(join_tab) <= 0)

10717

10709

/**

10718

10710

Retrieve records ends with a given beginning from the result of a join.

10719

10711

10720

For a given partial join record consisting of records from the tables

10712

For a given partial join record consisting of records from the tables

10721

10713

preceding the table join_tab in the execution plan, the function

10722

10714

retrieves all matching full records from the result set and

10723

send them to the result set stream.

10715

send them to the result set stream.

10724

10716

10725

10717

@note

10726

10718

The function effectively implements the final (n-k) nested loops

10760

10752

first row with t3.a=t1.a has been encountered.

10761

10753

Thus, the second predicate P2 is supplied with a guarded value that are

10762

10754

stored in the field 'found' of the first inner table for the outer join

10763

(table t2). When the first row with t3.a=t1.a for the current row

10755

(table t2). When the first row with t3.a=t1.a for the current row

10764

10756

of table t1 appears, the value becomes true. For now on the predicate

10765

10757

is evaluated immediately after the row of table t2 has been read.

10766

10758

When the first row with t3.a=t1.a has been encountered all

10768

10760

Only when all of them are true the row is sent to the output stream.

10769

10761

If not, the function returns to the lowest nest level that has a false

10770

10762

attached condition.

10771

The predicates from on expressions are also pushed down. If in the

10763

The predicates from on expressions are also pushed down. If in the

10772

10764

the above example the on expression were (t3.a=t1.a AND t2.a=t1.a),

10773

10765

then t1.a=t2.a would be pushed down to table t2, and without any

10774

10766

guard.

10778

10770

is complemented by nulls for t2 and t3. Then the pushed down predicates

10779

10771

are checked for the composed row almost in the same way as it had

10780

10772

been done for the first row with a match. The only difference is

10781

the predicates from on expressions are not checked.

10773

the predicates from on expressions are not checked.

10782

10774

10783

10775

@par

10784

10776

@b IMPLEMENTATION

10794

10786

and a pointer to a guarding boolean variable.

10795

10787

When the value of the guard variable is true the value of the object

10796

10788

is the same as the value of the predicate, otherwise it's just returns

10797

true.

10798

To carry out a return to a nested loop level of join table t the pointer

10789

true.

10790

To carry out a return to a nested loop level of join table t the pointer

10799

10791

to t is remembered in the field 'return_tab' of the join structure.

10800

10792

Consider the following query:

10801

10793

@code

10812

10804

t5.a=t3.a is found, the pushed down predicate t4.b=2 OR t4.b IS NULL

10813

10805

becomes 'activated', as well the predicate t4.a=t2.a. But

10814

10806

the predicate (t2.b=5 OR t2.b IS NULL) can not be checked until

10815

t4.a=t2.a becomes true.

10807

t4.a=t2.a becomes true.

10816

10808

In order not to re-evaluate the predicates that were already evaluated

10817

10809

as attached pushed down predicates, a pointer to the the first

10818

10810

most inner unmatched table is maintained in join_tab->first_unmatched.

10819

10811

Thus, when the first row from t5 with t5.a=t3.a is found

10820

this pointer for t5 is changed from t4 to t2.

10812

this pointer for t5 is changed from t4 to t2.

10821

10813

10822

10814

@par

10823

10815

@b STRUCTURE @b NOTES

10828

10820

@param join pointer to the structure providing all context info for

10829

10821

the query

10830

10822

@param join_tab the first next table of the execution plan to be retrieved

10831

@param end_records true when we need to perform final steps of retrival

10823

@param end_records true when we need to perform final steps of retrival

10832

10824

10833

10825

@return

10834

10826

return one of enum_nested_loop_state, except NESTED_LOOP_NO_MORE_ROWS.

10877

10869

/* Set first_unmatched for the last inner table of this group */

10878

10870

join_tab->last_inner->first_unmatched= join_tab;

10879

10871

}

10880

join->thd->row_count= 0;

10872

join->session->row_count= 0;

10881

10873

10882

10874

error= (*join_tab->read_first_record)(join_tab);

10883

10875

rc= evaluate_join_record(join, join_tab, error);

10884

10876

}

10885

10886

10887

Note: psergey has added the 2nd part of the following condition; the

10877

10878

10879

Note: psergey has added the 2nd part of the following condition; the

10888

10880

change should probably be made in 5.1, too.

10889

10881

10890

10882

while (rc == NESTED_LOOP_OK && join->return_tab >= join_tab)

10910

10902

10911

10903

SYNPOSIS

10912

10904

do_sj_dups_weedout()

10913

10905

10914

10906

RETURN

10915

10907

-1 Error

10916

10908

1 The row combination is a duplicate (discard it)

10917

10909

0 The row combination is not a duplicate (continue)

10918

10910

10919

10911

10920

int do_sj_dups_weedout(THD *thd, SJ_TMP_TABLE *sjtbl)

10912

int do_sj_dups_weedout(Session *session, SJ_TMP_TABLE *sjtbl)

10921

10913

{

10922

10914

int error;

10923

10915

SJ_TMP_TABLE::TAB *tab= sjtbl->tabs;

10924

10916

SJ_TMP_TABLE::TAB *tab_end= sjtbl->tabs_end;

10925

10917

unsigned char *ptr= sjtbl->tmp_table->record[0] + 1;

10926

10918

unsigned char *nulls_ptr= ptr;

10927

10919

10928

10920

/* Put the the rowids tuple into table->record[0]: */

10929

10921

10930

// 1. Store the length

10922

// 1. Store the length

10931

10923

if (((Field_varstring*)(sjtbl->tmp_table->field[0]))->length_bytes == 1)

10932

10924

{

10933

10925

*ptr= (unsigned char)(sjtbl->rowid_len + sjtbl->null_bytes);

10939

10931

ptr += 2;

10940

10932

}

10941

10933

10942

// 2. Zero the null bytes

10934

// 2. Zero the null bytes

10943

10935

if (sjtbl->null_bytes)

10944

10936

{

10945

10937

memset(ptr, 0, sjtbl->null_bytes);

10946

ptr += sjtbl->null_bytes;

10938

ptr += sjtbl->null_bytes;

10947

10939

}

10948

10940

10949

10941

// 3. Put the rowids

10970

10962

{

10971

10963

/* create_myisam_from_heap will generate error if needed */

10972

10964

if (sjtbl->tmp_table->file->is_fatal_error(error, HA_CHECK_DUP) &&

10973

create_myisam_from_heap(thd, sjtbl->tmp_table, sjtbl->start_recinfo,

10965

create_myisam_from_heap(session, sjtbl->tmp_table, sjtbl->start_recinfo,

10974

10966

&sjtbl->recinfo, error, 1))

10975

10967

return -1;

10976

10968

//return (error == HA_ERR_FOUND_DUPP_KEY || error== HA_ERR_FOUND_DUPP_UNIQUE) ? 1: -1;

11007

10999

ha_rows found_records=join->found_records;

11008

11000

COND *select_cond= join_tab->select_cond;

11009

11001

11010

if (error > 0 || (join->thd->is_error())) // Fatal error

11002

if (error > 0 || (join->session->is_error())) // Fatal error

11011

11003

return NESTED_LOOP_ERROR;

11012

11004

if (error < 0)

11013

11005

return NESTED_LOOP_NO_MORE_ROWS;

11014

if (join->thd->killed) // Aborted by user

11006

if (join->session->killed) // Aborted by user

11015

11007

{

11016

join->thd->send_kill_message();

11008

join->session->send_kill_message();

11017

11009

return NESTED_LOOP_KILLED; /* purecov: inspected */

11018

11010

}

11019

11011

if (!select_cond || select_cond->val_int())

11076

11068

join_tab->found_match= true;

11077

11069

if (join_tab->check_weed_out_table)

11078

11070

{

11079

int res= do_sj_dups_weedout(join->thd, join_tab->check_weed_out_table);

11071

int res= do_sj_dups_weedout(join->session, join_tab->check_weed_out_table);

11080

11072

if (res == -1)

11081

11073

return NESTED_LOOP_ERROR;

11082

11074

if (res == 1)

11084

11076

}

11085

11077

else if (join_tab->do_firstmatch)

11086

11078

{

11087

11088

We should return to the join_tab->do_firstmatch after we have

11079

11080

We should return to the join_tab->do_firstmatch after we have

11089

11081

enumerated all the suffixes for current prefix row combination

11090

11082

11091

11083

return_tab= join_tab->do_firstmatch;

11097

11089

(See above join->return_tab= tab).

11098

11090

11099

11091

join->examined_rows++;

11100

join->thd->row_count++;

11092

join->session->row_count++;

11101

11093

11102

11094

if (found)

11103

11095

{

11129

11121

with the beginning coinciding with the current partial join.

11130

11122

11131

11123

join->examined_rows++;

11132

join->thd->row_count++;

11124

join->session->row_count++;

11133

11125

join_tab->read_record.file->unlock_row();

11134

11126

}

11135

11127

return NESTED_LOOP_OK;

11160

11152

join_tab->found= 1;

11161

11153

join_tab->not_null_compl= 0;

11162

11154

/* The outer row is complemented by nulls for each inner tables */

11163

restore_record(join_tab->table,s->default_values); // Make empty record

11164

mark_as_null_row(join_tab->table); // For group by without error

11155

join_tab->table->restoreRecordAsDefault(); // Make empty record

11156

join_tab->table->mark_as_null_row(); // For group by without error

11165

11157

select_cond= join_tab->select_cond;

11166

11158

/* Check all attached conditions for inner table rows. */

11167

11159

if (select_cond && !select_cond->val_int())

11239

11231

info= &join_tab->read_record;

11240

11232

11241

11233

{

11242

if (join->thd->killed)

11234

if (join->session->killed)

11243

11235

{

11244

join->thd->send_kill_message();

11236

join->session->send_kill_message();

11245

11237

return NESTED_LOOP_KILLED; // Aborted by user /* purecov: inspected */

11246

11238

}

11247

11239

SQL_SELECT *select=join_tab->select;

11256

11248

if (!select || !select->skip_record())

11257

11249

{

11258

11250

int res= 0;

11259

if (!join_tab->check_weed_out_table ||

11260

!(res= do_sj_dups_weedout(join->thd, join_tab->check_weed_out_table)))

11251

if (!join_tab->check_weed_out_table ||

11252

!(res= do_sj_dups_weedout(join->session, join_tab->check_weed_out_table)))

11261

11253

{

11262

11254

rc= (join_tab->next_select)(join,join_tab+1,0);

11263

11255

if (rc != NESTED_LOOP_OK && rc != NESTED_LOOP_NO_MORE_ROWS)

11304

11296

table->const_table=1;

11305

11297

table->null_row=0;

11306

11298

table->status=STATUS_NO_RECORD;

11307

11299

11308

11300

if (tab->type == JT_SYSTEM)

11309

11301

{

11310

11302

if ((error=join_read_system(tab)))

11319

11311

}

11320

11312

else

11321

11313

{

11322

if (!table->key_read && table->covering_keys.is_set(tab->ref.key) &&

11314

if (!table->key_read && table->covering_keys.test(tab->ref.key) &&

11323

11315

!table->no_keyread &&

11324

11316

(int) table->reginfo.lock_type <= (int) TL_READ_HIGH_PRIORITY)

11325

11317

{

11346

11338

if (*tab->on_expr_ref && !table->null_row)

11347

11339

{

11348

11340

if ((table->null_row= test((*tab->on_expr_ref)->val_int() == 0)))

11349

mark_as_null_row(table);

11341

table->mark_as_null_row();

11350

11342

}

11351

11343

if (!table->null_row)

11352

11344

table->maybe_null=0;

11387

11379

{

11388

11380

if (error != HA_ERR_END_OF_FILE)

11389

11381

return table->report_error(error);

11390

mark_as_null_row(tab->table);

11391

empty_record(table); // Make empty record

11382

tab->table->mark_as_null_row();

11383

table->emptyRecord(); // Make empty record

11392

11384

return -1;

11393

11385

}

11394

store_record(table,record[1]);

11386

table->storeRecord();

11395

11387

}

11396

11388

else if (!table->status) // Only happens with left join

11397

restore_record(table,record[1]); // restore old record

11389

table->restoreRecord(); // restore old record

11398

11390

table->null_row=0;

11399

11391

return table->status ? -1 : 0;

11400

11392

}

11421

11413

if (table->status & STATUS_GARBAGE) // If first read

11422

11414

{

11423

11415

table->status= 0;

11424

if (cp_buffer_from_ref(tab->join->thd, &tab->ref))

11416

if (cp_buffer_from_ref(tab->join->session, &tab->ref))

11425

11417

error=HA_ERR_KEY_NOT_FOUND;

11426

11418

else

11427

11419

{

11433

11425

if (error)

11434

11426

{

11435

11427

table->status= STATUS_NOT_FOUND;

11436

mark_as_null_row(tab->table);

11437

empty_record(table);

11428

tab->table->mark_as_null_row();

11429

table->emptyRecord();

11438

11430

if (error != HA_ERR_KEY_NOT_FOUND && error != HA_ERR_END_OF_FILE)

11439

11431

return table->report_error(error);

11440

11432

return -1;

11441

11433

}

11442

store_record(table,record[1]);

11434

table->storeRecord();

11443

11435

}

11444

11436

else if (!(table->status & ~STATUS_NULL_ROW)) // Only happens with left join

11445

11437

{

11446

11438

table->status=0;

11447

restore_record(table,record[1]); // restore old record

11439

table->restoreRecord(); // restore old record

11448

11440

}

11449

11441

table->null_row=0;

11450

11442

return table->status ? -1 : 0;

11464

11456

11465

11457

RETURN

11466

11458

0 - Ok

11467

-1 - Row not found

11459

-1 - Row not found

11468

11460

1 - Error

11469

11461

11470

11462

11509

11501

11510

11502

DESCRIPTION

11511

11503

This is "read_fist" function for the "ref" access method.

11512

11504

11513

11505

The functon must leave the index initialized when it returns.

11514

11506

ref_or_null access implementation depends on that.

11515

11507

11516

11508

RETURN

11517

11509

0 - Ok

11518

-1 - Row not found

11510

-1 - Row not found

11519

11511

1 - Error

11520

11512

11521

11513

11528

11520

/* Initialize the index first */

11529

11521

if (!table->file->inited)

11530

11522

table->file->ha_index_init(tab->ref.key, tab->sorted);

11531

11523

11532

11524

/* Perform "Late NULLs Filtering" (see internals manual for explanations) */

11533

11525

for (uint32_t i= 0 ; i < tab->ref.key_parts ; i++)

11534

11526

{

11536

11528

return -1;

11537

11529

}

11538

11530

11539

if (cp_buffer_from_ref(tab->join->thd, &tab->ref))

11531

if (cp_buffer_from_ref(tab->join->session, &tab->ref))

11540

11532

return -1;

11541

11533

if ((error=table->file->index_read_map(table->record[0],

11542

11534

tab->ref.key_buff,

11547

11539

return table->report_error(error);

11548

11540

return -1; /* purecov: inspected */

11549

11541

}

11542

11550

11543

return 0;

11551

11544

}

11552

11545

11553

11546

11554

11547

/**

11555

This function is used when optimizing away order_st BY in

11548

This function is used when optimizing away order_st BY in

11556

11549

SELECT * FROM t1 WHERE a=1 order_st BY a DESC,b DESC.

11557

11550

11558

11551

11559

11552

static int

11560

11553

join_read_last_key(JOIN_TAB *tab)

11561

11554

{

11564

11557

11565

11558

if (!table->file->inited)

11566

11559

table->file->ha_index_init(tab->ref.key, tab->sorted);

11567

if (cp_buffer_from_ref(tab->join->thd, &tab->ref))

11560

if (cp_buffer_from_ref(tab->join->session, &tab->ref))

11568

11561

return -1;

11569

11562

if ((error=table->file->index_read_last_map(table->record[0],

11570

11563

tab->ref.key_buff,

11580

11573

11581

11574

/* ARGSUSED */

11582

11575

static int

11583

join_no_more_records(READ_RECORD *info __attribute__((unused)))

11576

join_no_more_records(READ_RECORD *)

11584

11577

{

11585

11578

return -1;

11586

11579

}

11593

11586

if (tab->insideout_match_tab->found_match)

11594

11587

{

11595

11588

KEY *key= tab->table->key_info + tab->index;

11596

11589

11597

11590

{

11598

11591

int error;

11599

11592

/* Save index tuple from record to the buffer */

11608

11601

table->status= STATUS_GARBAGE;

11609

11602

return -1;

11610

11603

}

11611

} while (!key_cmp(tab->table->key_info[tab->index].key_part,

11604

} while (!key_cmp(tab->table->key_info[tab->index].key_part,

11612

11605

tab->insideout_buf, key->key_length));

11613

11606

tab->insideout_match_tab->found_match= 0;

11614

11607

return 0;

11633

11626

table->status= STATUS_GARBAGE;

11634

11627

return -1;

11635

11628

}

11629

11636

11630

return 0;

11637

11631

}

11638

11632

11652

11646

table->status=STATUS_NOT_FOUND;

11653

11647

error= -1;

11654

11648

}

11649

11655

11650

return error;

11656

11651

}

11657

11652

11679

11674

{

11680

11675

delete tab->select->quick;

11681

11676

tab->select->quick=0;

11682

return tab->select->test_quick_select(tab->join->thd, tab->keys,

11677

return tab->select->test_quick_select(tab->join->session, tab->keys,

11683

11678

(table_map) 0, HA_POS_ERROR, 0,

11684

11679

false);

11685

11680

}

11690

11685

{

11691

11686

if (tab->select && tab->select->quick && tab->select->quick->reset())

11692

11687

return 1;

11693

init_read_record(&tab->read_record, tab->join->thd, tab->table,

11688

init_read_record(&tab->read_record, tab->join->session, tab->table,

11694

11689

tab->select,1,1);

11695

11690

return (*tab->read_record.read_record)(&tab->read_record);

11696

11691

}

11701

11696

{

11702

11697

int error;

11703

11698

Table *table=tab->table;

11704

if (!table->key_read && table->covering_keys.is_set(tab->index) &&

11699

if (!table->key_read && table->covering_keys.test(tab->index) &&

11705

11700

!table->no_keyread)

11706

11701

{

11707

11702

table->key_read=1;

11732

11727

table->report_error(error);

11733

11728

return -1;

11734

11729

}

11730

11735

11731

return 0;

11736

11732

}

11737

11733

11743

11739

if (tab->insideout_match_tab->found_match)

11744

11740

{

11745

11741

KEY *key= tab->table->key_info + tab->index;

11746

11742

11747

11743

{

11748

11744

int error;

11749

11745

/* Save index tuple from record to the buffer */

11751

11747

11752

11748

if ((error=info->file->index_next(info->record)))

11753

11749

return info->table->report_error(error);

11754

11755

} while (!key_cmp(tab->table->key_info[tab->index].key_part,

11750

} while (!key_cmp(tab->table->key_info[tab->index].key_part,

11756

11751

tab->insideout_buf, key->key_length));

11757

11752

tab->insideout_match_tab->found_match= 0;

11758

11753

return 0;

11777

11772

{

11778

11773

Table *table=tab->table;

11779

11774

int error;

11780

if (!table->key_read && table->covering_keys.is_set(tab->index) &&

11775

if (!table->key_read && table->covering_keys.test(tab->index) &&

11781

11776

!table->no_keyread)

11782

11777

{

11783

11778

table->key_read=1;

11793

11788

table->file->ha_index_init(tab->index, 1);

11794

11789

if ((error= tab->table->file->index_last(tab->table->record[0])))

11795

11790

return table->report_error(error);

11791

11796

11792

return 0;

11797

11793

}

11798

11794

11803

11799

int error;

11804

11800

if ((error= info->file->index_prev(info->record)))

11805

11801

return info->table->report_error(error);

11802

11806

11803

return 0;

11807

11804

}

11808

11805

11869

11866

11870

11867

/* ARGSUSED */

11871

11868

static enum_nested_loop_state

11872

end_send(JOIN *join, JOIN_TAB *join_tab __attribute__((unused)),

11873

bool end_of_records)

11869

end_send(JOIN *join, JOIN_TAB *,

11870

bool end_of_records)

11874

11871

{

11875

11872

if (!end_of_records)

11876

11873

{

11910

11907

join->send_records= table->file->stats.records;

11911

11908

}

11912

11909

}

11913

else

11910

else

11914

11911

{

11915

11912

join->do_send_rows= 0;

11916

11913

if (join->unit->fake_select_lex)

11934

11931

}

11935

11932

11936

11933

11937

/* ARGSUSED */

11934

/* ARGSUSED */

11938

11935

enum_nested_loop_state

11939

end_send_group(JOIN *join, JOIN_TAB *join_tab __attribute__((unused)),

11940

bool end_of_records)

11936

end_send_group(JOIN *join, JOIN_TAB *, bool end_of_records)

11941

11937

{

11942

11938

int idx= -1;

11943

11939

enum_nested_loop_state ok_code= NESTED_LOOP_OK;

11945

11941

if (!join->first_record || end_of_records ||

11946

11942

(idx=test_if_item_cache_changed(join->group_fields)) >= 0)

11947

11943

{

11948

if (join->first_record ||

11944

if (join->first_record ||

11949

11945

(end_of_records && !join->group && !join->group_optimized_away))

11950

11946

{

11951

11947

if (idx < (int) join->send_group_parts)

11972

11968

}

11973

11969

if (join->rollup.state != ROLLUP::STATE_NONE && error <= 0)

11974

11970

{

11975

if (join->rollup_send_data((uint) (idx+1)))

11971

if (join->rollup_send_data((uint32_t) (idx+1)))

11976

11972

error= 1;

11977

11973

}

11978

11974

}

12027

12023

}

12028

12024

12029

12025

12030

/* ARGSUSED */

12026

/* ARGSUSED */

12031

12027

enum_nested_loop_state

12032

end_write(JOIN *join, JOIN_TAB *join_tab __attribute__((unused)),

12033

bool end_of_records)

12028

end_write(JOIN *join, JOIN_TAB *,

12029

bool end_of_records)

12034

12030

{

12035

12031

Table *table=join->tmp_table;

12036

12032

12037

if (join->thd->killed) // Aborted by user

12033

if (join->session->killed) // Aborted by user

12038

12034

{

12039

join->thd->send_kill_message();

12035

join->session->send_kill_message();

12040

12036

return(NESTED_LOOP_KILLED); /* purecov: inspected */

12041

12037

}

12042

12038

if (!end_of_records)

12043

12039

{

12044

12040

copy_fields(&join->tmp_table_param);

12045

12041

copy_funcs(join->tmp_table_param.items_to_copy);

12046

#ifdef TO_BE_DELETED

12047

if (!table->uniques) // If not unique handling

12048

{

12049

/* Copy null values from group to row */

12050

order_st *group;

12051

for (group=table->group ; group ; group=group->next)

12052

{

12053

Item *item= *group->item;

12054

if (item->maybe_null)

12055

{

12056

Field *field=item->get_tmp_table_field();

12057

field->ptr[-1]= (unsigned char) (field->is_null() ? 1 : 0);

12058

}

12059

}

12060

}

12061

#endif

12062

12042

if (!join->having || join->having->val_int())

12063

12043

{

12064

12044

int error;

12067

12047

{

12068

12048

if (!table->file->is_fatal_error(error, HA_CHECK_DUP))

12069

12049

goto end;

12070

if (create_myisam_from_heap(join->thd, table,

12050

if (create_myisam_from_heap(join->session, table,

12071

12051

join->tmp_table_param.start_recinfo,

12072

12052

&join->tmp_table_param.recinfo,

12073

12053

error, 1))

12093

12073

/** Group by searching after group record and updating it if possible. */

12094

12074

12095

12075

static enum_nested_loop_state

12096

end_update(JOIN *join, JOIN_TAB *join_tab __attribute__((unused)),

12097

bool end_of_records)

12076

end_update(JOIN *join, JOIN_TAB *,

12077

bool end_of_records)

12098

12078

{

12099

12079

Table *table=join->tmp_table;

12100

12080

order_st *group;

12102

12082

12103

12083

if (end_of_records)

12104

12084

return(NESTED_LOOP_OK);

12105

if (join->thd->killed) // Aborted by user

12085

if (join->session->killed) // Aborted by user

12106

12086

{

12107

join->thd->send_kill_message();

12087

join->session->send_kill_message();

12108

12088

return(NESTED_LOOP_KILLED); /* purecov: inspected */

12109

12089

}

12110

12090

12124

12104

HA_WHOLE_KEY,

12125

12105

HA_READ_KEY_EXACT))

12126

12106

{ /* Update old record */

12127

restore_record(table,record[1]);

12107

table->restoreRecord();

12128

12108

update_tmptable_sum_func(join->sum_funcs,table);

12129

12109

if ((error=table->file->ha_update_row(table->record[1],

12130

12110

table->record[0])))

12152

12132

copy_funcs(join->tmp_table_param.items_to_copy);

12153

12133

if ((error=table->file->ha_write_row(table->record[0])))

12154

12134

{

12155

if (create_myisam_from_heap(join->thd, table,

12135

if (create_myisam_from_heap(join->session, table,

12156

12136

join->tmp_table_param.start_recinfo,

12157

12137

&join->tmp_table_param.recinfo,

12158

12138

error, 0))

12169

12149

/** Like end_update, but this is done with unique constraints instead of keys. */

12170

12150

12171

12151

static enum_nested_loop_state

12172

end_unique_update(JOIN *join, JOIN_TAB *join_tab __attribute__((unused)),

12173

bool end_of_records)

12152

end_unique_update(JOIN *join, JOIN_TAB *,

12153

bool end_of_records)

12174

12154

{

12175

12155

Table *table=join->tmp_table;

12176

12156

int error;

12177

12157

12178

12158

if (end_of_records)

12179

12159

return(NESTED_LOOP_OK);

12180

if (join->thd->killed) // Aborted by user

12160

if (join->session->killed) // Aborted by user

12181

12161

{

12182

join->thd->send_kill_message();

12162

join->session->send_kill_message();

12183

12163

return(NESTED_LOOP_KILLED); /* purecov: inspected */

12184

12164

}

12185

12165

12201

12181

table->file->print_error(error,MYF(0)); /* purecov: inspected */

12202

12182

return(NESTED_LOOP_ERROR); /* purecov: inspected */

12203

12183

}

12204

restore_record(table,record[1]);

12184

table->restoreRecord();

12205

12185

update_tmptable_sum_func(join->sum_funcs,table);

12206

12186

if ((error=table->file->ha_update_row(table->record[1],

12207

12187

table->record[0])))

12214

12194

}

12215

12195

12216

12196

12217

/* ARGSUSED */

12197

/* ARGSUSED */

12218

12198

enum_nested_loop_state

12219

end_write_group(JOIN *join, JOIN_TAB *join_tab __attribute__((unused)),

12220

bool end_of_records)

12199

end_write_group(JOIN *join, JOIN_TAB *,

12200

bool end_of_records)

12221

12201

{

12222

12202

Table *table=join->tmp_table;

12223

12203

int idx= -1;

12224

12204

12225

if (join->thd->killed)

12205

if (join->session->killed)

12226

12206

{ // Aborted by user

12227

join->thd->send_kill_message();

12207

join->session->send_kill_message();

12228

12208

return(NESTED_LOOP_KILLED); /* purecov: inspected */

12229

12209

}

12230

12210

if (!join->first_record || end_of_records ||

12245

12225

if (!join->having || join->having->val_int())

12246

12226

{

12247

12227

int error= table->file->ha_write_row(table->record[0]);

12248

if (error && create_myisam_from_heap(join->thd, table,

12228

if (error && create_myisam_from_heap(join->session, table,

12249

12229

join->tmp_table_param.start_recinfo,

12250

12230

&join->tmp_table_param.recinfo,

12251

12231

error, 0))

12253

12233

}

12254

12234

if (join->rollup.state != ROLLUP::STATE_NONE)

12255

12235

{

12256

if (join->rollup_write_data((uint) (idx+1), table))

12236

if (join->rollup_write_data((uint32_t) (idx+1), table))

12257

12237

return(NESTED_LOOP_ERROR);

12258

12238

}

12259

12239

if (end_of_records)

12343

12323

12344

12324

@note Because of current requirements for semijoin flattening, we do not

12345

12325

need to recurse here, hence this function will only examine the top-level

12346

AND conditions. (see JOIN::prepare, comment above the line

12326

AND conditions. (see JOIN::prepare, comment above the line

12347

12327

'if (do_materialize)'

12348

12328

12349

12329

@param join The top-level query.

12350

12330

@param old_cond The expression to be replaced.

12351

12331

@param new_cond The expression to be substituted.

12352

@param do_fix_fields If true, Item::fix_fields(THD*, Item**) is called for

12332

@param do_fix_fields If true, Item::fix_fields(Session*, Item**) is called for

12353

12333

the new expression.

12354

12334

@return <code>true</code> if there was an error, <code>false</code> if

12355

12335

successful.

12356

12336

12357

static bool replace_where_subcondition(JOIN *join, Item *old_cond,

12337

static bool replace_where_subcondition(JOIN *join, Item *old_cond,

12358

12338

Item *new_cond, bool do_fix_fields)

12359

12339

{

12360

12340

if (join->conds == old_cond) {

12361

12341

join->conds= new_cond;

12362

12342

if (do_fix_fields)

12363

new_cond->fix_fields(join->thd, &join->conds);

12343

new_cond->fix_fields(join->session, &join->conds);

12364

12344

return false;

12365

12345

}

12366

12346

12367

12347

if (join->conds->type() == Item::COND_ITEM) {

12368

12348

List_iterator<Item> li(*((Item_cond*)join->conds)->argument_list());

12369

12349

Item *item;

12370

12350

while ((item= li++))

12371

if (item == old_cond)

12351

if (item == old_cond)

12372

12352

{

12373

12353

li.replace(new_cond);

12374

12354

if (do_fix_fields)

12375

new_cond->fix_fields(join->thd, li.ref());

12355

new_cond->fix_fields(join->session, li.ref());

12376

12356

return false;

12377

12357

}

12378

12358

}

12382

12362

12383

12363

12384

12364

Extract a condition that can be checked after reading given table

12385

12365

12386

12366

SYNOPSIS

12387

12367

make_cond_for_table()

12388

12368

cond Condition to analyze

12389

12369

tables Tables for which "current field values" are available

12390

used_table Table that we're extracting the condition for (may

12370

used_table Table that we're extracting the condition for (may

12391

12371

also include PSEUDO_TABLE_BITS

12392

12372

12393

12373

DESCRIPTION

12397

12377

12398

12378

The function assumes that

12399

12379

- Constant parts of the condition has already been checked.

12400

- Condition that could be checked for tables in 'tables' has already

12380

- Condition that could be checked for tables in 'tables' has already

12401

12381

been checked.

12402

12382

12403

12383

The function takes into account that some parts of the condition are

12404

12384

guaranteed to be true by employed 'ref' access methods (the code that

12405

12385

does this is located at the end, search down for "EQ_FUNC").

12406

12386

12407

12387

12408