GitLab

           function with distinct.
Loose index scan is used to find MIN/MAX values using appropriate index and
thus allow to avoid grouping. For each found row it updates non-aggregated
fields with values from row with found MIN/MAX value.
Without loose index scan non-aggregated fields are copied by end_send_group
function. With loose index scan there is no need in end_send_group and
end_send is used instead. Non-aggregated fields still need to be copied and
this was wrongly implemented in QUICK_GROUP_MIN_MAX_SELECT::get_next.
WL#3220 added a case when loose index scan can be used with end_send_group to
optimize calculation of aggregate functions with distinct. In this case
the row found by QUICK_GROUP_MIN_MAX_SELECT::get_next might belong to a next
group and copying it will produce wrong result.

Update of non-aggregated fields is moved to the end_send function from
QUICK_GROUP_MIN_MAX_SELECT::get_next.


mysql-test/r/group_min_max.result:
  Added a test case for the bug#50539.
mysql-test/t/group_min_max.test:
  Added a test case for the bug#50539.
sql/opt_range.cc:
  Bug#50539: Wrong result when loose index scan is used for an aggregate
  function with distinct.
  Update of non-aggregated fields is moved to the end_send function from
  QUICK_GROUP_MIN_MAX_SELECT::get_next.
sql/sql_select.cc:
  Bug#50539: Wrong result when loose index scan is used for an aggregate
  function with distinct.
  Update of non-aggregated fields is moved to the end_send function from
  QUICK_GROUP_MIN_MAX_SELECT::get_next.

Bug#16565 mysqld --help --verbose does not order variablesBug#20413 sql_slave_skip_counter is not shown in show variables
Bug#20415 Output of mysqld --help --verbose is incomplete
Bug#25430 variable not found in SELECT @@global.ft_max_word_len;
Bug#32902 plugin variables don't know their names
Bug#34599 MySQLD Option and Variable Reference need to be consistent in formatting!
Bug#34829 No default value for variable and setting default does not raise error
Bug#34834 ? Is accepted as a valid sql mode
Bug#34878 Few variables have default value according to documentation but error occurs  
Bug#34883 ft_boolean_syntax cant be assigned from user variable to global var.
Bug#37187 `INFORMATION_SCHEMA`.`GLOBAL_VARIABLES`: inconsistent status
Bug#40988 log_output_basic.test succeeded though syntactically false.
Bug#41010 enum-style command-line options are not honoured (maria.maria-recover fails)
Bug#42103 Setting key_buffer_size to a negative value may lead t...

> ------------------------------------------------------------
> revno: 3181
> revision-id: alexey.kopytov@sun.com-20091016201951-fsht0wm8xn8vkzsx
> parent: joerg@mysql.com-20091016164025-kb4sbrggq5o7zufc
> committer: Alexey Kopytov <Alexey.Kopytov@Sun.com>
> branch nick: mysql-5.1-bugteam
> timestamp: Sat 2009-10-17 00:19:51 +0400
> message:
>   Bug #47123: Endless 100% CPU loop with STRAIGHT_JOIN 
>    
>   The problem was in incorrect handling of predicates involving 
>   NULL as a constant value by the range optimizer. 
>    
>   For example, when creating a SEL_ARG node from a condition of 
>   the form "field < const" (which would normally result in the 
>   "NULL < field < const" SEL_ARG),  the special case when "const" 
>   is NULL was not taken into account, so "NULL < field < NULL" 
>   was produced for the "field < NULL" condition. 
>    
>   As a result, SEL_ARG structures of this form could not be 
>   further optimized which in turn could lead to incorrectly 
>   constructed SEL_ARG trees. In particular, code assuming SEL_ARG 
>   structures to always form a sequence of ordered disjoint 
>   intervals could enter an infinite loop under some 
>   circumstances. 
>    
>   Fixed by changing get_mm_leaf() so that for any sargable 
>   predicate except "<=>" involving NULL as a constant, "empty" 
>   SEL_ARG is returned, since such a predicate is always false. 

> ------------------------------------------------------------
> revno: 3148.9.6
> revision-id: martin.hansson@sun.com-20091102122407-krzh4h0i052lbwr5
> parent: davi.arnaut@sun.com-20091102112236-k3myix2xy8miyv4s
> committer: Martin Hansson <martin.hansson@sun.com>
> branch nick: 5.1bt
> timestamp: Mon 2009-11-02 13:24:07 +0100
> message:
>   Bug#47925: regression of range optimizer and date comparison in 5.1.39!
>   
>   When a query was using a DATE or DATETIME value formatted
>   using any other separator characters beside hyphen '-', a
>   query with a greater-or-equal '>=' condition matching only
>   the greatest value in an indexed column, the result was
>   empty if index range scan was employed.
>   
>   The range optimizer got a new feature between 5.1.38 and
>   5.1.39 that changes a greater-or-equal condition to a
>   greater-than if the value matching that in the query was not
>   present in the table. But the value comparison function
>   compared the dates as strings instead of dates.
>   
>   The bug was fixed by splitting the function
>   get_date_from_str in two: One part that parses and does
>   error checking. This function is now visible outside the
>   module. The old get_date_from_str now calls the new
>   function.

> ------------------------------------------------------------
> revno: 1810.3959.5
> revision-id: ramil@mysql.com-20091023112648-gie6o3odj57cxh1e
> parent: ramil@mysql.com-20091021090408-208mvwwrcroi2j8c
> committer: Ramil Kalimullin <ramil@mysql.com>
> branch nick: b48258-5.0-bugteam
> timestamp: Fri 2009-10-23 16:26:48 +0500
> message:
>   Fix for bug#48258: Assertion failed when using a spatial index
>   
>   Problem: involving a spatial index for "non-spatial" queries
>   (that don't containt MBRXXX() functions) may lead to failed assert.
>   
>   Fix: don't use spatial indexes in such cases.

for each record'

There was an error in an internal structure in the range
optimizer (SEL_ARG). Bad design causes parts of a data
structure not to be initialized when it is in a certain
state. All client code must check that this state is not
present before trying to access the structure's data. Fixed
by

- Checking the state before trying to access data (in
several places, most of which not covered by test case.)

- Copying the keypart id when cloning SEL_ARGs


mysql-test/r/range.result:
  Bug#48459: Test result.
mysql-test/t/range.test:
  Bug#48459: Test case.
sql/opt_range.cc:
  Bug#48459: Fix + doxygenated count_key_part_usage comment.

When merging ranges during calculation of the result of OR
to two range sets the current range may be obsoleted by the 
resulting merged range.
The first overlapping range can be obsoleted as well.

Fixed by moving the pointer to the first overlapping range to the
pointer of the resulting union range.
Added few comments at key places in key_or().

            WHERE conditions 
 
check_group_min_max() checks if the loose index scan 
optimization is applicable for a given WHERE condition, that is 
if the MIN/MAX attribute participates only in range predicates 
comparing the corresponding field with constants. 
 
The problem was that it considered the whole predicate suitable 
for the loose index scan optimization as soon as it encountered 
a constant as a predicate argument. This is obviously wrong for 
cases when a constant is the first argument of a predicate 
which does not satisfy the above condition. 
 
Fixed check_group_min_max() so that all arguments of the input 
predicate are considered to decide if it passes the test, even 
though a constant has already been encountered.

mysql-test/r/group_min_max.result:
  Added a test case for bug #48472.
mysql-test/t/group_min_max.test:
  Added a test case for bug #48472.
sql/opt_range.cc:
  Fixed check_group_min_max() so that all arguments of the input 
  predicate are considered to decide if it passes the test, even 
  though a constant has already been encountered.

BUG#48447, BUG#48161, fixed a regression from fix of BUG#6045, where binary collations can use indexes/partition pruning for cases using equality conditions, however it cannot be used for any other condition like <, >, <=, >=, <>, also added test case for verification of BUG#47774 in this patch

When a query was using a DATE or DATETIME value formatted
using any other separator characters beside hyphen '-', a
query with a greater-or-equal '>=' condition matching only
the greatest value in an indexed column, the result was
empty if index range scan was employed.

The range optimizer got a new feature between 5.1.38 and
5.1.39 that changes a greater-or-equal condition to a
greater-than if the value matching that in the query was not
present in the table. But the value comparison function
compared the dates as strings instead of dates.

The bug was fixed by splitting the function
get_date_from_str in two: One part that parses and does
error checking. This function is now visible outside the
module. The old get_date_from_str now calls the new
function.


mysql-test/r/range.result:
  Bug#47925: Test result
mysql-test/t/range.test:
  Bug#47925: Test case
sql/item.cc:
  Bug#47925: Fix + some edit on the comments
sql/item.h:
  Bug#47925: Changed function signature
sql/item_cmpfunc.cc:
  Bug#47925: Split function in two
sql/item_cmpfunc.h:
  Bug#47925: Declaration of new function
sql/opt_range.cc:
  Bug#47925: Added THD to function call
sql/time.cc:
  Bug#47925: Added microsecond comparison

BUG#48397, set key_part->length to key_part->store_length isn't correct, store_length is a bit longer

BUG#48165, needed to introduce length restrictions on partitioning fields to ensure that no stack overruns occur

Problem: involving a spatial index for "non-spatial" queries
(that don't containt MBRXXX() functions) may lead to failed assert.

Fix: don't use spatial indexes in such cases.


mysql-test/r/gis-rtree.result:
  Fix for bug#48258: Assertion failed when using a spatial index
    - test result.
mysql-test/t/gis-rtree.test:
  Fix for bug#48258: Assertion failed when using a spatial index
    - test case.
sql/opt_range.cc:
  Fix for bug#48258: Assertion failed when using a spatial index
    - allow only spatial functions (MBRXXX) for itMBR keyparts.

sql/opt_range.cc:
  WL#3352 code review fixes
  
  Corrected indenting and added range_par parameter for easier reading/formatting.
sql/partition_element.h:
  WL#3352, changed formatting of copyright header
sql/sql_partition.cc:
  WL#3352, fixed indenting and changed MAX_VALUE to MAXVALUE
sql/sql_partition.h:
  WL#3352, fixed copyright header and indenting of
  function header

 
The problem was in incorrect handling of predicates involving 
NULL as a constant value by the range optimizer. 
 
For example, when creating a SEL_ARG node from a condition of 
the form "field < const" (which would normally result in the 
"NULL < field < const" SEL_ARG),  the special case when "const" 
is NULL was not taken into account, so "NULL < field < NULL" 
was produced for the "field < NULL" condition. 
 
As a result, SEL_ARG structures of this form could not be 
further optimized which in turn could lead to incorrectly 
constructed SEL_ARG trees. In particular, code assuming SEL_ARG 
structures to always form a sequence of ordered disjoint 
intervals could enter an infinite loop under some 
circumstances. 
 
Fixed by changing get_mm_leaf() so that for any sargable 
predicate except "<=>" involving NULL as a constant, "empty" 
SEL_ARG is returned, since such a predicate is always false. 

mysql-test/r/partition_pruning.result:
  Fixed a broken test case.
mysql-test/r/range.result:
  Added a test case for bug #47123.
mysql-test/r/subselect.result:
  Fixed a broken test cases.
mysql-test/t/range.test:
  Added a test case for bug #47123.
sql/opt_range.cc:
  Fixed get_mm_leaf() so that for any sargable
  predicate except "<=>" involving NULL as a constant, "empty"
  SEL_ARG is returned, since such a predicate is always false.

 
The problem was in incorrect handling of predicates involving 
NULL as a constant value by the range optimizer.  
 
For example, when creating a SEL_ARG node from a condition of 
the form "field < const" (which would normally result in the 
"NULL < field < const" SEL_ARG),  the special case when "const" 
is NULL was not taken into account, so "NULL < field < NULL" 
was produced for the "field < NULL" condition. 
 
As a result, SEL_ARG structures of this form could not be 
further optimized which in turn could lead to incorrectly 
constructed SEL_ARG trees. In particular, code assuming SEL_ARG 
structures to always form a sequence of ordered disjoint 
intervals could enter an infinite loop under some 
circumstances. 
 
Fixed by changing get_mm_leaf() so that for any sargable 
predicate except "<=>" involving NULL as a constant, "empty" 
SEL_ARG is returned, since such a predicate is always false. 

mysql-test/r/range.result:
  Added a test case for bug #47123.
mysql-test/t/range.test:
  Added a test case for bug #47123.
sql/opt_range.cc:
  Fixed get_mm_leaf() so that for any sargable 
  predicate except "<=>" involving NULL as a constant, "empty" 
  SEL_ARG is returned, since such a predicate is always false.

covering index
      
When two range predicates were combined under an OR
predicate, the algorithm tried to merge overlapping ranges
into one. But the case when a range overlapped several other
ranges was not handled. This lead to

1) ranges overlapping, which gave repeated results and 
2) a range that overlapped several other ranges was cut off.  

Fixed by 

1) Making sure that a range got an upper bound equal to the
next range with a greater minimum.
2) Removing a continue statement


mysql-test/r/group_min_max.result:
  Bug#42846: Changed query plans
mysql-test/r/range.result:
  Bug#42846: Test result.
mysql-test/t/range.test:
  Bug#42846: Test case.
sql/opt_range.cc:
  Bug#42846: The fix. 
  
  Part1: Previously, both endpoints from key2 were copied,
  which is not safe. Since ranges are processed in ascending
  order of minimum endpoints, it is safe to copy the minimum
  endpoint from key2 but not the maximum. The maximum may only
  be copied if there is no other range or the other range's
  minimum is greater than key2's maximum.

can lead to bad memory access

Problem: Field_bit is the only field which returns INT_RESULT
and doesn't have unsigned flag. As it's not a descendant of the 
Field_num, so using ((Field_num *) field_bit)->unsigned_flag may lead
to unpredictable results.

Fix: check the field type before casting.


mysql-test/r/type_bit.result:
  Fix for bug #42803: Field_bit does not have unsigned_flag field,
  can lead to bad memory access
    - test result.
mysql-test/t/type_bit.test:
  Fix for bug #42803: Field_bit does not have unsigned_flag field,
  can lead to bad memory access
    - test case.
sql/opt_range.cc:
  Fix for bug #42803: Field_bit does not have unsigned_flag field,
  can lead to bad memory access
    - don't cast to (Field_num *) Field_bit, as it's not a Field_num
  descendant and is always unsigned by nature.

Changed all no_ to num_ to avoid strange names like no_list_values which is not expected to be number of list values, rather a boolea indicating no list values

WL#3352, Introducing Column list partitioning, makes it possible to partition on most data types, makes it possible to prune on multi-field partitioning

            results in server crash 
 
check_group_min_max_predicates() assumed the input condition 
item to be one of COND_ITEM, SUBSELECT_ITEM, or FUNC_ITEM. 
Since a condition of the form "field" is also a valid condition 
equivalent to "field <> 0", using such a condition in a query 
where the loose index scan was chosen resulted in a debug 
assertion failure. 
 
Fixed by handling conditions of the FIELD_ITEM type in 
check_group_min_max_predicates(). 

mysql-test/r/group_min_max.result:
  Added a test case for bug #46607.
mysql-test/t/group_min_max.test:
  Added a test case for bug #46607.
sql/opt_range.cc:
  Handle conditions of the FUNC_ITEM type in 
  check_group_mix_max_predicates().

with gcc 4.3.2
      
This patch fixes a number of GCC warnings about variables used
before initialized. A new macro UNINIT_VAR() is introduced for
use in the variable declaration, and LINT_INIT() usage will be
gradually deprecated. (A workaround is used for g++, pending a
patch for a g++ bug.)
      
GCC warnings for unused results (attribute warn_unused_result)
for a number of system calls (present at least in later
Ubuntus, where the usual void cast trick doesn't work) are
also fixed.


client/mysqlmanager-pwgen.c:
  A fix for warn_unused_result, adding fallback to use of
  srand()/rand() if /dev/random cannot be used. Also actually
  adds calls to rand() in the second branch so that it actually
  creates a random password.

Problem was that the partition containing NULL values
was pruned away, since '2001-01-01' < '2001-02-00' but
TO_DAYS('2001-02-00') is NULL.

Added the NULL partition for RANGE/LIST partitioning on TO_DAYS()
function to be scanned too.

Also fixed a bug that added ALLOW_INVALID_DATES to sql_mode
(SELECT * FROM t WHERE date_col < '1999-99-99' on a RANGE/LIST
partitioned table would add it).

mysql-test/include/partition_date_range.inc:
  Bug#20577: Partitions: use of to_days() function leads to selection failures
  
  Added include file to decrease test code duplication
mysql-test/r/partition_pruning.result:
  Bug#20577: Partitions: use of to_days() function leads to selection failures
  
  Added test results
mysql-test/r/partition_range.result:
  Bug#20577: Partitions: use of to_days() function leads to selection failures
  
  Updated test result.
  This fix adds the partition containing NULL values to
  the list of partitions to be scanned.
mysql-test/t/partition_pruning.test:
  Bug#20577: Partitions: use of to_days() function leads to selection failures
  
  Added test case
sql/item.h:
  Bug#20577: Partitions: use of to_days() function leads to selection failures
  
  Added MONOTONIC_*INCREASE_NOT_NULL values to be used by TO_DAYS.
sql/item_timefunc.cc:
  Bug#20577: Partitions: use of to_days() function leads to selection failures
  
  Calculate the number of days as return value even for invalid dates.
  This is so that pruning can be used even for invalid dates.
sql/opt_range.cc:
  Bug#20577: Partitions: use of to_days() function leads to selection failures
  
  Fixed a bug that added ALLOW_INVALID_DATES to sql_mode
  (SELECT * FROM t WHERE date_col < '1999-99-99' on a RANGE/LIST
  partitioned table would add it).
sql/partition_info.h:
  Bug#20577: Partitions: use of to_days() function leads to selection failures
  
  Resetting ret_null_part when a single partition is to be used, this
  to avoid adding the NULL partition.
sql/sql_partition.cc:
  Bug#20577: Partitions: use of to_days() function leads to selection failures
  
  Always include the NULL partition if RANGE or LIST.
  Use the returned value for the function for pruning, even if
  it is marked as NULL, so that even '2000-00-00' can be
  used for pruning, even if TO_DAYS('2000-00-00') is NULL.
  
  Changed == to >= in get_next_partition_id_list to avoid
  crash if part_iter->part_nums is not correctly setup.

There were a problem since pruning uses the field
for comparison (while evaluate_join_record uses longlong),
resulting in pruning failures when comparing DATE to DATETIME.

Fix was to always comparing DATE vs DATETIME as DATETIME,
by adding ' 00:00:00' to the DATE string.

And adding optimization for comparing with 23:59:59, so that
DATETIME_col > '2001-02-03 23:59:59' ->
TO_DAYS(DATETIME_col) > TO_DAYS('2001-02-03 23:59:59') instead
of '>='.

mysql-test/r/partition_pruning.result:
  Bug#46362: Endpoint should be set to false for TO_DAYS(DATE)
  
  Updated result-file
mysql-test/t/partition_pruning.test:
  Bug#46362: Endpoint should be set to false for TO_DAYS(DATE)
  
  Added testcases.
sql-common/my_time.c:
  Bug#46362: Endpoint should be set to false for TO_DAYS(DATE)
  
  removed duplicate assignment.
sql/item.cc:
  Bug#46362: Endpoint should be set to false for TO_DAYS(DATE)
  
  Changed field_is_equal_to_item into field_cmp_to_item, to
  better handling DATE vs DATETIME comparision.
sql/item.h:
  Bug#46362: Endpoint should be set to false for TO_DAYS(DATE)
  
  Updated comment
sql/item_timefunc.cc:
  Bug#46362: Endpoint should be set to false for TO_DAYS(DATE)
  
  Added optimization (pruning) of DATETIME where time-part is
  23:59:59
sql/opt_range.cc:
  Bug#46362: Endpoint should be set to false for TO_DAYS(DATE)
  
  Using the new stored_field_cmp_to_item for better pruning.

The code was using a special global buffer for the value of IS NULL ranges.
This was not always long enough to be copied by a regular memcpy. As a 
result read buffer overflows may occur.
Fixed by setting the null byte to 1 and setting the rest of the field disk image
to NULL with a bzero (instead of relying on the buffer and memcpy()).

The check for stack overflow was independent of the size of the 
structure stored in the heap. 
Fixed by adding sizeof(PARAM) to the requested free heap size.

       
Fixed various compilation warnings when compiling on a 
 64 bit windows.

When the function exits with an error it was not
freeing the local Unique class instance.
Fixed my making sure all the places where the function
returns from are freeing the Unique instance 

WHERE and GROUP BY clause

Loose index scan may use range conditions on the argument of 
the MIN/MAX aggregate functions to find the beginning/end of 
the interval that satisfies the range conditions in a single go.
These range conditions may have open or closed minimum/maximum 
values. When the comparison returns 0 (equal) the code should 
check the type of the min/max values of the current interval 
and accept or reject the row based on whether the limit is 
open or not.
There was a wrong composite condition on checking this and it was
not working in all cases.
Fixed by simplifying the conditions and reversing the logic.

mysql-test/r/group_min_max.result:
  Bug #45386: test case
mysql-test/t/group_min_max.test:
  Bug #45386: test case
sql/opt_range.cc:
  Bug #45386: fix the check whether to use the value if on the
  interval boundry

Range analysis did not request sorted output from the storage engine,
which cause partitioned handlers to process one partition at a time
while reading key prefixes in ascending order, causing values to be 
missed. Fixed by always requesting sorted order during range analysis.
This fix is introduced in 6.0 by the fix for bug no 41136.

mysql-test/r/group_min_max.result:
  Bug#44821: Test result.
mysql-test/t/group_min_max.test:
  Bug#44821: Test case
sql/opt_range.cc:
  Bug#44821: Fix.

with gcc 4.3.2
      
Compiling MySQL with gcc 4.3.2 and later produces a number of 
warnings, many of which are new with the recent compiler
versions.
      
This bug will be resolved in more than one patch to limit the
size of changesets. This is the first patch, fixing a number 
of the warnings, predominantly "suggest using parentheses 
around && in ||", and empty for and while bodies.

with gcc 4.3.2

Compiling MySQL with gcc 4.3.2 and later produces a number of 
warnings, many of which are new with the recent compiler
versions.

This bug will be resolved in more than one patch to limit the
size of changesets. This is the first patch, fixing a number 
of the warnings, predominantly "suggest using parentheses 
around && in ||", and empty for and while bodies.

mysql-test/r/index_merge_myisam.result:
  Testcases
mysql-test/t/index_merge_myisam.test:
  Testcases
sql/strfunc.cc:
  Change optimizer_switch from no_xxx to xxx=on/xx=off.
  - Add functions to parse the new syntax

return no rows

The algorithm of determining the best key for loose index scan is doing a loop
over the available indexes and selects the one that has the best cost.
It retrieves the parameters of the current index into a set of variables.
If the cost of using the current index is lower than the best cost so far it 
copies these variables into another set of variables that contain the 
information for the best index so far.
After having checked all the indexes it uses these variables (outside of the 
index loop) to create the table read plan object instance.
The was a single omission : the key_infix/key_infix_len variables were used 
outside of the loop without being preserved in the loop for the best index 
so far.
This causes these variables to get overwritten by the next index(es) checked.
Fixed by adding variables to hold the data for the current index, passing 
the new variables to the function that assigns values to them an...

- Add support for setting it as a server commandline argument
- Add support for those switches:
  = no_index_merge
  = no_index_merge_union
  = no_index_merge_sort_union
  = no_index_merge_intersection

mysql-test/r/index_merge_myisam.result:
  Testcases for index_merge related @@optimizer_switch flags.
mysql-test/t/index_merge_myisam.test:
  Testcases for index_merge related @@optimizer_switch flags.
sql/set_var.cc:
  - Backport @@optimizer_switch support from 6.0
  - Add support for setting it as a server commandline argument
sql/sql_class.h:
  - Backport @@optimizer_switch support from 6.0
sql/sql_select.h:
  - Backport @@optimizer_switch support from 6.0