MDEV-30668 Set function aggregated in outer select used in view definition This patch fixes two bugs concerning views whose specifications contain subqueries with set functions aggregated in outer selects. Due to the first bug those such views that have implicit grouping were considered as mergeable. This led to wrong result sets for selects from these views. Due to the second bug the aggregation select was determined incorrectly and this led to bogus error messages. The patch added several test cases for these two bugs and for four other duplicate bugs. The patch also enables view-protocol for many other test cases. Approved by Oleksandr Byelkin <sanja@mariadb.com>
Deallocation of TABLE_LIST::dt_handler and TABLE_LIST::pushdown_derived was performed in multiple places if code. This not only made the code more difficult to maintain but also led to memory leaks and ASAN heap-use-after-free errors. This commit puts deallocation of TABLE_LIST::dt_handler and TABLE_LIST::pushdown_derived to the single point - JOIN::cleanup()
Consider a query of the form:
select ... from (select item2 as COL1) as T where COL1=123
Condition pushdown into derived table will try to push "COL1=123" condition
down into table T.
The process of pushdown involves "substituting" the item, that is,
replacing Item_field("T.COL1") with its "producing item" item2.
In order to use item2, one needs to clone it (call Item::build_clone).
If the item is not cloneable (e.g. Item_func_sp is not), the pushdown
process will fail and nothing at all will be pushed.
Fixed by introducing transform_condition_or_part() which will try to apply
the transformation for as many parts of condition as possible. The parts of
condition that couldn't be transformed are dropped.
This bug affected queries with views / derived_tables / CTEs whose specifications were of the form (SELECT ... LIMIT <n>) ORDER BY ... Units representing such specifications contains one SELECT_LEX structure for (SELECT ... LIMIT <n>) and additionally SELECT_LEX structure for fake_select_lex. This fact should have been taken into account in the function mysql_derived_fill(). This patch has to be applied to 10.2 and 10.3 only.
This bug affected queries with views / derived_tables / CTEs whose specifications were of the form (SELECT ... LIMIT <n>) ORDER BY ... Units representing such specifications contains one SELECT_LEX structure for (SELECT ... LIMIT <n>) and additionally SELECT_LEX structure for fake_select_lex. This fact should have been taken into account in the function mysql_derived_fill(). This patch has to be applied to 10.2 and 10.3 only.