ORDER BY could cause a server crash

Dependent subqueries like

  SELECT COUNT(*) FROM t1, t2 WHERE t2.b
   IN (SELECT DISTINCT t2.b FROM t2 WHERE t2.b = t1.a)

caused a memory leak proportional to the
number of outer rows.


The make_simple_join() function has been modified to
JOIN class method to store join_tab_reexec and
table_reexec values in the parent join only
(make_simple_join of tmp_join may access these values
via 'this' pointer of the parent JOIN).

NOTE: this patch doesn't include standard test case (this is
"out of memory" bug). See bug #42037 page for test cases.


sql/sql_select.cc:
  Bug #42037: Queries containing a subquery with DISTINCT and
              ORDER BY could cause a server crash
  
  The make_simple_join() function has been modified to
  JOIN class method to store join_tab_reexec and
  table_reexec values in the parent join only.
sql/sql_select.h:
  Bug #42037: Queries containing a subquery with DISTINCT and
              ORDER BY could cause a server crash
  
  1. The make_simple_join() function has been modified to
     JOIN class method.
  
  2. Type of JOIN::table_reexec field has been changed from
     TABLE** to TABLE *table_reexec[1]: this field always was
     NULL or a pointer to one-element array of pointers, so
     a pointer to a pointer has been replaced with one pointer
     and unnecessary memory allocation has been eliminated.

Problem: some queries using NAME_CONST(.. COLLATE ...)
lead to server crash due to failed type cast.

Fix: return the underlying item's type in case of
NAME_CONST(.. COLLATE ...) to avoid wrong casting.


mysql-test/r/func_misc.result:
  Fix for bug#42014: Crash, name_const with coll
    - test result.
mysql-test/t/func_misc.test:
  Fix for bug#42014: Crash, name_const with coll
    - test case.
sql/item.cc:
  Fix for bug#42014: Crash, name_const with coll
    - in case of NAME_CONST('name', 'value' COLLATE collation)
  Item_name_const::type() returns type of 'value' argument 
  to awoid wrong type casting of the Item_name_const items.

Accessing well defined MERGE table may return an error
stating that the merge table is incorrectly defined. This
happens if MERGE child tables were accessed before and we
failed to open another incorrectly defined MERGE table in
this connection.

myrg_open() internally used my_errno as a variable for determining
failure, and thus could be tricked into a wrong decision by other
uses of my_errno.

With this fix we use function local boolean flag instead of my_errno
to determine failure.

myisammrg/myrg_open.c:
  There are two requirement for accessing/setting my_errno variable,
  which were not followed by myrg_open():
  - it must be checked immediately after a function returned an error. There
    must be no calls to other functions that may change it's value between.
  - my_errno value must be set right before a function is going to return an
    error. There must be no calls to other functions that may change it's
    value between (that's why we have these tricks with save_errno at the
    bottom of myrg_open()).
  
  myrg_open() internally used my_errno as a variable for determining
  failure, and thus could be tricked into a wrong decision by other
  uses of my_errno.
mysql-test/r/merge.result:
  A test case for BUG#32047.
mysql-test/t/merge.test:
  A test case for BUG#32047.

The original symptoms of this bug have been fixed as a consequence of other bug fixes.
Taking this time to correct some formatting, such as replacing error numbers with names.
Beginning this with 5.0

   - 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 like
  - 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
Modifications according to the code review are included.
Fixed tests:
grant2
grant3
lock_tables_lost_commit
mysqldump
openssl_1
outfile

When storing a NULL to a TIMESTAMP NOT NULL DEFAULT ...,
NULL returned from some functions threw a 'cannot be NULL error.'

NULL-returns now correctly result in the timestamp-field being
assigned its default value.

mysql-test/r/type_timestamp.result:
  Show that for TIMESTAMP NOT NULL DEFAULT, NULL function returns
  set default now.
mysql-test/t/type_timestamp.test:
  Show that for TIMESTAMP NOT NULL DEFAULT, NULL function returns
  set default now.
sql/item.cc:
  When storing a returned NULL (from a time- or date-related
  function), make sure to apply NULL-means-DEFAULT magic where
  applicable.

            
If the system time is adjusted back during a query execution
(resulting in the end time being earlier than the start time)
the code that prints to the slow query log gets confused and 
prints unsigned negative numbers.
            
Fixed by not logging the statements that would have negative
execution time due to time shifts.
No test case since this would involve changing the system time.

sql/sql_parse.cc:
  Bug #35396: don't log queries with negative execution times 
  (due to shifts in system time).

            messed up

"ROW(...) IN (SELECT ... FROM DUAL)" always returned TRUE.

Item_in_subselect::row_value_transformer rewrites "ROW(...)
IN SELECT" conditions into the "EXISTS (SELECT ... HAVING ...)"
form.
For a subquery from the DUAL pseudotable resulting HAVING
condition is an expression on constant values, so further
transformation with optimize_cond() eliminates this HAVING
condition and resets JOIN::having to NULL.
Then JOIN::exec treated that NULL as an always-true-HAVING
and that caused a bug.

To distinguish an optimized out "HAVING TRUE" clause from
"HAVING FALSE" we already have the JOIN::having_value flag.
However, JOIN::exec() ignored JOIN::having_value as described
above as if it always set to COND_TRUE.

The JOIN::exec method has been modified to take into account
the value of the JOIN::having_value field.


mysql-test/r/subselect3.result:
  Added test case for bug #39069.
mysql-test/t/subselect3.test:
  Added test case for bug #39069.
sql/sql_select.cc:
  Bug #39069: <row constructor> IN <table-subquery> seriously
              messed up
  
  The JOIN::exec method has been modified to take into account
  the value of the JOIN::having_value field.

Dirty close tricky does not work on Windows.

mysql-test/r/query_cache_notembedded.result:
  Remove test case result.
mysql-test/t/query_cache_notembedded.test:
  Remove test case.

      
Re-generated the PKI files needed.
Removed the ones that are not needed.
Updated the tests to reference the correct SSL subject.

mysql-test/r/openssl_1.result:
  Bug #42366: Updated the tests to reference the correct SSL subject
mysql-test/std_data/cacert.pem:
  Bug #42366: Re-generated the PKI files needed.
mysql-test/std_data/client-cert.pem:
  Bug #42366: Re-generated the PKI files needed.
mysql-test/std_data/client-key.pem:
  Bug #42366: Re-generated the PKI files needed.
mysql-test/std_data/server-cert-des.pem:
  Bug #42366: Removed the ones that are not needed.
mysql-test/std_data/server-cert.pem:
  Bug #42366: Re-generated the PKI files needed.
mysql-test/std_data/server-key-des.pem:
  Bug #42366: Removed the ones that are not needed.
mysql-test/std_data/server-key.pem:
  Bug #42366: Re-generated the PKI files needed.
mysql-test/std_data/server8k-cert.pem:
  Bug #42366: Re-generated the PKI files needed.
mysql-test/std_data/server8k-key.pem:
  Bug #42366: Re-generated the PKI files needed.
mysql-test/t/openssl_1.test:
  Bug #42366: Updated the tests to reference the correct SSL subject

Check for existence of diff fails on Solaris due to unsupported "-v"
Fix is to do this check only on Windows where it was needed

packages, this is now corrected (Bug#42278)

The problem is that the query cache was storing partial results
if the statement failed when sending the results to the client.
This could cause clients to hang when trying to read the results
from the cache as they would, for example, wait indefinitely for
a eof packet that wasn't saved.

The solution is to always discard the caching of a query that
failed to send its results to the associated client.

mysql-test/r/query_cache_notembedded.result:
  Add test case result for Bug#40264
mysql-test/t/query_cache_notembedded.test:
  Add test case for Bug#40264
sql/sql_cache.cc:
  Abort if a unreported error was raised.

This is not the final merge of that release build, but we need early
access to these tool fixes (use of "awk" in the BDB configuration).

with mysql_change_user) to 5.0.

Views weren't sync()d the same way other structures were.

In creating the FRM for views, obey the same rules for variable 
"sync_frm" as for everything else.

The problem is that the query cache stores packets containing
the server status of the time when the cached statement was run.
This might lead to a wrong transaction status in the client side
if a statement is cached during a transaction and is later served
outside a transaction context (and vice-versa).

The solution is to take into account the transaction status when
storing in and serving from the query cache.

mysql-test/r/innodb_cache.result:
  Update test case result.
mysql-test/r/query_cache.result:
  Add test case result for Bug#36326
mysql-test/t/query_cache.test:
  Add test case for Bug#36326
sql/mysql_priv.h:
  Add new flags.
sql/sql_cache.cc:
  Remember the transaction and autocommit status stored in the packet.
tests/mysql_client_test.c:
  Add test case for Bug#36326

The default "awk" there cannot handle some of the scripts
which are used by BDB for configuration.

The fix:
1) Introduce a variable "AWK" in some of the BDB shell scripts,
2) search "gawk" and give it precedence over "awk"
   when assigning a value to the "AWK" variable,
   fail if neither is found,
3) use that variable when calling an "awk" program with one
   of the critical scripts.

The perfect solution would be to use the "awk" program found
by "configure", but we cannot follow that approach because
BDB's configuration is handled as a special case before the
overall "configure" is run. Because of this,
1) the "configure" result isn't yet available,
2) "configure" will not handle these BDB files.
Searching "gawk" is a (not-so-nice) way out.

Note that all this need not be perfectly portable,
it is needed only when we create a source distribution tarball
from a develkopment tree.

bdb/dist/s_all:
  Search "gawk" if available, give it precedence over "awk",
  fail if neither is found.
bdb/dist/s_include:
  Ensure we use a modern AWK, similar to GNU awk,
  the default awk on Solaris cannot handle BDB's script.
bdb/dist/s_recover:
  Ensure we use a modern AWK, similar to GNU awk,
  the default awk on Solaris cannot handle BDB's script.
bdb/dist/s_rpc:
  Ensure we use a modern AWK, similar to GNU awk,
  the default awk on Solaris cannot handle BDB's script.

      
The greedy optimizer tracks the current level of nested joins and the position
inside these by setting and maintaining a state that's global for the whole FROM
clause.
This state was correctly maintained inside the selection of the next partial plan
table (in best_extension_by_limited_search()). 
greedy_search() also moves the current position by adding the last partial match 
table when there's not enough tables in the partial plan found by 
best_extension_by_limited_search().
This may require update of the global state variables that describe the current
position in the plan if the last table placed by greedy_search is not a top-level 
join table.
Fixed by updating the state after placing the partial plan table in greedy_search()
in the same way this is done on entering the best_extension_by_limited_search().
Fixed the signature of the function called to update the state : 
check_interleaving_with_nj

mysql-test/r/greedy_optimizer.result:
  Bug #38795: test case
mysql-test/t/greedy_optimizer.test:
  Bug #38795: test case
sql/sql_select.cc:
  Bug #38795: correctly update current position when placing
  the next partial plan table in greedy_search().

Bug #39920: MySQL cannot deal with Leap Second expression in string literal.

Updated MySQL time handling code to react correctly on UTC leap second additions.
MySQL functions that return the OS current time, like e.g. CURDATE(), NOW() etc
will return :59:59 instead of :59:60 or 59:61.
As a result the reader will receive :59:59 for 2 or 3 consecutive seconds
during the leap second.

Original changesets:
> revision-id: kgeorge@mysql.com-20081201141835-rg8nnnadujj5wl9f
> parent: gshchepa@mysql.com-20081114172557-xh0jlzwal8ze3cy6
> committer: Georgi Kodinov <kgeorge@mysql.com>
> branch nick: B39920-5.0-bugteam
> timestamp: Mon 2008-12-01 16:18:35 +0200

> revision-id: kgeorge@mysql.com-20081201154106-c310zzy5or043rqa
> parent: kgeorge@mysql.com-20081201145656-6kjq91oga5nxbbob
> committer: Georgi Kodinov <kgeorge@mysql.com>
> branch nick: B39920-merge-5.0-bugteam
> timestamp: Mon 2008-12-01 17:41:06 +0200