[MDEV-21184] Assertion `used_tables_cache == 0' failed in Item_func::fix_fields with condition_pushdown_from_having Created: 2019-11-30  Updated: 2024-01-19  Resolved: 2020-01-19

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.4
Fix Version/s: 10.4.12, 10.5.1

Type: Bug Priority: Major
Reporter: Elena Stepanova Assignee: Igor Babaev
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Problem/Incident
causes MDEV-29363 Server crashed with heap-use-after-fr... In Review
Relates
relates to MDEV-25650 Assertion `used_tables_cache == 0' fa... Open

 Description    

SET optimizer_switch='condition_pushdown_from_having=on';
 
 
 
CREATE TABLE t1 (a INT);
 
INSERT INTO t1 VALUES (1),(2);
 
 
 
CREATE TABLE t2 (b INT);
 
INSERT INTO t2 VALUES (2),(3);
 
 
 
SELECT a FROM t1 GROUP BY a HAVING a = 8 OR a = ( SELECT MIN(b) FROM t2 );
 
 
 
# Cleanup
 
DROP TABLE t1, t2;

10.4 a3b63b8d

 
mysqld: /data/src/10.4/sql/item_func.cc:328: virtual bool Item_func::fix_fields(THD*, Item**): Assertion `used_tables_cache == 0' failed.
 
191201  0:29:59 [ERROR] mysqld got signal 6 ;
 
 
 
#7  0x00007ff69f024f12 in __GI___assert_fail (assertion=0x5624c9042ff8 "used_tables_cache == 0", file=0x5624c9042fd8 "/data/src/10.4/sql/item_func.cc", line=328, function=0x5624c9043f60 <Item_func::fix_fields(THD*, Item**)::__PRETTY_FUNCTION__> "virtual bool Item_func::fix_fields(THD*, Item**)") at assert.c:101
 
#8  0x00005624c8592648 in Item_func::fix_fields (this=0x7ff650073530, thd=0x7ff650000af0, ref=0x7ff6500754b8) at /data/src/10.4/sql/item_func.cc:328
 
#9  0x00005624c80963eb in Item::fix_fields_if_needed (this=0x7ff650073530, thd=0x7ff650000af0, ref=0x7ff6500754b8) at /data/src/10.4/sql/item.h:956
 
#10 0x00005624c8096419 in Item::fix_fields_if_needed_for_scalar (this=0x7ff650073530, thd=0x7ff650000af0, ref=0x7ff6500754b8) at /data/src/10.4/sql/item.h:960
 
#11 0x00005624c811aa67 in Item::fix_fields_if_needed_for_bool (this=0x7ff650073530, thd=0x7ff650000af0, ref=0x7ff6500754b8) at /data/src/10.4/sql/item.h:964
 
#12 0x00005624c85609c4 in Item_cond::fix_fields (this=0x7ff6500753c0, thd=0x7ff650000af0, ref=0x0) at /data/src/10.4/sql/item_cmpfunc.cc:4845
 
#13 0x00005624c823123e in make_cond_for_table_from_pred (thd=0x7ff650000af0, root_cond=0x7ff650015f18, cond=0x7ff650015f18, tables=4611686018427387905, used_table=0, join_tab_idx_arg=0, exclude_expensive_cond=false, retain_ref_cond=false, is_top_and_level=true) at /data/src/10.4/sql/sql_select.cc:22180
 
#14 0x00005624c8230d65 in make_cond_for_table (thd=0x7ff650000af0, cond=0x7ff650015f18, tables=4611686018427387905, used_table=0, join_tab_idx_arg=0, exclude_expensive_cond=false, retain_ref_cond=false) at /data/src/10.4/sql/sql_select.cc:22075
 
#15 0x00005624c81fab63 in JOIN::add_having_as_table_cond (this=0x7ff650016928, tab=0x7ff6500747c0) at /data/src/10.4/sql/sql_select.cc:3035
 
#16 0x00005624c81fd094 in JOIN::make_aggr_tables_info (this=0x7ff650016928) at /data/src/10.4/sql/sql_select.cc:3582
 
#17 0x00005624c81fa79f in JOIN::optimize_stage2 (this=0x7ff650016928) at /data/src/10.4/sql/sql_select.cc:2958
 
#18 0x00005624c81f7fee in JOIN::optimize_inner (this=0x7ff650016928) at /data/src/10.4/sql/sql_select.cc:2256
 
#19 0x00005624c81f595c in JOIN::optimize (this=0x7ff650016928) at /data/src/10.4/sql/sql_select.cc:1593
 
#20 0x00005624c8200c60 in mysql_select (thd=0x7ff650000af0, tables=0x7ff6500137f8, wild_num=0, fields=..., conds=0x0, og_num=1, order=0x0, group=0x7ff650013fd8, having=0x7ff650015f18, proc_param=0x0, select_options=2147748608, result=0x7ff650016900, unit=0x7ff650004a20, select_lex=0x7ff650013230) at /data/src/10.4/sql/sql_select.cc:4628
 
#21 0x00005624c81f0958 in handle_select (thd=0x7ff650000af0, lex=0x7ff650004958, result=0x7ff650016900, setup_tables_done_option=0) at /data/src/10.4/sql/sql_select.cc:420
 
#22 0x00005624c81b70d1 in execute_sqlcom_select (thd=0x7ff650000af0, all_tables=0x7ff6500137f8) at /data/src/10.4/sql/sql_parse.cc:6360
 
#23 0x00005624c81ac77f in mysql_execute_command (thd=0x7ff650000af0) at /data/src/10.4/sql/sql_parse.cc:3899
 
#24 0x00005624c81bb1dd in mysql_parse (thd=0x7ff650000af0, rawbuf=0x7ff650013138 "SELECT a FROM t1 GROUP BY a HAVING a = 8 OR a = ( SELECT MIN(b) FROM t2 )", length=73, parser_state=0x7ff6983f5170, is_com_multi=false, is_next_command=false) at /data/src/10.4/sql/sql_parse.cc:7901
 
#25 0x00005624c81a6382 in dispatch_command (command=COM_QUERY, thd=0x7ff650000af0, packet=0x7ff65019e7c1 "SELECT a FROM t1 GROUP BY a HAVING a = 8 OR a = ( SELECT MIN(b) FROM t2 )", packet_length=73, is_com_multi=false, is_next_command=false) at /data/src/10.4/sql/sql_parse.cc:1842
 
#26 0x00005624c81a4a0f in do_command (thd=0x7ff650000af0) at /data/src/10.4/sql/sql_parse.cc:1360
 
#27 0x00005624c832d3cd in do_handle_one_connection (connect=0x5624cc555500) at /data/src/10.4/sql/sql_connect.cc:1412
 
#28 0x00005624c832d11c in handle_one_connection (arg=0x5624cc555500) at /data/src/10.4/sql/sql_connect.cc:1316
 
#29 0x00005624c8d2f2ff in pfs_spawn_thread (arg=0x5624cc4d3440) at /data/src/10.4/storage/perfschema/pfs.cc:1862
 
#30 0x00007ff6a0b9a4a4 in start_thread (arg=0x7ff6983f6700) at pthread_create.c:456
 
#31 0x00007ff69f0e1d0f in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:97

Reproducible with at least MyISAM, InnoDB, Aria.
Reproducible on 10.4-10.5 debug builds.
EXPLAIN on a debug build also crashes.
Not reproducible with condition_pushdown_from_having=off.
No obvious problem on a non-debug build.
EXPLAIN on a non-debug build returns

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	Using temporary; Using filesort
 
2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	
Warnings:
 
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` group by `test`.`t1`.`a` having 1


 



 Comments   
Comment by Igor Babaev [ 2019-12-31 ]

Here's the result of my analysis:
The reported query has a GROUP BY clause and a HAVING clause. So the optimization of condition pushdown from HAVING into WHERE may be applicable and a call of the function st_select_lex::pushdown_from_having_into_where() checks whether the optimization can be applied and, if so, builds a conditions that can be pushed. To do this the finction first calls
check_cond_extraction_for_grouping_fields() that figure out what parts of the HAVING can be pushed into WHERE. At the time of this check the HAVING condition for our query looks like

"multiple equal(8, t1.a) or multiple equal((subquery#2), t1.a)"

As both disjuncts depend only on grouping fields the whole OR condition is marked as pushable. Here we have already one defect: expensive conditions should be pushed. Anyway our subquery is not evaluated as expensive.
After this check we try to build the condition that can be pushed (in a general case a list of such conditions) in a call of st_select_lex::build_pushable_cond_for_having_pushdown()d.
Our OR condition is a top level condition that wholly can be pushed. So it's has to be 'moved' : to be added to WHERE and removed from HAVING. However as it contains multiple equalities these multiple equalities first must be transformed into conjuctions of simple equalities otherwise it would be hard to add the pushed condition to WHERE that may contain its own multiple equalities.
The conversion of multiple equalities is done with a call of transformer() with the transformation call-back function multiple_equality_transformer(). The multiple equality
multiple equal(8, t1.a) is successfully replaced with the expression t1.a=8 while multiple equal((subquery#2), t1.a) failed to be converted because the conversion procedure
create_pushable_equalities() tries to build clones of both of all elements of the multiple equality and it fails to clone the subquery item (any subquery item cannot be cloned now).
With this failure the condition remains in HAVING, but in the form

t1.a=8 or multiple equal((subquery#2), t1.a)

with t1.a=8 unfixed. Fixing of this newly built item is supposed to happen later when the whole OR condition is supposed to be moved to WHERE.
After the call of substitute_for_best_equal_field() for having update_used_tables() is called for having and it updates used_tables_cache for t1.a=8 that remains unfixed.
Later in the call of make_cond_for_table() for having fix_fields() is called and it fails the assertion (used_tables_cache == 0) for t1.a=8.

Comment by Igor Babaev [ 2020-01-03 ]

An observation:
For the query

SELECT a FROM t1 GROUP BY a HAVING  a = ( SELECT MIN(c) FROM t2 );

the condition in HAVING is not pushed into WHERE:

MariaDB [test]> EXPLAIN FORMAT=JSON SELECT a FROM t1 GROUP BY a HAVING  a = ( SELECT MIN(c) FROM t2 ) \G
 
*************************** 1. row ***************************
 
EXPLAIN: {
 
  "query_block": {
 
    "select_id": 1,
 
    "having_condition": "t1.a = (subquery#2)",
 
    "filesort": {
 
      "sort_key": "t1.a",
 
      "temporary_table": {
 
        "table": {
 
          "table_name": "t1",
 
          "access_type": "ALL",
 
          "rows": 7,
 
          "filtered": 100
 
        },
 
        "subqueries": [
 
          {
 
            "query_block": {
 
              "select_id": 2,
 
              "table": {
 
                "table_name": "t2",
 
                "access_type": "ALL",
 
                "rows": 2,
 
                "filtered": 100
 
              }
 
            }
 
          }
 
        ]
 
      }
 
    }
 
  }
 
}

The condition can be just moved to WHERE and it is inexpensive. It makes sense to push it into WHERE.

Comment by Oleksandr Byelkin [ 2020-01-15 ]

OK to push

Comment by Igor Babaev [ 2020-01-19 ]

A fix for this bug was pushed into 10.4

Generated at Thu Feb 08 09:05:13 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.