sql_update.cc 45.3 KB
Newer Older
unknown's avatar
unknown committed
1
/* Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
2

unknown's avatar
unknown committed
3 4 5 6
   This program is free software; you can redistribute it and/or modify
   it under the terms of the GNU General Public License as published by
   the Free Software Foundation; either version 2 of the License, or
   (at your option) any later version.
7

unknown's avatar
unknown committed
8 9 10 11
   This program is distributed in the hope that it will be useful,
   but WITHOUT ANY WARRANTY; without even the implied warranty of
   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
   GNU General Public License for more details.
12

unknown's avatar
unknown committed
13 14 15 16 17
   You should have received a copy of the GNU General Public License
   along with this program; if not, write to the Free Software
   Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA */


18 19 20
/*
  Single table and multi table updates of tables.
  Multi-table updates were introduced by Sinisa & Monty
21
*/
unknown's avatar
unknown committed
22 23

#include "mysql_priv.h"
24
#include "sql_select.h"
25 26
#include "sp_head.h"
#include "sql_trigger.h"
unknown's avatar
unknown committed
27

28 29
static bool safe_update_on_fly(JOIN_TAB *join_tab, List<Item> *fields);

unknown's avatar
unknown committed
30 31
/* Return 0 if row hasn't changed */

32
static bool compare_record(TABLE *table, query_id_t query_id)
unknown's avatar
unknown committed
33
{
34
  if (table->s->blob_fields + table->s->varchar_fields == 0)
unknown's avatar
unknown committed
35
    return cmp_record(table,record[1]);
unknown's avatar
unknown committed
36
  /* Compare null bits */
unknown's avatar
unknown committed
37
  if (memcmp(table->null_flags,
38 39
	     table->null_flags+table->s->rec_buff_length,
	     table->s->null_bytes))
40
    return TRUE;				// Diff in NULL value
unknown's avatar
unknown committed
41
  /* Compare updated fields */
unknown's avatar
unknown committed
42 43
  for (Field **ptr=table->field ; *ptr ; ptr++)
  {
44
    if ((*ptr)->query_id == query_id &&
45
	(*ptr)->cmp_binary_offset(table->s->rec_buff_length))
46
      return TRUE;
unknown's avatar
unknown committed
47
  }
48
  return FALSE;
unknown's avatar
unknown committed
49 50 51
}


unknown's avatar
VIEW  
unknown committed
52 53 54 55 56
/*
  check that all fields are real fields

  SYNOPSIS
    check_fields()
unknown's avatar
unknown committed
57
    thd             thread handler
unknown's avatar
VIEW  
unknown committed
58 59 60 61 62 63 64
    items           Items for check

  RETURN
    TRUE  Items can't be used in UPDATE
    FALSE Items are OK
*/

unknown's avatar
unknown committed
65
static bool check_fields(THD *thd, List<Item> &items)
unknown's avatar
VIEW  
unknown committed
66
{
unknown's avatar
unknown committed
67
  List_iterator<Item> it(items);
unknown's avatar
VIEW  
unknown committed
68
  Item *item;
69
  Item_field *field;
70 71
  Name_resolution_context *context= &thd->lex->select_lex.context;

unknown's avatar
VIEW  
unknown committed
72 73
  while ((item= it++))
  {
74
    if (!(field= item->filed_for_view_update()))
unknown's avatar
VIEW  
unknown committed
75
    {
76
      /* item has name, because it comes from VIEW SELECT list */
77
      my_error(ER_NONUPDATEABLE_COLUMN, MYF(0), item->name);
unknown's avatar
VIEW  
unknown committed
78 79
      return TRUE;
    }
unknown's avatar
unknown committed
80 81 82 83
    /*
      we make temporary copy of Item_field, to avoid influence of changing
      result_field on Item_ref which refer on this field
    */
unknown's avatar
unknown committed
84
    thd->change_item_tree(it.ref(), new Item_field(thd, field));
unknown's avatar
VIEW  
unknown committed
85 86 87 88 89
  }
  return FALSE;
}


90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107
/*
  Process usual UPDATE

  SYNOPSIS
    mysql_update()
    thd			thread handler
    fields		fields for update
    values		values of fields for update
    conds		WHERE clause expression
    order_num		number of elemen in ORDER BY clause
    order		ORDER BY clause list
    limit		limit clause
    handle_duplicates	how to handle duplicates

  RETURN
    0  - OK
    2  - privilege check and openning table passed, but we need to convert to
         multi-update because of view substitution
unknown's avatar
merge  
unknown committed
108
    1  - error
109 110
*/

111 112 113 114 115
int mysql_update(THD *thd,
                 TABLE_LIST *table_list,
                 List<Item> &fields,
		 List<Item> &values,
                 COND *conds,
116
                 uint order_num, ORDER *order,
unknown's avatar
unknown committed
117
		 ha_rows limit,
118
		 enum enum_duplicates handle_duplicates, bool ignore)
unknown's avatar
unknown committed
119
{
unknown's avatar
unknown committed
120
  bool		using_limit= limit != HA_POS_ERROR;
unknown's avatar
unknown committed
121
  bool		safe_update= thd->options & OPTION_SAFE_UPDATES;
unknown's avatar
unknown committed
122
  bool		used_key_is_modified, transactional_table;
123
  bool		can_compare_record;
unknown's avatar
unknown committed
124
  int           res;
unknown's avatar
unknown committed
125
  int		error;
126 127
  uint		used_index= MAX_KEY;
  bool          need_sort= TRUE;
128 129 130
#ifndef NO_EMBEDDED_ACCESS_CHECKS
  uint		want_privilege;
#endif
unknown's avatar
unknown committed
131
  uint          table_count= 0;
132
  query_id_t	query_id=thd->query_id, timestamp_query_id;
133
  ha_rows	updated, found;
unknown's avatar
unknown committed
134 135
  key_map	old_used_keys;
  TABLE		*table;
unknown's avatar
unknown committed
136
  SQL_SELECT	*select;
unknown's avatar
unknown committed
137
  READ_RECORD	info;
138
  SELECT_LEX    *select_lex= &thd->lex->select_lex;
139
  bool need_reopen;
unknown's avatar
unknown committed
140
  DBUG_ENTER("mysql_update");
unknown's avatar
unknown committed
141

unknown's avatar
unknown committed
142
  LINT_INIT(timestamp_query_id);
unknown's avatar
unknown committed
143

144
  for ( ; ; )
145
  {
146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161
    if (open_tables(thd, &table_list, &table_count, 0))
      DBUG_RETURN(1);

    if (table_list->multitable_view)
    {
      DBUG_ASSERT(table_list->view != 0);
      DBUG_PRINT("info", ("Switch to multi-update"));
      /* pass counter value */
      thd->lex->table_count= table_count;
      /* convert to multiupdate */
      DBUG_RETURN(2);
    }
    if (!lock_tables(thd, table_list, table_count, &need_reopen))
      break;
    if (!need_reopen)
      DBUG_RETURN(1);
162
    close_tables_for_reopen(thd, &table_list);
163
  }
unknown's avatar
unknown committed
164

165
  if (mysql_handle_derived(thd->lex, &mysql_derived_prepare) ||
unknown's avatar
unknown committed
166 167
      (thd->fill_derived_tables() &&
       mysql_handle_derived(thd->lex, &mysql_derived_filling)))
unknown's avatar
merge  
unknown committed
168
    DBUG_RETURN(1);
unknown's avatar
unknown committed
169

unknown's avatar
unknown committed
170
  thd->proc_info="init";
unknown's avatar
unknown committed
171
  table= table_list->table;
unknown's avatar
unknown committed
172 173
  table->file->info(HA_STATUS_VARIABLE | HA_STATUS_NO_LOCK);

174
  /* Calculate "table->used_keys" based on the WHERE */
175
  table->used_keys= table->s->keys_in_use;
176
  table->quick_keys.clear_all();
unknown's avatar
unknown committed
177

unknown's avatar
unknown committed
178
#ifndef NO_EMBEDDED_ACCESS_CHECKS
179 180
  /* TABLE_LIST contain right privilages request */
  want_privilege= table_list->grant.want_privilege;
unknown's avatar
unknown committed
181
#endif
182
  if (mysql_prepare_update(thd, table_list, &conds, order_num, order))
unknown's avatar
merge  
unknown committed
183
    DBUG_RETURN(1);
184

unknown's avatar
unknown committed
185
  old_used_keys= table->used_keys;		// Keys used in WHERE
unknown's avatar
unknown committed
186
  /*
187 188
    Change the query_id for the timestamp column so that we can
    check if this is modified directly
unknown's avatar
unknown committed
189
  */
190 191 192 193 194
  if (table->timestamp_field)
  {
    timestamp_query_id=table->timestamp_field->query_id;
    table->timestamp_field->query_id=thd->query_id-1;
  }
unknown's avatar
unknown committed
195

196
  /* Check the fields we are going to modify */
unknown's avatar
unknown committed
197
#ifndef NO_EMBEDDED_ACCESS_CHECKS
unknown's avatar
VIEW  
unknown committed
198
  table_list->grant.want_privilege= table->grant.want_privilege= want_privilege;
199
  table_list->register_want_access(want_privilege);
unknown's avatar
unknown committed
200
#endif
201 202
  if (setup_fields_with_no_wrap(thd, 0, fields, 1, 0, 0))
    DBUG_RETURN(1);                     /* purecov: inspected */
unknown's avatar
unknown committed
203
  if (table_list->view && check_fields(thd, fields))
unknown's avatar
VIEW  
unknown committed
204
  {
unknown's avatar
merge  
unknown committed
205
    DBUG_RETURN(1);
unknown's avatar
VIEW  
unknown committed
206 207 208
  }
  if (!table_list->updatable || check_key_in_view(thd, table_list))
  {
209
    my_error(ER_NON_UPDATABLE_TABLE, MYF(0), table_list->alias, "UPDATE");
unknown's avatar
merge  
unknown committed
210
    DBUG_RETURN(1);
unknown's avatar
VIEW  
unknown committed
211
  }
212 213 214 215
  if (table->timestamp_field)
  {
    // Don't set timestamp column if this is modified
    if (table->timestamp_field->query_id == thd->query_id)
216
      table->timestamp_field_type= TIMESTAMP_NO_AUTO_SET;
217 218 219
    else
      table->timestamp_field->query_id=timestamp_query_id;
  }
220

unknown's avatar
unknown committed
221
#ifndef NO_EMBEDDED_ACCESS_CHECKS
222
  /* Check values */
unknown's avatar
VIEW  
unknown committed
223
  table_list->grant.want_privilege= table->grant.want_privilege=
unknown's avatar
unknown committed
224
    (SELECT_ACL & ~table->grant.privilege);
unknown's avatar
unknown committed
225
#endif
226
  if (setup_fields(thd, 0, values, 1, 0, 0))
unknown's avatar
unknown committed
227
  {
228
    free_underlaid_joins(thd, select_lex);
unknown's avatar
merge  
unknown committed
229
    DBUG_RETURN(1);				/* purecov: inspected */
unknown's avatar
unknown committed
230
  }
unknown's avatar
unknown committed
231

232 233 234 235 236 237 238
  if (conds)
  {
    Item::cond_result cond_value;
    conds= remove_eq_conds(thd, conds, &cond_value);
    if (cond_value == Item::COND_FALSE)
      limit= 0;                                   // Impossible WHERE
  }
239
  // Don't count on usage of 'only index' when calculating which key to use
240
  table->used_keys.clear_all();
unknown's avatar
unknown committed
241
  select= make_select(table, 0, 0, conds, 0, &error);
242 243
  if (error || !limit ||
      (select && select->check_quick(thd, safe_update, limit)))
unknown's avatar
unknown committed
244 245
  {
    delete select;
246
    free_underlaid_joins(thd, select_lex);
unknown's avatar
unknown committed
247 248
    if (error)
    {
unknown's avatar
merge  
unknown committed
249
      DBUG_RETURN(1);				// Error in where
unknown's avatar
unknown committed
250
    }
251
    send_ok(thd);				// No matching records
unknown's avatar
unknown committed
252 253
    DBUG_RETURN(0);
  }
254 255
  if (!select && limit != HA_POS_ERROR)
  {
256
    if ((used_index= get_index_for_order(table, order, limit)) != MAX_KEY)
257 258
      need_sort= FALSE;
  }
unknown's avatar
unknown committed
259
  /* If running in safe sql mode, don't allow updates without keys */
260
  if (table->quick_keys.is_clear_all())
unknown's avatar
unknown committed
261
  {
262
    thd->server_status|=SERVER_QUERY_NO_INDEX_USED;
unknown's avatar
unknown committed
263
    if (safe_update && !using_limit)
264
    {
unknown's avatar
unknown committed
265 266 267
      my_message(ER_UPDATE_WITHOUT_KEY_IN_SAFE_MODE,
		 ER(ER_UPDATE_WITHOUT_KEY_IN_SAFE_MODE), MYF(0));
      goto err;
268
    }
unknown's avatar
unknown committed
269
  }
270
  init_ftfuncs(thd, select_lex, 1);
unknown's avatar
unknown committed
271
  /* Check if we are modifying a key that we are used to search with */
272
  
unknown's avatar
unknown committed
273
  if (select && select->quick)
unknown's avatar
unknown committed
274
  {
275
    used_index= select->quick->index;
276 277
    used_key_is_modified= (!select->quick->unique_key_range() &&
                          select->quick->check_if_keys_used(&fields));
unknown's avatar
unknown committed
278
  }
279
  else
280
  {
281 282 283 284 285
    used_key_is_modified= 0;
    if (used_index == MAX_KEY)                  // no index for sort order
      used_index= table->file->key_used_on_scan;
    if (used_index != MAX_KEY)
      used_key_is_modified= check_if_key_used(table, used_index, fields);
286
  }
287

unknown's avatar
unknown committed
288
  if (used_key_is_modified || order)
unknown's avatar
unknown committed
289 290
  {
    /*
unknown's avatar
unknown committed
291 292
      We can't update table directly;  We must first search after all
      matching rows before updating the table!
unknown's avatar
unknown committed
293
    */
unknown's avatar
unknown committed
294
    table->file->extra(HA_EXTRA_RETRIEVE_ALL_COLS);
295
    if (used_index < MAX_KEY && old_used_keys.is_set(used_index))
unknown's avatar
unknown committed
296 297
    {
      table->key_read=1;
298
      table->file->extra(HA_EXTRA_KEYREAD);
unknown's avatar
unknown committed
299
    }
300

301
    /* note: can actually avoid sorting below.. */
302
    if (order && (need_sort || used_key_is_modified))
303
    {
unknown's avatar
unknown committed
304 305 306 307
      /*
	Doing an ORDER BY;  Let filesort find and sort the rows we are going
	to update
      */
308 309
      uint         length;
      SORT_FIELD  *sortorder;
310
      ha_rows examined_rows;
311

unknown's avatar
unknown committed
312
      table->sort.io_cache = (IO_CACHE *) my_malloc(sizeof(IO_CACHE),
unknown's avatar
unknown committed
313
						    MYF(MY_FAE | MY_ZEROFILL));
314
      if (!(sortorder=make_unireg_sortorder(order, &length, NULL)) ||
unknown's avatar
unknown committed
315
          (table->sort.found_records = filesort(thd, table, sortorder, length,
unknown's avatar
unknown committed
316
						select, limit,
unknown's avatar
unknown committed
317
						&examined_rows))
318 319
          == HA_POS_ERROR)
      {
unknown's avatar
unknown committed
320
	free_io_cache(table);
321
	goto err;
322
      }
unknown's avatar
unknown committed
323 324 325 326 327 328
      /*
	Filesort has already found and selected the rows we want to update,
	so we don't need the where clause
      */
      delete select;
      select= 0;
329
    }
unknown's avatar
unknown committed
330
    else
unknown's avatar
unknown committed
331
    {
unknown's avatar
unknown committed
332 333 334 335 336 337 338 339 340
      /*
	We are doing a search on a key that is updated. In this case
	we go trough the matching rows, save a pointer to them and
	update these in a separate loop based on the pointer.
      */

      IO_CACHE tempfile;
      if (open_cached_file(&tempfile, mysql_tmpdir,TEMP_PREFIX,
			   DISK_BUFFER_SIZE, MYF(MY_WME)))
341
	goto err;
unknown's avatar
unknown committed
342

unknown's avatar
unknown committed
343 344 345
      /* If quick select is used, initialize it before retrieving rows. */
      if (select && select->quick && select->quick->reset())
        goto err;
346

347 348 349 350 351 352 353 354 355 356
      /*
        When we get here, we have one of the following options:
        A. used_index == MAX_KEY
           This means we should use full table scan, and start it with
           init_read_record call
        B. used_index != MAX_KEY
           B.1 quick select is used, start the scan with init_read_record
           B.2 quick select is not used, this is full index scan (with LIMIT)
               Full index scan must be started with init_read_record_idx
      */
357
      if (used_index == MAX_KEY || (select && select->quick))
358 359 360
        init_read_record(&info,thd,table,select,0,1);
      else
        init_read_record_idx(&info, thd, table, 1, used_index);
unknown's avatar
VIEW  
unknown committed
361

unknown's avatar
unknown committed
362 363
      thd->proc_info="Searching rows for update";
      uint tmp_limit= limit;
unknown's avatar
unknown committed
364

unknown's avatar
unknown committed
365
      while (!(error=info.read_record(&info)) && !thd->killed)
unknown's avatar
unknown committed
366
      {
367
	if (!(select && select->skip_record()))
unknown's avatar
unknown committed
368
	{
unknown's avatar
unknown committed
369 370 371 372 373 374 375 376
	  table->file->position(table->record[0]);
	  if (my_b_write(&tempfile,table->file->ref,
			 table->file->ref_length))
	  {
	    error=1; /* purecov: inspected */
	    break; /* purecov: inspected */
	  }
	  if (!--limit && using_limit)
377 378
	  {
	    error= -1;
unknown's avatar
unknown committed
379
	    break;
380
	  }
unknown's avatar
unknown committed
381
	}
unknown's avatar
unknown committed
382 383
	else
	  table->file->unlock_row();
unknown's avatar
unknown committed
384
      }
385 386
      if (thd->killed && !error)
	error= 1;				// Aborted
387
      limit= tmp_limit;
unknown's avatar
unknown committed
388
      end_read_record(&info);
389
     
unknown's avatar
unknown committed
390 391 392 393 394 395 396 397 398
      /* Change select to use tempfile */
      if (select)
      {
	delete select->quick;
	if (select->free_cond)
	  delete select->cond;
	select->quick=0;
	select->cond=0;
      }
unknown's avatar
unknown committed
399 400
      else
      {
unknown's avatar
unknown committed
401 402
	select= new SQL_SELECT;
	select->head=table;
unknown's avatar
unknown committed
403
      }
unknown's avatar
unknown committed
404 405 406 407
      if (reinit_io_cache(&tempfile,READ_CACHE,0L,0,0))
	error=1; /* purecov: inspected */
      select->file=tempfile;			// Read row ptrs from this file
      if (error >= 0)
408
	goto err;
unknown's avatar
unknown committed
409 410 411 412 413 414 415 416
    }
    if (table->key_read)
    {
      table->key_read=0;
      table->file->extra(HA_EXTRA_NO_KEYREAD);
    }
  }

417
  if (ignore)
unknown's avatar
unknown committed
418
    table->file->extra(HA_EXTRA_IGNORE_DUP_KEY);
419 420 421
  
  if (select && select->quick && select->quick->reset())
        goto err;
unknown's avatar
unknown committed
422 423
  init_read_record(&info,thd,table,select,0,1);

424
  updated= found= 0;
425
  thd->count_cuted_fields= CHECK_FIELD_WARN;		/* calc cuted fields */
unknown's avatar
unknown committed
426
  thd->cuted_fields=0L;
427
  thd->proc_info="Updating";
428
  query_id=thd->query_id;
unknown's avatar
unknown committed
429

unknown's avatar
unknown committed
430 431
  transactional_table= table->file->has_transactions();
  thd->no_trans_update= 0;
unknown's avatar
unknown committed
432
  thd->abort_on_warning= test(!ignore &&
unknown's avatar
unknown committed
433 434 435
                              (thd->variables.sql_mode &
                               (MODE_STRICT_TRANS_TABLES |
                                MODE_STRICT_ALL_TABLES)));
unknown's avatar
unknown committed
436

437 438 439
  if (table->triggers)
    table->triggers->mark_fields_used(thd, TRG_EVENT_UPDATE);

440 441 442 443 444 445
  /*
    We can use compare_record() to optimize away updates if
    the table handler is returning all columns
  */
  can_compare_record= !(table->file->table_flags() &
                        HA_PARTIAL_COLUMN_READ);
unknown's avatar
unknown committed
446 447
  while (!(error=info.read_record(&info)) && !thd->killed)
  {
448
    if (!(select && select->skip_record()))
unknown's avatar
unknown committed
449
    {
unknown's avatar
unknown committed
450
      store_record(table,record[1]);
unknown's avatar
unknown committed
451 452 453
      if (fill_record_n_invoke_before_triggers(thd, fields, values, 0,
                                               table->triggers,
                                               TRG_EVENT_UPDATE))
unknown's avatar
unknown committed
454
	break; /* purecov: inspected */
unknown's avatar
unknown committed
455

unknown's avatar
unknown committed
456
      found++;
457

458
      if (!can_compare_record || compare_record(table, query_id))
unknown's avatar
unknown committed
459
      {
unknown's avatar
unknown committed
460
        if ((res= table_list->view_check_option(thd, ignore)) !=
unknown's avatar
unknown committed
461 462 463 464 465 466 467 468 469 470 471
            VIEW_CHECK_OK)
        {
          found--;
          if (res == VIEW_CHECK_SKIP)
            continue;
          else if (res == VIEW_CHECK_ERROR)
          {
            error= 1;
            break;
          }
        }
unknown's avatar
unknown committed
472 473 474 475
	if (!(error=table->file->update_row((byte*) table->record[1],
					    (byte*) table->record[0])))
	{
	  updated++;
unknown's avatar
unknown committed
476
          thd->no_trans_update= !transactional_table;
unknown's avatar
unknown committed
477 478 479 480 481 482 483 484

          if (table->triggers &&
              table->triggers->process_triggers(thd, TRG_EVENT_UPDATE,
                                                TRG_ACTION_AFTER, TRUE))
          {
            error= 1;
            break;
          }
unknown's avatar
unknown committed
485
	}
486
 	else if (!ignore || error != HA_ERR_FOUND_DUPP_KEY)
unknown's avatar
unknown committed
487
	{
488 489 490 491 492 493
          /*
            If (ignore && error == HA_ERR_FOUND_DUPP_KEY) we don't have to
            do anything; otherwise...
          */
          if (error != HA_ERR_FOUND_DUPP_KEY)
            thd->fatal_error(); /* Other handler errors are fatal */
unknown's avatar
unknown committed
494 495 496 497 498
	  table->file->print_error(error,MYF(0));
	  error= 1;
	  break;
	}
      }
499

unknown's avatar
unknown committed
500 501 502 503 504
      if (!--limit && using_limit)
      {
	error= -1;				// Simulate end of file
	break;
      }
unknown's avatar
unknown committed
505
    }
unknown's avatar
unknown committed
506 507
    else
      table->file->unlock_row();
508
    thd->row_count++;
unknown's avatar
unknown committed
509
  }
510 511
  if (thd->killed && !error)
    error= 1;					// Aborted
unknown's avatar
unknown committed
512
  end_read_record(&info);
unknown's avatar
unknown committed
513
  free_io_cache(table);				// If ORDER BY
unknown's avatar
unknown committed
514
  delete select;
unknown's avatar
unknown committed
515
  thd->proc_info="end";
unknown's avatar
unknown committed
516
  VOID(table->file->extra(HA_EXTRA_NO_IGNORE_DUP_KEY));
unknown's avatar
unknown committed
517 518 519 520 521 522

  /*
    Invalidate the table in the query cache if something changed.
    This must be before binlog writing and ha_autocommit_...
  */
  if (updated)
unknown's avatar
unknown committed
523
  {
unknown's avatar
unknown committed
524
    query_cache_invalidate3(thd, table_list, 1);
unknown's avatar
unknown committed
525
  }
unknown's avatar
unknown committed
526

527 528 529 530 531 532 533 534 535 536
  /*
    error < 0 means really no error at all: we processed all rows until the
    last one without error. error > 0 means an error (e.g. unique key
    violation and no IGNORE or REPLACE). error == 0 is also an error (if
    preparing the record or invoking before triggers fails). See
    ha_autocommit_or_rollback(error>=0) and DBUG_RETURN(error>=0) below.
    Sometimes we want to binlog even if we updated no rows, in case user used
    it to be sure master and slave are in same state.
  */
  if ((error < 0) || (updated && !transactional_table))
unknown's avatar
unknown committed
537
  {
538 539
    if (mysql_bin_log.is_open())
    {
540
      if (error < 0)
unknown's avatar
unknown committed
541
        thd->clear_error();
542
      Query_log_event qinfo(thd, thd->query, thd->query_length,
unknown's avatar
unknown committed
543
			    transactional_table, FALSE);
544 545
      if (mysql_bin_log.write(&qinfo) && transactional_table)
	error=1;				// Rollback update
546
    }
unknown's avatar
unknown committed
547
    if (!transactional_table)
548
      thd->options|=OPTION_STATUS_NO_TRANS_UPDATE;
unknown's avatar
unknown committed
549
  }
550
  free_underlaid_joins(thd, select_lex);
unknown's avatar
unknown committed
551 552 553 554 555
  if (transactional_table)
  {
    if (ha_autocommit_or_rollback(thd, error >= 0))
      error=1;
  }
unknown's avatar
unknown committed
556

557 558 559 560 561 562
  if (thd->lock)
  {
    mysql_unlock_tables(thd, thd->lock);
    thd->lock=0;
  }

unknown's avatar
unknown committed
563
  if (error < 0)
unknown's avatar
unknown committed
564
  {
unknown's avatar
unknown committed
565
    char buff[STRING_BUFFER_USUAL_SIZE];
566 567
    sprintf(buff, ER(ER_UPDATE_INFO), (ulong) found, (ulong) updated,
	    (ulong) thd->cuted_fields);
568 569
    thd->row_count_func=
      (thd->client_capabilities & CLIENT_FOUND_ROWS) ? found : updated;
570
    send_ok(thd, (ulong) thd->row_count_func,
unknown's avatar
unknown committed
571 572 573
	    thd->insert_id_used ? thd->insert_id() : 0L,buff);
    DBUG_PRINT("info",("%d records updated",updated));
  }
574
  thd->count_cuted_fields= CHECK_FIELD_IGNORE;		/* calc cuted fields */
575
  thd->abort_on_warning= 0;
unknown's avatar
unknown committed
576
  free_io_cache(table);
unknown's avatar
merge  
unknown committed
577
  DBUG_RETURN((error >= 0 || thd->net.report_error) ? 1 : 0);
578 579 580

err:
  delete select;
581
  free_underlaid_joins(thd, select_lex);
582 583 584 585 586
  if (table->key_read)
  {
    table->key_read=0;
    table->file->extra(HA_EXTRA_NO_KEYREAD);
  }
unknown's avatar
unknown committed
587
  thd->abort_on_warning= 0;
unknown's avatar
merge  
unknown committed
588
  DBUG_RETURN(1);
unknown's avatar
unknown committed
589
}
590

unknown's avatar
unknown committed
591 592 593 594 595 596
/*
  Prepare items in UPDATE statement

  SYNOPSIS
    mysql_prepare_update()
    thd			- thread handler
unknown's avatar
VIEW  
unknown committed
597
    table_list		- global/local table list
unknown's avatar
unknown committed
598 599 600 601 602
    conds		- conditions
    order_num		- number of ORDER BY list entries
    order		- ORDER BY clause list

  RETURN VALUE
unknown's avatar
unknown committed
603 604
    FALSE OK
    TRUE  error
unknown's avatar
unknown committed
605
*/
unknown's avatar
unknown committed
606
bool mysql_prepare_update(THD *thd, TABLE_LIST *table_list,
unknown's avatar
unknown committed
607 608 609 610 611
			 Item **conds, uint order_num, ORDER *order)
{
  TABLE *table= table_list->table;
  TABLE_LIST tables;
  List<Item> all_fields;
612
  SELECT_LEX *select_lex= &thd->lex->select_lex;
unknown's avatar
unknown committed
613 614 615
  DBUG_ENTER("mysql_prepare_update");

#ifndef NO_EMBEDDED_ACCESS_CHECKS
unknown's avatar
VIEW  
unknown committed
616 617
  table_list->grant.want_privilege= table->grant.want_privilege= 
    (SELECT_ACL & ~table->grant.privilege);
618
  table_list->register_want_access(SELECT_ACL);
unknown's avatar
unknown committed
619 620 621 622 623
#endif

  bzero((char*) &tables,sizeof(tables));	// For ORDER BY
  tables.table= table;
  tables.alias= table_list->alias;
unknown's avatar
unknown committed
624
  thd->lex->allow_sum_func= 0;
unknown's avatar
unknown committed
625

626 627 628 629
  if (setup_tables_and_check_access(thd, &select_lex->context, 
                                    &select_lex->top_join_list,
                                    table_list, conds, 
                                    &select_lex->leaf_tables,
630
                                    FALSE, UPDATE_ACL, SELECT_ACL) ||
631
      setup_conds(thd, table_list, select_lex->leaf_tables, conds) ||
632 633
      select_lex->setup_ref_array(thd, order_num) ||
      setup_order(thd, select_lex->ref_pointer_array,
unknown's avatar
VIEW  
unknown committed
634
		  table_list, all_fields, all_fields, order) ||
635
      setup_ftfuncs(select_lex))
unknown's avatar
unknown committed
636
    DBUG_RETURN(TRUE);
unknown's avatar
unknown committed
637 638 639

  /* Check that we are not using table that we are updating in a sub select */
  {
640
    TABLE_LIST *duplicate;
641
    if ((duplicate= unique_table(thd, table_list, table_list->next_global)))
642 643 644 645 646
    {
      update_non_unique_table_error(table_list, "UPDATE", duplicate);
      my_error(ER_UPDATE_TABLE_USED, MYF(0), table_list->table_name);
      DBUG_RETURN(TRUE);
    }
unknown's avatar
unknown committed
647
  }
unknown's avatar
VIEW  
unknown committed
648
  select_lex->fix_prepare_information(thd, conds);
unknown's avatar
unknown committed
649
  DBUG_RETURN(FALSE);
unknown's avatar
unknown committed
650 651
}

unknown's avatar
unknown committed
652

653
/***************************************************************************
654
  Update multiple tables from join 
655 656
***************************************************************************/

unknown's avatar
unknown committed
657
/*
658
  Get table map for list of Item_field
unknown's avatar
unknown committed
659 660
*/

661 662 663 664 665 666 667 668 669 670 671 672 673
static table_map get_table_map(List<Item> *items)
{
  List_iterator_fast<Item> item_it(*items);
  Item_field *item;
  table_map map= 0;

  while ((item= (Item_field *) item_it++)) 
    map|= item->used_tables();
  DBUG_PRINT("info",("table_map: 0x%08x", map));
  return map;
}


unknown's avatar
VIEW  
unknown committed
674 675
/*
  make update specific preparation and checks after opening tables
unknown's avatar
unknown committed
676

unknown's avatar
VIEW  
unknown committed
677 678 679
  SYNOPSIS
    mysql_multi_update_prepare()
    thd         thread handler
unknown's avatar
unknown committed
680

unknown's avatar
VIEW  
unknown committed
681
  RETURN
unknown's avatar
unknown committed
682 683
    FALSE OK
    TRUE  Error
unknown's avatar
VIEW  
unknown committed
684
*/
685

unknown's avatar
unknown committed
686
bool mysql_multi_update_prepare(THD *thd)
unknown's avatar
VIEW  
unknown committed
687 688 689
{
  LEX *lex= thd->lex;
  TABLE_LIST *table_list= lex->query_tables;
unknown's avatar
unknown committed
690
  TABLE_LIST *tl, *leaves;
unknown's avatar
VIEW  
unknown committed
691
  List<Item> *fields= &lex->select_lex.item_list;
unknown's avatar
unknown committed
692
  table_map tables_for_update;
unknown's avatar
unknown committed
693
  bool update_view= 0;
unknown's avatar
unknown committed
694 695 696 697 698 699
  /*
    if this multi-update was converted from usual update, here is table
    counter else junk will be assigned here, but then replaced with real
    count in open_tables()
  */
  uint  table_count= lex->table_count;
unknown's avatar
unknown committed
700
  const bool using_lock_tables= thd->locked_tables != 0;
unknown's avatar
unknown committed
701
  bool original_multiupdate= (thd->lex->sql_command == SQLCOM_UPDATE_MULTI);
702
  bool need_reopen= FALSE;
unknown's avatar
unknown committed
703 704
  DBUG_ENTER("mysql_multi_update_prepare");

unknown's avatar
unknown committed
705 706
  /* following need for prepared statements, to run next time multi-update */
  thd->lex->sql_command= SQLCOM_UPDATE_MULTI;
unknown's avatar
unknown committed
707

708 709
reopen_tables:

unknown's avatar
unknown committed
710
  /* open tables and create derived ones, but do not lock and fill them */
711
  if (((original_multiupdate || need_reopen) &&
712
       open_tables(thd, &table_list, &table_count, 0)) ||
unknown's avatar
unknown committed
713
      mysql_handle_derived(lex, &mysql_derived_prepare))
714
    DBUG_RETURN(TRUE);
unknown's avatar
unknown committed
715 716 717 718 719
  /*
    setup_tables() need for VIEWs. JOIN::prepare() will call setup_tables()
    second time, but this call will do nothing (there are check for second
    call in setup_tables()).
  */
unknown's avatar
merge  
unknown committed
720

721 722 723 724
  if (setup_tables_and_check_access(thd, &lex->select_lex.context,
                                    &lex->select_lex.top_join_list,
                                    table_list, &lex->select_lex.where,
                                    &lex->select_lex.leaf_tables, FALSE,
725
                                    UPDATE_ACL, SELECT_ACL))
unknown's avatar
merge  
unknown committed
726
    DBUG_RETURN(TRUE);
727

728
  if (setup_fields_with_no_wrap(thd, 0, *fields, 1, 0, 0))
unknown's avatar
unknown committed
729
    DBUG_RETURN(TRUE);
unknown's avatar
unknown committed
730 731 732 733 734 735 736 737 738 739 740

  for (tl= table_list; tl ; tl= tl->next_local)
  {
    if (tl->view)
    {
      update_view= 1;
      break;
    }
  }

  if (update_view && check_fields(thd, *fields))
741
  {
unknown's avatar
unknown committed
742
    DBUG_RETURN(TRUE);
unknown's avatar
VIEW  
unknown committed
743 744
  }

unknown's avatar
unknown committed
745
  tables_for_update= get_table_map(fields);
unknown's avatar
unknown committed
746 747

  /*
unknown's avatar
unknown committed
748
    Setup timestamp handling and locking mode
unknown's avatar
unknown committed
749
  */
750
  leaves= lex->select_lex.leaf_tables;
751
  for (tl= leaves; tl; tl= tl->next_leaf)
752
  {
unknown's avatar
unknown committed
753
    TABLE *table= tl->table;
unknown's avatar
unknown committed
754
    /* Only set timestamp column if this is not modified */
755 756
    if (table->timestamp_field &&
        table->timestamp_field->query_id == thd->query_id)
unknown's avatar
unknown committed
757
      table->timestamp_field_type= TIMESTAMP_NO_AUTO_SET;
unknown's avatar
VIEW  
unknown committed
758

unknown's avatar
unknown committed
759 760
    /* if table will be updated then check that it is unique */
    if (table->map & tables_for_update)
761
    {
unknown's avatar
unknown committed
762
      if (!tl->updatable || check_key_in_view(thd, tl))
unknown's avatar
unknown committed
763
      {
764
        my_error(ER_NON_UPDATABLE_TABLE, MYF(0), tl->alias, "UPDATE");
unknown's avatar
merge  
unknown committed
765
        DBUG_RETURN(TRUE);
unknown's avatar
unknown committed
766
      }
unknown's avatar
unknown committed
767

768 769 770
      if (table->triggers)
        table->triggers->mark_fields_used(thd, TRG_EVENT_UPDATE);

unknown's avatar
unknown committed
771
      DBUG_PRINT("info",("setting table `%s` for update", tl->alias));
772 773 774 775
      /*
        If table will be updated we should not downgrade lock for it and
        leave it as is.
      */
unknown's avatar
unknown committed
776
    }
unknown's avatar
unknown committed
777 778 779
    else
    {
      DBUG_PRINT("info",("setting table `%s` for read-only", tl->alias));
unknown's avatar
unknown committed
780 781 782 783 784 785
      /*
        If we are using the binary log, we need TL_READ_NO_INSERT to get
        correct order of statements. Otherwise, we use a TL_READ lock to
        improve performance.
      */
      tl->lock_type= using_update_log ? TL_READ_NO_INSERT : TL_READ;
unknown's avatar
unknown committed
786
      tl->updating= 0;
787 788 789
      /* Update TABLE::lock_type accordingly. */
      if (!tl->placeholder() && !tl->schema_table && !using_lock_tables)
        tl->table->reginfo.lock_type= tl->lock_type;
790
    }
unknown's avatar
unknown committed
791
  }
unknown's avatar
unknown committed
792
  for (tl= table_list; tl; tl= tl->next_local)
unknown's avatar
unknown committed
793
  {
unknown's avatar
unknown committed
794
    /* Check access privileges for table */
unknown's avatar
unknown committed
795
    if (!tl->derived)
796
    {
unknown's avatar
unknown committed
797 798
      uint want_privilege= tl->updating ? UPDATE_ACL : SELECT_ACL;
      if (check_access(thd, want_privilege,
799 800
                       tl->db, &tl->grant.privilege, 0, 0, 
                       test(tl->schema_table)) ||
unknown's avatar
unknown committed
801
          (grant_option && check_grant(thd, want_privilege, tl, 0, 1, 0)))
unknown's avatar
unknown committed
802
        DBUG_RETURN(TRUE);
803
    }
804
  }
unknown's avatar
unknown committed
805

unknown's avatar
unknown committed
806 807 808
  /* check single table update for view compound from several tables */
  for (tl= table_list; tl; tl= tl->next_local)
  {
unknown's avatar
unknown committed
809
    if (tl->effective_algorithm == VIEW_ALGORITHM_MERGE)
unknown's avatar
unknown committed
810 811
    {
      TABLE_LIST *for_update= 0;
unknown's avatar
unknown committed
812
      if (tl->check_single_table(&for_update, tables_for_update, tl))
unknown's avatar
unknown committed
813 814 815 816 817 818 819 820
      {
	my_error(ER_VIEW_MULTIUPDATE, MYF(0),
		 tl->view_db.str, tl->view_name.str);
	DBUG_RETURN(-1);
      }
    }
  }

unknown's avatar
unknown committed
821
  /* now lock and fill tables */
822
  if (lock_tables(thd, table_list, table_count, &need_reopen))
unknown's avatar
unknown committed
823
  {
824 825 826
    if (!need_reopen)
      DBUG_RETURN(TRUE);

unknown's avatar
unknown committed
827
    /*
828 829 830 831
      We have to reopen tables since some of them were altered or dropped
      during lock_tables() or something was done with their triggers.
      Let us do some cleanups to be able do setup_table() and setup_fields()
      once again.
unknown's avatar
unknown committed
832 833 834
    */
    List_iterator_fast<Item> it(*fields);
    Item *item;
unknown's avatar
unknown committed
835
    while ((item= it++))
unknown's avatar
unknown committed
836 837
      item->cleanup();

unknown's avatar
unknown committed
838
    /* We have to cleanup translation tables of views. */
unknown's avatar
unknown committed
839 840 841
    for (TABLE_LIST *tbl= table_list; tbl; tbl= tbl->next_global)
      tbl->cleanup_items();

842
    close_tables_for_reopen(thd, &table_list);
843
    goto reopen_tables;
unknown's avatar
unknown committed
844
  }
unknown's avatar
unknown committed
845

846 847 848 849 850
  /*
    Check that we are not using table that we are updating, but we should
    skip all tables of UPDATE SELECT itself
  */
  lex->select_lex.exclude_from_table_unique_test= TRUE;
unknown's avatar
unknown committed
851 852 853 854 855
  /* We only need SELECT privilege for columns in the values list */
  for (tl= leaves; tl; tl= tl->next_leaf)
  {
    TABLE *table= tl->table;
    TABLE_LIST *tlist;
856
    if (!(tlist= tl->top_table())->derived)
unknown's avatar
unknown committed
857 858 859 860 861 862 863
    {
      tlist->grant.want_privilege=
        (SELECT_ACL & ~tlist->grant.privilege);
      table->grant.want_privilege= (SELECT_ACL & ~table->grant.privilege);
    }
    DBUG_PRINT("info", ("table: %s  want_privilege: %u", tl->alias,
                        (uint) table->grant.want_privilege));
864
    if (tl->lock_type != TL_READ &&
865
        tl->lock_type != TL_READ_NO_INSERT)
866
    {
867
      TABLE_LIST *duplicate;
868
      if ((duplicate= unique_table(thd, tl, table_list)))
869 870 871 872
      {
        update_non_unique_table_error(table_list, "UPDATE", duplicate);
        DBUG_RETURN(TRUE);
      }
873
    }
unknown's avatar
unknown committed
874
  }
unknown's avatar
unknown committed
875 876 877 878 879 880
  /*
    Set exclude_from_table_unique_test value back to FALSE. It is needed for
    further check in multi_update::prepare whether to use record cache.
  */
  lex->select_lex.exclude_from_table_unique_test= FALSE;
 
unknown's avatar
unknown committed
881 882
  if (thd->fill_derived_tables() &&
      mysql_handle_derived(lex, &mysql_derived_filling))
883
    DBUG_RETURN(TRUE);
884

unknown's avatar
merge  
unknown committed
885
  DBUG_RETURN (FALSE);
unknown's avatar
VIEW  
unknown committed
886 887 888
}


unknown's avatar
unknown committed
889 890 891
/*
  Setup multi-update handling and call SELECT to do the join
*/
892

unknown's avatar
unknown committed
893 894 895 896 897
bool mysql_multi_update(THD *thd,
                        TABLE_LIST *table_list,
                        List<Item> *fields,
                        List<Item> *values,
                        COND *conds,
898
                        ulonglong options,
899
                        enum enum_duplicates handle_duplicates, bool ignore,
unknown's avatar
unknown committed
900
                        SELECT_LEX_UNIT *unit, SELECT_LEX *select_lex)
unknown's avatar
VIEW  
unknown committed
901 902 903 904
{
  multi_update *result;
  DBUG_ENTER("mysql_multi_update");

905
  if (!(result= new multi_update(table_list,
906 907
				 thd->lex->select_lex.leaf_tables,
				 fields, values,
908
				 handle_duplicates, ignore)))
unknown's avatar
unknown committed
909
    DBUG_RETURN(TRUE);
unknown's avatar
unknown committed
910

unknown's avatar
unknown committed
911 912 913 914 915
  thd->no_trans_update= 0;
  thd->abort_on_warning= test(thd->variables.sql_mode &
                              (MODE_STRICT_TRANS_TABLES |
                               MODE_STRICT_ALL_TABLES));

unknown's avatar
unknown committed
916
  List<Item> total_list;
917 918 919 920 921 922 923 924
  (void) mysql_select(thd, &select_lex->ref_pointer_array,
                      table_list, select_lex->with_wild,
                      total_list,
                      conds, 0, (ORDER *) NULL, (ORDER *)NULL, (Item *) NULL,
                      (ORDER *)NULL,
                      options | SELECT_NO_JOIN_CACHE | SELECT_NO_UNLOCK |
                      OPTION_SETUP_TABLES_DONE,
                      result, unit, select_lex);
unknown's avatar
unknown committed
925
  delete result;
unknown's avatar
unknown committed
926
  thd->abort_on_warning= 0;
927
  DBUG_RETURN(FALSE);
928 929
}

unknown's avatar
unknown committed
930

931
multi_update::multi_update(TABLE_LIST *table_list,
932
			   TABLE_LIST *leaves_list,
unknown's avatar
unknown committed
933
			   List<Item> *field_list, List<Item> *value_list,
unknown's avatar
unknown committed
934 935
			   enum enum_duplicates handle_duplicates_arg,
                           bool ignore_arg)
936
  :all_tables(table_list), leaves(leaves_list), update_tables(0),
937
   tmp_tables(0), updated(0), found(0), fields(field_list),
938 939
   values(value_list), table_count(0), copy_field(0),
   handle_duplicates(handle_duplicates_arg), do_update(1), trans_safe(0),
940
   transactional_tables(1), ignore(ignore_arg)
unknown's avatar
unknown committed
941 942 943 944 945 946 947
{}


/*
  Connect fields with tables and create list of tables that are updated
*/

948 949
int multi_update::prepare(List<Item> &not_used_values,
			  SELECT_LEX_UNIT *lex_unit)
950
{
unknown's avatar
unknown committed
951 952
  TABLE_LIST *table_ref;
  SQL_LIST update;
953
  table_map tables_to_update;
unknown's avatar
unknown committed
954 955 956 957
  Item_field *item;
  List_iterator_fast<Item> field_it(*fields);
  List_iterator_fast<Item> value_it(*values);
  uint i, max_fields;
958
  DBUG_ENTER("multi_update::prepare");
unknown's avatar
unknown committed
959

960
  thd->count_cuted_fields= CHECK_FIELD_WARN;
961
  thd->cuted_fields=0L;
unknown's avatar
unknown committed
962 963
  thd->proc_info="updating main table";

964
  tables_to_update= get_table_map(fields);
965

unknown's avatar
unknown committed
966
  if (!tables_to_update)
967
  {
unknown's avatar
unknown committed
968
    my_message(ER_NO_TABLES_USED, ER(ER_NO_TABLES_USED), MYF(0));
unknown's avatar
unknown committed
969
    DBUG_RETURN(1);
970
  }
unknown's avatar
unknown committed
971

972
  /*
unknown's avatar
unknown committed
973 974
    We have to check values after setup_tables to get used_keys right in
    reference tables
975
  */
976

977
  if (setup_fields(thd, 0, *values, 1, 0, 0))
978 979
    DBUG_RETURN(1);

980
  /*
unknown's avatar
unknown committed
981 982 983
    Save tables beeing updated in update_tables
    update_table->shared is position for table
    Don't use key read on tables that are updated
984
  */
unknown's avatar
unknown committed
985 986

  update.empty();
987
  for (table_ref= leaves; table_ref; table_ref= table_ref->next_leaf)
988
  {
989
    /* TODO: add support of view of join support */
unknown's avatar
unknown committed
990 991
    TABLE *table=table_ref->table;
    if (tables_to_update & table->map)
992
    {
unknown's avatar
unknown committed
993 994 995
      TABLE_LIST *tl= (TABLE_LIST*) thd->memdup((char*) table_ref,
						sizeof(*tl));
      if (!tl)
996
	DBUG_RETURN(1);
unknown's avatar
VIEW  
unknown committed
997
      update.link_in_list((byte*) tl, (byte**) &tl->next_local);
unknown's avatar
unknown committed
998 999
      tl->shared= table_count++;
      table->no_keyread=1;
1000
      table->used_keys.clear_all();
unknown's avatar
unknown committed
1001
      table->pos_in_table_list= tl;
1002 1003
    }
  }
unknown's avatar
unknown committed
1004 1005


unknown's avatar
unknown committed
1006 1007 1008
  table_count=  update.elements;
  update_tables= (TABLE_LIST*) update.first;

1009
  tmp_tables = (TABLE**) thd->calloc(sizeof(TABLE *) * table_count);
unknown's avatar
unknown committed
1010 1011 1012 1013 1014 1015
  tmp_table_param = (TMP_TABLE_PARAM*) thd->calloc(sizeof(TMP_TABLE_PARAM) *
						   table_count);
  fields_for_table= (List_item **) thd->alloc(sizeof(List_item *) *
					      table_count);
  values_for_table= (List_item **) thd->alloc(sizeof(List_item *) *
					      table_count);
1016
  if (thd->is_fatal_error)
unknown's avatar
unknown committed
1017 1018 1019 1020 1021 1022
    DBUG_RETURN(1);
  for (i=0 ; i < table_count ; i++)
  {
    fields_for_table[i]= new List_item;
    values_for_table[i]= new List_item;
  }
1023
  if (thd->is_fatal_error)
unknown's avatar
unknown committed
1024 1025 1026 1027 1028 1029 1030 1031 1032 1033 1034
    DBUG_RETURN(1);

  /* Split fields into fields_for_table[] and values_by_table[] */

  while ((item= (Item_field *) field_it++))
  {
    Item *value= value_it++;
    uint offset= item->field->table->pos_in_table_list->shared;
    fields_for_table[offset]->push_back(item);
    values_for_table[offset]->push_back(value);
  }
1035
  if (thd->is_fatal_error)
unknown's avatar
unknown committed
1036 1037 1038 1039 1040 1041 1042
    DBUG_RETURN(1);

  /* Allocate copy fields */
  max_fields=0;
  for (i=0 ; i < table_count ; i++)
    set_if_bigger(max_fields, fields_for_table[i]->elements);
  copy_field= new Copy_field[max_fields];
1043 1044 1045 1046 1047 1048 1049 1050 1051 1052 1053 1054 1055

  /*
    Mark all copies of tables that are updates to ensure that
    init_read_record() will not try to enable a cache on them

    The problem is that for queries like

    UPDATE t1, t1 AS t2 SET t1.b=t2.c WHERE t1.a=t2.a;

    the row buffer may contain things that doesn't match what is on disk
    which will cause an error when reading a row.
    (This issue is mostly relevent for MyISAM tables)
  */
1056
  for (table_ref= leaves;  table_ref; table_ref= table_ref->next_leaf)
1057 1058
  {
    TABLE *table=table_ref->table;
1059
    if ((tables_to_update & table->map) &&
1060
        unique_table(thd, table_ref, update_tables))
1061 1062
      table->no_cache= 1;			// Disable row cache
  }
1063
  DBUG_RETURN(thd->is_fatal_error != 0);
1064 1065 1066
}


unknown's avatar
unknown committed
1067
/*
1068
  Initialize table for multi table
unknown's avatar
unknown committed
1069

1070 1071 1072 1073
  IMPLEMENTATION
    - Update first table in join on the fly, if possible
    - Create temporary tables to store changed values for all other tables
      that are updated (and main_table if the above doesn't hold).
unknown's avatar
unknown committed
1074 1075 1076
*/

bool
1077 1078
multi_update::initialize_tables(JOIN *join)
{
unknown's avatar
unknown committed
1079 1080 1081 1082 1083 1084 1085
  TABLE_LIST *table_ref;
  DBUG_ENTER("initialize_tables");

  if ((thd->options & OPTION_SAFE_UPDATES) && error_if_full_join(join))
    DBUG_RETURN(1);
  main_table=join->join_tab->table;
  trans_safe= transactional_tables= main_table->file->has_transactions();
1086 1087 1088
  table_to_update= 0;

  /* Create a temporary table for keys to all tables, except main table */
unknown's avatar
VIEW  
unknown committed
1089
  for (table_ref= update_tables; table_ref; table_ref= table_ref->next_local)
1090
  {
unknown's avatar
unknown committed
1091
    TABLE *table=table_ref->table;
1092
    uint cnt= table_ref->shared;
unknown's avatar
unknown committed
1093
    Item_field *ifield;
1094 1095
    List<Item> temp_fields= *fields_for_table[cnt];
    ORDER     group;
unknown's avatar
unknown committed
1096

1097 1098 1099 1100 1101 1102 1103
    if (table == main_table)			// First table in join
    {
      if (safe_update_on_fly(join->join_tab, &temp_fields))
      {
	table_to_update= main_table;		// Update table on the fly
	continue;
      }
1104
    }
1105 1106 1107 1108 1109 1110 1111 1112 1113 1114 1115

    TMP_TABLE_PARAM *tmp_param= tmp_table_param+cnt;

    /*
      Create a temporary table to store all fields that are changed for this
      table. The first field in the temporary table is a pointer to the
      original row so that we can find and update it
    */

    /* ok to be on stack as this is not referenced outside of this func */
    Field_string offset(table->file->ref_length, 0, "offset",
unknown's avatar
unknown committed
1116
			table, &my_charset_bin);
1117 1118 1119 1120 1121
    /*
      The field will be converted to varstring when creating tmp table if
      table to be updated was created by mysql 4.1. Deny this.
    */
    offset.can_alter_field_type= 0;
unknown's avatar
unknown committed
1122
    if (!(ifield= new Item_field(((Field *) &offset))))
unknown's avatar
unknown committed
1123
      DBUG_RETURN(1);
unknown's avatar
unknown committed
1124 1125
    ifield->maybe_null= 0;
    if (temp_fields.push_front(ifield))
1126 1127 1128 1129 1130 1131 1132 1133 1134 1135 1136 1137 1138 1139
      DBUG_RETURN(1);

    /* Make an unique key over the first field to avoid duplicated updates */
    bzero((char*) &group, sizeof(group));
    group.asc= 1;
    group.item= (Item**) temp_fields.head_ref();

    tmp_param->quick_group=1;
    tmp_param->field_count=temp_fields.elements;
    tmp_param->group_parts=1;
    tmp_param->group_length= table->file->ref_length;
    if (!(tmp_tables[cnt]=create_tmp_table(thd,
					   tmp_param,
					   temp_fields,
unknown's avatar
unknown committed
1140 1141
					   (ORDER*) &group, 0, 0,
					   TMP_TABLE_ALL_COLUMNS,
1142 1143
					   HA_POS_ERROR,
					   (char *) "")))
1144 1145
      DBUG_RETURN(1);
    tmp_tables[cnt]->file->extra(HA_EXTRA_WRITE_CACHE);
1146
  }
unknown's avatar
unknown committed
1147
  DBUG_RETURN(0);
1148 1149
}

1150 1151 1152 1153 1154 1155 1156 1157 1158 1159
/*
  Check if table is safe to update on fly

  SYNOPSIS
    safe_update_on_fly
    join_tab		How table is used in join
    fields		Fields that are updated

  NOTES
    We can update the first table in join on the fly if we know that
unknown's avatar
unknown committed
1160 1161
    a row in this table will never be read twice. This is true under
    the following conditions:
1162 1163 1164 1165 1166 1167 1168

    - We are doing a table scan and the data is in a separate file (MyISAM) or
      if we don't update a clustered key.

    - We are doing a range scan and we don't update the scan key or
      the primary key for a clustered table handler.

unknown's avatar
unknown committed
1169 1170 1171 1172
    When checking for above cases we also should take into account that
    BEFORE UPDATE trigger potentially may change value of any field in row
    being updated.

1173 1174 1175 1176 1177 1178 1179 1180 1181 1182 1183 1184 1185 1186 1187
  WARNING
    This code is a bit dependent of how make_join_readinfo() works.

  RETURN
    0		Not safe to update
    1		Safe to update
*/

static bool safe_update_on_fly(JOIN_TAB *join_tab, List<Item> *fields)
{
  TABLE *table= join_tab->table;
  switch (join_tab->type) {
  case JT_SYSTEM:
  case JT_CONST:
  case JT_EQ_REF:
1188
    return TRUE;				// At most one matching row
1189
  case JT_REF:
unknown's avatar
unknown committed
1190
  case JT_REF_OR_NULL:
unknown's avatar
unknown committed
1191 1192 1193
    return !check_if_key_used(table, join_tab->ref.key, *fields) &&
           !(table->triggers &&
             table->triggers->has_before_update_triggers());
1194 1195 1196
  case JT_ALL:
    /* If range search on index */
    if (join_tab->quick)
unknown's avatar
unknown committed
1197 1198 1199
      return !join_tab->quick->check_if_keys_used(fields) &&
             !(table->triggers &&
               table->triggers->has_before_update_triggers());
1200 1201
    /* If scanning in clustered key */
    if ((table->file->table_flags() & HA_PRIMARY_KEY_IN_READ_INDEX) &&
1202
	table->s->primary_key < MAX_KEY)
unknown's avatar
unknown committed
1203 1204 1205
      return !check_if_key_used(table, table->s->primary_key, *fields) &&
             !(table->triggers &&
               table->triggers->has_before_update_triggers());
1206
    return TRUE;
1207 1208 1209
  default:
    break;					// Avoid compler warning
  }
1210
  return FALSE;
1211 1212
}

1213 1214 1215

multi_update::~multi_update()
{
unknown's avatar
unknown committed
1216
  TABLE_LIST *table;
unknown's avatar
VIEW  
unknown committed
1217
  for (table= update_tables ; table; table= table->next_local)
1218
    table->table->no_keyread= table->table->no_cache= 0;
unknown's avatar
unknown committed
1219

1220 1221
  if (tmp_tables)
  {
1222 1223 1224 1225 1226 1227 1228 1229
    for (uint cnt = 0; cnt < table_count; cnt++)
    {
      if (tmp_tables[cnt])
      {
	free_tmp_table(thd, tmp_tables[cnt]);
	tmp_table_param[cnt].cleanup();
      }
    }
1230
  }
unknown's avatar
unknown committed
1231 1232
  if (copy_field)
    delete [] copy_field;
1233
  thd->count_cuted_fields= CHECK_FIELD_IGNORE;		// Restore this setting
unknown's avatar
unknown committed
1234 1235
  if (!trans_safe)
    thd->options|=OPTION_STATUS_NO_TRANS_UPDATE;
1236 1237 1238
}


unknown's avatar
unknown committed
1239
bool multi_update::send_data(List<Item> &not_used_values)
1240
{
unknown's avatar
unknown committed
1241 1242 1243
  TABLE_LIST *cur_table;
  DBUG_ENTER("multi_update::send_data");

unknown's avatar
VIEW  
unknown committed
1244
  for (cur_table= update_tables; cur_table; cur_table= cur_table->next_local)
1245
  {
unknown's avatar
unknown committed
1246
    TABLE *table= cur_table->table;
1247 1248 1249 1250 1251 1252 1253 1254 1255 1256 1257 1258
    /*
      Check if we are using outer join and we didn't find the row
      or if we have already updated this row in the previous call to this
      function.

      The same row may be presented here several times in a join of type
      UPDATE t1 FROM t1,t2 SET t1.a=t2.a

      In this case we will do the update for the first found row combination.
      The join algorithm guarantees that we will not find the a row in
      t1 several times.
    */
unknown's avatar
unknown committed
1259 1260 1261 1262 1263
    if (table->status & (STATUS_NULL_ROW | STATUS_UPDATED))
      continue;

    uint offset= cur_table->shared;
    table->file->position(table->record[0]);
1264 1265 1266 1267
    /*
      We can use compare_record() to optimize away updates if
      the table handler is returning all columns
    */
1268
    if (table == table_to_update)
1269
    {
1270 1271 1272
      bool can_compare_record;
      can_compare_record= !(table->file->table_flags() &
                            HA_PARTIAL_COLUMN_READ);
1273
      table->status|= STATUS_UPDATED;
unknown's avatar
unknown committed
1274
      store_record(table,record[1]);
unknown's avatar
unknown committed
1275 1276 1277 1278
      if (fill_record_n_invoke_before_triggers(thd, *fields_for_table[offset],
                                               *values_for_table[offset], 0,
                                               table->triggers,
                                               TRG_EVENT_UPDATE))
unknown's avatar
unknown committed
1279
	DBUG_RETURN(1);
unknown's avatar
unknown committed
1280

1281
      found++;
1282
      if (!can_compare_record || compare_record(table, thd->query_id))
1283
      {
unknown's avatar
unknown committed
1284
	int error;
unknown's avatar
unknown committed
1285
        if ((error= cur_table->view_check_option(thd, ignore)) !=
unknown's avatar
unknown committed
1286 1287 1288 1289 1290 1291 1292 1293
            VIEW_CHECK_OK)
        {
          found--;
          if (error == VIEW_CHECK_SKIP)
            continue;
          else if (error == VIEW_CHECK_ERROR)
            DBUG_RETURN(1);
        }
unknown's avatar
unknown committed
1294
	if (!updated++)
1295
	{
unknown's avatar
unknown committed
1296 1297 1298 1299 1300 1301
	  /*
	    Inform the main table that we are going to update the table even
	    while we may be scanning it.  This will flush the read cache
	    if it's used.
	  */
	  main_table->file->extra(HA_EXTRA_PREPARE_FOR_UPDATE);
1302
	}
unknown's avatar
unknown committed
1303 1304
	if ((error=table->file->update_row(table->record[1],
					   table->record[0])))
1305
	{
unknown's avatar
unknown committed
1306
	  updated--;
1307
          if (!ignore || error != HA_ERR_FOUND_DUPP_KEY)
1308
	  {
1309 1310 1311 1312 1313 1314
            /*
              If (ignore && error == HA_ERR_FOUND_DUPP_KEY) we don't have to
              do anything; otherwise...
            */
            if (error != HA_ERR_FOUND_DUPP_KEY)
              thd->fatal_error(); /* Other handler errors are fatal */
1315 1316 1317
	    table->file->print_error(error,MYF(0));
	    DBUG_RETURN(1);
	  }
1318
	}
unknown's avatar
unknown committed
1319 1320 1321 1322 1323 1324 1325 1326 1327
        else
        {
          if (!table->file->has_transactions())
            thd->no_trans_update= 1;
          if (table->triggers &&
              table->triggers->process_triggers(thd, TRG_EVENT_UPDATE,
                                                TRG_ACTION_AFTER, TRUE))
	    DBUG_RETURN(1);
        }
1328
      }
unknown's avatar
unknown committed
1329 1330 1331 1332 1333
    }
    else
    {
      int error;
      TABLE *tmp_table= tmp_tables[offset];
unknown's avatar
unknown committed
1334
      fill_record(thd, tmp_table->field+1, *values_for_table[offset], 1);
unknown's avatar
unknown committed
1335 1336 1337 1338
      /* Store pointer to row */
      memcpy((char*) tmp_table->field[0]->ptr,
	     (char*) table->file->ref, table->file->ref_length);
      /* Write row, ignoring duplicated updates to a row */
1339
      if ((error= tmp_table->file->write_row(tmp_table->record[0])))
1340
      {
1341 1342 1343 1344
        if (error != HA_ERR_FOUND_DUPP_KEY &&
            error != HA_ERR_FOUND_DUPP_UNIQUE &&
            create_myisam_from_heap(thd, tmp_table,
                                         tmp_table_param + offset, error, 1))
1345
	{
unknown's avatar
unknown committed
1346 1347
	  do_update=0;
	  DBUG_RETURN(1);			// Not a table_is_full error
1348 1349
	}
      }
1350 1351
      else
        found++;
1352 1353
    }
  }
unknown's avatar
unknown committed
1354
  DBUG_RETURN(0);
1355 1356
}

unknown's avatar
unknown committed
1357

1358 1359 1360
void multi_update::send_error(uint errcode,const char *err)
{
  /* First send error what ever it is ... */
unknown's avatar
unknown committed
1361
  my_error(errcode, MYF(0), err);
1362 1363 1364 1365

  /* If nothing updated return */
  if (!updated)
    return;
1366

1367
  /* Something already updated so we have to invalidate cache */
1368 1369
  query_cache_invalidate3(thd, update_tables, 1);

1370
  /*
unknown's avatar
unknown committed
1371 1372
    If all tables that has been updated are trans safe then just do rollback.
    If not attempt to do remaining updates.
1373
  */
unknown's avatar
unknown committed
1374 1375

  if (trans_safe)
1376
    ha_rollback_stmt(thd);
unknown's avatar
unknown committed
1377 1378 1379 1380 1381
  else if (do_update && table_count > 1)
  {
    /* Add warning here */
    VOID(do_updates(0));
  }
1382 1383 1384
}


unknown's avatar
unknown committed
1385
int multi_update::do_updates(bool from_send_error)
1386
{
unknown's avatar
unknown committed
1387 1388 1389
  TABLE_LIST *cur_table;
  int local_error;
  ha_rows org_updated;
unknown's avatar
unknown committed
1390
  TABLE *table, *tmp_table;
unknown's avatar
unknown committed
1391 1392
  DBUG_ENTER("do_updates");

unknown's avatar
unknown committed
1393
  do_update= 0;					// Don't retry this function
1394
  if (!found)
unknown's avatar
unknown committed
1395
    DBUG_RETURN(0);
unknown's avatar
VIEW  
unknown committed
1396
  for (cur_table= update_tables; cur_table; cur_table= cur_table->next_local)
1397
  {
1398
    byte *ref_pos;
1399
    bool can_compare_record;
unknown's avatar
unknown committed
1400

unknown's avatar
unknown committed
1401
    table = cur_table->table;
1402
    if (table == table_to_update)
unknown's avatar
unknown committed
1403 1404
      continue;					// Already updated
    org_updated= updated;
1405
    tmp_table= tmp_tables[cur_table->shared];
unknown's avatar
unknown committed
1406
    tmp_table->file->extra(HA_EXTRA_CACHE);	// Change to read cache
unknown's avatar
unknown committed
1407
    (void) table->file->ha_rnd_init(0);
unknown's avatar
unknown committed
1408 1409 1410 1411 1412 1413 1414 1415 1416
    table->file->extra(HA_EXTRA_NO_CACHE);

    /*
      Setup copy functions to copy fields from temporary table
    */
    List_iterator_fast<Item> field_it(*fields_for_table[cur_table->shared]);
    Field **field= tmp_table->field+1;		// Skip row pointer
    Copy_field *copy_field_ptr= copy_field, *copy_field_end;
    for ( ; *field ; field++)
1417
    {
unknown's avatar
unknown committed
1418 1419
      Item_field *item= (Item_field* ) field_it++;
      (copy_field_ptr++)->set(item->field, *field, 0);
1420
    }
unknown's avatar
unknown committed
1421 1422
    copy_field_end=copy_field_ptr;

unknown's avatar
unknown committed
1423
    if ((local_error = tmp_table->file->ha_rnd_init(1)))
unknown's avatar
unknown committed
1424 1425
      goto err;

1426 1427 1428
    can_compare_record= !(table->file->table_flags() &
                          HA_PARTIAL_COLUMN_READ);

unknown's avatar
unknown committed
1429 1430
    ref_pos= (byte*) tmp_table->field[0]->ptr;
    for (;;)
1431
    {
unknown's avatar
unknown committed
1432 1433 1434 1435 1436 1437 1438 1439 1440 1441 1442 1443
      if (thd->killed && trans_safe)
	goto err;
      if ((local_error=tmp_table->file->rnd_next(tmp_table->record[0])))
      {
	if (local_error == HA_ERR_END_OF_FILE)
	  break;
	if (local_error == HA_ERR_RECORD_DELETED)
	  continue;				// May happen on dup key
	goto err;
      }
      if ((local_error= table->file->rnd_pos(table->record[0], ref_pos)))
	goto err;
1444
      table->status|= STATUS_UPDATED;
unknown's avatar
unknown committed
1445
      store_record(table,record[1]);
unknown's avatar
unknown committed
1446 1447 1448 1449 1450 1451 1452

      /* Copy data from temporary table to current table */
      for (copy_field_ptr=copy_field;
	   copy_field_ptr != copy_field_end;
	   copy_field_ptr++)
	(*copy_field_ptr->do_copy)(copy_field_ptr);

unknown's avatar
unknown committed
1453 1454 1455 1456 1457
      if (table->triggers &&
          table->triggers->process_triggers(thd, TRG_EVENT_UPDATE,
                                            TRG_ACTION_BEFORE, TRUE))
        goto err2;

1458
      if (!can_compare_record || compare_record(table, thd->query_id))
1459
      {
unknown's avatar
unknown committed
1460 1461 1462
	if ((local_error=table->file->update_row(table->record[1],
						 table->record[0])))
	{
1463
	  if (!ignore || local_error != HA_ERR_FOUND_DUPP_KEY)
unknown's avatar
unknown committed
1464 1465 1466
	    goto err;
	}
	updated++;
unknown's avatar
unknown committed
1467 1468 1469 1470 1471

        if (table->triggers &&
            table->triggers->process_triggers(thd, TRG_EVENT_UPDATE,
                                              TRG_ACTION_AFTER, TRUE))
          goto err2;
1472
      }
unknown's avatar
unknown committed
1473 1474 1475 1476 1477
    }

    if (updated != org_updated)
    {
      if (table->file->has_transactions())
unknown's avatar
unknown committed
1478
	transactional_tables= 1;
1479
      else
unknown's avatar
unknown committed
1480
	trans_safe= 0;				// Can't do safe rollback
1481
    }
unknown's avatar
unknown committed
1482 1483
    (void) table->file->ha_rnd_end();
    (void) tmp_table->file->ha_rnd_end();
1484
  }
unknown's avatar
unknown committed
1485 1486 1487 1488
  DBUG_RETURN(0);

err:
  if (!from_send_error)
unknown's avatar
unknown committed
1489 1490
  {
    thd->fatal_error();
unknown's avatar
unknown committed
1491
    table->file->print_error(local_error,MYF(0));
unknown's avatar
unknown committed
1492
  }
unknown's avatar
unknown committed
1493

unknown's avatar
unknown committed
1494
err2:
unknown's avatar
unknown committed
1495 1496 1497
  (void) table->file->ha_rnd_end();
  (void) tmp_table->file->ha_rnd_end();

unknown's avatar
unknown committed
1498 1499 1500
  if (updated != org_updated)
  {
    if (table->file->has_transactions())
unknown's avatar
unknown committed
1501
      transactional_tables= 1;
unknown's avatar
unknown committed
1502 1503 1504 1505
    else
      trans_safe= 0;
  }
  DBUG_RETURN(1);
1506 1507 1508
}


unknown's avatar
unknown committed
1509 1510
/* out: 1 if error, 0 if success */

1511 1512
bool multi_update::send_eof()
{
unknown's avatar
unknown committed
1513
  char buff[STRING_BUFFER_USUAL_SIZE];
unknown's avatar
unknown committed
1514
  thd->proc_info="updating reference tables";
1515 1516

  /* Does updates for the last n - 1 tables, returns 0 if ok */
unknown's avatar
unknown committed
1517
  int local_error = (table_count) ? do_updates(0) : 0;
1518
  thd->proc_info= "end";
1519

unknown's avatar
unknown committed
1520 1521 1522 1523 1524 1525 1526 1527
  /* We must invalidate the query cache before binlog writing and
  ha_autocommit_... */

  if (updated)
  {
    query_cache_invalidate3(thd, update_tables, 1);
  }

unknown's avatar
unknown committed
1528 1529
  /*
    Write the SQL statement to the binlog if we updated
unknown's avatar
unknown committed
1530
    rows and we succeeded or if we updated some non
1531
    transactional tables.
unknown's avatar
unknown committed
1532
  */
1533

1534
  if ((local_error == 0) || (updated && !trans_safe))
1535
  {
unknown's avatar
unknown committed
1536 1537
    if (mysql_bin_log.is_open())
    {
1538
      if (local_error == 0)
unknown's avatar
unknown committed
1539
        thd->clear_error();
unknown's avatar
unknown committed
1540
      Query_log_event qinfo(thd, thd->query, thd->query_length,
unknown's avatar
unknown committed
1541
			    transactional_tables, FALSE);
unknown's avatar
unknown committed
1542
      if (mysql_bin_log.write(&qinfo) && trans_safe)
1543
	local_error= 1;				// Rollback update
unknown's avatar
unknown committed
1544
    }
unknown's avatar
unknown committed
1545
    if (!transactional_tables)
unknown's avatar
unknown committed
1546 1547
      thd->options|=OPTION_STATUS_NO_TRANS_UPDATE;
  }
1548

unknown's avatar
unknown committed
1549 1550
  if (transactional_tables)
  {
1551
    if (ha_autocommit_or_rollback(thd, local_error != 0))
unknown's avatar
unknown committed
1552 1553
      local_error=1;
  }
1554

unknown's avatar
unknown committed
1555 1556 1557 1558 1559
  if (local_error > 0) // if the above log write did not fail ...
  {
    /* Safety: If we haven't got an error before (should not happen) */
    my_message(ER_UNKNOWN_ERROR, "An error occured in multi-table update",
	       MYF(0));
1560
    return TRUE;
1561
  }
unknown's avatar
unknown committed
1562 1563


1564 1565
  sprintf(buff, ER(ER_UPDATE_INFO), (ulong) found, (ulong) updated,
	  (ulong) thd->cuted_fields);
1566 1567
  thd->row_count_func=
    (thd->client_capabilities & CLIENT_FOUND_ROWS) ? found : updated;
1568
  ::send_ok(thd, (ulong) thd->row_count_func,
unknown's avatar
unknown committed
1569
	    thd->insert_id_used ? thd->insert_id() : 0L,buff);
1570
  return FALSE;
1571
}