Assertion `!table || (!table->read_set || bitmap_is_set(table->read_set, field_index))' with virtual columns

[Philip Stoev]

When a virtual column is used in an ORDER BY, the server asserts as follows:

mysqld: field.cc:6610: virtual String* Field_string::val_str(String*, String*): Assertion `!table || (!table->read_set || bitmap_is_set(table->read_set, field_index))' failed.

This is not observed if the expression from the virtual column is inlined in the query, so it seems to be a virtual-columns-specific bug.

[Philip Stoev (pstoev-askmonty)]

Test case:

CREATE TABLE IF NOT EXISTS X (
 f1 CHAR(255) BINARY NOT NULL DEFAULT 0,
 f2 CHAR(255) BINARY NOT NULL DEFAULT 0,
 v3 CHAR(255) BINARY AS ( CONCAT( f2 , f1 ) ) VIRTUAL );
REPLACE INTO X ( f1 , f2 ) VALUES ( '4' , '6' );
REPLACE INTO X ( f1 , f2 ) VALUES ( '4' , '6' );
SELECT 1 FROM X ORDER BY v3;

Backtrace:

#8 0x00897de8 in __assert_fail () from /lib/libc.so.6
#9 0x08242d5a in Field_string::val_str (this=0xb5f58490, val_buffer=0xb5f58700, val_ptr=0xb5f585b8) at field.cc:6610
#10 0x081b417c in Item_field::val_str (this=0xb5f585b0, str=0xb5f58700) at item.cc:2054
#11 0x081fd94e in Item_func_concat::val_str (this=0xb5f586f8, str=0xb5f58700) at item_strfunc.cc:288
#12 0x081bb567 in Item::save_in_field (this=0xb5f586f8, field=0xb5f58500, no_conversions=false) at item.cc:5178
#13 0x082e2a0e in update_virtual_fields (table=0xb5fd6800, for_write=false) at table.cc:5448
#14 0x0839b5f9 in find_all_keys (param=0xb60fdc38, select=0xb5f4bc68, sort_keys=0xb5f5cef0, buffpek_pointers=0xb60fda70, tempfile=0xb60fdb54, indexfile=0x0)
    at filesort.cc:590
#15 0x0839a3d9 in filesort (thd=0xaf38658, table=0xb5fd6800, sortorder=0xb5f4bd90, s_length=1, select=0xb5f4bc68, max_rows=18446744073709551615,
    sort_positions=false, examined_rows=0xb60fdda0) at filesort.cc:246
#16 0x0830814f in create_sort_index (thd=0xaf38658, join=0xb5f4a4c0, order=0xb5f4a410, filesort_limit=18446744073709551615,
    select_limit=18446744073709551615, is_order_by=false) at sql_select.cc:14091
#17 0x082ea43f in JOIN::exec (this=0xb5f4a4c0) at sql_select.cc:2253
#18 0x082eae4d in mysql_select (thd=0xaf38658, rref_pointer_array=0xaf3a08c, tables=0xb5f4a180, wild_num=0, fields=..., conds=0x0, og_num=1,
    order=0xb5f4a410, group=0x0, having=0x0, proc_param=0x0, select_options=2147764736, result=0xb5f4a4a8, unit=0xaf39d14, select_lex=0xaf39f94)
    at sql_select.cc:2510
#19 0x082e37a7 in handle_select (thd=0xaf38658, lex=0xaf39cb8, result=0xb5f4a4a8, setup_tables_done_option=0) at sql_select.cc:280
#20 0x08287dd5 in execute_sqlcom_select (thd=0xaf38658, all_tables=0xb5f4a180) at sql_parse.cc:5079
#21 0x0827e7c8 in mysql_execute_command (thd=0xaf38658) at sql_parse.cc:2264
#22 0x08289fa0 in mysql_parse (thd=0xaf38658, inBuf=0xb5f4a068 "SELECT 1 FROM X ORDER BY v3", length=27, found_semicolon=0xb60ff230) at sql_parse.cc:6024
#23 0x0827c1fa in dispatch_command (command=COM_QUERY, thd=0xaf38658, packet=0xaf50571 "SELECT 1 FROM X ORDER BY v3", packet_length=27) at sql_parse.cc:1183
#24 0x0827b6a0 in do_command (thd=0xaf38658) at sql_parse.cc:890
#25 0x08278800 in handle_one_connection (arg=0xaf38658) at sql_connect.cc:1153
#26 0x00a08919 in start_thread () from /lib/libpthread.so.0
#27 0x00951e5e in clone () from /lib/libc.so.6

bzr version-info:

revision-id: <email address hidden>
date: 2010-07-02 20:24:39 -0700
build-date: 2010-07-09 08:58:16 -0700
revno: 2818
branch-nick: 5.2

[Philip Stoev (pstoev-askmonty)]

Here is another test case:

CREATE TABLE IF NOT EXISTS X (
 f1 INTEGER NOT NULL DEFAULT 0,
 f2 VARCHAR(32) NOT NULL DEFAULT 0,
 f3 DATE NOT NULL DEFAULT 0,
 f4 TIME NOT NULL DEFAULT 0,
 v1 DOUBLE AS ( f3 ) VIRTUAL ,
 v2 DOUBLE AS ( ( CEILING( ( IF( f3 , f3 , ( f3 < ( GET_FORMAT( DATETIME, 'INTERNAL' ) ) ) ) ) ) ) ) VIRTUAL
);
INSERT INTO X ( f1 , f2 , f3 , f4 ) VALUES ( 1 , f1 , f4 , f3 );
INSERT INTO X ( f1 , f2 , f3 , f4 ) SELECT v1 , f3 , f2 , v1 FROM X ORDER BY v1,v2;
DELETE FROM X ORDER BY v1,v2 LIMIT 2 ;

[Philip Stoev (pstoev-askmonty)]

This assertion still happens even after Igor's patch:

Test case:

CREATE TABLE IF NOT EXISTS X ( f1 CHAR(255) BINARY NOT NULL DEFAULT 0, f2 CHAR(255) BINARY NOT NULL DEFAULT 0, f3 CHAR(255) BINARY NOT NULL DEFAULT 0, f4 CHAR(255) BINARY NOT NULL DEFAULT 0, v1 CHAR(255) BINARY AS ( ( f3 * f4 ) ) VIRTUAL , v2 CHAR(255) BINARY AS ( ( IF( f3 , ( STRCMP(f3, f1) ) , f3 ) ) ) VIRTUAL , v3 CHAR(255) BINARY AS ( ( LEFT( f2 , ( ( ( f1 NOT LIKE '.*' ) ) XOR ( f1 ) ) ) ) ) VIRTUAL , v4 CHAR(255) BINARY AS ( f4 ) VIRTUAL ) ; CREATE TABLE IF NOT EXISTS X ( f1 CHAR(255) BINARY NOT NULL DEFAULT 0, f2 CHAR(255) BINARY NOT NULL DEFAULT 0, f3 CHAR(255) BINARY NOT NULL DEFAULT 0, f4 CHAR(255) BINARY NOT NULL DEFAULT 0, v1 CHAR(255) BINARY AS ( ( CASE ( LOWER( f2 ) ) WHEN f3 THEN f1 WHEN ( IF( f3 , f4 , f4 ) ) THEN f1 ELSE f4 END ) ) VIRTUAL , v2 CHAR(255) BINARY AS ( f3 ) VIRTUAL , v3 CHAR(255) BINARY AS ( f2 ) VIRTUAL , v4 CHAR(255) BINARY AS ( f3 ) VIRTUAL ) ; CREATE TABLE IF NOT EXISTS X ( f1 CHAR(255) BINARY NOT NULL DEFAULT 0, f2 CHAR(255) BINARY NOT NULL DEFAULT 0, f3 CHAR(255) BINARY NOT NULL DEFAULT 0, f4 CHAR(255) BINARY NOT NULL DEFAULT 0, v1 CHAR(255) BINARY AS ( f4 ) VIRTUAL , v2 CHAR(255) BINARY AS ( f3 ) VIRTUAL , v3 CHAR(255) BINARY AS ( f1 ) VIRTUAL , v4 CHAR(255) BINARY AS ( f4 ) VIRTUAL );
REPLACE INTO X ( f1 , f2 , f3 , f4 ) VALUES ( 4 , 6 , f3 , 6 );
REPLACE INTO X ( f1 , f2 , f3 , f4 ) SELECT f3 , f4 , f3 , f1 FROM X WHERE v4 = 6 ORDER BY f1,f2,f3,f4,v1,v2,v3,v4 LIMIT 4;
SELECT v1, v1 FROM X WHERE v1 <=> v1 OR v3 < f3 ORDER BY v3;

backtrace:

#8 0x00897de8 in __assert_fail () from /lib/libc.so.6
#9 0x082430cb in Field_string::val_real (this=0xb5e09df0) at field.cc:6557
#10 0x081b4825 in Item_field::val_real (this=0xb5e0a0f8) at item.cc:2063
#11 0x081d8745 in Item_func_mul::real_op (this=0xb5e0a218) at item_func.cc:1207
#12 0x081d7107 in Item_func_numhybrid::val_str (this=0xb5e0a218, str=0xb5e0a220) at item_func.cc:760
#13 0x081bbb8f in Item::save_in_field (this=0xb5e0a218, field=0xb5e09ed0, no_conversions=false) at item.cc:5178
#14 0x082e327c in update_virtual_fields (thd=0xaed2618, table=0xb5e08200, for_write=false) at table.cc:5471
#15 0x0839c4a1 in find_all_keys (param=0xb60d1c38, select=0xb5e0bc20, sort_keys=0xb5e06278, buffpek_pointers=0xb60d1a70, tempfile=0xb60d1b54, indexfile=0x0)
    at filesort.cc:591
#16 0x0839b231 in filesort (thd=0xaed2618, table=0xb5e08200, sortorder=0xb5d4bff0, s_length=1, select=0xb5e0bc20, max_rows=18446744073709551615,
    sort_positions=false, examined_rows=0xb60d1da0) at filesort.cc:246
#17 0x08308aa3 in create_sort_index (thd=0xaed2618, join=0xb5d4aac0, order=0xb5d4aa10, filesort_limit=18446744073709551615,
    select_limit=18446744073709551615, is_order_by=false) at sql_select.cc:14093
#18 0x082ead17 in JOIN::exec (this=0xb5d4aac0) at sql_select.cc:2253
#19 0x082eb725 in mysql_select (thd=0xaed2618, rref_pointer_array=0xaed4058, tables=0xb5d4a2a0, wild_num=0, fields=..., conds=0xb5d4a8d8, og_num=1,
    order=0xb5d4aa10, group=0x0, having=0x0, proc_param=0x0, select_options=2147764736, result=0xb5d4aaa8, unit=0xaed3cdc, select_lex=0xaed3f60)
    at sql_select.cc:2510
#20 0x082e407f in ...

[Igor Babaev (igorb-seattle)]

Although the last test case triggers the same assert failure the cause of the problem is different from the one reported originally.
The last failure concerns queries with order by clauses that refer to virtual columns in their select lists, while the original failure concerned queries whose order by clauses contained references to virtual columns.
The last failure requires a different treatment. That's why I opened a new bug for it (see bug #607566).