Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
5.5(EOL), 10.0(EOL)
-
5.5.47-1, 5.5.48-0
Description
The bug report was initially reported upstream as http://bugs.mysql.com/bug.php?id=74889. On MariaDB 5.5/10.0, the same test case causes a different assertion failure.
Even if it's related, the upstream issue is said to be fixed in 5.6.22 (it's not public yet, so couldn't check), and since it's not InnoDB, the bugfix won't be automatically merged to 10.0.
5.5/sql/item_subselect.cc:4334: bool subselect_hash_sj_engine::init(List<Item>*, uint): Assertion `0' failed.
|
141117 15:23:25 [ERROR] mysqld got signal 6 ;
|
#6 0x00007f68cdf706f1 in *__GI___assert_fail (assertion=0xe1b1d8 "0", file=<optimized out>, line=4334, function=0xe1d620 "bool subselect_hash_sj_engine::init(List<Item>*, uint)") at assert.c:81
|
#7 0x0000000000885b5f in subselect_hash_sj_engine::init (this=0x7f68c898b5f0, tmp_columns=0x7f68c8a87a98, subquery_id=2) at /home/elenst/bzr/5.5/sql/item_subselect.cc:4334
|
#8 0x0000000000882633 in Item_in_subselect::setup_mat_engine (this=0x7f68c8a88c58) at /home/elenst/bzr/5.5/sql/item_subselect.cc:2766
|
#9 0x000000000077ba23 in JOIN::choose_subquery_plan (this=0x7f68c8968698, join_tables=1) at /home/elenst/bzr/5.5/sql/opt_subselect.cc:5539
|
#10 0x000000000066e626 in make_join_statistics (join=0x7f68c8968698, tables_list=..., conds=0x0, keyuse_array=0x7f68c89689a0) at /home/elenst/bzr/5.5/sql/sql_select.cc:3826
|
#11 0x00000000006654ce in JOIN::optimize (this=0x7f68c8968698) at /home/elenst/bzr/5.5/sql/sql_select.cc:1229
|
#12 0x000000000087c95c in Item_in_subselect::optimize (this=0x7f68c8a88c58, out_rows=0x7f68c93b4518, cost=0x7f68c93b4510) at /home/elenst/bzr/5.5/sql/item_subselect.cc:689
|
#13 0x000000000077ad5b in setup_jtbm_semi_joins (join=0x7f68c8968078, join_list=0x7f68c9953bd0, join_where=0x7f68c8968490) at /home/elenst/bzr/5.5/sql/opt_subselect.cc:5181
|
#14 0x0000000000664c56 in JOIN::optimize (this=0x7f68c8968078) at /home/elenst/bzr/5.5/sql/sql_select.cc:1084
|
#15 0x000000000066bd37 in mysql_select (thd=0x7f68c9950060, rref_pointer_array=0x7f68c9953cc8, tables=0x7f68c8a87298, wild_num=1, fields=..., conds=0x7f68c8a88c58, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7f68c8a88ed8, unit=0x7f68c9953380, select_lex=0x7f68c9953a58) at /home/elenst/bzr/5.5/sql/sql_select.cc:3080
|
#16 0x0000000000662899 in handle_select (thd=0x7f68c9950060, lex=0x7f68c99532d0, result=0x7f68c8a88ed8, setup_tables_done_option=0) at /home/elenst/bzr/5.5/sql/sql_select.cc:319
|
#17 0x000000000063bee0 in execute_sqlcom_select (thd=0x7f68c9950060, all_tables=0x7f68c8a87298) at /home/elenst/bzr/5.5/sql/sql_parse.cc:4689
|
#18 0x00000000006350c2 in mysql_execute_command (thd=0x7f68c9950060) at /home/elenst/bzr/5.5/sql/sql_parse.cc:2234
|
#19 0x000000000063e5ee in mysql_parse (thd=0x7f68c9950060, rawbuf=0x7f68c8a87078 "SELECT * FROM t1 WHERE a IN(SELECT MIN(a)FROM t1)", length=49, parser_state=0x7f68c93b5630) at /home/elenst/bzr/5.5/sql/sql_parse.cc:5800
|
#20 0x0000000000632609 in dispatch_command (command=COM_QUERY, thd=0x7f68c9950060, packet=0x7f68c9a07061 "SELECT * FROM t1 WHERE a IN(SELECT MIN(a)FROM t1)", packet_length=49) at /home/elenst/bzr/5.5/sql/sql_parse.cc:1079
|
#21 0x0000000000631795 in do_command (thd=0x7f68c9950060) at /home/elenst/bzr/5.5/sql/sql_parse.cc:793
|
#22 0x00000000007338f2 in do_handle_one_connection (thd_arg=0x7f68c9950060) at /home/elenst/bzr/5.5/sql/sql_connect.cc:1266
|
#23 0x00000000007333b1 in handle_one_connection (arg=0x7f68c9950060) at /home/elenst/bzr/5.5/sql/sql_connect.cc:1181
|
#24 0x0000000000b6b28d in pfs_spawn_thread (arg=0x7f68c9971ca0) at /home/elenst/bzr/5.5/storage/perfschema/pfs.cc:1015
|
#25 0x00007f68cfd6ab50 in start_thread (arg=<optimized out>) at pthread_create.c:304
|
#26 0x00007f68ce02120d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:112
|
|
Test case |
SET SESSION big_tables=1; |
CREATE TABLE t1(a char(255)DEFAULT'',KEY(a(10))) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; |
INSERT INTO t1 VALUES(0),(0),(0); |
SELECT * FROM t1 WHERE a IN(SELECT MIN(a)FROM t1); |
|
Stack trace from 5.5 |
revision-id: sanja@askmonty.org-20141115211833-waisauprym8dbgzi
|
date: 2014-11-15 22:18:33 +0100
|
build-date: 2014-11-17 16:09:29 +0400
|
revno: 4350
|
branch-nick: 5.5
|
Attachments
Issue Links
Activity
| Field | Original Value | New Value |
|---|---|---|
| Remote Link | This issue links to "Bug #74889 - Sig-6 abort in Item_in_subselect::exec | sql/item_subselect.cc:730 (Web Link)" [ 21500 ] |
| Workflow | MariaDB v2 [ 58604 ] | MariaDB v3 [ 64970 ] |
| Assignee | Sergei Petrunia [ psergey ] | Vicentiu Ciorbaru [ cvicentiu ] |
| Sprint | 5.5.47-1 [ 22 ] |
| Status | Open [ 1 ] | In Progress [ 3 ] |
| Rank | Ranked higher |
So, it is trying to initialize the query plan for non-merged semi-join materialization strategy (aka JTBM=Join TaB Materialization).
And the assert is here:
if (tmp_table->s->keys == 0)
|
{
|
That is, we assert when we have created a temporary table, but it turned out that the table has no keys (=> and hence it is not suitable for doing Materialization, without keys one can't remove duplicates or make efficient lookups).
Note that without SET SESSION big_tables=1 the query works, and its EXPLAIN is:
MariaDB [test]> explain SELECT * FROM t1 WHERE a IN(SELECT MIN(a)FROM t1);
|
+------+--------------+-------------+------+---------------+------+---------+------+------+-------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+--------------+-------------+------+---------------+------+---------+------+------+-------------------------------------------------+
|
| 1 | PRIMARY | <subquery2> | ALL | distinct_key | NULL | NULL | NULL | 1 | |
|
| 1 | PRIMARY | t1 | ALL | a | NULL | NULL | NULL | 3 | Using where; Using join buffer (flat, BNL join) |
|
| 2 | MATERIALIZED | t1 | ALL | NULL | NULL | NULL | NULL | 3 | |
|
+------+--------------+-------------+------+---------------+------+---------+------+------+-------------------------------------------------+
|
Debugging create_tmp_table().
without big_tables=1
- it creates a table with table->file= (ha_heap)
- table->s->keys==1
with big_tables=1:
The execution starts to differ here:
/* If result table is small; use a heap */
|
/* future: storage engine selection can be made dynamic? */
|
if (blob_count || using_unique_constraint
|
|| (thd->variables.big_tables && !(select_options & SELECT_SMALL_RESULT))
|
|| (select_options & TMP_TABLE_FORCE_MYISAM)
|
|| thd->variables.tmp_table_size == 0)
|
{
|
share->db_plugin= ha_lock_engine(0, TMP_ENGINE_HTON);
|
table->file= get_new_handler(share, &table->mem_root,
|
share->db_type());
|
table->file is a ha_maria object.
The execution proceeds in the same way as in the working case, until we reach
this place:
if (!do_not_open)
|
{
|
if (share->db_type() == TMP_ENGINE_HTON)
|
{
|
if (create_internal_tmp_table(table, param->keyinfo, param->start_recinfo,
|
¶m->recinfo, select_options))
|
goto err;
|
}
|
if (open_tmp_table(table))
|
goto err;
|
}
|
create_internal_tmp_table() is only called when table->file is ha_maria object.
Inside, we get here:
if (keyinfo->key_length > table->file->max_key_length() ||
|
keyinfo->user_defined_key_parts > table->file->max_key_parts() ||
|
share->uniques)
|
{
|
if (!share->uniques && !(keyinfo->flags & HA_NOSAME))
|
{
|
my_error(ER_INTERNAL_ERROR, MYF(0),
|
"Using too big key for internal temp tables");
|
DBUG_RETURN(1);
|
}
|
|
|
/* Can't create a key; Make a unique constraint instead of a key */
|
share->keys= 0;
|
The outermost if is taken, the innermost is not. We can see: it is unable to create a key and falls back to using a "constraint" instead.
The reason it is unable to create a key:
(gdb) p keyinfo->key_length
|
$42 = 1020
|
(gdb) p keyinfo->user_defined_key_parts
|
$43 = 1
|
(gdb) p table->file->max_key_length()
|
$44 = 1000
|
(gdb) p table->file->max_key_parts()
|
$45 = 32
|
This is a long-known problem:
- we can use Materialization only when we're able to create a temp. table with an appropriate key.
- the code that determines the condition for this is buried in multiple inside create_tmp_table().
- Hence, when deciding whether to use Materialization, we need to copy the check. Apparently it was done incorrectly.
Searching through OPTIMIZER_SWITCH_MATERIALIZATION finds the function that
makes the check: subquery_types_allow_materialization(). That function should also make an equivalent of the check that is made in create_internal_tmp_table:
if (keyinfo->key_length > table->file->max_key_length() ||
|
keyinfo->user_defined_key_parts > table->file->max_key_parts() ||
|
| Sprint | 5.5.47-1 [ 22 ] | 5.5.47-1, 5.5.48-0 [ 22, 33 ] |
| Rank | Ranked higher |
Hi Sergei,
Can you please review the patch for this bug? The patch is on the commits mailing list:
http://lists.askmonty.org/pipermail/commits/2016-February/008975.html
Regards,
Vicentiu
| Assignee | Vicentiu Ciorbaru [ cvicentiu ] | Sergei Petrunia [ psergey ] |
| Status | In Progress [ 3 ] | In Review [ 10002 ] |
Checking limits in storage engines:
#define MAX_REF_PARTS 32 /* Max parts used as ref */
|
#define MAX_KEY_LENGTH 3072 /* max possible key */
|
(gdb) p join_tab[0].table->file
|
$8 = (ha_maria *) 0x7fff7001c0b8
|
(gdb) p join_tab[0].table->file->max_key_length()
|
$9 = 1000
|
(gdb) p join_tab[0].table->file->max_key_parts()
|
$10 = 32
|
(gdb) p join_tab[0].table->file
|
$11 = (ha_myisam *) 0x7fff700351f8
|
(gdb) p join_tab[0].table->file->max_key_length()
|
$12 = 1000
|
(gdb) p join_tab[0].table->file->max_key_parts()
|
$13 = 32
|
(gdb) p join_tab[0].table->file
|
$16 = (ha_heap *) 0x7fff7003ea18
|
(gdb) p join_tab[0].table->file->max_key_length()
|
$17 = 3072
|
(gdb) p join_tab[0].table->file->max_key_parts()
|
$18 = 32
|
Looking at the above:
- table->file->max_key_parts() == MAX_REF_PARTS for all engines
- table->file->max_key_length() < MAX_KEY_LENGTH for some engines.
Discussed the issue with serg. Conclusions:
<serg> and when a temp table is created, add an assert that h->max_key_parts() >= MAX_REF_PARTS
for MAX_KEY_LENGTH, we need to #define MAX_TMP_TABLE_KEY_LENGTH, and then
- add assert(s) that created temp. tables have table->file->max_key_length() >= MAX_TMP_TABLE_KEY_LENGTH.
- subquery_types_allow_materialization() should then not chose materialziation if the expected key length exceeds MAX_TMP_TABLE_KEY_LENGTH.
cvicentiu any objections? If not pelase implement this.
| Assignee | Sergei Petrunia [ psergey ] | Vicentiu Ciorbaru [ cvicentiu ] |
| Status | In Review [ 10002 ] | Stalled [ 10000 ] |
Due to differences in storage engines, we ended up implementing an #ifdef'ed solution. Based on the temporary table storage engine, a new function called tmp_table_max_key_
{length|parts}() returns the required values.
Fixed with:
https://github.com/MariaDB/server/commit/775cccca9f1502ae2f4cf1417d0f94d4872d4630
https://github.com/MariaDB/server/commit/3c5c04bd2bccbfeb62a86bdc5610b1dcea378dd3
| Fix Version/s | 5.5.48 [ 21000 ] | |
| Fix Version/s | 10.0 [ 16000 ] | |
| Fix Version/s | 5.5 [ 15800 ] | |
| Resolution | Fixed [ 1 ] | |
| Status | Stalled [ 10000 ] | Closed [ 6 ] |
| Workflow | MariaDB v3 [ 64970 ] | MariaDB v4 [ 148478 ] |
I had a look and couldn't see anything buts its probably here somewhere https://github.com/mysql/mysql-server/compare/mysql-5.5.21...mysql-5.5.22