[MDEV-7245] Incorrect display of subquery cache in union.result Created: 2014-12-01  Updated: 2014-12-02  Resolved: 2014-12-02

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.1
Fix Version/s: 10.1.2

Type: Bug Priority: Major
Reporter: Sergei Petrunia Assignee: Sergei Petrunia
Resolution: Fixed Votes: 0
Labels: None


 Description   

Looking at union.result in 10.1 code, one can see:

# 
# Bug #49734: Crash on EXPLAIN EXTENDED UNION ... ORDER BY 
#   <any non-const-function>
 
# 
CREATE TABLE t1 (a VARCHAR(10), FULLTEXT KEY a (a));
 
INSERT INTO t1 VALUES (1),(2);
 
CREATE TABLE t2 (b INT);
 
INSERT INTO t2 VALUES (1),(2);

...

EXPLAIN EXTENDED
 
SELECT * FROM t1 UNION SELECT * FROM t1
 
ORDER BY (SELECT a FROM t2 WHERE b = 12);
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 
1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
2	UNION	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
NULL	UNION RESULT	<union1,2>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	Using filesort
 
3	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
 
Warnings:
 
Note	1276	Field or reference 'a' of SELECT #3 was resolved in SELECT #-1
 
Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` union select `test`.`t1`.`a` AS `a` from `test`.`t1` order by <expr_cache><>((select `a` from `test`.`t2` where (`test`.`t2`.`b` = 12)))

Note this:

<expr_cache><>((select `a` from `test`.`t2` where (`test`.`t2`.`b` = 12)))

The subquery is actually dependent, and it is incorrect that we see "<expr_cache><>" and not "<expr_cache><a>".

 Comments   
Comment by Sergei Petrunia [ 2014-12-01 ]

Investigated together with sanja.
The subquery cache didn't register dependency on 'a', because of the following:

<spetrunia> bool Item_ident::collect_outer_ref_processor(uchar *param)
 
<spetrunia> {
 
<spetrunia>   Collect_deps_prm *prm= (Collect_deps_prm *)param;
 
<spetrunia>   if (depended_from &&
 
<spetrunia>       depended_from->nest_level_base == prm->nest_level_base &&
 
<spetrunia>       depended_from->nest_level < prm->nest_level)
 
<spetrunia> (gdb) print depended_from
 
<spetrunia>   $28 = (st_select_lex *) 0x7fff940720a0
 
<spetrunia> (gdb) p depended_from->nest_level_base
 
<spetrunia>   $29 = (SELECT_LEX_UNIT *) 0x4
 
<spetrunia> (gdb) p prm->nest_level_base
 
<spetrunia>   $30 = (st_select_lex_unit *) 0x2cff6c0
 
<spetrunia> (gdb) p/x depended_from->select_number
 
<spetrunia>   $33 = 0xffffffff

fake_select_lex objects have invalid data in nest_level and nest_level_base. These members are never set.

Generated at Thu Feb 08 07:18:06 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.