Details
Major
Description
We have found the following error in maridb versions 5.5.28a , 5.3.11 and 10.0.0 .
There are correct select results from 5.2.12
mysql < example7.sql # file attached
level cnt
1 1
level cnt
2 1
and there are incorrect select resuslts from 5.5.28a , 5.3.11 and 10.0.0
mysql < example7.sql
level cnt
1 1
level cnt
3 1
Select results from native mysql-5.5 is correct, seems this problem is mariadb specific.
Problem exists only when keys are disabled/enabled, if we remove lines
/*!40000 ALTER TABLE `CCCC` DISABLE KEYS */;
/*!40000 ALTER TABLE `CCCC` ENABLE KEYS */;
then select results are correct.
================
The problem is reproducible with the default optimizer_switch as well as with all OFF values.
The test executes a multi-table update which does the following:
UPDATE AAAA c LEFT JOIN DDDD t ON (c.id = t.profile_id AND t.club_id = 2) SET c.level = IF (t.member_status IS NULL, 1, IF (t.member_status = 1, 2,3));
There is one row to be updated, and its member_status is 1, so level should have become 2, but becomes 3 instead.
GROUP BY in the last SELECT doesn't matter, simple SELECT * will suffice.
Whether it's related or not, on a debug build the server aborts on the update with the assertion failure:
mysqld: field.cc:3664: virtual longlong Field_long::val_int(): Assertion `!table || (!table->read_set || bitmap_is_set(table->read_set, field_index))' failed.
#5 0x00007f7c6dec7b0b in __GI_abort () at abort.c:92 #6 0x00007f7c6debcd4d in __GI___assert_fail (assertion=0xcfb8f0 "!table || (!table->read_set || bitmap_is_set(table->read_set, field_index))", file=<optimized out>, line=3664, function=<optimized out>) at assert.c:81 #7 0x000000000063b53d in Field_long::val_int (this=0x2c47998) at field.cc:3664 #8 0x0000000000581bbb in Item_field::val_int (this=0x2c4e9b8) at item.cc:2320 #9 0x000000000058dfdf in Item_direct_ref::val_int (this=0x2c5bbb8) at item.cc:6902 #10 0x000000000059b769 in Item_direct_view_ref::val_int (this=0x2c5bbb8) at item.h:3010 #11 0x00000000005cc30f in Arg_comparator::compare_int_unsigned_signed (this=0x2c41f40) at item_cmpfunc.cc:1242 #12 0x00000000005ac891 in Arg_comparator::compare (this=0x2c41f40) at item_cmpfunc.h:72 #13 0x00000000005cdf8e in Item_func_eq::val_int (this=0x2c41e78) at item_cmpfunc.cc:1853 #14 0x000000000057bd76 in Item::val_bool (this=0x2c41e78) at item.cc:199 #15 0x00000000005d12c9 in Item_func_if::val_int (this=0x2c421d8) at item_cmpfunc.cc:2641 #16 0x00000000005d130a in Item_func_if::val_int (this=0x2c42308) at item_cmpfunc.cc:2642 #17 0x000000000058a224 in Item::save_in_field (this=0x2c42308, field=0x2c315a8, no_conversions=false) at item.cc:5554 #18 0x00000000006f468c in fill_record (thd=0x2bc03c0, fields=..., values=..., ignore_errors=false) at sql_base.cc:8683 #19 0x00000000006f489b in fill_record_n_invoke_before_triggers (thd=0x2bc03c0, fields=..., values=..., ignore_errors=false, triggers=0x0, event=TRG_EVENT_UPDATE) at sql_base.cc:8741 #20 0x0000000000773d0b in multi_update::send_data (this=0x2c5b268, not_used_values=...) at sql_update.cc:1767 #21 0x0000000000735a5e in end_send (join=0x2c5b368, join_tab=0x2c5f7c8, end_of_records=false) at sql_select.cc:16789 #22 0x0000000000733983 in evaluate_join_record (join=0x2c5b368, join_tab=0x2c5f4a8, error=0) at sql_select.cc:15929 #23 0x000000000073322a in sub_select (join=0x2c5b368, join_tab=0x2c5f4a8, end_of_records=false) at sql_select.cc:15731 #24 0x0000000000733983 in evaluate_join_record (join=0x2c5b368, join_tab=0x2c5f188, error=0) at sql_select.cc:15929 #25 0x000000000073322a in sub_select (join=0x2c5b368, join_tab=0x2c5f188, end_of_records=false) at sql_select.cc:15731 #26 0x0000000000732a06 in do_select (join=0x2c5b368, fields=0x7f7c651bfbf0, table=0x0, procedure=0x0) at sql_select.cc:15392 #27 0x00000000007133e5 in JOIN::exec (this=0x2c5b368) at sql_select.cc:2764 #28 0x0000000000713c71 in mysql_select (thd=0x2bc03c0, rref_pointer_array=0x2bc3128, tables=0x2c405e8, wild_num=0, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=1342177408, result=0x2c5b268, unit=0x2bc2958, select_lex=0x2bc2e80) at sql_select.cc:2985 #29 0x0000000000772356 in mysql_multi_update (thd=0x2bc03c0, table_list=0x2c405e8, fields=0x2bc2f98, values=0x2bc3498, conds=0x0, options=0, handle_duplicates=DUP_ERROR, ignore=false, unit=0x2bc2958, select_lex=0x2bc2e80) at sql_update.cc:1295 #30 0x000000000068f1f8 in mysql_execute_command (thd=0x2bc03c0) at sql_parse.cc:3200 #31 0x000000000069857d in mysql_parse (thd=0x2bc03c0, rawbuf=0x2c55bd8 "UPDATE AAAA c LEFT JOIN DDDD t ON (c.id = t.profile_id AND t.club_id = 2) SET c.level = IF (t.member_status IS NULL, 1, IF (t.member_status = 1, 2,3))", length=150, found_semicolon=0x7f7c651c07d8) at sql_parse.cc:6173 #32 0x000000000068a08a in dispatch_command (command=COM_QUERY, thd=0x2bc03c0, packet=0x2c099a1 "UPDATE AAAA c LEFT JOIN DDDD t ON (c.id = t.profile_id AND t.club_id = 2) SET c.level = IF (t.member_status IS NULL, 1, IF (t.member_status = 1, 2,3))", packet_length=150) at sql_parse.cc:1243 #33 0x0000000000689328 in do_command (thd=0x2bc03c0) at sql_parse.cc:923 #34 0x0000000000685d83 in handle_one_connection (arg=0x2bc03c0) at sql_connect.cc:1231 #35 0x00007f7c6e9cbefc in start_thread (arg=0x7f7c651c1700) at pthread_create.c:304 #36 0x00007f7c6df72f4d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:112
revision-id: sergii@pisem.net-20130128081223-mp9rsd3t9soz8lly revno: 3621 branch-nick: maria-5.3
Also reproducible on current 5.5 and 10.0-base.
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
The problem is that t3.map_status (AKA v1.member_status) is not marked for read (probably the mark was not passed via view to the underlying table).
fix_fields set read_set correctly
SELECT_LEX::update_used_tables() cleaned it
then it should call update_used_tables for every part of the "SELECT" but update list of multi-update is not referenced there.
Fix committed for review.
Here is simplified test suite (I tried to remove everything which is not related to the crash):
CREATE TABLE `t1` (
`id` int(10) unsigned,
`level` tinyint(3) unsigned
);
INSERT INTO `t1` VALUES (2519583,1);
CREATE TABLE `t2` (
`club_id` int(11) DEFAULT '0',
`profile_id` int(11) DEFAULT '0',
`member_level_id` int(11) DEFAULT '0'
);
INSERT INTO `t2` VALUES (2,2519583,12);
CREATE TABLE `t3` (
`member_level_id` int(11) unsigned DEFAULT '0',
`map_level` int(11) unsigned DEFAULT '0',
`map_status` int(11) unsigned DEFAULT '0'
);
INSERT INTO `t3` VALUES (12,12,1);
CREATE
VIEW `v1` AS
select
club_id,
profile_id,
map_level AS member_level_id,
map_status AS member_status
from
(t2 join t3
on (t2.member_level_id = t3.member_level_id));
UPDATE
t1 LEFT JOIN v1 ON (t1.id = v1.profile_id)
SET t1.level = v1.member_status;
drop view v1;
drop table t1,t2,t3;