[MDEV-4123] Incorrect results after multi-table update or assertion `!table || (!table->read_set || bitmap_is_set(table->read_set, field_index))' failure Created: 2013-01-31  Updated: 2013-02-12  Resolved: 2013-02-12

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: 10.0.0, 5.5.29, 5.3.12
Fix Version/s: 10.0.2, 5.5.30, 5.3.13

Type: Bug Priority: Major
Reporter: Raimonds Assignee: Oleksandr Byelkin
Resolution: Fixed Votes: 0
Labels: None
Environment:

Ubuntu 12.04
mariadb installed from sources.


Attachments: File example7.sql    

 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.

 Comments   
Comment by Oleksandr Byelkin [ 2013-02-08 ]

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;

Comment by Oleksandr Byelkin [ 2013-02-08 ]

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).

Comment by Oleksandr Byelkin [ 2013-02-08 ]

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.

Comment by Oleksandr Byelkin [ 2013-02-08 ]

Fix committed for review.

Generated at Thu Feb 08 06:53:55 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.