Bug 2749

Summary: Rewrite like without modifiers as = query early in the process
Product: SQL Reporter: Stefan de Konink <stefan>
Component: allAssignee: SQL devs <bugs-sql>
Status: NEW ---    
Severity: enhancement CC: niels, renalkoclok, y.zhang
Priority: Normal Keywords: NONEEDTOTEST
Version: -- development   
Hardware: Other   
OS: Linux   
Attachments: Two traces like.
KONTOL

Description Stefan de Konink 2010-12-10 15:33:43 CET
User-Agent:       Mozilla/5.0 (X11; U; Linux i686; en-US) AppleWebKit/534.10 (KHTML, like Gecko) Chrome/8.0.552.208 Safari/534.10
Build Identifier: 

sql>explain select kvk,bedrijfsnaam from kvk where bedrijfsnaam like 'MonetDB
B.V.';
% .explain # table_name
% mal # name
% clob # type
% 0 # length
function user.s2_1{autoCommit=true}():void;
    _2 := sql.mvc();
barrier _93 := language.dataflow();
    _46:bat[:oid,:str]  :=
sql.bind(_2,"sys","kvk","bedrijfsnaam",0,0@0:oid,1310385@0);
    _7:bat[:oid,:str]  := sql.bind(_2,"sys","kvk","bedrijfsnaam",2);
    _51 := algebra.kdifference(_46,_7);
    _53 := algebra.semijoin(_7,_46);
    _55 := algebra.kunion(_51,_53);
    _11:bat[:oid,:oid]  := sql.bind_dbat(_2,"sys","kvk",1);
    _12 := bat.reverse(_11);
    _57 := algebra.kdifference(_55,_12);
    _58 := batcalc.str(_57);
    _61 := pcre.like_uselect(_58,"MonetDB B.V.":str,"":str);
    _64 := algebra.markT(_61,3,0);
    _76 := bat.reverse(_64);
    _49:bat[:oid,:lng]  := sql.bind(_2,"sys","kvk","kvk",0,0@0:oid,1310385@0);
    _13:bat[:oid,:lng]  := sql.bind(_2,"sys","kvk","kvk",2);
    _81 := algebra.kdifference(_49,_13);
    _83 := algebra.semijoin(_13,_49);
    _85 := algebra.kunion(_81,_83);
    _87 := algebra.leftjoin(_76,_85);
    _48:bat[:oid,:str]  :=
sql.bind(_2,"sys","kvk","bedrijfsnaam",0,1310385@0,nil:oid);
    _52 := algebra.kdifference(_48,_7);
    _54 := algebra.semijoin(_7,_48);
    _56 := algebra.kunion(_52,_54);
    _59 := batcalc.str(_56);
    _62 := pcre.like_uselect(_59,"MonetDB B.V.":str,"":str);
    _68 := algebra.markT(_62,3,1);
    _79 := bat.reverse(_68);
    _50:bat[:oid,:lng]  := sql.bind(_2,"sys","kvk","kvk",0,1310385@0,nil:oid);
    _82 := algebra.kdifference(_50,_13);
    _84 := algebra.semijoin(_13,_50);
    _86 := algebra.kunion(_82,_84);
    _88 := algebra.leftjoin(_79,_86);

    _9:bat[:oid,:str]  := sql.bind(_2,"sys","kvk","bedrijfsnaam",1);
    _60 := batcalc.str(_9);
    _63 := pcre.like_uselect(_60,"MonetDB B.V.":str,"":str);
    _71 := algebra.markT(_63,3,2);
    _80 := bat.reverse(_71);
    _14:bat[:oid,:lng]  := sql.bind(_2,"sys","kvk","kvk",1);
    _89 := algebra.leftjoin(_80,_14);
    _15 := mat.pack(_87,_88,_89);
    _92 := algebra.leftjoin(_80,_9);
    _91 := algebra.leftjoin(_79,_56);
    _90 := algebra.leftjoin(_76,_55);
exit _93;
    _17 := sql.resultSet(2,1,_15);
    sql.rsColumn(_17,"sys.kvk","kvk","bigint",64,0,_15);
    _16 := mat.pack(_90,_91,_92);
    sql.rsColumn(_17,"sys.kvk","bedrijfsnaam","varchar",256,0,_16);
    _28 := io.stdout();
    sql.exportResult(_28,_17);
end s2_1;


sql>explain select kvk,bedrijfsnaam from kvk where bedrijfsnaam = 'MonetDB
B.V.';
% .explain # table_name
% mal # name
% clob # type
% 0 # length
function user.s3_1{autoCommit=true}():void;
    _2 := sql.mvc();
    _3:bat[:oid,:str]  := sql.bind(_2,"sys","kvk","bedrijfsnaam",0);
    _8 := algebra.uselect(_3,"MonetDB B.V.":str);
    _9:bat[:oid,:str]  := sql.bind(_2,"sys","kvk","bedrijfsnaam",2);
    _11 := algebra.kdifference(_8,_9);
    _12 := algebra.uselect(_9,"MonetDB B.V.":str);
    _13 := algebra.kunion(_11,_12);
    _14:bat[:oid,:str]  := sql.bind(_2,"sys","kvk","bedrijfsnaam",1);
    _16 := algebra.uselect(_14,"MonetDB B.V.":str);
    _17 := algebra.kunion(_13,_16);
    _18:bat[:oid,:oid]  := sql.bind_dbat(_2,"sys","kvk",1);
    _19 := bat.reverse(_18);
    _20 := algebra.kdifference(_17,_19);
    _21 := algebra.markT(_20,0@0:oid);
    _22 := bat.reverse(_21);
    _23:bat[:oid,:lng]  := sql.bind(_2,"sys","kvk","kvk",0);
    _24:bat[:oid,:lng]  := sql.bind(_2,"sys","kvk","kvk",2);
    _25 := algebra.kdifference(_23,_24);
    _26 := algebra.kunion(_25,_24);
    _27:bat[:oid,:lng]  := sql.bind(_2,"sys","kvk","kvk",1);
    _28 := algebra.kunion(_26,_27);
    _29 := algebra.leftjoin(_22,_28);
    _30 := algebra.kdifference(_3,_9);
    _31 := algebra.kunion(_30,_9);
    _32 := algebra.kunion(_31,_14);
    _33 := algebra.leftjoin(_22,_32);
    _34 := sql.resultSet(2,1,_29);
    sql.rsColumn(_34,"sys.kvk","kvk","bigint",64,0,_29);
    sql.rsColumn(_34,"sys.kvk","bedrijfsnaam","varchar",256,0,_33);
    _45 := io.stdout();
    sql.exportResult(_45,_34);
end s3_1;

Mitosis off:

sql>explain select kvk,bedrijfsnaam from kvk where bedrijfsnaam like 'MonetDB
B.V.';
% .explain # table_name
% mal # name
% clob # type
% 0 # length
function user.s1_1{autoCommit=true}():void;
    _2 := sql.mvc();
barrier _59 := language.dataflow();
    _3:bat[:oid,:str]  := sql.bind(_2,"sys","kvk","bedrijfsnaam",0);
    _8:bat[:oid,:str]  := sql.bind(_2,"sys","kvk","bedrijfsnaam",2);
    _10 := algebra.kdifference(_3,_8);
    _11 := algebra.kunion(_10,_8);
    _12:bat[:oid,:str]  := sql.bind(_2,"sys","kvk","bedrijfsnaam",1);
    _14 := algebra.kunion(_11,_12);
    _15:bat[:oid,:oid]  := sql.bind_dbat(_2,"sys","kvk",1);
    _16 := bat.reverse(_15);
    _17 := algebra.kdifference(_14,_16);
    _18 := batcalc.str(_17);
    _19 := pcre.like_uselect(_18,"MonetDB B.V.":str,"":str);
    _20 := algebra.markT(_19,0@0:oid);
    _21 := bat.reverse(_20);
    _22:bat[:oid,:lng]  := sql.bind(_2,"sys","kvk","kvk",0);
    _23:bat[:oid,:lng]  := sql.bind(_2,"sys","kvk","kvk",2);
    _24 := algebra.kdifference(_22,_23);
    _25 := algebra.kunion(_24,_23);
    _26:bat[:oid,:lng]  := sql.bind(_2,"sys","kvk","kvk",1);
    _27 := algebra.kunion(_25,_26);
    _28 := algebra.leftjoin(_21,_27);
    _29 := algebra.leftjoin(_21,_14);
exit _59;
    _30 := sql.resultSet(2,1,_28);
    sql.rsColumn(_30,"sys.kvk","kvk","bigint",64,0,_28);
    sql.rsColumn(_30,"sys.kvk","bedrijfsnaam","varchar",256,0,_29);
    _41 := io.stdout();
    sql.exportResult(_41,_30);
end s1_1;


sql>select kvk,bedrijfsnaam from kvk where bedrijfsnaam like 'MonetDB B.V.';
+--------------+--------------+
| kvk          | bedrijfsnaam |
+==============+==============+
| 321351760000 | MonetDB B.V. |
| 321351760000 | MonetDB B.V. |
+--------------+--------------+
2 tuples (1.8s)
sql>select kvk,bedrijfsnaam from kvk where bedrijfsnaam like 'MonetDB B.V.';
+--------------+--------------+
| kvk          | bedrijfsnaam |
+==============+==============+
| 321351760000 | MonetDB B.V. |
| 321351760000 | MonetDB B.V. |
+--------------+--------------+
2 tuples (681.097ms)
sql>select kvk,bedrijfsnaam from kvk where bedrijfsnaam like 'MonetDB B.V.';
+--------------+--------------+
| kvk          | bedrijfsnaam |
+==============+==============+
| 321351760000 | MonetDB B.V. |
| 321351760000 | MonetDB B.V. |
+--------------+--------------+
2 tuples (676.295ms)
sql>select kvk,bedrijfsnaam from kvk where bedrijfsnaam like 'MonetDB B.V.';
+--------------+--------------+
| kvk          | bedrijfsnaam |
+==============+==============+
| 321351760000 | MonetDB B.V. |
| 321351760000 | MonetDB B.V. |
+--------------+--------------+
2 tuples (675.428ms)

sql>select kvk,bedrijfsnaam from kvk where bedrijfsnaam = 'MonetDB B.V.';
+--------------+--------------+
| kvk          | bedrijfsnaam |
+==============+==============+
| 321351760000 | MonetDB B.V. |
| 321351760000 | MonetDB B.V. |
+--------------+--------------+
2 tuples (2.437ms)
sql>select kvk,bedrijfsnaam from kvk where bedrijfsnaam = 'MonetDB B.V.';
+--------------+--------------+
| kvk          | bedrijfsnaam |
+==============+==============+
| 321351760000 | MonetDB B.V. |
| 321351760000 | MonetDB B.V. |
+--------------+--------------+
2 tuples (0.754ms)
sql>select kvk,bedrijfsnaam from kvk where bedrijfsnaam = 'MonetDB B.V.';
+--------------+--------------+
| kvk          | bedrijfsnaam |
+==============+==============+
| 321351760000 | MonetDB B.V. |
| 321351760000 | MonetDB B.V. |
+--------------+--------------+
2 tuples (0.724ms)
sql>select kvk,bedrijfsnaam from kvk where bedrijfsnaam = 'MonetDB B.V.';
+--------------+--------------+
| kvk          | bedrijfsnaam |
+==============+==============+
| 321351760000 | MonetDB B.V. |
| 321351760000 | MonetDB B.V. |
+--------------+--------------+
2 tuples (0.764ms)

Hunch made by Fabian http://bugs.monetdb.org/show_bug.cgi?id=2684#c11 was on batcalc.str()

Reproducible: Always
Comment 1 Fabian Groffen 2010-12-10 15:39:01 CET
please show us your TRACE output
Comment 2 Stefan de Konink 2010-12-10 15:42:07 CET
(In reply to comment #1)
> please show us your TRACE output

trace select kvk,bedrijfsnaam from kvk where bedrijfsnaam like 'MonetDB B.V.';
+--------------+--------------+
| kvk          | bedrijfsnaam |
+==============+==============+
| 321351760000 | MonetDB B.V. |
| 321351760000 | MonetDB B.V. |
+--------------+--------------+
2 tuples (683.465ms)
+--------+-------------------------------------------------------------------------------------------------------------+
| ticks  | stmt                                                                                                        |
+========+=============================================================================================================+
|      3 | _3 := sql.mvc();                                                                                            |
|     25 | _4:bat[:oid,:str] <tmp_122710>[2620771] := sql.bind(_3=2792274296,"sys","kvk","bedrijfsnaam",0);            |
|     31 | _9:bat[:oid,:str] <tmp_125626>[0] := sql.bind(_3=2792274296,"sys","kvk","bedrijfsnaam",2);                  |
|      5 | _13:bat[:oid,:str] <tmp_125627>[0] := sql.bind(_3=2792274296,"sys","kvk","bedrijfsnaam",1);                 |
|      9 | _16:bat[:oid,:oid] <tmp_125613>[0] := sql.bind_dbat(_3=2792274296,"sys","kvk",1);                           |
|     30 | _11<tmp_132426>[2620771] := algebra.kdifference(_4=nil:bat[:oid,:str],_9=<tmp_125626>:bat[:oid,:str][0]);   |
|      5 | _17<tmpr_125613>[0] := bat.reverse(_16=nil:bat[:oid,:oid]);                                                 |
|      7 | _23:bat[:oid,:lng] <tmp_123470>[2620771] := sql.bind(_3=2792274296,"sys","kvk","kvk",0);                    |
|     30 | _12<tmp_132464>[2620771] := algebra.kunion(_11=nil,_9=nil:bat[:oid,:str]);                                  |
|      7 | _24:bat[:oid,:lng] <tmp_125624>[0] := sql.bind(_3=2792274296,"sys","kvk","kvk",2);                          |
|     17 | _25<tmp_132426>[2620771] := algebra.kdifference(_23=nil:bat[:oid,:lng],_24=<tmp_125624>:bat[:oid,:lng][0]); |
|     25 | _15<tmp_132471>[2620771] := algebra.kunion(_12=nil,_13=nil:bat[:oid,:str]);                                 |
|     15 | _18<tmp_132431>[2620771] := algebra.kdifference(_15=<tmp_132471>[2620771],_17=nil);                         |
|     22 | _26<tmp_132464>[2620771] := algebra.kunion(_25=nil,_24=nil:bat[:oid,:lng]);                                 |
|     19 | _19<tmp_132426>[2620771] := batcalc.str(_18=nil);                                                           |
|      8 | _27:bat[:oid,:lng] <tmp_125625>[0] := sql.bind(_3=2792274296,"sys","kvk","kvk",1);                          |
|     39 | _28<tmp_132152>[2620771] := algebra.kunion(_26=nil,_27=nil:bat[:oid,:lng]);                                 |
| 673272 | _20<tmp_132431>[2] := pcre.like_uselect(_19=nil,A0="MonetDB B.V.","":str);                                  |
|     20 | _21<tmp_132426>[2] := algebra.markT(_20=nil,0@0:oid);                                                       |
|      6 | _22<tmpr_132426>[2] := bat.reverse(_21=nil);                                                                |
|     39 | _29<tmp_131312>[2] := algebra.leftjoin(_22=<tmpr_132426>[2],_28=nil);                                       |
|     87 | _30<tmp_132467>[2] := algebra.leftjoin(_22=nil,_15=nil);                                                    |
| 675197 | barrier _59 := language.dataflow();                                                                         |
|     11 | _31 := sql.resultSet(2,1,_29=<tmp_131312>[2]);                                                              |
|      9 | sql.rsColumn(_31=31,"sys.kvk","kvk","bigint",64,0,_29=nil);                                                 |
|     13 | sql.rsColumn(_31=31,"sys.kvk","bedrijfsnaam","varchar",256,0,_30=nil);                                      |
|      2 | _42 := io.stdout();                                                                                         |
|     40 | sql.exportResult(_42=139701201987120,_31=31);                                                               |
| 675696 | user.s3_1("MonetDB B.V.");                                                                                  |
+--------+-------------------------------------------------------------------------------------------------------------+
29 tuples (683.533ms)

trace select kvk,bedrijfsnaam from kvk where bedrijfsnaam = 'MonetDB B.V.';
+--------------+--------------+
| kvk          | bedrijfsnaam |
+==============+==============+
| 321351760000 | MonetDB B.V. |
| 321351760000 | MonetDB B.V. |
+--------------+--------------+
2 tuples (9.999ms)
+-------+-------------------------------------------------------------------------------------------------------------+
| ticks | stmt                                                                                                        |
+=======+=============================================================================================================+
|     3 | _3 := sql.mvc();                                                                                            |
|    26 | _4:bat[:oid,:str] <tmp_122710>[2620771] := sql.bind(_3=2792274296,"sys","kvk","bedrijfsnaam",0);            |
|    57 | _9<tmp_131146>[2] := algebra.uselect(_4=<tmp_122710>:bat[:oid,:str][2620771],A0="MonetDB B.V.");            |
|     6 | _10:bat[:oid,:str] <tmp_125626>[0] := sql.bind(_3=2792274296,"sys","kvk","bedrijfsnaam",2);                 |
|    15 | _12<tmp_132431>[2] := algebra.kdifference(_9=nil,_10=<tmp_125626>:bat[:oid,:str][0]);                       |
|    22 | _13<tmp_132471>[0] := algebra.uselect(_10=<tmp_125626>:bat[:oid,:str][0],A0="MonetDB B.V.");                |
|    16 | _14<tmp_132464>[2] := algebra.kunion(_12=nil,_13=nil);                                                      |
|     5 | _15:bat[:oid,:str] <tmp_125627>[0] := sql.bind(_3=2792274296,"sys","kvk","bedrijfsnaam",1);                 |
|    17 | _17<tmp_132431>[0] := algebra.uselect(_15=<tmp_125627>:bat[:oid,:str][0],A0="MonetDB B.V.");                |
|    19 | _18<tmp_132471>[2] := algebra.kunion(_14=nil,_17=nil);                                                      |
|     7 | _19:bat[:oid,:oid] <tmp_125613>[0] := sql.bind_dbat(_3=2792274296,"sys","kvk",1);                           |
|     4 | _20<tmpr_125613>[0] := bat.reverse(_19=nil:bat[:oid,:oid]);                                                 |
|    10 | _21<tmp_132431>[2] := algebra.kdifference(_18=nil,_20=nil);                                                 |
|    12 | _22<tmp_132471>[2] := algebra.markT(_21=nil,0@0:oid);                                                       |
|     4 | _23<tmpr_132471>[2] := bat.reverse(_22=nil);                                                                |
|     7 | _24:bat[:oid,:lng] <tmp_123470>[2620771] := sql.bind(_3=2792274296,"sys","kvk","kvk",0);                    |
|     4 | _25:bat[:oid,:lng] <tmp_125624>[0] := sql.bind(_3=2792274296,"sys","kvk","kvk",2);                          |
|     9 | _26<tmp_132431>[2620771] := algebra.kdifference(_24=nil:bat[:oid,:lng],_25=<tmp_125624>:bat[:oid,:lng][0]); |
|    13 | _27<tmp_132464>[2620771] := algebra.kunion(_26=nil,_25=nil:bat[:oid,:lng]);                                 |
|     4 | _28:bat[:oid,:lng] <tmp_125625>[0] := sql.bind(_3=2792274296,"sys","kvk","kvk",1);                          |
|    11 | _29<tmp_132431>[2620771] := algebra.kunion(_27=nil,_28=nil:bat[:oid,:lng]);                                 |
|    25 | _30<tmp_131312>[2] := algebra.leftjoin(_23=<tmpr_132471>[2],_29=nil);                                       |
|    10 | _31<tmp_132431>[2620771] := algebra.kdifference(_4=nil:bat[:oid,:str],_10=<tmp_125626>:bat[:oid,:str][0]);  |
|    12 | _32<tmp_132464>[2620771] := algebra.kunion(_31=nil,_10=nil:bat[:oid,:str]);                                 |
|    12 | _33<tmp_132431>[2620771] := algebra.kunion(_32=nil,_15=nil:bat[:oid,:str]);                                 |

|    26 | _34<tmp_132467>[2] := algebra.leftjoin(_23=nil,_33=nil);                                                    |
|     7 | _35 := sql.resultSet(2,1,_30=<tmp_131312>[2]);                                                              |
|     8 | sql.rsColumn(_35=41,"sys.kvk","kvk","bigint",64,0,_30=nil);                                                 |
|     5 | sql.rsColumn(_35=41,"sys.kvk","bedrijfsnaam","varchar",256,0,_34=nil);                                      |
|     2 | _46 := io.stdout();                                                                                         |
|    40 | sql.exportResult(_46=139701201987120,_35=41);                                                               |
|  1968 | user.s4_1("MonetDB B.V.");                                                                                  |
+-------+-------------------------------------------------------------------------------------------------------------+
32 tuples (10.067ms)
Comment 3 Stefan de Konink 2010-12-10 15:48:26 CET
Created attachment 48 [details]
Two traces like.

This is odd... can someone explain what happened here??

(I mean the huge output, vs the small one.)
Comment 4 Stefan de Konink 2010-12-10 15:49:06 CET
Equals for better readability:


sql>trace select kvk,bedrijfsnaam from kvk where bedrijfsnaam = 'MonetDB B.V.';
% sys.kvk,      sys.kvk # table_name
% kvk,  bedrijfsnaam # name
% bigint,       varchar # type
% 12,   12 # length
[ 321351760000, "MonetDB B.V."  ]
[ 321351760000, "MonetDB B.V."  ]
% .trace,       .trace # table_name
% ticks,        stmt # name
% bigint,       varchar # type
% 4,    107 # length
[ 3,    "_3 := sql.mvc();"      ]
[ 26,   "_4:bat[:oid,:str] <tmp_122710>[2620771] := sql.bind(_3=2792274296,\"sys\",\"kvk\",\"bedrijfsnaam\",0);"        ]
[ 53,   "_9<tmp_131146>[2] := algebra.uselect(_4=<tmp_122710>:bat[:oid,:str][2620771],A0=\"MonetDB B.V.\");"    ]
[ 6,    "_10:bat[:oid,:str] <tmp_125626>[0] := sql.bind(_3=2792274296,\"sys\",\"kvk\",\"bedrijfsnaam\",2);"     ]
[ 19,   "_12<tmp_132421>[2] := algebra.kdifference(_9=nil,_10=<tmp_125626>:bat[:oid,:str][0]);" ]
[ 22,   "_13<tmp_132465>[0] := algebra.uselect(_10=<tmp_125626>:bat[:oid,:str][0],A0=\"MonetDB B.V.\");"        ]
[ 16,   "_14<tmp_132332>[2] := algebra.kunion(_12=nil,_13=nil);"        ]
[ 6,    "_15:bat[:oid,:str] <tmp_125627>[0] := sql.bind(_3=2792274296,\"sys\",\"kvk\",\"bedrijfsnaam\",1);"     ]
[ 16,   "_17<tmp_132421>[0] := algebra.uselect(_15=<tmp_125627>:bat[:oid,:str][0],A0=\"MonetDB B.V.\");"        ]
[ 17,   "_18<tmp_132465>[2] := algebra.kunion(_14=nil,_17=nil);"        ]
[ 7,    "_19:bat[:oid,:oid] <tmp_125613>[0] := sql.bind_dbat(_3=2792274296,\"sys\",\"kvk\",1);" ]
[ 5,    "_20<tmpr_125613>[0] := bat.reverse(_19=nil:bat[:oid,:oid]);"   ]
[ 10,   "_21<tmp_132421>[2] := algebra.kdifference(_18=nil,_20=nil);"   ]
[ 11,   "_22<tmp_132465>[2] := algebra.markT(_21=nil,0@0:oid);" ]
[ 4,    "_23<tmpr_132465>[2] := bat.reverse(_22=nil);"  ]
[ 7,    "_24:bat[:oid,:lng] <tmp_123470>[2620771] := sql.bind(_3=2792274296,\"sys\",\"kvk\",\"kvk\",0);"        ]
[ 4,    "_25:bat[:oid,:lng] <tmp_125624>[0] := sql.bind(_3=2792274296,\"sys\",\"kvk\",\"kvk\",2);"      ]
[ 9,    "_26<tmp_132421>[2620771] := algebra.kdifference(_24=nil:bat[:oid,:lng],_25=<tmp_125624>:bat[:oid,:lng][0]);"   ]
[ 12,   "_27<tmp_132332>[2620771] := algebra.kunion(_26=nil,_25=nil:bat[:oid,:lng]);"   ]
[ 5,    "_28:bat[:oid,:lng] <tmp_125625>[0] := sql.bind(_3=2792274296,\"sys\",\"kvk\",\"kvk\",1);"      ]
[ 11,   "_29<tmp_132421>[2620771] := algebra.kunion(_27=nil,_28=nil:bat[:oid,:lng]);"   ]
[ 36,   "_30<tmp_132417>[2] := algebra.leftjoin(_23=<tmpr_132465>[2],_29=nil);" ]
[ 10,   "_31<tmp_132421>[2620771] := algebra.kdifference(_4=nil:bat[:oid,:str],_10=<tmp_125626>:bat[:oid,:str][0]);"    ]
[ 12,   "_32<tmp_132332>[2620771] := algebra.kunion(_31=nil,_10=nil:bat[:oid,:str]);"   ]
[ 12,   "_33<tmp_132421>[2620771] := algebra.kunion(_32=nil,_15=nil:bat[:oid,:str]);"   ]
[ 24,   "_34<tmp_132467>[2] := algebra.leftjoin(_23=nil,_33=nil);"      ]
[ 8,    "_35 := sql.resultSet(2,1,_30=<tmp_132417>[2]);"        ]
[ 8,    "sql.rsColumn(_35=49,\"sys.kvk\",\"kvk\",\"bigint\",64,0,_30=nil);"     ]
[ 6,    "sql.rsColumn(_35=49,\"sys.kvk\",\"bedrijfsnaam\",\"varchar\",256,0,_34=nil);"  ]
[ 2,    "_46 := io.stdout();"   ]
[ 39,   "sql.exportResult(_46=139701201987120,_35=49);" ]
[ 1957, "user.s4_1(\"MonetDB B.V.\");"  ]
Comment 5 Niels Nes cwiconfidential 2010-12-13 22:42:55 CET
stefan do you have a small table dump with schema such that I can repeat the problem.
Comment 6 Stefan de Konink 2010-12-13 22:50:28 CET
(In reply to comment #5)
> stefan do you have a small table dump with schema such that I can repeat the
> problem.

I think you must be able to apply it to the dump I have send you before regarding the 'order by' bug and distinct.
Comment 7 Fabian Groffen 2010-12-14 16:02:58 CET
We are sure that a like without modifiers is executed through an uselect.  Performance reduction is mitosis related.

This is a feature request for a more sophisticated detection of this, and a rewrite into an equals query, such that the plan really is equal, and the fast path for point queries (dealing with mitosis) is used.
Comment 8 Sjoerd Mullender cwiconfidential 2011-07-29 11:00:05 CEST
Apr2011-SP2 has been released.
Comment 9 Sjoerd Mullender cwiconfidential 2011-09-16 15:10:39 CEST
The Aug2011 version has been released.
Comment 10 Sjoerd Mullender cwiconfidential 2012-08-24 14:55:58 CEST
Jul2012-SP1 has been released.
Comment 11 Ying Zhang cwiconfidential 2012-11-27 15:27:38 CET
No test for feature request
Comment 13 Sjoerd Mullender cwiconfidential 2020-06-03 09:20:23 CEST
The content of attachment 678 [details] has been deleted for the following reason:

spam