[MDEV-3948] Assertion `records_are_comparable(table)' fails in compare_record(const TABLE*) on UPDATE with simple AND condition, index_merge+index_merge_intersection, InnoDB Created: 2012-12-18  Updated: 2013-01-28  Resolved: 2013-01-28

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: 5.5.28a
Fix Version/s: 5.5.29

Type: Bug Priority: Major
Reporter: Elena Stepanova Assignee: Timour Katchaounov (Inactive)
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Relates

 Description    

mysqld: maria-5.5/sql/sql_update.cc:67: bool compare_record(const TABLE*): Assertion `records_are_comparable(table)' failed.
 
[ERROR] mysqld got signal 6 ;
 

#5  0x00007f7d9b084b0b in __GI_abort () at abort.c:92
 
#6  0x00007f7d9b079d4d in __GI___assert_fail (assertion=0xd6f681 "records_are_comparable(table)", file=<optimized out>, line=67, function=<optimized out>) at assert.c:81
 
#7  0x00000000006c5817 in compare_record (table=0x33019d0) at maria-5.5/sql/sql_update.cc:67
 
#8  0x00000000006c77af in mysql_update (thd=0x32dba50, table_list=0x3301118, fields=..., values=..., conds=0x3392e38, order_num=0, order=0x0, limit=18446744073709551615, handle_duplicates=DUP_ERROR, ignore=false, found_return=0x7f7d8a95d060, updated_return=0x7f7d8a95d068) at maria-5.5/sql/sql_update.cc:698
 
#9  0x0000000000612539 in mysql_execute_command (thd=0x32dba50) at maria-5.5/sql/sql_parse.cc:2797
 
#10 0x000000000061aa93 in mysql_parse (thd=0x32dba50, rawbuf=0x333a9f8 "UPDATE t1 SET a=1 WHERE c=7 AND b=2", length=35, parser_state=0x7f7d8a95d4f0) at maria-5.5/sql/sql_parse.cc:5737
 
#11 0x000000000060e260 in dispatch_command (command=COM_QUERY, thd=0x32dba50, packet=0x32eccd1 "UPDATE t1 SET a=1 WHERE c=7 AND b=2", packet_length=35) at maria-5.5/sql/sql_parse.cc:1055
 
#12 0x000000000060d517 in do_command (thd=0x32dba50) at maria-5.5/sql/sql_parse.cc:794
 
#13 0x0000000000715a15 in do_handle_one_connection (thd_arg=0x32dba50) at maria-5.5/sql/sql_connect.cc:1253
 
#14 0x0000000000715400 in handle_one_connection (arg=0x32dba50) at maria-5.5/sql/sql_connect.cc:1168
 
#15 0x0000000000bb7d21 in pfs_spawn_thread (arg=0x3329510) at maria-5.5/storage/perfschema/pfs.cc:1015
 
#16 0x00007f7d9bdbdefc in start_thread (arg=0x7f7d8a95e700) at pthread_create.c:304
 
#17 0x00007f7d9b12ff4d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:112
 

branch: maria/5.5
 
revision-id: monty@askmonty.org-20121217203456-gjlhf7eslleda9rz
 
date: 2012-12-17 22:34:56 +0200
 
build-date: 2012-12-18 22:39:51 +0400
 
revno: 3597

Reproducible on older revisions of 5.5 too.
Could not reproduce on maria/5.3, maria/10.0, mysql-server/5.6
No visible problem on a non-debug build.
No failure with SELECT instead of INSERT.
No failure with MyISAM (but both InnoDB and XtraDB fail).

Minimal optimizer_switch: index_merge=on,index_merge_intersection=on
Full optimizer_switch (default):

index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off

Test case:

--source include/have_innodb.inc
 
 
 
SET optimizer_switch = 'index_merge=on,index_merge_intersection=on';
 
 
 
CREATE TABLE t1 (a INT, b INT, c INT, KEY(b), KEY(c)) ENGINE=InnoDB;
 
INSERT INTO t1 (b,c) VALUES (5,9),(3,4),(2,7);
 
UPDATE t1 SET a=1 WHERE c=7 AND b=2;
 
 



 Comments   
Comment by Timour Katchaounov (Inactive) [ 2013-01-25 ]

Analysis:

In 5.3 and 5.5 the range optimizer chooses a range scan, and all is well.
In 5.5 however, the optimizer chooses a ror-intersect access method.
In this case mysql_update() calls
QUICK_ROR_INTERSECT_SELECT::reset -> QUICK_ROR_INTERSECT_SELECT::init_ror_merged_scan -> QUICK_RANGE_SELECT::init_ror_merged_scan
The latter method resets both table->[read_set,write_set] to the bitmap '100'.
The subsequent call
can_compare_record= records_are_comparable(table);
sets can_compare_record to true.

A bit later the call to
while (!(error=info.read_record(&info)) && !thd->killed)
resets table->read_set to '110', and table->write_set to '001'.
Since can_compare_records == TRUE, the statement
if (!can_compare_record || compare_record(table))
evaluates compare_record(table) again, however this time the records are not comparable due to the
different read/write sets. As a result we get an assertion failure.

Some differences:

  • In 5.3, there is a minor difference in cost of the ROR scan (2 vs 2.0011 in 5.5), so the ror scan is not selected.
    If the cost is changed in gdb, and ror-intersect is selected, the read/write set still get wrong as in 5.5, but
    there is no assert failure because the assert is not there in 5.3.
  • In 10.0 the read/write sets are restored properly by QUICK_RANGE_SELECT::init_ror_merged_scan

The reason for the failed assert is that QUICK_RANGE_SELECT::init_ror_merged_scan re-computes incorrectly the
read/write sets of the table.

This problem is fixed in 10.0 by the following patch:

------------------------------------------------------------
revno: 3471
committer: Sergey Petrunya <psergey@askmonty.org>
branch nick: 10.0-serg-fix-imerge
timestamp: Sat 2012-11-03 12:24:36 +0400
message:

  1. MDEV-3817: Wrong result with index_merge+index_merge_intersection, InnoDB table, join, AND and OR conditions
    Reconcile the fixes from:
    #
  2. guilhem.bichot@oracle.com-20110805143029-ywrzuz15uzgontr0
  3. Fix for BUG#12698916 - "JOIN QUERY GIVES WRONG RESULT AT 2ND EXEC. OR
  4. AFTER FLUSH TABLES [-INT VS NULL]"
    #
  5. guilhem.bichot@oracle.com-20111209150650-tzx3ldzxe1yfwji6
  6. Fix for BUG#12912171 - ASSERTION FAILED: QUICK->HEAD->READ_SET == SAVE_READ_SET
  7. and
    #
    and related fixes from: BUG#1006164, MDEV-376:

Now, ROR-merged QUICK_RANGE_SELECT objects make no assumptions about the values
of table->read_set and table->write_set.
Each QUICK_ROR_SELECT has (and had before) its own column bitmap, but now, all
QUICK_ROR_SELECT's functions that care: reset(), init_ror_merged_scan(), and
get_next() will set table->read_set when invoked and restore it back to what
it was before the call before they return.

This allows to avoid the mess when somebody else modifies table->read_set for
some reason.
------------------------------------------------------------

Comment by Timour Katchaounov (Inactive) [ 2013-01-28 ]

pushed backported fix to 5.5

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