* some of these tests run just fine with InnoDB:
   -> s/have_xtradb/have_innodb/
* sys_var tests did basic tests for xtradb only variables
   -> remove them, they're useless anyway (sysvar_innodb does it better)
* multi_update had innodb specific tests
   -> move to multi_update_innodb.test

MDEV-5973: MySQL  Bug#11757486:49539: NON-DESCRIPTIVE ERR (ERROR 0 FROM STORAGE ENGINE) WITH MULTI-TABLE UPDATE

Condition in processing IGNORE clause for UPDATE & multi-table UPDATE made the same.

MDEV-6066: Merge new defaults from 5.6 and 5.7 (defaults changed, QC can be stopped with no-zero size)

save_prep_leaf_tables() made recursive to work with underlying view

Arena restoiring fixed in case of EOM.

Multi-update do not need full list of leaf tables. It also do not use it on prepare (mysql_multi_update_prepare()).

All underlying tables should share the same lock type.

back-ported the patch for bug #13256831 from mysql-5.6 code line.

  Here's the comment this patch was provided with:

  Fixed bug#13256831 - ERROR 1032 (HY000): CAN'T FIND RECORD.

  This bug only occurs if a user tries to update a base table using
  an updatable view and this view was created as a join for which
  the clause 'WITH CHECK OPTION' was specified.

  The reason for the bug was that when such an update was
  executed, row positions were not properly handled for tables
  that were not updated but had constraints that had to be
  checked due to the 'WITH CHECK OPTION' clause.

  The reason for the bug was that when such update is executed
  then for tables specified in the view definition and
  also listed in the 'WITH CHECK OPTION' clause the positioning to
  row being updated is not performed.

MDEV-6139: UPDATE w/ join against MRG_MyISAM table with read-only sub-table failsUPDATE w/ join against MRG_MyISAM table with read-only sub-table fails

The problem was that on opening all tables TL_WRITE, than local tables which is not updated set to TL_READ, but underlying tables of MyISAMmrg left untouched.

Prartition engine has not this problem.

All cases where lock_type assigned is not changed because call of virtual function is not cheap.

Missed update_used_tables() call for multi-update values.

              Fixed a typo in the comment.
              Fixing test cases which were previouslyno throwing  due
              disable warnings macro. 

sql/sql_base.cc:
  Change in indentation and fixing a typo in the comment.

              Fixed a typo in the comment.
              Fixing test cases which were previouslyno throwing  due
              disable warnings macro. 

       
Problem: Statements that write to tables with auto_increment columns
         based on the selection from another table, may lead to master
         and slave going out of sync, as the order in which the rows
         are retrieved from the table may differ on master and slave.
            
Solution: We mark writing to a table with auto_increment table
          based on the rows selected from another table as unsafe. This
          will cause the execution of such statements to throw a warning
          and forces the statement to be logged in ROW if the logging
          format is mixed. 
            
Changes:
       1. All the statements that writes to a table with auto_increment 
          column(s) based on the rows fetched from another table, will now
          be unsafe.
       2. CREATE TABLE with SELECT will now be unsafe.

sql/share/errmsg-utf8.txt:
  Added new warning messages.
sql/sql_base.cc:
  -Created function to check statements that write to 
   tables with auto_increment column and has select.
  -Marked all the statements that write to a table
   with auto_increment column based on rows fetched
   from other table(s) as unsafe.
sql/sql_table.cc:
  mark CREATE TABLE[with auto_increment column] as unsafe.

       
Problem: Statements that write to tables with auto_increment columns
         based on the selection from another table, may lead to master
         and slave going out of sync, as the order in which the rows
         are retrieved from the table may differ on master and slave.
            
Solution: We mark writing to a table with auto_increment table
          based on the rows selected from another table as unsafe. This
          will cause the execution of such statements to throw a warning
          and forces the statement to be logged in ROW if the logging
          format is mixed. 
            
Changes:
       1. All the statements that writes to a table with auto_increment 
          column(s) based on the rows fetched from another table, will now
          be unsafe.
       2. CREATE TABLE with SELECT will now be unsafe.

      
      Problem: Statements that write to tables with auto_increment columns
      based on the selection from another table, may lead to master
      and slave going out of sync, as the order in which the rows
      are retrived from the table may differ on master and slave.
      
      Solution: We mark writing to a table with auto_increment table
      as unsafe. This will cause the execution of such statements to
      throw a warning and forces the statement to be logged in ROW if
      the logging format is mixed. 
      
      Changes: 
      1. All the statements that writes to a table with auto_increment 
      column(s) based on the rows fetched from another table, will now
      be unsafe.
      2. CREATE TABLE with SELECT will now be unsafe.


sql/share/errmsg-utf8.txt:
  Added new Warning messages
sql/sql_base.cc:
  created a new function that checks for select + write on a autoinc table
  made all such statements to be unsafe.
sql/sql_parse.cc:
  made create autoincremnet tabble + select unsafe

      
      Problem: Statements that write to tables with auto_increment columns
      based on the selection from another table, may lead to master
      and slave going out of sync, as the order in which the rows
      are retrived from the table may differ on master and slave.
      
      Solution: We mark writing to a table with auto_increment table
      as unsafe. This will cause the execution of such statements to
      throw a warning and forces the statement to be logged in ROW if
      the logging format is mixed. 
      
      Changes: 
      1. All the statements that writes to a table with auto_increment 
      column(s) based on the rows fetched from another table, will now
      be unsafe.
      2. CREATE TABLE with SELECT will now be unsafe.

              UPDATED TWICE

For multi update it is not allowed to update a column
of a table if that table is accessed through multiple aliases
and either
1) the updated column is used as partitioning key
2) the updated column is part of the primary key 
   and the primary key is clustered

This check is done in unsafe_key_update().

The bug was that for case 2), it was checked whether
updated_column_number == table_share->primary_key 
However, the primary_key variable is the index number of the 
primary key, not a column number.

Prior to this bugfix, the first column was wrongly believed to be
the primary key. The columns covered by an index is found in
table->key_info[idx_number]->key_part. The bugfix is to check if
any of the columns in the keyparts of the primary key are
updated.

The user-visible effect is that for storage engines with
clustered primary key (e.g. InnoDB but not MyISAM) queries
like 
"UPDATE t1 AS A JOIN t2 AS B SET A.primkey=..."
will now error with 
"ERROR HY000: Primary key/partition key update is not allowed 
since the table is updated both as 'A' and 'B'." 
instead of 
"ERROR 1032 (HY000): Can't find record in 't1_tb'"
even if primkey is not the first column in the table. This 
was the intended behavior of bugfix 11764529.


mysql-test/r/multi_update.result:
  Add test for bug#11882110
mysql-test/r/multi_update_innodb.result:
  Add test for bug#11882110
mysql-test/t/multi_update.test:
  Add test for bug#11882110
mysql-test/t/multi_update_innodb.test:
  Add test for bug#11882110
sql/sql_update.cc:
  unsafe_key_update() wrongly checked if the primary key index
  number was the same as updated column number. Now it is checked
  whether any of the columns making up the primary key is updated.
sql/table.h:
  Fix comment on TABLE_SHARE::primary_key. Incorrect comment
  was introduced by an earlier merge conflict (as per dlenev)

              UPDATED TWICE

For multi update it is not allowed to update a column
of a table if that table is accessed through multiple aliases
and either
1) the updated column is used as partitioning key
2) the updated column is part of the primary key 
   and the primary key is clustered

This check is done in unsafe_key_update().

The bug was that for case 2), it was checked whether
updated_column_number == table_share->primary_key 
However, the primary_key variable is the index number of the 
primary key, not a column number.

Prior to this bugfix, the first column was wrongly believed to be
the primary key. The columns covered by an index is found in
table->key_info[idx_number]->key_part. The bugfix is to check if
any of the columns in the keyparts of the primary key are
updated.

The user-visible effect is that for storage engines with
clustered primary key (e.g. InnoDB but not MyISAM) queries
like 
"UPDATE t1 AS A JOIN t2 AS B SET A.primkey=..."
will now error with 
"ERROR HY000: Primary key/partition key update is not allowed 
since the table is updated both as 'A' and 'B'." 
instead of 
"ERROR 1032 (HY000): Can't find record in 't1_tb'"
even if primkey is not the first column in the table. This 
was the intended behavior of bugfix 11764529.

The patch imposes unconditional materialization for derived tables
used in update and multi-update statements.
Fixed a bug with a wrong order of processing derived tables/views
at the prepare stage that caused a crash for the variant of the 
query from test case for bug 52157.

Two of them (in the function make_join_statistics and in the function
sort_and_filter_keyuse) were due to bad merges while the third bug was
triggered by uninitialized values of the field JOIN_TAB::preread_init_done.

Fixed a compiler warning in mysql_priv.h.

Commented out some queries from the funcs_1 suite returning wrong
errors due to bugs concerning updatable views (see bugs 784297 and
784303).

Adjusted some results for the test cases with derived tables from
different suites. 

              UPDATES THE TABLE ENTRIES (formerly 55385)
BUG#11764529: MULTI UPDATE+INNODB REPORTS ER_KEY_NOT_FOUND 
              IF A TABLE IS UPDATED TWICE (formerly 57373)
            
If multiple-table update updates a row through two aliases and
the first update physically moves the row, the second update will
fail to locate the row. This results in different errors
depending on storage engine:
  * MyISAM: Got error 134 from storage engine
  * InnoDB: Can't find record in 'tbl'
None of these errors accurately describe the problem. 
      
Furthermore, since MyISAM is non-transactional, the update
executed first will be performed while the second will not.
In addition, for two equal multiple-table update statements,
one could succeed and the other fail based on whether or not
the record actually moved or not. This was inconsistent.
      
Two update operations may physically move a row:
  1) Update of a column in a clustered primary key
  2) Update of a column used to calculate which partition the 
     row belongs to
           
BUG#11764529 is about case 1) above, BUG#11762751 was about case 2).
      
The fix for these bugs is to return with an error if multiple-table 
update is about to:
  a) Update a table through multiple aliases, and
  b) Perform an update that may physically more the row 
     in at least one of these aliases
    
This avoids 
  * partial updates as described for MyISAM above,
  * provides the same error message that describes the actual problem
    for all SEs
  * inconsistent behavior where a statement fails or succeeds based on
    e.g. the partitioning algorithm of the table.

mysql-test/r/multi_update.result:
  Add test for bug#57373
mysql-test/r/multi_update_innodb.result:
  Add test for bug#57373
mysql-test/r/partition.result:
  Add test for bug#55385
mysql-test/t/multi_update.test:
  Add test for bug#57373
mysql-test/t/multi_update_innodb.test:
  Add test for bug#57373
mysql-test/t/partition.test:
  Add test for bug#55385
sql/handler.cc:
  Translate handler error HA_ERR_RECORD_DELETED to server error
sql/share/errmsg-utf8.txt:
  New error message for multi-table update where the same table is updated multiple times.
sql/sql_update.cc:
  Add function unsafe_key_update()

              UPDATES THE TABLE ENTRIES (formerly 55385)
BUG#11764529: MULTI UPDATE+INNODB REPORTS ER_KEY_NOT_FOUND 
              IF A TABLE IS UPDATED TWICE (formerly 57373)
            
If multiple-table update updates a row through two aliases and
the first update physically moves the row, the second update will
fail to locate the row. This results in different errors
depending on storage engine:
  * MyISAM: Got error 134 from storage engine
  * InnoDB: Can't find record in 'tbl'
None of these errors accurately describe the problem. 
      
Furthermore, since MyISAM is non-transactional, the update
executed first will be performed while the second will not.
In addition, for two equal multiple-table update statements,
one could succeed and the other fail based on whether or not
the record actually moved or not. This was inconsistent.
      
Two update operations may physically move a row:
  1) Update of a column in a clustered primary key
  2) Update of a column used to calculate which partition the 
     row belongs to
           
BUG#11764529 is about case 1) above, BUG#11762751 was about case 2).
      
The fix for these bugs is to return with an error if multiple-table 
update is about to:
  a) Update a table through multiple aliases, and
  b) Perform an update that may physically more the row 
     in at least one of these aliases
    
This avoids 
  * partial updates as described for MyISAM above,
  * provides the same error message that describes the actual problem
    for all SEs
  * inconsistent behavior where a statement fails or succeeds based on
    e.g. the partitioning algorithm of the table.

> ------------------------------------------------------------
> revno: 3520
> revision-id: sergey.glukhov@oracle.com-20101214093303-wmo9mqcb8rz0wv9f
> parent: tor.didriksen@oracle.com-20101213161301-81lprlbune7r98dl
> committer: Sergey Glukhov <sergey.glukhov@oracle.com>
> branch nick: mysql-5.1-bugteam
> timestamp: Tue 2010-12-14 12:33:03 +0300
> message:
>   Fixed following problems:
>   --Bug#52157 various crashes and assertions with multi-table update, stored function
>   --Bug#54475 improper error handling causes cascading crashing failures in innodb/ndb
>   --Bug#57703 create view cause Assertion failed: 0, file .\item_subselect.cc, line 846
>   --Bug#57352 valgrind warnings when creating view
>   --Recently discovered problem when a nested materialized derived table is used
>     before being populated and it leads to incorrect result
>   
>   We have several modes when we should disable subquery evaluation.
>   The reasons for disabling are different. It could be
>   uselessness of the evaluation as in case of 'CREATE VIEW'
>   or 'PREPARE stmt', or we should disable subquery evaluation
>   if tables are not locked yet as it happens in bug#54475, or
>   too early evaluation of subqueries can lead to wrong result
>   as it happened in Bug#19077.
>   Main problem is that if subquery items are treated as const
>   they are evaluated in ::fix_fields(), ::fix_length_and_dec()
>   of the parental items as a lot of these methods have
>   Item::val_...() calls inside.
>   We have to make subqueries non-const to prevent unnecessary
>   subquery evaluation. At the moment we have different methods
>   for this. Here is a list of these modes:
>   
>   1. PREPARE stmt;
>   We use UNCACHEABLE_PREPARE flag.
>   It is set during parsing in sql_parse.cc, mysql_new_select() for
>   each SELECT_LEX object and cleared at the end of PREPARE in
>   sql_prepare.cc, init_stmt_after_parse(). If this flag is set
>   subquery becomes non-const and evaluation does not happen.
>   
>   2. CREATE|ALTER VIEW, SHOW CREATE VIEW, I_S tables which
>      process FRM files
>   We use LEX::view_prepare_mode field. We set it before
>   view preparation and check this flag in
>   ::fix_fields(), ::fix_length_and_dec().
>   Some bugs are fixed using this approach,
>   some are not(Bug#57352, Bug#57703). The problem here is
>   that we have a lot of ::fix_fields(), ::fix_length_and_dec()
>   where we use Item::val_...() calls for const items.
>   
>   3. Derived tables with subquery = wrong result(Bug19077)
>   The reason of this bug is too early subquery evaluation.
>   It was fixed by adding Item::with_subselect field
>   The check of this field in appropriate places prevents
>   const item evaluation if the item have subquery.
>   The fix for Bug19077 fixes only the problem with
>   convert_constant_item() function and does not cover
>   other places(::fix_fields(), ::fix_length_and_dec() again)
>   where subqueries could be evaluated.
>   
>   Example:
>   CREATE TABLE t1 (i INT, j BIGINT);
>   INSERT INTO t1 VALUES (1, 2), (2, 2), (3, 2);
>   SELECT * FROM (SELECT MIN(i) FROM t1
>   WHERE j = SUBSTRING('12', (SELECT * FROM (SELECT MIN(j) FROM t1) t2))) t3;
>   DROP TABLE t1;
>   
>   4. Derived tables with subquery where subquery
>      is evaluated before table locking(Bug#54475, Bug#52157)
>   
>   Suggested solution is following:
>   
>   -Introduce new field LEX::context_analysis_only with the following
>    possible flags:
>    #define CONTEXT_ANALYSIS_ONLY_PREPARE 1
>    #define CONTEXT_ANALYSIS_ONLY_VIEW    2
>    #define CONTEXT_ANALYSIS_ONLY_DERIVED 4
>   -Set/clean these flags when we perform
>    context analysis operation
>   -Item_subselect::const_item() returns
>    result depending on LEX::context_analysis_only.
>    If context_analysis_only is set then we return
>    FALSE that means that subquery is non-const.
>    As all subquery types are wrapped by Item_subselect
>    it allow as to make subquery non-const when
>    it's necessary.

> ------------------------------------------------------------
> revno: 3520
> revision-id: sergey.glukhov@oracle.com-20101214093303-wmo9mqcb8rz0wv9f
> parent: tor.didriksen@oracle.com-20101213161301-81lprlbune7r98dl
> committer: Sergey Glukhov <sergey.glukhov@oracle.com>
> branch nick: mysql-5.1-bugteam
> timestamp: Tue 2010-12-14 12:33:03 +0300
> message:
>   Fixed following problems:
>   --Bug#52157 various crashes and assertions with multi-table update, stored function
>   --Bug#54475 improper error handling causes cascading crashing failures in innodb/ndb
>   --Bug#57703 create view cause Assertion failed: 0, file .\item_subselect.cc, line 846
>   --Bug#57352 valgrind warnings when creating view
>   --Recently discovered problem when a nested materialized derived table is used
>     before being populated and it leads to incorrect result
>   
>   We have several modes when we should disable subquery evaluation.
>   The reasons for disabling are different. It could be
>   uselessness of the evaluation as in case of 'CREATE VIEW'
>   or 'PREPARE stmt', or we should disable subquery evaluation
>   if tables are not locked yet as it happens in bug#54475, or
>   too early evaluation of subqueries can lead to wrong result
>   as it happened in Bug#19077.
>   Main problem is that if subquery items are treated as const
>   they are evaluated in ::fix_fields(), ::fix_length_and_dec()
>   of the parental items as a lot of these methods have
>   Item::val_...() calls inside.
>   We have to make subqueries non-const to prevent unnecessary
>   subquery evaluation. At the moment we have different methods
>   for this. Here is a list of these modes:
>   
>   1. PREPARE stmt;
>   We use UNCACHEABLE_PREPARE flag.
>   It is set during parsing in sql_parse.cc, mysql_new_select() for
>   each SELECT_LEX object and cleared at the end of PREPARE in
>   sql_prepare.cc, init_stmt_after_parse(). If this flag is set
>   subquery becomes non-const and evaluation does not happen.
>   
>   2. CREATE|ALTER VIEW, SHOW CREATE VIEW, I_S tables which
>      process FRM files
>   We use LEX::view_prepare_mode field. We set it before
>   view preparation and check this flag in
>   ::fix_fields(), ::fix_length_and_dec().
>   Some bugs are fixed using this approach,
>   some are not(Bug#57352, Bug#57703). The problem here is
>   that we have a lot of ::fix_fields(), ::fix_length_and_dec()
>   where we use Item::val_...() calls for const items.
>   
>   3. Derived tables with subquery = wrong result(Bug19077)
>   The reason of this bug is too early subquery evaluation.
>   It was fixed by adding Item::with_subselect field
>   The check of this field in appropriate places prevents
>   const item evaluation if the item have subquery.
>   The fix for Bug19077 fixes only the problem with
>   convert_constant_item() function and does not cover
>   other places(::fix_fields(), ::fix_length_and_dec() again)
>   where subqueries could be evaluated.
>   
>   Example:
>   CREATE TABLE t1 (i INT, j BIGINT);
>   INSERT INTO t1 VALUES (1, 2), (2, 2), (3, 2);
>   SELECT * FROM (SELECT MIN(i) FROM t1
>   WHERE j = SUBSTRING('12', (SELECT * FROM (SELECT MIN(j) FROM t1) t2))) t3;
>   DROP TABLE t1;
>   
>   4. Derived tables with subquery where subquery
>      is evaluated before table locking(Bug#54475, Bug#52157)
>   
>   Suggested solution is following:
>   
>   -Introduce new field LEX::context_analysis_only with the following
>    possible flags:
>    #define CONTEXT_ANALYSIS_ONLY_PREPARE 1
>    #define CONTEXT_ANALYSIS_ONLY_VIEW    2
>    #define CONTEXT_ANALYSIS_ONLY_DERIVED 4
>   -Set/clean these flags when we perform
>    context analysis operation
>   -Item_subselect::const_item() returns
>    result depending on LEX::context_analysis_only.
>    If context_analysis_only is set then we return
>    FALSE that means that subquery is non-const.
>    As all subquery types are wrapped by Item_subselect
>    it allow as to make subquery non-const when
>    it's necessary.

It includes speed optimizations for HANDLER READ by caching as much as possible in HANDLER OPEN
Other things:
- Added mysqld option --disable-thr-alarm to be able to benchmark things without thr_alarm
- Changed 'Locked' state to 'System lock' and 'Table lock' (these where used in the code but never shown to end user)
- Better error message if mysql_install_db.sh fails
- Moved handler function prototypes to sql_handler.h
- Remove not anymore used 'thd->locked' member


include/thr_alarm.h:
  Added my_disable_thr_alarm
include/thr_lock.h:
  Add new member to THR_LOCK_DATA to remember original lock type state. This is needed as thr_unlock() resets type to TL_UNLOCK.
mysql-test/include/check_no_concurrent_insert.inc:
  Locked -> Table lock
mysql-test/include/handler.inc:
  Locked -> Table lock
mysql-test/r/handler_innodb.result:
  Updated results for new tests
mysql-test/r/handler_myisam.result:
  Updated results for new tests
mysql-test/r/sp-threads.result:
  Locked -> Table lock
mysql-test/suite/binlog/t/binlog_stm_row.test:
  Locked -> Table lock
mysql-test/suite/funcs_1/datadict/processlist_val.inc:
  Locked -> Table lock
mysql-test/suite/pbxt/t/lock_multi.test:
  Locked -> Table lock
mysql-test/suite/sys_vars/r/concurrent_insert_func.result:
  Locked -> Table lock
mysql-test/suite/sys_vars/t/concurrent_insert_func.test:
  Locked -> Table lock
mysql-test/suite/sys_vars/t/delayed_insert_limit_func.test:
  Locked -> Table lock
mysql-test/suite/sys_vars/t/query_cache_wlock_invalidate_func.test:
  Locked -> Table lock
mysql-test/suite/sys_vars/t/sql_low_priority_updates_func.test:
  Locked -> Table lock
mysql-test/t/insert_notembedded.test:
  Locked -> Table lock
mysql-test/t/lock_multi.test:
  Locked -> Table lock
mysql-test/t/merge-big.test:
  Locked -> Table lock
mysql-test/t/multi_update.test:
  Locked -> Table lock
mysql-test/t/query_cache_28249.test:
  Locked -> Table lock
mysql-test/t/sp_notembedded.test:
  Locked -> Table lock
mysql-test/t/sp_sync.test:
  Locked -> Table lock
mysql-test/t/status.test:
  Locked -> Table lock
mysql-test/t/trigger_notembedded.test:
  Locked -> Table lock
mysys/thr_alarm.c:
  Added option to disable thr_alarm
mysys/thr_lock.c:
  Detect loops
scripts/mysql_install_db.sh:
  Give better error message if something goes wrong
sql/Makefile.am:
  Added sql_handler.h
sql/lock.cc:
  Split functions to allow one to cache value if store_lock() (for HANDLER functions).
  - Split mysql_lock_tables() into two functions, where first one allocates MYSQL_LOCK and other other one uses it.
  - Made get_lock_data() an external function.
  - Added argument to mysql_unlock_tables() to not free sql_lock.
  - Added argument to reset_lock_data() to reset lock structure to initial state (as after get_lock_data())
sql/mysql_priv.h:
  Moved handler function prototypes to sql_handler.h
  Added new lock functions.
sql/mysqld.cc:
  Added --thread-alarm startup option
sql/net_serv.cc:
  Don't call vio_blocking() if not needed
sql/sql_base.cc:
  include sql_handler.h
sql/sql_class.cc:
  include sql_handler.h
  Remove not anymore used 'thd->locked' member
sql/sql_class.h:
  Remove not anymore used 'thd->locked' member
sql/sql_db.cc:
  include sql_handler.h
sql/sql_delete.cc:
  include sql_handler.h
sql/sql_handler.cc:
  Rewrote all code to use SQL_HANDLER instead of TABLE_LIST (original interface)
  Rewrote mysql_ha_open() to cache all things from TABLE_LIST and items for field list, where etc.
  In mysql_ha_open() also cache MYSQL_LOCK structure from get_lock_data().
  Split functions into smaller sub functions (needed to be able to implement mysql_ha_read_prepare())
  Added mysql_ha_read_prepare() to allow one to prepare HANDLER READ.
sql/sql_handler.h:
  Interface to sql_handler.cc
sql/sql_parse.cc:
  include sql_handler.h
sql/sql_prepare.cc:
  Added mysql_test_handler_read(), prepare for HANDLER READ
sql/sql_rename.cc:
  include sql_handler.h
sql/sql_show.cc:
  Removed usage of thd->locked
sql/sql_table.cc:
  include sql_handler.h
sql/sql_trigger.cc:
  include sql_handler.h

--Bug#52157 various crashes and assertions with multi-table update, stored function
--Bug#54475 improper error handling causes cascading crashing failures in innodb/ndb
--Bug#57703 create view cause Assertion failed: 0, file .\item_subselect.cc, line 846
--Bug#57352 valgrind warnings when creating view
--Recently discovered problem when a nested materialized derived table is used
  before being populated and it leads to incorrect result

We have several modes when we should disable subquery evaluation.
The reasons for disabling are different. It could be
uselessness of the evaluation as in case of 'CREATE VIEW'
or 'PREPARE stmt', or we should disable subquery evaluation
if tables are not locked yet as it happens in bug#54475, or
too early evaluation of subqueries can lead to wrong result
as it happened in Bug#19077.
Main problem is that if subquery items are treated as const
they are evaluated in ::fix_fields(), ::fix_length_and_dec()
of the parental items as a lot of these methods have
Item::val_...() calls inside.
We have to make subqueries non-const to prevent unnecessary
subquery evaluation. At the moment we have different methods
for this. Here is a list of these modes:

1. PREPARE stmt;
We use UNCACHEABLE_PREPARE flag.
It is set during parsing in sql_parse.cc, mysql_new_select() for
each SELECT_LEX object and cleared at the end of PREPARE in
sql_prepare.cc, init_stmt_after_parse(). If this flag is set
subquery becomes non-const and evaluation does not happen.

2. CREATE|ALTER VIEW, SHOW CREATE VIEW, I_S tables which
   process FRM files
We use LEX::view_prepare_mode field. We set it before
view preparation and check this flag in
::fix_fields(), ::fix_length_and_dec().
Some bugs are fixed using this approach,
some are not(Bug#57352, Bug#57703). The problem here is
that we have a lot of ::fix_fields(), ::fix_length_and_dec()
where we use Item::val_...() calls for const items.

3. Derived tables with subquery = wrong result(Bug19077)
The reason of this bug is too early subquery evaluation.
It was fixed by adding Item::with_subselect field
The check of this field in appropriate places prevents
const item evaluation if the item have subquery.
The fix for Bug19077 fixes only the problem with
convert_constant_item() function and does not cover
other places(::fix_fields(), ::fix_length_and_dec() again)
where subqueries could be evaluated.

Example:
CREATE TABLE t1 (i INT, j BIGINT);
INSERT INTO t1 VALUES (1, 2), (2, 2), (3, 2);
SELECT * FROM (SELECT MIN(i) FROM t1
WHERE j = SUBSTRING('12', (SELECT * FROM (SELECT MIN(j) FROM t1) t2))) t3;
DROP TABLE t1;

4. Derived tables with subquery where subquery
   is evaluated before table locking(Bug#54475, Bug#52157)

Suggested solution is following:

-Introduce new field LEX::context_analysis_only with the following
 possible flags:
 #define CONTEXT_ANALYSIS_ONLY_PREPARE 1
 #define CONTEXT_ANALYSIS_ONLY_VIEW    2
 #define CONTEXT_ANALYSIS_ONLY_DERIVED 4
-Set/clean these flags when we perform
 context analysis operation
-Item_subselect::const_item() returns
 result depending on LEX::context_analysis_only.
 If context_analysis_only is set then we return
 FALSE that means that subquery is non-const.
 As all subquery types are wrapped by Item_subselect
 it allow as to make subquery non-const when
 it's necessary.


mysql-test/r/derived.result:
  test case
mysql-test/r/multi_update.result:
  test case
mysql-test/r/view.result:
  test case
mysql-test/suite/innodb/r/innodb_multi_update.result:
  test case
mysql-test/suite/innodb/t/innodb_multi_update.test:
  test case
mysql-test/suite/innodb_plugin/r/innodb_multi_update.result:
  test case
mysql-test/suite/innodb_plugin/t/innodb_multi_update.test:
  test case
mysql-test/t/derived.test:
  test case
mysql-test/t/multi_update.test:
  test case
mysql-test/t/view.test:
  test case
sql/item.cc:
  --removed unnecessary code
sql/item_cmpfunc.cc:
  --removed unnecessary checks
  --THD::is_context_analysis_only() is replaced with LEX::is_ps_or_view_context_analysis()
sql/item_func.cc:
  --refactored context analysis checks
sql/item_row.cc:
  --removed unnecessary checks
sql/item_subselect.cc:
  --removed unnecessary code
  --added DBUG_ASSERT into Item_subselect::exec()
    which asserts that subquery execution can not happen
    if LEX::context_analysis_only is set, i.e. at context
    analysis stage.
  --Item_subselect::const_item()
    Return FALSE if LEX::context_analysis_only is set.
    It prevents subquery evaluation in ::fix_fields &
    ::fix_length_and_dec at context analysis stage.
sql/item_subselect.h:
  --removed unnecessary code
sql/mysql_priv.h:
  --Added new set of flags.
sql/sql_class.h:
  --removed unnecessary code
sql/sql_derived.cc:
  --added LEX::context_analysis_only analysis intialization/cleanup
sql/sql_lex.cc:
  --init LEX::context_analysis_only field
sql/sql_lex.h:
  --New LEX::context_analysis_only field
sql/sql_parse.cc:
  --removed unnecessary code
sql/sql_prepare.cc:
  --removed unnecessary code
  --added LEX::context_analysis_only analysis intialization/cleanup
sql/sql_select.cc:
  --refactored context analysis checks
sql/sql_show.cc:
  --added LEX::context_analysis_only analysis intialization/cleanup
sql/sql_view.cc:
  --added LEX::context_analysis_only analysis intialization/cleanup

--Bug#52157 various crashes and assertions with multi-table update, stored function
--Bug#54475 improper error handling causes cascading crashing failures in innodb/ndb
--Bug#57703 create view cause Assertion failed: 0, file .\item_subselect.cc, line 846
--Bug#57352 valgrind warnings when creating view
--Recently discovered problem when a nested materialized derived table is used
  before being populated and it leads to incorrect result

We have several modes when we should disable subquery evaluation.
The reasons for disabling are different. It could be
uselessness of the evaluation as in case of 'CREATE VIEW'
or 'PREPARE stmt', or we should disable subquery evaluation
if tables are not locked yet as it happens in bug#54475, or
too early evaluation of subqueries can lead to wrong result
as it happened in Bug#19077.
Main problem is that if subquery items are treated as const
they are evaluated in ::fix_fields(), ::fix_length_and_dec()
of the parental items as a lot of these methods have
Item::val_...() calls inside.
We have to make subqueries non-const to prevent unnecessary
subquery evaluation. At the moment we have different methods
for this. Here is a list of these modes:

1. PREPARE stmt;
We use UNCACHEABLE_PREPARE flag.
It is set during parsing in sql_parse.cc, mysql_new_select() for
each SELECT_LEX object and cleared at the end of PREPARE in
sql_prepare.cc, init_stmt_after_parse(). If this flag is set
subquery becomes non-const and evaluation does not happen.

2. CREATE|ALTER VIEW, SHOW CREATE VIEW, I_S tables which
   process FRM files
We use LEX::view_prepare_mode field. We set it before
view preparation and check this flag in
::fix_fields(), ::fix_length_and_dec().
Some bugs are fixed using this approach,
some are not(Bug#57352, Bug#57703). The problem here is
that we have a lot of ::fix_fields(), ::fix_length_and_dec()
where we use Item::val_...() calls for const items.

3. Derived tables with subquery = wrong result(Bug19077)
The reason of this bug is too early subquery evaluation.
It was fixed by adding Item::with_subselect field
The check of this field in appropriate places prevents
const item evaluation if the item have subquery.
The fix for Bug19077 fixes only the problem with
convert_constant_item() function and does not cover
other places(::fix_fields(), ::fix_length_and_dec() again)
where subqueries could be evaluated.

Example:
CREATE TABLE t1 (i INT, j BIGINT);
INSERT INTO t1 VALUES (1, 2), (2, 2), (3, 2);
SELECT * FROM (SELECT MIN(i) FROM t1
WHERE j = SUBSTRING('12', (SELECT * FROM (SELECT MIN(j) FROM t1) t2))) t3;
DROP TABLE t1;

4. Derived tables with subquery where subquery
   is evaluated before table locking(Bug#54475, Bug#52157)

Suggested solution is following:

-Introduce new field LEX::context_analysis_only with the following
 possible flags:
 #define CONTEXT_ANALYSIS_ONLY_PREPARE 1
 #define CONTEXT_ANALYSIS_ONLY_VIEW    2
 #define CONTEXT_ANALYSIS_ONLY_DERIVED 4
-Set/clean these flags when we perform
 context analysis operation
-Item_subselect::const_item() returns
 result depending on LEX::context_analysis_only.
 If context_analysis_only is set then we return
 FALSE that means that subquery is non-const.
 As all subquery types are wrapped by Item_subselect
 it allow as to make subquery non-const when
 it's necessary.

inited==INDEX

When an error occurs while sending the data in a temporary table there was no
cleanup performed. This caused a failed assertion in the case when different
access methods were used for populating the table vs. retrieving the data from
the table if IGNORE was specified and sql_safe_updates = 0. In this case
execution continues, but the handler expects to continue with the access
method used for row retrieval.

Fixed by doing the cleanup even if errors occur.

inited==INDEX

When an error occurs while sending the data in a temporary table there was no
cleanup performed. This caused a failed assertion in the case when different
access methods were used for populating the table vs. retrieving the data from
the table if IGNORE was specified and sql_safe_updates = 0. In this case
execution continues, but the handler expects to continue with the access
method used for row retrieval.

Fixed by doing the cleanup even if errors occur.

FLUSH TABLES <list> WITH READ LOCK are incompatible" to
be pushed as separate patch.

Replaced thread state name "Waiting for table", which was
used by threads waiting for a metadata lock or table flush, 
with a set of names which better reflect types of resources
being waited for.

Also replaced "Table lock" thread state name, which was used 
by threads waiting on thr_lock.c table level lock, with more
elaborate "Waiting for table level lock", to make it 
more consistent with other thread state names.

Updated test cases and their results according to these 
changes.

Fixed sys_vars.query_cache_wlock_invalidate_func test to not
to wait for timeout of wait_condition.inc script.

mysql-test/r/query_cache.result:
  Added test coverage for query_cache_wlock_invalidate
  behavior for implicitly locked tables.
mysql-test/suite/sys_vars/r/query_cache_wlock_invalidate_func.result:
  Fixed sys_vars.query_cache_wlock_invalidate_func test to not
  to wait for timeout of wait_condition.inc script. Reverted
  changes to test which introduced timeout and replaced waiting
  condition with a more appropriate one.
  Test coverage for query_cache_wlock_invalidate behavior for
  implicitly locked tables was added to query_cache.test.
mysql-test/suite/sys_vars/t/query_cache_wlock_invalidate_func.test:
  Fixed sys_vars.query_cache_wlock_invalidate_func test to not
  to wait for timeout of wait_condition.inc script. Reverted
  changes to test which introduced timeout and replaced waiting
  condition with a more appropriate one.
  Test coverage for query_cache_wlock_invalidate behavior for
  implicitly locked tables was added to query_cache.test.
mysql-test/t/query_cache.test:
  Added test coverage for query_cache_wlock_invalidate
  behavior for implicitly locked tables.
mysys/thr_lock.c:
  Replaced "Table lock" thread state name, which was used by 
  threads waiting on thr_lock.c table level lock, with more
  elaborate "Waiting for table level lock", to make it 
  consistent with thread state names which are used while
  waiting for metadata locks and table flush.
sql/mdl.cc:
  Replaced thread state name "Waiting for table", which was
  used by threads waiting for a metadata lock or table flush, 
  with a set of names which better reflect types of resources
  being waited for. 
  
  To implement this:
  - Adjusted MDL_wait::timed_wait() to take thread state name
    as parameter.
  - Introduced method of MDL_key class which allows to get
    thread state name to be used while waiting for resource
    corresponding to the key and changed code to use it.
    Added array translating namespaces to thread state names
    as part of this change.
sql/mdl.h:
  To implement this:
  - Adjusted MDL_wait::timed_wait() to take thread state name
    as parameter.
  - Introduced method of MDL_key class which allows to get
    thread state name to be used while waiting for resource
    corresponding to the key and changed code to use it.
    Added array translating namespaces to thread state names
    as part of this change.
sql/sql_base.cc:
  Replaced thread state name "Waiting for table", which was
  used by threads waiting for table flush, with a more elaborate
  "Waiting for table flush".