Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
5.3.12, 5.5.38, 10.0.12
-
None
-
10.2.5-1
Description
mysqld: 5.5/sql/field.cc:7203: virtual String* Field_blob::val_str(String*, String*): Assertion `!table || (!table->read_set || bitmap_is_set(table->read_set, field_index))' failed.
|
140725 11:48:26 [ERROR] mysqld got signal 6 ;
|
#6 0x00007f954dd8a621 in *__GI___assert_fail (assertion=0xe2c4d0 "!table || (!table->read_set || bitmap_is_set(table->read_set, field_index))", file=<optimized out>, line=7203, function=0xe2e5e0 "virtual String* Field_blob::val_str(String*, String*)") at assert.c:81
|
#7 0x00000000007ce43f in Field_blob::val_str (this=0x7f9548859a68, val_buffer=0x7f95493b3330, val_ptr=0x7f95493b3330) at 5.5/sql/field.cc:7203
|
#8 0x000000000059a485 in Field::val_str (this=0x7f9548859a68, str=0x7f95493b3330) at 5.5/sql/field.h:277
|
#9 0x00000000007dbfad in do_field_string (copy=0x7f95489818b8) at 5.5/sql/field_conv.cc:369
|
#10 0x00000000007dba40 in do_copy_null (copy=0x7f95489818b8) at 5.5/sql/field_conv.cc:242
|
#11 0x000000000069fd7e in store_key_field::copy_inner (this=0x7f9548981890) at 5.5/sql/sql_select.h:1613
|
#12 0x000000000069fb9b in store_key::copy (this=0x7f9548981890) at 5.5/sql/sql_select.h:1554
|
#13 0x00000000006920be in cp_buffer_from_ref (thd=0x7f954c749060, table=0x7f95489d4860, ref=0x7f9548981710) at 5.5/sql/sql_select.cc:20319
|
#14 0x000000000068b357 in join_read_always_key (tab=0x7f9548981500) at 5.5/sql/sql_select.cc:17576
|
#15 0x00000000006899bb in sub_select (join=0x7f9548992eb0, join_tab=0x7f9548981500, end_of_records=false) at 5.5/sql/sql_select.cc:16836
|
#16 0x000000000068927b in do_select (join=0x7f9548992eb0, fields=0x7f9548993270, table=0x0, procedure=0x0) at 5.5/sql/sql_select.cc:16501
|
#17 0x00000000006681af in JOIN::exec (this=0x7f9548992eb0) at 5.5/sql/sql_select.cc:2873
|
#18 0x000000000087ec1f in subselect_single_select_engine::exec (this=0x7f95489498d8) at 5.5/sql/item_subselect.cc:3231
|
#19 0x00000000008778b2 in Item_subselect::exec (this=0x7f95489497a0) at 5.5/sql/item_subselect.cc:655
|
#20 0x0000000000879004 in Item_singlerow_subselect::val_int (this=0x7f95489497a0) at 5.5/sql/item_subselect.cc:1232
|
#21 0x000000000059bb91 in Item::val_int_result (this=0x7f95489497a0) at 5.5/sql/item.h:977
|
#22 0x0000000000808cf3 in Item_cache_int::cache_value (this=0x7f9548981f28) at 5.5/sql/item.cc:8854
|
#23 0x0000000000810232 in Item_cache_wrapper::cache (this=0x7f9548981e70) at 5.5/sql/item.cc:7658
|
#24 0x0000000000805a7f in Item_cache_wrapper::val_int (this=0x7f9548981e70) at 5.5/sql/item.cc:7712
|
#25 0x000000000059bb91 in Item::val_int_result (this=0x7f9548981e70) at 5.5/sql/item.h:977
|
#26 0x00000000007e0bd4 in make_sortkey (param=0x7f95493b40d0, to=0x7f954880e3c0 "\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245h4z\025\245\245\245\245\245\245\245\245ZZZZZZZZ"..., ref_pos=0x7f9548859c00 "") at 5.5/sql/filesort.cc:969
|
#27 0x00000000007e0278 in find_all_keys (param=0x7f95493b40d0, select=0x7f9548980968, sort_keys=0x7f954880e360, sort_keys_buf=0x7f954880e3c0 "\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245h4z\025\245\245\245\245\245\245\245\245ZZZZZZZZ"..., buffpek_pointers=0x7f95493b42e0, tempfile=0x7f95493b4150) at 5.5/sql/filesort.cc:748
|
#28 0x00000000007deaec in filesort (thd=0x7f954c749060, table=0x7f95489d9860, sortorder=0x7f95489b56b8, s_length=1, select=0x7f9548980968, max_rows=18446744073709551615, sort_positions=false, examined_rows=0x7f95493b4578) at 5.5/sql/filesort.cc:241
|
#29 0x0000000000690d23 in create_sort_index (thd=0x7f954c749060, join=0x7f9548992740, order=0x7f9548992640, filesort_limit=18446744073709551615, select_limit=18446744073709551615, is_order_by=true) at 5.5/sql/sql_select.cc:19785
|
#30 0x0000000000667f9e in JOIN::exec (this=0x7f9548992740) at 5.5/sql/sql_select.cc:2843
|
#31 0x00000000006689b9 in mysql_select (thd=0x7f954c749060, rref_pointer_array=0x7f954c74ccc8, tables=0x7f9548992078, wild_num=0, fields=..., conds=0x7f9548949df0, og_num=1, order=0x0, group=0x7f9548992640, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7f9548992720, unit=0x7f954c74c380, select_lex=0x7f954c74ca58) at 5.5/sql/sql_select.cc:3094
|
#32 0x000000000065f489 in handle_select (thd=0x7f954c749060, lex=0x7f954c74c2d0, result=0x7f9548992720, setup_tables_done_option=0) at 5.5/sql/sql_select.cc:319
|
#33 0x0000000000638a9f in execute_sqlcom_select (thd=0x7f954c749060, all_tables=0x7f9548992078) at 5.5/sql/sql_parse.cc:4688
|
#34 0x0000000000631c81 in mysql_execute_command (thd=0x7f954c749060) at 5.5/sql/sql_parse.cc:2233
|
#35 0x000000000063b1ae in mysql_parse (thd=0x7f954c749060, rawbuf=0x7f9548948078 "SELECT ( SELECT COUNT(*) FROM t1 WHERE a = c ) AS field, COUNT( DISTINCT c ) FROM t2 WHERE b <= 'quux' GROUP BY field", length=117, parser_state=0x7f95493b5630) at 5.5/sql/sql_parse.cc:5799
|
#36 0x000000000062f1f9 in dispatch_command (command=COM_QUERY, thd=0x7f954c749060, packet=0x7f9549afa061 "SELECT ( SELECT COUNT(*) FROM t1 WHERE a = c ) AS field, COUNT( DISTINCT c ) FROM t2 WHERE b <= 'quux' GROUP BY field", packet_length=117) at 5.5/sql/sql_parse.cc:1079
|
#37 0x000000000062e385 in do_command (thd=0x7f954c749060) at 5.5/sql/sql_parse.cc:793
|
#38 0x000000000072ffea in do_handle_one_connection (thd_arg=0x7f954c749060) at 5.5/sql/sql_connect.cc:1266
|
#39 0x000000000072faa9 in handle_one_connection (arg=0x7f954c749060) at 5.5/sql/sql_connect.cc:1181
|
#40 0x0000000000b64f09 in pfs_spawn_thread (arg=0x7f954a7fa8e0) at 5.5/storage/perfschema/pfs.cc:1015
|
#41 0x00007f954f2eab50 in start_thread (arg=<optimized out>) at pthread_create.c:304
|
#42 0x00007f954de39a7d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:112
|
Stack trace from
revision-id: jplindst@mariadb.org-20140725063405-65apg29t9bnlif5w
|
revno: 4238
|
branch-nick: 5.5
|
Test case:
CREATE TABLE t1 (a VARCHAR(8), KEY(a)) ENGINE=MyISAM; |
INSERT INTO t1 VALUES ('foo'),( 'bar'); |
|
|
CREATE TABLE t2 (b VARCHAR(8), c TINYTEXT, KEY(b)) ENGINE=MyISAM; |
INSERT INTO t2 VALUES ('baz','baz'),('qux', 'qux'); |
|
|
EXPLAIN EXTENDED
|
SELECT ( SELECT COUNT(*) FROM t1 WHERE a = c ) AS field, COUNT( DISTINCT c ) FROM t2 WHERE b <= 'quux' GROUP BY field; |
|
|
SELECT ( SELECT COUNT(*) FROM t1 WHERE a = c ) AS field, COUNT( DISTINCT c ) FROM t2 WHERE b <= 'quux' GROUP BY field; |
EXPLAIN:
id select_type table type possible_keys key key_len ref rows filtered Extra
|
1 PRIMARY t2 range b b 11 NULL 1 100.00 Using index condition; Using filesort
|
2 DEPENDENT SUBQUERY t1 ref a a 11 test.t2.c 2 100.00 Using where; Using index
|
Warnings:
|
Note 1276 Field or reference 'test.t2.c' of SELECT #2 was resolved in SELECT #1
|
Note 1003 select <expr_cache><`test`.`t2`.`c`>((select count(0) from `test`.`t1` where (`test`.`t1`.`a` = `test`.`t2`.`c`))) AS `field`,count(distinct `test`.`t2`.`c`) AS `COUNT( DISTINCT c )` from `test`.`t2` where (`test`.`t2`.`b` <= 'quux') group by <expr_cache><`test`.`t2`.`c`>((select count(0) from `test`.`t1` where (`test`.`t1`.`a` = `test`.`t2`.`c`)))
|
Attachments
Issue Links
- relates to
-
-
- Closed
-
Activity
| Field | Original Value | New Value |
|---|---|---|
| Workflow | MariaDB v2 [ 50801 ] | MariaDB v3 [ 63616 ] |
| Link |
This issue relates to |
| Assignee | Sergei Petrunia [ psergey ] | Varun Gupta [ varun ] |
| Status | Open [ 1 ] | In Progress [ 3 ] |
| Status | In Progress [ 3 ] | Stalled [ 10000 ] |
| Sprint | 10.0.30 [ 140 ] |
| Status | Stalled [ 10000 ] | In Progress [ 3 ] |
| Sprint | 10.0.30 [ 140 ] | 10.1.22 [ 143 ] |
| Rank | Ranked lower |
Adding a dummy clause that uses the column in the subquery does not fail the above ASSERT
|
|
SELECT ( SELECT COUNT(*) FROM t1 WHERE a = c AND NOT (c LIKE '%booooom!%') ) AS field, COUNT( DISTINCT c ) FROM t2 WHERE b <= 'quux' GROUP BY field; |
field COUNT( DISTINCT c ) |
0 1
|
Running subquery with Ignore Index
EXPLAIN EXTENDED
|
SELECT ( SELECT COUNT(*) FROM t1 IGNORE INDEX(a) where a = c ) AS field, COUNT( DISTINCT c ) FROM t2 WHERE b <= 'quux' GROUP BY field; |
id select_type table type possible_keys key key_len ref rows filtered Extra |
1 PRIMARY t2 range b b 11 NULL 1 100.00 Using index condition; Using filesort |
2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using where |
|
|
SELECT ( SELECT COUNT(*) FROM t1 IGNORE INDEX(a) where a = c ) AS field, COUNT( DISTINCT c ) FROM t2 WHERE b <= 'quux' GROUP BY field; |
field COUNT( DISTINCT c ) |
0 1
|
|
When adding ORDER BY NULL to the above query, we still se the ASSERT failing
EXPLAIN EXTENDED
|
SELECT ( SELECT COUNT(*) FROM t1 WHERE a = c ) AS field, COUNT( DISTINCT c ) FROM t2 WHERE b <= 'quux' GROUP BY field ORDER BY NULL; |
id select_type table type possible_keys key key_len ref rows filtered Extra |
1 PRIMARY t2 range b b 11 NULL 1 100.00 Using index condition; Using filesort |
2 DEPENDENT SUBQUERY t1 ref a a 11 test.t2.c 2 100.00 Using where; Using index |
Warnings:
|
Note 1276 Field or reference 'test.t2.c' of SELECT #2 was resolved in SELECT #1 |
Note 1003 select <expr_cache><`test`.`t2`.`c`>((select count(0) from `test`.`t1` where (`test`.`t1`.`a` = `test`.`t2`.`c`))) AS `field`,count(distinct `test`.`t2`.`c`) AS `COUNT( DISTINCT c )` from `test`.`t2` where (`test`.`t2`.`b` <= 'quux') group by <expr_cache><`test`.`t2`.`c`>((select count(0) from `test`.`t1` where (`test`.`t1`.`a` = `test`.`t2`.`c`))) order by NULL |
SELECT ( SELECT COUNT(*) FROM t1 WHERE a = c ) AS field, COUNT( DISTINCT c ) FROM t2 WHERE b <= 'quux' GROUP BY field ORDER BY NULL; |
main.bug-6486 [ fail ]
|
|
frame #0: 0x000000010003e574 mysqld`find_all_keys(param=0x0000700000115468, select=0x000000010961c438, fs_info=0x0000700000115388, buffpek_pointers=0x0000700000115568, tempfile=0x0000700000115738, pq=0x0000000000000000, found_rows=0x0000700000115a10) + 292 at filesort.cc:689
|
(lldb) p sort_form
|
(TABLE *) $1 = 0x000000010969d070
|
(lldb) p sort_form->alias
|
(String) $2 = {
|
Ptr = 0x0000000109633470 "t2" |
str_length = 2
|
Alloced_length = 8
|
extra_alloc = 0
|
alloced = true |
thread_specific = false |
str_charset = 0x000000010103b6e0
|
}
|
(lldb) p sort_form->read_set
|
(MY_BITMAP *) $3 = 0x000000010969d180
|
(lldb) p sort_form->read_set->bitmap[0]
|
(my_bitmap_map) $4 = 3
|
The value of sort_form->read_set->bitmap[0], shows that the columns with field index 0 and 1 are set for read( which in our case our column 'b' and column 'c')
Continuing debugging ....
|
|
719 sort_form->read_set= &sort_form->tmp_set;
|
(lldb) p sort_form->read_set
|
(MY_BITMAP *) $7 = 0x000000010969d1e0
|
(lldb) p sort_form->read_set->bitmap[0]
|
(my_bitmap_map) $8 = 0
|
Here the bitmap is unset for the columns 'b' and 'c'. Also keep track of the address of
sort_form->read_set.
Going further:
721 if (quick_select) |
722 select->quick->add_used_key_part_to_set(sort_form->read_set);
|
(lldb) p sort_form->read_set->bitmap[0]
|
(my_bitmap_map) $9 = 1
|
(lldb) p sort_form->read_set
|
(MY_BITMAP *) $10 = 0x000000010969d1e0
|
Now we come to the function Field_blob::var_str and then bitmap_is_set
(lldb) bt
|
* thread #4: tid = 0x7290, 0x00000001000126ff mysqld`bitmap_is_set(map=0x000000010969d180, bit=1) + 15 at my_bitmap.h:130, stop reason = step in |
* frame #0: 0x00000001000126ff mysqld`bitmap_is_set(map=0x000000010969d180, bit=1) + 15 at my_bitmap.h:130
|
frame #1: 0x000000010002a142 mysqld`Field_blob::val_str(this=0x00000001096216a8, val_buffer=0x0000700000114168, val_ptr=0x0000700000114168) + 98 at field.cc:7484 |
|
|
(lldb) p map
|
(const MY_BITMAP *) $13 = 0x000000010969d1e0 |
(lldb) p map->bitmap[0]
|
(my_bitmap_map) $14 = 1
|
|
So here we see that we have the new table->read_set for which column 'c' is not set to be read, so the ASSERT fails.
Debugged the difference.
So, find_all_keys does call Item_subselect::walk, which calls walk() for t1.a=t2.c.
The issue is that t2.c is represented by Item_outer_ref(Item_direct_ref(Item_copy_string( ...))).
Item_copy_string does have a pointer to an Item_field(t2.c) in Item_copy::item but not implement Item::walk method.
Adding a walk() method makes the testcase pass:
~/dev-git/10.0$ git diff
|
diff --git a/sql/item.h b/sql/item.h
|
index 2f8607d..f7f308c 100644
|
--- a/sql/item.h
|
+++ b/sql/item.h
|
@@ -4116,6 +4116,13 @@ class Item_copy :public Item
|
virtual double val_real() = 0;
|
virtual longlong val_int() = 0;
|
virtual int save_in_field(Field *field, bool no_conversions) = 0;
|
+
|
+ //psergey-added:
|
+ bool walk(Item_processor processor, bool walk_subquery, uchar *args)
|
+ {
|
+ return (item->walk(processor, walk_subquery, args)) ||
|
+ (this->*processor)(args);
|
+ }
|
};
|
It would be nicer to have a better understanding of the issue, though 
| Sprint | 10.1.22 [ 143 ] | 10.2.5-1 [ 144 ] |
| Rank | Ranked lower |
Note that 10.2 does not use Item_copy_string object (although 10.2 codebase has it).
The reference to t2.c in subquery's WHERE is represented by Item_outer_ref( Item_direct_ref ( Item_field ( t2.c)))
10.1 still uses Item_outer_ref(Item_direct_ref(Item_copy_string( ...))).
Item_copy_string is created in setup_copy_fields.
setup_copy_fields is the only function that creates Item_copy_XXX objects, they have one use case.
varun, please make a patch with the above 'walk' method and the testcase.
| Assignee | Varun Gupta [ varun ] | Sergei Petrunia [ psergey ] |
| Status | In Progress [ 3 ] | In Review [ 10002 ] |
| Assignee | Sergei Petrunia [ psergey ] | Varun Gupta [ varun ] |
| Status | In Review [ 10002 ] | Stalled [ 10000 ] |
| Component/s | Optimizer [ 10200 ] | |
| Fix Version/s | 5.5.55 [ 22311 ] | |
| Fix Version/s | 5.5 [ 15800 ] | |
| Fix Version/s | 10.0 [ 16000 ] | |
| Resolution | Fixed [ 1 ] | |
| Status | Stalled [ 10000 ] | Closed [ 6 ] |
| Workflow | MariaDB v3 [ 63616 ] | MariaDB v4 [ 148064 ] |
Still reproducible on 5.5 and 10.0, as of 5.5.48+ and 10.0.24+.
Not reproducible on current 10.1 tree, apparently it was fixed in 10.1 by this commit:
commit b05158cc10a75196b5c0bf8dad9360608a2dd5b9Author: Sergei Petrunia <psergey@askmonty.org>Date: Wed Feb 24 17:14:38 2016 +0300MDEV-8988: Apparently valid SQL query gives wrong result (nested WHERE)- "Early NULLs filtering" optimization used to "peel off" Item_ref andItem_direct_ref wrappers from an outside column reference beforeadding "outer_table_col IS NOT NULL" into JOIN::outer_ref_cond.- When this happened in a subquery that was evaluated in a post-GROUP-BYcontext, attempt to evaluate JOIN::outer_ref_cond would fetch anincorrect value of outer_table_col.It's still worth fixing in previous versions, unless of course it's a totally harmless debug assertion failure (in which case it might make sense to get rid of it).