Details
Major Description
CREATE TABLE t1 (f1 INT) ENGINE=MyISAM; |
INSERT INTO t1 VALUES (1),(2); |
|
|
CREATE TABLE t2 (f2 INT, KEY(f2)) ENGINE=MyISAM; |
INSERT INTO t2 VALUES (3); |
|
|
CREATE ALGORITHM=MERGE VIEW v2 AS SELECT * FROM t2; |
|
|
SELECT ( SELECT MIN(v2.f2) FROM t1 ) AS sq FROM v2 GROUP BY sq; |
|
Stack trace from 10.0 commit e9c10f99 |
10.0/sql/handler.cc:2597: int handler::ha_index_read_map(uchar*, const uchar*, key_part_map, ha_rkey_function): Assertion `table_share->tmp_table != NO_TMP_TABLE || m_lock_type != 2' failed.
|
|
|
#6 0x00007f4a316d2311 in *__GI___assert_fail (assertion=0xfdaad8 "table_share->tmp_table != NO_TMP_TABLE || m_lock_type != 2", file=<optimized out>, line=2597, function=0xfdd440 "int handler::ha_index_read_map(uchar*, const uchar*, key_part_map, ha_rkey_function)") at assert.c:81
|
#7 0x0000000000870175 in handler::ha_index_read_map (this=0x7f4a2987f088, buf=0x7f4a29827f88 "\375\003", key=0x7f4a338d94a0 "\001", keypart_map=1, find_flag=HA_READ_AFTER_KEY) at 10.0/sql/handler.cc:2596
|
#8 0x000000000099aa1d in get_index_min_value (table=0x7f4a2989f070, ref=0x7f4a338d9430, item_field=0x7f4a299f6478, range_fl=6, prefix_len=0) at 10.0/sql/opt_sum.cc:157
|
#9 0x000000000099b1c2 in opt_sum_query (thd=0x7f4a2c32f070, tables=..., all_fields=..., conds=0x0) at 10.0/sql/opt_sum.cc:401
|
#10 0x00000000006b2611 in JOIN::optimize_inner (this=0x7f4a299f9030) at 10.0/sql/sql_select.cc:1282
|
#11 0x00000000006b18be in JOIN::optimize (this=0x7f4a299f9030) at 10.0/sql/sql_select.cc:1024
|
#12 0x00000000006715df in st_select_lex::optimize_unflattened_subqueries (this=0x7f4a2c333478, const_only=false) at 10.0/sql/sql_lex.cc:3490
|
#13 0x00000000007e748a in JOIN::optimize_unflattened_subqueries (this=0x7f4a299f85c0) at 10.0/sql/opt_subselect.cc:4948
|
#14 0x00000000006b4330 in JOIN::optimize_inner (this=0x7f4a299f85c0) at 10.0/sql/sql_select.cc:1784
|
#15 0x00000000006b18be in JOIN::optimize (this=0x7f4a299f85c0) at 10.0/sql/sql_select.cc:1024
|
#16 0x00000000006b946d in mysql_select (thd=0x7f4a2c32f070, rref_pointer_array=0x7f4a2c3336f0, tables=0x7f4a298e1668, wild_num=0, fields=..., conds=0x0, og_num=1, order=0x0, group=0x7f4a298e1d68, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7f4a299f6b90, unit=0x7f4a2c332d88, select_lex=0x7f4a2c333478) at 10.0/sql/sql_select.cc:3296
|
#17 0x00000000006afacd in handle_select (thd=0x7f4a2c32f070, lex=0x7f4a2c332cc0, result=0x7f4a299f6b90, setup_tables_done_option=0) at 10.0/sql/sql_select.cc:373
|
#18 0x000000000068428d in execute_sqlcom_select (thd=0x7f4a2c32f070, all_tables=0x7f4a298e1668) at 10.0/sql/sql_parse.cc:5262
|
#19 0x000000000067c5be in mysql_execute_command (thd=0x7f4a2c32f070) at 10.0/sql/sql_parse.cc:2550
|
#20 0x0000000000686de5 in mysql_parse (thd=0x7f4a2c32f070, rawbuf=0x7f4a298e0088 "SELECT ( SELECT MIN(v2.f2) FROM t1 ) AS sq FROM v2 GROUP BY sq", length=62, parser_state=0x7f4a338db600) at 10.0/sql/sql_parse.cc:6517
|
#21 0x0000000000679811 in dispatch_command (command=COM_QUERY, thd=0x7f4a2c32f070, packet=0x7f4a2b7eb071 "SELECT ( SELECT MIN(v2.f2) FROM t1 ) AS sq FROM v2 GROUP BY sq", packet_length=62) at 10.0/sql/sql_parse.cc:1300
|
#22 0x0000000000678ba7 in do_command (thd=0x7f4a2c32f070) at 10.0/sql/sql_parse.cc:996
|
#23 0x00000000007963b7 in do_handle_one_connection (thd_arg=0x7f4a2c32f070) at 10.0/sql/sql_connect.cc:1375
|
#24 0x000000000079610a in handle_one_connection (arg=0x7f4a2c32f070) at 10.0/sql/sql_connect.cc:1289
|
#25 0x0000000000cd365e in pfs_spawn_thread (arg=0x7f4a2b76ce70) at 10.0/storage/perfschema/pfs.cc:1860
|
#26 0x00007f4a334ccb50 in start_thread (arg=<optimized out>) at pthread_create.c:304
|
#27 0x00007f4a3178295d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:112
|
See also https://bugs.mysql.com/bug.php?id=74448 – different test case, same assertion failure. And the test case above fails on 5.6 too.
There is probably no much point waiting for the upstream fix since we don't merge them automatically anyway.
Attachments
Issue Links
Activity
revision-id: 77b075f69e0e45baaf145efd90d79be34d78bde9 (mariadb-10.0.21-44-g77b075f)
parent(s): 18f7dfed179204dcfc02a27790e22bb9cc4e2e32
committer: Oleksandr Byelkin
timestamp: 2015-10-22 13:57:44 +0200
message:
MDEV-7930 Assertion `table_share->tmp_table != NO_TMP_TABLE || m_lock_type != 2' failed in handler::ha_index_read_map
Processing of constant tables added to max/min optimization.
—
SELECT ( SELECT MIN(v2.f2) FROM t1 ) AS sq FROM v2 GROUP BY sq;
|
Note that "v2" is the table from top-level select. Optimization proceeds as follows:
1. optimize the top-level query.
1.1 find "v2" to be a const table (and release the lock on it)
2. optimize the subquery. Call opt_sum_query. Here, we crash, because the table was already unlocked on step 1.1
Ok the fix removes the crash.
But the question is, what is it we are trying to calculate here? What is the meaning of MIN(v2.f2) in the subquery? Does it mean "minimum of v2.f2" within the group as defined by upper query's GROUP BY clause" ? However, GROUP BY clause in the upper query is the subquery itself.
revision-id: 9eff48713d5eada610138e415a047d418e33e580 (mariadb-10.0.21-44-g9eff487)
parent(s): 18f7dfed179204dcfc02a27790e22bb9cc4e2e32
committer: Oleksandr Byelkin
timestamp: 2015-10-26 11:54:58 +0100
message:
MDEV-7930 Assertion `table_share->tmp_table != NO_TMP_TABLE || m_lock_type != 2' failed in handler::ha_index_read_map
Processing of constant tables added to max/min optimization.
—
revision-id: 72bb358c2ba98e6e0d4a15b92af514a45751fe8d (mariadb-10.0.21-44-g72bb358)
parent(s): 18f7dfed179204dcfc02a27790e22bb9cc4e2e32
committer: Oleksandr Byelkin
timestamp: 2015-10-26 16:59:40 +0100
message:
MDEV-7930 Assertion `table_share->tmp_table != NO_TMP_TABLE || m_lock_type != 2' failed in handler::ha_index_read_map
In optimizing aggregate function do not try to touch tables from outer
query.
—
the t2 unlocked by mysql_unlock_some_tables() in JOIN::optimize() (WHY???). Naturally it can't be used anymore...