[MDEV-18255] Server crashes in Bitmap<64u>::intersect Created: 2019-01-15  Updated: 2019-02-18  Resolved: 2019-01-24

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.0, 10.1, 10.2, 10.3, 10.4
Fix Version/s: 10.4.3, 10.1.38, 10.0.38, 10.2.22, 10.3.13

Type: Bug Priority: Blocker
Reporter: Alice Sherepa Assignee: Varun Gupta (Inactive)
Resolution: Fixed Votes: 0
Labels: regression

Issue Links:
Relates
relates to MDEV-13784 query causes seg fault Closed

 Description    

create table t1 (v1 varchar(1)) engine=myisam ;
 
create table t2 (v1 varchar(1)) engine=myisam ;
 
 
 
select 1 from t1 where exists 
	(select 1 from t1 where t1.v1 in (select t2.v1 from t2 having t2.v1 < 'j')) ;

10.0 12f362c3338d803b665

 
#4  0x00000000006017c8 in Bitmap<64u>::intersect (this=0x8f8f8f8f8f8f8ff7, map2=...) at /git/10.0/sql/sql_bitmap.h:154
 
#5  0x0000000000896d93 in Item_field::update_table_bitmaps (this=0x7fde84d46c30) at /git/10.0/sql/item.h:2312
 
#6  0x0000000000896e4c in Item_field::update_used_tables (this=0x7fde84d46c30) at /git/10.0/sql/item.h:2326
 
#7  0x00000000008afe05 in Item_equal::update_used_tables (this=0x7fde84d478c0) at /git/10.0/sql/item_cmpfunc.cc:6444
 
#8  0x000000000064e379 in st_select_lex::update_used_tables (this=0x7fde84cf88b8) at /git/10.0/sql/sql_lex.cc:3934
 
#9  0x000000000064d663 in st_select_lex::optimize_unflattened_subqueries (this=0x7fde8d6540f8, const_only=false) at /git/10.0/sql/sql_lex.cc:3554
 
#10 0x00000000007dd596 in JOIN::optimize_unflattened_subqueries (this=0x7fde84f644a0) at /git/10.0/sql/opt_subselect.cc:5301
 
#11 0x00000000006958c5 in JOIN::optimize_inner (this=0x7fde84f644a0) at /git/10.0/sql/sql_select.cc:1953
 
#12 0x0000000000692432 in JOIN::optimize (this=0x7fde84f644a0) at /git/10.0/sql/sql_select.cc:1041
 
#13 0x000000000069a95e in mysql_select (thd=0x7fde8d650070, rref_pointer_array=0x7fde8d6543a0, tables=0x7fde84cf82c0, wild_num=0, fields=..., conds=0x7fde84f64238, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7fde84f64480, unit=0x7fde8d653a08, select_lex=0x7fde8d6540f8) at /git/10.0/sql/sql_select.cc:3334
 
#14 0x0000000000690374 in handle_select (thd=0x7fde8d650070, lex=0x7fde8d653940, result=0x7fde84f64480, setup_tables_done_option=0) at /git/10.0/sql/sql_select.cc:377
 
#15 0x0000000000660fb3 in execute_sqlcom_select (thd=0x7fde8d650070, all_tables=0x7fde84cf82c0) at /git/10.0/sql/sql_parse.cc:5308
 
#16 0x0000000000658f9e in mysql_execute_command (thd=0x7fde8d650070) at /git/10.0/sql/sql_parse.cc:2558
 
#17 0x0000000000664194 in mysql_parse (thd=0x7fde8d650070, rawbuf=0x7fde84cf8088 "select 1 from t1 where exists \n(select 1 from t1 where t1.v1 in (select t2.v1 from t2 having t2.v1 < 'j'))", length=106, parser_state=0x7fde9a6b7670) at /git/10.0/sql/sql_parse.cc:6644
 
#18 0x0000000000655ff8 in dispatch_command (command=COM_QUERY, thd=0x7fde8d650070, packet=0x7fde92973071 "select 1 from t1 where exists \n(select 1 from t1 where t1.v1 in (select t2.v1 from t2 having t2.v1 < 'j')) ", packet_length=107) at /git/10.0/sql/sql_parse.cc:1301
 
#19 0x000000000065525f in do_command (thd=0x7fde8d650070) at /git/10.0/sql/sql_parse.cc:1003
 
#20 0x000000000078ba38 in do_handle_one_connection (thd_arg=0x7fde8d650070) at /git/10.0/sql/sql_connect.cc:1377
 
#21 0x000000000078b786 in handle_one_connection (arg=0x7fde8d650070) at /git/10.0/sql/sql_connect.cc:1292
 
#22 0x0000000000e4ae4e in pfs_spawn_thread (arg=0x7fde8d3fa3f0) at /git/10.0/storage/perfschema/pfs.cc:1861
 
#23 0x00007fde998626ba in start_thread (arg=0x7fde9a6b8700) at pthread_create.c:333
 
#24 0x00007fde98f0d41d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:109

Introduced in commit d0d0f88f2cd4da23c2c2da702da51fb533e7fb8a
Author: Varun Gupta <varun.gupta@mariadb.com>
Date: Sun Jan 6 23:15:25 2019 +0530

 Comments   
Comment by Varun Gupta (Inactive) [ 2019-01-17 ]

Approach 1
Don't call update_used_tables for JOINS that have been cleaned up
Patch
http://lists.askmonty.org/pipermail/commits/2019-January/013290.html

Comment by Varun Gupta (Inactive) [ 2019-01-19 ]

Approach 2

JOIN::optimize_unflattened_subqueries

for a certain select $X:
at the end of its JOIN::optimize() call
we call JOIN::optimize_unflattened_subqueries (this causes children of select $X be optimized)
the suggestion is that at the end of JOIN::optimize_unflattened_subqueries we call
this>select_lex->update_used_tables()

Comment by Varun Gupta (Inactive) [ 2019-01-19 ]

Also some further details 

CREATE TABLE t1 (a int,b int,
 
UNIQUE (a), KEY (b)
 
);
 
INSERT INTO t1 VALUES (1,1), (2,1);
 
CREATE TABLE st1 like t1;
 
INSERT INTO st1 VALUES (1,1), (2,1);
 
CREATE TABLE st2 like t1;
 
INSERT INTO st2 VALUES (1,1), (2,1);

Case 1

MariaDB [test]> SELECT MAX(b), (SELECT count(*) FROM st1,st2 WHERE st2.b <= t1.b) FROM t1 WHERE a = 230;
 
+--------+----------------------------------------------------+
 
| MAX(b) | (SELECT count(*) FROM st1,st2 WHERE st2.b <= t1.b) |
 
+--------+----------------------------------------------------+
 
|   NULL |                                               NULL |
 
+--------+----------------------------------------------------+
 
1 row in set (0.024 sec)
 
 
 
h3. Case 2
 
MariaDB [test]> SELECT MAX(b), (SELECT count(*) FROM st1,st2 WHERE 1=0) FROM t1 WHERE a = 230;
 
+--------+------------------------------------------+
 
| MAX(b) | (SELECT count(*) FROM st1,st2 WHERE 1=0) |
 
+--------+------------------------------------------+
 
|   NULL |                                        0 |
 
+--------+------------------------------------------+
 
1 row in set (0.003 sec)

So we have two cases , in case 1 we don't execute the subquery while in the 2nd case we execute the subquery.
Both the queries have an IMPOSSIBLE WHERE in the parant select.
So to keep the behaviour constant we would be executing the subqueries even when we have an IMPOSSIBLE WHERE in the parent.

Comment by Varun Gupta (Inactive) [ 2019-01-19 ]

Patch
http://lists.askmonty.org/pipermail/commits/2019-January/013297.html

Comment by Sergei Petrunia [ 2019-01-23 ]

Looking at the patch, this result difference:

@@ -6647,7 +6647,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 
 2	SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	2	
 SELECT COUNT(f1), (SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3;
 
 COUNT(f1)	f4
 
-0	NULL
 
+0	7
 
 EXPLAIN
 
 SELECT COUNT(f1), exists(SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3;
 
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 
@@ -6655,7 +6655,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 
 2	SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	2	
 SELECT COUNT(f1), exists(SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3;
 
 COUNT(f1)	f4
 
-0	0
 
+0	1
 
 EXPLAIN
 
 SELECT COUNT(f1), f2 > ALL (SELECT f1 FROM t1 WHERE f2 > 0) AS f4 FROM t2, t1 WHERE 'v'= f3;
 
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

The testcase for it is:

#
 
# LP BUG#985667 Wrong result with subquery in SELECT clause, and constant table in 
# main query and implicit grouping
 
#
 
CREATE TABLE t1 (f1 int) engine=MyISAM;
 
INSERT INTO t1 VALUES (7),(8);
 
CREATE TABLE t2 (f2 int, f3 varchar(1)) engine=MyISAM;
 
INSERT INTO t2 VALUES (3,'f');
 
 
 
SELECT COUNT(f1), (SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3;
 
SELECT COUNT(f1), exists(SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3;

The change in .result matche the discussion between Varun and me:

  • The parent query's join doesn't produce any rows.
  • But aggregate function is present without GROUP BY, which means implied grouping is performed
  • Implied grouping generates a special summary row
    • aggregate functions have summary values
    • references to table columns are not allowed by the standard, but MySQL's
      Extended GROUP BY syntax allows them. And they all have NULL values.
    • Expressions are allowed and are computed. (if an expression has column references, the expression is still computed, column references have NULL values)

We consider a subquery to be a kind of expression. So, in the above example, the subqueries must be computed, with outside references having NULL values.

But when I look at the bug which introduced the testcase: https://bugs.launchpad.net/maria/+bug/985667

I see that Timour has intentionally changed the behavior that we think is correct to something that we think is not correct.

This requires careful analysis.

Comment by Sergei Petrunia [ 2019-01-23 ]

In MySQL 8.0, one needs to remove ONLY_FULL_GROUP_BY from @@sql_mode to get the
query accepted. Then, the output is

mysql> SELECT COUNT(f1), (SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3;
 
+-----------+------+
 
| COUNT(f1) | f4   |
 
+-----------+------+
 
|         0 | NULL |
 
+-----------+------+
 
 
 
mysql> SELECT COUNT(f1), exists(SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3;
 
+-----------+------+
 
| COUNT(f1) | f4   |
 
+-----------+------+
 
|         0 |    0 |
 
+-----------+------+

That is, it doesn't compute the subqueries.

Comment by Sergei Petrunia [ 2019-01-24 ]

Look at this code in item_subselect.cc:

void Item_singlerow_subselect::no_rows_in_result()
 
{
 
  /*
 
    Subquery predicates outside of the SELECT list must be evaluated in order
 
    to possibly filter the special result row generated for implicit grouping
 
    if the subquery is in the HAVING clause.
 
    If the predicate is constant, we need its actual value in the only result
 
    row for queries with implicit grouping.
 
  */
 
  if (parsing_place != SELECT_LIST || const_item())
 
    return;
 
  value= Item_cache::get_cache(new Item_null());

It makes non-constant subquery in select list to produce NULL value for implict-grouping's NULL-summary-row.

const_item gets its value from const_item_cache:

bool Item_subselect::const_item() const
 
{
 
  DBUG_ASSERT(thd);
 
  return (thd->lex->context_analysis_only ?
 
          FALSE :
 
          forced_const || const_item_cache);
 
}

.

Here, before the patch we have const_item_cache=false, even if subquery_item->used_tables()=0 and the subquery item is marked as uncorrelated.

Comment by Sergei Petrunia [ 2019-01-24 ]

Agreed to push the first patch as it is a more limited, conservative solution.

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