EXPLAIN EXTENDED warning.

Query optimizer searches for the constant tables and optimizes them away. This
means that fields of such tables are substituted for their values and on later
phases they are treated as constants. After this constant tables are removed
from the query execution plan. Nevertheless constant tables were shown in 
the EXPLAIN EXTENDED warning thus producing query that might be not an
equivalent of the original query.
        
Now the print_join function skips all tables that were optimized away from
printing to the EXPLAIN EXTENDED warning. If all tables were optimized away it
produces the 'FROM dual' clause.

  field references

This error requires a combination of factors : 
1. An "impossible where" in the outermost SELECT
2. An aggregate in the outermost SELECT
3. A correlated subquery with a WHERE clause that includes an outer 
field reference as a top level WHERE sargable predicate

When JOIN::optimize detects an "impossible WHERE" it will bail out
without doing the rest of the work and initializations. It will not
call make_join_statistics() as well.  And make_join_statistics fills 
in various structures for each table referenced.
When processing the result of the "impossible WHERE" the query must
send a single row of data if there are aggregate functions in it.
In this case the server marks all the aggregates as having received 
no rows and calls the relevant Item::val_xxx() method on the SELECT
list. However if this SELECT list happens to contain a correlated 
subquery this subquery is evaluated in a normal evaluation mode.
And if this correlated subquery has a reference to a field from the 
outermost "impossible where" SELECT the add_key_fields will mistakenly
consider the outer field reference as a "local" field reference when 
looking for sargable predicates.
But since the SELECT where the outer field reference refers to is not
completely initialized due to the "impossible WHERE" in this level
we'll get a NULL pointer reference.
Fixed by making a better condition for discovering if a field is "local"
to the SELECT level being processed. 
It's not enough to look for OUTER_REF_TABLE_BIT in this case since 
for outer references to constant tables the Item_field::used_tables() 
will return 0 regardless of whether the field reference is from the 
local SELECT or not.

The crash happens because select_union object is used as result set
for queries which have derived tables.
select_union use temporary table as data storage and if
fields count exceeds 10(count of values for procedure ANALYSE())
then we get a crash on fill_record() function.

When during the optimization an item is moved to the upper select
the item's context left unchanged. This caused wrong result in the 
PS/SP mode.
The Item_ident::remove_dependence_processor now sets the context
of the select to which the item is moved to.

In a subselect all fields from outer selects are marked as dependent on
selects they are belong to. In some cases optimizer substitutes it for an
equivalent expression. For example "a_field IN (SELECT outer_field)" is
substituted with "a_field = outer_field". As we moved the outer_field to the
upper select it's not really outer anymore. But it was left marked as outer.
If exists an index over a_field optimizer choose wrong execution plan and thus
return wrong result.

Now the Item_in_subselect::single_value_transformer function removes dependent
marking from fields when a subselect is optimized away.

Bug#42003 tests missing the disconnect of connections <> default
second slice
Content:
1. wait_until_count_sessions.inc
   - One PB run of a test using this routine failed because
     5 seconds timeout were exceeded. Although I have some doubts
     if the assigned timeout was really too small, I increase the
     value to 10. We waste the additional 5 seconds only if the
     tests fails anyway.
   - Print the content of the PROCESSLIST if the poll routine fails
2. minor improvements of formatting
3. query_cache_notembedded:
   Activate the wait_until_count_sessions.inc routine which was
   unfortunately forgotten in the changeset before.

   - If missing: add "disconnect <session>"
   - If physical disconnect of non "default" sessions is not finished
     at test end: add routine which waits till this happened
+ additional improvements
  - remove superfluous files created by the test
  - replace error numbers by error names
  - remove trailing spaces, replace tabs by spaces
  - unify writing of bugs within comments
  - correct comments
  - minor changes of formatting
Fixed tests:
  backup
  check
  compress
  grant
  information_schema
  multi_update
  overflow
  packet
  query_cache_not_embedded
  sp-threads
  subselect
  synchronization
  timezone_grant

Item_in_optimizer::is_null() evaluated "NULL IN (SELECT ...)" to NULL regardless of
whether subquery produced any records, this was a documented limitation.

The limitation has been removed (see bugs 8804, 24085, 24127) now
Item_in_optimizer::val_int() correctly handles all cases with NULLs. Make
Item_in_optimizer::is_null() invoke val_int() to return correct values for
"NULL IN (SELECT ...)".

Bug#34760 Character set autodetection appears to fail
the problem is the same as reported in bug#20835,
so the fix is backport of bug#20835 patch.

Original changeset:
> revision-id: sergey.glukhov@sun.com-20081121123959-58ffhp2nitg7f40h
> parent: ramil@mysql.com-20081120100836-gct60cm67b1rui29
> committer: Sergey Glukhov <Sergey.Glukhov@sun.com>
> branch nick: mysql-5.0-bugteam
> timestamp: Fri 2008-11-21 16:39:59 +0400

enable uncacheable flag if we update a view with check option
and check option has a subselect, otherwise, the check option
can be evaluated after the subselect was freed as independent
(See full_local in JOIN::join_free())

the problem is the same as reported in bug#20835,
so the fix is backport of bug#20835 patch.

      
When switching to indexed ORDER BY we must be sure to reset the index read
flag if we are switching from a covering index to non-covering.

Calling List<Cached_item>::delete_elements for the same list twice
caused a crash of the server in the function JOIN::cleaunup.
Ensured that delete_elements() in JOIN::cleanup would be called only once.

The file tree in mtr's vardir has changed so an old
hard-coded path in mysql-test/t/subselect.test didn't
work.
Fix: update the paths in the test.

 - moved the test into a separate file to check for presence of the test variable

When there is an error executing EXISTS predicates they return NULL as their string
or decimal value but don't set the NULL value flag.
Fixed by returning 0 (as a decimal or a string) on error exectuting the subquery.
Note that we can't return NULL as EXISTS is not supposed to return NULL.

The value of JOIN::tables must be set to 0 when there
is no matching min/max row.

  updated the testcase for bug 36011

   with dependent subqueries
An IN subquery is executed on EXPLAIN when it's not correlated.
If the subquery required a temporary table for its execution
not all the internal structures were restored from pointing to
the items of the temporary table to point back to the items of
the subquery.
Fixed by restoring the ref array when a temp tables were used in
executing the IN subquery during EXPLAIN EXTENDED.

  Post-commit minor cleanup of testcase (bug#36139).

- Make convert_zerofill_number_to_string() take into account that the 
  constant it is converting may evaluate to NULL.

                    Leopard.

The change_user test failed because results of some statements
depended on platform and server build flags.

subselect_notembedded failure was a result of a bad merge from 5.0.

Fixed the corresponding test cases.

The test case for the bug#31048 checks that there is no crash on stack
overrun. But due to different stack sizes on different platforms it failed
on some of them.

The new test case check that a query with at least 4 level subquery nesting
works without the stack overrun nesting and other levels of nesting doesn't
cause a crash.

but not collation.

The problem here was that text literals in a view were always
dumped with character set introducer. That lead to loosing
collation information.

The fix is to dump character set introducer only if it was
in the original query. That is now possible because there 
is no problem any more of loss of character set of string
literals in views -- after WL#4052 the view is dumped 
in the original character set.

of cleanups in the test case for bug33794.

The problem occurred when one had a subquery that had an equality X=Y where 
Y referred to a named select list expression from the parent select. MySQL 
crashed when trying to use the X=Y equality for ref-based access. 

Fixed by allowing non-Item_field items in the described case.

server crash.

The filesort implementation has an optimization for subquery execution which
consists of reusing previously allocated buffers. In particular the call to
the read_buffpek_from_file function might be skipped when a big enough buffer
for buffer descriptors (buffpeks) is already allocated. Beside allocating
memory for buffpeks this function fills allocated buffer with data read from
disk. Skipping it might led to using an arbitrary memory as fields' data and
finally to a crash.

Now the read_buffpek_from_file function is always called. It allocates
new buffer only when necessary, but always fill it with correct data.

Default values of variables were not subject to upper/lower bounds
and step, while setting variables was. Bounds and step are also
applied to defaults now; defaults are corrected quietly, values
given by the user are corrected, and a correction-warning is thrown
as needed. Lastly, very large values could wrap around, starting
from 0 again. They are bounded at the maximum value for the
respective data-type now if no lower maximum is specified in the
variable's definition.

  Test case for the bug#31048 is moved to the subselect_notembedded tests as the embedded server isn't affected by this bug.

  crashes MySQL 5.122
There was a difference in how UNIONs are handled
on top level and when in sub-query.
Because the rules for sub-queries were syntactically
allowing cases that are not currently supported by
the server we had crashes (this bug) or wrong results
(bug 32051).
Fixed by making the syntax rules for UNIONs match the 
ones at top level.

These rules however do not support nesting UNIONs, e.g.
(SELECT a FROM t1 UNION ALL SELECT b FROM t2) 
 UNION
(SELECT c FROM t3 UNION ALL SELECT d FROM t4)
Supports for statements with nested UNIONs will be
added in a future version.

  Fix for the bug#31048 for 64bit platforms.
subselect.test, subselect.result:
  Corrected text case for the bug#31048.

Index lookup does not always guarantee that we can
simply remove the relevant conditions from the WHERE
clause. Reasons can be e.g. conversion errors, 
partial indexes etc. 
The optimizer was removing these parts of the WHERE 
condition without any further checking.
This leads to "false positives" when using indexes.
Fixed by checking the index reference conditions
(using WHERE) when using indexes with sub-queries.

 only on some occasions

Referencing an element from the SELECT list in a WHERE 
clause is not permitted. The namespace of the WHERE
clause is the table columns only. This was not enforced
correctly when resolving outer references in sub-queries.

Fixed by not allowing references to aliases in a 
sub-query in WHERE.

This bug is actually two. The first one manifests itself on an EXPLAIN
SELECT query with nested subqueries that employs the filesort algorithm.
The whole SELECT under explain is marked as UNCACHEABLE_EXPLAIN to preserve
some temporary structures for explain. As a side-effect of this values of
nested subqueries weren't cached and subqueries were re-evaluated many
times. Each time buffer for filesort was allocated but wasn't freed because
freeing occurs at the end of topmost SELECT. Thus all available memory was
eaten up step by step and OOM event occur.
The second bug manifests itself on SELECT queries with conditions where
a subquery result is compared with a key field and the subquery itself also
has such condition. When a long chain of such nested subqueries is present
the stack overrun occur. This happens because at some point the range optimizer
temporary puts the PARAM structure on the stack. Its size if about 8K and
the stack is exhausted very fast.

Now the subselect_single_select_engine::exec function allows subquery result
caching when the UNCACHEABLE_EXPLAIN flag is set.
Now the SQL_SELECT::test_quick_select function calls the check_stack_overrun
function for stack checking purposes to prevent server crash.

After adding an index the <VARBINARY> IN (SELECT <BINARY> ...)
clause returned a wrong result: the VARBINARY value was illegally padded
with zero bytes to the length of the BINARY column for the index search.
(<VARBINARY>, ...) IN (SELECT <BINARY>, ... ) clauses are affected too.