Analysis:
A query with implicit grouping is one with aggregate functions and
no GROUP BY clause. MariaDB inherits from MySQL an SQL extenstion
that allows mixing aggregate functions with non-aggregate fields.
If a query with such mixed select clause produces an empty result
set, the meaning of aggregate functions is well defined - either
NULL (MIN, MAX, etc.), or 0 (count(*)). However the non-aggregated
fields must also have some value, and the only reasonable value in
the case of empty result is NULL.

The cause of the many wrong results was that if a field is declared
as non-nullable (e.g. because it is a PK or NOT NULL), the semantic
analysis and the optimization phases treat this field as non-nullable,
and generate all related query plan elements based on this assumption.

Later during execution, these incorrectly configured/generated query
plan elements result in a wrong result because the selected fields
are not null due to the not-null assumption during optimization.

Solution:
Detect before the context analysys phase that a query uses implicit
grouping with mixed aggregates/non-aggregates, and set all fields
as nullable. The parser already walks the SELECT clause, and
already sets Item::with_sum_func for Items that reference aggreagate
functions. The patch adds a symmetric Item::with_field so that all
Items that reference an Item_field are marked during their
construction at parse time in the same way as with aggregate function
use.

Added item.real_type() for easy access to the underlaying types for Item_ref and Item_cache_wrapper()
This allows us to simplify and speed up some tests and also remove get_cached_item()

sql/item.h:
  Added item.real_type()
  Removed get_cached_item()
sql/opt_range.cc:
  Simplify test
sql/sql_select.cc:
  Simplify test
sql/sql_show.cc:
  Simplify test

If join condition is of the form <t2.key>=<t1.no_key> then the server
performs no index look-ups when looking for matching rows of t2 for
the rows from t1 with t1.no_key=NULL. It happens because the function
add_not_null_conds() injects an additional condition of the form 
IS NOT NULL(<t1.no_key>) into the WHERE condition.
However if the join condition was of the form <t.key>=<outer_ref> no
additional null rejecting predicate was generated. This could lead
to extra records in the result set if the value of <outer_ref> happened
to be NULL.
The new code injects null rejecting predicates of the form 
IS NOT NULL(<outer_ref>) and evaluates them before the first row
the subquery is constructed.

The bug was a result of the fix for bug 668644 that turned out to be
not quite correct. A problem appeared with HAVING conditions containing
more than one predicate. If a query with an ORDER BY clause uses
such HAVING condition and the required order can be obtained with
a range/index scan then the HAVING condition has to be pushed into
two different formulas (items). To be able to do it we have to create
a copy of the ANDOR structure of the pushed condition.
 

- Make equality-substitution-for-ref-access code in JOIN::optimize() treat join_tab->ref.key_copy correctly
  (in the way create_ref_for_key() has filled it).

Changed some String.ptr() -> String.c_ptr() for String that are not guaranteed to end with \0
Removed some c_ptr() usage from parameters to functions that takes ptr & length
Use preallocate buffers to avoid calling malloc() for most operations. 


sql/event_db_repository.cc:
  alias is now a String
sql/event_scheduler.cc:
  c_ptr -> c_ptr_safe() to avoid warnings from valgrind.
sql/events.cc:
  c_ptr -> c_ptr_safe() to avoid warnings from valgrind.
  c_ptr -> ptr() as function takes ptr & length
sql/field.cc:
  alias is now a String
sql/field.h:
  alias is now a String
sql/ha_partition.cc:
  alias is now a String
sql/handler.cc:
  alias is now a String
  ptr() -> c_ptr() as string is not guaranteed to be \0 terminated
sql/item.cc:
  Store error parameter in separarte buffer to ensure correct error message
sql/item_func.cc:
  ptr() -> c_ptr_safe() as string is not guaranteed to be \0 terminated
sql/item_sum.h:
  Use my_strtod() instead of my_atof() to not have to make string \0 terminated
sql/lock.cc:
  alias is now a String
sql/log.cc:
  c_ptr() -> ptr() as function takes ptr & length
sql/log_event.cc:
  c_ptr_quick() -> ptr() as we only want to get the pointer to String buffer
sql/opt_range.cc:
  ptr() -> c_ptr() as string is not guaranteed to be \0 terminated
sql/opt_table_elimination.cc:
  alias is now a String
sql/set_var.cc:
  ptr() -> c_ptr() as string is not guaranteed to be \0 terminated
  c_ptr() -> c_ptr_safe() to avoid warnings from valgrind.
  c_ptr() -> ptr() as function takes ptr & length
  Simplify some code.
sql/sp.cc:
  c_ptr() -> ptr() as function takes ptr & length
sql/sp_rcontext.cc:
  alias is now a String
sql/sql_base.cc:
  alias is now a String.
  Here we win a realloc() for most alias usage.
sql/sql_class.cc:
  Use size descriptor for printf() to avoid accessing bytes outside of buffer
sql/sql_insert.cc:
  Change allocation of TABLE as it's now contains a String
  _ptr() -> ptr() as function takes ptr & length
sql/sql_load.cc:
  Use preallocate buffers to avoid calling malloc() for most operations.
sql/sql_parse.cc:
  Use c_ptr_safe() to ensure string is \0 terminated.
sql/sql_plugin.cc:
  c_ptr_quick() -> ptr() as function takes ptr & length
sql/sql_select.cc:
  alias is now a String
sql/sql_show.cc:
  alias is now a String
sql/sql_string.h:
  Added move() function to change who owns the string (owner does the free)
sql/sql_table.cc:
  alias is now a String
  c_ptr() -> c_ptr_safe() to avoid warnings from valgrind.
sql/sql_test.cc:
  c_ptr() -> c_ptr_safe() to avoid warnings from valgrind.
  alias is now a String
sql/sql_trigger.cc:
  c_ptr() -> c_ptr_safe() to avoid warnings from valgrind.
  Use field->init() to setup pointers to alias.
sql/sql_update.cc:
  alias is now a String
sql/sql_view.cc:
  ptr() -> c_ptr_safe() as string is not guaranteed to be \0 terminated
sql/sql_yacc.yy:
  r() -> c_ptr() as string is not guaranteed to be \0 terminated
sql/table.cc:
  alias is now a String
sql/table.h:
  alias is now a String
storage/federatedx/ha_federatedx.cc:
  Remove extra 1 byte alloc that is automaticly done by strmake()
  Ensure that error message ends with \0
storage/maria/ha_maria.cc:
  alias is now a String
storage/myisam/ha_myisam.cc:
  alias is now a String

sql/mysqld.cc:
  Fixed: optimize_join_buffer_size was missing in the description of possible
  options for the optimizer switch.
sql/sql_select.cc:
  Fixed: initialization for the field ref_table_rows of the KEYUSE structure was 
  missing (as a result of a lame merge).

even in the cases when there existed range/index-merge scans that
were cheaper than the full table scan.
This was a defect/bug of the implementation of mwl #128. 
Now hash join can work not only with full table scan of the joined
table, but also with full index scan, range and index-merge scans.
Accordingly, in the cases when hash join is used the column 'type'
in the EXPLAINs can contain now 'hash_ALL', 'hash_index', 'hash_range'
and 'hash_index_merge'. If hash join is coupled with a range/index_merge
scan then the columns 'key' and 'key_len' contain info not only on
the used hash index, but also on the indexes used for the scan.   

- Assume that outside subquery references are known when doing "Range-checked-for-each-record" check.

with the test case added by this patch.
The bug cannot be reproduced with the same test case for the main
5.3 tree because the backported fix for bug 59696 masks the 
problem that causes the crash in the mentioned test case. It's not
clear weather this fix masks this problem in all possible cases. 

Anyway the patch for bug 698882 introduced some inconsistent data
structures that could contain indirect references to deleted object.
It happened when two Item_equal objects were merged and the Item_field
list of the second object was joined to such list of the first object.
This operation required adjustment of the backward pointers in 
Item fields from the joined list. However the adjustment was missing 
and this caused crashes in the tree for mwl#128.

Now the backward pointers are set only when Item_equal items are
completely built and are not changed anymore.
 

This was another bug in the patch for bug 698882. The new
code from this patch did not ensured that substitutions
of fields for best equal fields were performed on all
AND-OR levels. As a result substitutions for best fields
in some predicates that had been used by the range optimizer
were not actually performed while range plans could employ
these substitutions. This could lead to inconsistent data
structures and ultimately to a crash.

The bug was in the code of the patch fixing bug 698882.
With improper casting the method store_key_field::change_source_field
was called for the elements of the array TABLE_REF::key_copy that
were either of a different type or not allocated at all. This caused
crashes in some queries. 

hash join over equi-join conditions without supporting indexes.

of sort_intersect scans.
 

hash join over equi-join conditions without supporting indexes.

Added logging of all possible fatal table errors if --log-warnings set to > 1


mysql-test/extra/rpl_tests/rpl_EE_err.test:
  Safety fix
mysql-test/extra/rpl_tests/rpl_row_basic.test:
  Added suppression of possible error message (so that one can run test with --log-warnings=2)
mysql-test/r/archive.result:
  Added suppression of possible error message (so that one can run test with --log-warnings=2)
mysql-test/r/csv.result:
  Added suppression of possible error message (so that one can run test with --log-warnings=2)
mysql-test/suite/maria/r/maria-autozerofill.result:
  Added suppression of possible error message (so that one can run test with --log-warnings=2)
mysql-test/suite/maria/t/maria-autozerofill.test:
  Added suppression of possible error message (so that one can run test with --log-warnings=2)
mysql-test/suite/maria/t/maria-recover.test:
  Added suppression of possible error message (so that one can run test with --log-warnings=2)
mysql-test/suite/parts/t/partition_recover_myisam.test:
  Added suppression of possible error message (so that one can run test with --log-warnings=2)
mysql-test/suite/rpl/r/rpl_bug38694.result:
  Added suppression of possible error message (so that one can run test with --log-warnings=2)
mysql-test/suite/rpl/r/rpl_idempotency.result:
  Added suppression of possible error message (so that one can run test with --log-warnings=2)
mysql-test/suite/rpl/r/rpl_ignore_table.result:
  Added suppression of possible error message (so that one can run test with --log-warnings=2)
mysql-test/suite/rpl/r/rpl_row_basic_11bugs.result:
  Added suppression of possible error message (so that one can run test with --log-warnings=2)
mysql-test/suite/rpl/r/rpl_row_conflicts.result:
  Added suppression of possible error message (so that one can run test with --log-warnings=2)
mysql-test/suite/rpl/r/rpl_temporary_errors.result:
  Added suppression of possible error message (so that one can run test with --log-warnings=2)
mysql-test/suite/rpl/t/rpl_bug38694.test:
  Added suppression of possible error message (so that one can run test with --log-warnings=2)
mysql-test/suite/rpl/t/rpl_idempotency.test:
  Added suppression of possible error message (so that one can run test with --log-warnings=2)
mysql-test/suite/rpl/t/rpl_ignore_table.test:
  Added suppression of possible error message (so that one can run test with --log-warnings=2)
mysql-test/suite/rpl/t/rpl_row_basic_11bugs.test:
  Added suppression of possible error message (so that one can run test with --log-warnings=2)
mysql-test/suite/rpl/t/rpl_row_conflicts.test:
  Added suppression of possible error message (so that one can run test with --log-warnings=2)
mysql-test/suite/rpl/t/rpl_temporary_errors.test:
  Added suppression of possible error message (so that one can run test with --log-warnings=2)
mysql-test/t/archive.test:
  Added suppression of possible error message (so that one can run test with --log-warnings=2)
mysql-test/t/csv.test:
  Added suppression of possible error message (so that one can run test with --log-warnings=2)
sql/handler.cc:
  If running with --assert-of-crashed-table or --log-warnings > 1 then print engine error to log
sql/sql_select.cc:
  Disable not initialized warning from gcc
strings/Makefile.am:
  Fixed compiler error on Solaris 10 (duplicate strmov() function)

Aria and MyISAM in create_internal_tmp_table_from_heap()
(safe, as duplicates are impossible).
This gives a HUGE speed boost!

sql/opt_subselect.cc:
  Fixed problem with wrong recinfo in create_duplicate_weedout_tmp_tabl()
  Tagged the table with 'no_rows' so that when we create the table on disk,
  we only store the index data. This gave us a major speedup!
sql/sql_select.cc:
  create_tmp_table_from_heap() now uses bulk_insert + repair_by_sort
  when creating Aria/MyISAM tables from HEAP tables.
  This gives a HUGE speed boost!
storage/maria/ha_maria.cc:
  Extended bulk_insert() to recreate UNIQUE keys for
  internal temporary tables
storage/maria/ma_open.c:
  Initialize m_info->lock.type properly for temporarly tables
  (needed for start_bulk_insert())
storage/maria/ma_write.c:
  Don't check uniques that are disabled
storage/myisam/ha_myisam.cc:
  Extended bulk_insert() to recreate UNIQUE keys for
  internal temporary tables.

This will also enable us in the future to collect statistics for
writes to internal tmp tables.

sql/handler.h:
  Added ha_write_tmp_row()
sql/opt_subselect.cc:
  ha_write_row -> ha_write_tmp_row
sql/sql_class.h:
  Added ha_write_tmp_row()
sql/sql_select.cc:
  ha_write_row -> ha_write_tmp_row

This makes the keys smaller (no row pointer) and gives us proper errors if we
use the table wrongly.

sql/sql_select.cc:
  Use NO_RECORD for tables that doesn't need row data.
storage/maria/Makefile.am:
  Added ma_norec.c
storage/maria/ma_check.c:
  Added support for NO_RECORD record format (don't store any row data)
storage/maria/ma_norec.c:
  Added support for NO_RECORD record format
storage/maria/ma_open.c:
  Added support for NO_RECORD record format
storage/maria/ma_search.c:
  Added support for 0 size row pointers (used with NO_RECORD)
storage/maria/ma_test1.c:
  Added testing of NO_RECORD record format.
storage/maria/maria_chk.c:
  Added support for NO_RECORD
storage/maria/maria_def.h:
  Added support for NO_RECORD
storage/maria/unittest/ma_test_all-t:
  Added testing of NO_RECORD record format

mysys/my_handler.c:
  Fixed typo

Made sure that the optimal fields are used by TABLE_REF objects
when building index access keys to joined tables.
Fixed a bug in the template function that sorts the elements of
a list using the bubble sort algorithm. The bug caused poor
performance of the function. Also added an optimization that
skips comparison with the most heavy elements that has been 
already properly placed in the list.
Made the comparison of the fields belonging to the same Item_equal
more granular: fields belonging to the same table are also ordered
according to some rules.

An assertion failure was triggered for a 6-way join query that uses two
join buffers.
The failure happened because every call of the function flush_cached_records()
saved and restored status of all tables before the table join_tab. It
must do it only for those of them that follow the last table that uses
a join buffer.

Date: Mon, 01 Nov 2010 15:15:25 -0000
3272 Roy Lyseng        2010-11-01
Bug#52068: Optimizer generates invalid semijoin materialization plan

When MaterializeScan semijoin strategy was used and there were one
or more outer dependent tables before the semijoin tables, the scan
over the materialized table was not properly reset for each row of
the prefix outer tables.

Example: suppose we have a join order:

  ot1 SJ-Mat-Scan(it2 it3)  ot4

Notice that this is called a MaterializeScan, even though there is an
outer table ahead of the materialized tables. Usually a MaterializeScan
has the outer tables after the materialized table, but this is
a special (but legal) case with outer dependent tables both before and
after the materialized table.

For each qualifying row from ot1, a new scan over the materialized
table must be set up. The code failed to do that, so all scans after
the first one returned zero rows from the materialized table.

tmptable needed

The function DEFAULT() works by modifying the the data buffer pointers (often
referred to as 'record' or 'table record') of its argument. This modification
is done during name resolution (fix_fields().) Unfortunately, the same
modification is done when creating a temporary table, because default values
need to propagate to the new table.

Fixed by skipping the pointer modification for fields that are arguments to
the DEFAULT function.

to crash mysqld". 
      
handler::pushed_cond was not always properly reset when table objects where
recycled via the table cache.
      
handler::pushed_cond is now set to NULL in handler::ha_reset(). This should 
prevent pushed conditions from (incorrectly) re-apperaring in later queries.

Patch provided by Dolf Schimmel

Fixes LP#695006 converting HEAP to Aria" status do not respond to KILL QUERY

sql/sql_select.cc:
  Make copy from heap to MyISAM / Aria killable.

for hash join in the cases when there are no suitable indexes
for these conditions.

- Removed files specific to compiling on OS/2
- Removed files specific to SCO Unix packaging
- Removed "libmysqld/copyright", text is included in documentation
- Removed LaTeX headers for NDB Doxygen documentation
- Removed obsolete NDB files
- Removed "mkisofs" binaries
- Removed the "cvs2cl.pl" script
- Changed a few GPL texts to use "program" instead of "library"

Analysis:
The assert failed because st_select_lex::print() was called for subqueries
as follows:

Item_subselect::print() ->
  subselect_single_select_engine::print() -> st_select_lex::print()

It was Item_subselect::fix_fields() that set the thd by calling set_thd(),
so when this print() was called before fix_fields(), subselect_engine::thd
was NULL.

Solution:
The patch makes all constructors of all subselect_engine classes to take
a THD parameter. The default subselect_single_select_engine engine is created
early during parse time, in the Item_subselect::init call, so we pass the
correct THD object already at this point.

--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

The second (final) patch.