Assertion `fixed == 1' failed in Item_func_eq::val_int() with semijoin + materialization + max_join_size

Bug #861147 reported by Philip Stoev
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
MariaDB
Fix Released
High
Sergey Petrunia

Bug Description

This particular example uses max_join_size=10 for brevity, however the issue has been observed with max_join_size=1000000 as well.

mysqld: item_cmpfunc.cc:1838: virtual longlong Item_func_eq::val_int(): Assertion `fixed == 1' failed.

#8 0x006b0d98 in __assert_fail () from /lib/libc.so.6
#9 0x0820e94e in Item_func_eq::val_int (this=0xae750230) at item_cmpfunc.cc:1838
#10 0x081f3d6f in eval_const_cond (cond=0xae750230) at item_func.cc:63
#11 0x083377ce in remove_eq_conds (thd=0xac9e6e0, cond=0xae750230, cond_value=0xae8fdf64) at sql_select.cc:12605
#12 0x083372c5 in remove_eq_conds (thd=0xac9e6e0, cond=0xae750650, cond_value=0xae73d2dc) at sql_select.cc:12486
#13 0x083371c3 in optimize_cond (join=0xae738188, conds=0xae750650, join_list=0xaca01e4, cond_value=0xae73d2dc, cond_equal=0xae73d36c) at sql_select.cc:12453
#14 0x0831b920 in JOIN::optimize (this=0xae738188) at sql_select.cc:975
#15 0x08321e11 in mysql_select (thd=0xac9e6e0, rref_pointer_array=0xaca025c, tables=0xae713188, wild_num=1, fields=..., conds=0xae72fd40, og_num=0,
    order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147764224, result=0xae72fe80, unit=0xac9fe40, select_lex=0xaca0120)
    at sql_select.cc:2886
#16 0x08319c67 in handle_select (thd=0xac9e6e0, lex=0xac9fde4, result=0xae72fe80, setup_tables_done_option=0) at sql_select.cc:283
#17 0x082b4434 in execute_sqlcom_select (thd=0xac9e6e0, all_tables=0xae713188) at sql_parse.cc:5110
#18 0x082ab1f1 in mysql_execute_command (thd=0xac9e6e0) at sql_parse.cc:2250
#19 0x082b6a63 in mysql_parse (thd=0xac9e6e0,
    rawbuf=0xae712ec0 "SELECT *\nFROM t1\nWHERE ( 1 , 3 ) IN (\nSELECT t2.f1 , MAX( t3.f3 ) \nFROM t2\nJOIN t3\nWHERE t3.f4 IN (\nSELECT t4.f5\nFROM t4\nSTRAIGHT_JOIN t5\nWHERE t4.f4 < t2.f5 \n)\n) AND ( 'p' , 'k' ) IN (\nSELECT f4 , f"..., length=212, found_semicolon=0xae8ff228) at sql_parse.cc:6110
#20 0x082a8e40 in dispatch_command (command=COM_QUERY, thd=0xac9e6e0,
    packet=0xacf7321 "SELECT *\nFROM t1\nWHERE ( 1 , 3 ) IN (\nSELECT t2.f1 , MAX( t3.f3 ) \nFROM t2\nJOIN t3\nWHERE t3.f4 IN (\nSELECT t4.f5\nFROM t4\nSTRAIGHT_JOIN t5\nWHERE t4.f4 < t2.f5 \n)\n) AND ( 'p' , 'k' ) IN (\nSELECT f4 , f"..., packet_length=212) at sql_parse.cc:1221
#21 0x082a829b in do_command (thd=0xac9e6e0) at sql_parse.cc:916
#22 0x082a527f in handle_one_connection (arg=0xac9e6e0) at sql_connect.cc:1191
#23 0x00821919 in start_thread () from /lib/libpthread.so.0
#24 0x0076acce in clone () from /lib/libc.so.6

explain also crashes.

minimal optimizer switch: semijoin=on,materialization=on

full optimizer switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on

test case:

CREATE TABLE t1 ( f2 int) ;

CREATE TABLE t2 ( f1 int, f3 int, f4 varchar(3), f5 varchar(35)) ;
INSERT INTO t2 VALUES (4057,9,'USA','Visalia'),(3993,11,'USA','Waco'),(3948,14,'USA','Warren'),(3813,57,'USA','Washington'),(4010,11,'USA','Waterbury'),(4017,11,'USA','West Covina'),(4004,11,'USA','West Valley City'),(4033,10,'USA','Westminster'),(3842,34,'USA','Wichita'),(4018,10,'USA','Wichita Falls'),(3899,19,'USA','Winston-Salem'),(3914,17,'USA','Worcester'),(3888,20,'USA','Yonkers');

CREATE TABLE t3 ( f3 int, f4 varchar(3)) ;
INSERT INTO t3 VALUES (86,'USA');

CREATE TABLE t4 ( f3 int, f4 varchar(3), f5 varchar(52)) ;
INSERT INTO t4 VALUES (0,'RUS','Belorussian'),(0,'USA','Portuguese');

CREATE TABLE t5 ( f2 int) ;

CREATE TABLE t6 ( f4 varchar(3));
INSERT INTO t6 VALUES ('RUS'),('USA');

SET SESSION max_join_size=10;
SET SESSION optimizer_switch='semijoin=ON,materialization=ON';
SELECT *
FROM t1
WHERE ( 1 , 3 ) IN (
        SELECT t2.f1 , MAX( t3.f3 )
        FROM t2
        JOIN t3
        WHERE t3.f4 IN (
                SELECT t4.f5
                FROM t4
                STRAIGHT_JOIN t5
                WHERE t4.f4 < t2.f5
        )
) AND ( 'p' , 'k' ) IN (
        SELECT f4 , f4 FROM t6
);

bzr version-info
revision-id: <email address hidden>
date: 2011-09-27 17:40:04 +0300
build-date: 2011-09-28 08:43:25 +0300
revno: 3197
branch-nick: maria-5.3

Changed in maria:
milestone: none → 5.3
assignee: nobody → Sergey Petrunia (sergefp)
Changed in maria:
importance: Undecided → High
Changed in maria:
status: New → Fix Committed
Changed in maria:
status: Fix Committed → Fix Released
To post a comment you must log in.
This report contains Public information  
Everyone can see this information.

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.