revid:georgi.kodinov@oracle.com-20120309130449-82e3bs5v3et1x0ef
  committer: Georgi Kodinov <Georgi.Kodinov@Oracle.com>
  timestamp: Fri 2012-03-09 15:04:49 +0200
  message:
    Bug #12408412: GROUP_CONCAT + ORDER BY + INPUT/OUTPUT SAME 
    USER VARIABLE = CRASH
    Moved the preparation of the variables that receive the output from 
    SELECT INTO from execution time (JOIN:execute) to compile time 
    (JOIN::prepare). This ensures that if the same variable is used in the
    SELECT part of SELECT INTO it will be properly marked as non-const
    for this query.
    Test case added.
    Used proper fast iterator.

a better fix (much smaller and without regressions) is coming from 5.1

MySQL Bug #12408412: GROUP_CONCAT + ORDER BY + INPUT/OUTPUT SAME USER VARIABLE = CRASH
and
MySQL Bug#14664077 SEVERE PERFORMANCE DEGRADATION IN SOME CASES WHEN USER VARIABLES ARE USED


sql/item_func.cc:
  don't use anything from Item_func_set_user_var::fix_fields()
  in Item_func_set_user_var::save_item_result()
sql/sql_class.cc:
  Call suv->save_item_result(item) *before* doing suv->fix_fields(), because
  the former evaluates the item (and caches its value), while the latter marks
  the user variable as non-const. The problem is that the item was fix_field'ed
  when the user variable was const, and it doesn't expect it to change to non-const
  in the middle of the execution.

mysys/errors.c:
  revert upstream's fix. use a much simpler one
mysys/my_write.c:
  revert upstream's fix. use a simpler one
sql/item_xmlfunc.cc:
  useless, but ok
sql/mysqld.cc:
  simplify upstream's fix
storage/heap/hp_delete.c:
  remove upstream's fix.
  we'll use a much less expensive approach.

The function remove_eq_cond removes the parts of a disjunction
for which it has been proved that they are always true. In the
result of this removal the disjunction may be converted into a 
formula without OR that must be merged into the the AND formula
that contains the disjunction.
The merging of two AND conditions must take into account the
multiple equalities that may be part of each of them.
These multiple equality must be merged and become part of the
and object built as the result of the merge of the AND conditions.
Erroneously the function remove_eq_cond lacked the code that 
would merge multiple equalities of the merged AND conditions.
This could lead to confusing situations when at the same AND 
level there were two multiple equalities with common members
and the list of equal items contained only some of these 
multiple equalities.
This, in its turn, could lead to an incorrect work of the
function substitute_for_best_equal_field when it tried to optimize
ref accesses. This resulted in forming invalid TABLE_REF objects
that were used to build look-up keys when materialized subqueries
were exploited.
   

Compilation : fix oqgraph's system check, in case where boost header aren't in standard include directory.

This bug in the legacy code could manifest itself in queries with
semi-join materialized subqueries.
When a subquery is materialized all conditions that are imposed
only on the columns belonging to the tables from the subquery 
are taken into account.The code responsible for subquery optimizations
that employes subquery materialization  makes sure to remove these
conditions from the WHERE conditions of the query obtained after
it has transformed the original query into a query with a semi-join.
If the condition to be removed is an equality condition it could
be added to ON expressions and/or conditions from disjunctive branches
(parts of OR conditions) in an attempt to generate better access keys
to the tables of the query. Such equalities are supposed to be removed
later from all the formulas where they have been added to.
However, erroneously, this was not done in some cases when an ON
expression and/or a disjunctive part of the OR condition could
be converted into one multiple equality. As a result some equality
predicates over columns belonging to the tables of the materialized
subquery remained in the ON condition and/or the a disjunctive 
part of the OR condition, and the excuter later, when trying to
evaluate them, returned wrong answers as the values of the fields
from these equalities were not valid.  
This happened because any standalone multiple equality (a multiple
equality that are not ANDed with any other predicates) lacked
the information about equality predicates inherited from upper
levels (in particular, inherited from the WHERE condition).
The fix adds a reference to such information to any standalone
multiple equality. 

Prior to this patch, _getch() was used to read password input from console. getch() has a property that it reads Ctrl-C as character with ASCII code 0x03, and disregards Ctrl-C handler for  current process. 
The fix is to use ReadConsole() API instead of getch() , after setting console mode to ENABLE_PROCESSED_INPUT - this mode allows current process to handle Ctrl-C events.

The wrong result set returned by the left join query  from
the bug test case happened due to several inconsistencies 
and bugs of the legacy mysql code.

The bug test case uses an execution plan that employs a scan
of a materialized IN subquery from the WHERE condition.
When materializing such an IN- subquery the optimizer injects
additional equalities  into the WHERE clause. These equalities
express the constraints imposed by the subquery predicate.
The injected equality of the query in the  test case happens
to belong to the same equality class, and a new equality 
imposing a condition on the rows of the materialized subquery
is inferred from this class. Simultaneously the multiple
equality is added to the ON expression of the LEFT JOIN
used in the main query.
  
The inferred equality of the form f1=f2 is taken into account
when optimizing the scan of  the rows the temporary table 
that is the result of the subquery materialization: only the 
values of the field f1 are read from the table into the record 
buffer. Meanwhile the inferred equality is removed from the
WHERE conditions altogether as a constraint on the fields
of the temporary table that has been used when filling this table. 
This equality is supposed to be removed from the ON expression
when the multiple equalities of the ON expression are converted
into an optimal set of equality predicates. It supposed to be
removed from the ON expression as an equality inferred from only
equalities of the WHERE condition. Yet, it did not happened
due to the following bug in the code.

Erroneously the code tried to build multiple equality for ON
expression twice: the first time, when it called optimize_cond()
for the WHERE condition, the second time, when it called
this function for the HAVING condition. When executing
optimize_con() for the WHERE condition  a reference
to the multiple equality of the WHERE condition is set
in the multiple equality of the  ON expression. This reference
would allow later to convert multiple equalities of the
ON expression into equality predicates. However the 
the second call of build_equal_items() for the ON expression
that happened when optimize_cond() was called for the
HAVING condition reset this reference to NULL.

This bug fix blocks calling build_equal_items() for ON
expressions for the second time. In general, it will be
beneficial for many queries as it removes from ON 
expressions any equalities that are to be checked for the
WHERE condition.
The patch also fixes two bugs in the list manipulation
operations and a bug in the function  
substitute_for_best_equal_field() that resulted
in passing wrong reference to the multiple equalities
of where conditions when processing multiple
equalities  of ON expressions.

The code of substitute_for_best_equal_field() and
the code the helper function eliminate_item_equal()
were also streamlined and cleaned up.
Now the conversion of the multiple equalities into
an optimal set of equality predicates first produces
the sequence of the all equalities processing multiple
equalities one by one, and, only after this, it inserts
the equalities at the beginning of the other conditions.

The multiple changes in the output of EXPLAIN
EXTENDED are mainly the result of this streamlining,
but in some cases is the result of the removal of
unneeded equalities from ON expressions. In
some test cases this removal were reflected in the
output of EXPLAIN resulted in disappearance of 
“Using where” in some rows of the execution plans.

Use MessageBeep, which employs sound card, rather than system speaker.
The secondary benefit is that one can use volume control for this sound
(see MySQL's Bug #17088)

    Checks added to return and error when inappropriate
    geometry type is stored in a field.

or OSX - i.e NetBSD, OpenBSD, DragonFly, etc.

-Ensure that symbols listed in CLIENT_API_EXTRA are not thrown away by the linker.
-Add THR_KEY_mysys to this list, because Fedora18 exports it.

Analysis:
Range analysis detects that the subquery is expensive and doesn't
build a range access method. Later, the applicability test for loose
scan doesn't take that into account, and builds a loose scan method
without a range scan on the min/max column. As a result loose scan
fetches the first key in each group, rather than the first key that
satisfies the condition on the min/max column.

Solution:
Since there is no SEL_ARG tree to be used for the min/max column,
it is not possible to use loose scan if the min/max column is compared
with an expensive scalar subquery. Make the test for loose scan
applicability to be in sync with the range analysis code by testing if
the min/max argument is compared with an expensive predicate.

Missed update_used_tables() call for multi-update values.

This bug happened because the executor tried to use a wrong
TABLE REF object when building access keys. It constructed
keys from fields of a materialized table from a ref object
created to construct keys from the fields of the underlying
base table. This could happen only when materialized table
was created for a non-correlated IN subquery and only
when the materialized table used for lookups.
In this case we are guaranteed to be able to construct the
keys from the fields of tables that would be outer tables
for the tables of the IN subquery.
The patch makes sure that no ref objects constructed from
fields of materialized lookup tables are to be used.

Analys:
The cause for the wrong result was that the optimizer
incorrectly chose min/max loose scan when it is not
applicable. The applicability test missed the case when
a condition on the MIN/MAX argument was OR-ed with a
condition on some other field. In this case, the MIN/MAX
condition cannot be used for loose scan.

Solution:
Extend the test check_group_min_max_predicates() to check
that the WHERE clause is of the form: "cond1 AND cond2"
where 
  cond1 - does not use min_max_column at all.
  cond2 - is an AND/OR tree with leaves in form "min_max_column $CMP$ const"
          or $CMP$ is one of the functions between, is [not] null

to enable both recompiling mysqli or odbc from sources in addition to drop-in replacement functionality.

The case in question is compiling mysqli from sources, that needs client_errors via ER() macro.

Previously, we exported it as mysql_client_errors (compatibly to Fedora's style symbol renaming, see MDEV-3842).
However, if MariaDB header files are used when compiling mysqli, client_errors needs to be exported with its original name.

MDEV-11 - Modifed tests and result files to use explicit column lists
          in INSERT and SELECT statements

Analysis:
Range analysis discoveres that the query can be executed via loose index scan for GROUP BY.
Later, GROUP BY analysis fails to confirm that the GROUP operation can be computed via an
index because there is no logic to handle duplicate field references in the GROUP clause.
As a result the optimizer produces an inconsistent plan. It constructs a temporary table,
but on the other hand the group fields are not set to point there.
    
Solution:
Make loose scan analysis work in sync with order by analysis. In the case of duplicate
columns loose scan will not be applicable. This limitation will be lifted in 10.0 by
removing duplicate columns.

Assertion happens in replication thread during THD destruction, when thread calls my_sync(), which in turn calls  thd_wait_begin() callback. Connection count can be 0, because the counter was decremented before THD destructor. 
This assertion currently reproducible only in Percona server  and not in MariaDB, due to differences in replication code.

Fixed by moving  code to decrement connection counter after the THD destructor.

from MariaDB 10.0.
  
The bug in mdev-3948 was an instance of the problem fixed by Sergey's patch
in 10.0 - namely that the range optimizer could change table->[read | write]_set,
and not restore it.
  
revno: 3471
committer: Sergey Petrunya <psergey@askmonty.org>
branch nick: 10.0-serg-fix-imerge
timestamp: Sat 2012-11-03 12:24:36 +0400
message:
  # MDEV-3817: Wrong result with index_merge+index_merge_intersection, InnoDB table, join, AND and OR conditions
  Reconcile the fixes from:
  #
  # guilhem.bichot@oracle.com-20110805143029-ywrzuz15uzgontr0
  # Fix for BUG#12698916 - "JOIN QUERY GIVES WRONG RESULT AT 2ND EXEC. OR
  # AFTER FLUSH TABLES [-INT VS NULL]"
  #
  # guilhem.bichot@oracle.com-20111209150650-tzx3ldzxe1yfwji6
  # Fix for BUG#12912171 - ASSERTION FAILED: QUICK->HEAD->READ_SET == SAVE_READ_SET
  # and
  #
  and related fixes from: BUG#1006164, MDEV-376:
  
  Now, ROR-merged QUICK_RANGE_SELECT objects make no assumptions about the values
  of table->read_set and table->write_set.
  Each QUICK_ROR_SELECT has (and had before) its own column bitmap, but now, all 
  QUICK_ROR_SELECT's functions that care: reset(), init_ror_merged_scan(), and 
  get_next()  will set table->read_set when invoked and restore it back to what 
  it was before the call before they return.

  This allows to avoid the mess when somebody else modifies table->read_set for 
  some reason.