Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.5
Description
Stumbled on this when reviewing MDEV-34041.
Consider this testcase (not necessarily minimal):
create table t1 (a int, b int, c int); |
insert into t1 select seq,seq,seq from seq_1_to_10; |
create table t2 (a int, b int, c int); |
insert into t2 select seq,seq,seq from seq_1_to_10; |
insert into t2 select seq,seq,seq from seq_1_to_10; |
insert into t2 select seq,seq,seq from seq_1_to_10; |
insert into t2 select seq,seq,seq from seq_1_to_10; |
insert into t2 select seq,seq,seq from seq_1_to_10; |
insert into t1 values (),(); |
insert into t1 values (),(); |
analyze format=json select a in (select a from t2) from t1; |
shows
{
|
"query_block": { |
"select_id": 1, |
"r_loops": 1, |
"r_total_time_ms": 6515166.843, |
"table": { |
"table_name": "t1", |
"access_type": "ALL", |
"r_loops": 1, |
"rows": 14, |
"r_rows": 14, |
"r_table_time_ms": 0.421506134, |
"r_other_time_ms": 6515162.901, |
"filtered": 100, |
"r_filtered": 100 |
},
|
"subqueries": [ |
{
|
"materialization": { |
"r_strategy": "partial_match_scan", |
"r_loops": 11, |
"r_index_lookup_loops": 10, |
"r_partial_match_loops": 1, |
"query_block": { |
"select_id": 2, |
"r_loops": 1, |
"r_total_time_ms": 3.349656134, |
"table": { |
"table_name": "t2", |
"access_type": "ALL", |
"r_loops": 1, |
"rows": 50, |
"r_rows": 50, |
"r_table_time_ms": 2.853555948, |
"r_other_time_ms": 0.474497955, |
"filtered": 100, |
"r_filtered": 100 |
}
|
}
|
}
|
}
|
]
|
}
|
}
|
This doesn't show expression_cache, but if I put a breakpoint into
Expression_cache_tmptable::check_value I see that it is hit.
(gdb) wher
|
#0 Expression_cache_tmptable::check_value (this=0x7fff14078448, value=0x7ffff4074c78) at /home/psergey/dev-git2/10.5-review-34041-orig/sql/ sql_expression_cache.cc:219
|
#1 0x000055555626bcdc in Item_cache_wrapper::check_cache (this=0x7fff140782c0) at /home/psergey/dev-git2/10.5-review-34041-orig/sql/item.cc:8970
|
#2 0x000055555626bf37 in Item_cache_wrapper::val_int (this=0x7fff140782c0) at /home/psergey/dev-git2/10.5-review-34041-orig/sql/item.cc:9033
|
#3 0x000055555611f6e3 in Type_handler::Item_send_long (this=0x555557aaf120 <type_handler_bool>, item=0x7fff140782c0, protocol=0x55555e53f600, buf=0x7ffff4074e60) at /home/psergey/dev-git2/10.5-review-34041-orig/sql/sql_type.cc:7598
|
#4 0x000055555612cb18 in Type_handler_long::Item_send (this=0x555557aaf120 <type_handler_bool>, item=0x7fff140782c0, protocol=0x55555e53f600, buf=0x7ffff4074e60) at /home/psergey/dev-git2/10.5-review-34041-orig/sql/sql_type.h:5735
|
#5 0x0000555555d72e86 in Item::send (this=0x7fff140782c0, protocol=0x55555e53f600, buffer=0x7ffff4074e60) at /home/psergey/dev-git2/10.5-review-34041- orig/sql/item.h:1082
|
#6 0x000055555626b9a4 in Item_cache_wrapper::send (this=0x7fff140782c0, protocol=0x55555e53f600, buffer=0x7ffff4074e60) at /home/psergey/dev-git2/10.5- review-34041-orig/sql/item.cc:8891
|
#7 0x0000555555d6c5b3 in Protocol::send_result_set_row (this=0x55555e53f600, row_items=0x7fff14016698) at /home/psergey/dev-git2/10.5-review-34041-orig/ sql/protocol.cc:1086
|
#8 0x0000555555e2b033 in select_send::send_data (this=0x7fff14019468, items=@0x7fff14016698: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x7fff14018390, last = 0x7fff14018390, elements = 1}, <No data fields>}) at /home/psergey/dev-git2/10.5-review-34041-orig/sql/sql_class.cc:3160
|
#9 0x0000555555f4cde5 in select_result_sink::send_data_with_check (this=0x7fff14019468, items=@0x7fff14016698: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x7fff14018390, last = 0x7fff14018390, elements = 1}, <No data fields>}, u=0x7fff14004f28, sent=0) at /home/psergey/dev-git2/10.5-review- 34041-orig/sql/sql_class.h:5564
|
For comparison, a non-IN subquery does show expression_cache:
analyze format=json select a > (select max(a) from t2 where t2.c<=t1.c) from t1; |
...
|
"subqueries": [
|
{
|
"expression_cache": {
|
"r_loops": 10,
|
"r_hit_ratio": 0,
|
"query_block": {
|
"select_id": 2,
|
"r_loops": 10,
|
Attachments
Issue Links
- relates to
-
MDEV-34041 Additional information for materialized subqueries must be displayed in EXPLAIN/ANALYZE FORMAT=JSON
- Closed