summaryrefslogtreecommitdiff
path: root/mysql-test/r/subselect.result
Commit message (Collapse)AuthorAgeFilesLines
* merge with 5.3Sergei Golubchik2011-10-191-88/+401
|\ | | | | | | | | | | | | | | | | | | | | | | | | 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
| * Fixed LP bug #826279.Igor Babaev2011-08-191-0/+25
| | | | | | | | | | | | | | | | | | When the WHERE/HAVING condition of a subquery has been transformed by the optimizer the pointer stored the 'where'/'having' field of the SELECT_LEX structure used for the subquery must be updated accordingly. Otherwise the pointer may refer to an invalid item. This can lead to the reported assertion failure for some queries with correlated subqueries
| * Fix bug lp:813473unknown2011-08-171-0/+73
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | The bug is a duplicate of MySQL's Bug#11764086, however MySQL's fix is incomplete for MariaDB, so this fix is slightly different. In addition, this patch renames Item_func_not_all::top_level() to is_top_level_item() to make it in line with the analogous methods of Item_in_optimizer, and Item_subselect. Analysis: It is possible to determine whether a predicate is NULL-rejecting only if it is a top-level one. However, this was not taken into account for Item_in_optimizer. As a result, a NOT IN predicate was erroneously considered as NULL-rejecting, and the NULL-complemented rows generated by the outer join were rejected before being checked by the NOT IN predicate. Solution: Change Item_in_optimizer to be considered as NULL-rejecting only if it a top-level predicate.
| * Bug lp:781508: Take relevant test cases from MySQL 5.6 feature preview treesunknown2011-08-121-0/+117
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | Identified all test cases in the MySQL file subquery.inc that are not present in MariaDB. This patch adds the test cases that are: - not present in MySQL 5.5, and - already fixed in MariaDB 5.3 The patch adds test cases for the following mysql-trunk bugs: - Bug#12763207 - not a bug, mysql-trunk, added test case - BUG#50257 - not a bug, mysql-trunk, added test case - Bug 11765699 - not a bug, mysql-trunk, added test case - BUG#12616253 - not a bug, mysql-trunk, added test case The comparison was based on the following version of mysql-trunk: revno: 3350 [merge] committer: Marko Mäkelä <marko.makela@oracle.com> branch nick: mysql-trunk timestamp: Mon 2011-08-08 12:42:09 +0300 message: Merge mysql-5.5 to mysql-trunk.
| * Fix bug lp:817384unknown2011-08-091-2/+2
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | This bug is a special case of lp:813447. Analysis: Constant optimization finds that the condition t2.a = 1 can be used to access the primary key of table 't2'. As a result both outer table t1,t2 are considered as constant when we reach the execution phase. At the same time, during constant optimization, the IN predicate is not evaluated because it is expensive. When execution of the outer query reaches do_select(), control flow enter the branch: if (join->table_count == join->const_tables) { ... } This branch checks only the WHERE and HAVING clauses, but doesn't check the ON clauses of the query. Since the IN predicate was not evaluated during optimization, it is not evaluated at all, thus execution doesn't detect that the ON clause is FALSE. Solution: Similar to the patch for bug lp:813447, exclude system tables from constant substitution based on unique key lookups if there is an expensive ON condition on the inner table.
| * Merge.Igor Babaev2011-07-211-2/+3
| |\
| | * Made the optimizer switches 'derived_merge' and 'derived_with_keys'Igor Babaev2011-07-211-2/+3
| | | | | | | | | | | | | | | off by default.
| * | Fix of LP BUG#780386.unknown2011-07-201-12/+31
| |/ | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | ALL subquery should return TRUE if subquery rowa set is empty independently of left part. The problem was that Item_func_(eq,ne,gt,ge,lt,le) do not call execution of second argument if first is NULL no in this case subquery will not be executed and when Item_func_not_all calls any_value() of the subquery or aggregation function which report that there was rows. So for NULL < ALL (SELECT...) result was FALSE instead of TRUE. Fix is just swapping of arguments of Item_func_(eq,ne,gt,ge,lt,le) (with changing the operation if it is needed) so that result will be the same (for examole a < b is equal to b > a). This fix exploit the fact that first argument will be executed in any case. mysql-test/r/subselect.result: The test suite added. mysql-test/r/subselect_no_mat.result: The test suite added. mysql-test/r/subselect_no_opts.result: The test suite added. mysql-test/r/subselect_no_semijoin.result: The test suite added. mysql-test/r/subselect_scache.result: The test suite added. mysql-test/t/subselect.test: The test suite added. sql/item_cmpfunc.cc: Swap arguments creation methods added. sql/item_cmpfunc.h: Swap arguments creation methods added. sql/item_subselect.cc: Swap arguments of the comparison.
| * Make subquery cache off by default.unknown2011-07-151-17/+17
| | | | | | | | | | | | | | mysql-test/r/subselect_scache.result: Test with subquery cache on. mysql-test/t/subselect_scache.test: Test with subquery cache on.
| * Set the default to be mrr=off,mrr_sort_keys=off:Sergey Petrunya2011-07-081-0/+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 the fix for bug lp:802979unknown2011-07-061-3/+3
| |\
| | * Fix LP bug lp:802979unknown2011-07-041-3/+3
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | Analysis: This bug consists of two related problems that are result of too early evaluation of single-row subqueries during the optimization phase of the outer query. Several optimizer code paths try to evaluate single-row subqueries in order to produce a constant and use that constant for further optimzation. When the execution of the subquery peforms destructive changes to the representation of the subquery, and these changes are not anticipated by the subsequent optimization phases of the outer query, we tipically get a crash or failed assert. Specifically, in this bug the inner-most suqbuery with DISTINCT triggers a substitution of the original JOIN object by a single-table JOIN object with a temp table needed to perform the DISTINCT operation (created by JOIN::make_simple_join). This substitution breaks EXPLAIN because: a) in the first example JOIN::cleanup no longer can reach the original table of the innermost subquery, and close all indexes, and b) in this second test query, EXPLAIN attempts to print the name of the internal temp table, and crashes because the temp table has no name (NULL pointer instead). Solution: a) fully disable subquery evaluation during optimization in all cases - both for constant propagation and range optimization, and b) change JOIN::join_free() to perform cleanup irrespective of EXPLAIN or not.
| * | Change the default @@optimizer_switch setting fromSergey Petrunya2011-07-051-3/+4
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | semijoin=on,firstmatch=on,loosescan=on to semijoin=off,firstmatch=off,loosescan=off Adjust the testcases: - Modify subselect*.test and join_cache.test so that all tests use the same execution paths as before (i.e. optimizations that are being tested are enabled) - Let all other test files run with the new default settings (i.e. with new optimizations disabled) - Copy subquery testcases from these files into t/subselect_extra.test which will run them with new optimizations enabled.
| * | Fix buildbot failures:Sergey Petrunya2011-06-301-4/+4
| |/ | | | | | | | | | | | | | | | | | | | | | | - JOIN::prepare would have set JOIN::table_count to incorrect value (bad merge of MWL 106) - optimize_keyuse() would use table-bit as table number (the change in optimize_keyuse is also the reason for query plan changes. Not expected to have much effect because only handles cases of no index statistics) - st_select_lex::register_dependency_item() ignored the fact that some of the selects on the dependency paths could have been merged to their parents (because they were mergeable VIEWs) - Undo the incorrect fix in Item_subselect::recalc_used_tables(): do not call fix_after_pullout() for Item_subselect::Ref_to_outside members.
| * Fixed LP bug #793436.Igor Babaev2011-06-061-2/+2
| | | | | | | | | | | | | | | | | | | | When looking for the execution plan of a derived table to be materialized JOIN::optimize finds out that all joined tables of the derived table contain not more than one row then the derived table should be maretialized at the optimization stage. Added a test case for the bug. Adjusted results in other test cases.
| * Merged the code of mwl 106 into the latest 5.3 with mwl 90 pushed.Igor Babaev2011-06-041-11/+10
| |\ | | | | | | | | | | | | | | | Resolved all conflicts and failures.
| | * MergeIgor Babaev2011-05-201-11/+10
| | |\
| | | * Merged the code of MWL#106 into 5.3Igor Babaev2011-05-161-11/+10
| | | |\ | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | Resolved all conflicts, bad merges and fixed a few minor bugs in the code. Commented out the queries from multi_update, view, subselect_sj, func_str, derived_view, view_grant that failed either with crashes in ps-protocol or with wrong results. The failures are clear indications of some bugs in the code and these bugs are to be fixed.
| | | | * Merge of the mwl106 tree into the latest 5.3 tree.Igor Babaev2010-09-231-11/+10
| | | | |\ | | | | | | | | | | | | | | | | | | Resolved conflicts. Adjusted some test results
| | | | | * MWL#106: Backport optimizations for derived tables and views.Igor Babaev2010-05-261-11/+10
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | The main consolidated patch.
| * | | | | Merge 5.3->main -> 5.3-mwl90Sergey Petrunya2011-05-291-1/+1
| |\ \ \ \ \
| | * | | | | Post-merge fixes part#2Sergey Petrunya2011-05-261-2/+2
| | | | | | |
| | * | | | | Merge MWL#90 with 5.3-mainSergey Petrunya2011-05-251-3/+3
| | |\ \ \ \ \
| | | * \ \ \ \ MWL#90: auto-merge with 5.3-mainSergey Petrunya2011-05-091-8/+12
| | | |\ \ \ \ \
| | | | * \ \ \ \ Merge MWL#90 into 5.3-mainSergey Petrunya2011-04-301-8/+12
| | | | |\ \ \ \ \
| | | | | * \ \ \ \ Merge MWL#90 with main 5.3 treeSergey Petrunya2011-04-021-8/+12
| | | | | |\ \ \ \ \
| | | | | | * \ \ \ \ Merge 5.3 -> 5.3-subqueries-mwl90Sergey Petrunya2011-03-011-0/+11
| | | | | | |\ \ \ \ \
| | | | | | * \ \ \ \ \ MWL#90: Subqueries: Inside-out execution for materialized non-sj subqueriesSergey Petrunya2011-02-151-8/+12
| | | | | | |\ \ \ \ \ \ | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | - Merge with 5.3 (3)
| | | | | | | * \ \ \ \ \ MWL#90: Subqueries: Inside-out execution for materialized non-sj subqueriesSergey Petrunya2010-12-091-7/+11
| | | | | | | |\ \ \ \ \ \ | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | - Merge into 5.3-main
| | | | | | | | * \ \ \ \ \ MWL#90: Subqueries: Inside-out execution for materialized non-sj subqueriespsergey2010-07-111-7/+11
| | | | | | | | |\ \ \ \ \ \ | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | - Merge with MariaDB 5.3 -main
| | | | | | | | | * | | | | | MWL#90: Code cleanup: Unification of merged and non-merged SJM nests processingSergey Petrunya2010-06-051-1/+1
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | - Make join buffering code to take into account that JOIN_TABs are not a linear array anymore.
| | | | | | | | | * | | | | | MWL#90: Subqueries: Inside-out execution for non-semijoin materialized ↵Sergey Petrunya2010-05-271-6/+6
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | subqueries that are AND-parts of the WHERE - Change "SUBQUERY#n" to "<subquery{n}>" in EXPLAIN output. We need to it to be lowercase so that EXPLAIN results do not differ in case between systems with case-sensitive and case-insensitive filesystems. - Remove garbage comments, add better comments.
| | | | | | | | | * | | | | | Always refer to materialized table as "SUBSELECT#%d" where %d is select numberSergey Petrunya2010-04-071-2/+2
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | - for Item-based materialization, it was "materialized subselect" - for SJ-Materialization it was "subquery%d"
| | | | | | | | | * | | | | | MWL#90: Subqueries: Inside-out execution for non-semijoin materializedSergey Petrunya2010-04-061-6/+10
| | | | | | | | | | |_|_|_|/ | | | | | | | | | |/| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | subqueries that are AND-parts of the WHERE - First code (needs cleanup).
| * | | | | | | | | | | | | automatic merge with 5.3Michael Widenius2011-05-281-4/+4
| |\ \ \ \ \ \ \ \ \ \ \ \ \
| | * \ \ \ \ \ \ \ \ \ \ \ \ MWL#89 - automatic merge with 5.3unknown2011-05-171-2/+2
| | |\ \ \ \ \ \ \ \ \ \ \ \ \ | | | |_|_|_|_|_|_|_|_|_|/ / / | | |/| | | | | | | | | | | |
| | | * | | | | | | | | | | | Fix LP BUG#778413unknown2011-05-171-2/+2
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | Analysis: The subquery is evaluated first during ref-optimization of the outer query because the subquery is considered constant from the perspective of the outer query. Thus an attempt is made to evaluate the MAX subquery and use the new constant to drive an index nested loops join. During this evaluation the inner-most subquery replaces the JOIN_TAB with a new one that fetches the data from a temp table. The function select_describe crashes at the lines: TABLE_LIST *real_table= table->pos_in_table_list; item_list.push_back(new Item_string(real_table->alias, strlen(real_table->alias), cs)); because 'table' is a temp table, and it has no corresponding table reference. This 'real_table' is NULL, and real_table->alias results in a crash. Solution: In the spirit of MWL#89 prevent the evaluation of expensive predicates during optimization. This patch prevents the evaluation of expensive predicates during ref optimization. sql/item_subselect.h: Remove unused class member. Not needed for the fix, but noticed now and removed.
| * | | | | | | | | | | | | | Merge with 5.1-microsecondsMichael Widenius2011-05-281-8/+8
| |\ \ \ \ \ \ \ \ \ \ \ \ \ \ | | |/ / / / / / / / / / / / / | |/| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | A lot of small fixes and new test cases. client/mysqlbinlog.cc: Cast removed client/mysqltest.cc: Added missing DBUG_RETURN include/my_pthread.h: set_timespec_time_nsec() now only takes one argument mysql-test/t/date_formats.test: Remove --disable_ps_protocl as now also ps supports microseconds mysys/my_uuid.c: Changed to use my_interval_timer() instead of my_getsystime() mysys/waiting_threads.c: Changed to use my_hrtime() sql/field.h: Added bool special_const_compare() for fields that may convert values before compare (like year) sql/field_conv.cc: Added test to get optimal copying of identical temporal values. sql/item.cc: Return that item_int is equal if it's positive, even if unsigned flag is different. Fixed Item_cache_str::save_in_field() to have identical null check as other similar functions Added proper NULL check to Item_cache_int::save_in_field() sql/item_cmpfunc.cc: Don't call convert_constant_item() if there is nothing that is worth converting. Simplified test when years should be converted sql/item_sum.cc: Mark cache values in Item_sum_hybrid as not constants to ensure they are not replaced by other cache values in compare_datetime() sql/item_timefunc.cc: Changed sec_to_time() to take a my_decimal argument to ensure we don't loose any sub seconds. Added Item_temporal_func::get_time() (This simplifies some things) sql/mysql_priv.h: Added Lazy_string_decimal() sql/mysqld.cc: Added my_decimal constants max_seconds_for_time_type, time_second_part_factor sql/table.cc: Changed expr_arena to be of type CONVENTIONAL_EXECUTION to ensure that we don't loose any items that are created by fix_fields() sql/tztime.cc: TIME_to_gmt_sec() now sets *in_dst_time_gap in case of errors This is needed to be able to detect if timestamp is 0 storage/maria/lockman.c: Changed from my_getsystime() to set_timespec_time_nsec() storage/maria/ma_loghandler.c: Changed from my_getsystime() to my_hrtime() storage/maria/ma_recovery.c: Changed from my_getsystime() to mmicrosecond_interval_timer() storage/maria/unittest/trnman-t.c: Changed from my_getsystime() to mmicrosecond_interval_timer() storage/xtradb/handler/ha_innodb.cc: Added support for new time,datetime and timestamp unittest/mysys/thr_template.c: my_getsystime() -> my_interval_timer() unittest/mysys/waiting_threads-t.c: my_getsystime() -> my_interval_timer()
| | * | | | | | | | | | | | | wl#173 - temporal types with sub-second resolutionSergei Golubchik2011-03-011-9/+9
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | and collateral changes. * introduce my_hrtime_t, my_timediff_t, and conversion macros * inroduce TIME_RESULT, but it can only be returned from Item::cmp_type(), never from Item::result_type() * pack_time/unpack_time function for "packed" representation of MYSQL_TIME in a longlong that can be compared * ADDTIME()/SUBTIME()/+- INTERVAL now work with TIME values * numbers aren't quoted in EXPLAIN EXTENDED * new column I_S.COLUMNS.DATETIME_PRECISION * date/time values are compares to anything as date/time, not as strings or numbers. * old timestamp(X) is no longer supported * MYSQL_TIME to string conversion functions take precision as an argument * unified the warnings from Field_timestamp/datetime/time/date/newdate store methods * Field_timestamp_hires, Field_datetime_hires, Field_time_hires * Field_temporal * Lazy_string class to pass a value (string, number, time) polymorphically down the stack * make_truncated_value_warning and Field::set_datetime_warning use Lazy_string as an argument, removed char*/int/double variants * removed Field::can_be_compared_as_longlong(). Use Field::cmp_type() == INT_RESULT instead * introduced Item::cmp_result() instead of Item::is_datetime() and Item::result_as_longlong() * in many cases date/time types are treated like other types, not as special cases * greatly simplified Arg_comparator (regarding date/time/year code) * SEC_TO_TIME is real function, not integer. * microsecond precision in NOW, CURTIME, etc * Item_temporal. All items derived from it only provide get_date, but no val* methods * replication of NOW(6) * Protocol::store(time) now takes the precision as an argument * @@TIMESTAMP is a double client/mysqlbinlog.cc: remove unneded casts include/my_sys.h: introduce my_hrtime_t, my_timediff_t, and conversion macros include/my_time.h: pack_time/unpack_time, etc. convenience functions to work with MYSQL_TIME::second_part libmysql/libmysql.c: str_to_time() is gone. str_to_datetime() does it now. my_TIME_to_str() takes the precision as an argument mysql-test/include/ps_conv.inc: time is not equal to datetime anymore mysql-test/r/distinct.result: a test for an old MySQL bug mysql-test/r/explain.result: numbers aren't quoted in EXPLAIN EXTENDED mysql-test/r/func_default.result: numbers aren't quoted in EXPLAIN EXTENDED mysql-test/r/func_sapdb.result: when decimals=NOT_FIXED_DEC it means "not fixed" indeed mysql-test/r/func_test.result: numbers aren't quoted in EXPLAIN EXTENDED mysql-test/r/func_time.result: ADDTIME()/SUBTIME()/+- INTERVAL now work with TIME values mysql-test/r/having.result: numbers aren't quoted in EXPLAIN EXTENDED mysql-test/r/information_schema.result: new column I_S.COLUMNS.DATETIME_PRECISION mysql-test/r/join_outer.result: numbers aren't quoted in EXPLAIN EXTENDED mysql-test/r/metadata.result: TIMESTAMP no longer has zerofill flag mysql-test/r/range.result: invalid datetime is not compared with as a string mysql-test/r/select.result: NO_ZERO_IN_DATE, etc only affect storage - according to the manual numbers aren't quoted in EXPLAIN EXTENDED mysql-test/r/subselect.result: numbers aren't quoted in EXPLAIN EXTENDED mysql-test/r/sysdate_is_now.result: when decimals=NOT_FIXED_DEC it means "not fixed" indeed mysql-test/r/type_blob.result: TIMESTAMP(N) is not deprecated mysql-test/r/type_timestamp.result: old TIMESTAMP(X) semantics is not supported anymore mysql-test/r/union.result: numbers aren't quoted in EXPLAIN EXTENDED mysql-test/r/varbinary.result: numbers aren't quoted in EXPLAIN EXTENDED mysql-test/t/distinct.test: test for an old MySQL bug mysql-test/t/func_time.test: +- INTERVAL now works with TIME values mysql-test/t/select.test: typo mysql-test/t/subselect.test: only one error per statement, please mysql-test/t/system_mysql_db_fix40123.test: old timestamp(X) is no longer supported mysql-test/t/system_mysql_db_fix50030.test: old timestamp(X) is no longer supported mysql-test/t/system_mysql_db_fix50117.test: old timestamp(X) is no longer supported mysql-test/t/type_blob.test: old timestamp(X) is no longer supported mysql-test/t/type_timestamp.test: old timestamp(X) is no longer supported mysys/my_getsystime.c: functions to get the time with microsecond precision mysys/my_init.c: move the my_getsystime.c initialization code to my_getsystime.c mysys/my_static.c: no need to make these variables extern mysys/my_static.h: no need to make these variables extern scripts/mysql_system_tables.sql: old timestamp(X) is no longer supported scripts/mysql_system_tables_fix.sql: old timestamp(X) is no longer supported scripts/mysqlhotcopy.sh: old timestamp(X) is no longer supported sql-common/my_time.c: * call str_to_time from str_to_datetime, as appropriate * date/time to string conversions take precision as an argument * number_to_time() * TIME_to_double() * pack_time() and unpack_time() sql/event_data_objects.cc: cast is not needed my_datetime_to_str() takes precision as an argument sql/event_db_repository.cc: avoid dangerous downcast (because the pointer is not always Field_timestamp, see events_1.test) sql/event_queue.cc: avoid silly double-work for cond_wait (having an endpoint of wait, subtract the current time to get the timeout, and use set_timespec() macro to fill in struct timespec, by adding the current time to the timeout) sql/field.cc: * remove virtual Field::get_time(), everyone should use only Field::get_date() * remove lots of #ifdef WORDS_BIGENDIAN * unified the warnings from Field_timestamp/datetime/time/date/newdate store methods * Field_timestamp_hires, Field_datetime_hires, Field_time_hires * Field_temporal * make_truncated_value_warning and Field::set_datetime_warning use Lazy_string as an argument, removed char*/int/double variants sql/field.h: * remove virtual Field::get_time(), everyone should use only Field::get_date() * remove lots of #ifdef WORDS_BIGENDIAN * unified the warnings from Field_timestamp/datetime/time/date/newdate store methods * Field_timestamp_hires, Field_datetime_hires, Field_time_hires * Field_temporal * make_truncated_value_warning and Field::set_datetime_warning use Lazy_string as an argument, removed char*/int/double variants * removed Field::can_be_compared_as_longlong(). Use Field::cmp_type() == INT_RESULT instead sql/filesort.cc: TIME_RESULT, cmp_time() sql/item.cc: * numbers aren't quoted in EXPLAIN EXTENDED * Item::cmp_result() instead of Item::is_datetime() and Item::result_as_longlong() * virtual Item::get_time() is gone * Item_param::field_type() is set correctly * Item_datetime, for a datetime constant * time to anything is compared as a time * Item_cache::print() prints the value is available * bug fixed in Item_cache_int::val_str() sql/item.h: * Item::print_value(), to be used from Item_xxx::print() when needed * Item::cmp_result() instead of Item::is_datetime() and Item::result_as_longlong() * virtual Item::get_time() is gone * Item_datetime, for a datetime constant * better default for cast_to_int_type() * Item_cache objects now *always* have the field_type() set sql/item_cmpfunc.cc: * get_year_value, get_time_value are gone. get_datetime_value does it all * get_value_a_func, get_value_b_func are gone * can_compare_as_dates() is gone too, TIME_RESULT is used instead * cmp_type() instead or result_type() when doing a comparison * compare_datetime and compate_e_datetime in the comparator_matrix, is_nulls_eq is gone * Item::cmp_result() instead of Item::is_datetime() and Item::result_as_longlong() sql/item_cmpfunc.h: greatly simplified Arg_comparator sql/item_create.cc: * fix a bug in error messages in CAST sql/item_func.cc: Item::cmp_result() instead of Item::is_datetime() and Item::result_as_longlong() mention all possibitiles in switch over Item_result values, or use default: sql/item_row.h: overwrite the default cmp_type() for Item_row, as no MYSQL_TYPE_xxx value corresponds to ROW_RESULT sql/item_timefunc.cc: rewrite make_datetime to support precision argument SEC_TO_TIME is real function, not integer. many functions that returned temporal values had duplicate code in val_* methods, some of them did not have get_date() which resulted in unnecessary date->str->date conversions. Now they all are derived from Item_temporal_func and *only* provide get_date, not val* methods. many fixes to set decimals (datetime precision) correctly. sql/item_timefunc.h: SEC_TO_TIME is real function, not integer. many functions that returned temporal values had duplicate code in val_* methods, some of them did not have get_date() which resulted in unnecessary date->str->date conversions. Now they all are derived from Item_temporal_func and *only* provide get_date, not val* methods. many fixes to set decimals (datetime precision) correctly. sql/log_event.cc: replication of NOW(6) sql/log_event.h: replication of NOW(6) sql/mysql_priv.h: Lazy_string class to pass a value (string, number, time) polymorphically down the stack. make_truncated_value_warning() that uses it. sql/mysqld.cc: datetime in Arg_comparator::comparator_matrix sql/opt_range.cc: cleanup: don't disable warnings before calling save_in_field_no_warnings() sql/protocol.cc: Protocol::store(time) now takes the precision as an argument sql/protocol.h: Protocol::store(time) now takes the precision as an argument sql/rpl_rli.cc: small cleanup sql/set_var.cc: SET TIMESTAMP=double sql/set_var.h: @@TIMESTAMP is a double sql/share/errmsg.txt: precision and scale are unsigned sql/slave.cc: replication of NOW(6) sql/sp_head.cc: cleanup sql/sql_class.cc: support for NOW(6) sql/sql_class.h: support for NOW(6) sql/sql_insert.cc: support for NOW(6) sql/sql_select.cc: use item->cmp_type(). move a comment where it belongs sql/sql_show.cc: new column I_S.COLUMNS.DATETIME_PRECISION sql/sql_yacc.yy: TIME(X), DATETIME(X), cast, NOW(X), CURTIME(X), etc sql/time.cc: fix date_add_interval() to support MYSQL_TIMESTAMP_TIME argument storage/myisam/ha_myisam.cc: TIMESTAMP no longer carries ZEROFIELD flag, still we keep MYI file compatible. strings/my_vsnprintf.c: warnings tests/mysql_client_test.c: old timestamp(X) does not work anymore datetime is no longer equal to time
| * | | | | | | | | | | | | | Merge with 5.3 mainMichael Widenius2011-05-161-44/+63
| |\ \ \ \ \ \ \ \ \ \ \ \ \ \ | | | |/ / / / / / / / / / / / | | |/| | | | | | | | | | | |
| | * | | | | | | | | | | | | Automatic merge with MWL148unknown2011-05-121-11/+11
| | |\ \ \ \ \ \ \ \ \ \ \ \ \ | | | |_|/ / / / / / / / / / / | | |/| | | | | | | | | | | |
| | | * | | | | | | | | | | | MergeOleksandr Byelkin2011-05-061-11/+11
| | | |\ \ \ \ \ \ \ \ \ \ \ \ | | | | | |_|_|_|_|_|_|_|_|/ / | | | | |/| | | | | | | | | |
| | | | * | | | | | | | | | | Moving max/min optimization from prepare to optimization phase. MWL#148Oleksandr Byelkin2011-05-041-11/+11
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | mysql-test/r/explain.result: fixed results (new item) mysql-test/r/subselect.result: fixed results (new item) mysql-test/r/subselect_no_mat.result: fixed results (new item) mysql-test/r/subselect_no_opts.result: fixed results (new item) mysql-test/r/subselect_no_semijoin.result: Fixed results (new item) mysql-test/suite/pbxt/r/subselect.result: Fixed results (new item) mysql-test/t/explain.test: Fixed results (correct behaviour) sql/item_cmpfunc.cc: Pass through for max/min sql/item_subselect.cc: moving max/min sql/item_subselect.h: moving max/min sql/mysql_priv.h: new uncacheble flags added sql/opt_subselect.cc: maxmin moved. sql/opt_subselect.h: New function for maxmin. sql/sql_class.h: debug code sql/sql_lex.cc: Fixed flags. Limit setting fixed. sql/sql_lex.h: 2 new flags. sql/sql_select.cc: Prepare divided on 2 function to be able recollect some info after transformation. sql/sql_select.h: Prepare divided on 2 functions.
| | * | | | | | | | | | | | | Merge fix for BUG#779885 Sergey Petrunya2011-05-111-0/+20
| | |\ \ \ \ \ \ \ \ \ \ \ \ \ | | | |/ / / / / / / / / / / / | | |/| | | | | | | | | | | |
| | | * | | | | | | | | | | | BUG#779885: Crash in eliminate_item_equal with materialization=on in maria-5.3Sergey Petrunya2011-05-101-0/+20
| | | | |_|/ / / / / / / / / | | | |/| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | - In eliminate_item_equal(), we could end up in a situation where: = The multiple equality has a constant C1 (and so it is the "head item") = The join order was such that we've generated "sj_inner_table1=C1" equality, and now are looking to generate "sj_inner_table2_=..." equality. When looking for what should be the other member of equality, we run Item *head_item= current_sjm? current_sjm_head: head; which sees current_sjm!=NULL, and takes current_sjm_head (which is NULL because the constant C1 is the head for all cases). - Fixed in a trivial way: take current_sjm_head if we don't have constant.
| | * | | | | | | | | | | | MWL#89unknown2011-05-051-1/+1
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | Adjusted test results after merge.
| | * | | | | | | | | | | | MWL#89unknown2011-05-051-36/+34
| | |\ \ \ \ \ \ \ \ \ \ \ \ | | | |/ / / / / / / / / / / | | |/| / / / / / / / / / / | | | |/ / / / / / / / / / Merge with main 5.3
| | | * | | | | | | | | | MWL#89unknown2011-05-021-36/+34
| | | |\ \ \ \ \ \ \ \ \ \ | | | | |_|/ / / / / / / / | | | |/| | | | | | | | | | | | | | | | | | | | | | Merge with 5.3
| | | | * | | | | | | | | Fix LP BUG#715069unknown2011-04-201-5/+5
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | Analysis: The wrong result is a consquence of sorting the subquery result and then selecting only the first row due to the artificial LIMIT 1 introduced by the fix_fields phase. Normally, if there is an ORDER BY in a subquery, the ORDER is removed (Item_in_subselect::select_in_like_transformer), however if a GROUP BY is transformed into ORDER, this happens later, after the removal of the ORDER clause of subqueries, so we end up with a subquery with an ORDER clause, and an artificially added LIMIT 1. The reason why the same works in the main 5.3 without MWL#89, is that the 5.3 performs all subquery transformations, including IN->EXISTS before JOIN::optimize(). The beginning of JOIN::optimize does: if (having || (select_options & OPTION_FOUND_ROWS)) select_limit= HA_POS_ERROR; which sets the limit back to infinity, thus 5.3 sorts the whole subquery result, and IN performs the lookup into all subquery result rows. Solution: Sorting of subqueries without LIMIT is meaningless. Since LIMIT in subqueries is not supported, the patch removes sorting by setting join->skip_sort_order= true for each subquery JOIN object. This improves a number of execution plans to not perform unnecessary sorting at all.
| | | | * | | | | | | | | MWL#89unknown2011-03-301-34/+32
| | | | |\ \ \ \ \ \ \ \ \ | | | | | |_|/ / / / / / / | | | | |/| | | | | | | | | | | | | | | | | | | | | Merge 5.3 with 5.3-mwl89.
View Lorry Controller Status