[MDEV-22779] Crash: Prepared Statement with a '?' parameter inside a re-used CTE Created: 2020-06-02  Updated: 2021-04-19  Resolved: 2020-06-14

Status: Closed
Project: MariaDB Server
Component/s: Optimizer - CTE
Affects Version/s: 10.3.21
Fix Version/s: 10.2.33, 10.3.24, 10.4.14, 10.5.5

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

AWS Linux



 Description   

I don't have a testcase, I only have a customer's core file.

As far as I was able to investigate, the following has happened:

1. There is a big query with nested SELECTs and non-recursive CTEs. Some CTEs are used multiple times.

2. The query is submitted to server as a Prepared Statement. The prepared statement has many '?' parameters.

3. [At least] one of the parameters is inside in a CTE that's reused and it used like so:

   WHERE 
      table.key= ?

We crash in optimize_keyuse(), when we are trying to process a KEYUSE element created from the above equality.

We crash somewhere here:

 
 
    if (keyuse->used_tables &
 
	(map= (keyuse->used_tables & ~join->const_table_map &
 
	       ~OUTER_REF_TABLE_BIT)))
 
    {
 
      uint n_tables= my_count_bits(map);
 
      if (n_tables == 1)			// Only one table
 
      {
 
        Table_map_iterator it(map);
 
        int tablenr= it.next_bit();
 
        DBUG_ASSERT(tablenr != Table_map_iterator::BITMAP_END);
 
	TABLE *tmp_table=join->table[tablenr];
 
        if (tmp_table) // already created
 
          keyuse->ref_table_rows= MY_MAX(tmp_table->file->stats.records, 100);
 
      }

The cause is that the keyuse has:

keyuse->used_tables = PARAM_TABLE_BIT
 
keyuse->val = Item_param

With PARAM_TABLE_BIT, the "if (n_tables == 1)" branch is taken, and we get a tablenr value, but join->table[tablenr] is not pointing to a valid TABLE object hence we get a poorly-reproducible crash.

We should not get {{ keyuse->used_tables = PARAM_TABLE_BIT}}. We do, because

$keyuse->val->state == Item_param::NO_VALUE

(which also shouldn't be happening).

There is Prepared_statement::param_array which is an array of "primary" parameter instances, and

$keyuse->val != $prepared_statement->param_array[$i] for any value of $i.

However, I can find $keyuse->val in

$prepared_statement->param_array[$a].m_clones.m_array[$b]

That is, it is a "secondary" Item_param object that was created for a CTE.

Further, I observe that

  • The "primary" Item_param objects from $prepared_statement->param_array[i] all have state=Item_param::SHORT_DATA_VALUE or other similar states
  • The "secondary" Item_param objects from "m_clones" arrays all have state=Item_param::NO_VALUE.

 Comments   
Comment by Sergei Petrunia [ 2020-06-02 ]

Documentation about Execute in the protocol: https://mariadb.com/kb/en/com_stmt_execute/

According to Diego,

  • A client has to send the datatypes on the first COM_STMT_EXECUTE
  • It doesn't have to send them on subsequent execute commands.
  • Java connector is always sending the datatypes.
  • He's not sure whether Connector/C is sending or not
Comment by Sergei Petrunia [ 2020-06-03 ]

Decoding the Client's packet

(gdb) x/1024xb $thd->net.read_pos
 
  0x17    0x08    0x00    0x00    0x00    0x00    0x01    0x00
 
|=COM_=| |=====   statement_id =======| |=flags| |== iteration_


  0x00    0x00    0x00    0x00    0x00    0x01   
  _count======|  |===== null-bitmap ==| |=send_type=|
 
 

  0xfd    0x00  - parameter# 1
 
  0xfd    0x00  - parameter# 2
 
  0xfd    0x00  - parameter# 3
 
  0xfd    0x00  - parameter# 4
 
  0xfd    0x00  - parameter# 5
 
  0xfd    0x00  - parameter# 6
 
  0xfd    0x00  - parameter# 7
 
  0xfd    0x00  - parameter# 8
 
  0xfd    0x00  - parameter# 9
 
  0xfd    0x00  - parameter# 10
 
  0xfd    0x00  - parameter# 11  
  0xfd    0x00  - parameter# 12  
  0xfd    0x00  - parameter# 13
 
  0xfd    0x00  - parameter# 14  
  0xfd    0x00  - parameter# 15
 
  0xfd    0x00  - parameter# 16
 
  0xfd    0x00  - parameter# 17
 
  0xfd    0x00  - parameter# 18  
  0xfd    0x00  - parameter# 19
 
  0xfd    0x00  - parameter# 20

0xfd=253=MYSQL_TYPE_VAR_STRING (see
https://mariadb.com/kb/en/resultset/#field-types)

Noe that send_type=1... This goes against the "exotic client didn't send the
types" hypothesis?

Comment by Sergei Petrunia [ 2020-06-13 ]

No, one doesn't need an "exotic" client after all. I was mistaken.

Comment by Sergei Petrunia [ 2020-06-13 ]

The steps to reproduce are:

Apply this patch:

diff --git a/sql/sql_select.cc b/sql/sql_select.cc
 
index ae26458d451..ff9b024756c 100644
 
--- a/sql/sql_select.cc
 
+++ b/sql/sql_select.cc
 
@@ -6540,6 +6540,7 @@ void optimize_keyuse(JOIN *join, DYNAMIC_ARRAY *keyuse_array)
 
       uint n_tables= my_count_bits(map);
 
       if (n_tables == 1)			// Only one table
 
       {
 
+        DBUG_ASSERT(!(map & PSEUDO_TABLE_BITS)); // Must be a real table
 
         Table_map_iterator it(map);
 
         int tablenr= it.next_bit();
 
         DBUG_ASSERT(tablenr != Table_map_iterator::BITMAP_END);

Then, start the server with slow-query-log enabled.

Then, prepare the dataset:

create table t1(a int, b int, key(a));
 
insert into t1 (a) values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

Then, prepare and execute this Prepared Statement. It must be done through client-server binary protocol. 

explain 
with T as 
( 
  select * from t1 where t1.a=? limit 2 
) 
select * from T as TA, T as TB;;

Here is a program that does that using Connector/C: https://gist.github.com/spetrunia/a3b98df451f8a8e42df51319181a259a

This will cause the assert to fire on the server:

  Thread 34 "mysqld" received signal SIGABRT, Aborted.
 
  [Switching to Thread 0x7fffd981f700 (LWP 13761)]
 
  __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:51
 
  51	../sysdeps/unix/sysv/linux/raise.c: No such file or directory.
 
(gdb) wher
 
  #0  __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:51
 
  #1  0x00007ffff5225801 in __GI_abort () at abort.c:79
 
  #2  0x00007ffff521539a in __assert_fail_base (fmt=0x7ffff539c7d8 "%s%s%s:%u: %s%sAssertion `%s' failed.\n%n", assertion=assertion@entry=0x555556876df8 "!(map & ((((table_map) 1) << (sizeof(table_map)*8-3)) | (((table_map) 1) << (sizeof(table_map)*8-2)) | (((table_map) 1) << (sizeof(table_map)*8-1))))", file=file@entry=0x555556876008 "/home/psergey/dev-git/10.3-cl/sql/sql_select.cc", line=line@entry=6543, function=function@entry=0x555556879a80 <optimize_keyuse(JOIN*, st_dynamic_array*)::__PRETTY_FUNCTION__> "void optimize_keyuse(JOIN*, DYNAMIC_ARRAY*)") at assert.c:92
 
  #3  0x00007ffff5215412 in __GI___assert_fail (assertion=0x555556876df8 "!(map & ((((table_map) 1) << (sizeof(table_map)*8-3)) | (((table_map) 1) << (sizeof(table_map)*8-2)) | (((table_map) 1) << (sizeof(table_map)*8-1))))", file=0x555556876008 "/home/psergey/dev-git/10.3-cl/sql/sql_select.cc", line=6543, function=0x555556879a80 <optimize_keyuse(JOIN*, st_dynamic_array*)::__PRETTY_FUNCTION__> "void optimize_keyuse(JOIN*, DYNAMIC_ARRAY*)") at assert.c:101
 
  #4  0x0000555555c8dd80 in optimize_keyuse (join=0x7fff68015078, keyuse_array=0x7fff68015368) at /home/psergey/dev-git/10.3-cl/sql/sql_select.cc:6543
 
  #5  0x0000555555c8998d in make_join_statistics (join=0x7fff68015078, tables_list=..., keyuse_array=0x7fff68015368) at /home/psergey/dev-git/10.3-cl/sql/sql_select.cc:5118
 
  #6  0x0000555555c7e67d in JOIN::optimize_inner (this=0x7fff68015078) at /home/psergey/dev-git/10.3-cl/sql/sql_select.cc:1942


(gdb) up
 
  #4  0x0000555555c8dd80 in optimize_keyuse (join=0x7fff68015078, keyuse_array=0x7fff68015368) at /home/psergey/dev-git/10.3-cl/sql/sql_select.cc:6543
 
(gdb) p/x map
 
  $89 = 0x2000000000000000
 
(gdb) p/x PARAM_TABLE_BIT
 
  $91 = 0x2000000000000000

Comment by Sergei Petrunia [ 2020-06-13 ]

There is a call to Item_param::sync_clones made in EXECUTE:

  #0  Item_param::sync_clones (this=0x7fff68073ad8) at /home/psergey/dev-git/10.3-cl/sql/item.cc:4138
 
  #1  0x0000555555c5a048 in set_conversion_functions (stmt=0x7fff680708f0, data=0x7fffd981e268, data_end=0x7fff68008ee2 "") at /home/psergey/dev-git/10.3-cl/sql/sql_prepare.cc:974
 
  #2  0x0000555555c5a139 in setup_conversion_functions (stmt=0x7fff680708f0, data=0x7fffd981e268, data_end=0x7fff68008ee2 "", bulk_protocol=false) at /home/psergey/dev-git/10.3-cl/sql/sql_prepare.cc:995
 
  #3  0x0000555555c61491 in Prepared_statement::set_parameters (this=0x7fff680708f0, expanded_query=0x7fffd981e340, packet=0x7fff68008edc "\003\200\002", packet_end=0x7fff68008ee2 "") at /home/psergey/dev-git/10.3-cl/sql/sql_prepare.cc:4135
 
  #4  0x0000555555c61624 in Prepared_statement::execute_loop (this=0x7fff680708f0, expanded_query=0x7fffd981e340, open_cursor=false, packet=0x7fff68008eda "", packet_end=0x7fff68008ee2 "") at /home/psergey/dev-git/10.3-cl/sql/sql_prepare.cc:4205
 
  #5  0x0000555555c5eed5 in mysql_stmt_execute_common (thd=0x7fff68000d50, stmt_id=1, packet=0x7fff68008eda "", packet_end=0x7fff68008ee2 "", cursor_flags=0, bulk_op=false, read_types=false) at /home/psergey/dev-git/10.3-cl/sql/sql_prepare.cc:3235
 
  #6  0x0000555555c5ea35 in mysqld_stmt_execute (thd=0x7fff68000d50, packet_arg=0x7fff68008ed1 "\001", packet_length=17) at /home/psergey/dev-git/10.3-cl/sql/sql_prepare.cc:3132
 
  #7  0x0000555555c3072d in dispatch_command (command=COM_STMT_EXECUTE, thd=0x7fff68000d50, packet=0x7fff68008ed1 "\001", packet_length=17, is_com_multi=false, is_next_command=false) at /home/psergey/dev-git/10.3-cl/sql/sql_parse.cc:1798

But this one "distributes" the the no-value state:

(gdb) p this->state
 
  $98 = Item_param::NO_VALUE

Comment by Sergei Petrunia [ 2020-06-13 ]

Then, Prepared_statement::set_parameters calls set_params
which points to insert_params_with_log().

insert_params_with_log doesn't have the 

    param->sync_clones();

call while insert_params() and other such functions do

Comment by Sergei Petrunia [ 2020-06-13 ]

bb-10.2-mdev22779

Comment by Sergei Petrunia [ 2020-07-02 ]

Fix Version/s: 10.5.4, 10.2.33, 10.3.24, 10.4.14

A correction: the fix is in the source for these MariaDB versions: 10.5.5, 10.2.33, 10.3.24, 10.4.14 (none of them is released yet at the moment).
It is not available in 10.5.4.

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