Skip to content

Commit 8f92347

Browse files
Dag Wanvikdahlerlend
Dag Wanvik
authored and
dahlerlend
committed
Bug#36921175 Complex Join Predicate with Subquery Not Possible To Offload to HeatWave (1/2 noclose)
Eliminate actual sorting in window specifications if they do not contain references to inner tables; in such cases there is no effective partitioning or ordering and the presence of the outer reference hinders offloading to RAPID. Note that MySQL allows outer references in PARTITION BY and ORDER BY expressions, whereas the standard requires all column references to be inner. This patch does not change this liberal interpretation, but skips the actual sorting in the access path (by skipping the orderings contribution to Window::m_sorting_order unless the ordering is semantically meaningful, i.e. it contains at least one inner reference). Change-Id: I64a00c90d5f37c104e5a375b1d59e415da81d6f2
1 parent 4496e7b commit 8f92347

8 files changed

+245
-30
lines changed

‎mysql-test/r/subquery_scalar_to_derived_correlated.result

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -315,7 +315,7 @@ EXPLAIN SELECT a,
315315
FROM t3;
316316
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
317317
1 PRIMARY t3 NULL ALL NULL NULL NULL NULL 2 100.00 NULL
318-
2 DEPENDENT SUBQUERY t2 NULL ALL NULL NULL NULL NULL 2 100.00 Using filesort
318+
2 DEPENDENT SUBQUERY t2 NULL ALL NULL NULL NULL NULL 2 100.00 NULL
319319
Warnings:
320320
Note 1276 Field or reference 'test.t3.a' of SELECT #2 was resolved in SELECT #1
321321
Note 3598 To get information about window functions use EXPLAIN FORMAT=JSON

‎mysql-test/r/window_functions.result

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -9201,7 +9201,7 @@ SELECT AVG(i) FROM t1 ORDER BY RANK() OVER (PARTITION BY AVG(i) ORDER BY i);
92019201
ERROR 42000: In aggregated query without GROUP BY, expression #1 of PARTITION BY or ORDER BY clause of window '<unnamed window>' contains nonaggregated column 'test.t1.i'; this is incompatible with sql_mode=only_full_group_by
92029202
SELECT AVG(i), RANK() OVER w FROM t1 WINDOW w AS (ORDER BY i);
92039203
ERROR 42000: In aggregated query without GROUP BY, expression #1 of PARTITION BY or ORDER BY clause of window 'w' contains nonaggregated column 'test.t1.i'; this is incompatible with sql_mode=only_full_group_by
9204-
SELECT (select AVG(i)+RANK() OVER (ORDER BY i)) FROM t1;
9204+
SELECT (SELECT AVG(i)+RANK() OVER (ORDER BY i)) FROM t1;
92059205
ERROR 42000: In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'test.t1.i'; this is incompatible with sql_mode=only_full_group_by
92069206
DROP TABLE t1;
92079207
# End of checking for errors

‎mysql-test/r/window_functions_bugs.result

Lines changed: 117 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2276,3 +2276,120 @@ ON (ta.vkey=outer_t.c5) LIMIT 1)
22762276
1
22772277
NULL
22782278
DROP TABLE t1;
2279+
#
2280+
# Bug#36921175 Complex Join Predicate with Subquery Not
2281+
# Possible To Offload to HeatWave
2282+
# See also HeatWave test added to check offload
2283+
CREATE TABLE t1(col INT);
2284+
CREATE TABLE t2(col INT);
2285+
INSERT INTO t1 VALUES (0), (1), (2), (NULL);
2286+
INSERT INTO t1 VALUES (0), (1), (2), (NULL);
2287+
ANALYZE TABLE t1;
2288+
Table Op Msg_type Msg_text
2289+
test.t1 analyze status OK
2290+
# verify that PARTTITION BY containing no inner reference
2291+
# is eliminated
2292+
SELECT
2293+
COUNT(s1.col)
2294+
FROM ( t1 AS s1
2295+
JOIN
2296+
t1 AS s2
2297+
ON ( s2.col IN (WITH RECURSIVE cte1 AS (
2298+
SELECT DENSE_RANK() OVER (PARTITION BY s1.col)
2299+
FROM t2
2300+
WHERE col >= 7)
2301+
SELECT * FROM cte1))
2302+
);
2303+
COUNT(s1.col)
2304+
0
2305+
EXPLAIN FORMAT=tree SELECT
2306+
COUNT(s1.col)
2307+
FROM ( t1 AS s1
2308+
JOIN
2309+
t1 AS s2
2310+
ON ( s2.col IN (WITH RECURSIVE cte1 AS (
2311+
SELECT DENSE_RANK() OVER (PARTITION BY s1.col)
2312+
FROM t2
2313+
WHERE col >= 7)
2314+
SELECT * FROM cte1))
2315+
);
2316+
EXPLAIN
2317+
-> Aggregate: count(s1.col) (rows=1)
2318+
-> Inner hash join (no condition) (rows=8)
2319+
-> Table scan on s1 (rows=8)
2320+
-> Hash
2321+
-> Remove duplicate s2 rows using temporary table (weedout) (rows=1)
2322+
-> Inner hash join (s2.col = cte1.`DENSE_RANK() OVER (PARTITION BY s1.col)`) (rows=1)
2323+
-> Table scan on s2 (rows=8)
2324+
-> Hash
2325+
-> Table scan on cte1 (rows=1)
2326+
-> Materialize CTE cte1 (rows=1)
2327+
-> Window aggregate: dense_rank() OVER (PARTITION BY s1.col ) (rows=1)
2328+
-> Filter: (t2.col >= 7) (rows=1)
2329+
-> Table scan on t2 (rows=1)
2330+
2331+
Warnings:
2332+
Note 1276 Field or reference 'test.s1.col' of SELECT #3 was resolved in SELECT #1
2333+
SET optimizer_trace="enabled=on";
2334+
SELECT
2335+
COUNT(s1.col)
2336+
FROM ( t1 AS s1
2337+
JOIN
2338+
t1 AS s2
2339+
ON ( s2.col IN (WITH RECURSIVE cte1 AS (
2340+
SELECT DENSE_RANK() OVER (PARTITION BY s1.col)
2341+
FROM t2
2342+
WHERE col >= 7)
2343+
SELECT * FROM cte1))
2344+
);
2345+
COUNT(s1.col)
2346+
0
2347+
Check that trace shows the elimination
2348+
SELECT JSON_PRETTY(JSON_EXTRACT(
2349+
trace,
2350+
'$.steps[*].join_preparation.steps[*].'
2351+
'join_preparation.steps[*].join_preparation.steps[0]'))
2352+
FROM information_schema.OPTIMIZER_TRACE;
2353+
JSON_PRETTY(JSON_EXTRACT(
2354+
trace,
2355+
'$.steps[*].join_preparation.steps[*].'
2356+
'join_preparation.steps[*].join_preparation.steps[0]'))
2357+
[
2358+
{
2359+
"eliminated sorting for PARTITION BY in window": "(PARTITION BY `s1`.`col` ) "
2360+
}
2361+
]
2362+
SET optimizer_trace="enabled=default";
2363+
# verify that PARTTITION BY is *not* eliminated; has an outer
2364+
# reference and an inner reference
2365+
EXPLAIN FORMAT=tree
2366+
SELECT
2367+
COUNT(s1.col)
2368+
FROM ( t1 AS s1
2369+
JOIN
2370+
t1 AS s2
2371+
ON ( s2.col IN (WITH RECURSIVE cte1 AS (
2372+
SELECT DENSE_RANK() OVER (PARTITION BY s1.col + t2.col)
2373+
FROM t2
2374+
WHERE col >= 7)
2375+
SELECT * FROM cte1))
2376+
);
2377+
EXPLAIN
2378+
-> Aggregate: count(s1.col) (rows=1)
2379+
-> Inner hash join (no condition) (rows=8)
2380+
-> Table scan on s1 (rows=8)
2381+
-> Hash
2382+
-> Remove duplicate s2 rows using temporary table (weedout) (rows=1)
2383+
-> Inner hash join (s2.col = cte1.`DENSE_RANK() OVER (PARTITION BY s1.col + t2.col)`) (rows=1)
2384+
-> Table scan on s2 (rows=8)
2385+
-> Hash
2386+
-> Table scan on cte1 (rows=1)
2387+
-> Materialize CTE cte1 (rows=1)
2388+
-> Window aggregate: dense_rank() OVER (PARTITION BY (s1.col + t2.col) ) (rows=1)
2389+
-> Sort: (s1.col + t2.col) (rows=1)
2390+
-> Filter: (t2.col >= 7) (rows=1)
2391+
-> Table scan on t2 (rows=1)
2392+
2393+
Warnings:
2394+
Note 1276 Field or reference 'test.s1.col' of SELECT #3 was resolved in SELECT #1
2395+
DROP TABLE t1, t2;

‎mysql-test/r/window_functions_explain.result

Lines changed: 2 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -13834,24 +13834,17 @@ EXPLAIN
1383413834
"query_block": {
1383513835
"select_id": 2,
1383613836
"cost_info": {
13837-
"query_cost": "20.05"
13837+
"query_cost": "2.05"
1383813838
},
1383913839
"windowing": {
1384013840
"windows": [
1384113841
{
1384213842
"name": "<unnamed window>",
13843-
"using_filesort": true,
13844-
"filesort_key": [
13845-
"(1 * 1)"
13846-
],
1384713843
"functions": [
1384813844
"rank"
1384913845
]
1385013846
}
1385113847
],
13852-
"cost_info": {
13853-
"sort_cost": "18.00"
13854-
},
1385513848
"table": {
1385613849
"table_name": "t",
1385713850
"access_type": "ALL",
@@ -14235,8 +14228,7 @@ EXPLAIN
1423514228
"name": "<unnamed window>",
1423614229
"using_filesort": true,
1423714230
"filesort_key": [
14238-
"(select (`a` + `d`) from `u`)",
14239-
"(select `d` from `u`)"
14231+
"(select (`a` + `d`) from `u`)"
1424014232
],
1424114233
"frame_buffer": {
1424214234
"using_temporary_table": true,

‎mysql-test/t/window_functions.test

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -4043,7 +4043,7 @@ SELECT AVG(i) FROM t1 ORDER BY RANK() OVER (PARTITION BY AVG(i) ORDER BY i);
40434043
--error ER_MIX_OF_GROUP_FUNC_AND_FIELDS
40444044
SELECT AVG(i), RANK() OVER w FROM t1 WINDOW w AS (ORDER BY i);
40454045
--error ER_MIX_OF_GROUP_FUNC_AND_FIELDS
4046-
SELECT (select AVG(i)+RANK() OVER (ORDER BY i)) FROM t1;
4046+
SELECT (SELECT AVG(i)+RANK() OVER (ORDER BY i)) FROM t1;
40474047

40484048
DROP TABLE t1;
40494049

‎mysql-test/t/window_functions_bugs.test

Lines changed: 60 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1545,3 +1545,63 @@ SELECT ROW(AVG(vkey) OVER (PARTITION BY vkey), 59) =
15451545
FROM t1 AS outer_t;
15461546

15471547
DROP TABLE t1;
1548+
1549+
--echo #
1550+
--echo # Bug#36921175 Complex Join Predicate with Subquery Not
1551+
--echo # Possible To Offload to HeatWave
1552+
--echo # See also HeatWave test added to check offload
1553+
CREATE TABLE t1(col INT);
1554+
CREATE TABLE t2(col INT);
1555+
INSERT INTO t1 VALUES (0), (1), (2), (NULL);
1556+
INSERT INTO t1 VALUES (0), (1), (2), (NULL);
1557+
ANALYZE TABLE t1;
1558+
--echo # verify that PARTTITION BY containing no inner reference
1559+
--echo # is eliminated
1560+
let $query =
1561+
SELECT
1562+
COUNT(s1.col)
1563+
FROM ( t1 AS s1
1564+
JOIN
1565+
t1 AS s2
1566+
ON ( s2.col IN (WITH RECURSIVE cte1 AS (
1567+
SELECT DENSE_RANK() OVER (PARTITION BY s1.col)
1568+
FROM t2
1569+
WHERE col >= 7)
1570+
SELECT * FROM cte1))
1571+
);
1572+
eval $query;
1573+
--replace_regex $elide_costs
1574+
--skip_if_hypergraph # Different the query plan.
1575+
eval EXPLAIN FORMAT=tree $query;
1576+
1577+
SET optimizer_trace="enabled=on";
1578+
eval $query;
1579+
1580+
--echo Check that trace shows the elimination
1581+
SELECT JSON_PRETTY(JSON_EXTRACT(
1582+
trace,
1583+
'$.steps[*].join_preparation.steps[*].'
1584+
'join_preparation.steps[*].join_preparation.steps[0]'))
1585+
FROM information_schema.OPTIMIZER_TRACE;
1586+
1587+
SET optimizer_trace="enabled=default";
1588+
1589+
--echo # verify that PARTTITION BY is *not* eliminated; has an outer
1590+
--echo # reference and an inner reference
1591+
--replace_regex $elide_costs
1592+
--skip_if_hypergraph # Different the query plan.
1593+
EXPLAIN FORMAT=tree
1594+
SELECT
1595+
COUNT(s1.col)
1596+
FROM ( t1 AS s1
1597+
JOIN
1598+
t1 AS s2
1599+
ON ( s2.col IN (WITH RECURSIVE cte1 AS (
1600+
SELECT DENSE_RANK() OVER (PARTITION BY s1.col + t2.col)
1601+
FROM t2
1602+
WHERE col >= 7)
1603+
SELECT * FROM cte1))
1604+
);
1605+
1606+
DROP TABLE t1, t2;
1607+

‎sql/window.cc

Lines changed: 57 additions & 17 deletions
Original file line numberDiff line numberDiff line change
@@ -58,6 +58,7 @@
5858
#include "sql/key_spec.h"
5959
#include "sql/mem_root_array.h"
6060
#include "sql/mysqld_cs.h"
61+
#include "sql/opt_trace.h"
6162
#include "sql/parse_location.h"
6263
#include "sql/parse_tree_nodes.h" // PT_*
6364
#include "sql/parse_tree_window.h" // PT_window
@@ -399,27 +400,55 @@ ORDER *Window::sorting_order(THD *thd, bool implicitly_grouped) {
399400
return nullptr;
400401
}
401402

402-
ORDER *part = effective_partition_by() ? effective_partition_by()->value.first
403-
: nullptr;
404-
ORDER *ord =
405-
effective_order_by() ? effective_order_by()->value.first : nullptr;
403+
// Use Window::can_eliminate_order to determine if we can skip the sort as
404+
// specified by PARTITION BY and/or ORDER BY clauses. If so also add info
405+
// about it to the optimizer trace, if active.
406+
std::array<std::pair<const PT_order_list *, const char *>, 2> orders{
407+
std::make_pair(effective_partition_by(), "PARTITION BY"),
408+
std::make_pair(effective_order_by(), "ORDER BY")};
409+
constexpr uint part_i = 0;
410+
constexpr uint ord_i = 1;
411+
ORDER *sort[2] = {nullptr, nullptr};
412+
uint idx = 0;
413+
for (auto &order : orders) {
414+
sort[idx] = order.first ? order.first->value.first : nullptr;
415+
if (can_eliminate_order(sort[idx])) {
416+
sort[idx] = nullptr;
417+
418+
char buff1[256];
419+
String legend(buff1, sizeof(buff1), system_charset_info);
420+
legend.length(0);
421+
legend.append("eliminated sorting for ");
422+
legend.append(order.second);
423+
legend.append(" in window");
424+
Opt_trace_context *const trace = &thd->opt_trace;
425+
Opt_trace_object trace_wrapper(trace);
426+
427+
char buff2[256];
428+
String w_spec(buff2, sizeof(buff2), system_charset_info);
429+
w_spec.length(0);
430+
print(thd, &w_spec, enum_query_type(QT_ORDINARY | QT_NO_DEFAULT_DB),
431+
/*expand_definition*/ false);
432+
433+
trace_wrapper.add_utf8(legend.ptr(), w_spec.ptr(), w_spec.length());
434+
}
435+
idx++;
436+
}
406437

407438
/*
408-
1. Copy both lists
409-
2. Append the ORDER BY list to the partition list.
410-
411-
This ensures that all columns are present in the resulting sort ordering
412-
and that all ORDER BY expressions are at the end.
413-
The resulting sort can the be used to detect partition change and also
414-
satisfy the window ordering.
439+
Set up m_sorting_order. The resulting sort can then be used to detect
440+
partition change and also satisfy the window ordering. This ensures that
441+
all columns are present in the resulting sort ordering and that all ORDER
442+
BY expressions are at the end.
415443
*/
416-
if (ord == nullptr)
417-
m_sorting_order = part;
418-
else if (part == nullptr)
419-
m_sorting_order = ord;
444+
if (sort[ord_i] == nullptr)
445+
m_sorting_order = sort[part_i];
446+
else if (sort[part_i] == nullptr)
447+
m_sorting_order = sort[ord_i];
420448
else {
421-
ORDER *sorting = clone(thd, part);
422-
ORDER *ob = clone(thd, ord);
449+
// Since we are building a new list, we must clone the contributors
450+
ORDER *sorting = clone(thd, sort[part_i]);
451+
ORDER *ob = clone(thd, sort[ord_i]);
423452
append_to_back(&sorting, ob);
424453
m_sorting_order = sorting;
425454
}
@@ -1089,6 +1118,17 @@ void Window::eliminate_unused_objects(List<Window> *windows) {
10891118
}
10901119
}
10911120

1121+
bool Window::can_eliminate_order(ORDER *order) const {
1122+
if (order == nullptr) return false;
1123+
for (ORDER *o = order; o != nullptr; o = o->next) {
1124+
// contains inner column? if so, return false
1125+
const table_map used_tables =
1126+
(*o->item)->used_tables() & ~PSEUDO_TABLE_BITS;
1127+
if (used_tables != 0) return false;
1128+
}
1129+
return true;
1130+
}
1131+
10921132
bool Window::setup_windows1(THD *thd, Query_block *select,
10931133
Ref_item_array ref_item_array, Table_ref *tables,
10941134
mem_root_deque<Item *> *fields,

‎sql/window.h

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1033,6 +1033,12 @@ class Window {
10331033
Table_ref *tables,
10341034
mem_root_deque<Item *> *fields, ORDER *o,
10351035
bool partition_order);
1036+
/**
1037+
If the ordering expression contains no inner column references, return true,
1038+
else false
1039+
*/
1040+
bool can_eliminate_order(ORDER *order) const;
1041+
10361042
/**
10371043
Return true if this window's name is not unique in windows
10381044
*/

0 commit comments

Comments
 (0)