I have removed the InnoDB variation of the test case, as it turned out it had nothing to do with this bug (or virtual columns in general). It was added to MDEV-20252 instead.
Here is a much simpler test case which produces the same assertion failure with all of MyISAM, Aria, InnoDB.
CREATE TABLE t1 (a BIT(8), b BIT(8) AS (a) STORED); |
CREATE VIEW v1 AS SELECT * FROM t1; |
INSERT IGNORE INTO t1 (a) VALUES (b'0'),(b'1'); |
DELETE FROM v1 ORDER BY b LIMIT 1; |
|
|
# Cleanup
|
DROP VIEW v1; |
DROP TABLE t1; |
|
10.4 71c57bcf |
mysqld: /data/src/10.4/sql/field.cc:9894: virtual longlong Field_bit::val_int(): Assertion `marked_for_read()' failed.
|
190911 23:55:48 [ERROR] mysqld got signal 6 ;
|
|
|
#7 0x00007faec1ac2f12 in __GI___assert_fail (assertion=0x55abd1bb9e80 "marked_for_read()", file=0x55abd1bb9c74 "/data/src/10.4/sql/field.cc", line=9894, function=0x55abd1bbf600 <Field_bit::val_int()::__PRETTY_FUNCTION__> "virtual longlong Field_bit::val_int()") at assert.c:101
|
#8 0x000055abd10510f3 in Field_bit::val_int (this=0x7faea013a178) at /data/src/10.4/sql/field.cc:9894
|
#9 0x000055abd105e432 in Field_bit::save_in_field (this=0x7faea013a178, to=0x7faea013a268) at /data/src/10.4/sql/field.h:4408
|
#10 0x000055abd105833e in Field::store_field (this=0x7faea013a268, from=0x7faea013a178) at /data/src/10.4/sql/field.h:774
|
#11 0x000055abd1062827 in field_conv_incompatible (to=0x7faea013a268, from=0x7faea013a178) at /data/src/10.4/sql/field_conv.cc:851
|
#12 0x000055abd1062883 in field_conv (to=0x7faea013a268, from=0x7faea013a178) at /data/src/10.4/sql/field_conv.cc:862
|
#13 0x000055abd109fe7d in save_field_in_field (from=0x7faea013a178, null_value=0x7faea013a61e, to=0x7faea013a268, no_conversions=false) at /data/src/10.4/sql/item.cc:6395
|
#14 0x000055abd10a00be in Item_field::save_in_field (this=0x7faea013a5a8, to=0x7faea013a268, no_conversions=false) at /data/src/10.4/sql/item.cc:6446
|
#15 0x000055abd0e47a9c in TABLE::update_virtual_fields (this=0x7faea01392e0, h=0x7faea012bf28, update_mode=VCOL_UPDATE_FOR_DELETE) at /data/src/10.4/sql/table.cc:8255
|
#16 0x000055abd123cfd6 in record_should_be_deleted (thd=0x7faea0000b00, table=0x7faea01392e0, sel=0x0, explain=0x7faea0017750, truncate_history=false) at /data/src/10.4/sql/sql_delete.cc:239
|
#17 0x000055abd123f38e in mysql_delete (thd=0x7faea0000b00, table_list=0x7faea0013208, conds=0x0, order_list=0x7faea00054b0, limit=1, options=0, result=0x0) at /data/src/10.4/sql/sql_delete.cc:807
|
#18 0x000055abd0d10218 in mysql_execute_command (thd=0x7faea0000b00) at /data/src/10.4/sql/sql_parse.cc:4728
|
#19 0x000055abd0d1bb52 in mysql_parse (thd=0x7faea0000b00, rawbuf=0x7faea0013118 "DELETE FROM v1 ORDER BY b LIMIT 1", length=33, parser_state=0x7faebc495170, is_com_multi=false, is_next_command=false) at /data/src/10.4/sql/sql_parse.cc:7909
|
#20 0x000055abd0d06b52 in dispatch_command (command=COM_QUERY, thd=0x7faea0000b00, packet=0x7faea0008321 "DELETE FROM v1 ORDER BY b LIMIT 1", packet_length=33, is_com_multi=false, is_next_command=false) at /data/src/10.4/sql/sql_parse.cc:1843
|
#21 0x000055abd0d0519c in do_command (thd=0x7faea0000b00) at /data/src/10.4/sql/sql_parse.cc:1360
|
#22 0x000055abd0e8ef01 in do_handle_one_connection (connect=0x55abd4c3b010) at /data/src/10.4/sql/sql_connect.cc:1412
|
#23 0x000055abd0e8ec50 in handle_one_connection (arg=0x55abd4c3b010) at /data/src/10.4/sql/sql_connect.cc:1316
|
#24 0x000055abd18c64cd in pfs_spawn_thread (arg=0x55abd4c70420) at /data/src/10.4/storage/perfschema/pfs.cc:1862
|
#25 0x00007faec36374a4 in start_thread (arg=0x7faebc496700) at pthread_create.c:456
|
#26 0x00007faec1b7fd0f in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:97
|
The test from description currently does not fail (10.5 48ab5a4997b073f, 10.4 571764c04fc9b736366 ), but the test from the above comment fails on 10.4, 10.5
The latter test case (with DELETE LIMIT 1) duplicates MDEV-18166
The reason the initial test case does not fail is optimizer behavior change present by
4d4b2867a MDEV-20056 Assertion `!prebuilt->index->is_primary()' failed
in row_search_idx_cond_check
--- a/sql/sql_select.cc
|
+++ b/sql/sql_select.cc
|
@@ -7701,7 +7707,8 @@ best_access_path(JOIN *join,
|
found_ref);
|
} /* not ft_key */
|
|
- if (records < DBL_MAX)
|
+ if (records < DBL_MAX &&
|
+ (found_part & 1)) // start_key->key can be used for index access
|
{
|
double rows= record_count * records;
|
double access_cost_factor= MY_MIN(tmp / rows, 1.0);
|
Reverting this code hunk makes the issue reproducible
Night is spent on trying to expose the fact that table->prepare_for_keyread sets the bits for a virtual field, if it was used, but not for the fields inside the vcol expression.
In JOIN::init_join_caches():
if ((tab->read_first_record == join_read_first || |
tab->read_first_record == join_read_last) &&
|
!tab->filesort && table->covering_keys.is_set(tab->index) &&
|
!table->no_keyread)
|
{
|
table->prepare_for_keyread(tab->index, table->read_set);
|
}
|
tab->read_first_record can be join_read_first when the key is a covering key, i.e. includes all the fields involved in select.
In make_join_readinfo():
if (!table->covering_keys.is_clear_all() &&
|
!(tab->select && tab->select->quick))
|
...
|
tab->read_first_record= join_read_first
|
is the only place which seems to be possibly reachable.
However, covering_keys are intersected with hinting keys.
Using asterisk (SELECT *) is pointless, since it will then load the record anyway, and mark a read bit.
Using USE/FORCE INDEX [FOR ORDER BY] did not provide any success.
test_if_skip_sort_order is left unreachable even under ~50k rows test.
The reason the initial test case does not fail is optimizer behavior change present by 4d4b2867a
MDEV-20056
I don't understand the point you make by closing it as "Can't reproduce" based on the above.
Are you saying that the patch for MDEV-20056 actually fixed the bug which was affecting virtual columns, or did it just make the test case irrelevant?
If you can confirm that MDEV-20056 has fixed the problem described here, then it should be closed as a duplicate or as fixed, whichever you prefer.
But if it just made the test irrelevant (hid the failure described here), closing it as "can't reproduce" is meaningless. Of course you can reproduce it, just take the revision before MDEV-20056 fix, when the test case was still relevant, and use it for debugging.
So the index was chosen correctly:
(gdb) bt
|
#0 0x000055c6327ac462 in bitmap_set_bit (map=0x7f1ee403bbf0, bit=2) at ../include/my_bitmap.h:116
|
#1 0x000055c6327abfb7 in TABLE::mark_columns_used_by_index_no_reset (this=0x7f1ee403baa8, index=2, bitmap=0x7f1ee403bbf0) at ../sql/table.cc:6939
|
#2 0x000055c6327ac23c in TABLE::mark_columns_used_by_index (this=0x7f1ee403baa8, index=2, bitmap=0x7f1ee403bbf0) at ../sql/table.cc:6905
|
#3 0x000055c6327ac141 in TABLE::prepare_for_keyread (this=0x7f1ee403baa8, index=2, map=0x7f1ee403bbf0) at ../sql/table.cc:6889
|
#4 0x000055c63269075d in JOIN::init_join_caches (this=0x7f1ee40516f8) at ../sql/sql_select.cc:1817
|
#5 0x000055c63268d041 in JOIN::optimize_stage2 (this=0x7f1ee40516f8) at ../sql/sql_select.cc:3052
|
#6 0x000055c63268f9dc in JOIN::optimize_inner (this=0x7f1ee40516f8) at ../sql/sql_select.cc:2335
|
#7 0x000055c63268a038 in JOIN::optimize (this=0x7f1ee40516f8) at ../sql/sql_select.cc:1658
|
#8 0x000055c632680ebf in mysql_select (thd=0x7f1ee4000d28, tables=0x7f1ee4013cb8, wild_num=1, fields=..., conds=0x7f1ee40158c0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7f1ee40516d0, unit=0x7f1ee4004ad0, select_lex=0x7f1ee40136d8) at ../sql/sql_select.cc:4725
|
#9 0x000055c6326805ad in handle_select (thd=0x7f1ee4000d28, lex=0x7f1ee4004a10, result=0x7f1ee40516d0, setup_tables_done_option=0) at ../sql/sql_select.cc:436
|
....
|
(gdb) f 4
|
#4 0x000055c63269075d in JOIN::init_join_caches (this=0x7f1ee40516f8) at ../sql/sql_select.cc:1817
|
1817 table->prepare_for_keyread(tab->index, table->read_set);
|
(gdb) p tab->index
|
$1 = 2
|
|
Keyread is enabled. However, we can see from the description stack trace that rr_sequential was used, which is incorrect due to keyread.
First, in best_access_path rowid filter was assigned:
filter=
|
table->best_range_rowid_filter_for_partial_join(start_key->key, rows,
|
access_cost_factor);
|
This is what was changed by the patch.
Then keyread was enabled in JOIN::init_join_caches, like in the trace above . Then it was reenabled by rowid filter in fill():
#0 handler::ha_start_keyread (this=0x7f1ee403c8a0, idx=0) at ../sql/handler.h:3299
|
#1 0x000055c6328ef141 in Range_rowid_filter::fill (this=0x7f1ee40539c0) at ../sql/rowid_filter.cc:570
|
#2 0x000055c6328ef8a5 in Range_rowid_filter::build (this=0x7f1ee40539c0) at ../sql/rowid_filter.h:267
|
#3 0x000055c6326c0dee in st_join_table::build_range_rowid_filter_if_needed (this=0x7f1ee4057fc0) at ../sql/sql_select.cc:13380
|
#4 0x000055c6328522ee in JOIN_CACHE::join_matching_records (this=0x7f1ee405c1f8, skip_last=false) at ../sql/sql_join_cache.cc:2320
|
#5 0x000055c632851c7b in JOIN_CACHE::join_records (this=0x7f1ee405c1f8, skip_last=false) at ../sql/sql_join_cache.cc:2147
|
Then it was disabled, also there
and wasnt reenabled back.
So after that, in init_read_record, if (table->file->keyread_enabled()) path is not chosen,
and
|
|
info->read_record_func= rr_sequential;
{cpp}is evaluated
psergei can you tell if there are other ways to create rowid filter together with keyread enabled? My point is this combination exposes the bug.
After 4d4b2867a fix mentioned above, the latter code path is no longer accessible since && (found_part & 1) is added which means there should be a primary key. But then b will be in ext key parts and will be marked for read. (However I guess, rr_sequential will still be chosen, and keyread can be wrongly finished)
So we can't at least add PK to the table. It is better to leave the test case as is. I wonder if the issue can be reproduced with carefully configured optimizer switch
The original test case in the description required rowid_filter, and that failure disappeared after this commit (released in 10.4.11):
commit 4d4b2867a2526872a7b476a5c3577be695800a8e (HEAD)
|
Author: Igor Babaev
|
Date: Mon Nov 25 17:40:47 2019 -0800
|
|
|
MDEV-20056 Assertion `!prebuilt->index->is_primary()' failed
|
in row_search_idx_cond_check
|
And the alternative test case in the comments was fixed by MDEV-18166.
So, since we don't have remaining failing test cases here, I will close it.
Unfortunately the failure itself still happens a lot, we'll just need another bug report for it.