|
Thanks! I repeated on 10.2-10.5:
--source include/have_innodb.inc
|
|
CREATE TABLE t1 (i1 int)engine=innodb;
|
INSERT INTO `t1` VALUES (62),(66);
|
CREATE TABLE t2 (i1 int) engine=innodb;
|
|
SELECT 1 FROM t1
|
WHERE t1.i1 =( SELECT t1.i1 FROM t2
|
UNION SELECT i1 FROM (t1 AS dt1 natural JOIN t2)
|
window w1 as (partition by t1.i1));
|
|
10.2 d0785f773188b5f0eebb313
|
#3 <signal handler called>
|
#4 __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:50
|
#5 0x00007fbc46d32859 in __GI_abort () at abort.c:79
|
#6 0x00007fbc46d32729 in __assert_fail_base (fmt=0x7fbc46ec8588 "%s%s%s:%u: %s%sAssertion `%s' failed.\n%n", assertion=0x55b23ec4e2c8 "cache != __null", file=0x55b23ec4bab8 "/10.2/src/sql/sql_select.cc", line=18647, function=<optimized out>) at assert.c:92
|
#7 0x00007fbc46d43f36 in __GI___assert_fail (assertion=0x55b23ec4e2c8 "cache != __null", file=0x55b23ec4bab8 "/10.2/src/sql/sql_select.cc", line=18647, function=0x55b23ec4e288 "enum_nested_loop_state sub_select_cache(JOIN*, JOIN_TAB*, bool)") at assert.c:101
|
#8 0x000055b23e2104d2 in sub_select_cache (join=0x7fbbec017b18, join_tab=0x7fbbec088800, end_of_records=true) at /10.2/src/sql/sql_select.cc:18647
|
#9 0x000055b23e21071b in sub_select (join=0x7fbbec017b18, join_tab=0x7fbbec088450, end_of_records=true) at /10.2/src/sql/sql_select.cc:18825
|
#10 0x000055b23e20feff in do_select (join=0x7fbbec017b18, procedure=0x0) at /10.2/src/sql/sql_select.cc:18420
|
#11 0x000055b23e1e9a87 in JOIN::exec_inner (this=0x7fbbec017b18) at /10.2/src/sql/sql_select.cc:3651
|
#12 0x000055b23e1e8f2e in JOIN::exec (this=0x7fbbec017b18) at /10.2/src/sql/sql_select.cc:3446
|
#13 0x000055b23e29015d in st_select_lex_unit::exec (this=0x7fbbec013498) at /10.2/src/sql/sql_union.cc:1073
|
#14 0x000055b23e4f843b in subselect_union_engine::exec (this=0x7fbbec016a28) at /10.2/src/sql/item_subselect.cc:4030
|
#15 0x000055b23e4ecca1 in Item_subselect::exec (this=0x7fbbec0168a8) at /10.2/src/sql/item_subselect.cc:770
|
#16 0x000055b23e4eeadd in Item_singlerow_subselect::val_int (this=0x7fbbec0168a8) at /10.2/src/sql/item_subselect.cc:1391
|
#17 0x000055b23e0c469d in Item::val_int_result (this=0x7fbbec0168a8) at /10.2/src/sql/item.h:1274
|
#18 0x000055b23e45391b in Item_cache_int::cache_value (this=0x7fbbec08a318) at /10.2/src/sql/item.cc:9701
|
#19 0x000055b23e45e3c6 in Item_cache_wrapper::cache (this=0x7fbbec08a260) at /10.2/src/sql/item.cc:8362
|
#20 0x000055b23e44f829 in Item_cache_wrapper::val_int (this=0x7fbbec08a260) at /10.2/src/sql/item.cc:8416
|
#21 0x000055b23e464abf in Arg_comparator::compare_int_signed (this=0x7fbbec016b20) at /10.2/src/sql/item_cmpfunc.cc:970
|
#22 0x000055b23e478cc4 in Arg_comparator::compare (this=0x7fbbec016b20) at /10.2/src/sql/item_cmpfunc.h:87
|
#23 0x000055b23e467117 in Item_func_eq::val_int (this=0x7fbbec016a60) at /10.2/src/sql/item_cmpfunc.cc:1803
|
#24 0x000055b23e210d22 in evaluate_join_record (join=0x7fbbec016cd0, join_tab=0x7fbbec176b20, error=0) at /10.2/src/sql/sql_select.cc:18969
|
#25 0x000055b23e210af8 in sub_select (join=0x7fbbec016cd0, join_tab=0x7fbbec176b20, end_of_records=false) at /10.2/src/sql/sql_select.cc:18913
|
#26 0x000055b23e20fea6 in do_select (join=0x7fbbec016cd0, procedure=0x0) at /10.2/src/sql/sql_select.cc:18418
|
#27 0x000055b23e1e9a87 in JOIN::exec_inner (this=0x7fbbec016cd0) at /10.2/src/sql/sql_select.cc:3651
|
#28 0x000055b23e1e8f2e in JOIN::exec (this=0x7fbbec016cd0) at /10.2/src/sql/sql_select.cc:3446
|
#29 0x000055b23e1ea108 in mysql_select (thd=0x7fbbec000d90, tables=0x7fbbec012960, wild_num=0, fields=..., conds=0x7fbbec016a60, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7fbbec016cb0, unit=0x7fbbec004988, select_lex=0x7fbbec0050c8) at /10.2/src/sql/sql_select.cc:3849
|
#30 0x000055b23e1de25c in handle_select (thd=0x7fbbec000d90, lex=0x7fbbec0048c8, result=0x7fbbec016cb0, setup_tables_done_option=0) at /10.2/src/sql/sql_select.cc:361
|
#31 0x000055b23e1a8771 in execute_sqlcom_select (thd=0x7fbbec000d90, all_tables=0x7fbbec012960) at /10.2/src/sql/sql_parse.cc:6274
|
#32 0x000055b23e19f2e5 in mysql_execute_command (thd=0x7fbbec000d90) at /10.2/src/sql/sql_parse.cc:3585
|
#33 0x000055b23e1ac52c in mysql_parse (thd=0x7fbbec000d90, rawbuf=0x7fbbec0126f8 "SELECT 1 FROM t1 \nWHERE t1.i1 =( SELECT t1.i1 FROM t2 \nUNION SELECT i1 FROM (t1 AS dt1 natural JOIN t2) \nwindow w1 as (partition by t1.i1))", length=140, parser_state=0x7fbc41050570, is_com_multi=false, is_next_command=false) at /10.2/src/sql/sql_parse.cc:7796
|
#34 0x000055b23e19a756 in dispatch_command (command=COM_QUERY, thd=0x7fbbec000d90, packet=0x7fbbec008b51 "", packet_length=140, is_com_multi=false, is_next_command=false) at /10.2/src/sql/sql_parse.cc:1827
|
#35 0x000055b23e199251 in do_command (thd=0x7fbbec000d90) at /10.2/src/sql/sql_parse.cc:1381
|
#36 0x000055b23e2f488e in do_handle_one_connection (connect=0x55b240e34800) at /10.2/src/sql/sql_connect.cc:1336
|
#37 0x000055b23e2f45f3 in handle_one_connection (arg=0x55b240e34800) at /10.2/src/sql/sql_connect.cc:1241
|
#38 0x000055b23eb201a8 in pfs_spawn_thread (arg=0x55b240e17c00) at /10.2/src/storage/perfschema/pfs.cc:1869
|
#39 0x00007fbc47255609 in start_thread (arg=<optimized out>) at pthread_create.c:477
|
#40 0x00007fbc46e2f293 in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95
|
on non-debug build:
Version: '10.5.10-MariaDB'
|
210510 11:58:31 [ERROR] mysqld got signal 11 ;
|
|
mysys/stacktrace.c:213(my_print_stacktrace)[0x5645b94bc705]
|
??:0(__restore_rt)[0x7f557b070730]
|
sql/sql_join_cache.h:679(sub_select_cache(JOIN*, st_join_table*, bool))[0x5645b9309e5a]
|
sql/sql_select.cc:20357(do_select)[0x5645b932df32]
|
sql/sql_select.cc:4284(JOIN::exec())[0x5645b932e390]
|
sql/sql_union.cc:2236(st_select_lex_unit::exec())[0x5645b9375ad4]
|
sql/item_subselect.cc:4104(subselect_union_engine::exec())[0x5645b955fa2e]
|
sql/item_subselect.cc:834(Item_subselect::exec())[0x5645b95604cd]
|
sql/item_subselect.cc:1441(Item_singlerow_subselect::val_int())[0x5645b955fd6e]
|
sql/item.cc:9917(Item_cache_int::cache_value())[0x5645b94cee98]
|
sql/item.cc:8704(Item_cache_wrapper::cache())[0x5645b94e90d3]
|
sql/item.cc:8758(Item_cache_wrapper::val_int())[0x5645b94e2888]
|
sql/item_cmpfunc.cc:943(Arg_comparator::compare_int_signed())[0x5645b94eb868]
|
sql/item_cmpfunc.cc:1777(Item_func_eq::val_int())[0x5645b94ebe9b]
|
sql/sql_select.cc:20922(evaluate_join_record(JOIN*, st_join_table*, int))[0x5645b92fc181]
|
sql/sql_select.cc:20860(sub_select(JOIN*, st_join_table*, bool))[0x5645b9309a8d]
|
sql/sql_select.cc:20355(do_select)[0x5645b932e09f]
|
sql/sql_select.cc:4284(JOIN::exec())[0x5645b932e390]
|
sql/sql_select.cc:4761(mysql_select(THD*, TABLE_LIST*, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*))[0x5645b932c698]
|
sql/sql_select.cc:443(handle_select(THD*, LEX*, select_result*, unsigned long))[0x5645b932cffe]
|
sql/sql_parse.cc:6313(execute_sqlcom_select(THD*, TABLE_LIST*))[0x5645b92cdb39]
|
sql/sql_parse.cc:6055(mysql_execute_command(THD*))[0x5645b92d72ce]
|
sql/sql_parse.cc:8116(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x5645b92c91be]
|
sql/sql_parse.cc:1894(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x5645b92d47ea]
|
sql/sql_parse.cc:1370(do_command(THD*))[0x5645b92d5724]
|
sql/sql_connect.cc:1410(do_handle_one_connection(CONNECT*, bool))[0x5645b93be1a0]
|
sql/sql_connect.cc:1312(handle_one_connection)[0x5645b93be57d]
|
perfschema/pfs.cc:2204(pfs_spawn_thread)[0x5645b96ef2eb]
|
nptl/pthread_create.c:487(start_thread)[0x7f557b065fa3]
|
x86_64/clone.S:97(clone)[0x7f557ac704cf]
|
|
Trying to get some variables.
|
Some pointers may be invalid and cause the dump to abort.
|
Query (0x7f5530011e10): SELECT 1 FROM t1
|
WHERE t1.i1 =( SELECT t1.i1 FROM t2
|
UNION SELECT i1 FROM (t1 AS dt1 natural JOIN t2)
|
window w1 as (partition by t1.i1))
|
|
|
while I was simplifying this test, I was getting also another assertion, `table->no_keyread || !table->covering_keys.is_set(tab->index) || table->file->keyread == tab->index' , reported MDEV-25643
|
|
test from MDEV-25634, without using WINDOW:
CREATE TABLE t1 (i3 int NOT NULL, i1 int , i2 int , i4 int , PRIMARY key(i2));
|
INSERT INTO t1 VALUES (6,72,98,98),(46,1,6952,0);
|
|
SELECT i1 FROM t1
|
WHERE t1.i3 =
|
(SELECT ref_4.i2 FROM t1 AS ref_4
|
WHERE t1.i2 > (SELECT i3 FROM t1 ORDER BY i3 LIMIT 1 OFFSET 4)
|
UNION
|
SELECT ref_6.i2
|
FROM (t1 AS ref_5 JOIN t1 AS ref_6 ON ((ref_6.i1 > ref_6.i2) OR (ref_5.i4 < ref_5.i4)))
|
WHERE (t1.i2 >= t1.i2));
|
|
|
Debugging the example without WINDOW clause:
SELECT i1 FROM t1
|
WHERE t1.i3 =
|
(SELECT ref_4.i2 FROM t1 AS ref_4
|
WHERE t1.i2 > (SELECT i3 FROM t1 ORDER BY i3 LIMIT 1 OFFSET 4)
|
UNION
|
SELECT ref_6.i2
|
FROM
|
(t1 AS ref_5
|
JOIN
|
t1 AS ref_6 ON ((ref_6.i1 > ref_6.i2) OR (ref_5.i4 < ref_5.i4)))
|
WHERE (t1.i2 >= t1.i2));
|
EXPLAIN doesn't crash:
+------+--------------------+------------+-------+---------------+---------+---------+------+------+-------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+--------------------+------------+-------+---------------+---------+---------+------+------+-------------------------------------------------+
|
| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 2 | Using where |
|
| 2 | DEPENDENT SUBQUERY | ref_4 | index | NULL | PRIMARY | 4 | NULL | 2 | Using index |
|
| 3 | SUBQUERY | t1 | ALL | NULL | NULL | NULL | NULL | 2 | Using filesort |
|
| 4 | UNION | ref_5 | ALL | NULL | NULL | NULL | NULL | 2 | |
|
| 4 | UNION | ref_6 | ALL | NULL | NULL | NULL | NULL | 2 | Using where; Using join buffer (flat, BNL join) |
|
| NULL | UNION RESULT | <union2,4> | ALL | NULL | NULL | NULL | NULL | NULL | |
|
+------+--------------------+------------+-------+---------------+---------+---------+------+------+-------------------------------------------------+
|
It crashes when trying use join buffer for table ref_6. The join buffer is already gone - it was freed after the subquery was executed the previous time.
|
|
The subquery tree is:
id=1 has children
- (id=2, ref_4)
- has a child: (id=3, table t1, select with ORDER BY)
- (id=4) is a union of ref_5 and ref_6
I would have expected the query with id=4 be "DEPENDENT UNION"...
|
|
Both parts of UNION have outer references (highlighting them below):
SELECT /*id=1*/ i1
|
FROM t1
|
WHERE t1.i3 =
|
(SELECT
|
/*id=2*/ref_4.i2
|
FROM t1 AS ref_4
|
WHERE
|
t1.i2 /*<-OUTER REF*/ > (SELECT /*id=3*/ i3 FROM t1a ORDER BY i3 LIMIT 1 OFFSET 4)
|
UNION
|
SELECT /*id=4*/
|
ref_6.i2
|
FROM
|
(t1 AS ref_5
|
JOIN
|
t1 AS ref_6 ON (ref_6.i1 > ref_6.i2 OR ref_5.i4 < ref_5.i4)
|
)
|
WHERE
|
t1.i2 /*<-OUTER REF*/ >= t1.i2 /*<-OUTER REF*/
|
);
|
The condition WHERE t1.i2 >= t1.i2 seems to be removed by the optimizer.
Execution reaches these lines in st_select_lex::optimize_unflattened_subqueries()
sl->update_correlated_cache();
|
is_correlated_unit|= sl->is_correlated;
|
st_select_lex::update_correlated_cache() computes is_correlated=false, and then changes uncacheable from 1 to 0:
if (!is_correlated)
|
uncacheable&= ~UNCACHEABLE_DEPENDENT;
|
|
|
Made a fix in st_select_lex::optimize_unflattened_subqueries to implement the
following logic:
If at least one select in a union is not correlated (is_correlated_unit=FALSE),
then mark all children SELECTs as uncacheable (sl->uncacheable |=
UNCACHEABLE_DEPENDENT).
This fixes the above example but causes this test failure:
--- r/subselect4.result 2021-05-21 19:04:48.073379985 +0300
|
+++ r/subselect4.reject 2021-05-23 00:23:33.266502688 +0300
|
@@ -1362,17 +1362,17 @@
|
SELECT * FROM t1 WHERE
|
(SELECT f2 FROM t2
|
WHERE f4 <= ALL
|
(SELECT max(SQ1_t1.f4)
|
FROM t3 AS SQ1_t1 JOIN t3 AS SQ1_t3 ON SQ1_t3.f4
|
GROUP BY SQ1_t1.f4));
|
id select_type table type possible_keys key key_len ref rows Extra
|
1 PRIMARY t1 system NULL NULL NULL NULL 1
|
-2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
|
+2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
|
3 SUBQUERY SQ1_t1 index NULL f4 5 NULL 2 Using index; Using temporary
|
3 SUBQUERY SQ1_t3 index f4 f4 5 NULL 2 Using where; Using index; Using join buffer (flat, BNL join)
|
SELECT * FROM t1 WHERE
|
(SELECT f2 FROM t2
|
WHERE f4 <= ALL
|
(SELECT max(SQ1_t1.f4)
|
FROM t3 AS SQ1_t1 JOIN t3 AS SQ1_t3 ON SQ1_t3.f4
|
GROUP BY SQ1_t1.f4));
|
|
|
The above is caused by the select_lex with id=2 gets marked as correlated:
st_select_lex::optimize_unflattened_subqueries() (invoked for this->select_number=1) reaches this code:
if (!inner_join->cleaned)
|
=> sl->update_used_tables();
|
sl->update_correlated_cache();
|
is_correlated_unit|= sl->is_correlated;
|
And here the select becomes correlated! this happens here in st_select_lex::update_correlated_cache():
if (join->conds)
|
is_correlated|= MY_TEST(join->conds->used_tables() & OUTER_REF_TABLE_BIT);
|
where we have
join->conds->used_tables() = OUTER_REF | 0x1
|
.
The join->conds is:
<not>(<in_optimizer>(t2.f4,<min>(subquery#3) < <cache>(t2.f4)))
|
taking it apart:
Item_func_not_all:
|
arg_count=1,
|
arg0= Item_in_optimizer.
|
|
Item_in_optimizer:
|
arg_count=2
|
arg0= Item_field("t2.f4") // used_tables=1
|
arg1= Item_func_lt
|
|
Item_func_lt:
|
arg_count=2
|
arg0 = Item_maxmin_subselect // used_tables()=0
|
arg1= Item_direct_ref
|
|
Item_direct_ref:
|
ref= Item_cache_int // used_tables()= OUTER_REF_TABLE_BIT
|
|
Item_cache_int
|
has used_table_map=OUTER_REF_TABLE_BIT
|
// used_table_map is defined for Item_basic_constant
|
(debugging note: the Item_cache_int object is the 3rd Item_cache_int created by the query)
|
|
I am not sure which part of the code is incorrect.
The Item_cache_int has used_tables()=OUTER_REF_TABLE_BIT because it is explicitly set so in Item_in_optimizer::fix_left:
if ((used_tables_cache= args[0]->used_tables()) || !args[0]->const_item())
|
cache->set_used_tables(OUTER_REF_TABLE_BIT);
|
Then, Item_allany_subselect::transform_into_max_min does this:
Item **place= optimizer->arguments() + 1;
|
...
|
subs= func->create_swap(thd, expr, subs);
|
thd->change_item_tree(place, subs);
|
Here, expr is the Item_direct_ref(Item_cache_int(...)).
Is the second argument of the IN-optimizer considered to be
A. in the context of the parent select
B. in the context of the child select?
if A is correct, then it's hard to explain the cache->set_used_tables(OUTER_REF_TABLE_BIT) call.
if B is correct, then Item_in_optimizer::update_used_tables() is incorrect.
|
|
Another look at what we have after the {{ f4 <= ALL (SELECT ...)}} subquery has finished optimization:
(gdb) p dbug_print_item(join->conds)
|
$576 = 0x5555570c98c0 <dbug_item_print_buf> "<not>(<in_optimizer>(t2.f4,<min>(subquery#3) < <cache>(t2.f4)))"
|
(gdb) p ((Item*)join->conds)->used_tables()
|
$577 = 1
|
Ok
(gdb) p ((Item*)join->conds)->args[0]
|
$578 = (Item_in_optimizer *) 0x7fff74019668
|
(gdb) p ((Item*)join->conds)->args[0]->used_tables()
|
$579 = 1
|
Ok
(gdb) p ((Item*)join->conds)->args[0]->args[0]
|
$580 = (Item_field *) 0x7fff74013008
|
(gdb) p ((Item*)join->conds)->args[0]->args[0]->used_tables()
|
$581 = 1
|
Ok
(gdb) p ((Item*)join->conds)->args[0]->args[1]
|
$582 = (Item_func_lt *) 0x7fff7401b838
|
(gdb) p/x ((Item*)join->conds)->args[0]->args[1]->used_tables()
|
$584 = 0x4000000000000000
|
Not OK
|
|
A demonstration of the issue:
(gdb) p ((Item*)join->conds)->used_tables()
|
$587 = 1
|
(gdb) call ((Item*)join->conds)->update_used_tables()
|
(gdb) p/x ((Item*)join->conds)->used_tables()
|
$589 = 0x4000000000000001
|
So this is a problem waiting to happen.
|
|
Tried implementing a fix for the above:
diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc
|
index ed8e5e900a2..ba5ab60f673 100644
|
--- a/sql/item_subselect.cc
|
+++ b/sql/item_subselect.cc
|
@@ -2121,7 +2121,16 @@ bool Item_allany_subselect::transform_into_max_min(JOIN *join)
|
The swap is needed for expressions of type 'f1 < ALL ( SELECT ....)'
|
where we want to evaluate the sub query even if f1 would be null.
|
*/
|
- subs= func->create_swap(thd, expr, subs);
|
+ Item *new_arg= expr;
|
+
|
+ if (expr->type() == Item::REF_ITEM) {
|
+ Item *item2= ((Item_ref*)expr)->ref[0];
|
+ if (item2->type() == Item::CACHE_ITEM) {
|
+ new_arg= ((Item_cache*)item2)->get_example();
|
+ fprintf(stderr, "AAA: fix2 worked\n");
|
+ }
|
+ }
|
+ subs= func->create_swap(thd, new_arg, subs);
|
thd->change_item_tree(place, subs);
|
if (subs->fix_fields(thd, &subs))
|
DBUG_RETURN(true);
|
|
It is causing a failure for subselect4.test.
|
|
... the failure in subselect4.test exposes another issue. It can be observed as
follows:
set storage_engine=myisam;
|
CREATE TABLE t2 (c int , a int, b int);
|
INSERT INTO t2 VALUES (10,7,0);
|
CREATE TABLE t3 (a int, b int) ;
|
INSERT INTO t3 VALUES (5,0),(7,0);
|
CREATE TABLE t4 (a int);
|
INSERT INTO t4 VALUES (2),(8);
|
set @@optimizer_switch='semijoin=off,in_to_exists=on,materialization=off,subquery_cache=off';
|
explain format=json
|
SELECT * FROM t2
|
WHERE
|
t2.b IN (SELECT b
|
FROM t3
|
WHERE
|
t3.a = t2.a AND
|
a < SOME (SELECT * FROM t4)
|
)
|
OR ( t2.c > 242 );
|
| {
|
"query_block": {
|
"select_id": 1,
|
"const_condition": "<in_optimizer>(0,<exists>(subquery#2))",
|
"table": {
|
"table_name": "t2",
|
"access_type": "system",
|
"rows": 1,
|
"filtered": 100
|
},
|
"subqueries": [
|
{
|
"query_block": {
|
"select_id": 2,
|
"const_condition": "<nop>(<in_optimizer>(7,(subquery#3) > <cache>(t3.a)))",
|
"table": {
|
"table_name": "t3",
|
"access_type": "ALL",
|
"rows": 2,
|
"filtered": 100,
|
"attached_condition": "<cache>(0) = t3.b and t3.a = 7"
|
},
|
"subqueries": [
|
{
|
"query_block": {
|
"select_id": 3,
|
"table": {
|
"table_name": "t4",
|
"access_type": "ALL",
|
"rows": 2,
|
"filtered": 100
|
}
|
}
|
}
|
]
|
}
|
}
|
]
|
}
|
} |
|
Note these lines:
"const_condition": "<nop>(<in_optimizer>(7,(subquery#3) > <cache>(t3.a)))",
|
followed by:
"table": {
|
"table_name": "t3",
|
That is, the const_condition has references to t3.a while table t3 is a non-constant table.
The select itself happens to produce a correct result:
+------+------+------+
|
| c | a | b |
|
+------+------+------+
|
| 10 | 7 | 0 |
|
+------+------+------+
|
|
|
<nop>(<in_optimizer>(7,(subquery#3) > <cache>(t3.a)))
|
This represents the
t3.a < SOME (SELECT t4.a FROM t4)
|
Initially it was
<nop>(<in_optimizer>(t3.a,t3.a < any (subquery#3)))
|
But the parent subquery has t3.a = t2.a and t2.a is an outer reference to constant table t2 with t2.a=7.
Equality substitution in the subquery has substituted one use of "t3.a" with 7 but not the other.
|
|
The
is represented by an Item_allany_subselect.
For this item, the used_tables() becomes incorrect here:
#0 Item_subselect::used_tables (this=0x62b000003818) at /home/psergey/dev-git/10.2-cl/sql/item_subselect.cc:970
|
#1 0x0000555556328802 in Used_tables_and_const_cache::used_tables_and_const_cache_join (this=0x62b000005fe0, item=0x62b000003818) at /home/psergey/dev-git/10.2-cl/sql/item.h:4241
|
#2 0x0000555556328967 in Used_tables_and_const_cache::used_tables_and_const_cache_update_and_join (this=0x62b000005fe0, item=0x62b000003818) at /home/psergey/dev-git/10.2-cl/sql/item.h:4247
|
#3 0x00005555563289d0 in Used_tables_and_const_cache::used_tables_and_const_cache_update_and_join (this=0x62b000005fe0, argc=2, argv=0x62b000005fc8) at /home/psergey/dev-git/10.2-cl/sql/item.h:4258
|
#4 0x000055555632c482 in Item_func::update_used_tables (this=0x62b000005f38) at /home/psergey/dev-git/10.2-cl/sql/item_func.h:144
|
#5 0x0000555556328954 in Used_tables_and_const_cache::used_tables_and_const_cache_update_and_join (this=0x62b000003b28, item=0x62b000005f38) at /home/psergey/dev-git/10.2-cl/sql/item.h:4246
|
#6 0x00005555563289d0 in Used_tables_and_const_cache::used_tables_and_const_cache_update_and_join (this=0x62b000003b28, argc=1, argv=0x62b000003b10) at /home/psergey/dev-git/10.2-cl/sql/item.h:4258
|
#7 0x000055555632c482 in Item_func::update_used_tables (this=0x62b000003a80) at /home/psergey/dev-git/10.2-cl/sql/item_func.h:144
|
#8 0x000055555653a3d5 in Item_func::build_equal_items (this=0x62b000003a80, thd=0x62a00009c270, inherited=0x62b000003c50, link_item_fields=false, cond_equal_ref=0x0) at /home/psergey/dev-git/10.2-cl/sql/sql_select.cc:13734
|
#9 0x0000555556538efe in Item_cond_and::build_equal_items (this=0x62b000003b68, thd=0x62a00009c270, inherited=0x62b000003c50, link_item_fields=true, cond_equal_ref=0x62b000005520) at /home/psergey/dev-git/10.2-cl/sql/sql_select.cc:13589
|
#10 0x000055555653a7d9 in build_equal_items (join=0x62b0000050e8, cond=0x62b000003b68, inherited=0x0, join_list=0x62b000000ec0, ignore_on_conds=false, cond_equal_ref=0x62b000005520, link_equal_fields=true) at /home/psergey/dev-git/10.2-cl/sql/sql_select.cc:13835
|
#11 0x0000555556543ef7 in optimize_cond (join=0x62b0000050e8, conds=0x62b000003b68, join_list=0x62b000000ec0, ignore_on_conds=false, cond_value=0x62b0000053f8, cond_equal=0x62b000005520, flags=1) at /home/psergey/dev-git/10.2-cl/sql/sql_select.cc:15519
|
#12 0x00005555564da163 in JOIN::optimize_inner (this=0x62b0000050e8) at /home/psergey/dev-git/10.2-cl/sql/sql_select.cc:1382
|
#13 0x00005555564d740c in JOIN::optimize (this=0x62b0000050e8) at /home/psergey/dev-git/10.2-cl/sql/sql_select.cc:1127
|
#14 0x0000555556407394 in st_select_lex::optimize_unflattened_subqueries (this=0x62a0000a05a8, const_only=false) at /home/psergey/dev-git/10.2-cl/sql/sql_lex.cc:3865
|
#15 0x00005555568b3d91 in JOIN::optimize_unflattened_subqueries (this=0x62b000004560) at /home/psergey/dev-git/10.2-cl/sql/opt_subselect.cc:5326
|
#16 0x00005555564e14e5 in JOIN::optimize_inner (this=0x62b000004560) at /home/psergey/dev-git/10.2-cl/sql/sql_select.cc:2089
|
#17 0x00005555564d740c in JOIN::optimize (this=0x62b000004560) at /home/psergey/dev-git/10.2-cl/sql/sql_select.cc:1127
|
#18 0x00005555564f2b70 in mysql_select (thd=0x62a00009c270, tables=0x62b0000005b0, wild_num=1, fields=..., conds=0x62b000004240, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748612, result=0x62b000004480, unit=0x62a00009fe68, select_lex=0x62a0000a05a8) at /home/psergey/dev-git/10.2-cl/sql/sql_select.cc:3835
|
#19 0x0000555556591014 in mysql_explain_union (thd=0x62a00009c270, unit=0x62a00009fe68, result=0x62b000004480) at /home/psergey/dev-git/10.2-cl/sql/sql_select.cc:25373
|
#20 0x00005555564411fb in execute_sqlcom_select (thd=0x62a00009c270, all_tables=0x62b0000005b0) at /home/psergey/dev-git/10.2-cl/sql/sql_parse.cc:6213
|
#21 0x000055555642d1cc in mysql_execute_command (thd=0x62a00009c270) at /home/psergey/dev-git/10.2-cl/sql/sql_parse.cc:3585
|
#22 0x000055555644af53 in mysql_parse (thd=0x62a00009c270, rawbuf=0x62b000000290 "explain format=json SELECT * FROM t2 WHERE t2.b IN (SELECT b FROM t3 WHERE t3.a = t2.a AND a < SOME (SELECT * FROM t4)) OR ( t2.c > 242 )", length=140, parser_state=0x7fffc525ad60, is_com_multi=false, is_next_command=false) at /home/psergey/dev-git/10.2-cl/sql/sql_parse.cc:7796
|
Here, we have
(gdb) p this->used_tables()
|
$284 = 0
|
But
(gdb) p this->left_expr->used_tables()
|
$286 = 1
|
|
|
Looking at what Item_allany_subselect has for used_tables and update_used_tables:
$287 = (const Item_allany_subselect * const) 0x62b000003818
|
(gdb) p this->update_used_tables
|
$288 = {void (Item_in_subselect * const)} 0x555556c9c798 <Item_in_subselect::update_used_tables()>
|
(gdb) p this->used_tables
|
$289 = {table_map (const Item_subselect * const)} 0x555556c819c0 <Item_subselect::used_tables() const>
|
Item_in_subselect's update_used_tables takes the left expression into account:
void Item_in_subselect::update_used_tables()
|
{
|
Item_subselect::update_used_tables();
|
left_expr->update_used_tables();
|
//used_tables_cache |= left_expr->used_tables();
|
used_tables_cache= Item_subselect::used_tables() | left_expr->used_tables();
|
}
|
But then we use ancestor's used_tables();
table_map Item_subselect::used_tables() const
|
{
|
return (table_map) ((engine->uncacheable() & ~UNCACHEABLE_EXPLAIN)?
|
used_tables_cache : 0L);
|
}
|
which makes an assumption that engine->uncacheable()? --> used_tables()=0.
This is not the case for Item_allany_subselect representing t3.a < any (subquery#3).
|
|
57fe50eab9ff01590ec3396dca0de9081ef0ef00 OK to push (but check buioldbot yourself)
|
abortion_report.txt
fuzz.sql