semijoin query generates suboptimal mal plan

[smavros]

the query

select * from f where f.c in (select b.c from b);

translates to a SEMIJOIN operator between tables f(left) and b (right)

project (                                 
| semijoin (                              
| | table("sys"."f") [ "f"."n", "f"."c" ],
| | project (                             
| | | table("sys"."b") [ "b"."c" ]        
| | ) [ "b"."c" as "%4"."%4" ]            
| ) [ ("f"."c") any = ("%4"."%4") ]       
) [ "f"."n", "f"."c" ]                    

the MAL plan though indicates a JOIN between f and b before the leftjoin (done by the intersect())

 function user.main():void;                                                                                                                    
     X_1:void := querylog.define("explain select * from f where f.c in (select b.c from b);":str, "default_pipe":str, 26:int);                 
 barrier X_103:bit := language.dataflow();                                                                                                     
     X_4:int := sql.mvc();                                                                                                                     
     C_5:bat[:oid] := sql.tid(X_4:int, "sys":str, "f":str);                                                                                    
     X_8:bat[:int] := sql.bind(X_4:int, "sys":str, "f":str, "n":str, 0:int);                                                                   
     X_15:bat[:str] := sql.bind(X_4:int, "sys":str, "f":str, "c":str, 0:int);                                                                  
     C_20:bat[:oid] := sql.tid(X_4:int, "sys":str, "b":str);                                                                                   
     X_22:bat[:str] := sql.bind(X_4:int, "sys":str, "b":str, "c":str, 0:int);                                                                  
     X_27:bat[:str] := algebra.projection(C_20:bat[:oid], X_22:bat[:str]);                                                                     
     X_28:bat[:oid] := algebra.join(X_15:bat[:str], X_27:bat[:str], C_5:bat[:oid], nil:BAT, false:bit, nil:lng);                               
     C_33:bat[:oid] := bat.mirror(X_8:bat[:int]);                                                                                              
     C_34:bat[:oid] := algebra.intersect(C_33:bat[:oid], X_28:bat[:oid], C_5:bat[:oid], nil:BAT, false:bit, false:bit, nil:lng);               
     X_105:void := language.pass(C_5:bat[:oid]);                                                                                               
     X_35:bat[:int] := algebra.projection(C_34:bat[:oid], X_8:bat[:int]);                                                                      
     X_106:void := language.pass(X_8:bat[:int]);                                                                                               
     X_36:bat[:str] := algebra.projection(C_34:bat[:oid], X_15:bat[:str]);                                                                     
     X_107:void := language.pass(C_34:bat[:oid]);                                                                                              
     X_108:void := language.pass(X_15:bat[:str]);                                                                                              
     X_38:bat[:str] := bat.pack("sys.f":str, "sys.f":str);                                                                                     
     X_39:bat[:str] := bat.pack("n":str, "c":str);                                                                                             
     X_40:bat[:str] := bat.pack("int":str, "clob":str);                                                                                        
     X_41:bat[:int] := bat.pack(32:int, 0:int);                                                                                                
     X_42:bat[:int] := bat.pack(0:int, 0:int);                                                                                                 
 exit X_103:bit;                                                                                                                               
     X_37:int := sql.resultSet(X_38:bat[:str], X_39:bat[:str], X_40:bat[:str], X_41:bat[:int], X_42:bat[:int], X_35:bat[:int], X_36:bat[:str]);
 end user.main;                                                                                                                                

the join probably should not appear since it has a high performance cost and we do not really need it

tested on default at b0f51c763c66 (mercurial changeset)

[njnes]

this is done when run without the mitosis optimizer.