summaryrefslogtreecommitdiff
path: root/mysql-test/r/join.result
Commit message (Collapse)AuthorAgeFilesLines
* Merge mariadb 5.3->mariadb 5.5unknown2012-03-241-0/+70
|\
| * Fixed LP bug #954900.Igor Babaev2012-03-221-0/+70
| | | | | | | | | | | | | | | | If the first component of a ref key happened to be a constant appeared after constant row substitution then no store_key element should be created for such a component. Yet create_ref_for_key() erroneously could create such an element that caused construction of invalid ref keys and wrong results for some joins.
* | Merge 5.3->5.5.Igor Babaev2012-03-011-0/+49
|\ \ | |/
| * Merge 5.2->5.3Sergey Petrunya2012-02-241-0/+49
| |\
| | * Back-ported the fix and test cases for bugs #59487 and #43368 fromIgor Babaev2012-02-221-0/+49
| | | | | | | | | | | | the mysql-5.6 code line.
* | | 5.3.4 mergeSergei Golubchik2012-02-151-0/+2
|\ \ \ | |/ /
| * | Adjust test results after Monty's push of the newunknown2012-01-181-0/+2
| | | | | | | | | | | | handler counter Handler_read_rnd_deleted.
* | | 5.3 mergeSergei Golubchik2012-01-131-1/+4
|\ \ \ | |/ /
| * | Back-ported the patch of the mysql-5.6 code line thatIgor Babaev2011-12-241-1/+1
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | fixed several defects in the greedy optimization: 1) The greedy optimizer calculated the 'compare-cost' (CPU-cost) for iterating over the partial plan result at each level in the query plan as 'record_count / (double) TIME_FOR_COMPARE' This cost was only used locally for 'best' calculation at each level, and *not* accumulated into the total cost for the query plan. This fix added the 'CPU-cost' of processing 'current_record_count' records at each level to 'current_read_time' *before* it is used as 'accumulated cost' argument to recursive best_extension_by_limited_search() calls. This ensured that the cost of a huge join-fanout early in the QEP was correctly reflected in the cost of the final QEP. To get identical cost for a 'best' optimized query and a straight_join with the same join order, the same change was also applied to optimize_straight_join() and get_partial_join_cost() 2) Furthermore to get equal cost for 'best' optimized query and a straight_join the new code substrcated the same '0.001' in optimize_straight_join() as it had been already done in best_extension_by_limited_search() 3) When best_extension_by_limited_search() aggregated the 'best' plan a plan was 'best' by the check : 'if ((search_depth == 1) || (current_read_time < join->best_read))' The term '(search_depth == 1' incorrectly caused a new best plan to be collected whenever the specified 'search_depth' was reached - even if this partial query plan was more expensive than what we had already found.
| * | Made the optimizer switch flags 'outer_join_with_cache', 'semijoin_with_cache'Igor Babaev2011-12-151-0/+3
| | | | | | | | | | | | set to 'on' by default.
* | | after merge changes:Sergei Golubchik2011-12-121-2/+2
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | * rename all debugging related command-line options and variables to start from "debug-", and made them all OFF by default. * replace "MySQL" with "MariaDB" in error messages * "Cast ... converted ... integer to it's ... complement" is now a note, not a warning * @@query_cache_strip_comments now has a session scope, not global.
* | | 5.3->5.5 mergeSergei Golubchik2011-11-271-1/+1
|\ \ \ | |/ /
| * | Made the optimizer switch for index condition pushdown set to 'on' by default. Igor Babaev2011-11-211-1/+1
| | |
* | | 5.3->5.5 mergeSergei Golubchik2011-11-221-0/+8
|\ \ \ | |/ /
| * | lp:839387 Assertion `(Item_result)i != TIME_RESULT' failed with CASE + datetimeSergei Golubchik2011-09-071-0/+8
| | | | | | | | | | | | | | | remove incorrect DBUG_ASSERT(). Fix incorrectly used cmp_item::get_comparator() in Item_func_case and Item_equal
* | | merge with 5.3Sergei Golubchik2011-10-191-8/+127
|\ \ \ | |/ / | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | sql/sql_insert.cc: CREATE ... IF NOT EXISTS may do nothing, but it is still not a failure. don't forget to my_ok it. ****** CREATE ... IF NOT EXISTS may do nothing, but it is still not a failure. don't forget to my_ok it. sql/sql_table.cc: small cleanup ****** small cleanup
| * | Set the default to be mrr=off,mrr_sort_keys=off:Sergey Petrunya2011-07-081-1/+1
| | | | | | | | | | | | | | | | | | | | | - Set the default - Adjust the testcases so that 'new' tests are run with optimizations turned on. - Pull out relevant tests from "irrelevant" tests and run them with optimizations on. - Run range.test and innodb.test with both mrr=on and mrr=off
| * | Merge 5.2 -> 5.3 Sergey Petrunya2011-06-241-0/+19
| |\ \ | | |/ | | | | | | (testcase for #798597 now crashes)
| | * Fix for bug lp:798597 Incorrect "Duplicate entry" error with views and GROUP BYMichael Widenius2011-06-241-0/+19
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | mysql-test/r/join.result: Test case for LP:798597 mysql-test/t/join.test: Test case for LP:798597 sql/sql_select.cc: In simplify_joins we reset table->maybe_null for outer join tables that can't ever be NULL. This caused a conflict between the previously calculated items and the group_buffer against the fields in the temporary table that are created as not null thanks to the optimization. The fix is to correct the group by items to also be not_null so that they match the used fields and keys.
| * | MWL#89unknown2011-03-301-1/+1
| |\ \ | | | | | | | | | | | | Merge 5.3 with 5.3-mwl89.
| * | | Fixed LP bugs BUG#729067/730466.Igor Babaev2011-03-121-0/+35
| |/ / | | | | | | | | | | | | Do not reset the value of the item_equal field in the Item_field object once it has been set.
| * | BUG#724275: Crash in JOIN::optimize in maria-5.3Sergey Petrunya2011-03-011-0/+34
| | | | | | | | | | | | | | | - 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).
| * | Merge.Igor Babaev2010-10-301-8/+38
| |\ \
| | * | MWL#128: Added into EXPLAIN output info about types of the used join buffers andIgor Babaev2010-10-181-2/+2
| | | | | | | | | | | | | | | | | | | | about the employed join algorithms. Refactored constructors of the JOIN_CACHE* classes.
| | * | Fixed bug #52636.Igor Babaev2010-09-281-6/+36
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | Applied the fix for bug #47217 from the mysql-6.0 codebase. The patch adds not null predicates generated for the left parts of the equality predicates used for ref accesses. This is done for such predicates both in where conditions and on conditions. For the where conditions the not null predicates were generated but in 5.0/5.1 they actually never were used due to some lame merge from 4.1 to 5.0. The fix for bug #47217 made these predicates to be used in the condition pushed to the tables. Yet only this patch generates not null predicates for equality predicated from on conditions of outer joins. This patch introduces a performance regression that can be observed on a test case from null_key.test. The regression will disappear after the fix for bug #57024 from mariadb-5.1 is pulled into mariadb-5.3. The patch contains many changes in the outputs of the EXPLAIN commands since generated not null predicates are considered as parts of the conditions pushed to join tables and may add 'Usingwhere' in some rows of EXPLAINs where there used to be no such comments.
* | | | merge.Sergei Golubchik2010-11-251-6/+6
|\ \ \ \ | |/ / / | | | | | | | | | | | | | | | | checkpoint. does not compile.
| * | | 5.2 mergeSergei Golubchik2010-10-281-0/+36
| |\ \ \ | | |/ / | |/| / | | |/
| * | MariaDB 5.2 -> MariaDB 5.3 merge Sergey Petrunya2010-06-261-0/+57
| |\ \
| * \ \ Merge MariaDB-5.2 -> MariaDB 5.3Sergey Petrunya2010-03-201-0/+64
| |\ \ \
| * | | | Backport into MariaDB-5.2 the following:Sergey Petrunya2009-12-151-1/+1
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | WL#2474 "Multi Range Read: Change the default MRR implementation to implement new MRR interface" WL#2475 "Batched range read functions for MyISAM/InnoDb" "Index condition pushdown for MyISAM/InnoDB" Igor's fix from sp1r-igor@olga.mysql.com-20080330055902-07614: There could be observed the following problems: 1. EXPLAIN did not mention pushdown conditions from on expressions in the 'extra' column. As a result if a query had no where conditions pushed down to a table, but had on conditions pushed to this table the 'extra' column in the EXPLAIN for the table missed 'using where'. 2. Conditions for ref access were not eliminated from on expressions though such conditions were eliminated from the where condition.
* | | | | mysql-5.1 -> mysql-5.5 mergeSergei Golubchik2010-11-051-0/+36
|\ \ \ \ \ | | |_|_|/ | |/| | |
| * | | | Post fix following fix for bug55531: Disabling testcase forKristofer Pettersson2010-09-071-35/+0
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | bug 55568 because {1} isn't a valid geometry for a geometry collection.
| * | | | Bug #53544: Server hangs during JOIN query in stored procedureAlexey Kopytov2010-08-261-0/+20
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | called twice in a row Queries with nested joins could cause an infinite loop in the server when used from SP/PS. When flattening nested joins, simplify_joins() tracks if the name resolution list needs to be updated by setting fix_name_res to TRUE if the current loop iteration has done any transformations to the join table list. The problem was that the flag was not reset before the next loop iteration leading to unnecessary "fixing" of the name resolution list which in turn could lead to a loop (i.e. circularly-linked part) in that list. This was causing problems on subsequent execution when used together with stored procedures or prepared statements. Fixed by making sure fix_name_res is reset on every loop iteration. mysql-test/r/join.result: Added a test case for bug #53544. mysql-test/t/join.test: Added a test case for bug #53544. sql/sql_select.cc: Make sure fix_name_res is reset on every loop iteration.
| * | | | Bug #55568: user variable assignments crash server when usedAlexey Kopytov2010-08-241-0/+51
| | |_|/ | |/| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | within query The server could crash after materializing a derived table which requires a temporary table for grouping. When destroying the temporary table used to execute a query for a derived table, JOIN::destroy() did not clean up Item_fields pointing to fields in the temporary table. This led to dereferencing a dangling pointer when printing out the items tree later in the outer SELECT. The solution is an addendum to the patch for bug37362: in addition to cleaning up items in tmp_all_fields3, do the same for items in tmp_all_fields1, since now we have an example where this is necessary. mysql-test/r/join.result: Added test cases for bug#55568 and a duplicate bug #54468. mysql-test/t/join.test: Added test cases for bug#55568 and a duplicate bug #54468. sql/field.cc: Make sure field->table_name is not set to NULL in Field::make_field() to avoid assertion failure in Item_field::make_field() after cleaning up items (the assertion fired in udf.test when running the test suite with the patch applied). sql/sql_select.cc: In addition to cleaning up items in tmp_all_fields3, do the same for items in tmp_all_fields1. Introduce a new helper function to avoid code duplication. sql/sql_select.h: Introduce a new helper function to avoid code duplication in JOIN::destroy().
* | | | Bug#52312 lost Handler_read_last status variableMarc Alff2010-08-251-0/+1
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | Before this fix, the ha_read_last_count status variable was defined and updated internally, for never exposed as a system variable. This fix exposes the system variable as "Handler_read_last", for completness of the Handler_read_* system variables interface. Adjusted tests results accordingly.
* | | | Manual merge of mysql-5.1-bugteam to mysql-trunk-merge.Alexey Kopytov2010-04-301-10/+10
|\ \ \ \ | |/ / / | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | Conflicts: Text conflict in configure.in Text conflict in dbug/dbug.c Text conflict in mysql-test/r/ps.result Text conflict in mysql-test/t/ps.test Text conflict in sql/CMakeLists.txt Text conflict in sql/ha_ndbcluster.cc Text conflict in sql/mysqld.cc Text conflict in sql/sql_plugin.cc Text conflict in sql/sql_table.cc
| * | | Null merge.Alexey Kopytov2010-04-271-10/+10
| |\ \ \
| | * | | Backport of the fix for bug #50335 to 5.0.Alexey Kopytov2010-04-271-0/+10
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | The problem was in an incorrect debug assertion. The expression used in the failing assertion states that when finding references matching ORDER BY expressions, there can be only one reference to a single table. But that does not make any sense, all test cases for this bug are valid examples with multiple identical WHERE expressions referencing the same table which are also present in the ORDER BY list. Fixed by removing the failing assertion. We also have to take care of the 'found' counter so that we count multiple references only once. We rely on this fact later in eq_ref_table(). mysql-test/r/join.result: Added a test case for bug #50335. mysql-test/t/join.test: Added a test case for bug #50335. sql/sql_select.cc: Removing the assertion in eq_ref_table() as it does not make any sense. We also have to take care of the 'found' counter so that we count multiple references only once. We rely on this fact later in eq_ref_table().
* | | | | Manual merge of mysql-5.1-bugteam into mysql-trunk-merge.Alexey Kopytov2010-04-031-0/+12
|\ \ \ \ \ | |/ / / / | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | Conflicts: Text conflict in mysql-test/suite/binlog/r/binlog_row_mix_innodb_myisam.result Text conflict in sql/log.cc Text conflict in sql/set_var.cc Text conflict in sql/sql_class.cc
| * | | | 5.0-bugteam->5.1-bugteam mergeSergey Glukhov2010-03-261-0/+12
| |\ \ \ \ | | |/ / /
| | * | | Bug#52177 crash with explain, row comparison, join, text fieldSergey Glukhov2010-03-261-0/+12
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | The crash is the result of an attempt made by JOIN::optimize to evaluate the WHERE condition when no records have been actually read. The fix is to remove erroneous 'outer_join' variable check. mysql-test/r/join.result: test result mysql-test/t/join.test: test case sql/sql_select.cc: removed erroneous 'outer_join' variable check.
* | | | | Automerge.Alexey Kopytov2010-04-021-0/+17
|\ \ \ \ \ | |/ / / /
| * | | | 5.0-bugteam->5.1-bugteam mergeSergey Glukhov2010-03-241-0/+17
| |\ \ \ \ | | |/ / /
| | * | | Bug#48483 crash in get_best_combination()Sergey Glukhov2010-03-241-0/+17
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | The crash happens because greedy_serach can not determine best plan due to wrong inner table dependences. These dependences affects join table sorting which performs before greedy_search starting. In our case table which has real 'no dependences' should be put on top of the list but it does not happen as inner tables have no dependences as well. The fix is to exclude RAND_TABLE_BIT mask from condition which checks if table dependences should be updated. mysql-test/r/join.result: test result mysql-test/t/join.test: test case sql/sql_select.cc: RAND_TABLE_BIT mask should not be counted as it prevents update of inner table dependences. For example it might happen if RAND() function is used in JOIN ON clause.
* | | | | Manual merge from mysql-5.1-bugteam to mysql-trunk-merge.Alexey Kopytov2010-02-261-0/+11
|\ \ \ \ \ | |/ / / / | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | Conflicts: Text conflict in scripts/Makefile.am Text conflict in sql/share/Makefile.am
| * | | | Bug #50335: Assertion `!(order->used & map)' in eq_ref_table Alexey Kopytov2010-02-251-0/+11
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | The problem was in an incorrect debug assertion. The expression used in the failing assertion states that when finding references matching ORDER BY expressions, there can be only one reference to a single table. But that does not make any sense, all test cases for this bug are valid examples with multiple identical WHERE expressions referencing the same table which are also present in the ORDER BY list. Fixed by removing the failing assertion. We also have to take care of the 'found' counter so that we count multiple references only once. We rely on this fact later in eq_ref_table(). mysql-test/r/join.result: Added a test case for bug #50335. mysql-test/t/join.test: Added a test case for bug #50335. sql/sql_select.cc: Removing the assertion in eq_ref_table() as it does not make any sense. We also have to take care of the 'found' counter so that we count multiple references only once. We rely on this fact later in eq_ref_table().
* | | | | Manual merge of mysql-5.1-bugteam to mysql-trunk-merge.Alexey Kopytov2010-02-221-0/+17
|\ \ \ \ \ | |/ / / /
| * | | | Bug#45195 valgrind warnings about uninitialized values in ↵Sergey Glukhov2010-02-101-0/+17
| | |_|/ | |/| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | store_record_in_cache() The problem becomes apparent only if HAVE_purify is undefined. It related to the part of code placed in open_table_from_share() fuction where we initialize record buffer only if HAVE_purify is enabled. So in case of HAVE_purify=OFF record buffer is not initialized on open table stage. Next we read key, find NULL value and update appropriate null bit but do not update record buffer. After that the record is stored in the join cache(store_record_in_cache). For CHAR fields we strip trailing spaces and in our case this procedure uses uninitialized record buffer. The fix is to skip stripping space procedure in case of null values for CHAR fields(partially based on 6.0 JOIN_CACHE implementation). mysql-test/r/join.result: test case mysql-test/t/join.test: test case sql/field.cc: code updated according to new CACHE_FIELD struct sql/sql_select.cc: code updated according to new CACHE_FIELD struct sql/sql_select.h: CACHE_FIELD struct: added new fields: Field *field, uint type; removed fields: Field_blob *blob_field, bool strip;
* | | | Manual merge from mysql-trunk-merge.Alexander Nozdrin2009-11-061-0/+54
|\ \ \ \ | |/ / /
| * | | mergeGeorgi Kodinov2009-10-301-0/+54
| |\ \ \ | | |/ /
View Lorry Controller Status