summaryrefslogtreecommitdiff
path: root/sql/sql_select.cc
Commit message (Collapse)AuthorAgeFilesLines
* MDEV-23596: Assertion `tab->ref.use_count' failed in join_read_key_unlock_rowbb-10.1-varunVarun Gupta2020-08-271-0/+3
| | | | | | | | The issue here was that the query was using ORDER BY LIMIT optimzation where the access method was changed from EQ_REF access to an index scan (index that would resolve the ORDER BY clause). But the parameter READ_RECORD::unlock_row was not reset to rr_unlock_row, which is used when the access method is not EQ_REF access.
* MDEV-23221: A subquery causes crashSergei Petrunia2020-07-241-1/+1
| | | | | | | | | | | | | | | | | | | | * Fix the crash: IN-to-EXISTS rewrite causes an error (and so JOIN::optimize() fails with an error, too), don't call update_used_tables(). Terminate the query execution instead. * Fix the cause of the error in the IN-to-EXISTS rewrite: don't do the rewrite if doing it will cause an error of this kind: This version of MariaDB doesn't yet support 'SUBQUERY in ROW in left expression of IN/ALL/ANY' * Fix another issue exposed by this testcase: JOIN::setup_subquery_caches() may be invoked before any select has saved its query plan, and will crash because none of the SELECTs has called create_explain_query_if_not_exists() to create the Explain Data Structure for this SELECT. TODO: When merging this to 10.2, remove the poorly-placed call to create_explain_query_if_not_exists made by fix for M_D_E_V-16153
* Revert "MDEV-22830: SQL_CALC_FOUND_ROWS not working properly for single ↵Oleksandr Byelkin2020-06-101-1/+1
| | | | | | SELECT for DUAL" This reverts commit 443391236d20cd0303fcc9957eb49a6aaf28316e.
* MDEV-22830: SQL_CALC_FOUND_ROWS not working properly for single SELECT for DUALrucha1742020-06-091-1/+1
| | | | | | | | | | | In case of SELECT without tables which returns either 0 or 1 rows, JOIN::exec_inner() did not check if the flag representing SQL_CALC_FOUND_ROWS is set or not and send_records was direclty assigned 0. So SELECT FOUND_ROWS() was giving 0 in the output. Now it checks if the flag is set, if it is set send_record=1 else 0. 1 is the number of rows that could have been sent to the client if the SELECT query had SQL_CALC_FOUND_ROWS. It is 0 when no rows were sent because the SELECT query did not have SQL_CALC_FOUND_ROWS.
* Fix compile warning:Sergei Petrunia2020-03-111-1/+1
| | | | | | | | It was: implicit conversion from 'ha_rows' (aka 'unsigned long long') to 'double' changes value from 18446744073709551615 to 18446744073709551616 Follow what JOIN::get_examined_rows() does for similar code.
* MDEV-21341: Fix UBSAN failures, part 8: fix error in ↵Sergei Petrunia2020-01-151-4/+8
| | | | | | | compare_fields_by_table_order Dont assign Item_field variables to point to Item_string objects (even if we don't make any dangerous calls for them).
* MDEV-21341: Fix optimizer-related UBSAN failures, part #1:Sergei Petrunia2019-12-181-1/+1
| | | | Fix wrong typecast
* Fix incorrect DBUG_ENTER message for join_read_lastSeth Shelnutt2019-11-261-1/+1
|
* MDEV-20519: Query plan regression with optimizer_use_condition_selectivity > 1Varun Gupta2019-11-071-4/+0
| | | | | | | | | | | | | | | | The issue here is the wrong estimate of the cardinality of a partial join, the cardinality is too high because the function table_cond_selectivity() returns an absurd number 100 while selectivity cannot be greater than 1. When accessing table t by outer reference t1.a via index we do not perform any range analysis for t. Yet we see TABLE::quick_key_parts[key] and TABLE->quick_rows[key] contain a non-zero value though these should have been remained untouched and equal to 0. Thus real cause of the problem is that TABLE::init does not clean the arrays TABLE::quick_key_parts[] and TABLE::>quick_rows[]. It should have done it because the TABLE structure created for any instance of a table can be reused for many queries.
* MDEV-20424: New default value for optimizer_use_condition-selectivity leads ↵Varun Gupta2019-11-011-0/+3
| | | | | | | | to bad plan In the function prev_record_reads where one finds the different row combinations for a subset of partial join, it did not take into account the selectivity of tables involved in the subset of partial join.
* MDEV-20576 A new assertion added to check validity of calculatedIgor Babaev2019-09-121-0/+15
| | | | | | | | | | | | | | | | | | | | | | selectivity values fails After having set the assertion that checks validity of selectivity values returned by the function table_cond_selectivity() a test case from order_by.tesst failed. The failure occurred because range optimizer could return as an estimate of the cardinality of the ranges built for an index a number exceeding the total number of records in the table. The second bug is more subtle. It may happen when there are several indexes with same prefix defined on the first joined table t accessed by a constant ref access. In this case the range optimizer estimates the number of accessed records of t for each usable index and these estimates can be different. Only the first of these estimates is taken into account when the selectivity of the ref access is calculated. However the optimizer later can choose a different index that provides a different estimate. The function table_condition_selectivity() could use this estimate to discount the selectivity of the ref access. This could lead to an selectivity value returned by this function that was greater that 1.
* Merge remote-tracking branch 'origin/5.5' into 10.1Alexander Barkov2019-08-161-2/+25
|\
| * MDEV-15955 Assertion `field_types == 0 || field_types[field_pos] == ↵Alexander Barkov2019-08-161-0/+22
| | | | | | | | MYSQL_TYPE_LONGLONG' failed in Protocol_text::store_longlong
* | MDEV-19834 Selectivity of an equality condition discounted twiceSergei Petrunia2019-08-151-6/+9
| | | | | | | | | | | | | | | | When discounting selectivity of ref access, don't discount the selectivity we've already discounted for range access. The 10.1 version of the fix. Will need to adjust condition filtering test results in 10.4
* | Merge branch '5.5' into 10.1Oleksandr Byelkin2019-07-251-1/+8
|\ \ | |/
| * MDEV-19778 Wrong Result on Left Outer Join with Subquery right on trueIgor Babaev2019-06-221-1/+8
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | and WHERE filter afterwards This patch complements the patch fixing the bug MDEV-6892. The latter properly handled queries that used mergeable views returning constant columns as inner tables of outer joins and whose where clause contained predicates referring to these columns if the predicates of happened not to be equality predicates. Otherwise the server still could return wrong result sets for such queries. Besides the fix for MDEV-6892 prevented some possible conversions of outer joins to inner joins for such queries. This patch corrected the function check_simple_equality() to handle properly conjunctive equalities of the where clause that refer to the constant columns of mergeable views used as inner tables of an outer join. The patch also changed the code of Item_direct_view_ref::not_null_tables(). This change allowed to take into account predicates containing references to constant columns of mergeable views when converting outer joins into inner joins.
* | imporve clang buildEugene Kosov2019-06-251-1/+1
| | | | | | | | | | | | | | | | | | cmake -DCMAKE_C_COMPILER=clang -DCMAKE_CXX_COMPILER=clang++ -DCMAKE_BUILD_TYPE=Debug Maintainer mode makes all warnings errors. This patch fix warnings. Mostly about deprecated `register` keyword. Too much warnings came from Mroonga and I gave up on it.
* | Merge branch '5.5' into 10.1Oleksandr Byelkin2019-06-121-22/+88
|\ \ | |/
| * MDEV-18479: Avoid COST_MULT(records, 1)Marko Mäkelä2019-06-111-5/+2
| |
| * MDEV-18479 Another complementIgor Babaev2019-06-101-7/+10
| | | | | | | | | | This patch complements the patch that fixes bug MDEV-18479. This patch takes care of possible overflow in JOIN::get_examined_rows().
| * MDEV-19580 Unrelated JOINs corrupt usage of 'WHERE function() IN (subquery)'Igor Babaev2019-06-091-14/+73
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | Handling of top level conjuncts in WHERE whose used_tables() contained RAND_TABLE_BIT in the function make_join_select() was incorrect. As a result if such a conjunct referred to fields non of which belonged to the last joined table it was pushed twice. (This could be seen for a test case from subselect.test whose output was changed after this patch had been applied. In 10.1 when running EXPLAIN FORMAT=JSON for the query from this test case we clearly see that one of the conjuncts is pushed twice.) This fact by itself was not good. Besides, if such a conjunct was pushed to a table that was the result of materialization of a semi-join the query could return a wrong result set. In particular we could watch it for queries with semi-join subqueries whose left parts used stored functions without "deterministic' specifier.
| * MDEV-18479 ComplementIgor Babaev2019-05-281-8/+13
| | | | | | | | | | | | This patch complements the patch that fixes bug MDEV-18479. This patch takes care of possible overflow when calculating the estimated number of rows in a materialized derived table / view.
* | Merge 5.5 into 10.1Marko Mäkelä2019-05-281-59/+68
|\ \ | |/
| * MDEV-18479 Assertion `join->best_read < double(1.79769313486231570815e+308L)'Igor Babaev2019-05-271-51/+62
| | | | | | | | | | | | | | | | | | or server crashes in JOIN::fix_all_splittings_in_plan after EXPLAIN This patch resolves the problem of overflowing when performing calculations to estimate the cost of an evaluated query execution plan. The overflowing in a non-debug build could cause different kind of problems uncluding crashes of the server.
* | MDEV-19258 RIGHT JOIN hangs in MariaDBIgor Babaev2019-05-231-2/+14
| | | | | | | | | | | | | | | | | | This patch corrects the patch for the bug 10006. The latter incorrectly calculates the attribute TABLE_LIST::dep_tables for inner tables of outer joins that are to be converted into inner joins. As a result after the patch some valid join orders were not evaluated and the optimizer could choose an execution plan that was far from being optimal.
* | MDEV-17752: Plan changes from hash_index_merge to index_merge with new ↵Varun Gupta2019-05-211-1/+3
| | | | | | | | | | | | | | | | | | optimizer defaults The code in best_access_path function, when it does not find a key suitable for ref access and join_cache_level is set to a value so that hash_join is possible we build a hash key. Later in the function we compare the cost of ref access with table scan (or index scan or quick selects). No need to do this when we have got the hash key.
* | Better comment from Monty for code in make_join_selectSergei Petrunia2019-05-171-2/+10
| |
* | Merge branch '5.5' into 10.1Vicențiu Ciorbaru2019-05-111-1/+1
|\ \ | |/
| * Update FSF AddressVicențiu Ciorbaru2019-05-111-1/+1
| | | | | | | | * Update wrong zip-code
| * MDEV-17382 Hash join algorithm should not be used to join materializedIgor Babaev2018-10-071-0/+8
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | derived table / view by equality Now rows of a materialized derived table are always put into a temporary table before join operation. If BNLH is used to join this table with the result of a partial join then both operands of the join are actually put into main memory. In most cases this is not efficient. We could avoid this by sending the rows of the derived table directly to the join operation. However this kind of data flow is not supported yet. Fixed by not allowing usage of hash join algorithm to join a materialized derived table if it's joined by an equality predicate of the form f=e where f is a field of the derived table.
* | don't cast random items to Item_result_field*Sergei Golubchik2019-04-241-9/+9
| | | | | | | | do it only for items that inherit from Item_result_field*
* | MDEV-18968 Both (WHERE 0.1) and (WHERE NOT 0.1) return empty setAlexander Barkov2019-03-261-2/+2
| |
* | fix gcc 8 compiler warningsSergei Golubchik2019-03-141-6/+6
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | There were two newly enabled warnings: 1. cast for a function pointers. Affected sql_analyse.h, mi_write.c and ma_write.cc, mf_iocache-t.cc, mysqlbinlog.cc, encryption.cc, etc 2. memcpy/memset of nontrivial structures. Fixed as: * the warning disabled for InnoDB * TABLE, TABLE_SHARE, and TABLE_LIST got a new method reset() which does the bzero(), which is safe for these classes, but any other bzero() will still cause a warning * Table_scope_and_contents_source_st uses `TABLE_LIST *` (trivial) instead of `SQL_I_List<TABLE_LIST>` (not trivial) so it's safe to bzero now. * added casts in debug_sync.cc and sql_select.cc (for JOIN) * move assignment method for MDL_request instead of memcpy() * PARTIAL_INDEX_INTERSECT_INFO::init() instead of bzero() * remove constructor from READ_RECORD() to make it trivial * replace some memcpy() with c++ copy assignments
* | Merge branch '10.0' into 10.1Sergei Golubchik2018-12-291-0/+4
|\ \
| * | MDEV-6453: Assertion `inited==NONE || (inited==RND && scan)' failed in ↵Varun Gupta2018-12-161-0/+4
| | | | | | | | | | | | | | | | | | | | | | | | handler::ha_rnd_init(bool) with InnoDB, joins, AND/OR conditions The inited parameter handler is not initialised when we do a quick_select after a table scan.
| * | MDEV-17382 Hash join algorithm should not be used to join materializedIgor Babaev2018-10-071-0/+8
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | derived table / view by equality Now rows of a materialized derived table are always put into a temporary table before join operation. If BNLH is used to join this table with the result of a partial join then both operands of the join are actually put into main memory. In most cases this is not efficient. We could avoid this by sending the rows of the derived table directly to the join operation. However this kind of data flow is not supported yet. Fixed by not allowing usage of hash join algorithm to join a materialized derived table if it's joined by an equality predicate of the form f=e where f is a field of the derived table.
* | | MDEV-17382 Hash join algorithm should not be used to join materializedIgor Babaev2018-10-071-0/+8
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | derived table / view by equality Now rows of a materialized derived table are always put into a temporary table before join operation. If BNLH is used to join this table with the result of a partial join then both operands of the join are actually put into main memory. In most cases this is not efficient. We could avoid this by sending the rows of the derived table directly to the join operation. However this kind of data flow is not supported yet. Fixed by not allowing usage of hash join algorithm to join a materialized derived table if it's joined by an equality predicate of the form f=e where f is a field of the derived table.
* | | MDEV-17155: Incorrect ORDER BY optimization: full index scan is used instead ↵Sergei Petrunia2018-09-121-0/+19
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | of range The bug was this scenario: 1. Join optimizer picks a range plan on index IDX1 (This index doesn't match the ORDER BY clause, so sorting will be needed) 2. Index Condition Pushdown pushes a part of WHERE down. The pushed condition is removed from SQL_SELECT::cond 3. test_if_skip_sort_order() figures that it's better to use IDX2 (as it will match ORDER BY ... LIMIT and so will execute faster) 3.1 It sees that there was a possible range access on IDX2. It tries to construct it by calling SQL_SELECT::test_quick_select(), but alas, SQL_SELECT::cond doesn't have all parts of WHERE anymore. So it uses full index scan which is slow. (The execution works fine because there's code further in test_if_skip_sort_order() which "Unpushes" the index condition and restores the original WHERE clause. It was just the test_quick_select call that suffered).
* | | Merge branch '11.0' into 10.1Oleksandr Byelkin2018-09-061-1/+1
|\ \ \ | |/ /
| * | MDEV-16465 Invalid (old?) table or database name or hang in ↵Sergei Golubchik2018-09-061-1/+1
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | ha_innobase::delete_table and log semaphore wait upon concurrent DDL with foreign keys lowercase db and table names before prelocking. Post-fix for 9180e8666b8 This fixes failures on main.lowercase_table4 on Windows
* | | MDEV-15433: Optimizer does not use group by optimization with distinctVarun Gupta2018-08-051-1/+9
| | | | | | | | | | | | | | | | | | | | | After the commit b76b69cd5fe634d8ddb9406aa2c82ef2a375b4d8 loose index scan for queries with DISTINCT stopped working. That is why that commit has to be reverted. Additionally this patch fixes the problem of MDEV-10880.
* | | Merge branch '10.0' into 10.1Oleksandr Byelkin2018-07-311-2/+7
|\ \ \ | |/ /
| * | Merge 5.5 into 10.0Marko Mäkelä2018-07-301-2/+7
| |\ \ | | |/
| | * Fix added along with a test for a case missed in the patch for MDEV-16751Varun Gupta2018-07-271-4/+4
| | |
| | * MDEV-16751: Server crashes in st_join_table::cleanup or ↵Varun Gupta2018-07-251-2/+7
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | TABLE_LIST::is_with_table_recursive_reference with join_cache_level>2 During muliple equality propagation for a query in which we have an IN subquery, the items in the select list of the subquery may not be part of the multiple equality because there might be another occurence of the same field in the where clause of the subquery. So we keyuse_is_valid_for_access_in_chosen_plan function which expects the items in the select list of the subquery to be same to the ones in the multiple equality (through these multiple equalities we create keyuse array). The solution would be that we expect the same field not the same Item because when we have SEMI JOIN MATERIALIZATION SCAN, we use copy back technique to copies back the materialised table fields to the original fields of the base tables.
| | * MDEV-16820 Lost 'Impossible where' from query with inexpensive subqueryIgor Babaev2018-07-241-0/+7
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | This patch fixes another problem introduced by the patch for mdev-4817. The latter changed Item_cond::fix_fields() in such a way that it could call the virtual method is_expensive(). With the first its call the method saves the result in Item::is_expensive_cache. For all next calls the method returns the result from this cache. So if the item once was determined as expensive the method always returns true. For subqueries it's not good, because non-optimized subqueries always is considered as expensive. It means that the cache should be invalidated after the call of optimize_constant_subqueries().
| * | MDEV-16820 Lost 'Impossible where' from query with inexpensive subqueryIgor Babaev2018-07-241-0/+7
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | This patch fixes another problem introduced by the patch for mdev-4817. The latter changed Item_cond::fix_fields() in such a way that it could call the virtual method is_expensive(). With the first its call the method saves the result in Item::is_expensive_cache. For all next calls the method returns the result from this cache. So if the item once was determined as expensive the method always returns true. For subqueries it's not good, because non-optimized subqueries always is considered as expensive. It means that the cache should be invalidated after the call of optimize_constant_subqueries().
* | | Merge branch '10.1' into bb-10.1-merge-sanjaOleksandr Byelkin2018-07-261-0/+7
|\ \ \
| * | | MDEV-16820 Lost 'Impossible where' from query with inexpensive subqueryIgor Babaev2018-07-241-0/+7
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | This patch fixes another problem introduced by the patch for mdev-4817. The latter changed Item_cond::fix_fields() in such a way that it could call the virtual method is_expensive(). With the first its call the method saves the result in Item::is_expensive_cache. For all next calls the method returns the result from this cache. So if the item once was determined as expensive the method always returns true. For subqueries it's not good, because non-optimized subqueries always is considered as expensive. It means that the cache should be invalidated after the call of optimize_constant_subqueries().
* | | | Merge branch '10.0' into bb-10.1-merge-sanjaOleksandr Byelkin2018-07-251-2/+2
|\ \ \ \ | |/ / / |/| / / | |/ /
View Lorry Controller Status