summaryrefslogtreecommitdiff
path: root/mysql-test/r/func_group.result
Commit message (Collapse)AuthorAgeFilesLines
* MDEV-19232: Floating point precision / value comparison problemVarun Gupta2020-07-221-2/+2
| | | | | | | | | | | | The issue occurs when the subquery_cache is enabled. When there is a cache miss the division was leading to a value with scale 9. In the case of cache hit the value returned was of scale 9 and due to the different values for the scales the where condition evaluated to FALSE, hence the output was incomplete. To fix this problem we need to round up the decimal to the limit mentioned in Item::decimals. This would make sure the values are compared with the same scale.
* Merge branch '5.5' into 10.1Oleksandr Byelkin2019-07-251-0/+2
|\
| * MDEV-19778 Wrong Result on Left Outer Join with Subquery right on trueIgor Babaev2019-06-221-0/+2
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | 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.
* | MDEV-18968 Both (WHERE 0.1) and (WHERE NOT 0.1) return empty setAlexander Barkov2019-03-261-4/+4
| |
* | MDEV-10556 Assertion `0' failed in virtual void ↵Alexander Barkov2016-09-211-0/+9
| | | | | | | | Item_sum_field::set_result_field(Field*)
* | MDEV-10017: Get unexpected `Empty Set` for correlated subquery with ↵Oleksandr Byelkin2016-08-311-0/+35
| | | | | | | | | | | | aggregate functions take into account all arguments of aggregate function
* | MDEV-10017: Get unexpected `Empty Set` for correlated subquery with ↵mariadb-10.1.17Oleksandr Byelkin2016-08-291-0/+18
| | | | | | | | | | | | aggregate functions (part 1) Make aggregate function dependency visible.
* | Merge branch '10.0' into 10.1Sergei Golubchik2016-08-251-0/+39
|\ \ | |/
| * MDEV-10468 Assertion `nr >= 0.0' failed in Item_sum_std::val_real()Alexander Barkov2016-08-081-0/+12
| |
| * MDEV-10500 CASE/IF Statement returns multiple values and shifts further ↵Alexander Barkov2016-08-081-0/+27
| | | | | | | | | | | | | | | | | | result values to the next column We assume all around the code that null_value==true is in sync with NULL value returned by val_str()/val_decimal(). Item_sum_sum::val_decimal() erroneously returned a non-NULL value together with null_value set to true. Fixing to return NULL instead.
* | MDEV-9656 Assertion `0' failed in Item_sum_field::get_tmp_table_field().Alexander Barkov2016-03-161-0/+9
| | | | | | | | | | Removing a wrong ASSERT. Item_sum_field now uses the inherited Item::get_tmp_table_field().
* | MDEV-7195 AVG() loses precision in INT contextAlexander Barkov2015-10-221-0/+17
| | | | | | | | | | The fix for MDEV-8918 previously fixed the problem reported in MDEV-7195. Adding a test case from MDEV-7195 only.
* | MDEV-8921 Wrong result for CAST(AVG(double_column) AS SIGNED)Alexander Barkov2015-10-081-0/+16
| |
* | MDEV-8918 Wrong result for CAST(AVG(bigint_column) AS SIGNED)Alexander Barkov2015-10-081-0/+17
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | - Moving Item_xxx_field declarations after Item_sum_xxx declarations, so Item_xxx_field constructors can be defined directly in item_sum.h rather than item_sum.cc. This removes some duplicate code, e.g. initialization of the following members at constructor time: name, decimals, max_length, unsigned_flag, field, maybe_null. - Adding Item_sum_field as a common parent for Item_avg_field and Item_variance_field - Deriving Item_sum_field directly from Item rather that Item_result_field, as Item_sum_field descendants do not need anything from Item_result_field. - Removing hybrid infrastructure from Item_avg_field, adding Item_avg_field_decimal and Item_avg_field_double instead, as desired result type is already known at constructor time (not only at fix_fields time). This simplifies the code. - Changing Item_avg_field_decimal::val_int() to call val_int_from_decimal() instead of doing { return (longlong) rint(val_real()); } This is the fix itself.
* | MDEV-8852 Implicit or explicit CAST from MAX(string) to INT,DOUBLE,DECIMAL ↵Alexander Barkov2015-09-281-0/+10
| | | | | | | | does not produce warnings
* | MDEV-8806 Numeric CAST produce different warnings for strings literals vs ↵Alexander Barkov2015-09-251-0/+3
| | | | | | | | functions
* | Ensure that fields declared with NOT NULL doesn't have DEFAULT values if not ↵Monty2015-08-181-1/+1
|/ | | | | | | | | | | | | | | | | | specified and if not timestamp or auto_increment In original code, sometimes one got an automatic DEFAULT value in some cases, in other cases not. For example: create table t1 (a int primary key) - No default create table t2 (a int, primary key(a)) - DEFAULT 0 create table t1 SELECT .... - Default for all fields, even if they where defined as NOT NULL ALTER TABLE ... MODIFY could sometimes add an unexpected DEFAULT value. The patch is quite big because we had some many test cases that used CREATE ... SELECT or CREATE ... (...PRIMARY KEY(xxx)) which doesn't have an automatic DEFAULT anymore. Other things: - Removed warnings from InnoDB when waiting from semaphore (got this when testing things with --big)
* MDEV-6743 crash in GROUP_CONCAT(IF () ORDER BY 1)Michael Widenius2014-09-231-1/+32
| | | | | | | | | mysql-test/r/func_group.result: Test case mysql-test/t/func_group.test: Test case sql/item_sum.cc: Restore ORDER for prepared statements
* Fixed MDEV-5617: mysqld crashes when running a query with ONLY_FULL_GROUP_BYMichael Widenius2014-02-111-1/+10
| | | | | | | | | | | | | Problem was that we used cache_table in some cases where it was not initialized mysql-test/r/func_group.result: Added test case mysql-test/t/func_group.test: Added test case sql/item.cc: Don't use cached_table if not set sql/item_sum.cc: Don't use cached_table
* MDEV-5257: MIN/MAX Optimization (Select tables optimized away) does not work ↵Sergey Petrunya2013-11-121-0/+44
| | | | | | | | for DateTime - MIN/MAX optimizer does a check whether a "field CMP const" comparison uses a constant that's longer than the field it is compared to. Make this check only for string columns, also compare character lengths, not byte lengths.
* Merge 5.3->5.5Igor Babaev2013-09-151-0/+13
|\
| * Fixed bug mdev-5015.Igor Babaev2013-09-151-0/+13
| | | | | | | | | | The patch for mdev-4355 had a defect: the cached values for bitmaps of used tables were not updated when processing degenerate OR formulas.
* | MDEV-4817: Optimizer fails to optimize expression of the form 'FOO' IS NULLSergey Petrunya2013-07-311-0/+2
| | | | | | | | | | | | | | | | | | - Modify the way Item_cond::fix_fields() and Item_cond::eval_not_null_tables() calculate bitmap for Item_cond_or::not_null_tables(): if they see a "... OR inexpensive_const_false_item OR ..." then the item can be ignored. - Updated test results. There can be more warnings produced since parts of WHERE are evaluated more times.
* | 5.3 merge.Sergei Golubchik2013-05-201-0/+28
|\ \ | |/ | | | | change maria.distinct to use a function that doesn't require ssl-enabled builds
| * MDEV-4290:unknown2013-05-031-0/+28
| | | | | | | | | | Fix agregate function resolution in derived tables (no name resolution over a derived table border)
* | Fix for MDEV-4144unknown2013-03-291-2/+2
| | | | | | | | | | | | | | | | | | | | | | | | Analysis: The reason for the inefficent plan was that Item_subselect::is_expensive() didn't detect the special case when a subquery was optimized, but had no join plan because it either has no table, or its tables have been optimized away, or the optimizer detected that the result set is empty. Solution: Identify the special cases above in the Item_subselect::is_expensive(), and consider such degenerate subqueries inexpensive.
* | 5.3 mergeSergei Golubchik2013-03-271-0/+7
|\ \ | |/
| * MDEV-4286 Server crashes in Protocol_text::store, stack smashing detectedSergei Golubchik2013-03-171-0/+7
| | | | | | | | | | | | AVG() returns a double, its max_length is reasonably limited by a double number length, even if the argument is many Kbytes long.
* | 5.3->5.5 mergeSergei Golubchik2013-02-281-1/+1
|\ \ | |/
| * Fixed bug mdev-3913.Igor Babaev2013-02-201-1/+1
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | The wrong result set returned by the left join query from the bug test case happened due to several inconsistencies and bugs of the legacy mysql code. The bug test case uses an execution plan that employs a scan of a materialized IN subquery from the WHERE condition. When materializing such an IN- subquery the optimizer injects additional equalities into the WHERE clause. These equalities express the constraints imposed by the subquery predicate. The injected equality of the query in the test case happens to belong to the same equality class, and a new equality imposing a condition on the rows of the materialized subquery is inferred from this class. Simultaneously the multiple equality is added to the ON expression of the LEFT JOIN used in the main query. The inferred equality of the form f1=f2 is taken into account when optimizing the scan of the rows the temporary table that is the result of the subquery materialization: only the values of the field f1 are read from the table into the record buffer. Meanwhile the inferred equality is removed from the WHERE conditions altogether as a constraint on the fields of the temporary table that has been used when filling this table. This equality is supposed to be removed from the ON expression when the multiple equalities of the ON expression are converted into an optimal set of equality predicates. It supposed to be removed from the ON expression as an equality inferred from only equalities of the WHERE condition. Yet, it did not happened due to the following bug in the code. Erroneously the code tried to build multiple equality for ON expression twice: the first time, when it called optimize_cond() for the WHERE condition, the second time, when it called this function for the HAVING condition. When executing optimize_con() for the WHERE condition a reference to the multiple equality of the WHERE condition is set in the multiple equality of the ON expression. This reference would allow later to convert multiple equalities of the ON expression into equality predicates. However the the second call of build_equal_items() for the ON expression that happened when optimize_cond() was called for the HAVING condition reset this reference to NULL. This bug fix blocks calling build_equal_items() for ON expressions for the second time. In general, it will be beneficial for many queries as it removes from ON expressions any equalities that are to be checked for the WHERE condition. The patch also fixes two bugs in the list manipulation operations and a bug in the function substitute_for_best_equal_field() that resulted in passing wrong reference to the multiple equalities of where conditions when processing multiple equalities of ON expressions. The code of substitute_for_best_equal_field() and the code the helper function eliminate_item_equal() were also streamlined and cleaned up. Now the conversion of the multiple equalities into an optimal set of equality predicates first produces the sequence of the all equalities processing multiple equalities one by one, and, only after this, it inserts the equalities at the beginning of the other conditions. The multiple changes in the output of EXPLAIN EXTENDED are mainly the result of this streamlining, but in some cases is the result of the removal of unneeded equalities from ON expressions. In some test cases this removal were reflected in the output of EXPLAIN resulted in disappearance of “Using where” in some rows of the execution plans.
| * 5.2 merge.Sergei Golubchik2012-08-221-0/+2
| |\ | | | | | | | | | | | | | | | | | | two tests still fail: main.innodb_icp and main.range_vs_index_merge_innodb call records_in_range() with both range ends being open (which triggers an assert)
| | * 5.1 mergeSergei Golubchik2012-08-221-0/+2
| | |\ | | | | | | | | | | | | increase xtradb verson from 13.0 to 13.01
* | | \ merge with MySQL 5.5.27Sergei Golubchik2012-08-091-0/+2
|\ \ \ \ | | | | | | | | | | | | | | | manually checked every change, reverted incorrect or stupid changes.
| * \ \ \ manual merge (WL6219)Gleb Shchepa2012-06-291-0/+2
| |\ \ \ \ | | | |_|/ | | |/| | | | | | | | | | | | sql/sql_yacc.yy: manual merge (backport of WL6219)
| | * | | Backport of the deprecation warning from WL#6219: "Deprecate and remove ↵Gleb Shchepa2012-06-291-0/+2
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | YEAR(2) type" Print the warning(note): YEAR(x) is deprecated and will be removed in a future release. Please use YEAR(4) instead on "CREATE TABLE ... YEAR(x)" or "ALTER TABLE MODIFY ... YEAR(x)", where x != 4
* | | | | MDEV-410: EXPLAIN shows type=range, while SHOW EXPLAIN and userstat show ↵Sergey Petrunya2012-07-251-1/+1
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | full table scan is used - Make Item_subselect::fix_fields() ignore UNCACHEABLE_EXPLAIN flag when deciding whether the subquery item should be marked as constant.
* | | | | Merge 5.3->5.5Igor Babaev2012-03-171-0/+47
|\ \ \ \ \ | | |_|/ / | |/| | |
| * | | | Merge 5.2->5.3unknown2012-03-141-0/+47
| |\ \ \ \ | | | |_|/ | | |/| |
| | * | | Fixed LP bug #884175.Igor Babaev2012-03-081-0/+47
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | If in the where clause of the a query some comparison conditions on the field under a MIN/MAX aggregate function contained constants whose sizes exceeded the size of the field then the query could return a wrong result when the optimizer had chosen to apply the MIN/MAX optimization. With such conditions the MIN/MAX optimization still could be applied, yet it would require a more thorough analysis of the keys built to find the value of MIN/MAX aggregate functions with index look-ups. The current patch just prohibits using the MIN/MAX optimization in this situation.
* | | | | Merge 5.3->5.5.Igor Babaev2012-03-011-1/+1
|\ \ \ \ \ | |/ / / /
| * | | | Fixed LP bug #939009.Igor Babaev2012-02-241-1/+1
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | The result of materialization of the right part of an IN subquery predicate is placed into a temporary table. Each row of the materialized table is distinct. A unique key over all fields of the temporary table is defined and created. It allows to perform key look-ups into the table. The table created for a materialized subquery can be accessed by key as any other table. The function best_access-path search for the best access to join a table to a given partial join. With some where conditions this function considers a possibility of a ref_or_null access. If such access employs the unique key on the temporary table then when estimating the cost this access the function tries to use the array rec_per_key. Yet, such array is not built for this unique key. This causes a crash of the server. Rows returned by the subquery that contain nulls don't have to be placed into temporary table, as they cannot be match any row produced by the left part of the subquery predicate. So all fields of the temporary table can be defined as non-nullable. In this case any ref_or_null access to the temporary table does not make any sense and it does not make sense to estimate such an access. The fix makes sure that the temporary table for a materialized IN subquery is defined with columns that are all non-nullable. The also ensures that any row with nulls returned by the subquery is not placed into the temporary table.
* | | | | 5.3.4 mergeSergei Golubchik2012-02-151-0/+64
|\ \ \ \ \ | |/ / / /
| * | | | Merge 5.2->5.3 in preparation for the release of mariadb-5.3.4-rc.Igor Babaev2012-02-011-1/+64
| |\ \ \ \ | | |/ / /
| | * | | Fixed LP bug #879860.Igor Babaev2011-12-271-0/+23
| | | | | | | | | | | | | | | | | | | | | | | | | The MIN/MAX optimization cannot be applied to a subquery if its WHERE clause contains a conjunctive condition depending on an outer reference.
| | * | | Fixed LP bug #904345.Igor Babaev2011-12-271-1/+39
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | The MIN/MAX optimizer code from the function opt_sum_query erroneously did not take into account conjunctive conditions that did not depend on any table, yet were not identified as constant items. These could be items containing rand() or PS/SP parameters. These items are supposed to be evaluated at the execution phase. That's why if such conditions can be extracted from the WHERE condition the MIN/MAX optimization is not applied as currently it is always done at the optimization phase. (In 5.3 expensive subqueries are also evaluated only at the execution phase. So, if a constant condition with such subquery can be extracted from the WHERE clause the MIN/MAX optimization should not be applied in 5.3.) IF an IN/ALL/SOME predicate with a constant left part is transformed into an EXISTS subquery the resulting subquery should not be considered uncacheable if the right part of the predicate is not uncacheable. Backported the function dbug_print_item() from 5.3. The function is used only for debugging.
* | | | | 5.3 mergeSergei Golubchik2012-01-131-6/+12
|\ \ \ \ \ | |/ / / /
| * | | | Made the optimizer switch flags 'outer_join_with_cache', 'semijoin_with_cache'Igor Babaev2011-12-151-0/+3
| | | | | | | | | | | | | | | | | | | | set to 'on' by default.
| * | | | Make subquery Materialization, as well as semi-join Materialization be shownSergey Petrunya2011-12-051-6/+6
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | in EXPLAIN as select_type==MATERIALIZED. Before, we had select_type==SUBQUERY and it was difficult to tell materialized subqueries from uncorrelated scalar-context subqueries.
| * | | | Set new default values for the optimizer switch flags 'derived_merge'Igor Babaev2011-11-261-0/+3
| | | | | | | | | | | | | | | | | | | | and 'derived_with_keys'. Now they are set on by default.
* | | | | 5.3->5.5 mergeSergei Golubchik2011-11-221-0/+9
|\ \ \ \ \ | |/ / / /
View Lorry Controller Status